Transformar dados com a linguagem de manipulação de dados (DML)

A linguagem de manipulação de dados (DML) do BigQuery permite atualizar, inserir e excluir dados das tabelas do BigQuery.

É possível executar instruções DML como se fossem uma instrução SELECT, com as seguintes condições:

• É necessário usar o GoogleSQL. Para ativar o GoogleSQL, consulte Como alternar dialetos SQL.
• Não é possível especificar uma tabela de destino para a consulta.

Para mais informações sobre como calcular o número de bytes processados por uma instrução DML, consulte Cálculo do tamanho da consulta sob demanda.

Limitações

• Uma instrução DML inicia uma transação implícita. Isso significa que as alterações feitas por ela são confirmadas automaticamente no final de cada instrução bem-sucedida.

• As linhas gravadas recentemente usando o método de streaming tabledata.insertall não podem ser modificadas com a linguagem de manipulação de dados (DML), como UPDATE, DELETE, MERGE ou TRUNCATE. As gravações recentes são aquelas que ocorreram nos últimos 30 minutos. Todas as outras linhas da tabela podem ser alteradas usando instruções UPDATE, DELETE, MERGE ou TRUNCATE. Os dados transmitidos podem levar até 90 minutos para ficarem disponíveis para operações de cópia.

  Como alternativa, as linhas gravadas recentemente com a API Storage Write podem ser modificadas com as instruções UPDATE, DELETE ou MERGE. Para mais informações, consulte Usar linguagem de manipulação de dados (DML) com dados de streaming recentemente.

• Subconsultas correlatas em when_clause, search_condition, merge_update_clause ou merge_insert_clause não são compatíveis com instruções MERGE.

• Consultas que contêm instruções DML não podem usar uma tabela curinga como destino da consulta. Por exemplo, uma tabela curinga pode ser usada na cláusula FROM de uma consulta UPDATE, mas não pode ser usada como destino da operação UPDATE.

Instruções DML

As seções a seguir descrevem os diferentes tipos de instruções DML e como usá-las.

Instrução INSERT

Use a instrução INSERT para adicionar novas linhas a uma tabela. O exemplo a seguir insere novas linhas na tabela dataset.Inventory com valores especificados explicitamente.

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 |
 +-------------------+----------+/

Para mais informações sobre instruções INSERT, consulte INSERT instrução.

Instrução DELETE

Use a instrução DELETE para excluir linhas em uma tabela. O exemplo a seguir exclui todas as linhas da tabela dataset.Inventory que têm o valor quantity como 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 |
 +-------------------+----------+/

Para excluir todas as linhas de uma tabela, use a instrução TRUNCATE TABLE. Para mais informações sobre instruções DELETE, consulte Instrução DELETE.

Instrução TRUNCATE

Use a instrução TRUNCATE para remover todas as linhas de uma tabela, mas deixe os metadados da tabela intactos, incluindo o esquema, a descrição e os rótulos da tabela. O exemplo a seguir remove todas as linhas da tabela dataset.Inventory.

TRUNCATE dataset.Inventory

Para excluir linhas específicas em uma tabela, use a instrução DELETE. Para mais informações sobre a instrução TRUNCATE, consulte Instrução TRUNCATE.

Instrução UPDATE

Use a instrução UPDATE para atualizar linhas existentes em uma tabela. A instrução UPDATE também precisa incluir a palavra-chave WHERE para especificar uma condição. O exemplo a seguir reduz o valor quantity das linhas em 10 para produtos que contêm a string 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 |
 +-------------------+----------+/

As instruções UPDATE também podem incluir cláusulas FROM para incluir tabelas mescladas. Para mais informações sobre instruções UPDATE, consulte UPDATE instrução.

Instrução MERGE

A instrução MERGE combina as operações INSERT, UPDATE e DELETE em uma única instrução e executa as operações de maneira atômica para mesclar dados de uma tabela a outra. Para mais informações e exemplos sobre a MERGE instrução, consulte MERGE instrução.

Jobs simultâneos

O BigQuery gerencia a simultaneidade de instruções DML que adicionam, modificam ou excluem linhas em uma tabela.

Observação: as instruções DML estão sujeitas a limites de taxa, como a taxa máxima de gravações de tabela. Você pode atingir um limite de taxa se enviar um grande número de jobs em uma tabela por vez. Essas taxas não limitam o número total de instruções DML que podem ser executadas. Se você receber uma mensagem de erro dizendo que excedeu um limite de taxa, repita a operação usando a espera exponencial entre as tentativas.

Simultaneidade de DML INSERT

Durante qualquer período de 24 horas, as primeiras 1.500 instruções INSERT são executadas imediatamente após o envio. Depois que esse limite é atingido, a simultaneidade de instruções INSERT que gravam em uma tabela é limitada a 10. Outras instruções INSERT são adicionadas a uma fila PENDING. Até 100 instruções INSERT podem ser colocadas em fila em uma tabela a qualquer momento. Quando uma instrução INSERT é concluída, a próxima instrução INSERT é removida da fila e executada.

Se você precisar executar instruções INSERT DML com mais frequência, considere fazer streaming de dados para sua tabela usando a API Storage Write.

Simultaneidade de DML UPDATE, DELETE, MERGE

As instruções DML UPDATE, DELETE e MERGE são chamadas de instruções DML mutantes. Se você enviar uma ou mais instruções DML mutantes em uma tabela enquanto outros jobs DML mutantes nela ainda estiverem em execução (ou pendentes), o BigQuery executará até dois deles simultaneamente. Depois desses, 20 ficarão na fila como PENDING. Quando um job que estava em execução é concluído, o próximo job pendente é retirado da fila e executado. As instruções DML mutantes compartilham uma fila por tabela com comprimento máximo de 20. Outras instruções além do tamanho máximo da fila para cada tabela falham com a mensagem de erro: Resources exceeded during query execution: Too many DML statements outstanding against table PROJECT_ID:DATASET.TABLE, limit is 20.

Os jobs DML de prioridade interativa que são enfileirados por mais de sete horas falham com a seguinte mensagem de erro:

DML statement has been queued for too long

Conflitos de instrução DML

A mutação de instruções DML executadas simultaneamente em uma tabela causa conflitos de instrução DML quando as instruções tentam fazer mutações na mesma partição. As instruções são bem-sucedidas desde que não modifiquem a mesma partição. O BigQuery tenta executar novamente as instruções com falha até três vezes.

• Uma instrução DML INSERT que insere linhas em uma tabela não entra em conflito com nenhuma outra instrução DML em execução simultânea.

• Uma instrução DML MERGE não entra em conflito com outras instruções DML em execução simultânea, desde que ela insira apenas linhas e não exclua ou atualize nenhuma linha existente. Isso pode incluir instruções MERGE com cláusulas UPDATE ou DELETE, desde que essas cláusulas não sejam invocadas quando a consulta estiver em execução.

DML refinada

Visualização

Esse recurso está sujeito aos "Termos da oferta pré-GA" na seção "Termos gerais de serviço" dos Termos específicos de serviço. Você pode gerenciar dados pessoais para este recurso conforme descrito no Aditivo sobre Tratamento de Dados do Cloud, sujeito às obrigações e restrições descritas no contrato que rege seu acesso ao Google Cloud. Os recursos pré-GA estão disponíveis "no estado em que se encontram" e podem ter suporte limitado. Para mais informações, consulte as descrições da fase de lançamento.

Observação: Para enviar feedback ou solicitar suporte sobre esse recurso, envie um e-mail para [e-mail].

A DML refinada é uma melhoria de performance projetada para otimizar a execução de instruções UPDATE, DELETE e MERGE (também conhecidas como instruções DML mutantes).

Considerações sobre desempenho

Sem a DML refinada ativada, as mutações DML são realizadas no nível do grupo de arquivos, o que pode levar a reescritas de dados ineficientes, especialmente para mutações esparsas. Isso pode levar a um consumo extra de slots e tempos de execução mais longos.

A DML refinada é uma melhoria de performance projetada para otimizar essas instruções DML mutantes, introduzindo uma abordagem mais granular que visa reduzir a quantidade de dados que precisam ser reescritos no nível do grupo de arquivos. Essa abordagem pode reduzir significativamente o tempo de processamento, E/S e slot consumido para jobs DML mutantes.

Há algumas considerações de performance a serem observadas ao usar a DML refinada:

• As operações DML refinadas processam dados excluídos em uma abordagem híbrida que distribui os custos de reescrita em várias mutações de tabela. Cada operação DML pode processar uma parte dos dados excluídos e, em seguida, descarregar o restante do processamento de dados excluídos para um processo de coleta de lixo em segundo plano. Para mais informações, consulte Considerações sobre dados excluídos.
• As tabelas com operações DML mutantes frequentes podem apresentar maior latência para consultas SELECT e jobs DML subsequentes. Para avaliar o impacto da ativação desse recurso, faça um benchmark da performance de uma sequência realista de operações DML e leituras subsequentes.
• A ativação da DML refinada não reduz a quantidade de bytes verificados da própria instrução DML mutante.

Ativar a DML refinada

Para ativar a DML refinada, defina a enable_fine_grained_mutations opção de tabela como TRUE ao executar uma CREATE TABLE ou ALTER TABLE instrução DDL.

Para criar uma nova tabela com DML refinada, use a CREATE TABLE instrução:

CREATE TABLE mydataset.mytable (
  product STRING,
  inventory INT64)
OPTIONS(enable_fine_grained_mutations = TRUE);

Para alterar uma tabela com DML refinada, use a ALTER TABLE instrução:

ALTER TABLE mydataset.mytable
SET OPTIONS(enable_fine_grained_mutations = TRUE);

Para alterar todas as tabelas em um conjunto de dados com DML refinada, use a ALTER TABLE instrução:

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;

Depois que a opção enable_fine_grained_mutations é definida como TRUE, as instruções DML mutantes são executadas com os recursos de DML refinada ativados e usam a sintaxe de instrução DML atual.

Para determinar se uma tabela foi ativada com DML refinada, consulte a INFORMATION_SCHEMA.TABLES visualização. O exemplo a seguir verifica quais tabelas em um conjunto de dados foram ativadas com esse recurso:

SELECT
  table_schema AS datasetId,
  table_name AS tableId,
  is_fine_grained_mutations_enabled
FROM
  DATASET_NAME.INFORMATION_SCHEMA.TABLES;

Substitua DATASET_NAME pelo nome do conjunto de dados em que você quer verificar se alguma tabela tem a DML refinada ativada.

Desativar a DML refinada

Para desativar a DML refinada de uma tabela, use a ALTER TABLE instrução.

ALTER TABLE mydataset.mytable
SET OPTIONS(enable_fine_grained_mutations = FALSE);

Ao desativar a DML refinada, pode levar algum tempo para que todos os dados excluídos sejam totalmente processados. Consulte Considerações sobre dados excluídos. Como resultado, as limitações da DML refinada podem persistir até que isso ocorra.

Preços

A ativação da DML refinada para uma tabela pode gerar custos extras. Esses custos incluem o seguinte:

• Custos de armazenamento do BigQuery para armazenar os metadados de mutação extras associados a operações DML refinadas O custo de armazenamento real depende da quantidade de dados modificados, mas, na maioria das situações, espera-se que seja insignificante em comparação com o tamanho da tabela.
• Custos de computação do BigQuery para processar dados excluídos usando jobs de coleta de lixo descarregados, e consultas SELECT subsequentes que processam metadados de exclusão extras que ainda não foram coletados.

Use as reservas do BigQuery para alocar recursos de computação dedicados do BigQuery para processar jobs de dados excluídos descarregados. As reservas permitem que você defina um limite para o custo de executar essas operações. Essa abordagem é particularmente útil e recomendada para tabelas muito grandes com operações DML mutantes refinadas frequentes, que, de outra forma, teriam custos sob demanda altos devido ao grande número de bytes processados ao executar cada job de processamento de dados excluídos descarregados.

Os jobs de processamento de dados excluídos descarregados da DML refinada são considerados jobs em segundo plano e exigem o uso do BACKGROUND tipo de atribuição de reserva, em vez do QUERY tipo de atribuição de reserva. Projetos que realizam operações DML refinadas sem uma BACKGROUND atribuição usam preços sob demanda para processar os jobs de dados excluídos descarregados.

Operação	Preços sob demanda	Preços baseados em capacidade
Instruções DML mutantes	Use o dimensionamento DML padrão para determinar os cálculos de bytes verificados sob demanda. A ativação da DML refinada não reduz a quantidade de bytes verificados da própria instrução DML.	Consome slots atribuídos com um tipo QUERY no tempo de execução da instrução.
Jobs de processamento de dados excluídos descarregados	Use o dimensionamento DML padrão para determinar os cálculos de bytes verificados sob demanda quando os jobs de processamento de dados excluídos são executados.	Consome slots atribuídos com um tipo BACKGROUND quando os jobs de tratamento de dados excluídos são executados.

Considerações sobre dados excluídos

As operações DML refinadas usam uma abordagem híbrida para gerenciar dados excluídos, combinando o processamento inline com a coleta de lixo descarregada para distribuir os custos de reescrita e otimizar a performance em várias instruções DML mutantes emitidas em uma tabela.

Durante a execução de uma instrução DML mutante, o BigQuery tenta realizar uma parte da coleta de lixo relevante de instruções DML anteriores inline. Todos os dados excluídos não processados inline são descarregados para um processo em segundo plano para limpeza posterior.

Projetos que realizam operações DML refinadas com uma atribuição BACKGROUND processam tarefas de coleta de lixo descarregadas usando slots. O processamento de dados excluídos está sujeito à disponibilidade de recursos da reserva configurada. Se não houver recursos suficientes disponíveis na reserva configurada, o processamento de operações de coleta de lixo descarregadas poderá levar mais tempo do que o previsto.

Projetos que realizam operações DML refinadas usando preços sob demanda ou sem uma atribuição BACKGROUND processam tarefas de coleta de lixo descarregadas usando recursos internos do BigQuery e são cobrados com taxas de preços sob demanda. Para mais informações, consulte Preços.

O tempo das tarefas de coleta de lixo descarregadas é determinado pela frequência da atividade DML na tabela e pela disponibilidade de recursos, se você estiver usando uma atribuição BACKGROUND:

• Para tabelas com operações DML mutantes contínuas, cada DML processa uma parte da carga de trabalho de coleta de lixo para garantir a performance consistente de leitura e gravação. Como resultado, a coleta de lixo é processada regularmente à medida que as DMLs subsequentes são executadas.
• Se nenhuma atividade DML subsequente ocorrer em uma tabela, a coleta de lixo descarregada será acionada automaticamente quando os dados excluídos atingirem 5 dias de idade.
• Em casos raros, pode levar mais tempo para processar totalmente os dados excluídos.

Para identificar jobs de processamento de dados excluídos DML refinados descarregados, consulte a INFORMATION_SCHEMA.JOBS visualização:

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

Limitações

As tabelas ativadas com DML refinada estão sujeitas às seguintes limitações:

• Para tabelas grandes com partições mutantes frequentes que excedem 2 TB, a DML refinada não é recomendada. Essas tabelas podem apresentar maior pressão de memória para consultas subsequentes, o que pode levar a maior latência de leitura ou erros de consulta.
• Apenas uma instrução DML mutante pode ser executada por vez em uma tabela que tem a DML refinada ativada. Os jobs subsequentes são enfileirados como PENDING. Para mais informações sobre o comportamento de simultaneidade DML mutante, consulte Simultaneidade de DML UPDATE, DELETE, MERGE.
• Uma tabela ativada com DML refinada não pode ter partições excluídas individualmente ou substituídas. Para excluir ou substituir dados em uma partição, use uma instrução DML mutante, como DELETE, UPDATE, MERGE ou TRUNCATE.
• Não é possível usar o tabledata.list método para ler conteúdo de uma tabela com a DML refinada ativada. Em vez disso, consulte a tabela com uma instrução SELECT para ler os registros da tabela.
• Não é possível visualizar uma tabela ativada com DML refinada usando o console do BigQuery.
• Não é possível copiar uma tabela com a DML refinada ativada após executar uma instrução UPDATE, DELETE ou MERGE.
• Não é possível criar um snapshot de tabela ou clone de tabela de uma tabela com DML refinada ativada após executar uma instrução UPDATE, DELETE ou MERGE.
• Não é possível ativar a DML refinada em uma tabela em um conjunto de dados replicado, e não é possível replicar um conjunto de dados que contém uma tabela com a DML refinada ativada.
• As instruções DML executadas em uma transação de várias instruções não são otimizadas com a DML refinada.
• Não é possível ativar a DML refinada em tabelas temporárias criadas com a CREATE TEMP TABLE instrução.
• Os metadados refletidos nas INFORMATION_SCHEMA.TABLE_STORAGE visualizações e INFORMATION_SCHEMA.PARTITIONS visualizações podem incluir temporariamente dados excluídos recentemente usando a DML refinada até que os jobs de coleta de lixo em segundo plano sejam concluídos.

Práticas recomendadas

Para ter o melhor desempenho, o Google recomenda os seguintes padrões:

• Evite enviar um grande número de inserções ou atualizações de linhas individuais. Em vez disso, agrupe as operações DML quando possível. Para mais informações, consulte as instruções DML que atualizam ou inserem linhas individuais.

• Se atualizações ou exclusões geralmente ocorrerem em dados mais antigos ou em um determinado intervalo de datas, particione suas tabelas. O particionamento garante que as alterações sejam limitadas a partições específicas dentro da tabela.

• Evite particionar tabelas se a quantidade de dados em cada partição for pequena, e cada atualização modificar uma grande fração das partições.

• Se você costuma atualizar linhas em que uma ou mais colunas estão em um intervalo de valores restrito, use tabelas em cluster. Isso garante que as alterações sejam limitadas a conjuntos específicos de blocos, reduzindo a quantidade de dados que precisam ser lidos e gravados. Veja a seguir um exemplo de instrução UPDATE que filtra um intervalo de valores de coluna:

  UPDATE mydataset.mytable
  SET string_col = 'some string'
  WHERE id BETWEEN 54 AND 75;

  Veja um exemplo semelhante que filtra uma pequena lista de valores de coluna:

  UPDATE mydataset.mytable
  SET string_col = 'some string'
  WHERE id IN (54, 57, 60);

  Crie um cluster na coluna id nesses casos.

• Se você precisar da funcionalidade OLTP, use consultas federadas do Cloud SQL, que permitem ao BigQuery consultar dados armazenados em Cloud SQL.

• Para resolver e evitar o erro de cota Too many DML statements outstanding against table, siga as orientações para esse erro na página de solução de problemas do BigQuery.

Para ver as práticas recomendadas para otimizar o desempenho da consulta, acesse Introdução à otimização do desempenho da consulta.

A seguir

• Para informações e amostras de sintaxe do DML, consulte Sintaxe da DML.
• Saiba mais sobre como atualizar dados tabela particionada usando DML.
• Para mais informações sobre como usar instruções DML em consultas programadas, acesse Como programar consultas.