I vincoli di chiave primaria e chiave esterna sono disponibili a livello generale e ora consentono query più veloci

Databricks è entusiasta di annunciare la disponibilità generale (GA) dei vincoli di chiave primaria (PK) e chiave esterna (FK), a partire da Databricks Runtime 15.2 e Databricks SQL 2024.30. Questa versione segue un'anteprima pubblica di grande successo, adottata da centinaia di clienti attivi ogni settimana, e rappresenta un'ulteriore pietra miliare significativa nel miglioramento dell'integrità dei dati e della gestione dei dati relazionali all'interno del Lakehouse.

Inoltre, Databricks può ora utilizzare questi vincoli per ottimizzare le query ed eliminare operazioni non necessarie dal piano di query, offrendo prestazioni molto più veloci.

Vincoli di chiave primaria e chiave esterna

Le chiavi primarie (PK) e le chiavi esterne (FK) sono elementi essenziali nei database relazionali e fungono da elementi costitutivi fondamentali per la modellazione dei dati. Forniscono informazioni sulle relazioni dei dati nello schema a utenti, strumenti e applicazioni e consentono ottimizzazioni che sfruttano i vincoli per velocizzare le query. Le chiavi primarie ed esterne sono ora generalmente disponibili per le tabelle Delta Lake ospitate in Unity Catalog.

Linguaggio SQL

È possibile definire i vincoli quando si crea una tabella:

Nell'esempio precedente, definiamo un vincolo di chiave primaria sulla colonna UserID. Databricks supporta anche i vincoli su gruppi di colonne.

È inoltre possibile modificare le tabelle Delta esistenti per aggiungere o rimuovere vincoli:

Qui creiamo la chiave primaria denominata products_pk sulla colonna non nullable ProductID in una tabella esistente. Per eseguire correttamente questa operazione, è necessario essere il proprietario della tabella. Si noti che i nomi dei vincoli devono essere univoci all'interno dello schema.
Il comando successivo rimuove la chiave primaria specificando il nome.

Lo stesso processo si applica alle chiavi esterne. La tabella seguente definisce due chiavi esterne al momento della creazione della tabella:

Fare riferimento alla documentazione sulle istruzioni CREATE TABLE e ALTER TABLE per maggiori dettagli sulla sintassi e sulle operazioni relative ai vincoli.

I vincoli di chiave primaria e chiave esterna non vengono applicati nel motore Databricks, ma possono essere utili per indicare una relazione di integrità dei dati che si intende mantenere vera. Databricks può invece applicare i vincoli di chiave primaria a monte come parte della pipeline di inserimento. Vedere Qualità dei dati gestita con Delta Live Tables per ulteriori informazioni sui vincoli applicati. Databricks supporta anche i vincoli NOT NULL e CHECK applicati (vedere la documentazione sui vincoli per ulteriori informazioni).

Ecosistema dei partner

Strumenti e applicazioni come l'ultima versione di Tableau e PowerBI possono importare e utilizzare automaticamente le relazioni di chiave primaria e chiave esterna da Databricks tramite connettori JDBC e ODBC.

Visualizzare i vincoli

Esistono diversi modi per visualizzare i vincoli di chiave primaria e chiave esterna definiti nella tabella. È anche possibile utilizzare semplicemente i comandi SQL per visualizzare le informazioni sui vincoli con il comando DESCRIBE TABLE EXTENDED:

Catalog Explorer e diagramma entità-relazione

È inoltre possibile visualizzare le informazioni sui vincoli tramite il Catalog Explorer:

Ogni colonna di chiave primaria e chiave esterna ha una piccola icona a forma di chiave accanto al suo nome.

Ed è possibile visualizzare le informazioni sulla chiave primaria e sulla chiave esterna e le relazioni tra le tabelle con il diagramma entità-relazione in Catalog Explorer. Di seguito è riportato un esempio di una tabella purchases che fa riferimento a due tabelle, users e products:

INFORMATION SCHEMA

Le seguenti tabelle INFORMATION_SCHEMA forniscono anche informazioni sui vincoli:

• TABLE_CONSTRAINTS: descrive i metadati per tutti i vincoli di chiave primaria e chiave esterna all'interno del catalogo.
• KEY_COLUMN_USAGE: elenca le colonne dei vincoli di chiave primaria o chiave esterna all'interno del catalogo.
• CONSTRAINT_TABLE_USAGE: descrive i vincoli che fanno riferimento alle tabelle nel catalogo.
• CONSTRAINT_COLUMN_USAGE: descrive i vincoli che fanno riferimento alle colonne nel catalogo.
• REFERENTIAL_CONSTRAINTS: descrive i vincoli referenziali (chiave esterna) definiti nel catalogo.

Utilizzare l'opzione RELY per abilitare le ottimizzazioni

Se si sa che il vincolo di chiave primaria è valido (ad esempio, perché la pipeline di dati o il processo ETL lo applica), è possibile abilitare le ottimizzazioni basate sul vincolo specificandolo con l'opzione RELY, come:

L'utilizzo dell'opzione RELY consente a Databricks di ottimizzare le query in modi che dipendono dalla validità del vincolo, perché si garantisce che l'integrità dei dati venga mantenuta. Prestare attenzione in questo caso perché se un vincolo è contrassegnato come RELY ma i dati violano il vincolo, le query potrebbero restituire risultati errati.

Quando non si specifica l'opzione RELY per un vincolo, il valore predefinito è NORELY, nel qual caso i vincoli possono comunque essere utilizzati per scopi informativi o statistici, ma le query non si baseranno su di essi per essere eseguite correttamente.

L'opzione RELY e le ottimizzazioni che la utilizzano sono attualmente disponibili per le chiavi primarie e saranno presto disponibili anche per le chiavi esterne.

È possibile modificare la chiave primaria di una tabella per modificare se è RELY o NORELY utilizzando ALTER TABLE, ad esempio:

Velocizzare le query eliminando le aggregazioni non necessarie

Una semplice ottimizzazione che possiamo fare con i vincoli di chiave primaria RELY è l'eliminazione di aggregazioni non necessarie. Ad esempio, in una query che applica un'operazione distinct su una tabella con una chiave primaria utilizzando RELY:

Possiamo rimuovere l'operazione DISTINCT non necessaria:

Come si può vedere, questa query si basa sulla validità del vincolo di chiave primaria RELY: se ci sono ID cliente duplicati nella tabella customer, la query trasformata restituirà risultati duplicati errati. L'utente è responsabile dell'applicazione della validità del vincolo se imposta l'opzione RELY.

Se la chiave primaria è NORELY (il valore predefinito), l'ottimizzatore non rimuoverà l'operazione DISTINCT dalla query. Quindi potrebbe essere eseguita più lentamente, ma restituisce sempre risultati corretti anche in presenza di duplicati. Se la chiave primaria è RELY, Databricks può rimuovere l'operazione DISTINCT, il che può accelerare notevolmente la query, di circa 2 volte per l'esempio precedente.

Velocizzare le query eliminando i join non necessari

Un'altra ottimizzazione molto utile che possiamo eseguire con le chiavi primarie RELY è l'eliminazione dei join non necessari. Se una query unisce una tabella a cui non si fa riferimento da nessuna parte tranne che nella condizione di join, l'ottimizzatore può determinare che il join non è necessario e rimuovere il join dal piano di query.

Per fare un esempio, supponiamo di avere una query che unisce due tabelle, store_sales e customer, unite sulla chiave primaria della tabella customer PRIMARY KEY (c_customer_sk) RELY.

Se non avessimo la chiave primaria, ogni riga di store_sales potrebbe potenzialmente corrispondere a più righe in customer e dovremmo eseguire il join per calcolare il valore SUM corretto. Ma poiché la tabella customer è unita alla sua chiave primaria, sappiamo che il join restituirà una riga per ogni riga di store_sales.

Quindi la query ha effettivamente bisogno solo della colonna ss_quantity dalla tabella dei fatti store_sales. Pertanto, l'ottimizzatore di query può eliminare completamente il join dalla query, trasformandola in:

Questo viene eseguito molto più velocemente evitando l'intero join: in questo esempio osserviamo che l'ottimizzazione accelera la query da 1,5 minuti a 6 secondi!. E i vantaggi possono essere ancora maggiori quando il join coinvolge molte tabelle che possono essere eliminate!

Potresti chiedere, perché qualcuno dovrebbe eseguire una query come questa? In realtà è molto più comune di quanto si possa pensare! Un motivo comune è che gli utenti costruiscono viste che uniscono diverse tabelle, come l'unione di molte tabelle di fatti e dimensioni. Scrivono query su queste viste che spesso utilizzano colonne solo da alcune delle tabelle, non da tutte, e quindi l'ottimizzatore può eliminare i join rispetto alle tabelle che non sono necessarie in ogni query. Questo modello è comune anche in molti strumenti di Business Intelligence (BI), che spesso generano query che uniscono molte tabelle in uno schema anche quando una query utilizza solo colonne di alcune delle tabelle.

Conclusione

Dalla sua anteprima pubblica, oltre 2600 clienti Databricks hanno utilizzato i vincoli di chiave primaria e chiave esterna. Oggi siamo entusiasti di annunciare la disponibilità generale di questa funzionalità, che segna una nuova fase nel nostro impegno per migliorare la gestione e l'integrità dei dati in Databricks.

Inoltre, Databricks ora sfrutta i vincoli di chiave con l'opzione RELY per ottimizzare le query, ad esempio eliminando aggregazioni e join non necessari, con conseguente prestazioni delle query molto più veloci.

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