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

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

Pode executar declarações DML tal como faria com uma declaração SELECT, com as seguintes condições:

  • Tem de usar o GoogleSQL. Para ativar o GoogleSQL, consulte o artigo Mudar de dialeto de SQL.
  • Não pode especificar uma tabela de destino para a consulta.

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

Limitações

  • Cada declaração DML inicia uma transação implícita, o que significa que as alterações feitas pela declaração são automaticamente confirmadas no final de cada declaração DML bem-sucedida.

  • Não é possível modificar as linhas escritas recentemente através do método de streaming tabledata.insertall com a linguagem de manipulação de dados (DML), como as declarações UPDATE, DELETE, MERGE ou TRUNCATE. As gravações recentes são as que ocorreram nos últimos 30 minutos. Todas as outras linhas na tabela permanecem modificáveis através de declarações UPDATE, DELETE, MERGE ou TRUNCATE. Os dados transmitidos podem demorar até 90 minutos a ficar disponíveis para operações de cópia.

    Em alternativa, as linhas escritas recentemente através da API Storage Write podem ser modificadas através das declarações UPDATE, DELETE ou MERGE. Para mais informações, consulte o artigo Use a linguagem de manipulação de dados (DML) com dados transmitidos recentemente.

  • As subconsultas correlacionadas numa cláusula when_clause, search_condition, merge_update_clause ou merge_insert_clause não são suportadas para declarações MERGE.

  • As consultas que contêm declarações DML não podem usar uma tabela com carateres universais como destino da consulta. Por exemplo, pode usar uma tabela com carateres universais na cláusula FROM de uma consulta UPDATE, mas não pode usar uma tabela com carateres universais como destino da operação UPDATE.

Instruções DML

As secções seguintes descrevem os diferentes tipos de declarações DML e como as pode usar.

INSERT declaração

Use a declaração INSERT para adicionar novas linhas a uma tabela existente. O exemplo seguinte 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 as declarações INSERT, consulte a declaração INSERT.

DELETE declaração

Use a declaração DELETE para eliminar linhas numa tabela. O exemplo seguinte elimina 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 eliminar todas as linhas de uma tabela, use a declaração TRUNCATE TABLE. Para mais informações sobre os extratos DELETE, consulte o artigo Extrato DELETE.

TRUNCATE declaração

Use a declaração TRUNCATE para remover todas as linhas de uma tabela, mas deixando os metadados da tabela intactos, incluindo o esquema, a descrição e as etiquetas da tabela. O exemplo seguinte remove todas as linhas da tabela dataset.Inventory.

TRUNCATE dataset.Inventory

Para eliminar linhas específicas numa tabela. Em alternativa, use a declaração DELETE. Para mais informações sobre a declaração TRUNCATE, consulte a declaração TRUNCATE.

UPDATE declaração

Use a declaração UPDATE para atualizar as linhas existentes numa tabela. A declaração UPDATE também tem de incluir a palavra-chave WHERE para especificar uma condição. O exemplo seguinte reduz o valor de 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 declarações UPDATE também podem incluir cláusulas FROM para incluir tabelas unidas. Para mais informações sobre os extratos UPDATE, consulte o extrato UPDATE.

MERGE declaração

A declaração MERGE combina as operações INSERT, UPDATE e DELETE numa única declaração e executa as operações de forma atómica para unir dados de uma tabela a outra. Para mais informações e exemplos acerca da declaração MERGE, consulte a declaração MERGE.

Tarefas simultâneas

O BigQuery gere a simultaneidade das declarações DML que adicionam, modificam ou eliminam linhas numa tabela.

Concorrência de DML de INSERÇÃO

Durante qualquer período de 24 horas, as primeiras 1500 declarações INSERT são executadas imediatamente após o envio. Depois de atingir este limite, a concorrência de declarações INSERT que escrevem numa tabela está limitada a 10. As declarações INSERT adicionais são adicionadas a uma fila PENDING. É possível colocar até 100 declarações INSERT em fila contra uma tabela em qualquer altura. Quando uma declaração INSERT é concluída, a declaração INSERT seguinte é removida da fila e executada.

Se tiver de executar declarações de DML INSERT com mais frequência, considere fazer stream de dados para a sua tabela através da API Storage Write.

Concorrência de DML UPDATE, DELETE e MERGE

As instruções DML UPDATE, DELETE e MERGE são denominadas instruções DML de mutação. Se enviar uma ou mais declarações DML de mutação numa tabela enquanto outras tarefas DML de mutação na mesma ainda estão em execução (ou pendentes), o BigQuery executa até 2 delas em simultâneo. Depois disso, até 20 são colocadas em fila como PENDING. Quando uma tarefa em execução anterior termina, a próxima tarefa pendente é removida da fila e executada. As declarações DML de mutação em fila partilham uma fila por tabela com um comprimento máximo de 20. As declarações adicionais que excedam o comprimento 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.

As tarefas de DML de prioridade interativa que estão em fila durante mais de 7 horas falham com a seguinte mensagem de erro:

DML statement has been queued for too long

Conflitos de instruções DML

A alteração de instruções DML executadas em simultâneo numa tabela provoca conflitos de instruções DML quando as instruções tentam alterar a mesma partição. As declarações são bem-sucedidas desde que não modifiquem a mesma partição. O BigQuery tenta executar novamente as declarações com falhas até três vezes.

  • Uma INSERTinstrução DML que insere linhas numa tabela não entra em conflito com nenhuma outra instrução DML em execução em simultâneo.

  • Uma instrução DML MERGE não entra em conflito com outras instruções DML em execução em simultâneo, desde que a instrução apenas insira linhas e não elimine nem atualize linhas existentes. Isto pode incluir declarações MERGE com cláusulas UPDATE ou DELETE, desde que essas cláusulas não sejam invocadas quando a consulta é executada.

DML detalhado

O DML detalhado é um melhoramento do desempenho concebido para otimizar a execução das instruções UPDATE, DELETE e MERGE (também conhecidas como instruções DML de alteração).

Considerações sobre o desempenho

Sem o DML detalhado ativado, as mutações DML são realizadas ao nível do grupo de ficheiros, o que pode levar a reescritas de dados ineficientes, especialmente para mutações esparsas. Isto pode levar a um consumo adicional de espaços e a tempos de execução mais longos.

O DML detalhado é um melhoramento do desempenho concebido para otimizar estas declarações de DML de mutação através da introdução de uma abordagem mais detalhada que visa reduzir a quantidade de dados que precisam de ser reescritos ao nível do grupo de ficheiros. Esta abordagem pode reduzir significativamente o tempo de processamento, E/S e intervalo consumido para alterar tarefas DML.

Existem algumas considerações de desempenho a ter em atenção quando usa DML detalhado:

  • As operações DML detalhadas processam dados eliminados numa abordagem híbrida que distribui os custos de reescrita por várias mutações de tabelas. Cada operação DML pode processar uma parte dos dados eliminados e, em seguida, transferir o processamento dos dados eliminados restantes para um processo de recolha de lixo em segundo plano. Para mais informações, consulte as considerações sobre os dados eliminados.
  • As tabelas com operações DML de mutação frequentes podem sofrer um aumento da latência para consultas SELECT e tarefas DML subsequentes. Para avaliar o impacto da ativação desta funcionalidade, compare o desempenho de uma sequência realista de operações DML e leituras subsequentes.
  • Para tabelas grandes com partições alteradas frequentemente que excedam 2 TB, não se recomenda o DML detalhado. Estas tabelas podem sofrer uma pressão de memória adicional para consultas subsequentes, o que pode levar a uma latência de leitura adicional ou a erros de consulta.
  • A ativação da DML detalhada não reduz a quantidade de bytes analisados da própria declaração de DML de mutação.

Ative o DML detalhado

Para ativar o DML detalhado, defina a opção de tabela enable_fine_grained_mutations como TRUE quando executar uma declaração DDL CREATE TABLE ou ALTER TABLE.

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

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

Para alterar uma tabela existente com DML detalhado, use a declaração ALTER TABLE:

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

Para alterar todas as tabelas existentes num conjunto de dados com DML detalhado, use a declaraçã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 de a opção enable_fine_grained_mutations ser definida como TRUE, as instruções DML de mutação são executadas com capacidades DML detalhadas ativadas e usam a sintaxe de instruções DML existente.

Para determinar se uma tabela foi ativada com DML detalhado, consulte a vista INFORMATION_SCHEMA.TABLES. O exemplo seguinte verifica que tabelas num conjunto de dados foram ativadas com esta funcionalidade:

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 no qual quer verificar se alguma tabela tem o DML detalhado ativado.

Desative o DML detalhado

Para desativar o DML detalhado numa tabela existente, use a declaração ALTER TABLE.

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

Quando desativa a DML detalhada, pode demorar algum tempo até que todos os dados eliminados sejam totalmente processados. Consulte as considerações sobre os dados eliminados. Como resultado, as limitações de DML detalhadas podem persistir até que isto ocorra.

Preços

A ativação da DML detalhada para uma tabela pode incorrer em custos adicionais. Estes custos incluem o seguinte:

  • Custos de armazenamento do BigQuery para armazenar os metadados de mutação adicionais associados a operações de DML detalhadas. O custo de armazenamento real depende da quantidade de dados que é modificada, 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 eliminados através de tarefas de recolha de lixo descarregadas e consultas SELECT subsequentes que processam metadados de eliminação adicionais que ainda não foram recolhidos.

Pode usar reservas do BigQuery para atribuir recursos de computação do BigQuery dedicados para processar tarefas de dados eliminados transferidos. As reservas permitem-lhe definir um limite para o custo de realização destas operações. Esta abordagem é particularmente útil e, muitas vezes, recomendada para tabelas muito grandes com operações de DML de mutação detalhadas frequentes, que, de outra forma, teriam custos a pedido elevados devido ao grande número de bytes processados quando se executa cada tarefa de processamento de dados eliminados carregados.

As tarefas de tratamento de dados eliminados transferidas de DML detalhados são consideradas tarefas em segundo plano e requerem a utilização do tipo de atribuição de reserva BACKGROUND, em vez do tipo de atribuição de reserva QUERY. Os projetos que executam operações DML detalhadas sem uma atribuição de BACKGROUND usam ospreços a pedidopara processar as tarefas de dados eliminados carregados.

Operação Preços a pedido Preços baseados na capacidade
Alterar instruções DML Use a dimensionamento de DML para determinar os cálculos de bytes analisados a pedido.

A ativação da DML detalhada não reduz a quantidade de bytes analisados da própria declaração DML.

 Consumir espaços atribuídos com um tipo QUERY no momento da execução do extrato.
Tarefas de processamento de dados eliminados transferidas Use a dimensionamento de DML padrão para determinar os cálculos de bytes analisados a pedido quando são executados trabalhos de processamento de dados eliminados. Consumir as posições atribuídas com um tipo BACKGROUND quando os trabalhos de tratamento de dados são executados.

Considerações sobre os dados eliminados

Projetos que executam operações DML detalhadas com uma atribuição BACKGROUND processam dados eliminados através de slots e estão sujeitos à disponibilidade de recursos da reserva configurada. Se não existirem recursos suficientes disponíveis na reserva configurada, o tratamento dos dados eliminados pode demorar mais tempo do que o previsto.

Os projetos que realizam operações DML detalhadas através da determinação de preços a pedido ou sem uma atribuição BACKGROUND, processam dados eliminados através da determinação de preços a pedido e têm regularmente dados eliminados processados através de recursos internos do BigQuery.

Para identificar tarefas de processamento de dados eliminados de DML detalhados transferidos, consulte a vista 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 detalhado estão sujeitas às seguintes limitações:

Práticas recomendadas

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

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

  • Se as atualizações ou as eliminações ocorrerem geralmente em dados mais antigos ou num intervalo de datas específico, considere particionar as tabelas. A partição garante que as alterações estão limitadas a partições específicas na tabela.

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

  • Se atualizar frequentemente linhas em que uma ou mais colunas se enquadram num intervalo restrito de valores, considere usar tabelas agrupadas. O clustering garante que as alterações se limitam a conjuntos específicos de blocos, o que reduz a quantidade de dados que têm de ser lidos e escritos. Segue-se um exemplo de uma declaração que filtra um intervalo de valores de colunas:UPDATE

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

    Segue-se um exemplo semelhante que filtra uma pequena lista de valores de colunas:

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

    Considere o agrupamento na coluna id nestes casos.

  • Se precisar da funcionalidade OLTP, considere usar as consultas federadas do Cloud SQL, que permitem ao BigQuery consultar dados residentes no Cloud SQL.

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

Para ver as práticas recomendadas para otimizar o desempenho das consultas, consulte o artigo Introdução à otimização do desempenho das consultas.

O que se segue?