Databricks で UNIQUE 制約を使って宣言と最適化を行う

データチームは、パイプラインで一意性の保証を維持することがよくありますが、それらのすべてをテーブルスキーマで柔軟に表現する方法がなければ、ユーザーやツールがそれらを見つけることができず、クエリ最適化オプティマイザも安全に活用できません。ビジネスエンティティには、一意であることが分かっている複数の値、または値の組み合わせが存在することがよくあります。usersテーブルでは、行の主識別子としてuser_idを使用する一方で、データモデルでもusername、メールアドレス、(workspace_id, external_user_id)に対してそれぞれ一意性の保証を記録している場合があります。

Databricksでは、すでにPRIMARY KEYおよびFOREIGN KEY制約をサポートしています。主キー（primary key）はテーブルの行を識別し、外部キー（foreign key）はその主キーを参照してリレーションシップを記述できます。ただし、テーブルに設定できる主キーは1つだけであり、その列はnull不可（non-nullable）である必要があります。UNIQUE制約は、Databricks SQLおよびDatabricks Runtime 18.2以降のUnity CatalogのDeltaテーブル向けにパブリックプレビューとして提供されており、その他の一意性の保証を記録する方法を追加します。テーブルには複数のUNIQUE制約を宣言でき、それらの列はnull許容（nullable）にすることが可能で、外部キーから参照することもできます。これらの制約を検証し、RELYでマークすると、クエリ最適化オプティマイザは不要な結合（join）や集約を排除するためにもこれらを利用できるようになります。

1つの主キーですべての一意な列をカバーできない場合

PRIMARY KEYとUNIQUEはいずれも一意性を記録しますが、スキーマ内での役割は異なります。

パイプラインの一意性保証をスキーマに組み込む

次のテーブルには、1つの主キーと2つのUNIQUE制約があります。

2つの一意制約は、異なる種類の一意性を示しています。users_external_uqは列の組み合わせに適用されます。各(workspace_id, external_user_id)のペアは、どちらかの値が他のペアに出現する可能性がある場合でも、一意である必要があります。users_optional_id_uqはnull許容列に適用されます。次の行は、どの値がその保証を満たしているかを示しています。

書き込みごとにコストをかけずに保証を記録する

Databricks上の主キーおよび外部キー制約と同様に、UNIQUE制約は情報提供を目的としており、データの書き込み時に強制されません。例えば、UNIQUE (optional_identifier)を宣言しても、null以外の重複値を含む書き込みが失敗するわけではありません。

この分離により、インジェストパイプライン、上流システム、またはその他のデータ品質プロセスによって維持される一意性保証をスキーマに記録できるようになります。また、この保証は、ユーザー、ツール、アプリケーションが確認できるテーブルメタデータになります。

外部キーからUNIQUE制約を参照する

外部キーは、主キーに加えてUNIQUE制約を参照できます。例えば、上記で宣言された複合UNIQUE制約は次のように参照できます。

参照される親列は、外部キーが対象とする一意制約を識別します。子列と親列は位置によって対応付けられます。workspace_idはworkspace_idにマップされ、source_user_idはexternal_user_idにマップされます。名前が一致している必要はありませんが、型と位置は対応している必要があります。REFERENCES（親）列は、UNIQUE制約の定義内の列と同じ順序でリストされる必要があります。そうしないと、制約自体は有効であっても、結合の削除がサイレントに実行されなくなります。

単一列のUNIQUE制約を参照するには、例えばREFERENCES users (username)のように、その親列を明示的に指定します。親列のリストが省略された場合、REFERENCES usersは代わりにテーブルの主キーを対象とします。

RELYでクエリを最適化する

デフォルトでは、一意制約はNORELYを指定して作成されます。これは意図されたスキーマのセマンティクスを記録しますが、Databricksは正当性に依存するクエリの書き換えには使用しません。次のステートメントは、このデフォルトで制約を作成します。

データを検証し、パイプラインが一意性の保証を維持し続けることを確認した後は、制約を削除してRELYを指定して再作成できます。

この例では、CASCADEはusers_username_uqを参照する外部キーも削除するため、一意制約を追加し直した後にそれらを再作成する必要があります。CASCADEを指定しない場合、DROP CONSTRAINTはデフォルトのRESTRICTの動作を使用し、依存する外部キーが存在すると拒否されます。

一意制約を参照する外部キーも、RELYを指定して宣言できます。

RELYは、それぞれの制約に対して個別に設定されます。既存のデータを検証したり、将来の書き込みに対して制約を強制したりするものではありません。これにより、Databricksはクエリを最適化する際に制約が有効であると想定できるようになります。データが信頼されている制約に違反している場合、その制約に基づく書き換えによって誤った結果が生成される可能性があります。そのため、RELYは検証済みであり、継続的に維持されている保証に対してのみ使用する必要があります。

RELYを使用すると、一意制約によって、結合や集約の書き換えに必要な情報を提供できます。書き換えの対象となるかどうかは、以下の例に示すように、null許容性を含め、クエリに必要なその他の保証にも依存します。この最適化サポートは、Photon有効化コンピュートで利用できます。主キーおよび外部キー制約のブログでは、UNIQUE制約によって有効化できる、制約に基づいた結合および集約の最適化について説明しています。その記事の公開以降、RELYサポートは外部キーに対して利用可能になり、現在はUNIQUE制約に対しても利用できるようになりました。

UNIQUEおよび外部キー制約を使用した結合の排除

同じデータに基づいて構築された一部のダッシュボードでは、usersの属性が必要になるため、チームでは常にuser_eventsとusersを結合する共有ビューを作成することがよくあります。user_eventsの情報のみを必要とするダッシュボードでも、ビューからその結合を引き継ぐ場合があります。そのため、生成されるSQLは、usersの列をまったく選択していない場合でも、以下のクエリのようになります。

クエリがusersの列を返さない場合でも、オプティマイザーは通常、結合を削除できません。一致するユーザーが存在しない場合は内部結合によってイベントが削除される可能性があり、複数のユーザーが同じユーザー名を持っている場合はイベントが重複する可能性があるためです。

信頼された制約により、これら両方の可能性が排除されます。UNIQUE制約により、一致するユーザーが最大でも1人であることが保証され、外部キーにより、一致するユーザーが存在することが保証されます。user_events.usernameもNOT NULLであるため、すべてのイベントに正確に1つの一致が存在します。したがって、結合によって結果が変わることはなく、オプティマイザーはuser_eventsから直接event_idを読み取ることができます。

先ほどの主キーおよび外部キーのブログでは、最大1つという保証のみを必要とする関連するLEFT OUTER JOINのケースを示しています。このINNER JOINの例では、一致が存在することを確立するためにも外部キーが必要になります。

NORELY制約を使用する場合、最適化されたプランには結合が保持されます。

両方の制約でRELYを使用している場合、オプティマイザーはすべてのイベントに一致するユーザーが正確に1人存在することを認識し、結合を削除できます。

この例では、user_events.usernameはNOT NULLですが、この最適化はnull許容の場合にも適用されます。Databricksは結合を削除し、内部結合によるnullの処理を維持するためにuser_events.usernameにIS NOT NULLフィルターを追加します。

この最適化を、10 TBのスケールファクターを表すTPC-DS SF10000データセットでベンチマークテストしました。288億行のstore_salesテーブルと6500万行のcustomerテーブルを結合した状態で、customer.c_customer_skをUNIQUEとして宣言し、store_sales.ss_customer_skからの外部キーを定義しました。両方の制約でRELYを使用することで、Databricksは結合を排除し、シャッフルを99.99%以上削減し、エンジンの中央値実行時間を23.6秒から3.0秒に短縮しました（7.9倍の高速化）。

値がすでに一意である場合にDISTINCTをスキップする

BIツールやクエリビルダーは、結合によって重複行が生成される可能性があるため、防御的にDISTINCTを追加することがよくあります。選択された値がすでに一意である場合、DISTINCTは何も削除しませんが、オプティマイザーがその操作が不要であることを証明できない限り、集約とシャッフルが必要になる場合があります。信頼されたUNIQUE制約により、その証明を提供できます。

次の2つの制約を持つuser_identifiersテーブルを考えてみます。

• UNIQUE (user_id) RELY（ここで、user_idはNOT NULL）
• UNIQUE (optional_identifier) RELY（ここで、optional_identifierはnull許容）

テーブルには両方の制約を満たす次のデータが含まれています。

user_idの場合、オプティマイザーは一意性と非nullの保証を組み合わせて使用できます。

最適化されたプランには集約が含まれません。

信頼された制約によりすべてのuser_idがすでに個別の値であることが確立されるため、集約はなくなります。

optional_identifierに対する同等のクエリは、オプティマイザーがnull許容性も考慮する理由を示しています。

最適化されたプランには集約が保持されます。

両方の制約でRELYを使用していますが、null許容制約により、上面に示した2つのNULL行が許可されます。SQLのDISTINCTではそれらの行を1つにまとめる必要があるため、集約を引き続き行う必要があります。

クエリで不足している非nullの保証が提供される場合、null許容のUNIQUE制約でも最適化を有効にすることができます。

オプティマイザーは集約を削除できます。

フィルターは、null許容のUNIQUEセマンティクスのもとで重複する可能性がある値を削除します。残りの値は一意であることが保証されるため、Databricksでは再び集約を回避できます。

この最適化を、10 TBのスケールファクターを表す同じTPC-DS SF10000データセットでベンチマークテストしました。288億行のstore_salesテーブルで、(ss_item_sk, ss_ticket_number)を複合UNIQUE制約として宣言し、それらの列に対してDISTINCTクエリを実行しました。RELYにより、DatabricksはDISTINCTの集約を排除し、シャッフルを99.99%以上削減し、エンジンの中央値実行時間を33.7秒から2.8秒に短縮しました（12.0倍の高速化）。

オプティマイザーが制約を使用する前にデータを検証する

重複行と孤立行を見つける

RELYを使用する前に、データが保証を満たしていることを確認してください。null許容のUNIQUE制約の場合は、NULLが含まれている行を除外してください。UNIQUEではそれらの行の重複が許可されるためです。このクエリでは、重複する非nullの識別子を検索します。

複合 UNIQUE 制約の場合は、制約対象のすべての列でグループ化し、NULL許容の列ごとに IS NOT NULL 条件を追加します。また、左アンチ結合を使用すると、対応する親がない子行を見つけることもできます。

これらのチェックによって、現在のデータが検証されます。RELY を有効にした後も、データ取り込みやデータ品質のパイプラインで保証を維持し続ける必要があります。

宣言された制約の確認

制約を宣言または再作成した後は、いくつかの方法でその定義を確認できます。

• SHOW CREATE TABLE
• DESCRIBE TABLE EXTENDED
• Unity Catalog INFORMATION_SCHEMA リレーション:
  • TABLE_CONSTRAINTS
  • KEY_COLUMN_USAGE
  • CONSTRAINT_TABLE_USAGE
  • CONSTRAINT_COLUMN_USAGE
  • REFERENTIAL_CONSTRAINTS

作成、変更、検査、削除の構文の詳細については、Databricks の制約および CONSTRAINT 句を参照してください。

UNIQUE と RELY の段階的な導入

UNIQUE は段階的に導入できます。

1. デフォルトの NORELY を使用して、既存の一意性の保証を宣言します。
2. 関係がスキーマの一部である外部キーから一意キーを参照します。
3. データパイプラインで一意性と参照整合性を検証し、継続的に維持します。
4. 一意性を検証した後に UNIQUE 制約で RELY を使用し、該当するすべての子値に対応する親行があることを確認した後に外部キーで使用します。
5. EXPLAIN を使用してプランを比較し、対象となる書き換えを確認します。

まとめ

UNIQUE 制約を使用すると、単一の NULL 非許容の PRIMARY KEY に加えて、テーブルに複数の一意性の保証を記録できるようになり、制約された列で NULL を許容できるようになります。外部キーは、主キーだけでなく UNIQUE 制約も参照できます。UNIQUE 制約はデフォルトでは情報提供を目的としていますが、検証後、Databricks は RELY を使用して、対象となる結合や DISTINCT クエリを最適化できるようになります。

(このブログ記事はAI翻訳ツールを使用して翻訳されています) 原文記事