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"}
    ]

Nos exemplos em que COLUMN_ID aparece, substitua-o pelo nome da coluna em que você quer operar.

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 usar a exclusão DML DELETE para excluir partições

Se uma instrução DELETE qualificada cobrir todas as linhas em uma partição, o BigQuery removerá a partição inteira. Essa remoção é feita sem verificar bytes ou consumir slots. O exemplo a seguir de uma instrução DELETE abrange toda a partição de um filtro na pseudocoluna _PARTITIONDATE:

DELETE mydataset.mytable
WHERE _PARTITIONDATE IN ('2076-10-07', '2076-03-06');

Desqualificações comuns

Consultas com as características a seguir podem não se beneficiar da otimização:

A qualificação para otimização pode variar de acordo com o tipo de particionamento, os metadados de armazenamento subjacentes e os predicados de filtro. Como prática recomendada, execute uma simulação para verificar se a consulta resulta em 0 bytes processados.

Transação com várias instruções

Essa otimização funciona em uma transação de várias instruções. O exemplo de consulta a seguir substitui uma partição por dados de outra tabela em uma única transação, sem verificar a partição em busca da instrução DELETE.

DECLARE REPLACE_DAY DATE;
BEGIN TRANSACTION;

-- find the partition which we want to replace
SET REPLACE_DAY = (SELECT MAX(d) FROM mydataset.mytable_staging);

-- delete the entire partition from mytable
DELETE FROM mydataset.mytable WHERE part_col = REPLACE_DAY;

-- insert the new data into the same partition in mytable
INSERT INTO mydataset.mytable
SELECT * FROM mydataset.mytable_staging WHERE part_col = REPLACE_DAY;

COMMIT TRANSACTION;

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, você pode limitar quais partições são verificadas incluindo a coluna de particionamento em um filtro de subconsulta, um filtro search_condition ou um filtro merge_condition. A remoção pode ocorrer ao verificar a tabela de origem, a tabela de destino ou ambas.

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

Como usar uma subconsulta para filtrar dados de origem

Na instrução MERGE a seguir, a subconsulta na cláusula USING filtra a pseudocoluna _PARTITIONTIME na tabela de origem.

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

Analisando o plano de execução da consulta, a subconsulta é executada primeiro. Somente as linhas na partição '2018-01-01' na tabela de origem são verificadas. Veja a etapa relevante no plano de consulta:

READ $10:name, $11:_PARTITIONTIME
FROM temp.source
WHERE equal($11, 1514764800.000000000)

Como usar um filtro na search_condition de uma when_clause

Se um search_condition contiver um filtro, o otimizador de consulta tentará remover partições. Por exemplo, na instrução MERGE a seguir, cada cláusula WHEN MATCHED e WHEN NOT MATCHED contém um filtro na pseudocoluna _PARTITIONTIME.

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

Durante o estágio de junção, apenas as seguintes partições são verificadas na tabela de destino: '2018-01-01', '2018-01-02' e '2018-01-03', ou seja, a união de todos os filtros search_condition.

No plano de execução da consulta:

READ
$1:COLUMN_ID, $2:_PARTITIONTIME, $3:$file_temp_id, $4:$row_temp_id
FROM temp.target
WHERE or(equal($2, 1514764800.000000000), equal($2, 1514851200.000000000), equal($2, 1514937600.000000000))

No entanto, no exemplo a seguir, a cláusula WHEN NOT MATCHED BY SOURCE não tem uma expressão de filtro:

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

Essa consulta precisa verificar toda a tabela de destino para calcular a cláusula WHEN NOT MATCHED BY SOURCE. Como resultado, nenhuma partição é removida.

Como usar um predicado falso constante em um merge_condition

Se você usar as cláusulas WHEN NOT MATCHED e WHEN NOT MATCHED BY SOURCE juntas, o BigQuery geralmente executará uma junção externa completa, que não pode ser removida. No entanto, se a condição de mesclagem usar um predicado falso constante, o BigQuery poderá usar a condição de filtro para remover a partição. Para mais informações sobre o uso de predicados falsos constantes, consulte a descrição da cláusula merge_condition na documentação da instrução MERGE.

O exemplo a seguir verifica somente 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(COLUMN_ID) VALUES(COLUMN_ID)
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. O otimizador de consulta pode ou não conseguir executar o push-down do predicado na etapa de verificação da tabela, dependendo do tipo de junção.

No exemplo a seguir, o merge_condition é usado como um predicado para unir as tabelas de origem e de destino. O otimizador de consulta pode executar o push-down desse predicado ao verificar ambas as tabelas. Como resultado, a consulta apenas verifica a partição '2018-01-01' nas tabelas de destino e de origem.

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

No próximo exemplo, merge_condition não contém um predicado para a tabela de origem. Portanto, não é possível executar a remoção da partição na tabela de origem. A instrução contém um predicado para a tabela de destino, mas a instrução usa uma cláusula WHEN NOT MATCHED BY SOURCE em vez de uma cláusula WHEN MATCHED. Isso significa que a consulta precisa verificar toda a tabela de destino em busca das linhas que não corresponderem.

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

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.

Segurança de tabelas

Para controlar o acesso a tabelas no BigQuery, consulte Introdução aos controles de acesso a tabelas.

A seguir