BigQueryのパフォーマンスを上げる方法

今春の新入社員に「えっ、なんでクラスター化してないですか?」
と言われたくない人の為にクラスター化テーブルについて書きます。

2018/08にβ版がリリースされている機能です。

クラスター化テーブルの概要

文章が難解なのでよく読んでませんが、
指定したカラムをクラスター化して保存してくれるから、
WHERE や JOIN でそのカラムを指定するとパフォーマンスが上がり、
料金も安くなるという感じが面で伝わってきます。

決まり事は下記です。

  • 1テーブルでクラスター化できるカラムは4つまで。
  • 分割テーブルのみ使用が可能。

それでは 分割テーブル(非クラスター) と 分割テーブル(クラスター化) に
同じデータを突っ込んで、比較検証をしてみましょう。

まずは、両テーブルの準備です。

分割テーブル(非クラスター) の作成

公開データセット「Stack Overflow」の Comment テーブルから作成します。

公開データセット

分割数が多いテーブルなので、2008/01/01〜2009/12/31 の期間を切り出し、
一時テーブルに出力します。

bq query --destination_table stackoverflow.comments --nouse_legacy_sql "SELECT * FROM \`bigquery-public-data:stackoverflow.comments\` WHERE creation_date >= TIMESTAMP('2008-01-01 00:00:00') AND creation_date < TIMESTAMP('2010-01-01 00:00:00')"

一時テーブルからGCSへエクポートします。

bq extract --noprint_header stackoverflow.comments gs://{bucket}/comments.tsv.*

Comment のスキーマを取得して、分割テーブルを作成します。

bq show --format json bigquery-public-data:stackoverflow.comments | jq .schema.fields > comments.json
bq mk --time_partitioning_type DAY --time_partitioning_field creation_date stackoverflow.comments comments.json

作成した分割テーブルにデータをロードして、完成です。

bq load --allow_quoted_newlines stackoverflow.comments gs://{bucket}/comments.tsv.*

作成した Comment テーブル情報です。

           Schema             Total Rows   Total Bytes   Time Partitioning
---------------------------- ------------ ------------- ----------------------------
- id: integer (required)       1363593      262702272    DAY (field: creation_date)
- text: string
- creation_date: timestamp
- post_id: integer
- user_id: integer
- user_display_name: string
- score: integer
分割テーブル(クラスター化) の作成

クラスター化するカラムは user_id を指定して、テーブルを作成。

bq mk --time_partitioning_type DAY --time_partitioning_field creation_date --clustering_fields user_id stackoverflow.comments_cluster_userid comments.json

分割テーブル(非クラスター)に load したデータを同じく load します。

bq load --allow_quoted_newlines stackoverflow.comments_cluster_userid gs://{bucket}/comments.tsv.*

user_id がクラスター化されているかの確認です。こちらも完成です。

bq show --format json stackoverflow.comments_cluster_userid | jq .clustering.fields
["user_id"]

非クラスター vs クラスター化

比較するテーブルが準備できたので、パフォーマンスとコストの検証をしていきます。

結果で表示される項目は、下記のようにジョブ情報が元になっています。

  • 実行時間(ms): endTime - startTime
  • 課金バイト数: totalBytesBilled

結果は実行30回の平均値です。
creation_date は 2009-10-01 〜 2009-12-03 の 2ヶ月間を指定しています。

CASE1:
WHERE 句に creation_date と user_id を指定してSELECT

 
分割テーブル(非クラスター)

SELECT
  *
FROM
  \`stackoverflow.comments\`
WHERE
  creation_date >= TIMESTAMP('2009-10-01 00:00:00')
  AND creation_date < TIMESTAMP('2010-01-01 00:00:00')
  AND user_id = 91

分割テーブル(クラスター化)

SELECT
  *
FROM
  \`stackoverflow.comments_cluster_userid\`
WHERE
  creation_date >= TIMESTAMP('2009-10-01 00:00:00')
  AND creation_date < TIMESTAMP('2010-01-01 00:00:00')
  AND user_id = 91
CASE1: 結果

非クラスターに対してクラスター化の方が、
課金バイト数が68.53%で、実行速度は4.17倍です。

課金バイト数

非クラスター クラスター 比率(%)
263192576 82837504 31.47

実行時間(ms)

非クラスター クラスター 比率(%)
AVG 4061 973.13 23.96
MAX 5471 2044 37.36
MIN 4182 606 14.49

CASE2:
WHERE 句に creation_date とscore を指定してSELECT

 
CASE2は WHERE 句にクラスター化したカラム以外のみを指定した場合は、
課金バイト数が増えたり、実行速度が落ちたりしないかという検証です。

分割テーブル(非クラスター)

SELECT
  *
FROM
  \`stackoverflow.comments\`
WHERE
  creation_date >= TIMESTAMP('2009-10-01 00:00:00')
  AND creation_date < TIMESTAMP('2010-01-01 00:00:00')
  AND score > 0 

分割テーブル(クラスター化)

SELECT
  *
FROM
  \`stackoverflow.comments_cluster_userid\`
WHERE
  creation_date >= TIMESTAMP('2009-10-01 00:00:00')
  AND creation_date < TIMESTAMP('2010-01-01 00:00:00')
  AND score > 0
CASE2: 結果

非クラスターに対してクラスター化の方が、
課金バイト数が68.53%で、実行速度は1.64倍です。

予想では課金バイト数は同じで、実行速度は殆ど同じと踏んでいたのですが、
課金バイト数がCASE1と同様の差分で、実行速度も上がっています。

課金バイト数

非クラスター クラスター 比率(%)
263192576 82837504 31.47

実行時間(ms)

非クラスター クラスター 比率(%)
AVG 5254.73 3206.37 61.01
MAX 6213 4014 64.6
MIN 4097 2666 65.07

CASE3:
JOIN の結合キーに creation_date と user_id 指定

 
分割テーブル(非クラスター) と 分割テーブル(非クラスター) を JOIN

SELECT
  a.*
FROM
  \`stackoverflow.comments\` a
JOIN
  \`stackoverflow.comments\` b
ON
  a.user_id = b.user_id
WHERE
  (a.creation_date >= TIMESTAMP('2009-10-01 00:00:00')
  AND a.creation_date < TIMESTAMP('2010-01-01 00:00:00'))
  AND (b.creation_date >= TIMESTAMP('2009-10-01 00:00:00')
  AND b.creation_date < TIMESTAMP('2010-01-01 00:00:00'))

分割テーブル(クラスター化) と 分割テーブル(クラスター化) を JOIN

SELECT
  a.*
FROM
  \`stackoverflow.comments_cluster_userid\` a
JOIN
  \`stackoverflow.comments_cluster_userid\` b
ON
  a.user_id = b.user_id
WHERE
  (a.creation_date >= TIMESTAMP('2009-10-01 00:00:00')
  AND a.creation_date < TIMESTAMP('2010-01-01 00:00:00'))
  AND (b.creation_date >= TIMESTAMP('2009-10-01 00:00:00')
  AND b.creation_date < TIMESTAMP('2010-01-01 00:00:00'))

分割テーブル(クラスター化) と 分割テーブル(非クラスター) を JOIN

SELECT
  a.*
FROM
  \`stackoverflow.comments_cluster_userid\` a
JOIN
  \`stackoverflow.comments_2008_2009\` b
ON
  a.user_id = b.user_id
WHERE
  (a.creation_date >= TIMESTAMP('2009-10-01 00:00:00')
  AND a.creation_date < TIMESTAMP('2010-01-01 00:00:00'))
  AND (b.creation_date >= TIMESTAMP('2009-10-01 00:00:00')
  AND b.creation_date < TIMESTAMP('2010-01-01 00:00:00'))

課金バイト数

非クラスター to 非クラスター クラスター to クラスター クラスター to 非クラスター
263192576 82837504 103809024

実行時間(ms)

非クラスター to 非クラスター クラスター to クラスター クラスター to 非クラスター
AVG 51827.8 25877.2 23282.2
MAX 60885 59304 48971
MIN 44022 19467 18779
CASE3: 結果

「非クラスター to 非クラスター」に対して「クラスター to クラスター」は
課金バイト数が68.5%で、実行速度は2倍です。

「非クラスター to 非クラスター」に対して「クラスター to 非クラスター」は
課金が60.6%で、実行速度は2.23倍です。


検証中に得たミニ知識
  • 分割テーブル(非クラスター) から 分割テーブル(クラスター化) にコピー不可
    • 日付パーティションを指定してのコピーはエラーになり、互換性なしです。
      一度、GCSにエクスポートして load する必要があります。
  • クラスター化でも全期間は課金バイト数は同じ。
    • creation_date を指定しない、creation_date に全期間を指定した場合は、
      課金バイト数は同じで、実行速度だけが向上します。(全期間で3.48倍)
  • 空振りが無料になる
    • クラスタ化テーブルに投げたクエリで結果件数が0だった場合に、
      totalBytesBilled が必ず0になります。
      参照した分が課金となるBigQueryの仕組みにおいては、
      ちょっとなに言ってるかわからない感じなので、
      バグで実際は課金されていそうな印象です。
      もしかすると0キロカロリー理論みたいなものかもしれません。

まとめ

クラスター化テーブルを使用する上で不安になるのは、
クラスター化する事で逆に極端に課金バイト数が増えたり、
実行速度が落ちたりするケースがあるのかという点だと思います。

複数のクラスター化カラムを指定した場合にカラムの相性や、
カラムとクエリとの相性で実行速度の平均とバラつきは変化すると思いますが、
課金バイト数においては高くなるという事はないと思われます。

DBのインデックス思考でCASE2の結果を見ると「何で?」と思ったりしますが、
BigQuery においてクエリのパフォーマンスとコストの調整は、
とりあえず打ってみないとわからない部分(クエリプラン見ないと)はあるので、
既にBigQuery を運用されていてパフォーマンスやコストにお悩みの方は、
既存テーブルのスキーマから何種類かクラスター化テーブルを作成して、
数日分のデータを入れて日々のクエリを実行して検証してみるといいと思います。

パフォーマンスとコストに考慮したデータ分析基盤の構築・運用などで
お困りでしたら是非 grasys へご連絡ください。
(うちのBlog、↑ のねーなと思って書いてみました)