【アップデート情報】 BigQueryで主キーと外部キーが正式にサポートされるようになりました

Google Cloudのデータエンジニアをしています、はんざわです。
先日のアップデートで主キーと外部キー正式にGAになりました。

BigQuery release notes

とはいえ、大多数の人が想像するRDBの主キーや外部キーと挙動が違ったりする部分もあるので実際の検証も含めながら触っていきたいと思います。

できないこととできること

できないこと

• 主キーと外部キーの制約は強制しない、つまりデフォルトでは重複もNULLも許容される。 （ユーザー側で制約に一致することを確認する必要がある）。

できること

• 結合のパフォーマンス大幅改善

さっそくテーブルを作ってみましょう。

主キーと外部キーの登録方法

例として、以下のようなテーブルで考えてみます。

• color_table

• fruit_table

既存のテーブルに主キーや外部キーを登録する場合はALTER TABLE ADD [PRIMARY / FOREIGN] KEY statementを使います。

ALTER TABLE ADD PRIMARY KEY statement

ALTER TABLE ADD FOREIGN KEY statement

実際にcolor_tableのidに主キーを登録し、fruit_tableのnumberに主キーをcolor_idには外部キーを登録します。

ALTER TABLE test.color_table
ADD PRIMARY KEY(id) NOT ENFORCED;

ALTER TABLE test.fruit_table
ADD PRIMARY KEY(number) NOT ENFORCED,
ADD FOREIGN KEY(color_id) REFERENCES test.color_table(id) NOT ENFORCED;

もちろんCREATE TABLE statementで最初から登録することも可能です。

CREATE TABLE statement

重複データを追加してみる

以下のクエリでcolor_tableに重複データを追加してみます。

INSERT test.color_table
SELECT
  1 AS id,
  'red' AS color

• color_table

追加できてしまいました。
冒頭で説明した通り、主キーと外部キーの制約は強制されていませんのでユーザー側で追加できないように条件を定義する必要があります。

結合のパフォーマンス検証

Google Cloud公式のブログにも同様の検証が行われていたので併せて確認してください。

使用するテーブル

今回の検証ではBigQuery public datasetsのwikipediaを使用します。
wikipediaテーブルではrevision_idがユニークな値となっているのでこのカラムを結合キーに使用します。テーブルのサイズと詳細は以下の通りです。

• テーブルのサイズ

• 詳細

These are unique across all revisions to all pages in a particular language and increase with time. Sorting the revisions to a page by revision_id will yield them in chronological order.

これらは、特定の言語のすべてのページのすべてのリビジョンで一意であり、時間とともに増加します。ページのリビジョンをrevision_idで並べ替えると、時系列で表示されます。（DeepL翻訳）

進め方

3種類のケースで結合した際のパフォーマンスを検証したいと思います。

使用するクエリ

以下のようなクエリでパフォーマンスの検証をします。クエリ実行時にはキャッシュの設定をオフにしています。
結合の前後で時間を測定し、その結果を保存します。この測定を各ケース毎に10回ずつ行いました。

BEGIN

  /* 使用する変数を定義 */
  DECLARE x INT64 DEFAULT 1;
  DECLARE START_TIME, END_TIME TIMESTAMP;

  /* 結果測定用のテーブル作成 */
  CREATE OR REPLACE TABLE `test.result1` (
    num INT64,
    diff FLOAT64
  );

  REPEAT

    /* 測定開始 */
    SET START_TIME = (SELECT CURRENT_TIMESTAMP());

    /* 対象のテーブルを結合する */
    CREATE OR REPLACE TEMP TABLE test1 AS (
      SELECT
        t1.*
      FROM
        test.wikipedia1 AS t1
      LEFT JOIN
        test.wikipedia2 AS t2
      ON
        t1.revision_id = t2.revision_id
    );

    /* 測定終了 */
    SET END_TIME = (SELECT CURRENT_TIMESTAMP());

    /* 検証結果を保存 */
    INSERT `test.result1`
    SELECT
      x AS num,
      TIMESTAMP_DIFF(END_TIME, START_TIME, millisecond) / 1000 as diff;

    SET x = x + 1;
    SELECT x;
    UNTIL x >= 11
  END REPEAT;

END;

結果

結果は以下の表の通りでした。

また、それぞれの最大値、最小値、平均値、標準偏差、スキャン量は以下の通りでした。

この結果からわかること

1. 公式記載の通り、通常のテーブルとキーを登録したテーブルではキーを登録したテーブルの方が処理の速度とスキャン量の両方で優れていた。
2. キーを登録したテーブル同士ではクラスタ化しているテーブルの方が処理速度が速かったが、スキャン量は同じだった。（要確認）

また、以下は実行グラフです。上が通常のテーブル（case1）で下が主キーと外部キーを登録しているテーブル（case3）です。

• 通常のテーブル（case1）

• 主キーと外部キーを登録しているテーブル（case3）

このように主キーと外部キーと適切に登録することで結合部分を簡略化でき、よりクエリを最適化することが可能になります。

まとめ

今回の記事では主キーと外部キーの良さをざっくりと紹介しました。
記事で紹介した通り、制約を強制しないため既存のデータパイプラインにも適用しやすいのではないかと思っています。
使い方次第ではよりクエリの最適化を行えるため、是非試してみてください。