Como gerenciar tabelas particionadas

Neste documento, você verá como gerenciar tabelas particionadas no BigQuery.

Observação: as informações em Como gerenciar tabelas também se aplicam a tabelas particionadas.

Receber metadados de partição

É possível conseguir informações sobre tabelas particionadas das seguintes maneiras:

• Use a visualização INFORMATION_SCHEMA.PARTITIONS (Visualização).
• Use a metatabela __PARTITIONS_SUMMARY__ (somente SQL legado).

Como ver metadados de partição usando visualizações INFORMATION_SCHEMA

Quando você consulta a visualização INFORMATION_SCHEMA.PARTITIONS, os resultados da consulta contêm uma linha para cada partição. Por exemplo, a consulta a seguir lista todas as partições da tabela no conjunto de dados chamado mydataset:

SELECT table_name, partition_id, total_rows
FROM `mydataset.INFORMATION_SCHEMA.PARTITIONS`
WHERE partition_id IS NOT NULL

Para ver mais informações, consulte INFORMATION_SCHEMA.PARTITIONS.

Como ver metadados de partição usando metatabelas

No SQL legado, é possível ver metadados sobre partições de tabela consultando a metatabela __PARTITIONS_SUMMARY__. Metatabelas são tabelas somente de leitura que contêm metadados.

Consulte a metatabela __PARTITIONS_SUMMARY__ da seguinte maneira:

#legacySQL
SELECT
  column
FROM
  [dataset.table$__PARTITIONS_SUMMARY__]

Observação: o GoogleSQL não é compatível com o separador de decorador de partição ($), portanto não é possível consultar __PARTITIONS_SUMMARY__ no GoogleSQL.

A metatabela __PARTITIONS_SUMMARY__ tem as seguintes colunas:

Valor	Descrição
project_id	Nome do projeto.
dataset_id	Nome do conjunto de dados.
table_id	Nome da tabela particionada por tempo.
partition_id	Nome (data) da partição.
creation_time	A hora em que a partição foi criada, em milissegundos desde 1º de janeiro de 1970, em UTC.
last_modified_time	A hora em que a partição foi modificada pela última vez, em milissegundos desde 1º de janeiro de 1970 UTC.

Para executar um job de consulta que usa a metatabela __PARTITIONS_SUMMARY__, é preciso ter pelo menos as permissões bigquery.jobs.create e bigquery.tables.getData.

Para mais informações sobre papéis do IAM no BigQuery, consulte Controle de acesso.

Definir a expiração da partição

Ao criar uma tabela particionada por tempo de ingestão ou coluna de unidade de tempo, é possível especificar uma expiração de partição. Essa configuração especifica por quanto tempo o BigQuery mantém os dados em cada partição. A configuração se aplica a todas as partições da tabela, mas é calculada de maneira independente para cada partição com base no tempo de partição.

O prazo de validade é calculado a partir do limite de partição em UTC. Por exemplo, com o particionamento diário, o limite da partição é à meia-noite (00:00:00 UTC). Se a validade da partição da tabela for de seis horas, cada partição expirará às 6h00:00 UTC no dia seguinte. Quando uma partição expira, o BigQuery exclui os dados nela.

Também é possível especificar uma expiração de partição padrão no nível do conjunto de dados. Se você definir a expiração da partição em uma tabela, o valor modificará a validade padrão de partições. Se você não especificar a validade da partição (na tabela ou no conjunto de dados), as partições nunca expirarão.

Observação: tabelas particionadas por intervalo de números inteiros não são compatíveis com prazos de validade de partição.

Se você definir a validade da tabela, esse valor terá precedência sobre a validade da partição. Por exemplo, se a validade da tabela for definida como cinco dias, e a validade da partição estiver definida como sete dias, a tabela e todas as partições dela serão excluídas após cinco dias.

Depois de criar uma tabela, é possível atualizar a validade da partição dela a qualquer momento. A nova configuração se aplica a todas as partições dessa tabela, independentemente de quando elas foram criadas. As partições atuais expiram imediatamente se forem anteriores à nova data de validade. Da mesma forma, se os dados estiverem sendo copiados ou inseridos em uma tabela particionada por coluna de unidade de tempo, todas as partições anteriores à expiração configurada para a tabela expirarão imediatamente.

Quando uma partição expira, os dados dessa partição não ficam mais disponíveis para consulta, e você não é cobrado pelo armazenamento nessa partição. O BigQuery excluirá a partição expirada. Até lá, a partição conta para fins de cotas de tabela. Para excluir uma partição imediatamente, é possível excluir manualmente a partição.

Atualizar a validade da partição

Para atualizar a validade da partição de uma tabela particionada:

Console

Não é possível atualizar a validade da partição no console do Google Cloud.

SQL

Use a instrução ALTER TABLE SET OPTIONS. A instrução a seguir atualiza a validade para cinco dias. Para remover a validade da partição de uma tabela, defina partition_expiration_days como NULL.

1. No Console do Google Cloud, acesse a página BigQuery.

   Ir para o BigQuery

2. No editor de consultas, digite a seguinte instrução:

   ALTER TABLE mydataset.mytable
     SET OPTIONS (
       -- Sets partition expiration to 5 days
       partition_expiration_days = 5);
   

3. Clique em Executar.

Para mais informações sobre como executar consultas, acesse Executar uma consulta interativa.

bq

Use o comando bq update com a sinalização --time_partitioning_expiration. Se você estiver atualizando uma tabela particionada em um projeto diferente do projeto padrão, adicione o ID do projeto ao nome do conjunto de dados no seguinte formato: project_id:dataset.

bq update \
--time_partitioning_expiration integer_in_seconds \
--time_partitioning_type unit_time \
project_id:dataset.table

Em que:

• integer é a vida útil padrão (em segundos) para as partições da tabela. Não há valor mínimo. O prazo de validade é avaliado para a data da partição acrescida deste valor. Se você especificar 0, a expiração da partição será removida e a partição nunca expirará. As partições sem expiração precisam ser excluídas manualmente;
• unit_time é DAY, HOUR, MONTH ou YEAR, com base na granularidade de particionamento da tabela. Esse valor precisa corresponder à granularidade que você definiu ao criar a tabela.
• project_id é o ID do projeto.
• dataset é o nome do conjunto de dados que contém a tabela que você está atualizando;
• table é o nome da tabela que você está atualizando.

Exemplos:

Insira o seguinte comando para atualizar o prazo de validade das partições em mydataset.mytable para 5 dias (432.000 segundos). mydataset está no projeto padrão.

bq update --time_partitioning_expiration 432000 mydataset.mytable

Insira o seguinte comando para atualizar o prazo de validade das partições em mydataset.mytable para 5 dias (432.000 segundos). mydataset está em myotherproject, e não no projeto padrão.

bq update \
--time_partitioning_expiration 432000 \
myotherproject:mydataset.mytable

API

Chame o método tables.patch e use a propriedade timePartitioning.expirationMs para atualizar a expiração da partição em milissegundos. Como o método tables.update substitui todo o recurso da tabela, é melhor usar o método tables.patch.

Definir requisitos de filtro de partição

Ao criar uma tabela particionada, é possível exigir que todas as consultas na tabela precisam incluir um filtro de predicado (uma cláusula WHERE) que filtre na coluna de particionamento. Essa configuração pode melhorar o desempenho e reduzir custos, porque o BigQuery pode usar o filtro para remover partições que não correspondem ao predicado.

Para mais informações sobre como adicionar a opção Exigir filtro de partição ao criar uma tabela particionada, consulte Como criar tabelas particionadas.

Se uma tabela particionada tiver a configuração Exigir filtro de partição, cada consulta nessa tabela precisará incluir pelo menos um predicado que faça referência apenas à coluna de particionamento. Consultas sem esse predicado retornam o seguinte erro:

Cannot query over table 'project_id.dataset.table' without a filter that can be used for partition elimination

Para mais informações, consulte Como consultar tabelas particionadas.

Atualizar o requisito de filtro de partição

Se você não ativar a opção Exigir filtro de partição ao criar a tabela particionada, será possível atualizá-la para adicionar a opção.

Console

Não é possível usar o console do Google Cloud para exigir filtros de partição depois que uma tabela particionada é criada.

SQL

Use a instrução ALTER TABLE SET OPTIONS para atualizar o requisito de filtro da partição. O exemplo a seguir atualiza o requisito para true:

1. No Console do Google Cloud, acesse a página BigQuery.

   Ir para o BigQuery

2. No editor de consultas, digite a seguinte instrução:

   ALTER TABLE mydataset.mypartitionedtable
     SET OPTIONS (
       require_partition_filter = true);
   

3. Clique em Executar.

Para mais informações sobre como executar consultas, acesse Executar uma consulta interativa.

bq

Para atualizar uma tabela particionada para exigir filtros de partição usando a ferramenta de linha de comando bq, digite o comando bq update e forneça a flag --require_partition_filter.

Para atualizar uma tabela particionada em um projeto que não seja o padrão, adicione o ID do projeto ao conjunto de dados no seguinte formato: project_id:dataset.

Exemplo:

Para atualizar mypartitionedtable em mydataset no seu projeto padrão, insira:

bq update --require_partition_filter mydataset.mytable

Para atualizar mypartitionedtable em mydataset em myotherproject, insira:

bq update --require_partition_filter myotherproject:mydataset.mytable

API

Chame o método tables.patch e use a propriedade requirePartitionFilter para true para exigir filtros de partição. Como o método tables.update substitui todo o recurso da tabela, é melhor usar o método tables.patch.

Java

Antes de testar esta amostra, siga as instruções de configuração do Java no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Java.

Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, acesse Configurar a autenticação para bibliotecas de cliente.

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Table;

// Sample to update require partition filter on a table.
public class UpdateTableRequirePartitionFilter {

  public static void runUpdateTableRequirePartitionFilter() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    updateTableRequirePartitionFilter(datasetName, tableName);
  }

  public static void updateTableRequirePartitionFilter(String datasetName, String tableName) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      Table table = bigquery.getTable(datasetName, tableName);
      table.toBuilder().setRequirePartitionFilter(true).build().update();

      System.out.println("Table require partition filter updated successfully");
    } catch (BigQueryException e) {
      System.out.println("Table require partition filter was not updated \n" + e.toString());
    }
  }
}

Copiar uma tabela particionada

O processo para copiar uma tabela particionada é igual ao de uma tabela padrão. Para saber mais, consulte Como copiar uma tabela.

Ao copiar uma tabela particionada, lembre-se destas observações:

• Copiar uma tabela particionada para uma nova tabela de destino

  Todas as informações de particionamento são copiadas com a tabela. Isso significa que essas duas tabelas terão partições idênticas.

• Como copiar uma tabela não particionada para uma tabela particionada atual

  Esta operação é compatível apenas com o particionamento por tempo de ingestão. O BigQuery copia os dados de origem para a partição que representa a data atual. Essa operação não é compatível com tabelas particionadas por coluna de unidade de tempo ou de intervalo de números inteiros.

• Como copiar uma tabela particionada para outra tabela particionada

  As especificações de partição das tabelas de origem e destino precisam corresponder.

• Copiar uma tabela particionada em uma tabela não particionada

  A tabela de destino permanece não particionada.

• Como copiar várias tabelas particionadas

  Só será possível copiar várias tabelas de origem para uma tabela particionada no mesmo job se elas forem do mesmo tipo.

  Se todas as tabelas de origem são particionadas, as especificações de partição delas precisam corresponder à especificação da tabela de destino.

Ao copiar para uma tabela existente, é possível especificar se a tabela de destino será anexada ou substituída.

Copiar partições individuais

É possível copiar os dados de uma ou mais partições para outra tabela.

Observação: as permissões necessárias são as mesmas que para copiar uma tabela.

Console

Não é possível copiar partições pelo console do Google Cloud.

bq

Para copiar uma partição, use o comando bq cp (copiar) da ferramenta de linha de comando bq com um decorador de partições ($date), como $20160201.

É possível usar sinalizações opcionais para controlar a disposição de gravação da partição de destino:

• -a ou --append_table anexa os dados da partição de origem para uma tabela ou partição atual no conjunto de dados de destino.
• -f ou --force substitui uma tabela ou partição atual no conjunto de dados de destino e não solicita confirmação.
• -n ou --no_clobber retorna a seguinte mensagem de erro se a tabela ou a partição existir no conjunto de dados de destino: Table '<var>project_id:dataset.table</var> or <var>table$date</var>' already exists, skipping. Se -n não é especificado, o comportamento padrão é solicitar que o usuário escolha se quer substituir a tabela ou a partição de destino.
• --destination_kms_key é a chave do Cloud KMS gerenciada pelo cliente que é usada para criptografar a tabela ou partição de destino.

O comando cp não aceita sinalizações --time_partitioning_field ou --time_partitioning_type. Não é possível usar um job de cópia para converter uma tabela particionada por tempo de ingestão em uma tabela particionada.

--destination_kms_key não é demonstrado aqui. Para saber mais, consulte Como proteger dados com chaves Cloud KMS.

Se o conjunto de dados de origem ou de destino estiver em um projeto diferente do padrão, adicione o ID do projeto aos nomes dos conjuntos de dados no seguinte formato: project_id:dataset.

Opcional: forneça a sinalização --location e defina o valor do local.

bq --location=location cp \
-a -f -n \
project_id:dataset.source_table$source_partition \
project_id:dataset.destination_table$destination_partition

Em que:

• location é o nome do local. A sinalização --location é opcional. Por exemplo, se estiver usando o BigQuery na região de Tóquio, defina o valor da sinalização como asia-northeast1. É possível definir um valor padrão para o local usando o arquivo .bigqueryrc;
• project_id é o ID do projeto;
• dataset é o nome do conjunto de dados de origem ou de destino;
• source_table é a tabela que você está copiando;
• source_partition é o decorador da partição de origem;
• destination_table é o nome da tabela no conjunto de dados de destino;
• destination_partition é o decorador da partição de destino.

Exemplos:

Observação: o separador de decorador de partição ($) é uma variável especial no shell do unix. Pode ser necessário usar o escape para o decorador ao usar a ferramenta de linha de comando. Os exemplos a seguir inserem um caractere de escape do decorador de partições: mydataset.table\$20160519, 'mydataset.table$20160519'.

Como copiar uma partição para uma nova tabela

Insira o seguinte comando para copiar a partição de 30 de janeiro de 2018 de mydataset.mytable para uma nova tabela - mydataset.mytable2. mydataset está em seu projeto padrão.

bq cp -a 'mydataset.mytable$20180130' mydataset.mytable2

Como copiar uma partição para uma tabela não particionada

Insira o seguinte comando para copiar a partição de 30 de janeiro de 2018 de mydataset.mytable para uma tabela não particionada - mydataset2.mytable2. O atalho -a é usado para anexar dados da partição à tabela de destino não particionada. Os dois conjuntos de dados estão no projeto padrão.

bq cp -a 'mydataset.mytable$20180130' mydataset2.mytable2

Insira o seguinte comando para copiar a partição de 30 de janeiro de 2018 de mydataset.mytable para uma tabela não particionada - mydataset2.mytable2. O atalho -f é usado para substituir a tabela de destino não particionada sem enviar solicitação.

bq --location=US cp -f 'mydataset.mytable$20180130' mydataset2.mytable2

Como copiar uma partição para outra tabela particionada

Insira o seguinte comando para copiar a partição de 30 de janeiro de 2018 de mydataset.mytable para outra tabela particionada - mydataset2.mytable2. O atalho -a é usado para anexar os dados da partição à tabela de destino. Como não é especificado nenhum decorador de partição na tabela de destino, a chave da partição de origem é mantida e os dados são copiados para a partição de 30 de janeiro de 2018 na tabela de destino. Também é possível especificar um decorador de partições na tabela de destino para copiar dados para uma partição específica. mydataset está no projeto padrão. mydataset2 está em myotherproject, e não no projeto padrão.

bq --location=US cp \
-a \
'mydataset.mytable$20180130' \
myotherproject:mydataset2.mytable2

Insira o seguinte comando para copiar a partição de 30 de janeiro de 2018 de mydataset.mytable para a partição de 20 de fevereiro de 2018 de outra tabela particionada - mydataset2.mytable2. O atalho -f é usado para substituir a partição de 20 de fevereiro de 2018 na tabela de destino sem solicitar. Se nenhum decorador de partições for usado, todos os dados na tabela de destino serão sobrescritos. mydataset está no projeto padrão. mydataset2 está em myotherproject, e não no projeto padrão.

bq cp \
-f \
'mydataset.mytable$20180130' \
'myotherproject:mydataset2.mytable2$20180220'

Insira o seguinte comando para copiar a partição de 30 de janeiro de 2018 de mydataset.mytable para outra tabela particionada - mydataset2.mytable2. mydataset está no projeto padrão. mydataset2 está em myotherproject, e não no projeto padrão. Se houver dados na tabela de destino, o comportamento padrão é solicitar que você sobrescreva.

bq cp \
'mydataset.mytable$20180130' \
myotherproject:mydataset2.mytable2

Observação: o comando bq cp com um decorador de partição funciona em partições baseadas em colunas em que as partições de origem e de destino são idênticas. O comando bq cp também funciona em partições baseadas no tempo de processamento, em que a partição representa a mesma unidade de tempo ou uma unidade de tempo aproximada que contém a partição de origem. Por exemplo, se $20180130 for o decorador da partição de origem, os decoradores de partição de destino válidos incluirão $20180130, $201801 e $2018. Para copiar uma partição baseada em colunas para um decorador de partição completamente diferente ou para uma partição de unidade de tempo com granularidade menor, use uma instrução INSERT SELECT.

Para copiar várias partições, especifique-as como uma lista separada por vírgulas:

bq cp \
'mydataset.mytable$20180130,mydataset.mytable$20180131' \
myotherproject:mydataset.mytable2

API

Chame o método jobs.insert e configure um job copy. (Opcional) Especifique sua região na property location da seção jobReference do recurso do job.

Na configuração do job, especifique as propriedades a seguir:

• Insira o conjunto de dados, a tabela e a partição de origem na propriedade sourceTables.
• Insira o conjunto de dados e a tabela de destino na propriedade destinationTable.
• Use a propriedade writeDisposition para especificar se pretende anexar ou substituir a tabela ou a partição de destino.

Para copiar várias partições, insira as partições de origem (incluindo os nomes do conjunto de dados e da tabela) na propriedade sourceTables.

Excluir uma partição

É possível excluir uma partição individual de uma tabela particionada. No entanto, não é possível excluir as partições especiais __NULL__ ou __UNPARTITIONED__.

No momento, só é possível excluir uma partição por vez.

Observação: as permissões necessárias são as mesmas para excluir uma tabela.

Para excluir uma partição, especifique o decorador da partição, a menos que seja uma das duas partições especiais.

Para excluir uma partição em uma tabela particionada:

Console

Não é possível excluir partições pelo console do Google Cloud.

SQL

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:

1. No Console do Google Cloud, acesse a página BigQuery.

   Ir para o BigQuery

2. No editor de consultas, digite a seguinte instrução:

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

3. Clique em Executar.

Para mais informações sobre como executar consultas, acesse Executar uma consulta interativa.

bq

Use o comando bq rm com a sinalização --table (ou o atalho -t) e especifique o decorador de partições para excluir uma partição específica.

bq rm --table project_id:dataset.table$partition

Em que:

• project_id é o ID do projeto. Se omitido, o projeto padrão será usado.
• dataset é o nome do conjunto de dados que contém a tabela;
• table é o nome da tabela.
• partition é o decorador da partição que você está excluindo.

Os decoradores de partições têm o seguinte formato, dependendo do tipo de particionamento:

• Partição por hora: yyyymmddhh. Exemplo: $2016030100
• Partição diária: yyyymmdd. Exemplo: $20160301
• Partição mensal: yyyymm. Exemplo: $201603
• Partição anual: yyyy. Exemplo: $2016
• Partição de intervalo de números inteiros: início do intervalo da partição. Exemplo: $20.

A ferramenta de linha de comando bq solicita que você confirme a ação. Para ignorar a confirmação, use a flag --force (ou atalho -f).

Observação: o separador de decorador de partição ($) é uma variável especial no shell do unix. Pode ser necessário usar o escape para o decorador ao usar a ferramenta de linha de comando. Os exemplos a seguir inserem um caractere de escape do decorador de partições: mydataset.table\$20160519, 'mydataset.table$20160519'.

Exemplos:

Exclua a partição de 1o de março de 2016 em uma tabela particionada diária chamada mydataset.mytable no seu projeto padrão:

bq rm --table 'mydataset.mytable$20160301'

Exclua a partição de março de 2016 em uma tabela particionada mensal:

bq rm --table 'mydataset.mytable$201603'

Exclua o intervalo de números inteiros a partir de 20 em uma tabela particionada de variação em números inteiros chamada mydataset.mytable:

bq rm --table 'mydataset.mytable$20'

API

Chame o método tables.delete e especifique o decorador de tabelas e partições usando o parâmetro tableId.

Segurança de tabelas particionadas

O controle de acesso para tabelas particionadas é o mesmo que o das tabelas padrão. Saiba mais em Introdução aos controles de acesso à tabela.