Embulk(CSVから入力) - HTN20190109の日記

https://uga-box.hatenablog.com/entry/2020/01/25/000000
https://www.embulk.org/docs/built-in.html

宛先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) );

select * from tab1;

-- 2. テストテーブルの作成(Oracle)

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

select * from tab1;

-- 3. テストテーブルの作成(PostgreSQL)

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

select * from tab1;

-- 4. テストテーブルの作成(SQL Server)

use test
go

drop table tab1;
go

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

select * from tab1;
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-output-mysql
embulk gem install embulk-output-oracle
embulk gem install embulk-output-postgresql
embulk gem install embulk-output-sqlserver

embulk gem list

-- 7. 動作確認

vi tab1.csv
1,"A","2023-08-13 01:01:33"
2,"B","2023-08-13 01:01:34"
3,"C","2023-08-13 01:01:35"

-- 7.1 CSV -> MySQL

vi csv_mysql.yml

in:
type: file
path_prefix: ./tab1.csv
parser:
type: csv
delimiter: ','
quote: '"'
escape: '"'
skip_header_lines: 0
trim_if_not_quoted: false
allow_optional_columns: false
allow_extra_columns: false
default_timezone: 'Asia/Tokyo'
newline: LF
line_delimiter_recognized: LF
charset: UTF-8
columns:
- {name: col1, type: long}
- {name: col2, type: string}
- {name: col3, type: timestamp, format: '%Y-%m-%d %H:%M:%S'}
out:
type: mysql
host: 192.168.137.66
user: root
password: password
database: test
table: tab1
mode: insert

embulk preview csv_mysql.yml

embulk run csv_mysql.yml

-- 7.2 CSV -> Oracle

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

vi csv_oracle.yml

in:
type: file
path_prefix: ./tab1.csv
parser:
type: csv
delimiter: ','
quote: '"'
escape: '"'
skip_header_lines: 0
trim_if_not_quoted: false
allow_optional_columns: false
allow_extra_columns: false
default_timezone: 'Asia/Tokyo'
newline: LF
line_delimiter_recognized: LF
charset: UTF-8
columns:
- {name: col1, type: long}
- {name: col2, type: string}
- {name: col3, type: timestamp, format: '%Y-%m-%d %H:%M:%S'}
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 csv_oracle.yml

embulk run csv_oracle.yml

-- 7.3 CSV -> PostgreSQL

wget https://jdbc.postgresql.org/download/postgresql-42.3.3.jar

vi csv_postgresql.yml

in:
type: file
path_prefix: ./tab1.csv
parser:
type: csv
delimiter: ','
quote: '"'
escape: '"'
skip_header_lines: 0
trim_if_not_quoted: false
allow_optional_columns: false
allow_extra_columns: false
default_timezone: 'Asia/Tokyo'
newline: LF
line_delimiter_recognized: LF
charset: UTF-8
columns:
- {name: col1, type: long}
- {name: col2, type: string}
- {name: col3, type: timestamp, format: '%Y-%m-%d %H:%M:%S'}
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 csv_postgresql.yml

embulk run csv_postgresql.yml

-- 7.4 CSV -> SQL Server

vi csv_sqlserver.yml

in:
type: file
path_prefix: ./tab1.csv
parser:
type: csv
delimiter: ','
quote: '"'
escape: '"'
skip_header_lines: 0
trim_if_not_quoted: false
allow_optional_columns: false
allow_extra_columns: false
default_timezone: 'Asia/Tokyo'
newline: LF
line_delimiter_recognized: LF
charset: UTF-8
columns:
- {name: col1, type: long}
- {name: col2, type: string}
- {name: col3, type: timestamp, format: '%Y-%m-%d %H:%M:%S'}
out:
type: sqlserver
host: 192.168.137.174
user: sa
password: password
database: test
table: tab1
mode: insert

embulk preview csv_sqlserver.yml

embulk run csv_sqlserver.yml