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 a instrução INSERT.

Instrução DELETE

Use a instrução DELETE para excluir linhas de uma tabela. O exemplo a seguir exclui todas as linhas da 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 deixando os metadados dela intactos, incluindo esquema, descrição e rótulos. 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 em 10 o valor quantity das linhas 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 a instrução UPDATE.

Instrução MERGE

A instrução MERGE combina as operações INSERT, UPDATE e DELETE em uma única instrução e realiza 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 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

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 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. Ela introduz 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 mutantes.

Há algumas considerações de performance a serem observadas ao usar 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 de 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 mutantes frequentes podem ter aumento na latência para consultas SELECT e jobs DML subsequentes. Para avaliar o impacto de ativar esse recurso, faça um comparativo de mercado do desempenho de uma sequência realista de operações de DML e leituras subsequentes.
  • Para tabelas grandes com partições mutadas com frequência que excedem 2 TB, não é recomendável usar DML refinado. Essas tabelas podem sofrer aumento na pressão da memória para consultas subsequentes, o que pode levar a mais latência de leitura ou erros de consulta.
  • Ativar a DML refinada não reduz a quantidade de bytes verificados da própria instrução DML de mutação.

Ativar DML refinado

Para ativar o DML refinado, 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 atual com DML refinada, use a instrução ALTER TABLE:

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 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 mutantes são executadas com recursos DML refinados ativados e usam a sintaxe de instrução DML existente.

Para determinar se uma tabela foi ativada com DML refinado, 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 será verificado se alguma tabela tem DML refinado ativado.

Desativar DML refinado

Para desativar a DML refinada de uma tabela atual, 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 aconteça.

Preços

Ativar a 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 de DML refinadas. 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 própria 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 adicionais que ainda não foram coletados como lixo.

É possível usar as reservas do BigQuery para alocar recursos de computação dedicados do BigQuery e 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 geralmente recomendada para tabelas muito grandes com operações frequentes de DML mutante refinado, que de outra forma teriam altos custos sob demanda 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 descarregados de DML refinados 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 de DML refinadas sem uma atribuição de BACKGROUND 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 de DML padrão para determinar os cálculos de bytes verificados sob demanda.

Ativar a DML refinada 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 descarregados Use o dimensionamento de DML padrão para determinar os cálculos de bytes sob demanda verificados quando os jobs de processamento de dados excluídos são 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

Projetos que realizam operações DML refinadas com um processo de atribuição de BACKGROUND excluem dados usando slots e estão sujeitos à disponibilidade de recursos da reserva configurada. Se não houver recursos suficientes disponíveis na reserva configurada, o processamento de dados excluídos poderá levar mais tempo do que o previsto.

Os projetos que realizam operações DML refinadas usando preços sob demanda ou sem uma atribuição de BACKGROUND processam dados excluídos usando os preços sob demanda e têm dados excluídos processados regularmente usando recursos internos do BigQuery.

Para identificar jobs de processamento de dados excluídos de DML refinado descarregados, 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 ativadas com DML refinado estão sujeitas às seguintes limitações:

  • Não é possível usar o método tabledata.list para ler 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 ativada com DML refinado usando o console do BigQuery.
  • Não é possível copiar uma tabela com o DML refinado ativado 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 após a execução de uma instrução UPDATE, DELETE ou MERGE.
  • Não é possível ativar a DML refinada em uma tabela em um conjunto de dados replicado, nem replicar um conjunto de dados que contenha 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 DML refinado.
  • Não é possível ativar a DML refinada em tabelas temporárias criadas com a instrução CREATE TEMP TABLE.

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