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

https://github.com/medjed/embulk-input-bigquery

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

===========================================================
BigQueryでの作業

-- 1. 前作業

gcloud init
gcloud auth list

gcloud --version

gcloud projects create project01-9999999 \
--name="project01"

gcloud config list
gcloud config set project project01-9999999
gcloud config set compute/region asia-northeast1 --quiet
gcloud config set compute/zone asia-northeast1-a --quiet

gcloud beta billing accounts list
gcloud beta billing projects link project01-9999999 --billing-account=111111-111111-111111

gcloud services enable compute.googleapis.com --project project01-9999999

gcloud components update

-- 2. BigQuery API有効化

gcloud services list --enabled

gcloud services enable bigquery.googleapis.com \
--project project01-9999999

-- 3. サービスアカウントの作成

gcloud iam service-accounts create sa123 \
--description="sa123" \
--display-name="sa123"

gcloud iam service-accounts list

gcloud projects add-iam-policy-binding project01-9999999 \
--member="serviceAccount:sa123@project01-9999999.iam.gserviceaccount.com" \
--role="roles/owner"

gcloud projects get-iam-policy project01-9999999

gcloud iam service-accounts keys create ~/key01.json \
--iam-account=sa123@project01-9999999.iam.gserviceaccount.com

cat ~/key01.json

gcloud iam service-accounts keys list \
--iam-account=sa123@project01-9999999.iam.gserviceaccount.com

scp key01.json root@192.168.137.190:/root

-- 4. Datasetとテストテーブル作成

bq mk ds01
bq ls

bq mk \
--table \
--schema 'col1:INT64,col2:STRING,col3:TIMESTAMP' \
ds01.tab1

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

select * FROM ds01.tab1;

===========================================================
宛先DBサーバでの作業

-- 5. テストテーブルの作成(MySQL)

use test

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

select * from tab1;

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

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

select * from tab1;

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

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

select * from tab1;

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

use test
go

drop table tab1;
go

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

select * from tab1;
go

===========================================================
Embulkサーバでの作業

-- 9. Java 8インストール

dnf install -y java-1.8.0-openjdk
dnf install -y java-1.8.0-openjdk-devel

java -version

-- 10. サーバー時刻の設定

認証エラー回避のため、正しい時刻に設定する

dnf install -y chrony
systemctl restart chronyd
systemctl enable chronyd
chronyc makestep
chronyc sources

-- 11. 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 search -rd embulk-input

embulk gem install jwt -v 2.3.0
embulk gem install public_suffix -v 4.0.7
embulk gem install mini_mime -v 1.0.0

embulk gem install representable -v 3.0.4

embulk gem install embulk-input-bigquery

embulk gem list

-- 12. 動作確認

-- 12.1 BQ -> MySQL

vi bq_mysql.yml

in:
type: bigquery
project: project01-9999999
keyfile: /root/key01.json
sql: 'SELECT col1,col2,col3 FROM ds01.tab1'
columns:
- {name: col1, type: long}
- {name: col2, type: string}
- {name: col3, type: timestamp}
out:
type: mysql
host: 192.168.137.66
user: root
password: password
database: test
table: tab1
mode: insert

embulk preview bq_mysql.yml

embulk run bq_mysql.yml

-- 12.2 BQ -> Oracle

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

vi bq_oracle.yml

in:
type: bigquery
project: project01-9999999
keyfile: /root/key01.json
sql: 'SELECT col1,col2,col3 FROM ds01.tab1'
columns:
- {name: col1, type: long}
- {name: col2, type: string}
- {name: col3, type: timestamp}
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 bq_oracle.yml

embulk run bq_oracle.yml

-- 12.3 BQ -> PostgreSQL

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

vi bq_postgresql.yml

in:
type: bigquery
project: project01-9999999
keyfile: /root/key01.json
sql: 'SELECT col1,col2,col3 FROM ds01.tab1'
columns:
- {name: col1, type: long}
- {name: col2, type: string}
- {name: col3, type: timestamp}
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 bq_postgresql.yml

embulk run bq_postgresql.yml

-- 12.4 BQ -> SQL Server

vi bq_sqlserver.yml

in:
type: bigquery
project: project01-9999999
keyfile: /root/key01.json
sql: 'SELECT col1,col2,col3 FROM ds01.tab1'
columns:
- {name: col1, type: long}
- {name: col2, type: string}
- {name: col3, type: timestamp}
out:
type: sqlserver
host: 192.168.137.174
user: sa
password: password
database: test
table: tab1
mode: insert

embulk preview bq_sqlserver.yml

embulk run bq_sqlserver.yml

-- 13. クリーンアップ

bq rm --recursive=true --force ds01
bq ls

gcloud projects list

gcloud projects delete project01-9999999 \
--quiet