grasys blog

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

higuchi

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

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_fie

分割テーブル(非クラスター)に 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倍です。

課金バイト数

非クラスタークラスター比率(%)
2631925768283750431.47

実行時間(ms)

非クラスタークラスター比率(%)
AVG4061973.1323.96
MAX5471204437.36
MIN418260614.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と同様の差分で、実行速度も上がっています。

課金バイト数

非クラスタークラスター比率(%)
2631925768283750431.47

実行時間(ms)

非クラスタークラスター比率(%)
AVG5254.733206.3761.01
MAX6213401464.6
MIN4097266665.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 非ｸﾗｽﾀｰ
26319257682837504103809024

実行時間(ms)

非ｸﾗｽﾀｰ to 非ｸﾗｽﾀｰｸﾗｽﾀｰ to ｸﾗｽﾀｰｸﾗｽﾀｰ to 非ｸﾗｽﾀｰ
AVG51827.825877.223282.2
MAX608855930448971
MIN440221946718779

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、↑ のねーなと思って書いてみました)

株式会社grasys(グラシス)は、技術が好きで一緒に夢中になれる仲間を募集しています。

grasysは、大規模・高負荷・高集積・高密度なシステムを多く扱っているITインフラの会社です。Google Cloud (GCP)、Amazon Web Services (AWS)、Microsoft Azureの最先端技術を活用してクラウドインフラやデータ分析基盤など、ITシステムの重要な基盤を設計・構築し、改善を続けながら運用しています。

お客様の課題解決をしながら技術を広げたい方、攻めのインフラ技術を習得したい方、とことん技術を追求したい方にとって素晴らしい環境が、grasysにはあります。
お気軽にご連絡ください。


採用情報
 お問い合わせ    

higuchi

＼ 合わせて読みたい ／Related article