Trasforma 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 istruzioni DML proprio come faresti con un'istruzione SELECT, con le seguenti condizioni:

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

Per un elenco di istruzioni DML di BigQuery ed esempi di come utilizzarle, consulta Istruzioni del linguaggio di manipolazione dei dati in GoogleSQL.

Limitazioni

  • Ogni istruzione DML avvia una transazione implicita, il che significa che le modifiche apportate dall'istruzione vengono committate automaticamente alla fine di ogni istruzione DML andata a buon fine.

  • 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 gli ultimi 30 minuti. Tutte le altre righe della tabella rimangono modificabili utilizzando comandi 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 maggiori informazioni informazioni, vedi Utilizzare il Data Manipulation Language (DML) con flussi di dati recenti.

  • Le sottoquery correlate all'interno di un 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 FROM di una query UPDATE, ma non è possibile utilizzare una tabella con funzione carattere jolly come target dell'operazione UPDATE.

Job simultanei

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

INSERISCI contemporaneità DML

Durante qualsiasi periodo di 24 ore, le prime 1500 istruzioni INSERT vengono eseguite immediatamente dopo l'invio. Una volta raggiunto questo limite, di istruzioni di INSERT che scrivono in una tabella è limitato a 10. Ulteriori stmtINSERT vengono aggiunti 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 istruzioni INSERT DML più di frequente, valuta la possibilità di eseguire lo streaming dei dati nella tabella utilizzando l'API Storage Write.

AGGIORNA, ELIMINA, UNISCI contemporaneità DML

Le istruzioni DML UPDATE, DELETE e MERGE sono chiamate istruzioni DML con mutazioni. Se invii una o più istruzioni DML con modifica a una tabella mentre altri job DML con modifica sono ancora in esecuzione (o in attesa), BigQuery ne esegue fino a due contemporaneamente, dopodiché fino a 20 vengono messi in coda come PENDING. Al termine di un job in esecuzione precedente, il successivo job in attesa viene rimosso dalla coda ed eseguito. Le istruzioni DML con modifica in coda condividono una coda per tabella con una lunghezza massima di 20. Estratti conto aggiuntivi precedenti la lunghezza massima della coda per ogni tabella non riesce e viene visualizzato il messaggio di errore: Resources exceeded during query execution: Too many DML statements outstanding against table PROJECT_ID:TABLE, limit is 20.

I job DML con priorità interattiva che sono in coda da più di 6 ore non riescono con il seguente messaggio di errore:

DML statement has been queued for too long

Conflitti tra istruzione DML

La modifica delle istruzioni DML eseguite contemporaneamente su una tabella causa conflitti tra le istruzione DML quando le istruzioni tentano di mutare la stessa partizione. Le istruzioni hanno esito positivo purché non modifichino la stessa partizione. BigQuery prova a ripetere fino a tre volte le istruzioni non riuscite.

  • 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 eseguite contemporaneamente, a condizione che l'istruzione inserisca solo righe e non elimini o aggiorni righe esistenti. Può includere istruzioni MERGE con UPDATE o DELETE, purché non vengano richiamate quando delle query eseguite.

Prezzi

Per informazioni sui prezzi di DML, vedi Prezzi di Data Manipulation Language sulla pagina Prezzi.

Best practice

Per ottenere il massimo rendimento, Google consiglia i seguenti pattern:

  • Evita di inviare un numero elevato di aggiornamenti o inserimenti di singole righe. Raggruppa invece le operazioni DML, se possibile. Per ulteriori informazioni, vedi Istruzioni DML che aggiornano o inseriscono singole righe.

  • Se gli aggiornamenti o le eliminazioni si verificano in genere su dati meno recenti o in un determinato intervallo di date, valuta la possibilità di partizionare le tabelle. Il partizionamento assicura che le modifiche siano limitate a specifiche delle partizioni di Compute Engine 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 le 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 le modifiche sono limitate a insiemi specifici di blocchi, riducendo la quantità di dati che deve essere letto e scritto. Di seguito è riportato un esempio di un'istruzione UPDATE che applica un filtro 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 in cui viene applicato un filtro 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, ti consigliamo di utilizzare le query federate di Cloud SQL, che consentono a BigQuery di eseguire query sui dati memorizzati in Cloud SQL.

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

Passaggi successivi