皆さんこんにちは。 インサイトテクノロジー 宮地です。
早いもので、2023年になって1ヶ月が経とうとしていますね。
年々月日の経つのを早く感じてしまいますが、実はジャネーの法則というもので心理学的に説明されています。
[ジャネーの法則]
体感時間での計算となりますが、人生80年とした場合、30歳で80%、40歳で85%、50歳で90%となっており、体感時間的には既に人生の終盤〜最終盤を迎えてしまっています！！
残り少ない（!?）時間を悔いなく、有効に使いたいものです。
では、本題となりますが、今回は「クラウドDWHにおける制約」について触れたいと思います。
まず、それぞれ対応している制約は、下記となっています。
■Snowflake
・一意キー
・主キー
・外部キー
・列の NOT NULL 制約
■Amazon Redshift
・一意キー
・主キー
・外部キー
・列の NOT NULL 制約
■Azure Synapse Analytics（専用SQLプール）
・一意キー
・主キー
・列の NOT NULL 制約
Azure Synapse Analyticsで外部キーを使えない以外は全て同じとなっています。
それぞれ、制約についての説明を確認してみましょう。
■Snowflake
Snowflakeは、制約の定義と維持をサポートしていますが、常に強制される NOT NULL 制約以外については強制しません。
https://docs.snowflake.com/ja/sql-reference/constraints-overview.html
■Amazon Redshift
一意性、プライマリキー、および外部キーの制約は情報提供のみを目的としており、Amazon Redshiftによって強要されることはありません。ただし、プライマリキーと外部キーはプランニング時のヒントとして使用されます。
アプリケーションが無効な外部キーまたはプライマリキーを許可する場合、いくつかのクエリが不正な結果を返す可能性があります。
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/t_Defining_constraints.html
■Azure Synapse Analytics（専用SQLプール）
主キーや一意キーを使用すると、専用 SQL プール エンジンでクエリの最適な実行プランを生成できます。
専用 SQL プールで主キーまたは一意制約を使用してテーブルを作成した後、ユーザーはそれらの列のすべての値が一意であることを確認する必要があります。 これに違反すると、クエリで不正確な結果が返される可能性があります。
https://learn.microsoft.com/ja-jp/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-table-constraints
Azure Synapseでは記載はありませんが、SnowflakeとRedshift同様、NOT NULL以外の制約は強制されません。（つまり、PKエラーや一意制約エラーは発生しないとのことです。）
また、Snowflakeでは記載はありませんが、RedshiftとAzure Synapseでは実行プランに関する影響の記載があります。
では、それぞれの動作を見てみましょう。
■Snowflake
create table pktest (id integer not null, value1 integer, remarks varchar(100), constraint pk_pktest1 primary key (id));
insert into pktest values (1,10,'AAA');
insert into pktest values (1,100,'AAA');
insert into pktest values (2,100,'AAA');
commit;普通にselect。
続いて、distinctでid値を見てみます。
1,2 と表示されました。
更に、idの値ごとのvalue1値の合計を取ってみます。
Oracle、PostgreSQL等のRDBMSと同じイメージの結果となっていますね。
■Amazon Redshift
create table gonta.pktest (id integer not null, value1 integer, remarks varchar(100) , constraint pk_pktest primary key (id));
insert into gonta.pktest values (1,10,'AAA');
insert into gonta.pktest values (1,100,'AAA');
insert into gonta.pktest values (2,10,'AAA');
commit;普通にselect。
distinctでid値。
あれ？id=1のレコードが2つ表示されています・・・
ということは、idの値ごとのvalue1値の合計は・・・
おっと、こっちはidごとに纏められて出力されました！
■Azure Synapse Analytics（専用SQLプール）
create table GONTA.PKTEST ( ID integer not null, value1 integer , remarks varchar(100), constraint PK_PKTEST primary key nonclustered (ID) not enforced);
insert into GONTA.PKTEST values (1,10,'AAA');
insert into GONTA.PKTEST values (1,100,'AAA');
insert into GONTA.PKTEST values (2,100,'AAA');
commit;普通にselect。
続いてidのdistinct。
こちらも、Amazon Redshift同様、ID=1が2つ表示されてしまっています。
最後、IDごとのVALUE1の合計値を見てみましょう。
あれれ、こちらはdistinctの結果と同じになりました・・・ある意味、一貫した動作と言えるのかもしれませんが。
（そもそもPK項目でGROUP BYなんて通常しないですが、というのはさておき）
※Unique制約、Foreign Key制約についても同様に確認しましたが、結果は同じでした。
まとめると・・・
・Primary Key制約、Unique制約は、ドキュメントに記載の通り、単なる目印でしかなく、重複したキー値での登録ができてしまう。
・重複したキー値がある状態でdistinct、group by等を行うと、想定した結果とならない。（Redshift、Azure Synapse）
その為、Primary Key制約、Unique制約を定義した項目の値はデータを登録するアプリケーション側で一意性を保証する必要があります。
Foreign Key制約の場合は、参照先データがあることの保証が必要となります。
目印だけの制約でしかなく、想定外の挙動をするなら、Primary Key制約、Unique制約、Foreign Key制約なんていらないんじゃね！？ と思いますよね。
Snowflake、Redshift、Azure Synapse Analytics（専用SQLプール）でこれらの制約がついていて、どんなメリットがあるかを考察してみました。
Snowflake、Redshift、Azure Synapse Analyticsはご存知の通り、クラウドベースのData Ware Houseです。（＝分析用途）
DWHの用途としては、まず、定型的なダッシュボード表示や帳票出力の処理を作成する際、複数テーブルを結合する際の目印として使えます。
また、各現場の担当者の方が自由に分析に利用する際、データカタログに登録し、データカタログからメタデータを参照しながら色々な分析を行うというシーンを考えた場合、複数テーブルの関連性を簡単に確認することが可能となります。
特にデータソースがDWHでデータカタログの利用を推進するというシーンでは有用と考えられます。
通常、DWHではPrimary Key、Unique、Foreign Keyはほぼ使用しないのですが、このような用途がある場合、定義することを検討してみてください。
※登録処理側で一意性を保証することを忘れずに！
今回はココまでです。
まだまだ寒い日が続いております。皆さん、体調にはくれぐれもお気を付けください。
