Embulk - HTN20190109の日記

MySQL

(8.0.26)
https://qiita.com/777nancy/items/d5512bffab50a0796c5c

前提: Embulkインストール済

wget https://download.oracle.com/otn-pub/otn_software/jdbc/1914/ojdbc8-full.tar.gz
tar xvzf ojdbc8-full.tar.gz
wget https://jdbc.postgresql.org/download/postgresql-42.3.3.jar

drop table tab1;
create table tab1(col1 bigint, col2 varchar(100),col3 datetime(6) );

insert into tab1 values(1,'A',CURRENT_TIMESTAMP(6));
insert into tab1 values(2,'B',CURRENT_TIMESTAMP(6));
insert into tab1 values(3,'C',CURRENT_TIMESTAMP(6));

select * from tab1;

-- MySQL -> Oracle

drop table tab1 purge;
create table tab1(col1 int, col2 varchar2(100),col3 timestamp);
select * from tab1;

vim mysql_oracle.yml

in:
type: mysql
host: 192.168.137.66
user: root
password: "password"
database: test
table: tab1

out:
type: oracle
driver_path: ./ojdbc8-full/ojdbc8.jar
driver_class: oracle.jdbc.driver.OracleDriver
url: jdbc:oracle:thin:@192.168.137.65:1521/pdb1.example.com
user: test
password: "test"
table: tab1
mode: insert

embulk preview mysql_oracle.yml
embulk run mysql_oracle.yml

-- MySQL -> PostgreSQL

drop table tab1;
create table tab1(col1 bigint, col2 varchar(100),col3 timestamp);
select * from tab1;

vim mysql_postgresql.yml

in:
type: mysql
host: 192.168.137.66
user: root
password: "password"
database: test
table: tab1

out:
type: postgresql
driver_path: ./postgresql-42.3.3.jar
host: 192.168.137.70
user: postgres
password: "postgres"
database: test
table: tab1
mode: insert

embulk preview mysql_postgresql.yml
embulk run mysql_postgresql.yml

-- MySQL -> SQL Server

drop table tab1;
create table tab1(col1 bigint, col2 varchar(100),col3 datetime2 );
select * from tab1;

vim mysql_sqlserver.yml

in:
type: mysql
host: 192.168.137.66
user: root
password: "password"
database: test
table: tab1

out:
type: sqlserver
host: 192.168.137.63
user: sa
password: "password"
database: test
table: tab1
mode: insert

embulk preview mysql_sqlserver.yml
embulk run mysql_sqlserver.yml

Oracle

(19c)
https://qiita.com/mkyz08/items/90b4ef10e69d1d89b0ad

前提: Embulkインストール済

wget https://download.oracle.com/otn-pub/otn_software/jdbc/1914/ojdbc8-full.tar.gz
tar xvzf ojdbc8-full.tar.gz
wget https://jdbc.postgresql.org/download/postgresql-42.3.3.jar

drop table tab1 purge;
create table tab1(col1 int, col2 varchar2(100),col3 timestamp);

insert into tab1 values(1,'A',systimestamp);
insert into tab1 values(2,'B',systimestamp);
insert into tab1 values(3,'C',systimestamp);
commit;
select * from tab1;

-- Oracle -> MySQL

drop table tab1;
create table tab1(col1 bigint, col2 varchar(100),col3 datetime(6) );
select * from tab1;

vim oracle_mysql.yml

in:
type: oracle
driver_path: ./ojdbc8-full/ojdbc8.jar
driver_class: oracle.jdbc.driver.OracleDriver
url: jdbc:oracle:thin:@192.168.137.65:1521/pdb1.example.com
user: test
password: "test"
table: tab1

out:
type: mysql
host: 192.168.137.66
user: root
password: "password"
database: test
table: tab1
mode: insert

embulk preview oracle_mysql.yml
embulk run oracle_mysql.yml

-- Oracle -> PostgreSQL

drop table tab1;
create table tab1(col1 bigint, col2 varchar(100),col3 timestamp);
select * from tab1;

vim oracle_postgresql.yml

out:
type: postgresql
driver_path: ./postgresql-42.3.3.jar
host: 192.168.137.70
user: postgres
password: "postgres"
database: test
table: tab1
mode: insert

embulk preview oracle_postgresql.yml
embulk run oracle_postgresql.yml

-- Oracle -> SQL Server

drop table tab1;
create table tab1(col1 bigint, col2 varchar(100),col3 datetime2 );
select * from tab1;

vim oracle_sqlserver.yml

out:
type: sqlserver
host: 192.168.137.63
user: sa
password: "password"
database: test
table: tab1
mode: insert

embulk preview oracle_sqlserver.yml
embulk run oracle_sqlserver.yml

PostgreSQL

(14)

前提: Embulkインストール済

wget https://download.oracle.com/otn-pub/otn_software/jdbc/1914/ojdbc8-full.tar.gz
tar xvzf ojdbc8-full.tar.gz
wget https://jdbc.postgresql.org/download/postgresql-42.3.3.jar

drop table tab1;
create table tab1(col1 bigint, col2 varchar(100),col3 timestamp);

insert into tab1 values(1,'A',clock_timestamp());
insert into tab1 values(2,'B',clock_timestamp());
insert into tab1 values(3,'C',clock_timestamp());

select * from tab1;

-- PostgreSQL -> MySQL

drop table tab1;
create table tab1(col1 bigint, col2 varchar(100),col3 datetime(6) );
select * from tab1;

vim postgresql_mysql.yml

in:
type: postgresql
driver_path: ./postgresql-42.3.3.jar
host: 192.168.137.70
user: postgres
password: "postgres"
database: test
table: tab1

out:
type: mysql
host: 192.168.137.66
user: root
password: "password"
database: test
table: tab1
mode: insert

embulk preview postgresql_mysql.yml
embulk run postgresql_mysql.yml

-- PostgreSQL -> Oracle

drop table tab1 purge;
create table tab1(col1 int, col2 varchar2(100),col3 timestamp);
select * from tab1;

vim postgresql_oracle.yml

in:
type: postgresql
driver_path: ./postgresql-42.3.3.jar
host: 192.168.137.70
user: postgres
password: "postgres"
database: test
table: tab1

embulk preview postgresql_oracle.yml
embulk run postgresql_oracle.yml

-- PostgreSQL -> SQL Server

drop table tab1;
create table tab1(col1 bigint, col2 varchar(100),col3 datetime2 );
select * from tab1;

vim postgresql_sqlserver.yml

in:
type: postgresql
driver_path: ./postgresql-42.3.3.jar
host: 192.168.137.70
user: postgres
password: "postgres"
database: test
table: tab1

out:
type: sqlserver
host: 192.168.137.63
user: sa
password: "password"
database: test
table: tab1
mode: insert

embulk preview postgresql_sqlserver.yml
embulk run postgresql_sqlserver.yml

SQL Server

(2019)

https://github.com/embulk/embulk-input-jdbc/tree/master/embulk-input-sqlserver
時刻型は文字型として移行

前提: Embulkインストール済

wget https://download.oracle.com/otn-pub/otn_software/jdbc/1914/ojdbc8-full.tar.gz
tar xvzf ojdbc8-full.tar.gz
wget https://jdbc.postgresql.org/download/postgresql-42.3.3.jar

drop table tab1;
create table tab1(col1 bigint, col2 varchar(100),col3 datetime2 );

insert into tab1 values(1,'A',getdate());
insert into tab1 values(2,'B',getdate());
insert into tab1 values(3,'C',getdate());

select * from tab1;

-- SQL Server -> MySQL

drop table tab1;
create table tab1(col1 bigint, col2 varchar(100),col3 varchar(100) );
select * from tab1;

vim sqlserver_mysql.yml

in:
type: sqlserver
host: 192.168.137.63
user: sa
password: "password"
database: test
table: tab1
default_column_options:
datetime2: { type: string, timestamp_format: "%Y/%m/%d %H:%M:%S", timezone: "+0900"}

out:
type: mysql
host: 192.168.137.66
user: root
password: "password"
database: test
table: tab1
mode: insert

embulk preview sqlserver_mysql.yml
embulk run sqlserver_mysql.yml

-- SQL Server -> Oracle

drop table tab1 purge;
create table tab1(col1 int, col2 varchar2(100),col3 varchar2(100) );
select * from tab1;

vim sqlserver_oracle.yml

embulk preview sqlserver_oracle.yml
embulk run sqlserver_oracle.yml

-- SQL Server -> PostgreSQL

drop table tab1;
create table tab1(col1 bigint, col2 varchar(100),col3 varchar(100) );
select * from tab1;

vim sqlserver_postgresql.yml

out:
type: postgresql
driver_path: ./postgresql-42.3.3.jar
host: 192.168.137.70
user: postgres
password: "postgres"
database: test
table: tab1
mode: insert

embulk preview sqlserver_postgresql.yml
embulk run sqlserver_postgresql.yml