Aggiornamento dei dati tabella partizionata mediante DML
Questa pagina fornisce una panoramica del supporto del Data Manipulation Language (DML) per partizionate.
Per ulteriori informazioni sul DML, consulta:
- Introduzione a DML
- Sintassi DML
- Aggiornare i dati delle tabelle utilizzando Data Manipulation Language (DPO)
Tabelle utilizzate negli esempi
Le seguenti definizioni dello schema JSON rappresentano le tabelle utilizzate negli esempi. in questa pagina.
mytable
: una tabella partizionata in base al tempo di importazione
[ {"name": "field1", "type": "INTEGER"}, {"name": "field2", "type": "STRING"} ]
mytable2
: una tabella standard (non partizionata)
[ {"name": "id", "type": "INTEGER"}, {"name": "ts", "type": "TIMESTAMP"} ]
mycolumntable
: una tabella partizionata
partizionato utilizzando la colonna ts
TIMESTAMP
[ {"name": "field1", "type": "INTEGER"}, {"name": "field2", "type": "STRING"} {"name": "field3", "type": "BOOLEAN"} {"name": "ts", "type": "TIMESTAMP"} ]
Negli esempi in cui compare COLUMN_ID, sostituiscilo con il nome del colonna su cui desideri operare.
Inserimento dei dati
Utilizzi un'istruzione INSERT
DML
per aggiungere righe a una tabella partizionata.
Inserimento di dati in tabelle partizionate in fase di importazione
Quando utilizzi un'istruzione DML per aggiungere righe a una tabella partizionata per data di importazione,
puoi specificare la partizione a cui aggiungere le righe. Fai riferimento
la partizione utilizzando la pseudocolonna _PARTITIONTIME
.
Ad esempio, la seguente istruzione INSERT
aggiunge una riga al 1° maggio 2017
partizione di mytable
- “2017-05-01”
.
INSERT INTO project_id.dataset.mytable (_PARTITIONTIME, field1, field2) SELECT TIMESTAMP("2017-05-01"), 1, "one"
È possibile utilizzare solo i timestamp che corrispondono a limiti esatti di date. Per Ad esempio, la seguente istruzione DML restituisce un errore:
INSERT INTO project_id.dataset.mytable (_PARTITIONTIME, field1, field2) SELECT TIMESTAMP("2017-05-01 21:30:00"), 1, "one"
Inserimento dei dati in tabelle partizionate
Inserire dati in una tabella partizionata utilizzando DML è la stessa cosa che inserire i dati in una tabella non partizionata.
Ad esempio, la seguente istruzione INSERT
aggiunge righe a una tabella partizionata
mycolumntable
selezionando i dati da mytable2
(tabella non partizionata).
INSERT INTO project_id.dataset.mycolumntable (ts, field1) SELECT ts, id FROM project_id.dataset.mytable2
Eliminazione di dati
Utilizzi un'istruzione DELETE
DML
per eliminare le righe da una tabella partizionata.
Eliminazione dei dati nelle tabelle partizionate in fase di importazione
La seguente istruzione DELETE
elimina tutte le righe dal 1° giugno 2017
partizione ("2017-06-01"
) di mytable
dove field1
è uguale a 21
. Tu
fare riferimento alla partizione utilizzando la pseudocolonna _PARTITIONTIME
.
DELETE project_id.dataset.mytable WHERE field1 = 21 AND _PARTITIONTIME = "2017-06-01"
Eliminazione dei dati nelle tabelle partizionate
Eliminare i dati in una tabella partizionata utilizzando DML è come eliminare i dati da una tabella non partizionata.
Ad esempio, la seguente istruzione DELETE
elimina tutte le righe dalla
Il 1° giugno 2017 partizione ("2017-06-01"
) di mycolumntable
in cui field1
è
uguale a 21
.
DELETE project_id.dataset.mycolumntable WHERE field1 = 21 AND DATE(ts) = "2017-06-01"
Utilizzo di DML DELETE per eliminare le partizioni
Se un'istruzione DELETE
qualificante copre tutte le righe di una partizione,
BigQuery rimuove l'intera partizione. La rimozione è stata completata
senza scansionare byte o consumare slot. Il seguente esempio di DELETE
copre l'intera partizione di un filtro nell'elemento _PARTITIONDATE
pseudocolonna:
DELETE mydataset.mytable WHERE _PARTITIONDATE IN ('2076-10-07', '2076-03-06');
Interruzioni comuni
Le query con le seguenti caratteristiche potrebbero non trarre vantaggio dall'ottimizzazione:
- Copertura partizione parziale
- Riferimenti alle colonne non partizionanti
- Dati importati di recente tramite l'API Storage Scrivi di BigQuery o l'API per flussi di dati legacy
- Filtri con sottoquery o predicati non supportati
L'idoneità per l'ottimizzazione può variare in base al tipo di partizionamento, i metadati di archiviazione sottostanti e i predicati del filtro. Come best practice, esegui una prova per verificare che la query generi 0 byte elaborati.
Transazione multiistruzione
Questa ottimizzazione funziona all'interno di una transazione con più dichiarazioni. Il seguente esempio di query sostituisce una partizione con i dati di un'altra tabella in una singola transazione, senza analizzare la partizione
l'istruzione DELETE
.
DECLARE REPLACE_DAY DATE; BEGIN TRANSACTION; -- find the partition which we want to replace SET REPLACE_DAY = (SELECT MAX(d) FROM mydataset.mytable_staging); -- delete the entire partition from mytable DELETE FROM mydataset.mytable WHERE part_col = REPLACE_DAY; -- insert the new data into the same partition in mytable INSERT INTO mydataset.mytable SELECT * FROM mydataset.mytable_staging WHERE part_col = REPLACE_DAY; COMMIT TRANSACTION;
Aggiornamento dei dati
Utilizzi un'istruzione UPDATE
per aggiornare le righe in una tabella partizionata.
Aggiornamento dei dati nelle tabelle partizionate in fase di importazione
La seguente istruzione UPDATE
sposta le righe da una partizione all'altra.
Righe nella partizione del 1° maggio 2017 (“2017-05-01”
) di mytable
in cui field1
uguale a 21
vengono spostate nella partizione del 1° giugno 2017 (“2017-06-01”
).
UPDATE project_id.dataset.mytable SET _PARTITIONTIME = "2017-06-01" WHERE _PARTITIONTIME = "2017-05-01" AND field1 = 21
Aggiornamento dei dati nelle tabelle partizionate
Aggiornare i dati in una tabella partizionata utilizzando DML è la stessa cosa che aggiornare i dati
da una tabella non partizionata. Ad esempio, il seguente UPDATE
sposta le righe da una partizione all'altra. Righe del 1° maggio 2017
partizione (“2017-05-01”
) di mytable
dove field1
è uguale a 21
sono
spostato nella partizione (“2017-06-01”
) del 1° giugno 2017.
UPDATE project_id.dataset.mycolumntable SET ts = "2017-06-01" WHERE DATE(ts) = "2017-05-01" AND field1 = 21
DML in tabelle partizionate orarie, mensili e annuali
Puoi utilizzare le istruzioni DML per modificare una tabella partizionata oraria, mensile o annuale. Specifica l'ora, il mese o l'intervallo di anni di date/timestamp/date/orari pertinenti, come nell'esempio seguente per le tabelle partizionate mensili:
bq query --nouse_legacy_sql 'DELETE FROM my_dataset.my_table WHERE TIMESTAMP_TRUNC(ts_column, MONTH) = "2020-01-01 00:00:00";'
Oppure un altro esempio di tabelle partizionate con colonna DATETIME
:
bq query --nouse_legacy_sql 'DELETE FROM my_dataset.my_table WHERE dt_column BETWEEN DATETIME("2020-01-01") AND DATETIME("2020-05-01");'
Utilizzo di un'istruzione MERGE
Utilizzi un'istruzione MERGE
DML
per combinare le operazioni INSERT
, UPDATE
e DELETE
per una tabella partizionata
in un'istruzione ed eseguirle a livello atomico.
Eliminare le partizioni quando si utilizza un'istruzione MERGE
Quando esegui un'istruzione MERGE
su una tabella partizionata, puoi limitare
quali partizioni vengono analizzate includendo la colonna di partizionamento in
un filtro di sottoquery, un filtro search_condition
o un filtro merge_condition
.
L'eliminazione può verificarsi durante l'analisi della tabella di origine, della tabella di destinazione o di entrambe.
Ciascuno degli esempi seguenti esegue query su una tabella partizionata per data di importazione utilizzando
la pseudocolonna _PARTITIONTIME
come filtro.
Utilizzo di una sottoquery per filtrare i dati di origine
Nella seguente istruzione MERGE
, la sottoquery della clausola USING
filtra
nella pseudocolonna _PARTITIONTIME
nella tabella di origine.
MERGE dataset.target T USING (SELECT * FROM dataset.source WHERE _PARTITIONTIME = '2018-01-01') S ON T.COLUMN_ID = S.COLUMN_ID WHEN MATCHED THEN DELETE
Osservando il piano di esecuzione della query, la sottoquery viene eseguita per prima. Solo le righe in
viene analizzata la partizione '2018-01-01'
nella tabella di origine. Ecco il
pertinente nel piano di query:
READ $10:name, $11:_PARTITIONTIME
FROM temp.source
WHERE equal($11, 1514764800.000000000)
Utilizzo di un filtro in search_condition
di una when_clause
Se un elemento search_condition
contiene un filtro, l'ottimizzatore delle query tenta di
eliminare le partizioni. Ad esempio, nella seguente istruzione MERGE
, ogni clausola WHEN
MATCHED
e WHEN NOT MATCHED
contiene un filtro nel campo _PARTITIONTIME
pseudocolonna.
MERGE dataset.target T USING dataset.source S ON T.COLUMN_ID = S.COLUMN_ID WHEN MATCHED AND T._PARTITIONTIME = '2018-01-01' THEN UPDATE SET COLUMN_ID = S.COLUMN_ID WHEN MATCHED AND T._PARTITIONTIME = '2018-01-02' THEN UPDATE SET COLUMN_ID = S.COLUMN_ID + 10 WHEN NOT MATCHED BY SOURCE AND T._PARTITIONTIME = '2018-01-03' THEN DELETE
Durante la fase di join, nella destinazione vengono scansionate solo le seguenti partizioni
tabella: '2018-01-01'
, '2018-01-02'
e '2018-01-03'
, ovvero
all'unione di tutti i filtri search_condition
.
Dal piano di esecuzione della query:
READ
$1:COLUMN_ID, $2:_PARTITIONTIME, $3:$file_temp_id, $4:$row_temp_id
FROM temp.target
WHERE or(equal($2, 1514764800.000000000), equal($2, 1514851200.000000000), equal($2, 1514937600.000000000))
Tuttavia, nell'esempio seguente, la clausola WHEN NOT MATCHED BY SOURCE
non contengono un'espressione di filtro:
MERGE dataset.target T USING dataset.source S ON T.COLUMN_ID = S.COLUMN_ID WHEN MATCHED AND T._PARTITIONTIME = '2018-01-01' THEN UPDATE SET COLUMN_ID = S.COLUMN_ID WHEN NOT MATCHED BY SOURCE THEN UPDATE SET COLUMN_ID = COLUMN_ID + 1
Questa query deve analizzare l'intera tabella di destinazione per calcolare la clausola WHEN NOT MATCHED BY
SOURCE
. Di conseguenza, non viene eliminata nessuna partizione.
Utilizzo di un predicato falso costante in un merge_condition
Se utilizzi le clausole WHEN NOT MATCHED
e WHEN NOT MATCHED BY SOURCE
allora BigQuery di solito esegue un outer join completo,
che non possono essere eliminati. Tuttavia, se la condizione di unione utilizza un falso costante
predicato, BigQuery può utilizzare la condizione di filtro
l'eliminazione della partizione. Per ulteriori informazioni sull'uso di "constant false"
predicati, consulta la descrizione della clausola merge_condition
nella
Dichiarazione di MERGE
documentazione.
L'esempio seguente analizza solo la partizione '2018-01-01'
in entrambi i file di destinazione
e tabelle di origine.
MERGE dataset.target T USING dataset.source S ON FALSE WHEN NOT MATCHED AND _PARTITIONTIME = '2018-01-01' THEN INSERT(COLUMN_ID) VALUES(COLUMN_ID) WHEN NOT MATCHED BY SOURCE AND _PARTITIONTIME = '2018-01-01' THEN DELETE
Utilizzare un filtro in un merge_condition
Lo strumento di ottimizzazione delle query tenta di utilizzare un filtro in un merge_condition
per eliminare
partizioni di Compute Engine. Lo strumento di ottimizzazione delle query potrebbe o meno essere in grado di eseguire il push del predicato
fino alla fase di scansione della tabella, a seconda del tipo di join.
Nell'esempio seguente, merge_condition
viene utilizzato come predicato per l'unione
alle tabelle di origine e di destinazione. Lo strumento di ottimizzazione delle query può eseguire il push-down di questo predicato
durante l'analisi di entrambe le tabelle. Di conseguenza, la query analizza solo '2018-01-01'
sia nella tabella di destinazione che in quella di origine.
MERGE dataset.target T USING dataset.source S ON T.COLUMN_ID = S.COLUMN_ID AND T._PARTITIONTIME = '2018-01-01' AND S._PARTITIONTIME = '2018-01-01' WHEN MATCHED THEN UPDATE SET COLUMN_ID = NEW_VALUE
Nel prossimo esempio, merge_condition
non contiene un predicato per il valore
tabella di origine, quindi non è possibile eseguire l'eliminazione delle partizioni sulla tabella di origine. La
contiene un predicato per la tabella target, mentre l'istruzione utilizza
una clausola WHEN NOT MATCHED BY SOURCE
anziché una clausola WHEN MATCHED
. Questo
la query deve analizzare l'intera tabella target alla ricerca delle righe che non
corrispondono.
MERGE dataset.target T USING dataset.source S ON T.COLUMN_ID = S.COLUMN_ID AND T._PARTITIONTIME = '2018-01-01' WHEN NOT MATCHED BY SOURCE THEN UPDATE SET COLUMN_ID = NEW_VALUE
Limitazioni
Per informazioni sulle limitazioni di DML, vedi Limitazioni il Riferimento per DML .
Quote
Per informazioni sulle quote DML, consulta Istruzioni DML sulla pagina Quote e limiti.
Prezzi
Per informazioni sui prezzi di DML, vedi Prezzi di DML per le tabelle partizionate.
Sicurezza dei tavoli
Per controllare l'accesso alle tabelle in BigQuery, consulta Introduzione ai controlli di accesso alle tabelle.
Passaggi successivi
- Scopri come creare tabelle partizionate
- Scopri come eseguire query su tabelle partizionate
- Consulta una presentazione a DML
- Scopri come comporre istruzioni DML utilizzando la sintassi DML.