Specifica i valori predefiniti della colonna
Questa pagina descrive come impostare un valore predefinito per una colonna in una tabella BigQuery. Quando aggiungi una riga a una tabella che non contiene dati per una colonna con un valore predefinito, il valore predefinito viene scritto nella colonna.
Espressione valore predefinito
L'espressione del valore predefinito per una colonna deve essere una funzione letterale o una delle seguenti:
CURRENT_DATE
CURRENT_DATETIME
CURRENT_TIME
CURRENT_TIMESTAMP
GENERATE_UUID
RAND
SESSION_USER
ST_GEOGPOINT
Puoi scrivere un valore predefinito di tipo STRUCT o ARRAY con queste funzioni, ad esempio
[CURRENT_DATE(), DATE '2020-01-01']
.
Le funzioni vengono valutate quando i dati vengono scritti nella tabella.
Il tipo di valore predefinito deve corrispondere o coercere al tipo di colonna a cui si applica. Se non viene impostato alcun valore predefinito, il valore predefinito è NULL
.
Impostare valori predefiniti
Puoi impostare il valore predefinito per le colonne quando crei una nuova tabella. Utilizza l'istruzione DDL CREATE TABLE
e aggiungi la parola chiave DEFAULT
e l'espressione del valore predefinito dopo il nome e il tipo di colonna. L'esempio seguente crea una tabella denominata simple_table
con due
colonne STRING
, a
e b
. Il valore predefinito della colonna b
è 'hello'
.
CREATE TABLE mydataset.simple_table ( a STRING, b STRING DEFAULT 'hello');
Quando inserisci dati in simple_table
che omettono la colonna b
, viene utilizzato il valore predefinito 'hello'
, ad esempio:
INSERT mydataset.simple_table (a) VALUES ('val1'), ('val2');
La tabella simple_table
contiene i seguenti valori:
+------+-------+ | a | b | +------+-------+ | val1 | hello | | val2 | hello | +------+-------+
Se una colonna è di tipo STRUCT
, devi impostare il valore predefinito per
l'intero campo STRUCT
. Non puoi impostare il valore predefinito per un sottoinsieme di campi. Il
valore predefinito per un array non può essere NULL
o contenere elementi NULL
.
Nell'esempio seguente viene creata una tabella denominata complex_table
e viene impostato un valore predefinito per la colonna struct_col
, che contiene campi nidificati, tra cui un tipo ARRAY
:
CREATE TABLE mydataset.complex_table ( struct_col STRUCT<x STRUCT<x1 TIMESTAMP, x2 NUMERIC>, y ARRAY<DATE>> DEFAULT ((CURRENT_TIMESTAMP(), NULL), [DATE '2022-01-01', CURRENT_DATE()]) );
Non puoi impostare valori predefiniti che violano un vincolo sulla colonna, ad esempio un valore predefinito non conforme a un tipo con parametri o un valore predefinito NULL
quando la modalità della colonna è REQUIRED
.
Modificare i valori predefiniti
Per modificare il valore predefinito di una colonna, seleziona una delle seguenti opzioni:
Console
Nella console Google Cloud, vai alla pagina BigQuery.
Nel riquadro Explorer, espandi il progetto e il set di dati, poi seleziona la tabella.
Nel riquadro dei dettagli, fai clic sulla scheda Schema.
Fai clic su Modifica schema. È possibile che sia necessario scorrere la pagina per visualizzare questo pulsante.
Nella pagina Schema corrente, individua il campo di primo livello da modificare.
Inserisci il valore predefinito per il campo in questione.
Fai clic su Salva.
SQL
Utilizza l'istruzione DDL ALTER COLUMN SET DEFAULT
.
Nella console Google Cloud, vai alla pagina BigQuery.
Nell'Editor query, inserisci la seguente istruzione:
ALTER TABLE mydataset.mytable ALTER COLUMN column_name SET DEFAULT default_expression;
Fai clic su
Esegui.
Per saperne di più su come eseguire le query, consulta Eseguire una query interattiva.
L'impostazione del valore predefinito per una colonna influisce solo sugli inserti futuri nella tabella.
I dati della tabella esistenti non vengono modificati. L'esempio seguente imposta il
valore predefinito della colonna a
su SESSION_USER()
;
ALTER TABLE mydataset.simple_table ALTER COLUMN a SET DEFAULT SESSION_USER();
Se inserisci una riga in simple_table
che omette la colonna a
, viene utilizzato
l'utente della sessione corrente.
INSERT mydataset.simple_table (b) VALUES ('goodbye');
La tabella simple_table
contiene i seguenti valori:
+------------------+---------+ | a | b | +------------------+---------+ | val1 | hello | | val2 | hello | | user@example.com | goodbye | +------------------+---------+
Rimuovi valori predefiniti
Per rimuovere il valore predefinito di una colonna, seleziona una delle seguenti opzioni:
Console
Nella console Google Cloud, vai alla pagina BigQuery.
Nel riquadro Explorer, espandi il progetto e il set di dati, poi seleziona la tabella.
Nel riquadro dei dettagli, fai clic sulla scheda Schema.
Fai clic su Modifica schema. È possibile che sia necessario scorrere la pagina per visualizzare questo pulsante.
Nella pagina Schema corrente, individua il campo di primo livello da modificare.
Inserisci
NULL
come valore predefinito.Fai clic su Salva.
SQL
Utilizza l'istruzione DDL ALTER COLUMN DROP DEFAULT
.
Nella console Google Cloud, vai alla pagina BigQuery.
Nell'Editor query, inserisci la seguente istruzione:
ALTER TABLE mydataset.mytable ALTER COLUMN column_name DROP DEFAULT;
Puoi anche rimuovere il valore predefinito da una colonna modificandone il valore in
NULL
con l'istruzione DDLALTER COLUMN SET DEFAULT
.Fai clic su
Esegui.
Per saperne di più su come eseguire le query, consulta Eseguire una query interattiva.
Utilizzare le istruzioni DML con valori predefiniti
Puoi aggiungere a una tabella righe con valori predefiniti utilizzando
l'istruzione DML INSERT
.
Il valore predefinito viene utilizzato quando il valore di una colonna non è specificato o quando viene utilizzata la parola chiave DEFAULT
al posto dell'espressione del valore. L'esempio seguente crea una tabella e inserisce una riga in cui ogni valore è il valore predefinito:
CREATE TABLE mydataset.mytable ( x TIME DEFAULT CURRENT_TIME(), y INT64 DEFAULT 5, z BOOL); INSERT mydataset.mytable (x, y, z) VALUES (DEFAULT, DEFAULT, DEFAULT);
La tabella mytable
ha il seguente aspetto:
+-----------------+---+------+ | x | y | z | +-----------------+---+------+ | 22:13:24.799555 | 5 | null | +-----------------+---+------+
La colonna z
non ha un valore predefinito, quindi viene utilizzato NULL
come predefinito. Quando il valore predefinito è una funzione, come CURRENT_TIME()
, viene valutato nel momento in cui il valore viene scritto. Se chiami INSERT
con il valore predefinito per la colonna x
, il valore di TIME
sarà diverso. Nel seguente esempio, solo la colonna z
ha un valore impostato in modo esplicito e le colonne omesse utilizzano i propri valori predefiniti:
INSERT mydataset.mytable (z) VALUES (TRUE);
La tabella mytable
ha il seguente aspetto:
+-----------------+---+------+ | x | y | z | +-----------------+---+------+ | 22:13:24.799555 | 5 | null | | 22:18:29.890547 | 5 | true | +-----------------+---+------+
Puoi aggiornare una tabella con valori predefiniti utilizzando
l'istruzione DML MERGE
.
L'esempio seguente crea due tabelle e ne aggiorna una con un'istruzione MERGE
:
CREATE TABLE mydataset.target_table ( a STRING, b STRING DEFAULT 'default_b', c STRING DEFAULT SESSION_USER()) AS ( SELECT 'val1' AS a, 'hi' AS b, '123@google.com' AS c UNION ALL SELECT 'val2' AS a, 'goodbye' AS b, SESSION_USER() AS c ); CREATE TABLE mydataset.source_table ( a STRING DEFAULT 'default_val', b STRING DEFAULT 'Happy day!') AS ( SELECT 'val1' AS a, 'Good evening!' AS b UNION ALL SELECT 'val3' AS a, 'Good morning!' AS b ); MERGE mydataset.target_table T USING mydataset.source_table S ON T.a = S.a WHEN NOT MATCHED THEN INSERT(a, b) VALUES (a, DEFAULT);
Il risultato è il seguente:
+------+-----------+--------------------+ | a | b | c | +------+-----------+--------------------+ | val1 | hi | 123@google.com | | val2 | goodbye | default@google.com | | val3 | default_b | default@google.com | +------+-----------+--------------------+
Puoi aggiornare una tabella con valori predefiniti utilizzando
l'istruzione DML UPDATE
.
L'esempio seguente aggiorna la tabella source_table
in modo che ogni riga della colonna b
sia uguale al valore predefinito:
UPDATE mydataset.source_table SET b = DEFAULT WHERE TRUE;
Il risultato è il seguente:
+------+------------+ | a | b | +------+------------+ | val1 | Happy day! | | val3 | Happy day! | +------+------------+
Aggiungere 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 valori predefiniti. Se la query omette una colonna con un valore predefinito, viene assegnato il valore predefinito. Nel seguente esempio vengono aggiunti dati in cui vengono specificati solo i valori della colonna z
:
bq query \ --nouse_legacy_sql \ --append_table \ --destination_table=mydataset.mytable \ 'SELECT FALSE AS z UNION ALL SELECT FALSE AS Z'
La tabella mytable
utilizza valori predefiniti per le colonne x
e y
:
+-----------------+---+-------+ | x | y | z | +-----------------+---+-------+ | 22:13:24.799555 | 5 | NULL | | 22:18:29.890547 | 5 | true | | 23:05:18.841683 | 5 | false | | 23:05:18.841683 | 5 | false | +-----------------+---+-------+
Carica dati
Puoi caricare dati in una tabella con valori predefiniti utilizzando il comando bq load
o l'istruzione LOAD DATA
.
I valori predefiniti vengono applicati quando i dati caricati hanno meno colonne rispetto alla tabella di destinazione. I valori NULL
nei dati caricati non vengono convertiti in valori predefiniti.
I formati binari, come AVRO, Parquet o ORC, hanno schemi di file codificati. Se lo schema del file omette alcune colonne, vengono applicati i valori predefiniti.
I formati di testo, ad esempio JSON e CSV, non hanno uno schema di file codificato. Per specificare lo schema utilizzando lo strumento a riga di comando bq, puoi utilizzare il flag --autodetect
o fornire uno schema JSON. Per specificare lo schema utilizzando l'istruzione LOAD DATA
, devi fornire un elenco di colonne. Di seguito è riportato un esempio che carica solo la colonna a
da un file CSV:
LOAD DATA INTO mydataset.insert_table (a) FROM FILES( uris = ['gs://test-bucket/sample.csv'], format = 'CSV');
API Write
L'API Storage Write compila i valori predefiniti solo quando nello schema del flusso di scrittura manca un campo contenuto nello schema della tabella di destinazione.
In questo caso, il campo mancante viene completato con il valore predefinito nella colonna per ogni scrittura. Se il campo esiste nello schema del flusso di scrittura, ma non è presente nei dati stessi, il campo mancante viene completato con NULL
.
Ad esempio, supponiamo che tu stia scrivendo dati
in una tabella BigQuery con lo schema seguente:
[ { "name": "a", "mode": "NULLABLE", "type": "STRING", }, { "name": "b", "mode": "NULLABLE", "type": "STRING", "defaultValueExpression": "'default_b'" }, { "name": "c", "mode": "NULLABLE", "type": "STRING", "defaultValueExpression": "'default_c'" } ]
Nel seguente schema di scrittura del flusso di lavoro manca il campo c
presente nella tabella di destinazione:
[ { "name": "a", "type": "STRING", }, { "name": "b", "type": "STRING", } ]
Supponi di trasmettere in streaming i seguenti valori alla tabella:
{'a': 'val_a', 'b': 'val_b'} {'a': 'val_a'}
Il risultato è il seguente:
+-------+-------+-----------+ | a | b | c | +-------+-------+-----------+ | val_a | val_b | default_c | | val_a | NULL | default_c | +-------+-------+-----------+
Lo schema del flusso di scrittura contiene il campo b
, quindi il valore predefinito default_b
non viene utilizzato anche se non è specificato alcun valore per il campo. Poiché lo schema del flusso di scrittura non contiene il campo c
, ogni riga nella colonna c
viene completata con il valore predefinito default_c
della tabella di destinazione.
Il seguente schema del flusso di scrittura corrisponde allo schema della tabella in cui stai scrivendo:
[ { "name": "a", "type": "STRING", }, { "name": "b", "type": "STRING", } { "name": "c", "type": "STRING", } ]
Supponi di trasmettere in streaming i seguenti valori alla tabella:
{'a': 'val_a', 'b': 'val_b'} {'a': 'val_a'}
Nello schema del flusso di scrittura non mancano i campi contenuti nella tabella di destinazione, quindi non viene applicato nessuno dei valori predefiniti delle colonne, indipendentemente dal fatto che i campi siano compilati nei flussi di dati:
+-------+-------+------+ | a | b | c | +-------+-------+------+ | val_a | val_b | NULL | | val_a | NULL | NULL | +-------+-------+------+
Puoi specificare le impostazioni dei valori predefiniti a livello di connessione in default_missing_value_interpretation
all'interno del messaggio AppendRowsRequest
. Se il valore è impostato su DEFAULT_VALUE
, il valore mancante acquisirà il valore predefinito anche quando la colonna è presente nello schema utente.
Puoi anche specificare valori predefiniti a livello di richiesta nella
mappa missing_value_interpretations
all'interno del
messaggio AppendRowsRequest
.
Ogni chiave è il nome di una colonna e il relativo valore indica come interpretare i valori mancanti.
Ad esempio, la mappa {'col1': NULL_VALUE, 'col2': DEFAULT_VALUE}
significa che tutti i valori mancanti in col1
vengono interpretati come NULL
e
tutti i valori mancanti in col2
vengono interpretati come il valore predefinito impostato per col2
nello schema della tabella.
Se un campo non è in questa mappa e ha valori mancanti, i valori mancanti
vengono interpretati come NULL
.
Le chiavi possono essere solo nomi di colonna di primo livello. Le chiavi non possono essere campi secondari di struct, ad esempio col1.subfield1
.
Utilizza il metodo API insertAll
Il metodo API tabledata.insertAll
compila i valori predefiniti a livello di riga quando i dati vengono scritti in una tabella.
Se in una riga mancano colonne con valori predefiniti, a queste colonne vengono applicati i valori predefiniti.
Ad esempio, supponi di avere il seguente schema di tabella:
[ { "name": "a", "mode": "NULLABLE", "type": "STRING", }, { "name": "b", "mode": "NULLABLE", "type": "STRING", "defaultValueExpression": "'default_b'" }, { "name": "c", "mode": "NULLABLE", "type": "STRING", "defaultValueExpression": "'default_c'" } ]
Supponi di trasmettere in streaming i seguenti valori alla tabella:
{'a': 'val_a', 'b': 'val_b'} {'a': 'val_a'} {}
Il risultato è il seguente:
+-------+------------+-----------+ | a | b | c | +-------+------------+-----------+ | val_a | val_b | default_c | | val_a | default_b | default_c | | NULL | default_b | default_c | +-------+------------+-----------+
La prima riga inserita non contiene un valore per il campo c
, quindi il valore predefinito default_c
viene scritto nella colonna c
. La seconda riga inserita non contiene valori per i campi b
o c
, pertanto i loro valori predefiniti vengono scritti nelle colonne b
e c
. La terza riga inserita
non contiene valori. Il valore scritto nella colonna a
è NULL
perché non è impostato nessun altro valore predefinito. I valori predefiniti default_b
e default_c
sono scritti nelle colonne b
e c
.
Visualizza valori predefiniti
Per visualizzare il valore predefinito di una colonna, esegui una query sulla vista INFORMATION_SCHEMA.COLUMNS
. Il campo della colonna column_default
contiene il valore predefinito per la colonna. Se non viene impostato alcun valore predefinito, è NULL
. L'esempio seguente mostra i nomi delle colonne e i valori predefiniti per la tabella mytable
:
SELECT column_name, column_default FROM mydataset.INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'mytable';
Il risultato è simile al seguente:
+-------------+----------------+ | column_name | column_default | +-------------+----------------+ | x | CURRENT_TIME() | | y | 5 | | z | NULL | +-------------+----------------+
Limitazioni
- Puoi leggere dalle tabelle con valori predefiniti utilizzando l'SQL precedente, ma non puoi scrivere in tabelle con valori predefiniti utilizzando l'SQL precedente.
- Non puoi aggiungere una nuova colonna con un valore predefinito a una tabella esistente.
Tuttavia, puoi aggiungere la colonna senza un valore predefinito, quindi modificarne il valore predefinito utilizzando l'istruzione DDL
ALTER COLUMN SET DEFAULT
. - Non puoi copiare e aggiungere una tabella di origine a una tabella di destinazione che ha più
colonne della tabella di origine e le colonne aggiuntive hanno valori
predefiniti. Puoi invece eseguire
INSERT destination_table SELECT * FROM source_table
per copiare i dati.
Passaggi successivi
- Per ulteriori informazioni sul caricamento dei dati in BigQuery, consulta Introduzione al caricamento dei dati.