株式会社G-gen の技術ブログ

全764件

2021/12/07

G-genの杉村です。Google Cloud（旧称 GCP）のフルマネージドのデータウェアハウスである BigQuery には、パフォーマンス向上やコスト削減に当たり、パーティションとクラスタリングという重要な概念があります。それぞれの仕組みや使い分けを解説していきます。パフォーマンスのためのテーブル設計パーティションパーティションとは使用方法パーティションフィルタ要件メリットパーティションの分割基準時間の列取り込み時間整数範囲の列パーティションの管理パーティションの上限と注意点クラスタリングクラスタリングとは使用方法クラスタ化に指定する列自動再クラスタリングパーティション vs クラスタリングパーティションとクラスタリングの違いパーティションとクラスタリングの使い分けと併用パーティション・クラスターのレコメンデーション参考情報パフォーマンスのためのテーブル設計 BigQuery において、最適なパフォーマンスを出すためのテーブル設計として、最も重要なのがパーティショニングとクラスタリングです。一般的な RDBMS（リレーショナルデータベースマネジメントシステム）では、テーブルに対してインデックスを作成することで、検索パフォーマンスを向上させます。BigQuery には検索インデックス（search index）機能があるものの、これは主に特定の文字列を特定のフィールドから高速に検索するために使用する機能であり、基本的には分析や可視化のパフォーマンス向上に寄与するものではありません。 blog.g-gen.co.jp 検索インデックスは、システムログの検索、セキュリティ監査などの文字列検索のパフォーマンスを向上させるために使います。一方で、当記事で紹介するパーティショニングやクラスタリングは、スキャン効率や速度、コストパフォーマンスを向上させるために有用です。そのため多くの機会で、パーティショニングやクラスタリングは、BigQuery のテーブル設計における基本的な考えであるといえます。パーティションパーティションとはパーティションとは、 BigQuery の一つのテーブルを、特定の列の値を基準にして内部的に複数の部位に分割する機能です。これによりクエリ時にスキャンする範囲を狭め、パフォーマンス向上とスキャン料金の節約ができます。参考 : パーティション分割テーブルの概要分割基準として使う列をテーブル作成時に指定することで、パーティション分割されたテーブルを作成することができます。1つのテーブルには、パーティション列は1つしか指定できません。パーティションで分割されたテーブル使用方法テーブル作成方法は以下のドキュメントの通りです。参考 : パーティション分割テーブルの作成例として、以下のような DDL で、パーティション分割されたテーブルを作成することができます。 CREATE TABLE mydataset.purchase_tran ( purchase_dt DATE , prod_id STRING, prod_name STRING, store_id INT64, store_name STRING ) PARTITION BY purchase_dt このように作成されたテーブルで以下のようにクエリを実行すると、 BigQuery は当該の値を含んだパーティションだけをスキャンします。 SELECT * FROM mydataset.purchase_tran WHERE purchase_dt = " 2025-04-01 " パーティションフィルタ要件パーティション分割テーブルの作成時に、パーティションフィルタ要件（Partition filter requirements）を有効化することで、WHERE 句でパーティション列が指定されていないクエリを、エラーとして拒否することができます。これを設定することで、テーブルの利用者は、パーティションによるスキャン範囲を指定したクエリしか投げられなくなりますので、テーブルに対する不用意なフルスキャンを予防することができます。メリットパーティションが無い場合、BigQuery はテーブル全体をフルスキャンします。パーティションによる範囲スキャンは、フルスキャンに比べて大幅にスキャン範囲を節約でき、料金と時間の節約となります。また前述のパーティションフィルタ要件を使えば、ユーザーが大規模なテーブル全体に対して誤ってクエリを実行する等の、費用の急増を防ぐ効果もあります。パーティションの分割基準時間の列 TIMESTAMP 型、 DATE 型、 DATETIME 型のいずれかの列をパーティション列として指定可能です。 TIMESTAMP 列と DATETIME 列では、パーティションを時間単位、日単位、月単位、年単位のいずれかで作成できます。 DATE 列の場合、パーティションは日単位、月単位、年単位で作成できます。いずれも、分割単位を指定しない場合、デフォルトは日単位となります。以下は、DDL の例です。DATE 型の列をパーティション列に指定すると、デフォルトでは日単位での分割になりますが、以下の例のように DATE_TRUNC 関数を使って月単位で切り捨てることで、月単位の分割になります。 CREATE TABLE mydataset.newtable ( transaction_id INT64, transaction_date DATE ) PARTITION BY DATE_TRUNC(transaction_date, MONTH) OPTIONS ( require_partition_filter = TRUE ); 取り込み時間取り込み時間をパーティション基準として選択すると、BigQuery がデータを取り込んだタイムスタンプに基づいてテーブルが分割されます。分割粒度は、時間単位、日単位、月単位、年単位から選択できます。デフォルトは日単位です。テーブル作成時には、 _PARTITIONTIME という疑似列（仮想列）をパーティション列として指定します。以下は、DDL の例です。 CREATE TABLE mydataset.newtable ( transaction_id INT64 ) PARTITION BY _PARTITIONDATE 整数範囲の列パーティション分割の基準列として、INTEGER 型の列を指定可能です。またこの場合、分割の開始値・終了値と分割の間隔を指定できます。以下は、DDL の例です。 CREATE TABLE mydataset.newtable ( customer_id INT64, date1 DATE ) PARTITION BY RANGE_BUCKET( customer_id, GENERATE_ARRAY( 0 , 100 , 10 ) ); この例では customer_id 列でパーティショニングし、開始値 0、終了値 100、間隔 10 としています。このように設定した場合、customer_id が 0 から 9 の行が最初のパーティションに入り、10 から 19 が次のパーティションに入ります。この処理が 99 まで続きます。この範囲外の値は、 __UNPARTITIONED__ という名前のパーティションに入ります。customer_id が NULL の行は、 __NULL__ という名前のパーティションに入ります。テーブルにどんなパーティションが存在しているかは、テーブルのメタデータから確認できます。参考 : パーティション分割テーブルの管理 - パーティションメタデータの取得パーティションの管理時間単位または取り込み時間で分割したテーブルの場合、パーティションの有効期限を設定できます。指定した有効期限が過ぎたらデータは自動的に削除されます。このとき BigQuery のユーザーに割り当てれたリソースは消費されません。有効期限をうまく使うことで、ハウスキーピング用のジョブをユーザーが作成する必要がなくなります。参考 : パーティション分割テーブルの管理 - パーティションの有効期限を設定するデフォルトのパーティション有効期限をデータセットで設定できるほか、テーブル単位で有効期限を設定することもできます。テーブルで有効期限が設定されている場合は、テーブルの有効期限が優先されます。パーティションの有効期限はテーブル作成時に指定するほか、作成後にも変更できます。パーティションの上限と注意点 1テーブルが持てるパーティション数には上限があり、 1テーブルにつき10,000パーティションまでです。従来は4,000が最大値でしたが、2024年5月29日のアップデートで10,000に変更されました。これは、時間単位であれば 10,000 時間 = 約416日 = 約13ヶ月間であり、日単位での分割であれば 10,000日 = 約322ヶ月 = 約27年です。パーティション数の上限に達すると、ジョブがエラーとなります。パーティションのデータのバックアップを取得する仕組みを用意したうえで、パーティションに有効期限を設けてデータが自動削除されるようにする等、運用上の考慮を検討する必要があります。また「1 つのジョブで変更されるパーティションの数」や「1 日の取り込み時間パーティション分割テーブルあたりのパーティションの変更回数」「1 日の列パーティション分割テーブルあたりのパーティション変更数」などにも上限があります。バッチ処理がこれに抵触していないかは、十分注意する必要があります。参考 : 割り当てと上限 - パーティション分割テーブル以下は、10,001 個目のパーティションを追加しようとした場合のエラーメッセージです。 Resources exceeded during query execution: Table my-project:my_dataset.my_table will have 10001 partitions when the job finishes, exceeding limit 10000. If partitions were recently expired, it may take some time to be reflected unless explicitly deleted. パーティション上限を超えた際のエラーメッセージまた、1回のジョブで変更可能なパーティション数は4,000です。これを超えるようなクエリを発行した場合、以下のようなメッセージが表示されます。 Too many partitions produced by query, allowed 4000, query produces at least 10000 partitions クラスタリングクラスタリングとはクラスタリングとは、 BigQuery のテーブルの特定の列の値に基づいてテーブルのデータをソートし、内部的に近い位置に配置しすることで、フィルタや集計クエリを高速化する機能です。テーブル作成時に、列をクラスタ化列として指定します。クラスタリングを利用すると、指定した列の値に基づいて行がソートされるため、 WHERE 句でこの列に基づいてフィルタするクエリを投げた際、不要なデータのスキャンをスキップすることができます。また、クラスタ化した列で GROUP BY して集計するクエリの場合、行がソートされ近い位置に配置されているので、パフォーマンスが向上します。参考 : クラスタ化テーブルの概要クラスタはパーティションと併用することも可能です。クラスタリングとパーティショニングを併用すると、データはパーティション分割された後に、クラスタ化されます。またクラスタ化列は、1つのテーブルで複数（最大 4 列まで）指定可能です。複数指定した場合、指定の順番が重要になります。まず最初に指定した列で行がソートされ、次にその中で2番めに指定した列でソート、次に3番目... というように、順番にソートされます。クラスタ化されたテーブル使用方法クラスタリングされたテーブルを作成する方法は、以下のドキュメントのとおりです。参考 : クラスタ化テーブルの作成と使用例として以下のような DDL で、クラスタリングされたテーブルを作成できます。例では、パーティショニングも併用しています。 CREATE TABLE mydataset.purchase_tran_cls ( purchase_dt DATE , prod_id STRING, prod_name STRING, store_id INT64, store_name STRING ) PARTITION BY purchase_dt CLUSTER BY prod_id また、既存のテーブルをクラスタリングしたり、列の指定を変更することも可能です。参考 : クラスタ化テーブルの作成と使用 - クラスタリング仕様を変更するクラスタ化に指定する列クラスタ化に指定する列は、一意の値を多く含む（カーディナリティの高い）列が推奨されます。そのほうが、ソートによるスキャン範囲のスキップの効果が高く期待されるためです。また、組み合わせて使われることの多い複数の列をクラスタ化すると効果が期待できます。先の記述の通り、順番に注意して、頻繁に WHERE で指定あるいは GROUP BY される複数列をクラスタ化列として指定すると、効果が大きくなります。参考 : BigQuery 特集: ストレージの概要参考 : BigQuery のクラスタリングでメンテナンスの手間を省いてクエリを高速化自動再クラスタリングクラスタリングのメンテナンスは自動で行われます。データが新規で追加されたり、変更されたりした場合でも、自動で再クラスタリングが行われます。一般的なデータベース製品で必要とされる VACUUM といった処理は不要です。再クラスタリングは、スロットなどのリソースが消費されることもなく、自動的かつ透過的に行われるため、ユーザーが意識する必要はありません。パーティション vs クラスタリングパーティションとクラスタリングの違いパーティションとクラスタリングは併用できますが、どのようなケースでどちらを使えばいいのか、またどのような列を指定すればいいのか、使い分けに迷うときもあります。パーティションとクラスタリングの違いは、以下のような点にあります。パーティショニングでは実際のクエリ実行前にドライランでスキャン量の試算ができる（料金試算が可能）。一方のクラスタリングでは、試算はテーブル単位、パーティション単位で行われるためドライランに反映されず、実際のスキャン量は見積もりより小さくなる可能性がある参考 : クエリの実行 - ドライランパーティショニングでは有効期限の設定ができるパーティショニングでは分割粒度（時間・日・月・年・整数範囲）の選択ができるパーティショニングでは1つの列しか指定できない。クラスタリングでは4列まで指定できるパーティショニングでは特定の型の列しか指定できないが、クラスタリングには型の制限はないパーティションとクラスタリングの使い分けと併用まずはパーティションを適用できる列があるかどうかを検討します。以下のような場合、パーティションの利用を検討します。日付または時間の型の列があり、それらの列でフィルタするクエリがあるパーティションの有効期限設定を使ってテーブルのメンテナンスをしたいドライランでスキャン量（費用）の見積もりを行いたい 1個のパーティションあたりのデータ量がおよそ 10 GB 以上になる見込み（それ未満の場合はオーバーヘッドにより逆に非効率になる可能性があるためクラスタリングの使用を検討する）上記の観点でパーティショニングを適用する列を検討をした後、以下のようにクラスタリングを適用する列を検討します。よくフィルタ対象になる列だが、パーティショニングは既に別の列に適用しているよくフィルタ対象になる列だが、データサイズが 10 GB 未満になる見込みよくフィルタ対象になる列であり、値のカーディナリティが大きい（クラスタ化により速度改善の可能性あり）よくフィルタ対象になる列だが、パーティションを使うと分割粒度が小さくなりすぎ、1テーブルの上限である 10,000 パーティションを超えてしまうテーブル内の大部分のパーティションが頻繁に（たとえば、数分ごとに）変更されるオペレーションがある。この場合、パーティションは避けてクラスタリングを利用する。1日あたりのパーティション変更数の上限があるため結合に使われている列。クラスタ化によって結合が高速化する可能性がある（データが同じカラムナファイルに記録されスロット間のデータ移動が押さえられる）ただし 64 MB 未満のテーブルやパーティションではクラスタ化のメリットは小さいサイズがある程度大きいテーブルの場合は上記のように検討し、パーティションとクラスタリングを併用することで、スキャン料を節減してパフォーマンスとコスト効率を向上させられる可能性があります。以下の公式ドキュメントも参照してください。参考 : パーティション分割テーブルの概要 - クラスタ化テーブルとパーティション分割テーブルを組み合わせる参考 : クラスタ化テーブルの概要 - クラスタリングを使用する場合参考 : クラスタ化テーブルの概要 - クラスタ化テーブルとパーティション分割テーブルを組み合わせるパーティション・クラスターのレコメンデーション BigQuery には、過去のワークロードに基づいてテーブルの適切なパーティショニングやクラスタリングを推奨する機能があります。 Recommender API が過去30日間の実績を機械学習で分析し、テーブルの適切なパーティショニング・クラスタリング設定を提示します。対象テーブル、対象列、またどのくらいのスロット時間が節約できるかの見込みが表示されます。推奨の対象となるテーブルは「パーティショニング無し・クラスタリング無し」「パーティショニング有り・クラスタリング無し」のテーブルです。一方で 10 GB 以下のテーブルや既にパーティションとクラスターを両方設定済みのテーブル、また過去30日以内に読み取りされていないテーブルなどは対象外となります。推奨はコンソール、gcloud、REST API で確認可能です。コンソールでは、画面右上の電球マークから確認できます。参考 : パーティションとクラスタの推奨事項を管理する参考情報以下の公式記事では、パーティションやクラスタリングの仕組みが詳細に解説されていますので、是非参考にしてください。参考 : BigQuery 特集: ストレージの概要参考 : BigQuery のクラスタリングでメンテナンスの手間を省いてクエリを高速化杉村勇馬 (記事一覧) 執行役員 CTO 元警察官という経歴を持つ IT エンジニア。クラウド管理・運用やネットワークに知見。AWS 認定資格および Google Cloud 認定資格はすべて取得。X（旧 Twitter）では Google Cloud や Google Workspace のアップデート情報をつぶやいています。 Follow @y_sugi_it

株式会社G-gen の技術ブログ

コンテンツ