こんにちは、エンジニアの大迫です。

Kaizen Platformでは、以前からGoogle BigQueryを利用して、ウェブサイトの行動ログや広告の配信レポートなど様々なデータを保存・活用できるような仕組みを整え、お客様のウェブサイトや広告クリエイティブの改善に取り組んできました。特にここ最近では、非エンジニア向けにBigQueryやSQLの社内勉強会が行われたり、 @ikedayu によりProduction以外のメンバーでも気軽にデータ分析ができる仕組みが作られたりして全社的にBigQueryの利用が広がっています。

その一方で、データを活用できる人が増えた結果として、BigQueryのクエリ料金も増えていく傾向になっています。せっかくエンジニア以外でも分析できる仕組みがあるのに、クエリコストが気になってクエリ書くのが怖くなってしまってはもったいないので、こちらの記事にあるように @ikedayu によってBigQueryコストの可視化をしながら必要のないクエリが定期的に実行されてないか、不必要に多くのデータをスキャンしていないかといった傾向を把握して改善する仕組みを回し始めたりしています。

そんなある日、BigQueryのドキュメントを読んでいたところ、 Clustered Tableというβ機能の存在を知りました。これを活用するとKaizen PlatformのBigQueryの利用料の大きな割合を占めるクエリ料金を削減できる可能性があったので調べてみたところ、この機能を適切に活用すると、クエリにもよるものの、クエリ料金を大きく削減できそうなことが分かったので、調べた結果を簡単にまとめたいと思います。

Clustered Tableを使ってクエリ料金を削減

BigQueryのクエリ料金についての詳細はこちらを参照していただきたいですが、 https://cloud.google.com/bigquery/pricing#queries

クエリ料金はデータ処理容量によって決まります。 Kaizen Platfromでもデータ読み込み量を減らす為に、分割テーブル(Partitioned Tables)を使ったり、クエリで参照カラムを減らす努力をしたりなどは今までもしてきました。

ここでは、最近新しくBigQueryに追加されたClustered Tables(Beta)という機能を使ってクエリのコストを大幅に削減する事ができたので紹介します。 Clustered Tablesについての詳細はこちらを参照してください。 https://cloud.google.com/bigquery/docs/clustered-tables

Kaizen Platformでの具体例

Kaizen Platformで実際に使われているイベント情報を保存している event_log テーブルを例に説明します。 event_log テーブルはウェブサイトの訪問ログやクリックログなどのイベントログが保存されているテーブルで、サイトごとに固有の site_id カラムでどのサイトのイベントか識別できるようになっています。(ちなみに event_log や site_id などは実際のテーブル名やカラム名とは若干異なりますが、説明しやすくするために脚色しています）

適当サンプリングした結果からテーブルを作り、その中の143GBのPartitionを使用して検証しました。

Clustered Tablesを使う前のクエリの例がこちらです。

#StandardSQL
SELECT
    COUNT(DISTINCT url)
FROM
  `kaizen-*****.event_log`
WHERE
  partition_id = TIMESTAMP("2018-08-28")
  AND site_id = '1234567890'

Query complete (2.880 sec elapsed, 23.43 GB processed)

データのスキャン量を減らすために、Partition Tablesによるパーティションの指定と読み込むカラムを制限していますが、このクエリは 2018/08/28のパーティションからsite_id, urlカラムを全て読み込む事になり結果23.43GBのデータが処理されました。

このテーブルを元にsite_idを使ってClustered Tablesを作成し、同様のクエリを実行した結果がこちらです

Query complete (1.288 sec elapsed, 19.76 MB processed)

Clustered Tablesを使う事で、where句のAND site_id = '1234567890'を処理するためにsite_idをフルスキャンする必要が無くなった結果、高速化され、データ処理量も 0.1% 以下になりました。

以上で表題の件については終わりですが、せっかくなのでもう少し試してみた事について書いておきます。

Clustered Tablesで指定したカラムをwhere句でフィルタに使わなかった場合

#StandardSQL
SELECT
    COUNT(DISTINCT (case when site_id = '1234567890' then url else null end))
FROM
  `kaizen-*****.event_log`
WHERE
  partition_id = TIMESTAMP("2018-08-28")

Query complete (3.4s elapsed, 23.4 GB processed)

当然ですが、スキャン量に変化無し

集計関数は最適化されるか

公式docによると

Clustering can improve the performance of certain types of queries such as queries that use filter clauses and queries that aggregate data

と書かれていますが、いくつか集計関数を試して見ましたが、今回利用したデータではスキャン量の変化はありませんでした。実行時間もほとんど変化無し。

一応わかりやすい例を1つ書いておきます

#StandardSQL
SELECT
  min(site_id) # max(site_id) も変化無し
FROM
  `kaizen-*****.event_log`
WHERE
   partition_id = TIMESTAMP("2018-08-28")

この辺りに関してはおそらくClustered Tablesの仕様としての問題ではないと思うので、今後に期待しておきましょう

前方一致フィルタ

前方一致は公式docのsampleとしてもあげられています

#StandardSQL
SELECT
    COUNT(DISTINCT url)
FROM
  `kaizen-*****.event_log`
WHERE
  partition_id = TIMESTAMP("2018-08-28")
  AND site_id like '0%'

Query complete (2.8s elapsed, 960 MB processed)

こちらは1/5程にスキャン量を減らす事ができました。実際のデータを確認すると1/10程には減りそうでしたが、Clustered Tableの処理過程で何かオーバーヘッドがあるのでしょうか。

ソートは？

#StandardSQL
SELECT
  site_id
FROM
  `kaizen-*****.event_log`
WHERE
  partition_id = TIMESTAMP("2018-08-28")
order by site_id
limit 1

変化無し。これも今後に期待で良いでしょうか

joinは？

クエリは省略しますが、いくつか試した感じでは、joinで結合に使うカラムには効果がないようでした。

PLAIDさんによるBigQueryのJOINについての解説 tech.plaid.co.jp

今の所はBigQueryにあるJoinの仕組みだと各テーブルのクエリ結果を配布した後でJOINが行われるためClustered Tableが有効に使えなさそうです。 Joinをする場合はできるだけ事前に絞り込んでからJoinすると良いかもしれません。

複数カラム指定でClustered Table作成した場合

こちらもクエリは省略しますが、今回試したケースでは複数カラムでのフィルタはなぜかスキャン量が若干増えてしまいました。実行時間に変化はなさそうです。

...これはちょっと気の利いたコメントが思いつかないですが、beta版という事で良いでしょうか

実際の運用

Kaizen Platformではまだこれから実運用に乗せていこうかどうかという段階ですが、

いわゆるRDBMSのインデックスのように後からカジュアルにテーブルに追加設定していけるものでは無いので、できればテーブル設計の段階で考慮しておくのがいいと思います。ただし実際の運用では、なかなかそう上手くはいかないと思うので、必要に応じてClustered Tableを追加作成してバッチ処理でパーティション単位でINSERTしていくような運用なども考慮しても良いかもしれません。

最後に

まだまだbeta版だからか動きが怪しいところがありますが、うまく使えば確実にクエリコスト削減を期待する事はできそうです。 BigQueryを使うとどんなクエリでも難なく捌いてしまうため、インデックスやスキャンについて考慮する事が無くなってしまいますが、Clustered Tablesの登場がそういう事について改めて考え直すきっかけになれば良い事だと思いました