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 a API BigQuery Storage Write (REST) não podem ser modificadas com a linguagem de manipulação de dados (DML), como as instruções 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 (gRPC) 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 Instrução INSERT.

Instrução DELETE

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

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 a 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 as instruções UPDATE, consulte Instrução UPDATE.

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 para outra. Para mais informações e exemplos sobre a instrução MERGE, consulte Instrução MERGE.

Jobs simultâneos

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

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 DML INSERT com mais frequência, considere fazer streaming de dados para sua tabela usando a API Storage Write (gRPC).

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.

  • A fila automática de DML se aplica apenas a instruções DML independentes que são executadas fora de uma transação de várias instruções. As instruções DML de mutação em uma transação de várias instruções adquirem um bloqueio no nível da tabela, em vez de bloquear partições individuais. As transações simultâneas de várias instruções que modificam a mesma tabela não são colocadas em fila e falham imediatamente, mesmo que segmentem partições diferentes. Se um procedimento armazenado ou uma consulta de várias instruções executar apenas uma única instrução DML de mutação, como uma instrução MERGE, não envolva a instrução em instruções BEGIN TRANSACTION e COMMIT TRANSACTION. Para mais informações, consulte Simultaneidade de transações.

DML granular

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

Considerações sobre desempenho

Sem a DML refinada ativada, as mutações de 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 adicional de slots e tempos de execução mais longos.

A DML refinada é uma melhoria de desempenho projetada para otimizar essas instruções DML de mutação, 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 de DML de mutação.

Há algumas considerações de desempenho que você precisa saber ao usar a DML refinada:

  • As operações de DML refinadas processam dados excluídos em uma abordagem híbrida que distribui os custos de regravação em várias mutações de tabela. Cada operação DML pode processar uma parte dos dados excluídos e, em seguida, transferir 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.
  • Tabelas com operações DML de mutação frequentes podem ter maior latência para consultas SELECT e jobs DML subsequentes. Para avaliar o impacto da ativação desse recurso, compare o desempenho de uma sequência realista de operações de DML e leituras subsequentes.
  • Ativar a DML detalhada não reduz a quantidade de bytes verificados da instrução DML de mutação.

Ativar DML refinada

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

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

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

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

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

Para alterar todas as tabelas de um conjunto de dados com DML detalhada, use a instrução 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;

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

Para determinar se uma tabela foi ativada com DML refinada, consulte a visualização INFORMATION_SCHEMA.TABLES. 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 detalhada ativada.

Desativar DML granular

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

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 de DML refinadas podem persistir até que isso ocorra.

Preços

Ativar a DML detalhada para uma tabela pode gerar custos adicionais. Esses custos incluem:

  • Custos de armazenamento do BigQuery para armazenar os metadados de mutação extra associados a operações de DML detalhadas. O custo real do armazenamento 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 em si.
  • Custos de computação do BigQuery para processar dados excluídos usando jobs de coleta de lixo descarregados e consultas subsequentes do SELECT que processam metadados de exclusão adicionais 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 é útil principalmente, e muitas vezes recomendada, para tabelas muito grandes com operações de DML de mutação refinadas frequentes, que de outra forma teriam altos custos on demand devido ao grande número de bytes processados ao realizar cada job de processamento de dados excluídos descarregado.

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

Operação Preços sob demanda Preços baseados em capacidade
Instruções DML de mutação Use o dimensionamento de DML padrão para determinar os cálculos de bytes verificados sob demanda.

Ativar a DML detalhada não reduz a quantidade de bytes verificados da própria instrução DML.

 Consumir slots atribuídos com um tipo QUERY no momento da execução da instrução.
Jobs de processamento de dados excluídos transferidos Use o dimensionamento de DML padrão para determinar os cálculos de bytes verificados sob demanda quando os jobs de processamento de dados excluídos forem executados. Consumir slots atribuídos com um tipo BACKGROUND quando jobs de processamento 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 regravação e otimizar o desempenho em várias instruções DML de mutação emitidas em uma tabela.

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

Os 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 transferidas poderá levar mais tempo do que o previsto.

Os projetos que realizam operações de 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 de acordo com as taxas de preços sob demanda. Para saber mais informações, consulte Preços.

O momento das tarefas de coleta de lixo transferidas é determinado pela frequência da atividade de DML na tabela e pela disponibilidade de recursos, se uma atribuição BACKGROUND for usada:

  • Para tabelas com operações DML de mutação contínua, cada DML processa uma parte da carga de trabalho de coleta de lixo para garantir um desempenho 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 de DML subsequente ocorrer em uma tabela, a coleta de lixo descarregada será acionada automaticamente quando os dados excluídos atingirem cinco dias de idade.
  • Em casos raros, pode levar mais tempo para processar totalmente os dados excluídos.

Para identificar jobs de tratamento de dados excluídos por DML refinado e transferidos, consulte a visualização INFORMATION_SCHEMA.JOBS: 

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

Limitações

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

  • Para tabelas grandes com partições modificadas com frequência que excedem 2 TB, a DML refinada não é recomendada. Essas tabelas podem sofrer maior pressão de memória para consultas subsequentes, o que pode levar a mais latência de leitura ou erros de consulta.
  • Apenas uma instrução DML de mutação pode ser executada por vez em uma tabela que tenha a DML refinada ativada. Os jobs subsequentes são colocados na fila como PENDING. Para mais informações sobre como modificar o comportamento de simultaneidade de DML, 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 mutável, como DELETE, UPDATE, MERGE ou TRUNCATE.
  • Não é possível usar o método tabledata.list para ler o conteúdo de uma tabela com DML refinada ativada. Em vez disso, consulte a tabela com uma instrução SELECT para ler os registros dela.
  • Não é possível visualizar uma tabela com DML detalhada usando o console do BigQuery.
  • Não é possível copiar uma tabela com DML refinada ativada depois de executar uma instrução UPDATE, DELETE ou MERGE.
  • Não é possível criar um snapshot de tabela ou um clone de tabela de uma tabela com DML refinado ativado depois de executar uma instrução UPDATE, DELETE ou MERGE.
  • Não é possível renomear uma tabela com DML refinada ativada depois de executar uma instrução UPDATE, DELETE ou MERGE.
  • A replicação entre regiões não é compatível com tabelas que têm a DML refinada ativada. Embora o sistema bloqueie a criação de conjuntos de dados replicados que já contêm essas tabelas, ele não bloqueia a ativação de DML refinada em tabelas dentro de conjuntos de dados replicados existentes. No entanto, isso pode resultar em resultados de consulta incorretos na secundária para a tabela com DML refinada ativada.
  • As instruções DML executadas em uma transação de várias instruções não são otimizadas com DML refinada.
  • Não é possível ativar a DML detalhada em tabelas temporárias criadas com a instrução CREATE TEMP TABLE.
  • Os metadados refletidos nas visualizações INFORMATION_SCHEMA.TABLE_STORAGE e INFORMATION_SCHEMA.PARTITIONS podem incluir temporariamente dados excluídos recentemente usando 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 no 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