Specifica i valori predefiniti delle colonne
In questa pagina viene descritto come impostare un valore predefinito per una colonna in un Tabella BigQuery. Quando aggiungi una riga a una tabella che non contiene dati per una colonna con un valore predefinito, quest'ultimo viene scritto nel .
Espressione del valore predefinito
L'espressione di valore predefinita per una colonna deve essere un literal o uno dei le seguenti funzioni:
CURRENT_DATE
CURRENT_DATETIME
CURRENT_TIME
CURRENT_TIMESTAMP
GENERATE_UUID
RAND
SESSION_USER
ST_GEOGPOINT
Puoi comporre un valore predefinito di 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 del valore predefinito deve corrispondere o
coerci
al tipo di colonna a cui si applica. Se non viene impostato alcun valore predefinito,
è NULL
.
Imposta valori predefiniti
Puoi impostare il valore predefinito per le colonne quando crei una nuova tabella. Puoi utilizzare
CREATE TABLE
Istruzione DDL
e aggiungi la parola chiave DEFAULT
e l'espressione del valore predefinito dopo il nome della colonna
e digitare. L'esempio seguente crea una tabella denominata simple_table
con due
STRING
colonne, a
e b
. La colonna b
ha il valore predefinito 'hello'
.
CREATE TABLE mydataset.simple_table ( a STRING, b STRING DEFAULT 'hello');
Quando inserisci in simple_table
dati che omettono la colonna b
, il valore predefinito
viene utilizzato il valore '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. La
il valore predefinito per un array non può essere NULL
o contenere elementi NULL
.
L'esempio seguente crea una tabella denominata complex_table
e imposta una
valore predefinito per la colonna struct_col
, che contiene campi nidificati,
incluso 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 nella colonna, come
un valore predefinito non conforme a un
tipo con parametri
o un valore predefinito NULL
quando la modalità della colonna
è REQUIRED
.
Modifica 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, quindi seleziona nella tabella.
Nel riquadro dei dettagli, fai clic sulla scheda Schema.
Fai clic su Modifica schema. Potresti dover scorrere per visualizzare questo pulsante.
Nella pagina Schema attuale, individua il campo di primo livello che ti interessa. per cambiare.
Inserisci il valore predefinito per il campo in questione.
Fai clic su Salva.
SQL
Utilizza la
ALTER COLUMN SET DEFAULT
Istruzione DDL.
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 ulteriori informazioni su come eseguire le query, consulta Eseguire una query interattiva.
L'impostazione del valore predefinito per una colonna influisce solo sugli inserimenti futuri della tabella.
I dati della tabella esistenti non vengono modificati. L'esempio seguente imposta il parametro
il valore predefinito della colonna a
è SESSION_USER()
;
ALTER TABLE mydataset.simple_table ALTER COLUMN a SET DEFAULT SESSION_USER();
Se inserisci in simple_table
una riga che omette la colonna a
, la riga
viene usato l'utente della sessione.
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, quindi seleziona nella tabella.
Nel riquadro dei dettagli, fai clic sulla scheda Schema.
Fai clic su Modifica schema. Potresti dover scorrere per visualizzare questo pulsante.
Nella pagina Schema attuale, individua il campo di primo livello che ti interessa. per cambiare.
Inserisci
NULL
per il valore predefinito.Fai clic su Salva.
SQL
Utilizza la
ALTER COLUMN DROP DEFAULT
Istruzione DDL.
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
conALTER COLUMN SET DEFAULT
Istruzione DDL.Fai clic su
Esegui.
Per ulteriori informazioni su come eseguire le query, consulta Eseguire una query interattiva.
Utilizzare istruzioni DML con valori predefiniti
Puoi aggiungere righe con valori predefiniti a una tabella utilizzando il metodo
INSERT
Istruzione DML.
Il valore predefinito viene utilizzato quando il valore di una colonna non è specificato o quando
la parola chiave DEFAULT
viene utilizzata al posto dell'espressione di valore. Le seguenti
esempio crea una tabella e inserisce una riga in cui ogni valore è il valore predefinito
valore:
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 valore predefinito. Quando
il valore predefinito è una funzione, come CURRENT_TIME()
, viene valutato
l'ora in cui il valore viene scritto. Chiamata a INSERT
con il valore predefinito per
La colonna x
restituisce di nuovo un valore diverso per TIME
. Nel seguente
Ad esempio,
La colonna z
ha un valore impostato in modo esplicito e le colonne omesse usano il loro valore predefinito
valori:
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 i valori predefiniti utilizzando il metodo
MERGE
Istruzione DML.
L'esempio seguente crea due tabelle e ne aggiorna una con un 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 i valori predefiniti utilizzando il metodo
UPDATE
Istruzione DML.
L'esempio seguente aggiorna la tabella source_table
in modo che ogni riga di
colonna b
è 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! | +------+------------+
Aggiungi una tabella
Puoi utilizzare il comando bq query
con il flag --append_table
per aggiungere il componente
i risultati di una query a una tabella di destinazione con valori predefiniti. Se la query
omette una colonna con un valore predefinito, quest'ultimo viene assegnato. Le seguenti
esempio aggiunge dati che specificano i valori solo per la 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 i 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 i dati
in una tabella con valori predefiniti utilizzando
bq load
o
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
e i relativi valori.
I formati binari, come AVRO, Parquet o ORC, hanno schemi di file codificati. Quando nello schema del file vengono omesse alcune colonne e vengono applicati i valori predefiniti.
I formati di testo, ad esempio JSON e CSV, non hanno uno schema di file codificato. Per specificare
utilizzando lo strumento a riga di comando bq, puoi usare il flag --autodetect
o fornire un
Schema JSON. Per specificare
utilizzando l'istruzione LOAD DATA
, devi fornire un elenco
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 Scrivi
L'API StorageWrite inserisce i valori predefiniti solo quando
scrittura flusso di dati
nello schema manca un campo contenuto nello schema della tabella di destinazione.
In questo caso, il campo mancante viene compilato con il valore predefinito nella
per ogni scrittura. Se il campo esiste nello schema del flusso di scrittura, ma è
non è presente nei dati stessi, il campo mancante viene compilato con NULL
.
Ad esempio, supponiamo che tu stia scrivendo dati
in una tabella BigQuery con il seguente schema:
[ { "name": "a", "mode": "NULLABLE", "type": "STRING", }, { "name": "b", "mode": "NULLABLE", "type": "STRING", "defaultValueExpression": "'default_b'" }, { "name": "c", "mode": "NULLABLE", "type": "STRING", "defaultValueExpression": "'default_c'" } ]
Le seguenti
schema flusso di scrittura
non contiene il campo c
presente nella tabella di destinazione:
[ { "name": "a", "type": "STRING", }, { "name": "b", "type": "STRING", } ]
Supponi di inviare 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 di scrittura flusso contiene il campo b
, quindi il valore predefinito default_b
non viene utilizzato anche se non viene specificato alcun valore per il campo. Poiché la scrittura
lo schema dello stream non contiene il campo c
, ogni riga della colonna c
è
compilata con il valore predefinito default_c
della tabella di destinazione.
Il seguente schema di flusso di scrittura corrisponde allo schema della tabella che stai scrivendo a:
[ { "name": "a", "type": "STRING", }, { "name": "b", "type": "STRING", } { "name": "c", "type": "STRING", } ]
Supponi di inviare i seguenti valori alla tabella:
{'a': 'val_a', 'b': 'val_b'} {'a': 'val_a'}
Nello schema di scrittura del flusso non mancano i campi contenuti nei campi tabella di destinazione, quindi nessuna delle colonne vengono applicati i valori predefiniti a prescindere dal fatto che i campi siano compilati o meno nei dati trasmessi in flusso:
+-------+-------+------+ | 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
nel messaggio AppendRowsRequest
. Se il valore è impostato su
DEFAULT_VALUE
, il valore mancante acquisirà il valore predefinito anche quando la colonna è
presentati nello schema utente.
Puoi anche specificare i valori predefiniti a livello di richiesta nel
Mappa missing_value_interpretations
nel
AppendRowsRequest
messaggio.
Ogni chiave è il nome di una colonna e la sua
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
sono 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 presenta valori mancanti, allora i valori mancanti
vengono interpretati come NULL
.
Le chiavi possono essere solo nomi di colonne di primo livello. Le chiavi non possono essere campi secondari di struct, come
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, i valori predefiniti sono
applicati a queste colonne.
Ad esempio, supponiamo che tu abbia: schema della 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 inviare 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
il valore default_c
è scritto nella colonna c
. La seconda riga inserita non
contengono valori per i campi b
o c
, quindi i loro valori predefiniti sono scritti in
colonne b
e c
. La terza riga inserita
non contiene valori. Il valore scritto nella colonna a
è NULL
poiché non esiste un'altra
è impostato il valore predefinito. Vengono scritti i valori predefiniti default_b
e default_c
alle colonne b
e c
.
Visualizza valori predefiniti
Per vedere il valore predefinito di una colonna, esegui una query sul
INFORMATION_SCHEMA.COLUMNS
visualizzazione. 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 l'attributo
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 può scrivere nelle 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 e poi modificarne il valore
usando l'istruzione DDL
ALTER COLUMN SET DEFAULT
. - Non puoi copiare e aggiungere una tabella di origine a una tabella di destinazione che ne contiene
colonne di quelle della tabella di origine e le colonne aggiuntive hanno colonne
e i relativi valori. 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, vedi Introduzione al caricamento dei dati.