BigQueryではクエリのたびに対象のテーブルをフルスキャンします
スキャンしたテーブルのサイズによって料金が請求されるので、コストの削減のために日付などの単位でテーブルを分割するのがベストプラクティスとして知られています
テーブルを日付ごとに分割する利点
- クエリのときのテーブルのサイズが小さくなるので、費用が下がる
- テーブルを最後に更新してから90日以上たつとストレージ代が安くなる
- テーブルが分かれているので、特定の日のデータだけ入れ直すのが簡単
- expireを設定すれば一定期間で自動的に古い方から消えていくようにできる
以前このブログでも紹介しましたが、最近テーブルを日付ごとにパーティションする機能がサポートされました
sucrose.hatenablog.com
パーティション機能の挙動を知りたかったのでいろいろ調べてまとめてみました
以下に書いてあるmkやloadなどのbqコマンドの、テーブルのスキーマ指定は省略してあるので必要だったら足してください
テーブルの作成
データのload
クエリ
パーティションの確認
以下のクエリでパーティションの情報の一覧が表示されます
パーティションの作成日時などがわかります
SELECT * FROM [mydataset.table1$__PARTITIONS_SUMMARY__]
期間の指定
1日分だけクエリに使う場合にはFROM [テーブル名$日付]
でよい
特定の期間の場合には_PARTITIONTIME
カラムに擬似的にパーティションのタイムスタンプが入っているので、これを参照することでクエリに使われるテーブルのデータ量が削減されます
SELECT * FROM [mydataset.table1] WHERE _PARTITIONTIME BETWEEN TIMESTAMP('2016-07-01') AND TIMESTAMP('2016-07-30');
_PARTITIONTIME
と比較する時は_PARTITIONTIME
に関数を適用せずに比較対象に関数を適用したほうがパフォーマンスがよいとのこと
ちなみに日付ごとに分けてテーブルを作っている場合にはTABLE_DATE_RANGE()
関数を使ってテーブルを指定します
FROM TABLE_DATE_RANGE([mydataset.table1], TIMESTAMP('2016-07-01'), TIMESTAMP('2016-07-30'))
期間を無指定の時の挙動
すべてのパーティションからデータを取得してきます
誤って全期間のテーブルをフルスキャンしてしまってクエリ代がひどいことになる事故が怖いですね
日付ごとに分かれたテーブルからパーティションを使ったテーブルへの移行
以下のようなコマンドでパーティションが使われたテーブルに移行できます
bq partition mydataset.sharded_ mydataset.partitioned
コマンドのヘルプにはコピーすると書いてあり、パーティションの作成日時も新たな日時になっているので、おそらくまた90日以上たたないとLong-term storage pricingの割引は効かなそう
長期間のデータを移行すると急にストレージ代が増えてしまうかも