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 attivare 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, vedi 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 istruzioni INSERT che scrivono in una tabella è limitata a 10. INSERT istruzioni aggiuntive vengono aggiunte a una coda PENDING. In qualsiasi momento possono essere accodate 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 successivo job in attesa 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 interattivi con priorità 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 eseguite contemporaneamente, 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). Se il linguaggio DML granulare non è abilitato, le modifiche vengono eseguite a livello di gruppo di file, il che può comportare riscrizioni inefficienti dei dati. La DML granulare introduce un approccio più granulare che mira a ridurre la quantità di dati da riscrivere e a ridurre il consumo complessivo di slot.

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 disattivare la DML granulare su una tabella, imposta enable_fine_grained_mutations su FALSE utilizzando l'istruzione DDL ALTER TABLE.

Prezzi

L'attivazione di DML granulare per una tabella può comportare costi di archiviazione BigQuery aggiuntivi per archiviare i metadati di mutazione aggiuntivi associati alle operazioni DML granulari. Il costo effettivo dipende dalla quantità di dati modificati, ma nella maggior parte dei casi si prevede che sia trascurabile rispetto alle dimensioni della tabella stessa.

Le operazioni DML granulari elaborano i dati eliminati in modalità offline. Questi job di elaborazione dei dati eliminati comportano costi di calcolo di BigQuery aggiuntivi.

Puoi utilizzare le prenotazioni BigQuery per allocare risorse di calcolo BigQuery dedicate per questi job di elaborazione dei dati eliminati offline. Le prenotazioni ti consentono di impostare un limite per il costo dell'esecuzione di queste operazioni. Questo approccio è particolarmente utile per tabelle molto grandi con frequenti operazioni DML di modifica granulare, che altrimenti avrebbero costi on demand elevati a causa del gran numero di byte elaborati durante l'esecuzione di ogni job di elaborazione offline dei dati eliminati granulari.

I job di elaborazione offline dei dati eliminati in modo granulare sono considerati job in background e utilizzano il BACKGROUND tipo di assegnazione, anziché il QUERY tipo di assegnazione. I progetti che eseguono operazioni DML granulari senza un processo di assegnazione BACKGROUND hanno eliminato i dati utilizzando i prezzi on demand.

Per i progetti configurati per utilizzare i prezzi di calcolo on demand, le istruzioni DML granulari non riducono i byte scansionati.

Per trovare i job di elaborazione dei dati eliminati DML granulari offline: 

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

Considerazioni sui dati eliminati

I progetti che eseguono operazioni DML granulari utilizzando i prezzi on demand hanno regolarmente dati eliminati elaborati utilizzando risorse BigQuery interne.

I progetti che eseguono operazioni DML granulari con un processo di assegnazione BACKGROUND hanno eliminato 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.

Limitazioni

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

  • 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 l'esecuzione di 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 abilitare 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 sono ottimizzate con DML granulare.

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 determinato intervallo di date, 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 frazione 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 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 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 si trovano in Cloud SQL.

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

Passaggi successivi