Trasformare i dati con il Data Manipulation Language (DML)
Il Data Manipulation Language (DML) di BigQuery consente di aggiornare, inserire ed eliminare i dati dalle tabelle BigQuery.
Puoi eseguire istruzioni DML proprio come faresti con un'istruzione SELECT
, con le
condizioni seguenti:
- Devi utilizzare GoogleSQL. Per attivare GoogleSQL, consulta la sezione Cambio di 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 relative al 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 sottoposte automaticamente a commit alla fine di ogni istruzione DML riuscita.
Le righe scritte di recente utilizzando il metodo di inserimento di flussi
tabledata.insertall
non possono essere modificate con il Data Manipulation Language (DML), ad esempio le istruzioniUPDATE
,DELETE
,MERGE
oTRUNCATE
. Le scritture recenti sono quelle che si sono verificate negli ultimi 30 minuti. Tutte le altre righe della tabella rimangono modificabili utilizzando le istruzioniUPDATE
,DELETE
,MERGE
oTRUNCATE
. I flussi di dati possono richiedere fino a 90 minuti prima che siano 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
oMERGE
(anteprima). Per ulteriori informazioni, consulta Utilizzare il Data Manipulation Language (DML) con i flussi di dati trasmessi di recente.Le sottoquery correlate in
when_clause
,search_condition
,merge_update_clause
omerge_insert_clause
non sono supportate per le istruzioniMERGE
.Le query che contengono istruzioni DML non possono utilizzare una tabella con caratteri jolly come destinazione della query. Ad esempio, è possibile utilizzare una tabella con funzione carattere jolly nella clausola
FROM
di una queryUPDATE
, ma non come target dell'operazioneUPDATE
.
Job simultanei
BigQuery gestisce la contemporaneità delle istruzioni DML che aggiungono, modificano o eliminano righe in una tabella.
INSERISCI contemporaneità DML
In un 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. Altre
istruzioni INSERT
vengono aggiunte a una coda PENDING
. Puoi mettere in coda fino a 100 istruzioni INSERT
in una tabella in qualsiasi momento. Una volta completata un'istruzione INSERT
, l'istruzione INSERT
successiva viene rimossa dalla coda ed eseguita.
UPDATE, DELETE, MERGE DML contemporaneità
Le istruzioni DML UPDATE
, DELETE
e MERGE
sono chiamate istruzioni DML mutanti. Se invii una o più istruzioni DML mutanti su una tabella mentre altri job DML mutanti sono ancora in esecuzione (o in attesa), BigQuery ne esegue fino a due contemporaneamente, dopodiché fino a un massimo di 20 vengono messi in coda come PENDING
. Al termine di un job precedentemente in esecuzione, il job
in attesa successivo viene rimosso dalla coda ed eseguito. Le istruzioni DML mutanti in coda condividono
una coda per tabella con lunghezza massima di 20. Le istruzioni aggiuntive oltre
la lunghezza massima della coda per ogni tabella non riescono e vengono visualizzati 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 interattivi con priorità che sono in coda per più di 6 ore non riescono con il seguente messaggio di errore:
DML statement has been queued for too long
Conflitti delle istruzione DML
La modifica delle istruzioni DML eseguite contemporaneamente su una tabella causa conflitti di istruzione DML quando le istruzioni tentano di modificare la stessa partizione. Le istruzioni hanno esito positivo purché non modifichino la stessa partizione. BigQuery tenta di eseguire fino a tre volte le istruzioni non riuscite.
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 aggiorna le righe esistenti. Può includere istruzioniMERGE
con clausoleUPDATE
oDELETE
, purché queste non vengano richiamate durante l'esecuzione della query.
Prezzi
Per informazioni sui prezzi dei DML, consulta la sezione Prezzi di Data Manipulation Language nella pagina dei prezzi.
best practice
Per un rendimento ottimale, Google consiglia i seguenti pattern:
Evita di inviare un numero elevato di aggiornamenti o posizionamenti di singole righe. Se possibile, raggruppa invece le operazioni DML. Per saperne di più, consulta Istruzioni DML che aggiornano o inseriscono righe singole.
Se generalmente aggiornamenti o eliminazioni vengono eseguiti 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 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 frazione delle partizioni.
Se aggiorni spesso le righe in cui una o più colonne rientrano in un intervallo ristretto di valori, valuta l'utilizzo di tabelle in cluster. Il clustering assicura 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 delle colonne:UPDATE mydataset.mytable SET string_col = 'some string' WHERE id BETWEEN 54 AND 75;
Ecco un esempio simile che applica un filtro 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, puoi utilizzare le query federate di Cloud SQL, che consentono a BigQuery di eseguire query sui dati che risiedono in Cloud SQL.
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 la sintassi DML.
- Per informazioni sull'utilizzo delle istruzioni DML nelle query pianificate, consulta Pianificazione delle query.