https://qiita.com/reflet/items/5dbfcfc99d9b1edf2a3a
https://www.embulk.org/docs/built-in.html#file-output-plugin
ソースDBサーバ :
MySQL : CentOS 7 , 8.0.33
Oracle : CentOS 7 , 19c
PostgreSQL : CentOS 8 , 14.5
SQL Server : Rocky Linux 8 , 2019
Embulkサーバ : Rocky Linux 9
バージョン :
java : 1.8.0_382
embulk : 0.9.25
===========================================================
ソースDBサーバでの作業
-- 1. テストテーブルの作成(MySQL)
use test
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;
-- 2. テストテーブルの作成(Oracle)
drop table tab2 purge;
create table tab2(col1 int, col2 varchar2(100),col3 timestamp);
insert into tab2 values(1,'A',systimestamp);
insert into tab2 values(2,'B',systimestamp);
insert into tab2 values(3,'C',systimestamp);
commit;
select * from tab2;
-- 3. テストテーブルの作成(PostgreSQL)
drop table tab3;
create table tab3(col1 bigint, col2 varchar(100),col3 timestamp);
insert into tab3 values(1,'A',clock_timestamp());
insert into tab3 values(2,'B',clock_timestamp());
insert into tab3 values(3,'C',clock_timestamp());
select * from tab3;
-- 4. テストテーブルの作成(SQL Server)
use test
go
drop table tab4;
go
create table tab4(col1 bigint, col2 varchar(100),col3 datetime2 );
insert into tab4 values(1,'A',getdate());
insert into tab4 values(2,'B',getdate());
insert into tab4 values(3,'C',getdate());
select * from tab4;
go
===========================================================
Embulkサーバでの作業
-- 5. Java 8インストール
dnf install -y java-1.8.0-openjdk java-1.8.0-openjdk-devel
java -version
-- 6. Embulkインストール
0.11は未対応のプラグインが多いので0.9を使用する
curl --create-dirs -o ~/.embulk/bin/embulk -L "https://github.com/embulk/embulk/releases/download/v0.9.25/embulk-0.9.25.jar"
chmod +x ~/.embulk/bin/embulk
echo 'export PATH="$HOME/.embulk/bin:$PATH"' >> ~/.bashrc
source ~/.bashrc
embulk --version
embulk gem install embulk-input-mysql
embulk gem install embulk-input-oracle
embulk gem install embulk-input-postgresql
embulk gem install embulk-input-sqlserver
embulk gem list
-- 7. 動作確認
in:
type: mysql
host: 192.168.137.66
user: root
password: password
database: test
table: tab1
out:
type: file
path_prefix: "./tab1"
sequence_format: "."
file_ext: csv
formatter:
type: csv
delimiter: ","
quote: '"'
quote_policy: ALL
escape: "\\"
header_line: false
null_string: "\\N"
newline: LF
newline_in_field: LF
charset: UTF-8
default_timezone: 'Asia/Tokyo'
column_options:
col3: {format: '%Y-%m-%d %H:%M:%S'}
cat tab1.csv
wget https://download.oracle.com/otn-pub/otn_software/jdbc/1914/ojdbc8-full.tar.gz
tar xvzf ojdbc8-full.tar.gz
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: tab2
out:
type: file
path_prefix: "./tab2"
sequence_format: "."
file_ext: csv
formatter:
type: csv
delimiter: ","
quote: '"'
quote_policy: ALL
escape: "\\"
header_line: false
null_string: "\\N"
newline: LF
newline_in_field: LF
charset: UTF-8
default_timezone: 'Asia/Tokyo'
column_options:
COL3: {format: '%Y-%m-%d %H:%M:%S'}
cat tab2.csv
-- 7.3 PostgreSQL -> CSV
wget https://jdbc.postgresql.org/download/postgresql-42.3.3.jar
vi postgresql_csv.yml
in:
type: postgresql
driver_path: ./postgresql-42.3.3.jar
host: 192.168.137.70
user: postgres
password: postgres
database: test
table: tab3
out:
type: file
path_prefix: "./tab3"
sequence_format: "."
file_ext: csv
formatter:
type: csv
delimiter: ","
quote: '"'
quote_policy: ALL
escape: "\\"
header_line: false
null_string: "\\N"
newline: LF
newline_in_field: LF
charset: UTF-8
default_timezone: 'Asia/Tokyo'
column_options:
col3: {format: '%Y-%m-%d %H:%M:%S'}
embulk preview postgresql_csv.yml
embulk run postgresql_csv.yml
cat tab3.csv
-- 7.4 SQL Server -> CSV
in:
type: sqlserver
host: 192.168.137.174
user: sa
password: password
database: test
table: tab4
default_column_options:
datetime2: { type: string, timestamp_format: "%Y/%m/%d %H:%M:%S", timezone: "+0900"}
out:
type: file
path_prefix: "./tab4"
sequence_format: "."
file_ext: csv
formatter:
type: csv
delimiter: ","
quote: '"'
quote_policy: ALL
escape: "\\"
header_line: false
null_string: "\\N"
newline: LF
newline_in_field: LF
charset: UTF-8
default_timezone: 'Asia/Tokyo'
column_options:
col3: {format: '%Y-%m-%d %H:%M:%S'}
embulk preview sqlserver_csv.yml
cat tab4.csv
