唯物是真 @Scaled_Wurm

プログラミング(主にPython2.7)とか機械学習とか

BigQueryで有り金全部溶かさないように、テーブルの日付ごとのパーティション(partition)機能について調べてみた

BigQueryではクエリのたびに対象のテーブルをフルスキャンします
スキャンしたテーブルのサイズによって料金が請求されるので、コストの削減のために日付などの単位でテーブルを分割するのがベストプラクティスとして知られています

qiita.com

テーブルを日付ごとに分割する利点

  • クエリのときのテーブルのサイズが小さくなるので、費用が下がる
  • テーブルを最後に更新してから90日以上たつとストレージ代が安くなる
  • テーブルが分かれているので、特定の日のデータだけ入れ直すのが簡単
  • expireを設定すれば一定期間で自動的に古い方から消えていくようにできる

以前このブログでも紹介しましたが、最近テーブルを日付ごとにパーティションする機能がサポートされました
sucrose.hatenablog.com

パーティション機能の挙動を知りたかったのでいろいろ調べてまとめてみました
以下に書いてあるmkやloadなどのbqコマンドの、テーブルのスキーマ指定は省略してあるので必要だったら足してください

テーブルの作成

パーティション機能を使ったテーブル

通常のテーブルを作成するコマンドに--time_partitioning_type=DAYを加えればよいです
ちなみに現在は日付単位(DAY)でしかパーティションをサポートしてないらしい

bq mk --time_partitioning_type=DAY mydataset.table1

パーティションの保存期間を指定する

パーティションが3日(259200秒)で消えるようなテーブルを作成するコマンド

bq mk --time_partitioning_type=DAY  --time_partitioning_expiration=259200 mydataset.table1

データのload

パーティション無指定でパーティション機能が有効なテーブルにloadする場合の挙動

パーティションを指定せずにloadするとその日のパーティションに入れられる
Webコンソールからデータを保存する場合も同様

bq load mydataset.table1 data.csv

特定のパーティションにloadする

テーブル名の後ろに$日付を指定

bq load 'mydataset.table1$20160713' data.csv

置換する場合の挙動

--replaceをつけて、パーティションを指定せずにloadしたときはすべてのパーティションが消えて新しくloadした当日のパーティションだけになってしまいます(危ない)

bq load --replace mydataset.table1 data.csv

パーティションを指定するとそのパーティションだけが置き換えられます

bq load --replace 'mydataset.table1$20160713' data.csv

クエリ

パーティションの確認

以下のクエリでパーティションの情報の一覧が表示されます
パーティションの作成日時などがわかります

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'))

期間を無指定の時の挙動

すべてのパーティションからデータを取得してきます
誤って全期間のテーブルをフルスキャンしてしまってクエリ代がひどいことになる事故が怖いですね

Long-term storage pricing

90日以上更新されていないテーブル(パーティション機能を使っていれば古いパーティションも)はストレージ代が安くなります

日付ごとに分かれたテーブルからパーティションを使ったテーブルへの移行

以下のようなコマンドでパーティションが使われたテーブルに移行できます

bq partition mydataset.sharded_ mydataset.partitioned

コマンドのヘルプにはコピーすると書いてあり、パーティションの作成日時も新たな日時になっているので、おそらくまた90日以上たたないとLong-term storage pricingの割引は効かなそう
長期間のデータを移行すると急にストレージ代が増えてしまうかも

まとめ

パーティション機能を公式でサポートして便利になりました

今のところ日付別にテーブルを分ける方法よりも劇的に便利になるわけではないので、日付別にテーブルを作成する方法で問題なく動いているのであればすぐに移行するモチベーションはなさそう(コマンド一発でテーブルは移行できる)

日付でテーブルを分けたりパーティション機能を使ったりすることの良い点悪い点

  • 日付で分ける
    • 明示的に書かない限り1日分のテーブルにしかクエリを発行しない
    • テーブルが増えて管理しづらくなる
    • 途中の日付からカラムを追加できる
  • パーティション機能
    • 明示的に書かないと全パーティションにクエリが発行されてしまう
    • テーブルの数が一つで管理しやすい。データをloadするのも単純にテーブル名だけ指定してloadしつづければよい