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 das 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 com a 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 declaração 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

As instruções DML de mutação que são executadas em simultâneo numa tabela causam 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 declarações UPDATE, DELETE e MERGE (também conhecidas como declarações DML mutantes). Sem o DML detalhado ativado, as mutações são realizadas ao nível do grupo de ficheiros, o que pode levar a reescritas de dados ineficientes. A DML detalhada introduz uma abordagem mais detalhada que visa reduzir a quantidade de dados que têm de ser reescritos e reduzir o consumo geral de espaços.

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 a partir de uma tabela existente, use a declaração ALTER TABLE.

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

Quando desativa o DML detalhado, o processamento completo de todos os dados eliminados pode demorar algum tempo. 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 de armazenamento do BigQuery adicionais para armazenar os metadados de mutação adicionais associados a operações DML detalhadas. O custo real depende da quantidade de dados que são modificados, mas, na maioria das situações, espera-se que seja insignificante em comparação com o tamanho da própria tabela.

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 processa uma parte dos dados eliminados e o sistema também transfere o processamento restante para segundo plano. Estas tarefas de tratamento de dados eliminadas incorrem em custos de computação do BigQuery adicionais.

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

As tarefas de tratamento de dados eliminados detalhados offline são consideradas tarefas em segundo plano e usam o BACKGROUND tipo de atribuição, em vez do QUERY tipo de atribuição. Os projetos que executam operações DML detalhadas sem um processo de atribuição BACKGROUND eliminaram dados através dos preços a pedido.

Para projetos configurados para usar os preços de computação a pedido, as declarações de DML detalhadas não reduzem os bytes analisados.

Para encontrar os trabalhos de processamento de dados de DML detalhados offline eliminados: 

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

Se a tabela receber um grande número de tarefas de DML, o tratamento dos dados eliminados pode ser processado inteiramente por DMLs, eliminando a necessidade de tratamento em segundo plano.

Considerações sobre 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 de 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.

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 agrupamento 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);

    Pondere agrupar pela coluna id nestes casos.

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

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?