--- title: "BigQueryのパフォーマンスを上げる方法 | grasys blog" url: "https://blog.grasys.io/post/higuchi/bigquery-clustered-tables/" description: "今春の新入社員に「えっ、なんでクラスター化してないですか？」 と言われたくない人の為にクラスター化テーブルについて書きます。 2018/08にβ版がリリースされている機能です。 クラスター化テーブルの概要 文章が難解なのでよく読んでませんが、 指定したカラムをクラスター化して保存してくれるから、 WHERE や JO…" --- # BigQueryのパフォーマンスを上げる方法 - ![](/_astro/noicon.CTHOhNiB_1HMs6A.webp)[higuchi](/authors/higuchi/) - 公開日：2019年3月14日 - カテゴリー：[Tech](/categories/tech/) - タグ：[#BigQuery](/tags/bigquery/) ![BigQueryのパフォーマンスを上げる方法](/_astro/og-image.DDSiu2Ny_XHpDd.webp) 今春の新入社員に「えっ、なんでクラスター化してないですか？」 と言われたくない人の為にクラスター化テーブルについて書きます。 2018/08にβ版がリリースされている機能です。 [クラスター化テーブルの概要](https://cloud.google.com/bigquery/docs/clustered-tables) 文章が難解なのでよく読んでませんが、 指定したカラムをクラスター化して保存してくれるから、 WHERE や JOIN でそのカラムを指定するとパフォーマンスが上がり、 料金も安くなるという感じが面で伝わってきます。 決まり事は下記です。 - 1テーブルでクラスター化できるカラムは4つまで。 - 分割テーブルのみ使用が可能。 それでは 分割テーブル(非クラスター) と 分割テーブル(クラスター化) に 同じデータを突っ込んで、比較検証をしてみましょう。 まずは、両テーブルの準備です。 ## 分割テーブル(非クラスター) の作成 公開データセット「Stack Overflow」の Comment テーブルから作成します。 [公開データセット](https://cloud.google.com/bigquery/public-data/stackoverflow?hl=ja) 分割数が多いテーブルなので、2008/01/01〜2009/12/31 の期間を切り出し、 一時テーブルに出力します。 Plain textcontent\_copy ``` 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へエクポートします。 Plain textcontent\_copy ``` bq extract --noprint_header stackoverflow.comments gs://{bucket}/comments.tsv.* ``` Comment のスキーマを取得して、分割テーブルを作成します。 Plain textcontent\_copy ``` 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 ``` 作成した分割テーブルにデータをロードして、完成です。 Plain textcontent\_copy ``` bq load --allow_quoted_newlines stackoverflow.comments gs://{bucket}/comments.tsv.* ``` 作成した Comment テーブル情報です。 Plain textcontent\_copy ``` 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 を指定して、テーブルを作成。 Plain textcontent\_copy ``` bq mk --time_partitioning_type DAY --time_partitioning_field creation_date --clustering_fie ``` 分割テーブル(非クラスター)に load したデータを同じく load します。 Plain textcontent\_copy ``` bq load --allow_quoted_newlines stackoverflow.comments_cluster_userid gs://{bucket}/comments.tsv.* ``` user\_id がクラスター化されているかの確認です。こちらも完成です。 Plain textcontent\_copy ``` 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 　 分割テーブル(非クラスター) Plain textcontent\_copy ``` 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 ``` 分割テーブル(クラスター化) Plain textcontent\_copy ``` 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 句にクラスター化したカラム以外のみを指定した場合は、 課金バイト数が増えたり、実行速度が落ちたりしないかという検証です。 分割テーブル(非クラスター) Plain textcontent\_copy ``` 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 ``` 分割テーブル(クラスター化) Plain textcontent\_copy ``` 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 Plain textcontent\_copy ``` 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 Plain textcontent\_copy ``` 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 Plain textcontent\_copy ``` 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、↑ のねーなと思って書いてみました) ## この記事を書いた人 [![](/_astro/noicon.CTHOhNiB_Z24aAgM.webp)](/authors/higuchi/) ### [higuchi](/authors/higuchi/) higuchiのプロフィールと執筆記事をご覧いただけます。 [プロフィールと記事一覧](/authors/higuchi/)