前提
- Ruby on Rails で独自実装したログを扱うことを考える
- 元々はログを Redshift に保存していた
- この記事は Redshift から BigQuery に乗り換えるために調査していることのメモである
データ挿入方法
- 正確性を求めるならバッチインサート
- 即時性を求めるならストリーミングインサート
基本的にバッチインサートで処理し，例えば速報値がほしい場合などは別途ストリーミングインサートを併用する形がよいと思われる．
Google Cloud 社のエンジニア曰く「バッチインサートではデータをいったん GCS に保存してから BigQuery にロードする方法がベストプラクティス」とのこと．しかし，ロードにかかる詳細な処理は共有されていない． GCS 上のどのデータがすでにロードされているのか，まだロードされていないのか，ロードに失敗してリトライ待ちなのか，といったフラグ管理を自前で実装するのは骨が折れたため，できれば GCP の仕組みだけで完結できるようになるとよい． GCS のファイル生成イベントは Cloud Pub/Sub に通知できるため， Pub/Sub と Dataflow で諸々の処理を完結させられるとベストと思われる．
タイムスタンプ関連
Redshift における timestamp without time zone (JST の時刻をベタ書き) は推奨されない．タイムゾーンやオフセットを明示して扱うことが望ましい．
また，すべてのログに「ログが発生した時刻を示す TIMESTAMP 型のデータ」が含まれていることが望ましい．
ログ発生時刻
ログ発生時刻は分割テーブルを使用する上で必要となる． BigQuery では検索対象となったデータの量に対して課金される．課金額を抑えるためには分割テーブルを使用して検索対象のデータを絞り込む必要がある
分割テーブルには「取り込み時間分割テーブル」と「分割テーブル」の 2 種類がある．前者はレコードが BigQuery に取り込まれた日付でパーティショニングされ，後者はレコードに含まれる任意の TIMESTAMP 型データが示す日付でパーティショニングされる．
BigQuery にバッチインサートを行うことを前提とすると，取り込み時間分割テーブルではログが発生した時刻とパーティショニングの日付にズレが生じる可能性がある．一方，分割テーブルではログ発生時刻でパーティショニングされるため，日付のズレが生じることはない (と思われる)．
よって， BigQuery を使う場合は，すべてのログにログ発生時刻を示す値を含める必要がある．
DATETIME or TIMESTAMP
DATETIME は Redshift の timestamp without time zone に等しく，タイムゾーンやオフセットを扱えない． TIMESTAMP はタイムゾーンやオフセットを扱える．
先述の分割テーブルしかり， BigQuery で時刻を扱う場合は TIMESTAMP 型が基本となっているため，ログでは DATETIME 型ではなく TIMESTAMP 型でタイムゾーンやオフセットを明示して時刻を扱うことが推奨される．
- タイムゾーンを明示:
2018-01-01 00:00:00.00 Asia/Tokyo - オフセットを明示:
2018-01-01 00:00:00.00+09:00
なお， BigQuery 内部では TIMESTAMP 型の値は UTC として格納される．
分割テーブルの挙動
- 分割テーブルを TIMESTAMP 型のカラムでパーティショニングする
- TIMESTAMP 型のカラムに対してオフセットを明示した値を使用する
以上の前提のもとで分割テーブルの挙動を示す．
まず，ログの時刻が BigQuery にどのように格納されるかを下表に示す．先述の通り， TIMESTAMP 型の値は BigQuery では UTC として格納される．この UTC の値に基づき，各レコードは UTC の日付の 0 時のパーティションに格納される．時差の関係により JST の 9 時を境に格納先のパーティションが切り替わることになる．
| ログの値 | BigQuery の値 | BigQuery のパーティション |
|---|---|---|
| 2018-01-01 00:00:00+09:00 | 2017-12-31 15:00:00 UTC | 2017-12-31 00:00:00 UTC |
| 2018-01-01 10:00:00+09:00 | 2018-01-01 01:00:00 UTC | 2018-01-01 00:00:00 UTC |
| 2018-01-02 06:00:00+09:00 | 2018-01-01 21:00:00 UTC | 2018-01-01 00:00:00 UTC |
次に，パーティションを指定して BigQuery を検索したときの挙動を示す． TIMESTAMP 型のカラムを partition_time として WHERE partition_time > '2018-01-02 00:00:00+09:00' として検索した結果を下表に示す．クエリに指定した時刻は BigQuery 側で UTC に変換されるため，まず検索対象は 2018-01-01 00:00:00 UTC 以降のパーティションに絞り込まれる．その上で，検索対象のパーティション内で条件に合致したレコードが検索結果として返される．
| Rails の値 | BigQuery の値 | BigQuery のパーティション | 検索対象に含まれるか | 検索結果に含まれるか |
|---|---|---|---|---|
| 2018-01-01 00:00:00+09:00 | 2017-12-31 15:00:00 UTC | 2017-12-31 00:00:00 UTC | 含まれない | 含まれない |
| 2018-01-01 10:00:00+09:00 | 2018-01-01 01:00:00 UTC | 2018-01-01 00:00:00 UTC | 含まれる | 含まれない |
| 2018-01-02 06:00:00+09:00 | 2018-01-01 21:00:00 UTC | 2018-01-01 00:00:00 UTC | 含まれる | 含まれる |
時差の問題は BigQuery が吸収するため，ログ出力側では TIMESTAMP 型のカラムに対してオフセットを明示した値を使用することを遵守すれば問題は起こらないと思われる．
クエリのレスポンス
Rails (Ruby) からデータを取得する際に pg を使用すると Redshift のレスポンスは文字列となっている．一方で BigQuery の場合は google/cloud/bigquery を使用すると BigQuery 側の型のオブジェクトが返ってくる．例えば BigQuery から TIMESTAMP 型のデータを取得した際のレスポンスは Time オブジェクトとなる．
データ型
標準 SQL データ型 | BigQuery | Google Cloud
Redshift で扱えなかった型を BigQuery で扱える場合があるため，乗り換え前に適した型を検討しておきたい．