Atualizar dados de tabelas particionadas através de DML

Esta página fornece uma vista geral do suporte da linguagem de manipulação de dados (DML) para tabelas particionadas.

Para mais informações sobre a LMD, consulte:

Tabelas usadas nos exemplos

As seguintes definições do esquema JSON representam as tabelas usadas nos exemplos nesta 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 que é particionada através da coluna ts TIMESTAMP 

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

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

Inserir dados

Usa uma declaração INSERTDML para adicionar linhas a uma tabela particionada.

Inserir dados em tabelas particionadas por tempo de ingestão

Quando usa uma declaração DML para adicionar linhas a uma tabela particionada por tempo de carregamento, pode especificar a partição à qual as linhas devem ser adicionadas. Faz referência à partição através da pseudocoluna _PARTITIONTIME.

Por exemplo, a seguinte declaraçã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"

Só é possível usar indicações de tempo que correspondam a limites de datas exatos. Por exemplo, a seguinte instrução DML devolve um erro:

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

Inserir dados em tabelas particionadas

A inserção de dados numa tabela particionada através de DML é igual à inserção de dados numa tabela não particionada.

Por exemplo, a seguinte declaraçã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

Eliminar dados

Use uma declaração DML DELETE para eliminar linhas de uma tabela particionada.

Eliminar dados em tabelas particionadas por tempo de ingestão

A seguinte declaração DELETE elimina todas as linhas da partição de 1 de junho de 2017 ("2017-06-01") de mytable onde field1 é igual a 21. Faz referência à partição através da pseudocoluna _PARTITIONTIME.

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

Eliminar dados em tabelas particionadas

A eliminação de dados numa tabela particionada através de DML é igual à eliminação de dados de uma tabela não particionada.

Por exemplo, a seguinte declaração DELETE elimina todas as linhas da partição de 1 de junho de 2017 ("2017-06-01") de mycolumntable onde field1 é igual a 21.

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

Usar o comando DELETE de DML para eliminar partições

Se uma declaração DELETE de qualificação abranger todas as linhas numa partição, o BigQuery remove a partição inteira. Esta remoção é feita sem analisar bytes nem consumir espaços. O exemplo seguinte de uma declaraçã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

As consultas com as seguintes caraterísticas podem não beneficiar da otimização:

A elegibilidade para otimização pode variar consoante o tipo de partição, os metadados de armazenamento subjacentes e os predicados de filtro. Como prática recomendada, faça um teste para verificar se a consulta resulta em 0 bytes processados.

Transação com vários extratos

Esta otimização funciona numa transação com várias declarações. O exemplo de consulta seguinte substitui uma partição por dados de outra tabela numa única transação, sem analisar a partição para a declaraçã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;

A atualizar dados

Use uma declaração UPDATE para atualizar linhas numa tabela particionada.

Atualizar dados em tabelas particionadas por tempo de ingestão

A seguinte declaração UPDATE 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

Atualizar dados em tabelas particionadas

A atualização de dados numa tabela particionada através de DML é igual à atualização de dados de uma tabela não particionada. Por exemplo, a seguinte declaração UPDATE move linhas de uma partição para outra. As linhas na partição de 1 de maio de 2017 (“2017-05-01”) de mytable onde 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

Pode usar declarações DML para modificar uma tabela particionada por hora, mês ou ano. Indique o intervalo de horas, meses ou anos das datas/indicações de tempo/datas e horas relevantes, como no seguinte exemplo para tabelas particionadas mensalmente:

    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");'

Usar uma declaração MERGE

Usa uma declaração MERGEDML para combinar operações INSERT, UPDATE e DELETE para uma tabela particionada numa declaração e executá-las de forma atómica.

Eliminar partições quando usar uma declaração MERGE

Quando executa uma declaração MERGE numa tabela particionada, pode limitar as partições que são analisadas incluindo a coluna de particionamento num filtro de subconsulta, num filtro search_condition ou num filtro merge_condition. A eliminação pode ocorrer durante a análise da tabela de origem, da tabela de destino ou de ambas.

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

Usar uma subconsulta para filtrar dados de origem

Na declaração MERGE seguinte, a subconsulta na cláusula USING filtra na 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. Apenas as linhas na partição '2018-01-01' na tabela de origem são analisadas. Segue-se a fase relevante no plano de consulta:

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

Usar um filtro no search_condition de um when_clause

Se um search_condition contiver um filtro, o otimizador de consultas tenta remover partições. Por exemplo, na declaração MERGE seguinte, 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 a fase de junção, apenas são analisadas as seguintes partições 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.

A partir do 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 seguinte, 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

Esta consulta tem de analisar toda a tabela de destino para calcular a cláusula WHEN NOT MATCHED BY SOURCE. Como resultado, não são removidas partições.

Usar um predicado falso constante num merge_condition

Se usar as cláusulas WHEN NOT MATCHED e WHEN NOT MATCHED BY SOURCE em conjunto, o BigQuery costuma fazer uma junção externa completa, que não pode ser reduzida. No entanto, se a condição de união usar um predicado constante falso, o BigQuery pode usar a condição de filtro para a eliminação de partições. Para mais informações sobre a utilização de predicados falsos constantes, consulte a descrição da cláusula merge_condition na documentação da declaração MERGE.

O exemplo seguinte analisa 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(COLUMN_ID) VALUES(COLUMN_ID)
WHEN NOT MATCHED BY SOURCE AND _PARTITIONTIME = '2018-01-01' THEN
  DELETE

Usar um filtro num merge_condition

O otimizador de consultas tenta usar um filtro num merge_condition para remover partições. O otimizador de consultas pode ou não conseguir enviar o predicado para a fase de análise da tabela, consoante o tipo de junção.

No exemplo seguinte, o merge_condition é usado como um predicado para juntar as tabelas de origem e de destino. O otimizador de consultas pode enviar este predicado para baixo quando analisa ambas as tabelas. Como resultado, a consulta apenas analisa a partição nas tabelas de destino e de origem.'2018-01-01'

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 exemplo seguinte, o merge_condition não contém um predicado para a tabela de origem, pelo que não é possível realizar a eliminação de partições na tabela de origem. A declaração contém um predicado para a tabela de destino, mas usa uma cláusula WHEN NOT MATCHED BY SOURCE em vez de uma cláusula WHEN MATCHED. Isto significa que a consulta tem de analisar toda a tabela de destino para encontrar as linhas que não correspondem.

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 obter informações sobre as limitações da DML, consulte as Limitações na página Referência da DML.

Quotas

Para obter informações sobre as quotas de DML, consulte o artigo Declarações DML na página Quotas e limites.

Preços

Para ver informações sobre os preços da DML, saiba como calcular o tamanho da consulta para declarações de DML executadas em tabelas particionadas.

Segurança da mesa

Para controlar o acesso a tabelas no BigQuery, consulte o artigo Controle o acesso a recursos com a IAM.

O que se segue?