プライマリキーと外部キー制約がGAになり、クエリが高速化されました

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

さらに、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コマンドで制約情報を表示することもできます。

カタログエクスプローラーおよびエンティティリレーションシップダイアグラム

カタログエクスプローラーを通じて制約情報を表示することもできます。

各プライマリキーおよび外部キー列の名前の横には、小さなキーアイコンがあります。

また、カタログエクスプローラーのエンティティリレーションシップダイアグラムを使用して、プライマリキーと外部キーの情報、およびテーブル間のリレーションシップを視覚化できます。以下は、usersテーブルとproductsテーブルを参照するpurchasesテーブルの例です。

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プライマリキー制約で行える簡単な最適化の1つは、不要な集計を削除することです。たとえば、RELYプライマリキーを使用したテーブルに対してDISTINCT操作を適用するクエリの場合。

不要なDISTINCT操作を削除できます。

ご覧のとおり、このクエリはRELYプライマリキー制約の有効性に依存しています。customerテーブルに重複する顧客IDがある場合、変換されたクエリは誤った重複結果を返します。RELYオプションを設定した場合、制約の有効性を強制するのはユーザーの責任です。

プライマリキーがデフォルトのNORELYの場合、オプティマイザはクエリからDISTINCT操作を削除しません。この場合、実行速度は遅くなる可能性がありますが、重複があっても常に正しい結果が返されます。プライマリキーがRELYの場合、DatabricksはDISTINCT操作を削除できるため、クエリを大幅に高速化できます。上記の例では約2倍になります。

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

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

例として、store_salesテーブルとcustomerテーブルを結合するクエリがあるとします。結合条件は、PRIMARY KEY (c_customer_sk) RELYというcustomerテーブルのプライマリキーです。

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

したがって、クエリは実際にはファクトテーブルstore_salesからss_quantity列のみを必要とします。そのため、クエリオプティマイザは結合をクエリから完全に削除し、次のように変換できます。

これにより、結合全体を回避できるため、はるかに高速に実行されます。この例では、最適化によってクエリが1.5分から6秒に高速化されたことがわかります。そして、結合が削除できる多数のテーブルを含む場合、そのメリットはさらに大きくなる可能性があります。

このようなクエリを実行する人がいるのか疑問に思うかもしれません。実際には、あなたが考えているよりもはるかに一般的です。一般的な理由の1つは、ユーザーが複数のテーブルを結合するビューを構築することです。たとえば、多くのファクトテーブルとディメンションテーブルを結合するなどです。これらのビューに対してクエリを作成しますが、多くの場合、クエリは一部のテーブルの列のみを使用し、すべてのテーブルの列を使用しないため、オプティマイザは各クエリで必要とされないテーブルとの結合を削除できます。このパターンは、多くのBusiness Intelligence（BI）ツールでも一般的です。BIツールは、クエリが一部のテーブルの列のみを使用する場合でも、スキーマ内の多くのテーブルを結合するクエリを生成することがよくあります。

結論

パブリックプレビュー以来、2600を超えるDatabricksのお客様がプライマリキーと外部キー制約を使用しています。本日、この機能の一般提供を発表できることを嬉しく思います。これは、Databricksにおけるデータ管理と整合性の強化へのコミットメントの新しい段階を示すものです。

さらに、Databricksは現在、RELYオプション付きのキー制約を活用して、不要な集計や結合の排除など、クエリを最適化し、クエリパフォーマンスを大幅に向上させています。

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