出たとこデータサイエンス

アラサーでデータサイエンティストになったエンジニアが、覚えたことを書きなぐるためのブログ

BigQueryの分割テーブルをちょっとだけ完全に理解する

BigQuery

新しい会社に来て初めてGoogle BigQueryに触っているので、新しく学んだ概念や機能を備忘録として記していきたい。
今日のテーマは分割テーブル。

分割テーブをざっくり要約すると……

  • 分割テーブルは、巨大なテーブルに対するクエリのパフォーマンスを上げる手段
  • 分割テーブルは、見た目上は一つのテーブルだが、実際にはレコードの日付（時間）によって複数の場所に分散して格納されている
  • 分割テーブルを作るには、通常のテーブル作成手順+αでOK
  • 分割テーブルにクエリを投げる際、日付（時間）で絞り込みをかけることでクエリのパフォーマンスが向上する

分割テーブルの概要

分割テーブルを使う目的

BigQueryにクエリを投げる際、意図した集計結果が素早く返ってくることは非常に重要だ。
いくらビッグデータに特化したBigQueryといえども、レコードが数億、数十億と蓄積されていくと、やはりパフォーマンスは悪化していく。
特にアプリの行動ログやサイトの閲覧ログは途轍もないデータ量のことが多いので、愚直に集計するとエディタの前で結果を待つ時間が長くなり、分析にスピード感が出ない。

クエリが遅くなる原因の一つは、テーブルが巨大すぎてスキャンに時間がかかるところにある。
実際に必要なデータが1ヶ月分だとしても、期間の始まりと終わりを特定するためには余計なレコードまで舐める必要があり、スキャンにかかる時間はテーブルのレコード量に依存してしまう。

f:id:mizuwan:20181216162946p:plain

分割テーブルの仕組み

上記の問題を解決（または緩和）する手段の一つが、分割テーブルだ。
分割テーブルは、見かけ上は一つのテーブルとして扱うことができる。
しかし実際には、レコードは日付ごとに別々の場所（これをパーティションという）に格納されている。
こうすることで、WHERE句で日付で絞り込んだ際に、スキャン範囲を特定のパーティションに限定することが可能になり、結果的にクエリが速くなる。
分析では普通「昨日のアプリ起動数」や「先月のコンバージョン数」のように集計期間を限定するので、特定期間のパーティションだけをスキャンするこの仕様は、短気な分析者にとっては非常に恩恵が大きい。

f:id:mizuwan:20181216162918p:plain

分割テーブルの作り方

では早速、分割テーブルを作ってみる。
今回は例として下記のような簡単な構造を持った購買ログを分割テーブルとして作ってみたい。

salesテーブル

カラム名 説明
id STRING 売上一つごとに一意なID
user_id STRING 購買者のID
item_id STRING 商品のID
sales_time TIMESTAMP 購入時刻

分割テーブルを作る際に決めなければならないのは、「何を基準にパーティションを分割するか」だ。
具体的には

  1. レコード挿入日で分割する
  2. TIMESTAMP型またはDATE型のカラムで分割する

という2つの方法から選ぶことになる。
売上がリアルタイムでテーブルに書き込まれる前提ならレコード挿入日で分割すれば良いし、一度バッチを噛ませてから翌日に一気にレコードが挿入される等、ビジネス上意味のある時間とレコード挿入時間にギャップが生じる場合はカラムで分割する形の方が良いだろう。
大事なのは「集計クエリのWHERE句で範囲を絞り込む時に使用する日付を指定する」こと。

Web UIでテーブルを作る際は、次図のように「No Partition」と書かれたドロップダウンリストをクリック。
その上で、レコード挿入日で分割する際は「Partition by ingestion time」を、カラムで分割する場合は「Partition by field」以下のカラム名をクリックすればよい。

f:id:mizuwan:20181216163028p:plain

また分割テーブルを作成する際にオプションで、クエリでWHERE句で日付の絞り込みを行うことを強制することができる。
せっかく分割テーブルを作っても日付の絞り込みをしなければ結局レコードを片っ端から読みに行ってしまうので、分割テーブルの利点を活かすためにもチェックをつけておくと良いだろう
（余談だが、Treasure Dataではtime列での絞り込みをかけないと警告が出る仕様になっていた。）
方法は下記の通り、「Requiring partition filter」チェックをつけるだけ。

f:id:mizuwan:20181216163047p:plain

分割テーブルへのクエリの書き方

分割テーブルにクエリを投げる際に気をつけるべき点は１点だけで、日付による絞り込みを必ず行うことである。
テーブル作成時にレコード挿入日での分割を選択した場合は、_PARTITIONDATEという隠れカラムが生成されているので、このカラムで絞り込みを行う。

select
    id
    , user_id
    , item_id
    , sales_time
from
    sales
where
    _PARTITIONDATE between '2018-01-01' and '2018-01-31' 
;

テーブル作成時にカラムでの分割を選択した場合は、単にそのカラムをWHERE句に書けばよい。

select
    id
    , user_id
    , item_id
    , sales_time
from
    sales
where
    sales_time >= '2018-01-01'
    and sales_time < '2018-02-01'
;

なお、絞り込みに使うカラム（上記でいう_PARITIONDATEやsales_time）をWHERE句に書く場合、比較演算子の左側にはそのカラムを単独で書かなければ分割テーブルの恩恵が得られないので注意。
ここらへんの詳しい仕様は公式ドキュメントを参照のこと。