AWSのAuroraからGCPのBigQueryにEmbulkを使ってデータを転送する

AWS Aurora GCP BigQuery Embulk

AWSのAuroraからGCPのBigQueryにデータを転送する必要があり、最終的にEmbulkを使うことに落ち着いた話です。

最初は、AWS Data Pipelineを使ってAuroraからS3にエクスポートしてBigQuery Data Transfer Serviceを使ってS3からBigQueryにインポートするフローにする予定でしたが、全体的に少し辛かったので別の方法を検討していました。
そうしたら、Embulkを使った方法があまりにも簡単すぎたので、こちらを採用することにしました。

Embulk

Embulkとはデータ転送のためのツールで、様々なストレージやDBやクラウドサービス間でデータ転送を実現できます。

環境構築

基本的に、公式通りにやりました。GitHub - embulk/embulk: Embulk: Pluggable Bulk Data Loader.

Ubuntu 18.04で動作確認済みです。

Embulkの動作に必要なJava8とEmbulk自身をインストールします。

$ sudo apt install openjdk-8-jdk
$ sudo curl -o /usr/local/bin/embulk -L "https://dl.embulk.org/embulk-latest.jar"
$ sudo chmod 755 /usr/local/bin/embulk

今回はAuroraからBigQueryに転送したいので、それぞれのプラグインを用意します。

$ cd ~
$ embulk gem install embulk-input-mysql embulk-output-bigquery

以上です。

BigQueryの設定

GCP上でEmbulk実行用のサービスアカウントを作成し、そのアカウントに「BigQuery データ編集者」「BigQuery ジョブユーザー」のロールを与えます。
そして、そのサービスアカウントのJSON鍵をダウンロードしておきます。

設定ファイル作成

今回は全更新と差分挿入のいずれも必要なので両方に対応しました。

まず、全体として同じような設定を共通化します。
サンプルとして、本当に必要最低限の設定にしています。

$ cat common/_in_mysql.yml.liquid
in:
  type: mysql
  host: {{ env.DB_HOST }}
  user: {{ env.DB_USER }}
  password: {{ env.DB_PASS }}
  database: {{ env.DB_DATABASE }}
  default_timezone: Asia/Tokyo

$ cat common/_out_bigquery.yml.liquid
out:
  type: bigquery
  auth_method: service_account
  json_keyfile: {{ env.GCP_CREDENTIALS }}
  dataset: {{ env.BIGQUERY_DATASET }}
  default_timezone: Asia/Tokyo

続いて、データ転送対象のテーブルの設定をします。
指定の方法は何通りかありますが、特別なwhere句が必要ない場合は in > select で対象のカラムを抽出するようにして、差分挿入などwhere句が必要な場合は in > query でSQLをそのまま記述しています。

$ cat replace_table.yml.liquid
in:
  {% include 'common/in_mysql' %}
  table: replace_table_name
  select: id,title,created_at
out:
  {% include 'common/out_bigquery' %}
  table: replace_table_name
  mode: replace

$ cat insert_table.yml.liquid
in:
  {% include 'common/in_mysql' %}
  query:
    SELECT
      id,title,created_at
    FROM
      insert_table_name
    WHERE
      DATE_FORMAT(created_at, '%Y-%m-%d') = '{{ env.TARGET_DATE }}'
out:
  {% include 'common/out_bigquery' %}
  table: insert_table_name
  mode: append

実行

まずは設定の際にenvに記述したものを、以下の方法などで環境変数に入れておく必要があります。

$ export DB_HOST=xxxxxxxxxxxxxx.rds.amazonaws.com
$ export DB_USER=db_user
$ export DB_PASS=db_password
$ export DB_DATABASE=database
$ export GCP_CREDENTIALS=/home/user/key/bigquery-key.json
$ export BIGQUERY_DATASET=bigquery_dataset
$ export TARGET_DATE=`date -d yesterday "+%Y-%m-%d"`

そして、実行します。

$ /usr/local/bin/embulk run replace_table.yml.liquid
$ /usr/local/bin/embulk run insert_table.yml.liquid

デフォルトだとBigQuery側にテーブルを用意していなくても自動で作成してくれるので、このように実行するだけで完了です。

簡単すぎてビビりました。