Zenn
Open31

EmbulkでRDS(MySQL)のデータをBigQueryに同期

Hid3Hid3

EC2 t3.medium3台でapp,batchを動かしている大体10%弱。

本番どこで動かす？
EC2, ECS

Hid3Hid3
Usage: embulk [-vm-options] <command> [--options]
Commands:
   mkbundle   <directory>                             # create a new plugin bundle environment.
   bundle     [directory]                             # update a plugin bundle environment.
   run        <config.yml>                            # run a bulk load transaction.
   cleanup    <config.yml>                            # cleanup resume state.
   preview    <config.yml>                            # dry-run the bulk load without output and show preview.
   guess      <partial-config.yml> -o <output.yml>    # guess missing parameters to create a complete configuration file.
   gem        <install | list | help>                 # install a plugin or show installed plugins.
   new        <category> <name>                       # generates new plugin template
   migrate    <path>                                  # modify plugin code to use the latest Embulk plugin API
   example    [path]                                  # creates an example config file and csv file to try embulk.
   selfupdate [version]                               # upgrades embulk to the latest released version or to the specified version.

VM options:
   -E...                            Run an external script to configure environment variables in JVM
                                    (Operations not just setting envs are not recommended nor guaranteed.
                                     Expect side effects by running your external script at your own risk.)
   -J-O                             Disable JVM optimizations to speed up startup time (enabled by default if command is 'run')
   -J+O                             Enable JVM optimizations to speed up throughput
   -J...                            Set JVM options (use -J-help to see available options)
   -R--dev                          Set JRuby to be in development mode

Use `<command> --help` to see description of the commands.
Hid3Hid3

embulk設定をしていく。
設定ふぁいるは
ymlファイルとyml.liquidがあってliquidは環境変数を埋め込める。

とりあえずローカルで試す。

in:
  type: mysql
  host: localhost
  user: root
  password: ""
  database: ohohoho
  table: user_activations
  select: "id, user_id, activated_at"
  order_by: "id DESC"

embulk preview xxxxxxxxxxxxxxx.yml.liquid

で動いたわね。

Hid3Hid3

MySQLの複数テーブルを、BigQueryのそれぞれのテーブルへ同期。
上書き？作成して、削除？

configのinとoutの書き方は？

Hid3Hid3

https://qiita.com/shiozaki/items/f79eecf8e1878aa64a40
(digdaga)テーブル一覧を取得してループで一つずつ環境変数でテーブル情報などを渡してembulkを実行している。

https://techblog.zozo.com/entry/embulk_data_transfer

https://findy-code.io/engineer-lab/ecs-embulk
これはテーブルごとにymlファイル作る。

https://kamihikouki.hatenablog.com/entry/2017/08/21/011446
テーブルごとにymlファイルを作る。DB情報は共通化している。ls configs/*.yml.liquid | xargs -I {} embulk run {}

https://labs.gree.jp/blog/2019/03/17834/
テーブルごとに作ってxargs

Hid3Hid3
ls config/*.yml.liquid | xargs -I {} embulk run {}

動かん。、なんで、。。。

xargs: embulk: Exec format error

xargsわからん

ls config/*.yml.liquid | xargs embulk

xargs: embulk: Exec format error

これでもだめ、。普通embulkのコマンド一覧みたいなのが出るはずなのに出ない

ls config/*.yml.liquid | xargs echo

これは透。

Hid3Hid3

embulkは1回の実行で一つのテーブルをmysql->bigqueryに移す。
何十ものテーブルを同期する場合、その数だけembulkを実行させる？
その場合、テーブルごとに時間がずれるのでデータの中身も変わってしまう。

これを防ぐには、mysqlをdumpして、dumpしたものをbigqueryに移すのがよい？
dumpって実行した時点でのデータが取れるの？dump中にDBのデータに変更があった場合ってどうなるの？
オプションでhttps://dev.mysql.com/doc/refman/5.6/ja/mysqldump.html#option_mysqldump_single-transaction があるので大丈夫。

このタイミングの違いによるデータの内容のずれって許容してもいいのかな。
用途としては、本番DBのデータを一部マスクしてまるっとBigQueryに移して、分析に使う。1日1回動けばいい。

Hid3Hid3

1テーブル2分半かかってる。。。100テーブルだと4時間くらい。。これはだめだ。
直列。並列で実行するのか？DB接続数。
embulk毎回DBへ接続しにいく必要ある？

Hid3Hid3

そもそも、RDSスナップショット取って起動・停止が自動でできれば
・テーブルごとにタイミングずれる問題
・ついでにDB負荷の考慮
解決だ。

Hid3Hid3
[
  {
    "name": "id",
    "type": "INTEGER"
  },
  {
    "name": "name",
    "type": "STRING"
  },
  {
    "name": "created_at",
    "type": "TIMESTAMP"
  }
]

こんなのをテーブルの数だけ。30くらい作らなきゃ。
既存で存在しているやつを元に作るので、画面みながら手作業？
って思ったらjsonで既存のものからダウンロードできるっぽい１

Hid3Hid3

DBに日本時間で入っている、
そのままだとUTCになってしまう。

Hid3Hid3
"Error while reading data, error message: Could not parse '2000-01-01 00:00:00.000000 +00:00' as DATE for field opened_on (position 5) starting at location 0  with message 'Unable to parse'"

date型が素直に入らない

Hid3Hid3

booleanもCould not parse 'true' as INT64 for field registration_flow (position 2) starting at location 0 with message 'Unable to parse'

Hid3Hid3

dateは文字列に
in:
default_column_options:
DATE: { type: string, timestamp_format: "%Y-%m-%d" }

tinyintはbigqueryのスキーマをintegerにしてたのをbooleanにして対応
bigquery側には0, 1ではなくtru, falseで入る。

Hid3Hid3

embulk実行中に以下のエラ〜

org.embulk.exec.PartialExecutionException: java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Communications link failure 
during rollback(). Transaction resolution unknown

開始30分後にエラ〜。
mysqlのタイムアウトあたり、embulkのタイムアウトの設定をみてみる。

Hid3Hid3

https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0075

connect_timeout
MySQLがクライアントからの接続パケットを待機する時間（秒）となります。デフォルトは10秒です。コネクション生成エラーが頻発する場合はこの値を変更するといいでしょう。set global句を使用したオンラインでの変更可能です。

wait_timeout
アプリケーションなどから接続された非対話型の接続に対してのアイドルタイムアウト時間（秒）となります。コネクションステータスがSleep（アイドル）の状態がこのタイムアウト値を超えると切断されます。デフォルトは28800秒（8時間）で，オンラインでの変更可能です。

interactive_timeout
mysqlクライアントでログインした時などの対話型の接続に対してのアイドルタイムアウト時間（秒）となります（mysqlクライアントは--executeオプションを使用した場合は非対話型となります⁠）⁠。接続が切断される条件やデフォルト値などはwait_timeoutオプションと同様です。

どちらもデフォルトなのでこっちの問題はなさそう

Hid3Hid3

mysql
aaa tinyint(3) unsigned NOT NULL,
bbb tinyint(1) NOT NULL DEFAULT '0',

BigQueryにaaaはintegerで入れることができる
bbbはintegerに入れようとするとエラー
なぜ？

bbbの方はintegerじゃなくてbooleanにすれば解決はするけど気になる。

Hid3Hid3
[{:reason=>"invalidQuery", :message=>"Cannot return an invalid timestamp value of -62135769600000000 microseconds relative to the Unix epoch. The range of valid timestamp values is [0001-01-1 00:00:00, 9999-12-31 23:59:59.999999]; error in writing field birth_date", :location=>"query"}]
Hid3Hid3

mysql: 0001-01-02 09:00:01
をbigqueryに写そとしても上記エラー。

Hid3Hid3

タイムゾーンがわからない。。。
bigquertの型はtimestamp, datetimeどっちでもいい？