Aggiorna i dati della tabella partizionata utilizzando DML

Il Data Manipulation Language (DML) in BigQuery consente di inserire, aggiornare ed eliminare dati nelle tabelle partizionate in modo da poter modificare partizioni specifiche riducendo al minimo la quantità di dati analizzati.

Per saperne di più su DML, consulta Introduzione a DML, Sintassi DML e Aggiornamento dei dati delle tabelle utilizzando il linguaggio di manipolazione dei dati.

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 acquisizione 

    [
      {"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 TIMESTAMP ts 

    [
      {"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

Per aggiungere righe a una tabella partizionata, utilizza un'istruzione DML INSERT.

Inserimento di dati in tabelle partizionate per data di acquisizione

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 alla partizione utilizzando la pseudo-colonna _PARTITIONTIME.

Ad esempio, la seguente istruzione INSERT aggiunge una riga alla partizione del 1° maggio 2017 di mytable: “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 data 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 in tabelle partizionate

L'inserimento di dati in una tabella partizionata utilizzando DML è lo stesso dell'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

Utilizza un'istruzione DELETE DML per eliminare le righe da una tabella partizionata.

Eliminazione di dati in tabelle partizionate per data di acquisizione

La seguente istruzione DELETE elimina tutte le righe dalla partizione del 1° giugno 2017 ("2017-06-01") di mytable in cui field1 è uguale a 21. Puoi 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

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 byte o consumare 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

L'idoneità all'ottimizzazione può variare in base al tipo di partizionamento, ai metadati di archiviazione sottostanti e ai predicati di filtro. Ad esempio, le query con le seguenti caratteristiche potrebbero non trarre vantaggio dall'utilizzo di DML DELETE per l'ottimizzazione:

Questa ottimizzazione potrebbe non essere applicata anche alle tabelle partizionate raggruppate in cluster utilizzando colonne diverse da quelle partizionate.

Come best practice, esegui un dry run per verificare che la query restituisca 0 byte elaborati.

Transazione con più istruzioni

Questa ottimizzazione funziona all'interno di una transazione con più istruzioni. L'esempio di query seguente 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

Utilizza un'istruzione UPDATE per aggiornare le righe di una tabella partizionata.

Aggiornamento dei dati nelle tabelle partizionate per data di acquisizione

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 utilizzando 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 in tabelle partizionate per ora, mese e anno

Puoi utilizzare le istruzioni DML per modificare una tabella partizionata per ora, mese o anno. Fornisci l'intervallo di ore, mesi o anni delle date/timestamp/datetime pertinenti, come nell'esempio seguente 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

Utilizza 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 sottoquery, un filtro search_condition o un filtro merge_condition. La potatura può avvenire durante la scansione della tabella di origine o della tabella di destinazione o di entrambe.

Ognuno 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 in base alla 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

Se esaminiamo il piano di esecuzione della query, la prima a essere eseguita è la subquery. Vengono scansionate solo le righe della partizione '2018-01-01' nella 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 nella clausola search_condition di un'istruzione when_clause

Se un 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 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 join, 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 viene eseguita la potatura di 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 insieme, BigQuery di solito esegue un full outer join, che non può essere eliminato. Tuttavia, se la condizione di unione utilizza un predicato falso costante, BigQuery può utilizzare la condizione di filtro per la potatura delle partizioni. Per ulteriori informazioni sull'utilizzo di predicati falsi costanti, consulta la descrizione della clausola merge_condition nella documentazione dell'istruzione MERGE.

L'esempio seguente 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 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

Utilizzo di un filtro in un merge_condition

Query Optimizer tenta di utilizzare un filtro in un merge_condition per eliminare le partizioni. L'ottimizzatore 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 unire le tabelle di origine e di destinazione. Query Optimizer può eseguire il pushdown di questo predicato 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 seguente, merge_condition non contiene un predicato per la tabella di origine, quindi non è possibile eseguire la riduzione delle partizioni sulla 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 trovare 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 DML, consulta Limitazioni nella pagina Riferimento DML.

Quote

Per informazioni sulle quote DML, consulta Istruzioni DML nella pagina Quote e limiti.

Prezzi

Per informazioni sui prezzi di 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, consulta Controlla l'accesso alle risorse con IAM.

Passaggi successivi