MySQLからEmbulkを使用してGoogleBigQueryへデータをロードする

ユースケース

RDBへ書き込んでいたログデータをBigQueryのようなDWHに取り込みたい時に
簡単にバルクインサートしてくれます。
（もちろんRDBとBigQueryだけの関係でもありませんが）

特にログデータとは限らないのですが、時間とユーザーさんの数が増えるごとに「永遠に増え続けるデータ」をRDBへ書き込んでいた。
そのまま時間が経ってしまい「単一のTBLのレコード数が数千万になってしまった」
時に利用すると良いと思います。

「最初からFluentdで送っておけば良いじゃない」というのはごもっともですが。

Embulkを使わない方法

mysqldump -> csv -> gcs(awsのs3のような物) -> Big queryヘロード
が妥当かと思います。実際やってみましたが簡単です。

が、一回きりのロードであればこれでもいいのですが
「定期的に取り込みたい」
「バルクロードでちゃんと進捗が見たい」
「あのTBLもこのTBLも取り込みたい」

となるともう少し「設定」っぽいやり方がしたくなります。
そこでEmbulkを利用することにしました。

Embulkとは

本家が綺麗に図解されています。
Fluentdで最初っから送っておけばこんな思いしなくていいのに。
というのがとても感じられます。
https://github.com/embulk/embulk

EmbulkがMySQLからデータを引っ張り出してきてくれて、
一時ファイルに保存してBigQueryにデータをロードしてくれます。

前提

GCP, BigQueryのセットアップは済んでいること
http://heartbeats.jp/hbblog/2014/06/google-bigquery.html
参考になります。

環境

Amazon Linux AMI
MySQL5.6
embulk 0.8.8

補足

会社で利用しようとするとクレジットカード登録だったり、すでに有るGoogleAppsとの兼ね合いだったりで何やかんやと面倒な事が多いです。早めに動く事とそれなりに気長に考える事も重要。

Embulkのインストール

インストール（本家のコピペ）

curl --create-dirs -o ~/.embulk/bin/embulk -L "http://dl.embulk.org/embulk-latest.jar"
chmod +x ~/.embulk/bin/embulk
echo 'export PATH="$HOME/.embulk/bin:$PATH"' >> ~/.bashrc
source ~/.bashrc

プラグインのインストール

embulk gem install embulk-input-mysql
embulk gem install embulk-output-bigquery

動かしてみる

必要なファイルは2つだけ。

• MySQLのTBL毎にymlファイル
• BigQueryのTBL毎にjsonファイル

例

IN

• MySQL
  • Database -> hoge
  • Table -> users

OUT

• BigQuery
  • Dataset -> foo
  • Table -> users

作成ファイル

• users.yml
• users.json

users.yml(mysqlへの接続情報、取得設定、BigQueryへの接続情報、フォーマットを記述）

in:
  type: mysql
  host: HOST_NAME
  user: ********
  password: ********
  database: hoge
  table: users
  select: id,user_name, created, modified
out:
  type: bigquery
  auth_method: json_key
  json_keyfile: # jsonのクレデンシャルファイル
  path_prefix: /tmp # 一時ファイル作成場所
  file_ext: .csv.gz
  source_format: CSV
  project: BigQuery側のProject
  dataset: foo
  auto_create_table: true # 自動でTBL生成
  table: users
  schema_file: users.json
  formatter: {type: csv, charset: UTF-8, delimiter: ',', header_line: false}
  encoders:
  - {type: gzip}

users.json(BigQueryのスキーマ情報を記述）

[
  {
    "name": "id",
    "type": "INTEGER"
  },
  {
    "name": "user_name",
    "type": "STRING"
  },
  {
    "name": "created",
    "type": "TIMESTAMP"
  },
  {
    "name": "modified",
    "type": "TIMESTAMP"
  }
]

実行

embulk run users.yml


input(mysql)とoutput(big query)の設定オプション

outputを見ることが多いとおもう

crondのようなバッチで定期的にTBLを作り変える

GoogleBigQueryは行の削除や更新は想定されていないように見受けられるので、
最新のデータを毎回ロードしてやるのがいいと思う。

GoogleBigQuery標準のコマンドでTBLを毎回削除して、
毎回作り直す。

# TBLを削除(引数はdataset.table名)
bq rm -f foo.users 
# データをロード
embulk run users.yml

これをcrondに仕込んでおく。

参考