SnowflakeのPrimary KeyおよびUnique制約が実行計画に与える影響の有無について調べてみた。
はじめに
好物はインフラとフロントエンドのかじわらゆたかです。
Redshiftを用いながら業務をしていく中でのハマりごととして、Primary KeyおよびUnique制約が実行計画に与える影響があるかと思います。
これはRedshiftのドキュメントに以下のように記載されている内容となります。
一意性、プライマリキー、および外部キーの制約は情報提供のみを目的としており、 Amazon Redshift によって強要されることはありません。 ただし、プライマリキーと外部キーはプランニング時のヒントとして使用されます。 (中略) プランナはこれらのキーの関係を活用しますが、 Amazon Redshift テーブルのすべてのキーがロード時に有効であることが前提となります。 アプリケーションが無効な外部キーまたはプライマリキーを許可する場合、 いくつかのクエリが不正な結果を返す可能性があります。
この現象の詳細については以下の松村のブログを見てもらうのが良いのですが、 これと同様のことがSnowflakeでは起きるのかを検証してみました。
結論
Snowflakeではプライマリーキーの制約はRedshiftと同様情報提供として用いられますが、 Redshiftとは異なり実行計画への影響もなく情報提供のみの活用の模様です。
ドキュメントを確認してみる
snowflakeの以下のドキュメントに制約事項についての記載があります。
注釈
Snowflakeは、制約の定義と維持をサポートしていますが、 常に強制される NOT NULL 制約以外については強制しません。
制約の概要 — Snowflake Documentation
Redshiftのような実行計画についての記載はありませんでした。
実際に試してみる。
松村がRedshiftで検証したのと同様のSQL文をSnowflakeでも実行してみました。 なおここでは、以下は前提としてスキップします。
- ユーザーがログイン後、適切なロールで動いていること
- 仮想データウェアハウスは作成済みであること
- データベース・スキーマーは作成済みであること
前準備
松村のブログからそのままですが、実際にPrimary Key制約とUniq制約付与したテーブルを作り、 重複したデータを投入しました。
CREATE OR REPLACE TABLE users (
id INTEGER,
mailaddress VARCHAR(100),
name VARCHAR(100),
PRIMARY KEY(id),
UNIQUE(mailaddress)
);
INSERT INTO users
(id, mailaddress, name)
VALUES
(1, 'foo@example.com', 'foo'),
(2, 'bar@example.com', 'bar'),
(2, 'bar@example.com', 'bar');
制約のない列でDISTINCTした場合
SELECT DISTINCT name FROM users; +------+ | NAME | |------| | foo | | bar | +------+
想定通り、重複が排除されて結果が返ってきました。
実行計画も出せるので、出してみます。
EXPLAIN USING TEXT SELECT DISTINCT name FROM users;
+-------------------------------------------------------------------------------------------------------------------------+
| content |
|-------------------------------------------------------------------------------------------------------------------------|
| GlobalStats: |
| partitionsTotal=1 |
| partitionsAssigned=1 |
| bytesAssigned=1536 |
| Operations: |
| 1:0 ->Result USERS.NAME |
| 1:1 ->Aggregate groupKeys: [USERS.NAME] |
| 1:2 ->TableScan BOA_DB.PUBLIC.USERS NAME {partitionsTotal=1, partitionsAssigned=1, bytesAssigned=1536} |
| |
+-------------------------------------------------------------------------------------------------------------------------+
実行計画の読み方等はくわしくないのですが、groupKeys: [USERS.NAME] の箇所でそれらしい動きをしてそうです。
主キーでDISTINCTした場合
主キーでDISTINCT してみます。
SELECT DISTINCT id FROM users; +----+ | ID | |----| | 1 | | 2 | +----+
Redshiftとは異なり、主キーであっても重複は排除されています。
実行計画も制約のない列でやったときと似ており、groupKeys: [USERS.ID] で重複を除去していることがわかります。
EXPLAIN USING TEXT SELECT DISTINCT id FROM users;
+-----------------------------------------------------------------------------------------------------------------------+
| content |
|-----------------------------------------------------------------------------------------------------------------------|
| GlobalStats: |
| partitionsTotal=1 |
| partitionsAssigned=1 |
| bytesAssigned=1536 |
| Operations: |
| 1:0 ->Result USERS.ID |
| 1:1 ->Aggregate groupKeys: [USERS.ID] |
| 1:2 ->TableScan BOA_DB.PUBLIC.USERS ID {partitionsTotal=1, partitionsAssigned=1, bytesAssigned=1536} |
| |
+-----------------------------------------------------------------------------------------------------------------------+
ユニークキーでDISTINCTした場合
ユニークキーで DISTINCT してみます。
SELECT DISTINCT mailaddress FROM users; +-----------------+ | MAILADDRESS | |-----------------| | foo@example.com | | bar@example.com | +-----------------+
こちらもRedshiftのときとは異なり、重複が排除されています。
実行計画も制約のない列に対して実行したときと同様にgroupKeys: [USERS.MAILADDRESS] で重複の除去を行おうとしていることがわかります。
EXPLAIN USING TEXT SELECT DISTINCT mailaddress FROM users;
+--------------------------------------------------------------------------------------------------------------------------------+
| content |
|--------------------------------------------------------------------------------------------------------------------------------|
| GlobalStats: |
| partitionsTotal=1 |
| partitionsAssigned=1 |
| bytesAssigned=1536 |
| Operations: |
| 1:0 ->Result USERS.MAILADDRESS |
| 1:1 ->Aggregate groupKeys: [USERS.MAILADDRESS] |
| 1:2 ->TableScan BOA_DB.PUBLIC.USERS MAILADDRESS {partitionsTotal=1, partitionsAssigned=1, bytesAssigned=1536} |
| |
+--------------------------------------------------------------------------------------------------------------------------------+
最後に
RedshiftとSnowflakeの実装の違いがわかる検証結果になったかと思います。 制約をDWH内部でどのように用いるかといったのはそれぞれのDWHごとに思想によるということがわかりました。 テーブル作成の制約がどのように影響するかといった話は意外と影響があったりする話でもあるので、 きちんと確認をしておくことが必要です。
![[新機能]dbtのdata testで任意の設定をconfigで指定できるようになり、各data testでSnowflakeのウェアハウスを指定できるようになりました](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-f846b6052d1f7b983172226c6b1ed44b/6b4729aabfb5b7a3359acc27dc6b5453/dbt-1200x630-1.jpg)
