Esta página foi traduzida pela API Cloud Translation.

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 ver uma lista de instruções DML do BigQuery e exemplos de como usá-las, consulte Instruções da linguagem de manipulação de dados no GoogleSQL. 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.

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:TABLE, limit is 20.

Os jobs DML de prioridade interativa que são enfileirados por mais de seis 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 detalhada

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 mutáveis). Sem a DML detalhada ativada, as mutações são realizadas no nível do grupo de arquivos, o que pode levar a reescritas de dados ineficazes. A DML detalhada apresenta uma abordagem mais granular que visa reduzir a quantidade de dados que precisam ser reescritos e reduzir o consumo geral de slots.

Para demonstrar interesse em inscrever um projeto na prévia do DML de granularidade fina, preencha o formulário de inscrição do DML de granularidade fina do BigQuery. Os projetos são inscritos de forma seletiva com base em uma avaliação das cargas de trabalho.

Ativar a DML refinada

Para ativar a DML detalhada, 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 nova tabela com DML de granularidade fina, use a instrução CREATE TABLE:

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

Para alterar uma tabela existente com DML de granularidade fina, use a instrução ALTER TABLE:

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

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

Para desativar a DML de granularidade fina em uma tabela, defina enable_fine_grained_mutations como FALSE usando a instrução DDL ALTER TABLE.

Preços

Ativar a DML detalhada para uma tabela pode gerar outros custos de armazenamento do BigQuery para armazenar os metadados de mutação extras associados a operações de DML detalhadas. O custo real depende da quantidade de dados que é modificada, mas, na maioria das situações, ele é insignificante em comparação com o tamanho da tabela.

Os projetos configurados para usar reservations usam slots para processar instruções DML detalhadas, incluindo qualquer processamento em segundo plano de metadados de tabela ou mutação.

Considerações sobre dados excluídos

As operações DML detalhadas processam dados excluídos de forma off-line.

Os projetos que executam operações DML detalhadas sem um processo de atribuição BACKGROUND excluem dados usando preços sob demanda. Nesse caso, o processamento de dados excluídos é realizado regularmente usando recursos internos do BigQuery.

Os projetos que executam operações DML detalhadas com uma atribuição BACKGROUND processam dados excluídos 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.

Limitações

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

Não é possível usar o método tabledata.list para ler o conteúdo de uma tabela com DML detalhada ativada. Em vez disso, use a API Storage Read para ler registros de tabela usando uma API.
Não é possível criar um snapshot de tabela ou um clone de tabela de uma tabela com DML de granularidade fina ativado.
Não é possível ativar a DML detalhada em uma tabela em um conjunto de dados replicado, nem replicar um conjunto de dados que contenha uma tabela com a DML detalhada ativada.
As instruções DML executadas em uma transação de várias instruções não são otimizadas com DML de granularidade fina.

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 ver as práticas recomendadas para otimizar o desempenho da consulta, acesse Introdução à otimização do desempenho da consulta.

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.

A seguir

Para informações e amostras de sintaxe do DML, consulte Sintaxe da DML.
Para mais informações sobre como usar instruções DML em consultas programadas, acesse Como programar consultas.