Trasformare i dati con DML (Data Manipulation Language)

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

Puoi eseguire le istruzioni DML 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 le modifiche apportate dall'istruzione vengono eseguite automaticamente alla fine di ogni istruzione DML riuscita.

• Le righe scritte di recente utilizzando il metodo di streaming tabledata.insertall non possono essere modificate con DML (Data Manipulation Language), ad esempio con le 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 essere disponibili per le operazioni di copia.

  In alternativa, le righe scritte di recente utilizzando l'API Storage Write possono essere modificate utilizzando le istruzioni UPDATE, DELETE o MERGE. Per saperne di più, consulta Utilizzare DML (Data Manipulation Language) con i dati in streaming di recente.

• Le sottoquery correlate all'interno di una 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 target della query. Ad esempio, una tabella con funzione carattere jolly può essere utilizzata nella clausola FROM di una query UPDATE, ma una tabella con funzione carattere jolly non può essere utilizzata come target dell'operazione UPDATE.

Istruzioni DML

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

Istruzione INSERT

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 INSERT istruzione.

Istruzione DELETE

Utilizza l'istruzione DELETE per eliminare le righe in una tabella. L'esempio seguente elimina tutte le righe della tabella dataset.Inventory che hanno il valore quantity pari a 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 invece l'istruzione TRUNCATE TABLE. Per saperne di più sulle istruzioni DELETE, consulta DELETE istruzione.

Istruzione TRUNCATE

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

TRUNCATE dataset.Inventory

Per eliminare righe specifiche in una tabella, utilizza invece l'istruzione DELETE. Per saperne di più sull'istruzione TRUNCATE, consulta Istruzione TRUNCATE.

Istruzione UPDATE

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 le tabelle unite. Per saperne di più sulle istruzioni UPDATE, consulta UPDATE istruzione.

Istruzione MERGE

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

Job simultanei

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

Nota: le istruzioni DML sono soggette a limiti di frequenza, ad esempio la frequenza massima di scrittura delle tabelle. Potresti raggiungere un limite di frequenza se invii un numero elevato di job a una tabella contemporaneamente. Queste frequenze non limitano il numero totale di istruzioni DML che possono essere eseguite. Se ricevi un messaggio di errore che indica che hai superato un limite di frequenza, prova nuovamente l'operazione utilizzando il backoff esponenziale tra un nuovo tentativo e l'altro.

Concorrenza DML INSERT

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

Se devi eseguire le istruzioni DML INSERT con maggiore frequenza, valuta la possibilità di eseguire lo streaming dei dati nella tabella utilizzando l' API Storage Write.

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 sono ancora in esecuzione (o in attesa) altri job DML di mutazione, BigQuery ne esegue fino a 2 contemporaneamente, dopodiché ne mette in coda fino a 20 come PENDING. Al termine di un job in esecuzione, 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 riescono a essere eseguite 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 messi in coda per più di 7 ore non riescono a essere eseguiti 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 le istruzioni tentano di modificare la stessa partizione. Le istruzioni vengono eseguite correttamente a condizione che 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 altre istruzioni 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 le righe esistenti. Ciò può includere istruzioni MERGE con clausole UPDATE o DELETE, a condizione che queste clausole non vengano richiamate durante l'esecuzione della query.

DML granulare

Anteprima

Questa funzionalità è soggetta ai "Termini dell'offerta pre-GA" nella sezione dei Termini di servizio generali dei Termini specifici dei servizi. Puoi elaborare i dati personali per questa funzionalità come indicato nell' Addendum per il trattamento dei dati Cloud, fatti salvi gli obblighi e le restrizioni descritti nell' accordo che regola l'accesso a Google Cloud. Le funzionalità pre-GA sono disponibili "così come sono" e potrebbero avere un supporto limitato. Per ulteriori informazioni, consulta le descrizioni della fase di lancio.

Nota: per fornire feedback o richiedere assistenza per questa funzionalità, invia un'email all'indirazione [e-mail].

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

Se il DML granulare non è abilitato, le mutazioni DML vengono eseguite a livello di gruppo di file, il che può portare a riscritture inefficienti dei dati, soprattutto per le mutazioni sparse. Ciò può comportare un consumo di slot aggiuntivo 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 il tempo di elaborazione, I/O e slot consumato per i job DML di mutazione.

Quando utilizzi il DML granulare, devi tenere presente alcune considerazioni sulle prestazioni:

• Le operazioni DML granulari elaborano i dati eliminati con un approccio ibrido che distribuisce i costi di riscrittura su numerose mutazioni delle tabelle. Ogni operazione DML potrebbe elaborare una parte dei dati eliminati e quindi trasferire l'elaborazione dei dati eliminati rimanenti in un processo di garbage collection in background. Per saperne di più, consulta Considerazioni sui dati eliminati.
• Le tabelle con operazioni DML di mutazione frequenti potrebbero riscontrare una maggiore latenza 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 riduce la quantità di byte analizzati dell'istruzione DML di mutazione stessa.

Attivare il DML granulare

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

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

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 statement:

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' ALTER TABLE istruzione:

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 attivate e utilizzano la sintassi delle istruzioni DML esistenti .

Per determinare se una tabella è stata abilitata con DML granulare, esegui una query sulla INFORMATION_SCHEMA.TABLES visualizzazione. L'esempio seguente verifica quali tabelle all'interno 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 il DML granulare è abilitato per le tabelle.

Disattivare il DML granulare

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

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

Quando disattivi il DML granulare, potrebbe essere necessario del tempo prima che tutti i dati eliminati vengano elaborati completamente. Per saperne di più, consulta Considerazioni sui dati eliminati. Di conseguenza, le limitazioni del DML granulare potrebbero persistere fino a quando non si verifica questa situazione.

Prezzi

L'attivazione del 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 l'elaborazione dei dati eliminati utilizzando i job di garbage collectionscaricati e le query SELECTsuccessive che elaborano i metadati di eliminazione aggiuntivi che devono ancora essere sottoposti a garbage collection.

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

I job di elaborazione dei dati eliminati scaricati 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' BACKGROUND assegnazione utilizzano i prezzi on demand per elaborare i job di dati eliminati scaricati.

Operazione	Prezzi on demand	Prezzi basati sulla capacità
Istruzioni DML di mutazione	Utilizza il dimensionamento DML standard per determinare i calcoli dei byte analizzati on demand. L'attivazione del DML granulare non riduce la quantità di byte analizzati dell' istruzione DML stessa.	Consuma gli slot assegnati con un tipo QUERY al momento dell'esecuzione dell'istruzione.
Job di elaborazione dei dati eliminati scaricati	Utilizza il dimensionamento DML standard per determinare i calcoli dei byte analizzati on demand quando vengono eseguiti i job di elaborazione dei dati eliminati.	Consuma gli slot assegnati con un tipo BACKGROUND quando vengono eseguiti i job di elaborazione dei dati eliminati.

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 scaricata per distribuire i costi di riscrittura e ottimizzare le prestazioni su più istruzioni DML di mutazione emesse 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 scaricati in un processo in background per la pulizia successiva.

I progetti che eseguono operazioni DML granulari con un'assegnazione BACKGROUND elaborano le attività di garbage collection scaricate 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 scaricate 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 scaricate utilizzando le risorse interne di BigQuery e vengono addebitati in base alle tariffe dei prezzi on demand. Per saperne di più, consulta Prezzi.

La tempistica delle attività di garbage collection scaricate è 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 continue, ogni DML elabora una parte del carico di lavoro di garbage collection per garantire prestazioni di lettura e scrittura coerenti. Di conseguenza, la garbage collection viene elaborata regolarmente durante l'esecuzione dei DML successivi.
• Se non si verifica alcuna attività DML successiva su una tabella, la garbage collection scaricata 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 elaborazione dei dati eliminati DML granulari scaricati, esegui una query sulla INFORMATION_SCHEMA.JOBS visualizzazione:

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 le tabelle di grandi dimensioni con partizioni modificate di frequente che superano i 2 TB, il DML granulare non è consigliato. Queste tabelle potrebbero riscontrare una maggiore pressione sulla memoria per le query successive, il che può comportare una maggiore latenza di lettura o errori di query.
• Su una tabella con DML granulare abilitato può essere eseguita una sola istruzione DML di mutazione alla volta. 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.
• Non è possibile eliminare singolarmente o sovrascriverele partizioni di una tabella abilitata con DML granulare. 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 tabledata.list metodo 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 aver eseguito un'istruzione UPDATE, DELETE o MERGE.
• Non puoi attivare il DML granulare su una tabella in un set di dati replicato e non puoi replicare un set di dati che contiene una 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 INFORMATION_SCHEMA.TABLE_STORAGE visualizzazioni e INFORMATION_SCHEMA.PARTITIONS visualizzazioni 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 ottenere prestazioni ottimali, Google consiglia i seguenti pattern:

• Evita di inviare un numero elevato di aggiornamenti o inserimenti di singole righe. Raggruppa invece le operazioni DML quando possibile. Per saperne di più, consulta Istruzioni DML che aggiornano o inseriscono singole righe.

• Se gli aggiornamenti o le eliminazioni avvengono in genere su dati precedenti 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 frazione elevata delle partizioni.

• Se aggiorni spesso le righe in cui una o più colonne rientrano in un intervallo di valori ristretto, valuta la possibilità di utilizzare le tabelle in cluster. Il clustering garantisce che le modifiche siano limitate a set di blocchi specifici, riducendo la quantità di dati che devono essere letti e scritti. 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 risiedono in Cloud SQL.

• Per risolvere e prevenire 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 e esempi sulla sintassi DML, consulta Sintassi DML.
• Scopri di più su Aggiornamento dei dati tabella partizionata mediante DML.
• Per informazioni sull'utilizzo delle istruzioni DML nelle query pianificate, consulta Pianificare le query.