Definisci e ottimizza con vincoli UNIQUE su Databricks

I team di dati mantengono spesso garanzie di unicità nelle loro pipeline, ma senza un modo flessibile per rappresentarle tutte nello schema della tabella, gli utenti e gli strumenti non possono rilevarle e l'ottimizzatore delle query non può sfruttarle in sicurezza. Le entità di business hanno spesso più di un valore, o una combinazione di valori, noti per essere unici: una tabella users potrebbe utilizzare user_id come identificatore di riga primario, mentre il suo modello di dati registra anche garanzie di unicità separate per username, un indirizzo email e (workspace_id, external_user_id).

Databricks supporta già i vincoli PRIMARY KEY e FOREIGN KEY: una chiave primaria identifica le righe di una tabella e le chiavi esterne possono farvi riferimento per descrivere le relazioni. Tuttavia, una tabella può avere una sola chiave primaria e le sue colonne non devono ammettere valori nulli. I vincoli UNIQUE, ora disponibili in Anteprima pubblica per le tabelle Unity Catalog Delta in Databricks SQL e Databricks Runtime 18.2 e versioni successive, aggiungono un modo per registrare le altre garanzie di unicità. Una tabella può dichiarare più vincoli UNIQUE, le loro colonne possono ammettere valori nulli e le chiavi esterne possono farvi riferimento. Dopo aver convalidato questi vincoli e averli contrassegnati con RELY, l'ottimizzatore delle query può anche utilizzarli per eliminare join e aggregazioni non necessari.

Quando una chiave primaria non è sufficiente per ogni colonna unica

PRIMARY KEY e UNIQUE registrano entrambi l'unicità, ma svolgono ruoli diversi nello schema:

Includi le garanzie di unicità della tua pipeline nello schema

La tabella seguente ha una chiave primaria e due vincoli UNIQUE:

I due vincoli di unicità illustrano diversi tipi di unicità. users_external_uq si applica a una combinazione di colonne: ciascuna coppia (workspace_id, external_user_id) deve essere unica, anche se ciascun valore può comparire in altre coppie. users_optional_id_uq si applica a una colonna che ammette valori nulli. Le righe seguenti mostrano quali valori soddisfano tale garanzia:

Registra la garanzia senza costi a ogni scrittura

Analogamente ai vincoli di chiave primaria ed estera su Databricks, i vincoli UNIQUE sono informativi e non vengono applicati durante la scrittura dei dati. La dichiarazione di UNIQUE (optional_identifier), ad esempio, non causa il fallimento di una scrittura contenente valori non nulli duplicati.

Questa separazione consente allo schema di registrare una garanzia di unicità gestita da una pipeline di ingestione, da un sistema monte o da un altro processo di qualità dei dati. La garanzia diventa inoltre metadato della tabella che utenti, strumenti e applicazioni possono ispezionare.

Fa' riferimento a un vincolo UNIQUE da una chiave esterna

Una chiave esterna può fare riferimento a un vincolo UNIQUE oltre che a una chiave primaria. Ad esempio, è possibile fare riferimento al vincolo composito UNIQUE dichiarato sopra come segue:

Le colonne padre di riferimento identificano il vincolo di unicità a cui mira la chiave esterna. Le colonne figlio e padre corrispondono per posizione: workspace_id mappa a workspace_id e source_user_id mappa a external_user_id. I loro nomi non devono necessariamente coincidere, ma i tipi e le posizioni devono corrispondere. Le colonne (padre) REFERENCES devono essere elencate nello stesso ordine delle colonne nella definizione del vincolo UNIQUE; in caso contrario, il vincolo rimane valido ma l'eliminazione dei join non verrà attivata silenziosamente.

Per fare riferimento a un vincolo UNIQUE a colonna singola, assegna un nome esplicito alla sua colonna padre, ad esempio REFERENCES users (username). Se l'elenco delle colonne padre viene omesso, REFERENCES users punta invece alla chiave primaria della tabella.

Ottimizza le query con RELY

Per impostazione predefinita, un vincolo di unicità viene creato con NORELY. Registra la semantica dello schema prevista, ma Databricks non lo utilizza per la riscrittura delle query basata sulla correttezza. L'istruzione seguente crea un vincolo con questa impostazione predefinita:

Dopo aver verificato i dati e assicurato che le tue pipeline continueranno a mantenere la garanzia, puoi eliminare e ricreare il vincolo con RELY:

In questo esempio, CASCADE rimuove anche tutte le chiavi esterne che fanno riferimento a users_username_uq, e queste devono essere ricreate dopo che il vincolo di unicità è stato riaggiunto. Senza CASCADE, DROP CONSTRAINT utilizza il comportamento predefinito RESTRICT e viene rifiutato se esistono chiavi esterne dipendenti.

Una chiave esterna che fa riferimento al vincolo di unicità può anche essere dichiarata con RELY:

RELY viene impostato indipendentemente su ciascun vincolo. Non convalida i dati esistenti né applica il vincolo sulle scritture future. Consente a Databricks di assumere che il vincolo sia valido durante l'ottimizzazione delle query. Se i dati violano un vincolo su cui si fa affidamento, una riscrittura basata su tale vincolo potrebbe produrre risultati non corretti. Dovresti pertanto utilizzare RELY solo per le garanzie che vengono convalidate e mantenute continuamente.

Con RELY, i vincoli di unicità possono fornire le informazioni necessarie per i riscrivi di join e aggregazione idonei. L'idoneità dipende anche da altre garanzie richieste dalla query, tra cui la nullabilità, come illustrato negli esempi seguenti. Questo supporto per l'ottimizzazione è disponibile sul compute abilitato per Photon. Il blog sulle chiavi primarie ed esterne descrive le ottimizzazioni di join e aggregazione basate sui vincoli che anche i vincoli UNIQUE possono abilitare. Da quando è stato pubblicato quel post, il supporto per RELY è diventato disponibile per le chiavi esterne e ora anche per i vincoli UNIQUE.

Elimina i join utilizzando i vincoli UNIQUE e chiave esterna

Alcune dashboard basate sugli stessi dati richiedono attributi da users, quindi i team spesso creano una vista condivisa che unisce sempre user_events con users. Una dashboard che necessita solo di informazioni da user_events potrebbe comunque ereditare quel join dalla vista. Il suo SQL generato può quindi assomigliare alla query sottostante, anche se non seleziona alcuna colonna da users:

Sebbene la query non restituisca alcuna colonna da users, l'ottimizzatore normalmente non può rimuovere il join. Un inner join potrebbe rimuovere un evento se non esiste un utente corrispondente, o duplicarlo se più utenti hanno lo stesso nome utente.

I vincoli su cui si fa affidamento escludono entrambe le possibilità. Il vincolo UNIQUE garantisce al massimo un utente corrispondente, mentre la chiave esterna garantisce che esista una corrispondenza. Poiché user_events.username è anche NOT NULL, ogni evento ha esattamente una corrispondenza. Il join quindi non può modificare il risultato e l'ottimizzatore può leggere event_id direttamente da user_events.

Il precedente blog sulle chiavi primarie ed esterne dimostra il caso correlato LEFT OUTER JOIN, che richiede solo la garanzia di al-most-one. Anche questo esempio INNER JOIN richiede la chiave esterna per stabilire che esiste una corrispondenza.

Con i vincoli NORELY, il piano ottimizzato mantiene il join:

Quando entrambi i vincoli utilizzano RELY, l'ottimizzatore può sapere che ogni evento ha esattamente un utente corrispondente e rimuovere il join:

In questo esempio, user_events.username è NOT NULL, ma l'ottimizzazione si applica anche quando è nullable: Databricks rimuoverà il join e aggiungerà un filtro IS NOT NULL su user_events.username per preservare la gestione dei valori nulli dell'inner join.

Abbiamo eseguito il benchmark di questa ottimizzazione sul dataset TPC-DS SF10000, che rappresenta un fattore di scala di 10 TB. Sulla tabella di 28,8 miliardi di righe store_sales unita alla tabella di 65 milioni di righe customer, abbiamo dichiarato customer.c_customer_sk come UNIQUE e definito una chiave esterna da store_sales.ss_customer_sk. Con RELY su entrambi i vincoli, Databricks ha eliminato il join, rimosso oltre il 99,99% dello shuffle e ridotto il tempo di esecuzione medio del motore da 23,6 secondi a 3,0 secondi, un'accelerazione di 7,9x.

Salta DISTINCT quando i valori sono già unici

Gli strumenti BI e i generatori di query spesso aggiungono DISTINCT in modo difensivo perché i join possono produrre righe duplicate. Quando i valori selezionati sono già unici, DISTINCT non rimuove nulla, ma può comunque richiedere un'aggregazione e uno shuffle a meno che l'ottimizzatore non possa dimostrare che l'operazione non è necessaria. Un vincolo UNIQUE su cui si fa affidamento può fornire tale prova.

Considera una tabella user_identifiers con questi due vincoli:

• UNIQUE (user_id) RELY, dove user_id è NOT NULL
• UNIQUE (optional_identifier) RELY, dove optional_identifier è nullable

La tabella contiene i seguenti dati, che soddisfano entrambi i vincoli:

Per user_id, l'ottimizzatore può utilizzare insieme le garanzie di unicità e non-null:

Il piano ottimizzato non contiene alcuna aggregazione:

L'aggregazione è scomparsa perché il vincolo su cui si fa affidamento stabilisce che ogni user_id è già distinto.

La query equivalente su optional_identifier mostra perché l'ottimizzatore considera anche la nullabilità:

Il piano ottimizzato mantiene l'aggregazione:

Entrambi i vincoli utilizzano RELY, ma il vincolo nullable consente le due righe NULL mostrate sopra. SQL DISTINCT deve comprimere tali righe in una sola, quindi l'aggregazione è ancora necessaria.

I vincoli UNIQUE nullable possono comunque abilitare l'ottimizzazione quando la query fornisce la garanzia di non-null mancante:

L'ottimizzatore può rimuovere l'aggregazione:

Il filtro rimuove i valori che potrebbero ripetersi con la semantica UNIQUE nullable. I valori rimanenti sono garantiti come distinti, quindi Databricks può nuovamente evitare l'aggregazione.

Abbiamo eseguito il benchmark di questa ottimizzazione sullo stesso dataset TPC-DS SF10000, che rappresenta un fattore di scala di 10 TB. Sulla tabella di 28,8 miliardi di righe store_sales, abbiamo dichiarato (ss_item_sk, ss_ticket_number) come vincolo composito UNIQUE e abbiamo eseguito una query DISTINCT su tali colonne. Con RELY, Databricks ha eliminato l'aggregazione DISTINCT, rimosso oltre il 99,99% dello shuffle e ridotto il tempo di esecuzione medio del motore da 33,7 secondi a 2,8 secondi, un'accelerazione di 12,0x.

Valida i tuoi dati prima che l'ottimizzatore utilizzi un vincolo

Trova righe duplicate e orfane

Prima di utilizzare RELY, verifica che i dati soddisfino la garanzia. Per un vincolo UNIQUE nullable, escludi le righe contenenti NULL poiché UNIQUE consente a tali righe di ripetersi. Questa query trova identificatori non null duplicati:

Per un vincolo UNIQUE composito, raggruppa per ogni colonna vincolata e aggiungi una condizione IS NOT NULL per ciascuna colonna che ammette valori nulli. Puoi anche trovare le righe figlio senza un genitore corrispondente usando un left anti join:

Questi controlli convalidano i dati correnti. La tua pipeline di ingestione o di qualità dei dati deve continuare a mantenere le garanzie dopo aver abilitato RELY.

Conferma i vincoli dichiarati

Dopo aver dichiarato o ricreato un vincolo, puoi confermarne la definizione in diversi modi:

• SHOW CREATE TABLE
• DESCRIBE TABLE EXTENDED
• Relazioni di Unity Catalog INFORMATION_SCHEMA:
  • TABLE_CONSTRAINTS
  • KEY_COLUMN_USAGE
  • CONSTRAINT_TABLE_USAGE
  • CONSTRAINT_COLUMN_USAGE
  • REFERENTIAL_CONSTRAINTS

Vedi Vincoli su Databricks e la clausola CONSTRAINT per la sintassi completa di creazione, modifica, controllo ed eliminazione.

Implementa UNIQUE e RELY in modo incrementale

Puoi adottare UNIQUE in modo incrementale:

1. Dichiara le garanzie di unicità esistenti con il valore predefinito NORELY.
2. Fai riferimento a chiavi univoche da chiavi esterne in cui la relazione fa parte dello schema.
3. Convalida e mantieni continuamente l'unicità e l'integrità referenziale nelle tue pipeline di dati.
4. Usa RELY sul vincolo UNIQUE solo dopo aver verificato l'unicità, e sulla chiave esterna solo dopo aver verificato che ogni valore figlio applicabile abbia una riga genitore corrispondente.
5. Confronta i piani con EXPLAIN per confermare i ricalcoli idonei.

Conclusione

I vincoli UNIQUE consentono a una tabella di registrare più garanzie di unicità oltre alla sua singola PRIMARY KEY non nulla, e le colonne vincolate possono ammettere valori nulli. Le chiavi esterne possono fare riferimento a un vincolo UNIQUE così come a una chiave primaria. I vincoli UNIQUE sono informativi per impostazione predefinita; dopo la convalida, RELY consente a Databricks di utilizzarli per ottimizzare i join idonei e le query DISTINCT.

(Questo post sul blog è stato tradotto utilizzando strumenti basati sull'intelligenza artificiale) Post originale