Specificare una colonna di identità

Questo documento descrive come creare e utilizzare le colonne di identità, a volte chiamate colonne con incremento automatico, che vengono utilizzate per creare e gestire le chiavi primarie nelle tabelle. Quando inserisci una riga in una tabella con una colonna di identità, BigQuery genera un valore intero univoco per quella colonna.

Panoramica

Una colonna di identità è una colonna INT64 compilata con valori univoci generati dal sistema.

Il caso d'uso principale per le colonne di identità è la generazione di chiavi primarie. Puoi anche generare chiavi primarie utilizzando la GENERATE_UUID funzione per generare stringhe univoche, ma le colonne di identità sono generalmente preferite per i seguenti motivi:

  • I valori interi richiedono meno spazio di archiviazione rispetto ai valori stringa.
  • L'utilizzo di numeri interi per i join di tabelle è più efficiente rispetto all'utilizzo di stringhe.

I valori di una colonna di identità vengono generati in base a un valore iniziale che definisce il primo valore e a un valore di incremento che definisce la differenza minima tra i valori generati successivamente.

I valori delle colonne di identità generate hanno le seguenti proprietà:

  • Univoco. I valori generati automaticamente sono univoci all'interno della tabella.
  • Ordinato in modo approssimativo. Non è garantito che i valori generati siano in ordine strettamente crescente o decrescente.
  • Sparso. Non è garantito che i valori generati siano consecutivi. Alcuni valori potrebbero essere saltati, ma i valori in una colonna di identità differiscono sempre di un multiplo dell'incremento specificato.

Limitazioni

  • Una tabella può avere al massimo una colonna di identità.
  • Puoi leggere le tabelle con colonne di identità utilizzando SQL precedente, ma non puoi scrivere nelle tabelle con colonne di identità utilizzando SQL precedente.
  • Non puoi utilizzare il clustering o il partizionamento su una colonna di identità.

  • Le seguenti operazioni di copia delle tabelle non sono supportate se una tabella di origine o di destinazione ha una colonna di identità:

    • Copia della tabella con disposizione di scrittura WRITE_APPEND o WRITE_TRUNCATE
    • Copia della tabella con più origini

  • Lo streaming di dati tramite l'API Storage Write (gRPC) o il tabledata.insertAll metodo API non è supportato per le tabelle con colonne di identità.

Creare colonne di identità

Puoi creare una colonna di identità quando crei una nuova tabella utilizzando l' CREATE TABLE istruzione DDL. Utilizza la clausola GENERATED AS IDENTITY per designare una colonna INT64 come colonna di identità. Una tabella può avere al massimo una colonna di identità. Puoi specificare una delle seguenti modalità di generazione che determina se puoi inserire manualmente i valori nella colonna di identità:

  • GENERATED ALWAYS AS IDENTITY: i valori vengono sempre generati dal sistema. Non puoi fornire un valore personalizzato quando inserisci o aggiorni i dati in questa colonna. Se non specifichi ALWAYS o BY DEFAULT, viene utilizzato ALWAYS.

  • GENERATED BY DEFAULT AS IDENTITY: puoi inserire o modificare i valori nella colonna di identità. BigQuery non applica l'unicità dei valori che inserisci o modifichi.

    Se ometti la colonna o fornisci NULL quando inserisci i dati, BigQuery genera automaticamente un valore. La colonna di identità non può contenere un valore NULL. Se vuoi utilizzare un valore generato in un'istruzione INSERT, MERGE o UPDATE, puoi utilizzare la parola chiave DEFAULT o NULL.

L'esempio seguente crea la tabella mydataset.id_table con una colonna di identità id che inizia da 0 e incrementa di 5:

CREATE TABLE mydataset.id_table (
  id INT64 GENERATED ALWAYS AS IDENTITY(START WITH 0 INCREMENT BY 5),
  data STRING
);

Aggiungere la proprietà della colonna di identità a una colonna

Per modificare una colonna esistente in modo da generare valori di identità, utilizza l' ALTER TABLE ALTER COLUMN SET GENERATED istruzione DDL. Questa istruzione modifica una colonna INT64 esistente in una colonna di identità. Non esegue il backfill dei valori per le righe esistenti nella colonna di identità.

Utilizzare le istruzioni DML con le colonne di identità

Puoi utilizzare le istruzioni DML come INSERT, MERGE e UPDATE con le colonne di identità. Le sezioni seguenti utilizzano la tabella mydataset.mytable che ha una colonna di identità denominata id e una colonna stringa denominata data:

CREATE OR REPLACE TABLE mydataset.mytable (
  id INT64 GENERATED BY DEFAULT AS IDENTITY(START WITH 100 INCREMENT BY 10),
  data STRING
);

Inserisci i dati

Quando inserisci i dati in una tabella con una colonna di identità, puoi omettere la colonna di identità dall'elenco delle colonne per generare un valore. L'istruzione INSERT seguente omette la colonna id, BigQuery genera i valori:

INSERT mydataset.mytable (data) VALUES ('A'), ('B'), ('C');

Il risultato è simile al seguente, anche se l'ordine di assegnazione dei valori generati alle righe potrebbe variare:

+-----+------+
| id  | data |
+-----+------+
| 110 | A    |
| 120 | B    |
| 100 | C    |
+-----+------+

Se una colonna di identità è definita con GENERATED BY DEFAULT AS IDENTITY, puoi specificare un valore personalizzato per la colonna. Puoi anche utilizzare la parola chiave DEFAULT o NULL per fare in modo che BigQuery generi un valore.

L'istruzione INSERT seguente fornisce un valore per una riga e utilizza DEFAULT o NULL per generare valori per le altre due righe:

INSERT mydataset.mytable (id, data)
VALUES (155, 'D'), (DEFAULT, 'E'), (NULL, 'F');

Il risultato è simile al seguente:

+-----+------+
| id  | data |
+-----+------+
| 110 | A    |
| 120 | B    |
| 100 | C    |
| 155 | D    |
| 140 | E    |
| 130 | F    |
+-----+------+

Se una colonna di identità è definita con GENERATED ALWAYS AS IDENTITY, puoi utilizzare solo la parola chiave DEFAULT per fare in modo che BigQuery generi un valore. Non puoi fornire un valore personalizzato o utilizzare NULL.

Unire i dati

Puoi utilizzare l' MERGEistruzione per unire i dati in una tabella con una colonna di identità. Se la colonna di identità utilizza la modalità di generazione GENERATED BY DEFAULT AS IDENTITY, puoi utilizzare le parole chiave DEFAULT o NULL per generare un valore quando inserisci o aggiorni i dati nell'ambito di un'istruzione MERGE.

L'esempio seguente unisce mydataset.source_table in mydataset.mytable, inserendo una nuova riga se non esiste una corrispondenza nella colonna data e aggiornando la colonna id a un nuovo valore generato se esiste una corrispondenza:

CREATE OR REPLACE TABLE mydataset.source_table(data STRING)
AS SELECT * FROM UNNEST(['A', 'C', 'G']);

MERGE mydataset.mytable T
USING mydataset.source_table S
ON T.data = S.data
WHEN MATCHED THEN
  UPDATE SET id = DEFAULT
WHEN NOT MATCHED THEN
  INSERT(data)
  VALUES(S.data);

Il risultato è simile al seguente:

+-----+------+
| id  | data |
+-----+------+
| 160 | A    |
| 120 | B    |
| 150 | C    |
| 155 | D    |
| 140 | E    |
| 130 | F    |
| 170 | G    |
+-----+------+

Se la colonna di identità utilizza la modalità di generazione GENERATED ALWAYS AS IDENTITY, non puoi includere la colonna di identità in nessuna clausola di aggiornamento dell'unione. Per utilizzare una clausola di inserimento dell'unione, puoi omettere la colonna di identità dall'elenco delle colonne o utilizzare la parola chiave DEFAULT.

Aggiorna i dati

Puoi utilizzare l' UPDATE istruzione per aggiornare i valori in una colonna di identità che utilizza la GENERATED BY DEFAULT AS IDENTITY modalità di generazione. Puoi utilizzare le parole chiave DEFAULT o NULL per generare un nuovo valore.

L'esempio seguente aggiorna tutti i valori nella colonna id ai valori appena generati:

UPDATE mydataset.mytable
SET id = NULL
WHERE TRUE;

Il risultato è simile al seguente:

+-----+------+
| id  | data |
+-----+------+
| 190 | A    |
| 210 | B    |
| 240 | C    |
| 230 | D    |
| 180 | E    |
| 200 | F    |
| 220 | G    |
+-----+------+

Se la colonna di identità utilizza la modalità di generazione GENERATED ALWAYS AS IDENTITY, non puoi aggiornare la colonna di identità.

Aggiungere a una tabella

Puoi utilizzare il comando bq query con il flag --append_table per aggiungere i risultati di una query a una tabella di destinazione con una colonna di identità. Se la query omette la colonna di identità, viene generato un valore.

L'esempio seguente aggiunge i dati solo per la colonna data a mydataset.mytable:

bq query \
    --nouse_legacy_sql \
    --append_table \
    --destination_table=mydataset.mytable \
    'SELECT "H" AS data'

A mydataset.mytable viene aggiunta una nuova riga con un valore id generato.

Carica dati

Puoi caricare i dati in una tabella con una colonna di identità utilizzando il bq load comando o l' LOAD DATA istruzione. Se la colonna di identità viene omessa dai dati o dallo schema di origine, vengono generati i valori. Se la colonna di identità è GENERATED ALWAYS AS IDENTITY, deve essere omessa.

L'esempio seguente carica i dati da un file CSV data.csv in mydataset.mytable. Il file contiene solo i dati per la colonna data:

"X"
"Y"

Il seguente bq load comando carica data.csv in mydataset.mytable, omettendo la riga di intestazione e specificando solo la colonna data nello schema:

bq load --source_format=CSV --skip_leading_rows=0 \
mydataset.mytable data.csv data:STRING

Il job di caricamento genera i valori id per le nuove righe.

Rimuovere la proprietà della colonna di identità

Puoi rimuovere la proprietà di identità da una colonna utilizzando l' ALTER TABLE ALTER COLUMN DROP GENERATED istruzione DDL.

L'esempio seguente rimuove le proprietà della colonna di identità dalla colonna id in mydataset.mytable:

ALTER TABLE mydataset.mytable
ALTER COLUMN id DROP GENERATED;

Visualizzare le informazioni sulle colonne di identità

Per visualizzare la configurazione della colonna di identità per una colonna, esegui una query sulla INFORMATION_SCHEMA.COLUMNS visualizzazione.

L'esempio seguente mostra le informazioni sulla colonna di identità per le colonne in mydataset.mytable:

SELECT
  column_name,
  is_identity,
  identity_generation,
  identity_start,
  identity_increment
FROM
  mydataset.INFORMATION_SCHEMA.COLUMNS
WHERE
  table_name = 'mytable';

Il risultato è simile al seguente:

+-------------+-------------+---------------------+----------------+--------------------+
| column_name | is_identity | identity_generation | identity_start | identity_increment |
+-------------+-------------+---------------------+----------------+--------------------+
| id          | YES         | BY DEFAULT          | 100            | 10                 |
| data        | NO          | NULL                | NULL           | NULL               |
+-------------+-------------+---------------------+----------------+--------------------+

In alternativa, puoi eseguire una query sulla colonna ddl della visualizzazione INFORMATION_SCHEMA.TABLES per visualizzare la definizione della colonna di identità nell'istruzione DDL CREATE TABLE per una tabella.

Passaggi successivi