Utilizzare chiavi primarie ed esterne

Le chiavi primarie e le chiavi esterne sono vincoli di tabella che possono contribuire all'ottimizzazione delle query. Questo documento spiega come creare, visualizzare e gestire i vincoli e come utilizzarli per ottimizzare le query.

BigQuery supporta i seguenti vincoli di chiave:

  • Chiave primaria: una chiave primaria per una tabella è una combinazione di una o più colonne univoca per ogni riga e non NULL.
  • Chiave esterna: una chiave esterna per una tabella è una combinazione di una o più colonne presenti nella colonna di chiave primaria di una tabella a cui viene fatto riferimento oppure è NULL.

Le chiavi primaria ed esterna vengono in genere utilizzate per garantire l'integrità dei dati e consentire l'ottimizzazione delle query. BigQuery non applica i vincoli di chiave primaria ed esterna. Quando dichiari i vincoli nelle tabelle, devi assicurarti che i tuoi dati siano conformi. BigQuery può utilizzare i vincoli delle tabelle per ottimizzare le query.

Gestire i vincoli

Le relazioni di chiave primaria ed esterna possono essere create e gestite tramite le seguenti istruzioni DDL:

Puoi anche gestire i vincoli delle tabelle tramite l'API BigQuery aggiornando l'oggetto TableConstraints.

Visualizza vincoli

Le seguenti visualizzazioni forniscono informazioni sui vincoli della tabella:

Ottimizza le query

Quando crei e applichi chiavi primarie ed esterne alle tabelle, BigQuery può utilizzare queste informazioni per eliminare o ottimizzare determinati join di query. Sebbene sia possibile imitare queste ottimizzazioni riscrivendo le query, queste riscritture non sono sempre pratiche.

In un ambiente di produzione, potresti creare viste che uniscono molte tabelle dei fatti e delle dimensioni. Gli sviluppatori possono eseguire query sulle viste anziché sulle tabelle sottostanti e riscrivere manualmente i join ogni volta. Se definisci i vincoli corretti, le ottimizzazioni dei join vengono eseguite automaticamente per tutte le query a cui si applicano.

Gli esempi nelle sezioni seguenti fanno riferimento alle tabelle store_sales e customer con vincoli:

CREATE TABLE mydataset.customer (customer_name STRING PRIMARY KEY NOT ENFORCED);

CREATE TABLE mydataset.store_sales (
    item STRING PRIMARY KEY NOT ENFORCED,
    sales_customer STRING REFERENCES mydataset.customer(customer_name) NOT ENFORCED,
    category STRING);

Eliminare i join interni

Considera la seguente query che contiene un INNER JOIN:

SELECT ss.*
FROM mydataset.store_sales AS ss
    INNER JOIN mydataset.customer AS c
    ON ss.sales_customer = c.customer_name;

La colonna customer_name è una chiave primaria nella tabella customer, quindi ogni riga della tabella store_sales ha una sola corrispondenza o nessuna corrispondenza se sales_customer è NULL. Poiché la query seleziona solo le colonne della tabella store_sales, l'ottimizzatore di query può eliminare il join e riscrivere la query come segue:

SELECT *
FROM mydataset.store_sales
WHERE sales_customer IS NOT NULL;

Elimina i join esterni

Per rimuovere un LEFT OUTER JOIN, le chiavi di unione sul lato destro devono essere univoche e devono essere selezionate solo le colonne sul lato sinistro. Considera la seguente query:

SELECT ss.*
FROM mydataset.store_sales ss
    LEFT OUTER JOIN mydataset.customer c
    ON ss.category = c.customer_name;

In questo esempio, non esiste alcuna relazione tra category e customer_name. Le colonne selezionate provengono solo dalla tabella store_sales e la chiave di join customer_name è una chiave primaria nella tabella customer, quindi ogni valore è univoco. Ciò significa che esiste esattamente una corrispondenza (forse NULL) nella tabella customer per ogni riga della tabella store_sales e che LEFT OUTER JOIN può essere eliminato:

SELECT ss.*
FROM mydataset.store_sales;

Riordina unioni

Quando BigQuery non può eliminare un join, può utilizzare i vincoli della tabella per ottenere informazioni sulle cardinalità del join e ottimizzare l'ordine in cui eseguire i join.

Limitazioni

Le chiavi primarie e le chiavi esterne sono soggette alle seguenti limitazioni:

  • I vincoli di chiave non vengono applicati in BigQuery. Sei responsabile del mantenimento dei vincoli in qualsiasi momento. Le query sulle tabelle con vincoli violati potrebbero restituire risultati errati.
  • Le chiavi primarie non possono superare le 16 colonne.
  • Le chiavi esterne devono avere valori presenti nella colonna della tabella a cui viene fatto riferimento. Questi valori possono essere NULL.
  • Le chiavi primarie e le chiavi esterne devono essere di uno dei seguenti tipi: BIGNUMERIC, BOOLEAN, DATE, DATETIME, INT64, NUMERIC, STRING, o TIMESTAMP.
  • Le chiavi primarie e le chiavi esterne possono essere impostate solo sulle colonne di primo livello.
  • Le chiavi primarie non possono essere denominate.
  • Le tabelle con vincoli di chiave primaria non possono essere rinominate.
  • Una tabella può avere fino a 64 chiavi esterne.
  • Una chiave esterna non può fare riferimento a una colonna della stessa tabella.
  • I campi che fanno parte di vincoli di chiave primaria o di chiave esterna non possono essere rinominati o il loro tipo non può essere modificato.
  • Se copi, cloni, ripristini, o istantanea una tabella senza l'opzione -a o --append_table, i vincoli della tabella di origine vengono copiati e sovrascritti nella tabella di destinazione. Se utilizzi l'opzione -a o --append_table, solo i record della tabella di origine vengono aggiunti alla tabella di destinazione senza i vincoli della tabella.

Passaggi successivi