MySQLからBigQueryへのデータロード

はじめまして、エンジニアの古堀です。

Aimingではログの分析ツールとしてGoogleのBigQueryを利用しています。
ゲームプレイのログを集計、分析して機能開発、改善の指針として活用しています。
実際に運用に乗せてみるとログだけでは情報が足りず、ユーザー情報やマスターデータなども必要であると気付きました。そこでMySQLのデータをBigQueryに反映させる試みに取り組んだので紹介したいと思います。

BigQueryの特長と言えば以下の2点ですが、実際に使用してみるとGoogleアカウントでの認証や権限設定なども便利だと感じますね。

• クエリーの処理速度が速い(数十億件のテーブルでも数十秒で結果が返ってくる)
• 費用が安い

Embulkの採用

MySQLのデータをBigQueryに反映するツールとして Embulk を利用しています。
以下の理由からEmbulkを採用しましたが、最新技術を使ってみて活用事例を増やしてみたいという個人的欲求もありました。

• Java実装なのでどの環境でも動作する
• 設定ファイルを追加するだけで動かせる
• 社内の他のプロジェクトで動作検証済だった
• 新しいツールだが今後の機能拡張に期待ができそう

事前準備

1. Embulkのインストール
2. 対象テーブル毎にBigQueryのスキーマ定義を作成する
3. 対象テーブル毎にEmbulkの設定ファイルを作成する

処理時間の都合で全テーブルではなく、20テーブルほどに対象を絞っています。
2、3のステップは対象テーブルが増える毎に作業が発生するのが難点です。
あとは設定ファイル毎(テーブル毎)にEmbulkのプロセスを起動しなければならず、テーブル数が増えてくると面倒になってきます。
そこで設定ファイル作成、Embulkの起動を簡単にするためのツールを作成して設定の手間を軽減しています。

実行

実際に動かしてみるとすんなり処理が完了して感動しました！Embulkは導入から実行までの敷居が低くて良いですね。ただ、オーバーヘッドが大きく、テーブルサイズによらず1テーブルあたり処理時間が1分ほどかかりました。レコード件数が2000万件を超えるとIOトラブルが起きやすいですね。

困ったこと、ハマったこと

以下の問題に直面しましたが1はMySQLのテーブル定義からBigQueryのスキーマを作成するツール、2・3はMySQLからデータをロードするSQLの生成ツールを作成して解決しました。

1.スキーマ変換

MySQLのカラムの型定義とBigQueryの型定義が異なるので互換性のある型を指定する必要があります。

2.Boolean型の変換

tinyint(1) で定義されたカラムの値がEmbulkで true や false とし扱われてるのでBigQueryの integer 型のカラムにインサートできません。
対応としては signed に型キャストするSQLを書くか、BigQueryのカラムを boolean にする必要があります。

3.タイムゾーン

BigQueryのタイムゾーンはUTC固定なので、JSTなどの他のタイムゾーンのDBのデータは時刻補正が必要です。