Como usar 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 SQL padrão. Para ativá-lo, consulte Como ativar o SQL padrão.
  • Não é possível especificar uma tabela de destino para a consulta.

Para ver uma lista de instruções DML do BigQuery, consulte Instruções da linguagem de manipulação de dados no SQL padrão.

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.

  • Linhas que foram gravadas em uma tabela recentemente usando streaming (o método tabledata.insertall ou a API Storage Write) não podem ser modificadas com instruções UPDATE, DELETE ou MERGE. As gravações recentes são aquelas que ocorrem nos últimos 30 minutos. Todas as outras linhas da tabela podem ser alteradas usando instruções UPDATE, DELETE ou MERGE. Os dados transmitidos podem levar até 90 minutos para ficarem disponíveis para operações de cópia.

  • 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.

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 em uma tabela são executadas simultaneamente. Depois que esse limite é atingido, a simultaneidade de instruções INSERT que gravam em uma tabela é limitada a 10. Todos os jobs DML INSERT além de 10 são colocados em fila no estado PENDING. Depois que um job anterior é concluído, o próximo job pendente é retirado da fila e executado. Atualmente, até 100 instruções DML INSERT podem ser enfileiradas em uma tabela a qualquer momento.

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. Atualmente, as instruções DML mutantes compartilham uma fila por tabela com comprimento máximo de 20. As instruções adicionais além do tamanho máximo da fila falharão para cada tabela.

Os jobs DML de prioridade INTERACTIVE que estão na fila por mais de seis horas falham.

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.

Preços

Para saber mais sobre preços da DML, consulte Preços da Linguagem de manipulação de dados, na página Preços.

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 s = "some string" WHERE id BETWEEN 54 AND 75

    Veja um exemplo semelhante que filtra uma pequena lista de valores de coluna:

    UPDATE s = "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.

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.