Trasformare i dati con 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 proprio come faresti con un'istruzione SELECT, con le seguenti condizioni:

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

Per ulteriori informazioni 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 automaticamente eseguite alla fine di ogni istruzione DML riuscita.

  • Le righe scritte di recente utilizzando il metodo di streaming tabledata.insertall non possono essere modificate con il linguaggio di manipolazione dei dati (DML), ad esempio le istruzioni UPDATE, DELETE, MERGE o TRUNCATE. Le scritture recenti sono quelle che si sono verificate negli ultimi 30 minuti. Tutte le altre righe della tabella rimangono modificabili utilizzando le istruzioni UPDATE, DELETE, MERGE o TRUNCATE. Potrebbero essere necessari fino a 90 minuti prima che i dati in streaming diventino 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 ulteriori informazioni, consulta Utilizzare DML (Data Manipulation Language) con i dati di streaming recenti.

  • Le sottoquery correlate all'interno di un'istruzione 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 caratteri 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 estratto conto

Utilizza l'istruzione INSERT per aggiungere nuove righe a una tabella esistente. Il seguente esempio 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 estratto conto

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 maggiori informazioni sugli estratti conto DELETE, consulta Estratto conto DELETE.

TRUNCATE estratto conto

Utilizza l'istruzione TRUNCATE per rimuovere tutte le righe da una tabella, lasciando 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 ulteriori informazioni sull'istruzione TRUNCATE, consulta l'istruzione TRUNCATE.

UPDATE estratto conto

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 il valore di quantity delle righe di 10 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 ulteriori informazioni sugli estratti conto UPDATE, consulta Estratto conto UPDATE.

MERGE estratto conto

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 da una tabella a un'altra. Per ulteriori informazioni ed esempi sull'istruzione MERGE, consulta l'istruzione MERGE.

Job simultanei

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

Contemporaneità 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 INSERT istruzioni che scrivono in una tabella è limitata a 10. Ulteriori estratto conto INSERT vengono aggiunti a una coda PENDING. In un determinato momento, è possibile mettere in coda fino a 100 istruzioni INSERT per una tabella. Quando un'istruzione INSERT viene completata, l'istruzione INSERT successiva viene rimossa dalla coda ed eseguita.

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

Contemporaneità DML UPDATE, DELETE, MERGE

Le istruzioni DML UPDATE, DELETE e MERGE sono chiamate istruzioni DML mutanti. Se invii una o più istruzioni DML mutanti su una tabella mentre sono ancora in esecuzione (o in attesa) altri job DML mutanti, BigQuery ne esegue fino a due contemporaneamente, dopodiché fino a 20 vengono accodati come PENDING. Quando un job precedentemente in esecuzione termina, il job in attesa successivo viene rimosso dalla coda ed eseguito. Le istruzioni DML mutanti 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 da 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 di istruzione DML

Le istruzioni DML di mutazione eseguite contemporaneamente su una tabella causano conflitti tra le istruzione DML quando tentano di modificare la stessa partizione. Le istruzioni hanno esito positivo 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 qualsiasi altra istruzione DML in esecuzione contemporaneamente.

  • Un'istruzione DML MERGE non è in conflitto con altre istruzioni DML in esecuzione simultanea, a condizione che l'istruzione inserisca solo righe e non elimini o aggiorni 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

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

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 aggiuntivo di slot e tempi di esecuzione più lunghi.

La DML granulare è un miglioramento delle prestazioni progettato per ottimizzare queste istruzioni DML mutanti 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 significativamente il tempo di elaborazione, I/O e slot utilizzato per i job DML di mutazione.

Quando utilizzi il linguaggio DML granulare, devi tenere presente alcune considerazioni sul rendimento:

  • Le operazioni DML granulari elaborano i dati eliminati con un approccio ibrido che distribuisce i costi di riscrittura su 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 maggiori informazioni, 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.
  • Per le tabelle di grandi dimensioni con partizioni mutate di frequente che superano i 2 TB, non è consigliabile utilizzare DML granulare. Queste tabelle potrebbero subire una maggiore pressione della memoria per le query successive, il che può comportare una latenza di lettura aggiuntiva o errori di query.
  • L'attivazione di DML granulare non riduce la quantità di byte analizzati dell'istruzione DML di mutazione stessa.

Abilita DML granulare

Per attivare DML granulare, imposta l'opzione enable_fine_grained_mutations table 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;

Una volta impostata 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 esistenti.

Per determinare se una tabella è stata abilitata con DML granulare, esegui una query sulla visualizzazione INFORMATION_SCHEMA.TABLES. Il seguente esempio verifica quali tabelle all'interno di un set di dati sono state attivate 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 abilitate istruzioni DML granulari.

Disattiva DML granulare

Per disattivare 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 finché non si verifica.

Prezzi

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

  • Costi di archiviazione BigQuery per archiviare i metadati di mutazione aggiuntivi associati alle operazioni DML granulari. Il costo effettivo di archiviazione 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 calcolo di BigQuery per elaborare i dati eliminati utilizzando job di garbage collection offloaded e le successive query SELECTche elaborano 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 l'elaborazione dei 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 tabelle molto grandi con frequenti operazioni DML di mutazione granulare, che altrimenti avrebbero costi on demand elevati a causa del gran numero di byte elaborati durante l'esecuzione di ogni job di elaborazione dei dati eliminati scaricati.

I job di trattamento dei dati eliminati con DML granulare offloaded sono considerati job in background e richiedono l'utilizzo del tipo di assegnazione di prenotazione BACKGROUND, anziché del tipo di assegnazione di 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 scaricati.

Operazione Prezzi on demand Prezzi basati sulla capacità
Istruzioni DML mutanti Utilizza il dimensionamento DML standard per determinare i calcoli dei byte on demand scansionati.

L'attivazione di 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'estratto conto.
Job di trattamento dati eliminati scaricati Utilizza il dimensionamento DML standard per determinare i calcoli dei byte on demand scansionati quando vengono eseguiti i job di elaborazione dei dati eliminati. Utilizza gli slot assegnati con un tipo BACKGROUND quando vengono eseguiti job di elaborazione dei dati eliminati.

Considerazioni sui dati eliminati

I progetti che eseguono operazioni DML granulari con un processo di assegnazione BACKGROUND eliminano i dati utilizzando gli slot e sono soggetti alla disponibilità delle risorse della prenotazione configurata. Se non sono disponibili risorse sufficienti all'interno della prenotazione configurata, l'elaborazione dei dati eliminati potrebbe richiedere più tempo del previsto.

I progetti che eseguono operazioni DML granulari utilizzando i prezzi on demand o senza un'assegnazione BACKGROUND elaborano i dati eliminati utilizzando i prezzi on demand e i dati eliminati vengono regolarmente elaborati utilizzando le risorse interne di BigQuery.

Per identificare i job di trattamento dati eliminati DML granulari di cui è stato eseguito l'offload, esegui una query sulla INFORMATION_SCHEMA.JOBS vista: 

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:

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 ulteriori informazioni, vedi Istruzioni DML che aggiornano o inseriscono singole righe.

  • Se gli aggiornamenti o le eliminazioni in genere riguardano dati meno recenti o 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 è piccola e ogni aggiornamento modifica una grande parte delle partizioni.

  • Se aggiorni spesso righe in cui una o più colonne rientrano in un intervallo ristretto di valori, valuta la possibilità di utilizzare tabelle in cluster. Il clustering garantisce che le modifiche siano limitate a set specifici di blocchi, 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 nella 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