BigQueryを解析基盤として利用できるか検証してみた!

こんにちは。みんなのウェディングのエンジニアkazumalabです。この記事はくふうカンパニーアドベントカレンダーの1日目になります。

はじめに

くふうカンパニーとは…

2018年10月にウェディング事業の「みんなのウェディング」と不動産事業の「オウチーノ」の共同持株会社として設立されました。

「くふうで生活を賢く・楽しく」を理念としてサービスを作っています。

今回の検証内容

GoogleAnalyticsの有料版(GA360)の機能としてBigQueryへのアクセスログ書き出しが利用できます。 そこでGA360を導入も検討していたため解析基盤をBigQueryに置き換えるか検証する必要がありました。

現在の解析基盤

現在、みんなのウェディングではデータウェアハウスとして、AWSのRedShiftを利用しています。サービスのDBはAWSのAuroraを利用しています。

  • AuroraからRedshiftへのデータ転送
  • 収集したデータはbricolageを利用してデータの加工
  • 加工したデータをアプリケーション側で使いたい場合はEmbulkでデータを転送する

もし、解析基盤として置き換えるならば上記3つを満たす必要がありました。 この記事ではそれぞれに対して、解決した方法を記述していきます。 以下の図は現在のデータ解析基盤です。

定期実行はKuroko2が担っています。

AuroraからRedshiftへのデータ転送

AuroraからRedshiftへはデータの転送としてEmbulkを利用していたので、そちらの基盤と同じくEmbulkを利用し、embulk-output-bigqueryをアウトプットプラグインとして利用しました。

BigQueryには

INSERT ステートメント実行数の 1 日あたりテーブルあたり最大値 - 1,000

という制限がありますが、今回ここでは触れません。 数百GBのデータをMySQLからBigQueryへ同期するが参考になりそうです。

データの加工

BigQueryに持ってきたデータを加工する必要がありました。 今回はDigdagにbqコマンドが使えるのでそちらを利用することにしました。

Digdagにgcp.credentialを設定する

redshiftのデータをbricolageで加工する時にDigdagを利用していたので同じくDigdagが動く環境のDockerを用意します。(今回はローカル上に起動したDockerで動かします)

bqを実行するためには認証をする必要があるため、公式にもあるように、

digdag secrets --local --set gcp.credential=@json.key

を実行する必要があります。 しかし、Dockerにsecret.jsonを含みたくないので、環境変数を利用して、Dockerを起動するタイミングでsecret.jsonを作る、Rubyのスクリプトを書くことにしました。

require 'json'

File.open("json.key", "w") do |file|
  hash = {
    private_key_id: ENV['GCP_PRIVATE_KEY_ID'],
    private_key:    ENV['GCP_PRIVATE_KEY'].gsub("\\n", "\n"),
    client_email:   ENV['GCP_CLIENT_EMAIL'],
    client_id:      ENV['GCP_CLIENT_ID'],
    project_id:     ENV['GCP_PROJECT'],
    type: ENV['GCP_TYPE']
  }
  JSON.dump(hash, file)
end

private_keyでgsubを使って置き換えをしているのはENVで読み込んだ際にエスケープされてしまうので、認証が通らないという問題に当たったので、こうなりました。

これをDockerのEntryPointで呼ぶようにし、認証情報をセットします。

if [ ! -e bigquery-dwh/json.key ]; then
  ruby bigquery-dwh/bin/create_credential.rb
  chmod 755 bigquery-dwh/json.key
fi
cat bigquery-dwh/json.key | digdag secrets --local --set gcp.credential=@json.key

上記スクリプトなどを含めてこのようなフォルダ構成にしました。

  • bigquery-dwh
    • bin
      • entry_point.sh
      • create_credential.rb
    • sql
      • daily_job.dig
      • sample.sql
    • json.key

Digdagで呼ぶ

次にデータを加工するSQLを書いて、それをdaily_job.digの中で呼びます。

_export:
  bq:
    dataset: sample_dataset
+step1:
  _retry: 3
  bq>: bigquery-dwh/sql/sample.sql
  destination_table: sample
  write_disposition: WRITE_TRUNCATE

データ量が多く制限にかかってしまうので$YYYYMMDDをつけてテーブルを分割するのが良さそうです。参考

弊社ではJob管理ツールとしてKuroko2を利用しているため、このdaily_job.digを呼ぶことで定期的に実行する環境を作ることができました。

<余談>BigQueryのdashboardで定期実行できるジョブを発行できる

旧ダッシュボードの方でSchedule Queryがあり、定期的にSQLを実行して、新しいデータを作ってくれる機能があります。こちらは新ダッシュボードにないのでそのまま消えるのかわからないですが、軽く使う分には良さそうな気がします。

Destination tableに実行結果のデータを入れる先を指定できます。

BigQueryからAurora に転送する

ランキングや、レコメンドなどのアプリケーションで利用するデータは作った後にアプリケーション側に持ってくる必要がありました。 そこで、同じくEmbulkでデータを取得しよう…と思ったのですが、

embulk-input-bigqueryを利用するとembulk-output-bigqueryで使っているgoogle-api-clientとのバージョンが違ったのでやめました。

今回はEmbulkにembulk-input-gcsがあったので、GoogleCloudStorage(GCS)にデータを書き出し、そちらをAuroraに転送することにしました。

GoogleCloudStorageにデータを書き出すのはDigdagでできるので、先ほど作ったdaily_job.digに1つ追加します。

_export:
  bq:
    dataset: sample_dataset
+step1:
  _retry: 3
  bq>: /opt/bigquery-dwh/sql/favorite_places.sql
  destination_table: favorite_places
  write_disposition: WRITE_TRUNCATE
+export:
  bq_extract>: sample_dataset.sample
  destination: gs://my_bucket/sample.csv.gz
  destination_format: CSV
  compression: GZIP

追加したのはexportです。これによりBigQueryのテーブルをCSVにし、GZIPでGoogleCloudStorageに保存することができました。bq_extractでは先ほどデータマートとして作ったテーブル名を指定します。

embulkは以下のようなconfigにしました。

in:
  type: gcs
  auth_method: json_key
  path_prefix: sample.csv.gz
  json_keyfile:
    content: |
      {
        "type": "service_account",
        "private_key_id": "{{ env.GCP_PRIVATE_KEY_ID }}",
        "private_key": "{{ env.GCP_PRIVATE_KEY }}",
        "client_email": "{{ env.GCP_CLIENT_EMAIL }}",
        "client_id": "{{ env.GCP_CLIENT_ID }}"
      }
  decoders:
  - {type: gzip}
  bucket: my_bucket
  parser:
    charset: UTF-8
    newline: CRLF
    type: csv
    delimiter: ','
    quote: '"'
    escape: '"'
    null_string: 'NULL'
    columns:
    - {name: sample_id, type: long}
    - {name: created_at, type: timestamp, format: '%Y-%m-%d %H:%M:%S'}
out:
  type: mysql
  host: {{ env.DB_HOST }}
  user: {{ env.DB_USER }}
  password: {{ env.DB_PASSWORD }}
  database: {{ env.DB_DATABASE }}
  table: sample
  mode: truncate_insert
  default_timezone: "Asia/Tokyo"
  options:
    characterEncoding: MS932

これでデータをAuroraに取り込むことができました。

最終的にできた構成図は以下のようになります。

最後に

  • Google Analyticsとの相性がいいので有料版のGAと組み合わせると良さそう(BigQueryへの書き出し)
  • 分析に関しては毎月1TBまでは無料で使える
  • ちょっと複雑なSQLでも早い

確かにこの辺りがいいとネット上で聞いていた通りでした。 あと驚きだったのがBIツールのDataStudioが使いやすくて魅力的ですね。 ぜひ導入のチャンスがあれば導入してみたいですね。