Como atualizar dados de tabela particionada usando DML

Nesta página, você encontra uma visão geral do suporte à Linguagem de manipulação de dados (DML, na sigla em inglês) para tabelas particionadas.

Para saber mais sobre a DML, consulte estes tópicos:

Tabelas usadas nos exemplos

As seguintes definições de esquema JSON representam as tabelas usadas nos exemplos desta página.

mytable: uma tabela particionada por tempo de ingestão

    [
      {"name": "field1", "type": "INTEGER"},
      {"name": "field2", "type": "STRING"}
    ]

mytable2: uma tabela padrão (não particionada)

    [
      {"name": "id", "type": "INTEGER"},
      {"name": "ts", "type": "TIMESTAMP"}
    ]

mycolumntable: uma tabela particionada por meio da coluna ts TIMESTAMP

    [
      {"name": "field1", "type": "INTEGER"},
      {"name": "field2", "type": "STRING"}
      {"name": "field3", "type": "BOOLEAN"}
      {"name": "ts", "type": "TIMESTAMP"}
    ]

Como inserir dados

Use uma instrução DML INSERT para adicionar linhas a uma tabela particionada.

Como inserir dados em tabelas particionadas por tempo de ingestão

Quando você usa uma instrução DML para adicionar linhas a uma tabela particionada por tempo de ingestão, é possível especificar a partição à qual as linhas devem ser adicionadas. Faça referência à partição usando a pseudocoluna _PARTITIONTIME.

Por exemplo, a seguinte instrução INSERT adiciona uma linha à partição de 1º de maio de 2017 de mytable - “2017-05-01”.

INSERT INTO
  project_id.dataset.mytable (_PARTITIONTIME,
    field1,
    field2)
SELECT
  TIMESTAMP("2017-05-01"),
  1,
  "one"

Somente os registros de data e hora que correspondem aos limites de data exatos podem ser usados. Por exemplo, a seguinte instrução DML retorna um erro:

INSERT INTO
  project_id.dataset.mytable (_PARTITIONTIME,
    field1,
    field2)
SELECT
  TIMESTAMP("2017-05-01 21:30:00"),
  1,
  "one"

Como inserir dados em tabelas particionadas

O processo para inserir dados em uma tabela particionada usando DML é igual ao usado para inserir dados em uma tabela não particionada.

Por exemplo, a seguinte instrução INSERT adiciona linhas à tabela particionada mycolumntable selecionando dados de mytable2 (uma tabela não particionada).

INSERT INTO
  project_id.dataset.mycolumntable (ts,
    field1)
SELECT
  ts,
  id
FROM
  project_id.dataset.mytable2

Como excluir dados

Use uma instrução DML DELETE para excluir linhas de uma tabela particionada.

Como excluir dados em tabelas particionadas por tempo de ingestão

A instrução DELETE a seguir exclui de mytable todas as linhas da partição de 1º de junho de 2017 ("2017-06-01"), em que field1 é igual a 21. Faça referência à partição usando a pseudocoluna _PARTITIONTIME.

DELETE
  project_id.dataset.mytable
WHERE
  field1 = 21
  AND _PARTITIONTIME = "2017-06-01"

Como excluir dados em tabelas particionadas

O processo usado para excluir dados em uma tabela particionada usando DML é igual ao usado para excluir dados de uma tabela não particionada.

Por exemplo, a instrução DELETE a seguir exclui de "2017-06-01" todas as linhas da partição de 1º de junho de 2017 (mycolumntable), em que field1 é igual a 21.

DELETE
  project_id.dataset.mycolumntable
WHERE
  field1 = 21
  AND DATE(ts) = "2017-06-01"

Como atualizar dados

Use uma instrução UPDATE para atualizar linhas em uma tabela particionada.

Como atualizar dados em tabelas particionadas por tempo de ingestão

A instrução UPDATE a seguir move linhas de uma partição para outra. As linhas na partição de 1º de maio de 2017 (“2017-05-01”) de mytable em que field1 é igual a 21 são movidas para a partição de 1º de junho de 2017 (“2017-06-01”).

UPDATE
  project_id.dataset.mytable
SET
  _PARTITIONTIME = "2017-06-01"
WHERE
  _PARTITIONTIME = "2017-05-01"
  AND field1 = 21

Como atualizar dados em tabelas particionadas

O processo de atualizar dados em uma tabela particionada usando DML é o igual ao usado para atualizar dados de uma tabela não particionada. A instrução UPDATE a seguir move linhas de uma partição para outra. As linhas na partição de 1º de maio de 2017 (“2017-05-01”) de mytable em que field1 é igual a 21 são movidas para a partição de 1º de junho de 2017 (“2017-06-01”).

UPDATE
  project_id.dataset.mycolumntable
SET
  ts = "2017-06-01"
WHERE
  DATE(ts) = "2017-05-01"
  AND field1 = 21

Como usar uma instrução MERGE

Use uma instrução DML MERGE para combinar operações INSERT, UPDATE e DELETE de uma tabela particionada em uma única instrução e executá-las de maneira atômica.

Como remover partições ao usar uma instrução MERGE

Ao executar uma instrução MERGE em uma tabela particionada, limite as partições envolvidas na instrução usando a pseudocoluna _PARTITIONTIME (para tabelas particionadas de tempo de processamento) ou usando a coluna de data ou carimbo de data/hora (para tabelas particionadas). A remoção de partições reduz o custo e melhora o desempenho da consulta.

Use condições de remoção de partição nos seguintes locais: em um filtro de subconsulta, um filtro search_condition ou um filtro merge_condition.

Cada um dos exemplos abaixo consulta uma tabela particionada por tempo de processamento usando a pseudocoluna _PARTITIONTIME.

Como usar uma subconsulta para filtrar dados de origem

Use um filtro em uma subconsulta para remover partições. Por exemplo, na instrução MERGE a seguir, somente as linhas na partição '2018-01-01' na tabela de origem são verificadas.

MERGE dataset.target T
USING (SELECT * FROM dataset.source WHERE _PARTITIONTIME = '2018-01-01') S
ON T.c1 = S.c1
WHEN MATCHED THEN
  DELETE

Como usar um filtro na search_condition de uma when_clause

O otimizador de consultas tenta usar um filtro em uma search_condition para remover partições. Por exemplo, na instrução MERGE a seguir, somente as linhas nas seguintes partições são verificadas na tabela de destino: '2018-01-01', '2018-01-02' e '2018-01-03'.

MERGE dataset.target T
USING dataset.source S
ON T.c1 = S.c1
WHEN MATCHED AND T._PARTITIONTIME = '2018-01-01' THEN
  UPDATE SET c1 = S.c1
WHEN MATCHED AND T._PARTITIONTIME = '2018-01-02' THEN
  UPDATE SET c1 = c1 + 10
WHEN NOT MATCHED BY SOURCE AND T._PARTITIONTIME = '2018-01-03' THEN
  DELETE

No exemplo a seguir, a cláusula WHEN NOT MATCHED BY SOURCE precisa de todos os dados da tabela de destino. Dessa maneira, todas as partições são verificadas, e você é cobrado pelos bytes lidos em todas as partições.

MERGE dataset.target T
USING dataset.source S
ON T.c1 = S.c1
WHEN MATCHED AND T._PARTITIONTIME = '2018-01-01' THEN
  UPDATE SET c1 = S.c1
WHEN NOT MATCHED BY SOURCE THEN
  UPDATE SET c1 = c1 + 1

Em geral, quando você usa as cláusulas WHEN NOT MATCHED e WHEN NOT MATCHED BY SOURCE juntas, o BigQuery pressupõe um FULL OUTER JOIN entre as tabelas de origem e de destino. Normalmente, não é possível remover partições em uma FULL OUTER JOIN. Porém, se um predicado falso constante for usado, a condição de filtro poderá ser usada para remover partição. Na consulta a seguir, a remoção de partição é usada para verificar apenas a partição '2018-01-01' nas tabelas de destino e de origem.

MERGE dataset.target T
USING dataset.source S
ON FALSE
WHEN NOT MATCHED AND _PARTITIONTIME = '2018-01-01' THEN
  INSERT(c1) VALUES(c1)
WHEN NOT MATCHED BY SOURCE AND _PARTITIONTIME = '2018-01-01' THEN
  DELETE

Como usar um filtro em uma merge_condition

O otimizador de consultas tenta usar um filtro em uma merge_condition para remover partições. Por exemplo, a consulta a seguir só verificará a partição '2018-01-01' nas tabelas de destino e de origem.

MERGE dataset.target T
USING dataset.source S
ON T.c1 = S.c1 AND
  T._PARTITIONTIME = '2018-01-01' AND
  S._PARTITIONTIME = '2018-01-01'
WHEN MATCHED THEN
  UPDATE SET c1 = S.c1

Neste exemplo, a merge_condition é usada como um predicado para unir as tabelas de origem e de destino. O otimizador de consulta pode ser capaz ou não de usar o push-down de predicado (dependendo do tipo de junção).

No exemplo a seguir, a instrução MERGE não permite a remoção de partição porque o filtro de partição é um predicado em uma condição de associação que não pode ser aplicada diretamente na tabela.

MERGE dataset.target T
USING dataset.source S
ON T.c1 = S.c1 AND T._PARTITIONTIME = '2018-01-01'
WHEN NOT MATCHED BY SOURCE THEN
  UPDATE SET c1 = S.c1

Limitações

Para informações sobre as limitações da DML, consulte Limitações na página Linguagem de manipulação de dados.

Cotas

Para informações sobre as cotas de DML, consulte Instruções DML na página Cotas e limites.

Preços

Para saber mais sobre os preços para DML, consulte Preços da DML para tabelas particionadas.

Próximas etapas

Esta página foi útil? Conte sua opinião sobre:

Enviar comentários sobre…

Precisa de ajuda? Acesse nossa página de suporte.