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) 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 instrução INSERT a seguir 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 carimbos de data/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 instrução INSERT a seguir 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

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 seguinte instrução DELETE exclui todas as linhas da partição de 1º de junho de 2017 ("2017-06-01") de mytable 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 todas as linhas da partição de 1º de junho de 2017 ("2017-06-01") de 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

DML em tabelas particionadas por hora, mês e ano

É possível usar instruções DML para modificar uma tabela particionada por hora, mês ou ano. Forneça o intervalo de hora, mês ou ano das datas ou carimbos de data/hora relevantes, como no exemplo a seguir para tabelas particionadas mensais:

    bq query --nouse_legacy_sql 'DELETE FROM my_dataset.my_table WHERE
    TIMESTAMP_TRUNC(ts_column, MONTH) = "2020-01-01 00:00:00";'

Ou outro exemplo para tabelas particionadas com a coluna DATETIME:

    bq query --nouse_legacy_sql 'DELETE FROM my_dataset.my_table WHERE
    dt_column BETWEEN DATETIME("2020-01-01")
    AND DATETIME("2020-05-01");'

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 por tempo de processamento) ou usando a data, o carimbo de data/hora ou a coluna de data e hora (para tabelas particionadas). A remoção de partições reduz os custos e melhora o desempenho da consulta.

É possível usar condições de remoção de partição nos seguintes locais: em um filtro da subconsulta, um filtro search_condition ou um filtro merge_condition.

Cada um dos exemplos abaixo consulta uma tabela particionada por tempo de ingestão 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 consulta 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 supõe uma FULL OUTER JOIN entre as tabelas de origem e de destino. Normalmente, não é possível remover partições em uma FULL OUTER JOIN. No entanto, se um predicado falso constante for usado, a condição de filtro poderá ser usada para a remoção de 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 consulta 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 Referência da DML.

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