🔑

BigQuery クエリ性能改善: Foreign key の削除、変更方法

2023/09/29に公開

Google Cloud

BigQuery

tech

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

BigQuery テーブルに Foreign key を設定する場合、 CREATE TABLE ステートメント、追加する場合は ALTER TABLE ADD FOREIGN KEY ステートメントを使用すれば良いことは、比較的容易にわかります。
ただ、削除と変更方法は少しづらかったので、実際に試して確認してみます。

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

以下 2 つのテーブルを作成します。それぞれ id カラムを PK / FK に設定します。

pk_drop_test
fk_drop_test

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

-- Primary key を設定したテーブルを作成
CREATE OR REPLACE TABLE pk_fk_dataset.pk_drop_test(
  id INT64 PRIMARY KEY NOT ENFORCED,
  value INT64,
);

-- Foreign key を設定したテーブルを作成
CREATE OR REPLACE TABLE pk_fk_dataset.fk_drop_test(
  id INT64 REFERENCES pk_fk_dataset.pk_drop_test(id) NOT ENFORCED,
  value INT64,
);

2. Foreign key 削除

まず、下記 SQLで、fk_drop_test テーブルに設定された FK の制約名を確認します。

SELECT *
FROM pk_fk_dataset.INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE table_name = 'fk_drop_test';

constraint_name から、制約名が fk_drop_test.fk$1 (または、fk$1) であることが確認できました。

次に、下記 SQLでこの制約を削除します。

ALTER TABLE pk_fk_dataset.fk_drop_test DROP CONSTRAINT `fk_drop_test.fk$1`;

fk_drop_test テーブルを見ると、FK が削除されていることを確認できました。

また、制約名の部分を fk$1 にしても削除可能です。

ALTER TABLE pk_fk_dataset.fk_drop_test DROP CONSTRAINT `fk$1`;

3. Foreign key の変更

下記 2つの方法で Foreign key を変更できることを確認します。

ALTER TABLE ステートメント (ALTER TABLE DROP CONSTRAINT ステートメントと ALTER TABLE ADD FOREIGN KEY ステートメント) を使用
テーブル再作成

(それぞれ実施するときはテーブルを初期状態に戻しておきます。)

3.1. ALTER TABLE ステートメントを使用

下記 SQLで、FK を id カラムから、value カラムに変更します。

ALTER TABLE pk_fk_dataset.fk_drop_test DROP CONSTRAINT `fk$1`;

ALTER TABLE pk_fk_dataset.fk_drop_test ADD CONSTRAINT fk_name FOREIGN KEY (value) REFERENCES pk_fk_dataset.pk_drop_test(id) NOT ENFORCED;

fk_drop_test テーブルを見ると、FK が value カラムに変更されていることを確認できました。

3.2. テーブル再作成

下記 SQLで、fk_drop_test テーブルを再作成します。

CREATE OR REPLACE TABLE pk_fk_dataset.fk_drop_test(
  id INT64,
  value INT64,
);

ALTER TABLE pk_fk_dataset.fk_drop_test ADD CONSTRAINT fk_name FOREIGN KEY (value) REFERENCES pk_fk_dataset.pk_drop_test(id) NOT ENFORCED;