Trasformare i dati con data manipulation language (DML)

GoogleSQL è il nuovo nome di SQL standard di Google. Nuovo nome, stesso ottimo dialetto SQL.

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

Puoi eseguire istruzioni DML 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. Per ulteriori informazioni su come calcolare il numero di byte elaborati da un istruzione DML, consulta Calcolo delle dimensioni delle query on demand.

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 delle scritture delle tabelle. Potresti raggiungere un limite di frequenza se invii contemporaneamente un numero elevato di job per una tabella. Queste tariffe non limitano il numero totale di istruzioni DML che possono essere eseguite. Se visualizzi un messaggio di errore che indica che hai superato un limite di frequenza, riprova l'operazione utilizzando il backoff esponenziale tra un nuovo tentativo e l'altro.

Concorrenza DML INSERT

Durante qualsiasi 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. 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.

Contemporaneità DML per UPDATE, DELETE e MERGE

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. Le istruzioni aggiuntive oltre la lunghezza massima della coda per ogni tabella non vanno a buon fine con 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 in coda per più di 6 ore non riescono a completare l'operazione con il seguente messaggio di errore:

DML statement has been queued for too long

Conflitti di istruzione DML

Le istruzioni DML che vengono eseguite contemporaneamente in una tabella causano conflitti quando tentano di modificare la stessa partizione. Le istruzioni vanno a buon fine 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 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. Possono essere incluse istruzioni MERGE con clausole UPDATE o DELETE, a condizione che queste clausole non vengano richiamate al momento dell'esecuzione della query.

DML granulare

Anteprima

Questa funzionalità è soggetta ai "Termini delle Offerte pre-GA" nella sezione Termini generali del servizio dei Termini specifici dei servizi. L'utente può trattare i dati personali relativi a questa funzionalità come descritto nell'Addendum per il trattamento dei dati Cloud, in conformità agli obblighi e alle limitazioni descritti nel contratto in base al quale accede a Google Cloud. Le funzionalità pre-GA sono disponibili "così come sono" e potrebbero avere un supporto limitato. Per saperne di più, consulta le descrizioni della fase di lancio.

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

La DML granulare è un miglioramento delle prestazioni progettato per ottimizzare l'esecuzione delle istruzioni UPDATE, DELETE e MERGE (note anche come istruzioni DML mutazionali). Se la DML granulare non è abilitata, le mutazioni vengono eseguite a livello di gruppo di file, il che può comportare riscritture dei dati inefficienti. La DML granulare introduce un approccio più granulare che ha lo scopo di ridurre la quantità di dati che devono essere riscritti e di ridurre il consumo complessivo degli slot.

Per esprimere il tuo interesse a registrare un progetto nell'anteprima della DML granulare, compila il modulo di registrazione alla DML granulare di BigQuery. I progetti vengono registrati in modo selettivo in base a una valutazione dei tuoi workload.

Attivare la DML granulare

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

Dopo aver impostato l'opzione enable_fine_grained_mutations su TRUE, le istruzioni DML con mutazioni vengono eseguite con le funzionalità DML granulari abilitate e utilizzano la sintassi delle istruzioni DML esistente.

Per disattivare la DML granulare in una tabella, imposta enable_fine_grained_mutations su FALSE utilizzando l'istruzione DDL ALTER TABLE.

Prezzi

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

I progetti configurati per l'utilizzo delle prenotazioni utilizzano gli slot per elaborare istruzioni DML granulari, inclusa l'eventuale elaborazione in background dei metadati delle tabelle o delle mutazioni.

Considerazioni sui dati eliminati

Le operazioni DML granulari elaborano i dati eliminati in modalità offline.

I progetti che eseguono operazioni DML granulari senza un processo di BACKGROUND assegnazione eliminano i dati utilizzando i prezzi on demand. In questo caso, l'elaborazione dei dati eliminati viene eseguita regolarmente utilizzando le risorse BigQuery interne.

I progetti che eseguono operazioni DML granulari con un'assegnazione BACKGROUND elaborano i dati eliminati 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 la DML granulare abilitata. Utilizza invece l'API Storage Read per leggere i record delle tabelle utilizzando un'API.
• Non puoi creare uno snapshot della tabella o una copia della tabella di una tabella con la DML granulare abilitata.
• Non puoi attivare la DML granulare su una tabella in un set di dati replicato e non puoi replicare un set di dati contenente una tabella con la DML granulare abilitata.
• Le istruzioni DML eseguite in una transazione con più istruzioni non sono ottimizzate con la DML granulare.

Best practice

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

• Evita di inviare un numero elevato di aggiornamenti o inserzioni di singole righe. Raggruppa invece le operazioni DML, se possibile. Per ulteriori informazioni, consulta Comandi 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. La partizione 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 grande parte delle partizioni.

• Se aggiorni spesso righe in cui una o più colonne rientrano in un intervallo ristretto di valori, ti consigliamo di utilizzare le tabelle raggruppate. Il clustering garantisce 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 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 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, 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.

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 ulteriori informazioni, consulta Utilizzare data manipulation language (DML) con i dati sottoposti a streaming di recente.

• 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 nella clausola FROM di una query UPDATE, ma non può essere utilizzata come destinazione dell'operazione UPDATE.

Passaggi successivi

• Per informazioni e esempi sulla sintassi DML, consulta Sintassi DML.
• Per informazioni sull'utilizzo delle istruzioni DML nelle query pianificate, consulta Pianificare le query.