Zenn
🔑

BigQuery クエリ性能改善: Primary key をドキュメントに記載されている型以外の列に設定可能かどうか

2023/09/29に公開
Google Cloud
BigQuery
tech

本記事は、BigQuery の主キーと外部キーにより、クエリの性能改善が得られるかどうか検証してみたの検証の一部になります。

公式ドキュメント TABLE_CONSTRAINTS ビューには、以下のような記載があります。

主キーと外部キーの値の制約は適用されません。ユーザーは、各制約に値が一致することを確認する必要があります。そうしないと、誤った結果が生じる可能性があります。具体的な内容は以下のとおりです。

  • (中略)
  • 主キーと外部キーは、BIGNUMERIC、BOOLEAN、DATE、DATETIME、INT64、NUMERIC、STRING、TIMESTAMP のいずれかのタイプにする必要があります。

上記以外の下記データ型に対して、Primary key を設定し、正しい結果になるのか・PK / FK の効果を受けることができるのかを確認します。

  • BYTES
  • FLOAT64
  • GEOGRAPHY
  • INTERVAL
  • JSON
  • TIME

1. Primary key を BYTES 型カラムに設定

データセット・テーブルの準備

CREATE SCHEMA IF NOT EXISTS pk_fk_dataset;

CREATE OR REPLACE TABLE pk_fk_dataset.pk1
(
    id BYTES NOT NULL,
    PRIMARY KEY(id) NOT ENFORCED
) AS (
     SELECT
        b'test' AS id,
);

以下エラーが発生し、BYTES 型のカラムに Primary key を設定できませんでした。

Invalid value: Primary key column cannot be of type BYTES. Invalid column: id. at [3:1]

2. Primary key を FLOAT64 型カラムに設定

データセット・テーブルの準備

CREATE SCHEMA IF NOT EXISTS pk_fk_dataset;

CREATE OR REPLACE TABLE pk_fk_dataset.pk1
(
    id FLOAT64 NOT NULL,
    PRIMARY KEY(id) NOT ENFORCED
) AS (
     SELECT
        0.1 AS id,
);

以下エラーが発生し、FLOAT64 型のカラムに Primary key を設定できませんでした。

Invalid value: Primary key column cannot be of type FLOAT. Invalid column: id. at [3:1]

3. Primary key を GEOGRAPHY 型カラムに設定

データセット・テーブルの準備

CREATE SCHEMA IF NOT EXISTS pk_fk_dataset;

CREATE OR REPLACE TABLE pk_fk_dataset.pk1
(
    id GEOGRAPHY NOT NULL,
    PRIMARY KEY(id) NOT ENFORCED
) AS (
     SELECT
        ST_GEOGPOINT(139.6503, 35.6762) AS id,
);

以下エラーが発生し、GEOGRAPHY 型のカラムに Primary key を設定できませんでした。

Invalid value: Primary key column cannot be of type GEOGRAPHY. Invalid column: id. at [3:1]

4. Primary key を INTERVAL 型カラムに設定

データセット・テーブルの準備

CREATE SCHEMA IF NOT EXISTS pk_fk_dataset;

CREATE OR REPLACE TABLE pk_fk_dataset.pk1
(
    id INTERVAL NOT NULL,
    PRIMARY KEY(id) NOT ENFORCED
) AS (
     SELECT
        INTERVAL 1 DAY AS id,
);

以下エラーが発生し、INTERVAL 型のカラムに Primary key を設定できませんでした。

Invalid value: Primary key column cannot be of type INTERVAL. Invalid column: id. at [3:1]

5. Primary key を JSON 型カラムに設定

データセット・テーブルの準備

CREATE SCHEMA IF NOT EXISTS pk_fk_dataset;

CREATE OR REPLACE TABLE pk_fk_dataset.pk1
(
    id JSON NOT NULL,
    PRIMARY KEY(id) NOT ENFORCED
) AS (
     SELECT
        JSON '''{
            "name": "cloud-ace",
            "year": "2023"
        }''' AS id
);

以下エラーが発生し、JSON 型のカラムに Primary key を設定できませんでした。

Invalid value: Primary key column cannot be of type JSON. Invalid column: id. at [3:1]

6. Primary key を TIME 型カラムに設定

6.1. データセット・テーブルの準備

BigQuery public datasets の wikipedia テーブルの timestamp カラムから TIME 型の time カラムを作成し、Primary key / Foreign key として利用します。
以下 4 つのテーブルを用意します。

  • normal1
  • normal2
  • pk1
  • fk1

ただし、Primary key として利用する normal1 テーブルと pk1 テーブルでは、time カラムが一意になるようにします。

-- データセット作成
CREATE SCHEMA IF NOT EXISTS pk_fk_dataset;

-- 通常テーブル作成
CREATE OR REPLACE TABLE pk_fk_dataset.normal1
(
    title STRING NOT NULL,
    id INTEGER,
    language STRING NOT NULL,
    wp_namespace INTEGER NOT NULL,
    is_redirect BOOLEAN,
    revision_id INTEGER,
    contributor_ip STRING,
    contributor_id INTEGER,
    contributor_username STRING,
    timestamp INTEGER NOT NULL,
    is_minor BOOLEAN,
    is_bot BOOLEAN,
    reversion_id INTEGER,
    comment STRING,
    num_characters INTEGER NOT NULL,
    time TIME NOT NULL,
) AS (
     SELECT
         * except(rn)
     FROM (
         SELECT
            *,
            TIME(TIMESTAMP_SECONDS(timestamp)) as time,
            ROW_NUMBER() OVER (PARTITION BY TIME(TIMESTAMP_SECONDS(timestamp)) ORDER BY timestamp) as rn,
        FROM
            bigquery-public-data.samples.wikipedia
        QUALIFY
            rn = 1
     )
);

CREATE OR REPLACE TABLE pk_fk_dataset.normal2
(
    title STRING NOT NULL,
    id INTEGER,
    language STRING NOT NULL,
    wp_namespace INTEGER NOT NULL,
    is_redirect BOOLEAN,
    revision_id INTEGER,
    contributor_ip STRING,
    contributor_id INTEGER,
    contributor_username STRING,
    timestamp INTEGER NOT NULL,
    is_minor BOOLEAN,
    is_bot BOOLEAN,
    reversion_id INTEGER,
    comment STRING,
    num_characters INTEGER NOT NULL,
    time TIME NOT NULL,
) AS (
     SELECT
         *,
         TIME(TIMESTAMP_SECONDS(timestamp)) as time,
     FROM
         bigquery-public-data.samples.wikipedia
);

-- Primary key, Foreign keyを設定したテーブル作成
CREATE OR REPLACE TABLE
pk_fk_dataset.pk1
CLONE pk_fk_dataset.normal1;

CREATE OR REPLACE TABLE
pk_fk_dataset.fk1
CLONE pk_fk_dataset.normal2;

ALTER table pk_fk_dataset.pk1 ADD PRIMARY KEY(time) NOT ENFORCED;

ALTER table pk_fk_dataset.fk1 ADD FOREIGN KEY(time)  REFERENCES pk_fk_dataset.pk1(time)  NOT ENFORCED;

TIME 型の場合は、エラーにならず、PK / FK を設定することができました。



6.2. 結合の検証

6.2.1. 内部結合 (左: PK, 右: FK)

PK / FK テーブルでの結果 (行数: 313,797,035) は、通常テーブルの結果 (行数: 313,797,035) と同じでした。

通常テーブル

SELECT
    normal1.time
FROM
    pk_fk_dataset.normal1
INNER JOIN
    pk_fk_dataset.normal2
ON
    normal1.time = normal2.time

PK / FK テーブル

SELECT
    pk1.time
FROM
    pk_fk_dataset.pk1
INNER JOIN
    pk_fk_dataset.fk1
ON
    pk1.time = fk1.time

6.2.2. 内部結合 (左: FK, 右: PK)

PK / FK テーブルでの結果 (行数: 313,797,035) は、通常テーブルの結果 (行数: 313,797,035) と同じでした。
また、結合解除も発生しました。

通常テーブル

SELECT
    normal2.time
FROM
    pk_fk_dataset.normal2
INNER JOIN
    pk_fk_dataset.normal1
ON
    normal2.time = normal1.time

PK / FK テーブル

SELECT
    fk1.time
FROM
    pk_fk_dataset.fk1
INNER JOIN
    pk_fk_dataset.pk1
ON
    fk1.time = pk1.time

6.2.3. 左外部結合 (左: PK, 右: FK)

PK / FK テーブルでの結果 (行数: 313,797,035) は、通常テーブルの結果 (行数: 313,797,035) と同じでした。

通常テーブル

SELECT
    normal1.time
FROM
    pk_fk_dataset.normal1
LEFT JOIN
    pk_fk_dataset.normal2
ON
    normal1.time = normal2.time

PK / FK テーブル

SELECT
    pk1.time
FROM
    pk_fk_dataset.pk1
LEFT JOIN
    pk_fk_dataset.fk1
ON
    pk1.time = fk1.time

6.2.4. 左外部結合 (左: FK, 右: PK)

PK / FK テーブルでの結果 (行数: 313,797,035) は、通常テーブルの結果 (行数: 313,797,035) と同じでした。
また、結合解除も発生しました。

通常テーブル

SELECT
    normal2.time
FROM
    pk_fk_dataset.normal2
LEFT JOIN
    pk_fk_dataset.normal1
ON
    normal2.time = normal1.time

PK / FK テーブル

SELECT
    fk1.time
FROM
    pk_fk_dataset.fk1
LEFT JOIN
    pk_fk_dataset.pk1
ON
    fk1.time = pk1.time

6.2.5. 結合解除されないときの PK / FK の効果確認 (内部結合)

normal1 (pk1) と normal2 (fk1) 両方のカラムを取得するように 6.2.2. 内部結合 (左: FK, 右: PK) の SQL を変更することで、結合解除されないようにし、PK / FK により結合性能が改善されるか確認します。

通常テーブル

SELECT
    normal2.time,
    normal1.time
FROM
    pk_fk_dataset.normal2
INNER JOIN
    pk_fk_dataset.normal1
ON
    normal2.time = normal1.time

PK / FK テーブル

SELECT
    fk1.time,
    pk1.time
FROM
    pk_fk_dataset.fk1
INNER JOIN
    pk_fk_dataset.pk1
ON
    fk1.time = pk1.time

通常テーブルと PK / FK テーブルそれぞれ 10 回実行した結果は以下のようになりました。

通常テーブル

# 経過時間 (s) 消費したスロット時間 (s)
1 9 1047
2 17 1183
3 15 1079
4 13 1066
5 20 1035
6 15 1909
7 19 1803
8 17 1170
9 20 2198
10 19 1225
平均 16.4 1372

PK / FK テーブル

# 経過時間 (s) 消費したスロット時間 (s)
1 11 1332
2 11 1183
3 18 1759
4 10 1759
5 9 964
6 15 1134
7 18 1255
8 13 1406
9 10 2382
10 11 2129
平均 12.6 1530

今回の場合、PK / FK の効果により、消費したスロット時間は増えているが、クエリの処理速度は速くなりました。

6.2.6. 結合解除されないときの PK / FK の効果確認 (左外部結合)

normal1 (pk1) と normal2 (fk1) 両方のカラムを取得するように 6.2.4. 左外部結合 (左: FK, 右: PK) の SQL を変更することで、結合解除されないようにし、PK / FK により結合性能が改善されるか確認します。

通常テーブル

SELECT
    normal2.time,
    normal1.time
FROM
    pk_fk_dataset.normal2
LEFT JOIN
    pk_fk_dataset.normal1
ON
    normal2.time = normal1.time

PK / FK テーブル

SELECT
    fk1.time,
    pk1.time
FROM
    pk_fk_dataset.fk1
LEFT JOIN
    pk_fk_dataset.pk1
ON
    fk1.time = pk1.time

通常テーブルと PK / FK テーブルそれぞれ 10 回実行した結果は以下のようになりました。

通常テーブル

# 経過時間 (s) 消費したスロット時間 (s)
1 16 2205
2 17 1220
3 11 1119
4 15 1894
5 21 1151
6 12 1196
7 17 1187
8 10 1186
9 24 1266
10 11 1945
平均 15.4 1437

PK / FK テーブル

# 経過時間 (s) 消費したスロット時間 (s)
1 15 1409
2 13 1489
3 15 1488
4 16 1323
5 14 2669
6 17 1394
7 19 1373
8 13 1979
9 10 2122
10 16 1988
平均 14.8 1724

今回の場合、PK / FK の効果により、消費したスロット時間は増えているが、クエリの処理速度は速くなりました。

7. まとめ

公式ドキュメント TABLE_CONSTRAINTS ビューには、以下のような記載があります。

主キーと外部キーの値の制約は適用されません。ユーザーは、各制約に値が一致することを確認する必要があります。そうしないと、誤った結果が生じる可能性があります。具体的な内容は以下のとおりです。

  • (中略)
  • 主キーと外部キーは、BIGNUMERIC、BOOLEAN、DATE、DATETIME、INT64、NUMERIC、STRING、TIMESTAMP のいずれかのタイプにする必要があります。

上記以外の下記データ型に対して、主キーを設定しようとすると、BigQuery がエラーを発生させました。

  • BYTES
  • FLOAT64
  • GEOGRAPHY
  • INTERVAL
  • JSON

ただし、次のカラムに対して PK / FK を設定可能でした。PK / FK の効果も発揮されました。

  • TIME

Discussion