Deklarieren und optimieren Sie mit UNIQUE-Beschränkungen auf Databricks

Daten-Teams stellen in ihren Pipelines oft Eindeutigkeitsgarantien sicher, aber ohne eine flexible Möglichkeit, diese im Tabellenschema darzustellen, können Benutzer und Tools sie nicht erkennen und der Abfrageoptimierer sie nicht sicher nutzen. Geschäftseinheiten haben oft mehr als einen Wert oder eine Kombination von Werten, die als eindeutig bekannt sind: Eine users-Tabelle verwendet möglicherweise user_id als primären Zeilenbezeichner, während ihr Datenmodell auch separate Eindeutigkeitsgarantien für username, eine E-Mail-Adresse und (workspace_id, external_user_id) erfasst.

Databricks unterstützt bereits PRIMARY KEY- und FOREIGN KEY-Constraints: Ein Primärschlüssel identifiziert die Zeilen einer Tabelle, und Fremdschlüssel können darauf verweisen, um Beziehungen zu beschreiben. Eine Tabelle kann jedoch nur einen Primärschlüssel haben, und dessen Spalten dürfen keine Nullwerte enthalten. UNIQUE-Constraints, die jetzt in der Public Preview für Unity Catalog Delta-Tabellen in Databricks SQL und Databricks Runtime 18.2 und höher verfügbar sind, bieten eine Möglichkeit, die anderen Eindeutigkeitsgarantien zu erfassen. Eine Tabelle kann mehrere UNIQUE-Constraints deklarieren, ihre Spalten dürfen Nullwerte enthalten, und Fremdschlüssel können auf sie verweisen. Nachdem Sie diese Constraints validiert und mit RELY markiert haben, kann der Abfrageoptimierer sie auch verwenden, um unnötige Joins und Aggregationen zu eliminieren.

Wenn ein Primärschlüssel nicht jede eindeutige Spalte abdecken kann

PRIMARY KEY und UNIQUE erfassen beide die Eindeutigkeit, erfüllen jedoch unterschiedliche Schema-Rollen:

Fügen Sie die Eindeutigkeitsgarantien Ihrer Pipeline in das Schema ein

Die folgende Tabelle hat einen Primärschlüssel und zwei UNIQUE-Constraints:

Die beiden Eindeutigkeits-Constraints veranschaulichen verschiedene Arten von Eindeutigkeit. users_external_uq gilt für eine Spaltenkombination: Jedes (workspace_id, external_user_id)-Paar muss eindeutig sein, auch wenn jeder Wert in anderen Paaren vorkommen kann. users_optional_id_uq gilt für eine Spalte, die Nullwerte zulässt. Die folgenden Zeilen zeigen, welche Werte diese Garantie erfüllen:

Erfassen Sie die Garantie, ohne bei jedem Schreibvorgang Kosten zu verursachen

Ähnlich wie Primär- und Fremdschlüssel-Constraints bei Databricks sind UNIQUE-Constraints informativ und werden beim Schreiben von Daten nicht erzwingt. Das Deklarieren von UNIQUE (optional_identifier) führt beispielsweise nicht dazu, dass ein Schreibvorgang mit doppelten Nicht-Null-Werten fehlschlägt.

Diese Trennung ermöglicht es dem Schema, eine Eindeutigkeitsgarantie zu erfassen, die von einer Ingestion-Pipeline, einem vorgelagerten System oder einem anderen Datenqualifizierungsprozess aufrechterhalten wird. Die Garantie wird zudem zu Tabellenmetadaten, die von Benutzern, Tools und Anwendungen überprüft werden können.

Verweisen auf einen UNIQUE-Constraint von einem Fremdschlüssel aus

Ein Fremdschlüssel kann zusätzlich zu einem Primärschlüssel auf einen UNIQUE-Constraint verweisen. Zum Beispiel kann auf den oben deklarierten zusammengesetzten UNIQUE-Constraint wie folgt verwiesen werden:

Die referenzierten übergeordneten Spalten geben an, auf welchen eindeutigen Constraint der Fremdschlüssel abzielt. Untergeordnete und übergeordnete Spalten entsprechen sich nach Position: workspace_id wird auf workspace_id abgebildet, und source_user_id wird auf external_user_id abgebildet. Ihre Namen müssen nicht übereinstimmen, aber ihre Typen und Positionen müssen korrespondieren. Die (übergeordneten) REFERENCES-Spalten müssen in derselben Reihenfolge wie die Spalten in der Definition des UNIQUE-Constraints aufgelistet werden; andernfalls ist der Constraint zwar weiterhin gültig, aber die Join-Eliminierung greift im Hintergrund nicht.

Um auf einen UNIQUE-Constraint mit einer einzelnen Spalte zu verweisen, benennen Sie dessen übergeordnete Spalte explizit, zum Beispiel REFERENCES users (username). Wenn die Liste der übergeordneten Spalten weggelassen wird, zielt REFERENCES users stattdessen auf den Primärschlüssel der Tabelle ab.

Optimieren von Abfragen mit RELY

Standardmäßig wird ein eindeutiger Constraint mit NORELY erstellt. Er erfasst die beabsichtigte Schemasemantik, aber Databricks verwendet ihn nicht für Korrektheitsabhängige Abfrage-Umschreibungen. Die folgende Anweisung erstellt einen Constraint mit diesem Standardwert:

Nachdem Sie die Daten überprüft und sichergestellt haben, dass Ihre Pipelines die Garantie weiterhin aufrechtzuerhalten, können Sie den Constraint löschen und mit RELY neu erstellen:

In diesem Beispiel löscht CASCADE auch alle Fremdschlüssel, die auf users_username_uq verweisen, und diese müssen neu erstellt werden, nachdem der eindeutige Constraint wieder hinzugefügt wurde. Ohne CASCADE verwendet DROP CONSTRAINT das Standardverhalten von RESTRICT und wird abgelehnt, wenn abhängige Fremdschlüssel vorhanden sind.

Ein Fremdschlüssel, der auf den eindeutigen Constraint verweist, kann ebenfalls mit RELY deklariert werden:

RELY wird für jeden Constraint unabhängig festgelegt. Es validiert keine vorhandenen Daten und erzwingt den Constraint nicht bei zukünftigen Schreibvorgängen. Es ermöglicht Databricks anzunehmen, dass der Constraint bei der Abfrageoptimierung gültig ist. Wenn die Daten einen verlässlichen Constraint verletzen, kann eine auf diesem Constraint basierende Umschreibung zu falschen Ergebnissen führen. Sie sollten RELY daher nur für Garantien verwenden, die validiert und kontinuierlich aufrechterhalten werden.

Mit RELY können Eindeutigkeitsbeschränkungen die Informationen bereitstellen, die für gültige Join- und Aggregationsneuschreibungen erforderlich sind. Die Gültigkeit hängt außerdem von anderen vom Abfrageprozess geforderten Garantien ab, einschließlich der Nullable-Eigenschaft, wie die folgenden Beispiele zeigen. Diese Optimierungsunterstützung ist für Photon-aktiviertes Compute verfügbar. Der Primär- und Fremdschlüssel-Blog beschreibt beschränkungsbasierte Join- und Aggregationsoptimierungen, die auch durch UNIQUE-Beschränkungen ermöglicht werden können. Seit der Veröffentlichung dieses Beitrags ist die Unterstützung für RELY für Fremdschlüssel und nun auch für UNIQUE-Beschränkungen verfügbar.

Joins mithilfe von UNIQUE- und Fremdschlüsselbeschränkungen eliminieren

Einige Dashboards, die auf denselben Daten basieren, benötigen Attribute von users, weshalb Teams häufig eine gemeinsame Ansicht erstellen, die user_events immer mit users verknüpft. Ein Dashboard, das nur Informationen von user_events benötigt, übernimmt diesen Join möglicherweise trotzdem aus der Ansicht. Das generierte SQL kann daher wie die folgende Abfrage aussehen, obwohl keine Spalten aus users ausgewählt werden:

Obwohl die Abfrage keine Spalten von users zurückgibt, kann der Optimizer den Join normalerweise nicht entfernen. Ein Inner Join könnte ein Ereignis entfernen, wenn kein passender Benutzer vorhanden ist, oder es duplizieren, wenn mehrere Benutzer denselben Benutzernamen haben.

Die zugrunde gelegten Beschränkungen schließen beide Möglichkeiten aus. Die UNIQUE-Beschränkung garantiert höchstens einen übereinstimmenden Benutzer, während der Fremdschlüssel garantiert, dass eine Übereinstimmung existiert. Da user_events.username ebenfalls NOT NULL ist, hat jedes Ereignis genau eine Übereinstimmung. Der Join kann das Ergebnis daher nicht verändern, und der Optimizer kann event_id direkt aus user_events lesen.

Der frühere Primär- und Fremdschlüssel-Blog zeigt den damit verbundenen LEFT OUTER JOIN-Fall, der nur die Garantie „höchstens eine“ benötigt. Dieses INNER JOIN-Beispiel benötigt zudem den Fremdschlüssel, um festzustellen, dass eine Übereinstimmung existiert.

Mit NORELY-Beschränkungen behält der optimierte Plan den Join bei:

Wenn beide Beschränkungen RELY verwenden, weiß der Optimizer, dass jedes Ereignis genau einen übereinstimmenden Benutzer hat, und kann den Join entfernen:

In diesem Beispiel ist user_events.username zwar NOT NULL, aber die Optimierung greift auch, wenn es NULL-Werte zulässt: Databricks entfernt den Join und fügt einen IS NOT NULL-Filter auf dem user_events.username hinzu, um die Behandlung von NULL-Werten des Inner Joins beizubehalten.

Wir haben diese Optimierung am TPC-DS SF10000-Dataset getestet, das einem Skalierungsfaktor von 10 TB entspricht. Bei der Tabelle store_sales mit 28,8 Milliarden Zeilen verknüpft mit der Tabelle customer mit 65 Millionen Zeilen haben wir customer.c_customer_sk als UNIQUE deklariert und einen Fremdschlüssel von store_sales.ss_customer_sk definiert. Mit RELY für beide Beschränkungen hat Databricks den Join eliminiert, über 99,99 % des Shuffle-Aufwands entfernt und die mittlere Ausführungszeit der Engine von 23,6 Sekunden auf 3,0 Sekunden reduziert – eine Beschleunigung um den Faktor 7,9.

DISTINCT überspringen, wenn Werte bereits eindeutig sind

BI-Tools und Abfragegeneratoren fügen DISTINCT häufig vorsichtshalber hinzu, da Joins doppelte Zeilen erzeugen können. Wenn die ausgewählten Werte bereits eindeutig sind, entfernt DISTINCT zwar nichts, erfordert aber dennoch möglicherweise eine Aggregation und ein Shuffle, es sei denn, der Optimizer kann nachweisen, dass der Vorgang unnötig ist. Eine zugrunde gelegte UNIQUE-Beschränkung kann diesen Nachweis erbringen.

Betrachten wir eine user_identifiers-Tabelle mit diesen beiden Beschränkungen:

• UNIQUE (user_id) RELY, wobei user_id NOT NULL ist
• UNIQUE (optional_identifier) RELY, wobei optional_identifier NULL-Werte zulässt

Die Tabelle enthält die folgenden Daten, die beide Beschränkungen erfüllen:

Für user_id kann der Optimizer die Eindeutigkeits- und Nicht-NULL-Garantien gemeinsam nutzen:

Der optimierte Plan enthält keine Aggregation:

Die Aggregation ist verschwunden, da die zugrunde gelegte Beschränkung sicherstellt, dass jedes user_id bereits eindeutig ist.

Die entsprechende Abfrage über optional_identifier zeigt, warum der Optimizer auch die NULL-Fähigkeit berücksichtigt:

Der optimierte Plan behält die Aggregation bei:

Beide Beschränkungen verwenden RELY, aber die NULL-Werte zulassende Beschränkung lässt die beiden oben gezeigten NULL-Zeilen zu. SQL DISTINCT muss diese Zeilen zu einer zusammenfassen, weshalb die Aggregation weiterhin erforderlich ist.

NULL-Werte zulassende UNIQUE-Beschränkungen können die Optimierung dennoch ermöglichen, wenn die Abfrage die fehlende Nicht-NULL-Garantie liefert:

Der Optimizer kann die Aggregation entfernen:

Der Filter entfernt die Werte, die sich unter der Semantik von NULL-Werten zulassenden UNIQUE wiederholen könnten. Für die verbleibenden Werte wird garantiert, dass sie eindeutig sind, sodass Databricks die Aggregation erneut vermeiden kann.

Wir haben diese Optimierung am selben TPC-DS SF10000-Dataset getestet, das einem Skalierungsfaktor von 10 TB entspricht. In der Tabelle store_sales mit 28,8 Milliarden Zeilen haben wir (ss_item_sk, ss_ticket_number) als zusammengesetzte UNIQUE-Beschränkung deklariert und eine DISTINCT-Abfrage über diese Spalten ausgeführt. Mit RELY hat Databricks die DISTINCT-Aggregation eliminiert, über 99,99 % des Shuffle-Aufwands entfernt und die mittlere Ausführungszeit der Engine von 33,7 Sekunden auf 2,8 Sekunden reduziert – eine Beschleunigung um den Faktor 12,0.

Validieren Sie Ihre Daten, bevor der Optimizer eine Beschränkung verwendet

Doppelte und verwaiste Zeilen finden

Bevor Sie RELY verwenden, überprüfen Sie, ob die Daten die Garantie erfüllen. Schließen Sie bei einer NULL-Werte zulassenden UNIQUE-Beschränkung Zeilen mit NULL aus, da UNIQUE das Wiederholen dieser Zeilen erlaubt. Diese Abfrage findet doppelte Nicht-NULL-Kennungen:

Für eine zusammengesetzte UNIQUE Constraint gruppieren Sie nach jeder eingeschränkten Spalte und fügen für jede Spalte, die Nullwerte zulässt, eine IS NOT NULL-Bedingung hinzu. Sie können untergeordnete Zeilen ohne passenden übergeordneten Eintrag auch mithilfe eines Left Anti Joins finden:

Diese Prüfungen validieren die aktuellen Daten. Ihre Ingestion- oder Datenqualitäts-Pipeline muss die Garantien auch nach dem Aktivieren von RELY weiterhin aufrechtzuerhalten.

Die deklarierten Constraints bestätigen

Nachdem Sie einen Constraint deklariert oder neu erstellt haben, können Sie dessen Definition auf verschiedene Weise überprüfen:

• SHOW CREATE TABLE
• DESCRIBE TABLE EXTENDED
• Unity Catalog INFORMATION_SCHEMA-Relationen:
  • TABLE_CONSTRAINTS
  • KEY_COLUMN_USAGE
  • CONSTRAINT_TABLE_USAGE
  • CONSTRAINT_COLUMN_USAGE
  • REFERENTIAL_CONSTRAINTS

Vollständige Syntax zum Erstellen, Ändern, Prüfen und Löschen finden Sie unter Constraints auf Databricks und in der CONSTRAINT-Klausel.

UNIQUE und RELY schrittweise einführen

Sie können UNIQUE schrittweise übernehmen:

1. Deklarieren Sie bestehende Eindeutigkeitsgarantien mit dem Standardwert NORELY.
2. Referenzieren Sie eindeutige Schlüssel über Fremdschlüssel, wenn die Beziehung Teil des Schemas ist.
3. Validieren und pflegen Sie Eindeutigkeit und referenzielle Integrität in Ihren Daten-Pipelines kontinuierlich.
4. Verwenden Sie RELY für den UNIQUE-Constraint erst nach der Überprüfung der Eindeutigkeit und für den Fremdschlüssel erst nach der Bestätigung, dass jeder anwendbare untergeordnete Wert eine passende übergeordnete Zeile hat.
5. Vergleichen Sie Pläne mit EXPLAIN, um zulässige Neuerstellungen zu bestätigen.

Fazit

UNIQUE-Constraints ermöglichen es einer Tabelle, neben ihrem einzelnen, nichtnullable PRIMARY KEY mehrere Eindeutigkeitsgarantien aufzuzeichnen, und die eingeschränkten Spalten können Nullwerte zulassen. Fremdschlüssel können sowohl auf einen UNIQUE-Constraint als auch auf einen Primärschlüssel verweisen. UNIQUE-Constraints sind standardmäßig informativ; nach der Validierung ermöglicht RELY Databricks, diese zu nutzen, um berechtigte Joins und DISTINCT-Abfragen zu optimieren.

(Dieser Blogbeitrag wurde mit KI-gestützten Tools übersetzt.) Originalbeitrag