CA Reward

Tech Blog

Tech Blogトップに戻る

BigQuery パーティショニングを使い倒す

2017.01.25

  • このエントリーをはてなブックマークに追加
  • Pocket
kotaking
kotakingエンジニア

開発部の小高です。

今回はBigQueryのパーティショニングについてです。

BigQueryに対しての操作はAPI、WebUI、コマンド(bq)が用意されていますが、今回はbqコマンドベースでの操作について紹介したいと思います。

メリット

  • スキャンするデータ量が制限される(コスト削減)
  • スキャンデータ量が抑えられる為、SQLが高速になる
  • expireオプションを使用する事で、定期的に過去データの削除が可能

注意点

パーティショニング機能が提供される前は、分割テーブルに対して TABLE_DATA_RANGE で参照テーブルを明示していましたが、パーティショニングの場合、期間指定なしでSQLを発行すると全期間スキャンしてしまうので注意が必要です。

パーティショニング操作

テーブル作成(schema定義なし)

MySQLなどの場合、テーブル作成というと、CREATE TABLE でカラムの定義を同時に行いますが、BigQueryにおけるテーブル作成は、必ずしもカラム定義(schema定義)とセットである必要はありません。以下はschema定義なしで、空のテーブルを作成するコマンドになります。

パーティショニングテーブルの作成は、通常のテーブル作成コマンド(bq mk)に、 --time_partitioning_type オプションを指定します。 2016年12月時点では DAYのパーティショニングのみサポートされています。

mkコマンド発行後、showコマンドを実行すると、schema定義の無いテーブルが作成されたのが確認できます(adhoc.access_logという名前で作成しました)

保存期間10日間でテーブル作成

--time_partitioning_expiration オプションを指定します。今はDAYのみサポートされているので、1日(86400秒)× n日の値を指定します。オプション指定無しの場合は無期限での登録となります。データの追加・更新がない場合、90日以上前のデータについてはストレージ料金が安くなりますが、これはパーティショニング化された各テーブルに対しても有効なようです

テーブル作成(schema定義あり)

今回、schema定義は下記のようにしました。

field type
time timestamp
useragent string

 

コマンド内埋め込み

--schema オプションに続けて カラム名:型 を指定します。複数のカラムを指定する際はカンマで区切って指定します。コマンド内にschema定義を埋め込む場合、modeの指定は出来ずデフォルトのnullableになるようです

 

jsonファイルから読み込み

こちらはmodeの指定も可能になります

schema.json

テーブル作成(schema定義を他テーブルからコピー)

adhoc.access_log_201612 のようなテーブルがあり、このテーブルのレイアウトを流用する場合は、一旦schema定義なしでテーブル作成し、bq queryコマンドで --destination_table オプションを指定します。ポイントとしては、SELECTにLIMIT 0 をつける事で、既存テーブルのレイアウトのみをコピーしています

データ登録

 

ローカルファイル(JSON)をアップロード

1行に1JSONとなるので、末尾にカンマとかつけると怒られます。

data.json

bq load コマンドで登録します。

テーブル名の後に ${yyyymmdd} をつけて登録先のパーティションを指定します。$の前に「\」が付いているのはコマンド上のエスケープ処理です。また、${yyyymmdd}を省略した場合は今日のパーティショニングに格納されるようです。

 

Google Cloud Storageからロード

ローカルファイルをloadするよりこちらの方が高速。特に理由がなければこちらの方がおすすめです。 Google Cloud Storageをコマンドラインから操作するには gsutil コマンドをインストールする必要があります。gsutilインストール後、 コマンドラインからの使用を許可する為に下記認証を行います。

ブラウザから認証用URLにアクセスすると、以下のような画面になるので、ここで Google Cloud SDK のリクエストを許可するようにします。

2016-12-13 18 23 26

認証許可したら、gsutil cp コマンドを使ってアップロードします

あとはJSONファイルをloadした時と同様、ファイルパスをgsのパスに置き換えるだけです。

データ更新

データの更新は出来ません。パーティショニングされたテーブルの場合、日単位で分割されていますので、データの追加・更新などが発生しない当日より前のデータなどであれば、一度リセットして再度作り直すという解決策がありそうです。既に投入済みのデータをリセットして再投入するには --replace オプションを指定します

これで特定の日付のデータのみを再作成する事が可能です。

データ削除

データ削除のコマンドはありませんが、色々と試行錯誤したところ、空のjsonファイルをreplaceオプション付きでloadする事で特定パーティションデータを削除出来ました。

まずは空のjsonファイルを作成します

次に --replace オプション付きで、先ほどの空jsonファイルをloadします

実施前

実施後

SQLオプティマイズ

パーティショニング化すると、_PARTITIONTIME というパーティショニング日付の疑似列ができるので、SQLの条件にこの疑似列を加えるようにします。

 

基本

 

評価式を反転

こちらでもパーティショニング制限は有効でしたが、パフォーマンスに影響が出るようなので、_PARTITIONTIME は左列に書いた方が良いようです

実際に影響があるか見てみましょう。ExplainはSQL実行画面にある「Explaination」から確認する事ができます。

疑似列を左列に指定(5.8s)

疑似列を右列に指定(8.3s)

今回の検証は100万レコードで2.5sの差が出ました。特別な理由がない限り疑似列は左列に記述した方がよさそうです。 各クエリの実行プランの見方は クエリプラン に説明が載っていますので参考にしてみてください。

 

FROM句に パーティション デコレーターを指定

こちらも該当のパーティショニングテーブルのみ参照する為、データサイズに変更はありませんでした。

 

疑似列に関数を使用

読み込まれるデータ量自体は変わらないですが、パフォーマンスに影響があるようです。 公式ドキュメントにも _PARTITIONTIME 列は単独で配置するよう記述されていました。

 

_PARTITIONTIME に近いところで使用

公式ドキュメントにもありますが、以下の例ではパーティショニングが効かずフルスキャンとなってしまうようです

パーティショニングを有効にするには下記のようにサブクエリに閉じ込めるといいようです

尚、これはLegacySQLの場合で、標準SQLで試したところ前者の書き方でもパーティションは有効になっていました。

まとめ

コストパフォーマンスや操作のし易さなどでメリットが多いパーティショニングですが、SQLの書き方によっては _PARTITIONTIME を指定してもフルスキャンとなってしまう場合があるので、事前にオプティマイズをかけて、指定したパーティションテーブルのみを参照しているか確認する事は必須だと思いました。うまく使えばとても重宝する機能なので、是非試してみてください。