主キーと外部キー制約が一般提供開始：クエリの高速化を実現

Databricksは、Databricks Runtime 15.2およびDatabricks SQL 2024.30から始まる主キー（PK）および外部キー（FK）制約の一般提供（GA）を発表することを大変嬉しく思います。このリリースは、毎週数百のアクティブな顧客によって受け入れられた非常に成功したパブリックプレビューに続き、レイクハウス内のデータ整合性とリレーショナルデータ管理の強化における重要なマイルストーンをさらに表しています。

さらに、Databricksはこれらの制約を使用してクエリを最適化し、クエリプランから不要な操作を排除することができ、パフォーマンスを大幅に向上させることができます。

主キーと外部キー制約

主キー（PK）と外部キー（FK）は、リレーショナルデータベースの基本的な構成要素であり、データモデリングの基礎を形成します。これらは、スキーマ内のデータ関係についての情報をユーザー、ツール、アプリケーションに提供し、制約を活用した最適化を可能にし、クエリの速度を向上させます。主キーと外部キーは、Unity CatalogでホストされているDelta Lakeテーブルに対して一般利用可能になりました。

SQL言語

テーブルを作成するときに制約を定義することができます：

上記の例では、UserID列に主キー制約を定義しています。Databricksはまた、列のグループに対する制約もサポートしています。

既存のDeltaテーブルを修正して制約を追加または削除することもできます：

ここでは、既存のテーブルで非Null列である ProductID に products_pk という名前の主キーを作成します。この操作を成功させるためには、テーブルの所有者である必要があります。制約名はスキーマ内で一意でなければならないことに注意してください。
後続のコマンドは、名前を指定してプライマリキーを削除しています。

外部キーにも同じプロセスが適用されます。次の表は、テーブル作成時に2つの外部キーを定義します：

制約に関連する構文と操作の詳細については、CREATE TABLEとALTER TABLEの文書を参照してください。

主キーと外部キーの制約はDatabricksエンジンでは強制されませんが、データの整合性関係を示すために役立つ場合があります。Databricksは、取り込みパイプラインの一部として上流で主キー制約を強制することもできます。Delta Live Tablesで管理されたデータ品質を参照して、強制された制約について詳しく知ることができます。Databricksは、強制的なNOT NULLおよびCHECK制約もサポートしています（詳細は制約のドキュメンテーションを参照してください）。

パートナーエコシステム

最新バージョンのTableauやPowerBIなどのツールやアプリケーションは、JDBCおよびODBCコネクタを通じてDatabricksから主キーと外部キーの関係を自動的にインポートして利用できます。

制約を表示する

テーブルで定義された主キーと外部キーの制約を表示する方法はいくつかあります。また、DESCRIBE TABLE EXTENDEDコマンドを使用してSQLコマンドを単純に使用して制約情報を表示することもできます：

カタログエクスプローラーとER図

また、カタログエクスプローラーを通じて制約情報を閲覧することもできます：

各主キーと外部キーの列の名前の隣には小さなキーアイコンがあります。

そして、カタログエクスプローラーのER図で、主キーと外部キーの情報やテーブル間の関係を視覚化することができます。以下は、purchasesテーブルが二つのテーブル、usersとproductsを参照する例です：

INFORMATION SCHEMA

次のINFORMATION_SCHEMAテーブルも制約情報を提供します：

• TABLE_CONSTRAINTS：カタログ内のすべての主キーと外部キー制約のメタデータを記述します。
• KEY_COLUMN_USAGE：カタログ内のプライマリキーまたは外部キー制約の列をリストします。
• CONSTRAINT_TABLE_USAGE：カタログ内のテーブルを参照する制約を説明します。
• CONSTRAINT_COLUMN_USAGE：カタログ内の列を参照する制約を説明します。
• REFERENTIAL_CONSTRAINTS：カタログで定義された参照（外部キー）制約を説明します。

RELYオプションを使用して最適化を有効にする

主キー制約が有効であることを知っている場合（例えば、データパイプラインやETLジョブがそれを強制するため）、RELYオプションを指定して制約に基づく最適化を有効にすることができます。

RELYオプションを使用すると、Databricksはデータの整合性が保たれていることを保証するため、制約の有効性に依存する方法でクエリを最適化することができます。ここでは注意が必要です。制約がRELYとマークされているが、データが制約を違反している場合、クエリの結果が誤っている可能性があります。

制約にRELYオプションを指定しない場合、デフォルトはNORELYで、制約は情報提供や統計目的で使用される可能性がありますが、クエリはそれらに依存して正しく実行されるわけではありません。

RELYオプションとそれを利用した最適化は現在、主キーに対して利用可能で、外部キーに対しても近々利用可能になります。

ALTER TABLEを使用して、テーブルの主キーを変更してRELYまたはNORELYにすることができます。例えば：

不必要な集約を排除してクエリを高速化する

RELYプライマリキー制約を使用した簡単な最適化の一つは、不要な集約の排除です。例えば、RELYを使用してプライマリキーを持つテーブルに対してdistinct操作を適用するクエリでは：

不必要なDISTINCT操作を削除することができます：

このクエリはRELY主キー制約の有効性に依存しています - カスタマーテーブルに重複したカスタマーIDがある場合、変換されたクエリは誤った重複結果を返します。RELYオプションを設定した場合、制約の有効性を確保する責任はあなたにあります。

プライマリキーがNORELY（デフォルト）の場合、最適化プログラムはクエリからDISTINCT操作を削除しません。その後、実行速度は遅くなるかもしれませんが、重複があっても常に正確な結果を返します。プライマリキーがRELYの場合、DatabricksはDISTINCT操作を削除でき、これによりクエリの速度が大幅に向上します - 上記の例では約2倍です。

不必要な結合を排除してクエリを高速化する

RELYプライマリキーを使用して行うことができる非常に便利な最適化の一つは、不要な結合の排除です。クエリが結合条件以外のどこでも参照されていないテーブルを結合する場合、最適化プログラムは結合が不要であると判断し、クエリプランから結合を削除することができます。

例を挙げてみましょう。2つのテーブル、store_salesとcustomerが、顧客テーブルの主キーPRIMARY KEY (c_customer_sk) RELYで結合されているとします。

主キーがなければ、store_salesの各行はcustomerの複数の行と一致する可能性があり、正しいSUM値を計算するために結合を実行する必要があります。しかし、テーブルcustomerがその主キーで結合されているため、結合はstore_salesの各行に対して一行を出力することがわかります。

したがって、クエリは実際には事実テーブルstore_salesからの列ss_quantityだけが必要です。したがって、クエリオプティマイザはクエリから結合を完全に排除し、それを次のように変換することができます：

これは、結合全体を避けることで大幅に速くなります - この例では、最適化によりクエリが1.5分から6秒に速くなることを確認できます！そして、結合が多くのテーブルを排除できる場合、その利点はさらに大きくなります！

あなたは疑問に思うかもしれません、なぜ誰もがこのようなクエリを実行するのでしょうか？実際には、あなたが思っているよりもはるかに一般的です！一般的な理由の一つは、ユーザーがいくつかのテーブルを結合してビューを作成することです。例えば、多くの事実テーブルとディメンションテーブルを結合します。彼らはこれらのビューに対してクエリを書き、テーブルの一部だけから列を使用することがよくあります - そのため、最適化プログラムは各クエリで必要とされないテーブルに対する結合を排除することができます。このパターンは、ビジネスインテリジェンス（BI）ツールでもよく見られます。これらのツールは、クエリがテーブルの一部の列しか使用していない場合でも、スキーマ内の多くのテーブルを結合するクエリを生成することがよくあります。

まとめ

公開プレビュー以来、2600人以上のDatabricksの顧客が主キーと外部キーの制約を使用しています。今日、この機能の一般提供を発表することを大変うれしく思います。これは、Databricksでのデータ管理と整合性の強化に向けた私たちの取り組みの新たなステージを示しています。

さらに、Databricksは現在、RELYオプションの主要な制約を利用してクエリを最適化し、不必要な集約や結合を排除することで、クエリのパフォーマンスを大幅に向上させています。