Utilizzare chiavi primarie ed esterne

Le chiavi primarie ed 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 della chiave primaria di una tabella a cui viene fatto riferimento o è NULL.

Le chiavi primarie ed esterne 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 sulle tabelle, devi assicurarti che i dati siano conformi a questi vincoli. Per garantire l'unicità dei valori all'interno di una colonna, valuta la possibilità di utilizzare una colonna Identity. BigQuery può utilizzare i vincoli di tabella per ottimizzare le query.

Gestire i vincoli

Le relazioni con le chiavi primarie ed esterne possono essere create e gestite tramite le seguenti istruzioni DDL:

Puoi anche gestire i vincoli di tabella tramite l'API BigQuery aggiornando l' TableConstraints oggetto.

Visualizzare i vincoli

Le seguenti viste forniscono informazioni sui vincoli di tabella:

Ottimizzare le query

Quando crei e applichi chiavi primarie ed esterne nelle tabelle, BigQuery può utilizzare queste informazioni per eliminare o ottimizzare determinati join di query. Sebbene sia possibile simulare 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 singola 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;

Eliminare i join esterni

Per rimuovere un LEFT OUTER JOIN, le chiavi di join sul lato destro devono essere univoche e vengono 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 da la 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 (possibilmente NULL) nella tabella customer per ogni riga della tabella store_sales e il LEFT OUTER JOIN può essere eliminato:

SELECT ss.*
FROM mydataset.store_sales;

Riordinare i join

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

Limitazioni

Le chiavi primarie ed esterne sono soggette alle seguenti limitazioni:

  • I vincoli di chiave non vengono applicati in BigQuery. È tua responsabilità mantenere i vincoli in ogni 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 ed esterne devono essere di uno dei seguenti tipi: BIGNUMERIC, BOOLEAN, BYTES, DATE, DATETIME, INT64, NUMERIC, STRING, o TIMESTAMP.
  • Le chiavi primarie ed esterne possono essere impostate solo sulle colonne di primo livello.
  • Le chiavi primarie non possono essere denominate.
  • Non è possibile rinominare le tabelle con vincoli di chiave primaria.
  • Una tabella può avere fino a 64 chiavi esterne.
  • Una chiave esterna non può fare riferimento a una colonna nella stessa tabella.
  • Non è possibile rinominare o modificare il tipo dei campi che fanno parte di vincoli di chiave primaria o di chiave esterna.
  • Se copi, cloni, ripristini, o crei uno snapshot di 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 di tabella.

Passaggi successivi