Aggiornamento dei dati tabella partizionata mediante DML
Questa pagina fornisce una panoramica del supporto del data manipulation language (DML) per le tabelle partizionate.
Per ulteriori informazioni su DML, vedi:
- Introduzione a DML
- Sintassi DML
- Aggiornamento dei dati delle tabelle mediante Data Manipulation Language
Tabelle utilizzate negli esempi
Le seguenti definizioni dello schema JSON rappresentano le tabelle utilizzate negli esempi in questa pagina.
mytable
: una tabella partizionata per data 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
partizionata 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 viene visualizzato COLUMN_ID, sostituiscilo con il nome della colonna su cui vuoi operare.
Inserimento dei dati
Utilizzi un'istruzione DML INSERT
per aggiungere righe a una tabella partizionata.
Inserimento di dati nelle tabelle partizionate per data di importazione
Quando utilizzi un'istruzione DML per aggiungere righe a una tabella partizionata per data di importazione,
puoi specificare la partizione a cui devono essere aggiunte le righe. Fai riferimento
alla partizione utilizzando la pseudocolonna _PARTITIONTIME
.
Ad esempio, la seguente istruzione INSERT
aggiunge una riga alla partizione del 1° maggio 2017 di mytable
, ovvero “2017-05-01”
.
INSERT INTO project_id.dataset.mytable (_PARTITIONTIME, field1, field2) SELECT TIMESTAMP("2017-05-01"), 1, "one"
Possono essere utilizzati solo i timestamp che corrispondono a limiti di date esatti. 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 di dati nelle tabelle partizionate
L'inserimento di dati in una tabella partizionata utilizzando DML è uguale all'inserimento di dati in una tabella non partizionata.
Ad esempio, la seguente istruzione INSERT
aggiunge righe alla tabella partizionata
mycolumntable
selezionando i dati da mytable2
(una 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 DML DELETE
per eliminare righe da una tabella partizionata.
Eliminazione dei dati nelle tabelle partizionate per data di importazione
La seguente istruzione DELETE
elimina tutte le righe dalla partizione 1° giugno 2017 ("2017-06-01"
) di mytable
in cui field1
è uguale a 21
. Fai
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
L'eliminazione dei dati in una tabella partizionata utilizzando DML è uguale all'eliminazione dei dati da una tabella non partizionata.
Ad esempio, la seguente istruzione DELETE
elimina tutte le righe dalla
partizione del 1° giugno 2017 ("2017-06-01"
) di mycolumntable
dove 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
idonea copre tutte le righe di una partizione,
BigQuery rimuove l'intera partizione. Questa rimozione viene eseguita
senza analizzare i byte o utilizzare slot. Il seguente esempio di istruzione DELETE
copre l'intera partizione di un filtro sulla pseudocolonna _PARTITIONDATE
:
DELETE mydataset.mytable WHERE _PARTITIONDATE IN ('2076-10-07', '2076-03-06');
Squalifiche comuni
Le query con le seguenti caratteristiche potrebbero non trarre vantaggio dall'ottimizzazione:
- Copertura parziale della partizione
- Riferimenti a colonne non di partizionamento
- Dati importati di recente tramite l'API BigQuery Storage Write o l'API Streaming precedente
- Filtri con sottoquery o predicati non supportati
L'idoneità all'ottimizzazione può variare in base al tipo di partizionamento, ai metadati di archiviazione sottostanti e ai predicati di filtro. Come best practice, esegui una prova generale per verificare che la query restituisca 0 byte elaborati.
Transazione con più istruzioni
Questa ottimizzazione funziona all'interno di una transazione con più istruzioni. Il seguente esempio di query sostituisce una partizione con i dati di un'altra tabella in una singola transazione, senza eseguire la scansione della partizione per 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 per data di importazione
La seguente istruzione UPDATE
sposta le righe da una partizione all'altra.
Le 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
L'aggiornamento dei dati in una tabella partizionata mediante DML è uguale all'aggiornamento dei dati
da una tabella non partizionata. Ad esempio, la seguente istruzione UPDATE
sposta le righe da una partizione all'altra. Le 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.mycolumntable SET ts = "2017-06-01" WHERE DATE(ts) = "2017-05-01" AND field1 = 21
DML nelle tabelle partizionate orarie, mensili e annuali
Puoi utilizzare le istruzioni DML per modificare una tabella partizionata oraria, mensile o annuale. Fornisci l'intervallo di ore, mesi o anni delle date/timestamp/datetime pertinenti, come nel seguente esempio per le tabelle partizionate mensilmente:
bq query --nouse_legacy_sql 'DELETE FROM my_dataset.my_table WHERE TIMESTAMP_TRUNC(ts_column, MONTH) = "2020-01-01 00:00:00";'
Un altro esempio per le tabelle partizionate con la 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 DML MERGE
per combinare le operazioni INSERT
, UPDATE
e DELETE
per una tabella partizionata
in un'unica istruzione ed eseguirle in modo atomico.
Eliminazione delle partizioni quando si utilizza un'istruzione MERGE
Quando esegui un'istruzione MERGE
su una tabella partizionata, puoi limitare
le partizioni analizzate includendo la colonna di partizionamento in un filtro di una sottoquery, in un filtro search_condition
o in un filtro merge_condition
.
Il pruning può verificarsi durante la scansione della tabella di origine, della tabella di destinazione o di entrambe.
Ciascuno degli esempi riportati di seguito esegue una 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 nella clausola USING
filtra
la 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
Esaminando il piano di esecuzione della query, la subquery viene eseguita per prima. Vengono scansionate solo le righe nella partizione '2018-01-01'
della tabella di origine. Ecco la fase
pertinente nel piano di query:
READ $10:name, $11:_PARTITIONTIME
FROM temp.source
WHERE equal($11, 1514764800.000000000)
Utilizzo di un filtro nel search_condition
di un when_clause
Se un search_condition
contiene un filtro, l'ottimizzatore di query tenta di
eliminare le partizioni. Ad esempio, nella seguente istruzione MERGE
, ogni clausola WHEN
MATCHED
e WHEN NOT MATCHED
contiene un filtro sulla pseudocolonna _PARTITIONTIME
.
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 unione, nella tabella di destinazione vengono analizzate solo le seguenti partizioni: '2018-01-01'
, '2018-01-02'
e '2018-01-03'
, ovvero l'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, nel seguente esempio, la clausola WHEN NOT MATCHED BY SOURCE
non
ha 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 vengono eliminate partizioni.
Utilizzo di un predicato false costante in un merge_condition
Se utilizzi le clausole WHEN NOT MATCHED
e WHEN NOT MATCHED BY SOURCE
insieme, BigQuery esegue in genere un full outer join, che
non può essere eliminato. Tuttavia, se la condizione di unione utilizza un predicato costante false, BigQuery può utilizzare la condizione di filtro per l'eliminazione delle partizioni. Per ulteriori informazioni sull'utilizzo di predicati costanti falsi, consulta la descrizione della clausola merge_condition
nell'istruzione MERGE
della documentazione.
L'esempio seguente esegue la scansione solo della partizione '2018-01-01'
nelle tabelle di destinazione e 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 le partizioni. L'ottimizzatore di 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 unire le tabelle di origine e di destinazione. Lo strumento di ottimizzazione delle query può eseguire il push di questo predicato verso il basso
quando analizza entrambe le tabelle. Di conseguenza, la query analizza solo la partizione '2018-01-01'
sia nella tabella di destinazione sia 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
Nell'esempio successivo, merge_condition
non contiene un predicato per la
tabella di origine, quindi non è possibile eseguire il partizionamento della tabella di origine. L'istruzione
contiene un predicato per la tabella di destinazione, ma utilizza
una clausola WHEN NOT MATCHED BY SOURCE
anziché una clausola WHEN MATCHED
. Ciò
significa che la query deve scansionare l'intera tabella di destinazione per le 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 del DML, vedi Limitazioni nella pagina Riferimento DML.
Quote
Per informazioni sulle quote DML, consulta la sezione Istruzioni DML nella pagina Quote e limiti.
Prezzi
Per informazioni sui prezzi DML, consulta come calcolare le dimensioni delle query per le istruzioni DML eseguite su tabelle partizionate.
Sicurezza delle tabelle
Per controllare l'accesso alle tabelle in BigQuery, vedi Controllare l'accesso alle risorse con IAM.
Passaggi successivi
- Scopri come creare tabelle partizionate
- Scopri come eseguire query sulle tabelle partizionate
- Scopri di più sul linguaggio DML
- Scopri come comporre istruzioni DML utilizzando la sintassi DML.