BigQuery の主キーと外部キーで結合を最適化

※この投稿は米国時間 2023 年 7 月 15 日に、Google Cloud blog に投稿されたものの抄訳です。

BigQuery は、完全にサーバーレスで費用対効果に優れたエンタープライズ データ ウェアハウスです。各種のクラウドで機能し、データに合わせたスケーリングも可能です。ユーザーデータは BigQuery テーブルに保存されます。すべてのテーブルは、列名、データ型、その他の情報を記述するスキーマによって定義されます。

このたび BigQuery に、強制適用されない主キー制約と外部キー制約が導入されました。この投稿では、強制適用されないキー制約と、それが BigQuery のクエリにどのようにメリットをもたらすのかについて詳細に説明します。

制約の定義

CREATE TABLE ステートメントを使用してテーブルを作成する際に、テーブルに制約を定義できます。ALTER TABLE ADD PRIMARY KEY ステートメントまたは ALTER TABLE ADD CONSTRAINT ステートメントを使用して、制約を既存のテーブルに追加することもできます。制約の削除は、ALTER TABLE DROP PRIMARY KEY ステートメントまたは ALTER TABLE DROP CONSTRAINT ステートメントを使用して行えます。

既存のインベントリ テーブルに制約を追加するには、次の ALTER TABLE ステートメントを使用します。

現時点では、BigQuery で強制適用はサポートされていないため、制約を定義する際に NOT ENFORCED 修飾子を使用する必要があります。

テーブル制約は、BigQuery UI の [キー] 列で確認できます。

ツールチップに、キーに関する詳細が表示されます。

別の方法として、情報スキーマビュー TABLE_CONSTRAINTS、KEY_COLUMN_USAGE、CONSTRAINTS_COLUMN_USAGE をクエリすることもできます。

主キーと外部キーを作成する理由

「キー制約は強制適用されないのに、なぜ作成する必要があるのか」という疑問が生じると思います。

その答えは、「クエリ オプティマイザ―がより効果的にクエリを最適化するためにこの情報を使用する可能性があるから」です。

次に、キー制約を利用するクエリ最適化である内部結合解除、外部結合解除、結合順序変更の 3 つについて説明します。

内部結合解除

結合は、データベースで特に費用がかかるオペレーションです。結合を最適化するために試行されているクエリ最適化手法はたくさんありますが、どの手法も、結合を完全に解除することにはかないません。

以下の TPC-DS スキーマのテーブルを見てみましょう。

store_sales テーブルは、データセット内のファクト テーブルの一つです。これは、複合主キー（ss_item_sk、ss_ticket_number）を格納し、次の 9 個のディメンション テーブルを参照します。

• ss_sold_date_sk の参照先: date_dim(d_date_sk)

• ss_sold_time_sk の参照先: time_dim(t_time_sk)

• ss_item_sk の参照先: item(i_item_sk)

• ss_customer_sk の参照先: customer(c_customer_sk)

• ss_cdemo_sk の参照先: customer_demographics(cd_demo_sk)

• ss_hdemo_sk の参照先: household_demographics(hd_demo_sk)

• ss_addr_sk の参照先: customer_address(ca_address_sk)

• ss_store_sk の参照先: store(s_store_sk)

• ss_promo_sk の参照先: promotion(p_promo_sk)

この情報を念頭に置いて、次のクエリを見てみましょう。

このクエリでは、参照関係を形成する列を使用してディメンション テーブルと結合されるファクト テーブルの列のみが選択されます。

制約を使用すると、store_sales 内の各行に対して customer 内の一致する単一行が返され、ss_customer_sk が NULL だった場合は一致なしとなります。クエリプランの作成時に、クエリ オプティマイザーを使用してこの結合を解除できます。

まずは制約を定義せずにクエリを実行して、実行プランを見てみましょう。

ご覧のように、クエリには、2 つのテーブル間の単一結合が含まれます。

次に制約を定義して、クエリをもう一度実行します。プランは次のようになります。

結合は解除されていますが、結果は同じです。これが可能だったのは、キー制約が存在していたからです。

外部結合解除

外部結合解除の要件は、内部結合解除の場合より少ないです。左外部結合を解除するには、右側の結合キーが一意である必要があり、左側の列のみが選択されます。右外部結合を解除するには、左側の結合キーが一意である必要があり、右側の列のみが選択されます。

この場合、キーが一意であることによって、相手側の各行に最大 1 個の一致があることが保証されるので、結合を解除できます。

次のクエリを見てみましょう。

ss_item_sk と c_customer_sk の間には、何の関係性もありません。

次に制約を定義して、同じクエリを実行すると、結合が解除されていることを確認できます。

前述のようなクエリを記述するのはなぜか？ユーザーが自分で結合を解除してはいけないか？

多くの本番環境で、ユーザーはさまざまなファクト テーブルとディメンション テーブルを結合するビューを作成します。

アプリケーションを記述するデベロッパーは、テーブルをクエリし、結合を記述することを何度も繰り返すのではなく、そういったビューをクエリします。ビューにアクセスするクエリごとに異なる列セットが選択されます。制約が定義されていると、オプティマイザーはスキャンするテーブルを、各クエリへの応答に必要なものだけに限定できます。

結合順序変更

結合を解除できない場合、クエリ オプティマイザーはテーブル制約を使用して、結合カーディナリティに関する情報を引き出します。その情報は、クエリ オプティマイザーが結合を順序変更する際に使用することがあります。

次にキー制約を定義して、クエリを再実行すると、[実行の詳細] タブの数値が改善されたことがわかります。

これらの改善は、クエリの結合順序がより適切であったことによります。プラン全体と結合順序は、[実行グラフ] タブで確認できます。

ユーザーの責任

優れた性能には、大きな責任が伴います。BigQuery ではキー制約は強制適用されないため、ユーザーには常に制約を管理する責任があります。

主キー列の値はすべての行で一意でなければならず、NULL 値であってはいけません。

各外部キーは、NULL 値であるか、参照先テーブルの対応する主キー行である必要があります。

これらの制約のいずれかに違反があると、違反している制約のあるテーブルに対するクエリは誤った結果を返すことがあります。

まとめ

まとめると、BigQuery オプティマイザーはキー制約を利用して、より適切なクエリプランを作成できます。そのような最適化は、大きなリソース節約と応答時間の短縮につながります。

こういった最適化からメリットを得るには、テーブルを新規作成する際にキー制約を定義するか、既存テーブルの場合は制約の定義を追加する変更を加えます。

主キー制約は、他のシステムから BigQuery へのデータのストリーミングにも使用されることをご存知ですか。BigQuery の変更データ キャプチャの機能の詳細をぜひご確認ください。