bigqueryのテーブルを分割管理する

February 15, 2020

ログなどを用いてデータ分析をしたいがデータが多すぎて、クエリを叩くと、とてつもない時間とお金がかかる。課金が怖くてデータ分析が進められないので、何か解決策はないか調査してみた。 テーブルを分割して管理 + データをクラスター化 することで時間とお金を節約できるということがわかった。

1. パーティション分割

パーティション分割って、頭痛が痛いのような響きであるが、bigqueryの公式documentでは、パーティション分割テーブルが正式な呼び名の模様。テーブルを分割する方法は以下の４通りある。結論から言うと、documentでもおすすめされている日付/タイムスタンプがベスト。どんな違いがあるのか一応調べてみたので共有。

取り込み時間
日付 / タイムスタンプ
シャーディング
整数の範囲 => ベータ版(2020/02/15時点)

1-1. 取り込み時間

データが取り込まれた時間でテーブルを分割する。 “_PARTITIONTIME”という擬似列に、データが取り込まれたタイムスタンプが格納される。こんな感じでデータを取得。

SELECT
    * 
FROM 
    `table` 
 WHERE 
     _PARTITIONTIME >= TIMESTAMP("2020-02-01")

1-2. 日付/タイムスタンプ

DATEもしくはTIMESTAMP型のカラムのデータを使ってテーブルをパーティショニングする。擬似列は不要。パーティショニングに使用するカラムを条件文で範囲指定てあげるだけで、必要なテーブルのデータだけが利用される。

SELECT
    * 
FROM 
    `table`
 WHERE 
     created >= TIMESTAMP("2020-02-01")

1-3. シャーディング

[テーブル名]_YYYYMMDDという名前のテーブルを作ると、テーブルを日付で分割できる。一番簡単な方法。しかし、データ抽出時、ワイルドカード文字やUNIONなど使わなきゃいけない。こちらはパフォーマンスが悪いらしいので、bigquery的には1-2の日付/タイムスタンプを用いたテーブル分割をオススメしていいる。

SELECT
    * 
FROM 
    `table_*`
 WHERE 
  _TABLE_SUFFIX >= "2020-02-01"

1-4. 整数の範囲パーティション

日付とかではなくてINTEGERのカラムをテーブル分割の際の指標に選べる。

2. クラスタ化テーブル

2-1. 概要

カラムを指定するとそのカラムでソートした状態でデータを提示してくれる。これによって不要なデータのスキャンを省略する。似た値をもつデータが並ぶのでクエリの効率がアップする。
４列まで指定することができる。
パーティション分割済みテーブルでのみ使用可能。
ただし、スロットの消費量が増える傾向がある。デフォは2000スロット。基本は問題ないらしいが、上限を突破する可能性があることは念頭に置いておくべし。

2-2. スロット？

BigQuery スロットとは、SQL クエリの実行に必要な演算能力の単位です。BigQuery はクエリのサイズと複雑さに基づいて、クエリごとに必要なスロットの数を自動的に計算します。

3. まとめ

データを取り込む際に、パーティション分割に利用するカラムやクラスタ化に使用するカラムを指定するだけで、テーブルの分割管理やクラスタ化が実現できる。むちゃむちゃ簡単だし便利。設定しない理由を見つける方が難しいと思った。