Trasforma i dati con il data manipulation language (DML)

Il Data Manipulation Language (DML) di BigQuery consente di aggiornare, inserire ed eliminare dati dalle tabelle BigQuery.

Puoi eseguire istruzioni DML esattamente come faresti con un'istruzione SELECT, con le seguenti condizioni:

• Devi utilizzare GoogleSQL. Per attivare GoogleSQL, consulta Cambio dei dialetti SQL.
• Non puoi specificare una tabella di destinazione per la query.

Per saperne di più su come calcolare il numero di byte elaborati da un'istruzione DML, consulta Calcolo delle dimensioni delle query on demand.

Limitazioni

• Ogni istruzione DML avvia una transazione implicita, il che significa che viene eseguito automaticamente il commit delle modifiche apportate dall'istruzione alla fine di ogni istruzione DML riuscita.

• Le righe scritte di recente utilizzando l'API BigQuery Storage Write (REST) non possono essere modificate con il linguaggio di manipolazione dei dati (DML), ad esempio con istruzioni UPDATE, DELETE, MERGE o TRUNCATE. Le scritture recenti sono quelle avvenute negli ultimi 30 minuti. Tutte le altre righe della tabella rimangono modificabili utilizzando le istruzioni UPDATE, DELETE, MERGE o TRUNCATE. I dati in streaming possono richiedere fino a 90 minuti per diventare disponibili per le operazioni di copia.

  In alternativa, le righe scritte di recente utilizzando l'API Storage Write (gRPC) possono essere modificate utilizzando le istruzioni UPDATE, DELETE o MERGE. Per saperne di più, consulta Utilizza data manipulation language (DML) con i dati trasmessi in streaming di recente.

• Le sottoquery correlate all'interno di when_clause, search_condition, merge_update_clause o merge_insert_clause non sono supportate per le istruzioni MERGE.

• Le query che contengono istruzioni DML non possono utilizzare una tabella con funzione carattere jolly come destinazione della query. Ad esempio, una tabella con funzione carattere jolly può essere utilizzata nella clausola FROM di una query UPDATE, ma non può essere utilizzata come destinazione dell'operazione UPDATE.

Istruzioni DML

Le sezioni seguenti descrivono i diversi tipi di istruzioni DML e come puoi utilizzarle.

INSERT istruzioni

Utilizza l'istruzione INSERT per aggiungere nuove righe a una tabella esistente. L'esempio seguente inserisce nuove righe nella tabella dataset.Inventory con valori specificati in modo esplicito.

INSERT dataset.Inventory (product, quantity)
VALUES('whole milk', 10),
      ('almond milk', 20),
      ('coffee beans', 30),
      ('sugar', 0),
      ('matcha', 20),
      ('oat milk', 30),
      ('chai', 5)

/+-------------------+----------+
 |      product      | quantity |
 +-------------------+----------+
 | almond milk       |       20 |
 | chai              |        5 |
 | coffee beans      |       30 |
 | matcha            |       20 |
 | oat milk          |       30 |
 | sugar             |        0 |
 | whole milk        |       10 |
 +-------------------+----------+/

Per saperne di più sulle istruzioni INSERT, consulta l'istruzione INSERT.

DELETE istruzioni

Utilizza l'istruzione DELETE per eliminare le righe di una tabella. L'esempio seguente elimina tutte le righe della tabella dataset.Inventory che hanno il valore quantity 0.

DELETE dataset.Inventory
WHERE quantity = 0

/+-------------------+----------+
 |      product      | quantity |
 +-------------------+----------+
 | almond milk       |       20 |
 | chai              |        5 |
 | coffee beans      |       30 |
 | matcha            |       20 |
 | oat milk          |       30 |
 | whole milk        |       10 |
 +-------------------+----------+/

Per eliminare tutte le righe di una tabella, utilizza l'istruzione TRUNCATE TABLE. Per saperne di più sulle istruzioni DELETE, consulta Istruzione DELETE.

TRUNCATE istruzioni

Utilizza l'istruzione TRUNCATE per rimuovere tutte le righe da una tabella, ma lascia intatti i metadati della tabella, inclusi lo schema, la descrizione e le etichette della tabella. L'esempio seguente rimuove tutte le righe dalla tabella dataset.Inventory.

TRUNCATE dataset.Inventory

Per eliminare righe specifiche in una tabella, utilizza l'istruzione DELETE. Per ulteriori informazioni sull'istruzione TRUNCATE, consulta Istruzione TRUNCATE.

UPDATE istruzioni

Utilizza l'istruzione UPDATE per aggiornare le righe esistenti in una tabella. L'istruzione UPDATE deve includere anche la parola chiave WHERE per specificare una condizione. L'esempio seguente riduce di 10 il valore quantity delle righe per i prodotti che contengono la stringa milk.

UPDATE dataset.Inventory
SET quantity = quantity - 10,
WHERE product LIKE '%milk%'

/+-------------------+----------+
 |      product      | quantity |
 +-------------------+----------+
 | almond milk       |       10 |
 | chai              |        5 |
 | coffee beans      |       30 |
 | matcha            |       20 |
 | oat milk          |       20 |
 | whole milk        |        0 |
 +-------------------+----------+/

Le istruzioni UPDATE possono includere anche clausole FROM per includere tabelle unite. Per saperne di più sulle istruzioni UPDATE, consulta Istruzione UPDATE.

MERGE istruzioni

L'istruzione MERGE combina le operazioni INSERT, UPDATE e DELETE in un'unica istruzione ed esegue le operazioni in modo atomico per unire i dati di una tabella a un'altra. Per saperne di più e per esempi sull'istruzione MERGE, consulta Istruzione MERGE.

Job simultanei

BigQuery gestisce la contemporaneità delle istruzioni DML che aggiungono, modificano o eliminano righe in una tabella.

Concorrenza DML INSERT

In qualsiasi periodo di 24 ore, le prime 1500 istruzioni INSERT vengono eseguite immediatamente dopo l'invio. Una volta raggiunto questo limite, la contemporaneità delle istruzioni INSERT che scrivono in una tabella è limitata a 10. Le istruzioni INSERT aggiuntive vengono aggiunte a una coda PENDING. È possibile mettere in coda fino a 100 istruzioni INSERT per una tabella in qualsiasi momento. Quando un'istruzione INSERT viene completata, l'istruzione INSERT successiva viene rimossa dalla coda ed eseguita.

Se devi eseguire istruzioni DML INSERT più di frequente, valuta la possibilità di trasmettere i dati in streaming alla tabella utilizzando l'API Storage Write (gRPC).

Concorrenza DML UPDATE, DELETE, MERGE

Le istruzioni DML UPDATE, DELETE e MERGE sono chiamate istruzioni DML di mutazione. Se invii una o più istruzioni DML di mutazione su una tabella mentre altri job DML di mutazione sono ancora in esecuzione (o in attesa), BigQuery ne esegue fino a 2 contemporaneamente, dopodiché fino a 20 vengono messi in coda come PENDING. Quando un job precedentemente in esecuzione termina, il job in attesa successivo viene rimosso dalla coda ed eseguito. Le istruzioni DML di mutazione in coda condividono una coda per tabella con una lunghezza massima di 20. Le istruzioni aggiuntive oltre la lunghezza massima della coda per ogni tabella non vanno a buon fine e viene visualizzato il messaggio di errore: Resources exceeded during query execution: Too many DML statements outstanding against table PROJECT_ID:DATASET.TABLE, limit is 20.

I job DML con priorità interattiva in coda per più di 7 ore non vanno a buon fine e viene visualizzato il seguente messaggio di errore:

DML statement has been queued for too long

Conflitti tra istruzione DML

Le istruzioni DML di mutazione eseguite contemporaneamente su una tabella causano conflitti tra le istruzione DML quando queste tentano di mutare la stessa partizione. Le istruzioni vengono eseguite correttamente purché non modifichino la stessa partizione. BigQuery tenta di eseguire nuovamente le istruzioni non riuscite fino a tre volte.

• Un'istruzione DML INSERT che inserisce righe in una tabella non è in conflitto con nessun'altra istruzione DML in esecuzione contemporaneamente.

• Un'istruzione DML MERGE non è in conflitto con altre istruzioni DML in esecuzione contemporaneamente, a condizione che l'istruzione inserisca solo righe e non elimini o aggiorni righe esistenti. Sono incluse le istruzioni MERGE con clausole UPDATE o DELETE, purché queste clausole non vengano richiamate durante l'esecuzione della query.

• La coda DML automatica si applica solo alle istruzioni DML autonome eseguite all'esterno di una transazione con più istruzioni. Le istruzioni DML di mutazione all'interno di una transazione con più istruzioni acquisiscono un blocco a livello di tabella anziché bloccare singole partizioni. Le transazioni multi-istruzione simultanee che modificano la stessa tabella non vengono messe in coda e non vanno a buon fine immediatamente, anche se hanno come target partizioni diverse. Se una stored procedure o una query con più istruzioni esegue una singola istruzione DML di mutazione, ad esempio un'istruzione MERGE, non inserire l'istruzione tra le istruzioni BEGIN TRANSACTION e COMMIT TRANSACTION. Per saperne di più, consulta Concorrenza delle transazioni.

DML granulare

Il DML granulare è un miglioramento delle prestazioni progettato per ottimizzare l'esecuzione delle istruzioni UPDATE, DELETE e MERGE (note anche come istruzioni DML di mutazione).

Considerazioni sulle prestazioni

Senza DML granulare abilitato, le mutazioni DML vengono eseguite a livello di gruppo di file, il che può portare a riscritture dei dati inefficienti, soprattutto per le mutazioni sparse. Ciò può comportare un consumo aggiuntivo di slot e tempi di esecuzione più lunghi.

Il DML granulare è un miglioramento delle prestazioni progettato per ottimizzare queste istruzioni DML di mutazione introducendo un approccio più granulare che mira a ridurre la quantità di dati che devono essere riscritti a livello di gruppo di file. Questo approccio può ridurre in modo significativo i tempi di elaborazione, I/O e slot utilizzati per i job DML di mutazione.

Esistono alcune considerazioni sul rendimento da tenere presenti quando utilizzi il DML granulare:

• Le operazioni DML granulari elaborano i dati eliminati con un approccio ibrido che distribuisce i costi di riscrittura tra numerose mutazioni della tabella. Ogni operazione DML potrebbe elaborare una parte dei dati eliminati e poi scaricare l'elaborazione dei dati eliminati rimanenti in un processo di garbage collection in background. Per saperne di più, consulta le considerazioni sui dati eliminati.
• Le tabelle con operazioni DML di mutazione frequenti potrebbero registrare una latenza maggiore per le query SELECT e i job DML successivi. Per valutare l'impatto dell'attivazione di questa funzionalità, esegui il benchmark delle prestazioni di una sequenza realistica di operazioni DML e delle letture successive.
• L'attivazione del DML granulare non ridurrà la quantità di byte scansionati dell'istruzione DML di mutazione stessa.

Abilita DML granulare

Per attivare il DML granulare, imposta l'opzione della tabella enable_fine_grained_mutations su TRUE quando esegui un'istruzione DDL CREATE TABLE o ALTER TABLE.

Per creare una nuova tabella con DML granulare, utilizza l'istruzione CREATE TABLE:

CREATE TABLE mydataset.mytable (
  product STRING,
  inventory INT64)
OPTIONS(enable_fine_grained_mutations = TRUE);

Per modificare una tabella esistente con DML granulare, utilizza l'istruzione ALTER TABLE:

ALTER TABLE mydataset.mytable
SET OPTIONS(enable_fine_grained_mutations = TRUE);

Per modificare tutte le tabelle esistenti in un set di dati con DML granulare, utilizza l'istruzione ALTER TABLE:

FOR record IN
 (SELECT CONCAT(table_schema, '.', table_name) AS table_path
 FROM mydataset.INFORMATION_SCHEMA.TABLES)
DO
 EXECUTE IMMEDIATE
   "ALTER TABLE " || record.table_path || " SET OPTIONS(enable_fine_grained_mutations = TRUE)";
END FOR;

Dopo aver impostato l'opzione enable_fine_grained_mutations su TRUE, le istruzioni DML di mutazione vengono eseguite con le funzionalità DML granulari abilitate e utilizzano la sintassi delle istruzioni DML esistente.

Per determinare se una tabella è stata abilitata con DML granulare, esegui una query sulla vista INFORMATION_SCHEMA.TABLES. L'esempio seguente controlla quali tabelle di un set di dati sono state abilitate con questa funzionalità:

SELECT
  table_schema AS datasetId,
  table_name AS tableId,
  is_fine_grained_mutations_enabled
FROM
  DATASET_NAME.INFORMATION_SCHEMA.TABLES;

Sostituisci DATASET_NAME con il nome del set di dati in cui verificare se sono presenti tabelle con DML granulare abilitato.

Disabilita DML granulare

Per disattivare il DML granulare da una tabella esistente, utilizza l'istruzione ALTER TABLE.

ALTER TABLE mydataset.mytable
SET OPTIONS(enable_fine_grained_mutations = FALSE);

Quando disattivi il DML granulare, l'elaborazione completa di tutti i dati eliminati potrebbe richiedere del tempo. Consulta Considerazioni sui dati eliminati. Di conseguenza, le limitazioni DML granulari potrebbero persistere fino a quando non si verifica questo evento.

Prezzi

L'attivazione di DML granulare per una tabella può comportare costi aggiuntivi. Questi costi includono:

• Costi di archiviazione di BigQuery per archiviare i metadati di mutazione aggiuntivi associati alle operazioni DML granulari. Il costo di archiviazione effettivo dipende dalla quantità di dati modificati, ma nella maggior parte dei casi si prevede che sia trascurabile rispetto alle dimensioni della tabella stessa.
• Costi di computing di BigQuery per elaborare i dati eliminati utilizzando job di garbage collection offloaded e query SELECT successive che elaborano metadati di eliminazione aggiuntivi che non sono ancora stati sottoposti a garbage collection.

Puoi utilizzare le prenotazioni BigQuery per allocare risorse di calcolo BigQuery dedicate per l'elaborazione dei job di dati eliminati di cui è stato eseguito l'offload. Le prenotazioni ti consentono di impostare un limite per il costo dell'esecuzione di queste operazioni. Questo approccio è particolarmente utile, e spesso consigliato, per tabelle molto grandi con frequenti operazioni DML di mutazione granulari, che altrimenti avrebbero costi on demand elevati a causa dell'elevato numero di byte elaborati durante l'esecuzione di ogni job di trattamento dati eliminati offloaded.

I job di trattamento dati eliminati con l'offload del DML granulare sono considerati job in background e richiedono l'utilizzo del tipo di assegnazione della prenotazione BACKGROUND, anziché del tipo di assegnazione della prenotazione QUERY. I progetti che eseguono operazioni DML granulari senza un'assegnazione BACKGROUND utilizzano i prezzi on demand per elaborare i job di dati eliminati di cui è stato eseguito l'offload.

Considerazioni sui dati eliminati

Le operazioni DML granulari utilizzano un approccio ibrido per gestire i dati eliminati, combinando l'elaborazione in linea con la garbage collection offloaded per distribuire i costi di riscrittura e ottimizzare le prestazioni tra più istruzioni DML di mutazione eseguite su una tabella.

Durante l'esecuzione di un'istruzione DML di mutazione, BigQuery tenta di eseguire in linea una parte della garbage collection pertinente dalle istruzioni DML precedenti. Tutti i dati eliminati non gestiti in linea vengono trasferiti a un processo in background per la successiva pulizia.

I progetti che eseguono operazioni DML granulari con un'assegnazione BACKGROUND eseguono l'offload delle attività di garbage collection utilizzando gli slot. L'elaborazione dei dati eliminati è soggetta alla disponibilità delle risorse della prenotazione configurata. Se non sono disponibili risorse sufficienti all'interno della prenotazione configurata, l'elaborazione delle operazioni di garbage collection con offload potrebbe richiedere più tempo del previsto.

I progetti che eseguono operazioni DML granulari utilizzando i prezzi on demand o senza un'assegnazione BACKGROUND elaborano le attività di garbage collection offloaded utilizzando risorse BigQuery interne e vengono addebitati in base alle tariffe dei prezzi on demand. Per saperne di più, consulta i prezzi.

La tempistica delle attività di garbage collection trasferite è determinata dalla frequenza dell'attività DML sulla tabella e dalla disponibilità delle risorse, se utilizzi un'assegnazione BACKGROUND:

• Per le tabelle con operazioni DML di mutazione continua, ogni DML elabora una parte del workload di garbage collection per garantire prestazioni di lettura e scrittura coerenti. Di conseguenza, la garbage collection viene elaborata regolarmente man mano che vengono eseguiti i DML successivi.
• Se non si verifica alcuna attività DML successiva su una tabella, la garbage collection con offload viene attivata automaticamente quando i dati eliminati raggiungono i 5 giorni di età.
• In rari casi, l'elaborazione completa dei dati eliminati potrebbe richiedere più tempo.

Per identificare i job di trattamento dati eliminati con DML granulare con offload, esegui una query sulla vista INFORMATION_SCHEMA.JOBS:

SELECT
  *
FROM
  region-us.INFORMATION_SCHEMA.JOBS
WHERE
  job_id LIKE "%fine_grained_mutation_garbage_collection%"

Limitazioni

Le tabelle abilitate con DML granulare sono soggette alle seguenti limitazioni:

• Per tabelle di grandi dimensioni con partizioni mutate di frequente che superano i 2 TB, il DML granulare non è consigliato. Queste tabelle potrebbero subire una maggiore pressione sulla memoria per le query successive, il che può comportare una latenza di lettura aggiuntiva o errori di query.
• È possibile eseguire una sola istruzione DML di mutazione alla volta su una tabella con DML granulare abilitato. I job successivi vengono messi in coda come PENDING. Per saperne di più sul comportamento della concorrenza DML di mutazione, consulta Concorrenza DML UPDATE, DELETE, MERGE.
• Una tabella abilitata con DML granulare non può avere partizioni eliminate singolarmente o sovrascritte. Per eliminare o sostituire i dati all'interno di una partizione, devi utilizzare un'istruzione DML di mutazione, ad esempio DELETE, UPDATE, MERGE o TRUNCATE.
• Non puoi utilizzare il metodo tabledata.list per leggere i contenuti di una tabella con DML granulare abilitato. Esegui invece una query sulla tabella con un'istruzione SELECT per leggere i record della tabella.
• Non è possibile visualizzare l'anteprima di una tabella abilitata con DML granulare utilizzando la console BigQuery.
• Non puoi copiare una tabella con DML granulare abilitato dopo aver eseguito un'istruzione UPDATE, DELETE o MERGE.
• Non puoi creare uno snapshot della tabella o un clone della tabella di una tabella con DML granulare abilitato dopo l'esecuzione di un'istruzione UPDATE, DELETE o MERGE.
• Non puoi rinominare una tabella con DML granulare abilitato dopo aver eseguito un'istruzione UPDATE, DELETE o MERGE.
• La replica tra regioni non è supportata per le tabelle con DML granulare abilitato. Sebbene il sistema blocchi la creazione di set di dati replicati che contengono già queste tabelle, non blocca l'attivazione del DML granulare sulle tabelle all'interno dei set di dati replicati esistenti. Tuttavia, ciò potrebbe comportare risultati di query errati nel database secondario per la tabella con DML granulare abilitato.
• Le istruzioni DML eseguite in una transazione con più istruzioni non vengono ottimizzate con il DML granulare.
• Non puoi attivare il DML granulare sulle tabelle temporanee create con l'istruzione CREATE TEMP TABLE.
• I metadati riportati nelle viste INFORMATION_SCHEMA.TABLE_STORAGE e nelle viste INFORMATION_SCHEMA.PARTITIONS possono includere temporaneamente i dati eliminati di recente utilizzando il DML granulare fino al completamento dei job di garbage collection in background.

Best practice

Per prestazioni ottimali, Google consiglia i seguenti pattern:

• Evita di inviare un numero elevato di aggiornamenti o inserimenti di singole righe. Invece, raggruppa le operazioni DML quando possibile. Per ulteriori informazioni, consulta Istruzioni DML che aggiornano o inseriscono singole righe.

• Se gli aggiornamenti o le eliminazioni si verificano in genere su dati meno recenti o all'interno di un intervallo di date specifico, valuta la possibilità di partizionare le tabelle. Il partizionamento garantisce che le modifiche siano limitate a partizioni specifiche all'interno della tabella.

• Evita di partizionare le tabelle se la quantità di dati in ogni partizione è ridotta e ogni aggiornamento modifica una grande parte delle partizioni.

• Se aggiorni spesso righe in cui una o più colonne rientrano in un intervallo di valori ristretto, valuta l'utilizzo di tabelle in cluster. Il clustering garantisce che le modifiche siano limitate a insiemi specifici di blocchi, riducendo la quantità di dati da leggere e scrivere. Di seguito è riportato un esempio di istruzione UPDATE che filtra in base a un intervallo di valori di colonna:

  UPDATE mydataset.mytable
  SET string_col = 'some string'
  WHERE id BETWEEN 54 AND 75;

  Ecco un esempio simile che filtra in base a un piccolo elenco di valori di colonna:

  UPDATE mydataset.mytable
  SET string_col = 'some string'
  WHERE id IN (54, 57, 60);

  In questi casi, valuta la possibilità di eseguire il clustering sulla colonna id.

• Se hai bisogno della funzionalità OLTP, valuta la possibilità di utilizzare le query federate di Cloud SQL, che consentono a BigQuery di eseguire query sui dati che si trovano in Cloud SQL.

• Per risolvere ed evitare l'errore di quota Too many DML statements outstanding against table, segui le indicazioni per questo errore nella pagina Risoluzione dei problemi di BigQuery.

Per le best practice per ottimizzare le prestazioni delle query, consulta Introduzione all'ottimizzazione delle prestazioni delle query.

Passaggi successivi

• Per informazioni ed esempi sulla sintassi DML, consulta Sintassi DML.
• Scopri di più sull'aggiornamento dei dati tabella partizionata utilizzando DML.
• Per informazioni sull'utilizzo delle istruzioni DML nelle query pianificate, consulta Pianificazione delle query.