Como gerenciar dados de tabela particionada

Neste documento, você aprende a gerenciar dados de tabelas particionadas no BigQuery. É possível trabalhar com eles das seguintes maneiras:

  • Carregar dados em uma tabela particionada.
  • Procurar (ou pré-visualizar) dados de tabelas particionadas.
  • Consultar dados de tabelas particionadas.
  • Anexar ou substituir dados de tabelas particionadas.
  • Modificar os dados de tabelas particionadas usando as declarações da Linguagem de manipulação de dados.
  • Copiar dados de tabelas particionadas.
  • Transmitir dados para tabelas particionadas.
  • Exportar dados de tabelas particionadas

Para informações sobre como gerenciar esquemas de tabelas, consulte Como modificar esquemas de tabelas.

Como carregar dados em uma tabela particionada

Você pode criar uma tabela particionada ao carregar dados ou gerar uma tabela vazia e carregar os dados posteriormente. Ao carregar dados em uma tabela particionada, use a detecção automática de esquema em formatos de dados compatíveis ou especifique o esquema.

Para mais informações sobre o carregamento de dados, consulte a documentação para o formato e o local dos dados de origem:

Java

Antes de testar essa amostra, siga as instruções de configuração para 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 do BigQuery para Java.

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.FormatOptions;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobId;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.LoadJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TimePartitioning;
import java.time.Duration;
import java.time.temporal.ChronoUnit;
import java.util.UUID;

public class LoadPartitionedTable {

  public static void runLoadPartitionedTable() throws Exception {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "/path/to/file.csv";
    loadPartitionedTable(datasetName, tableName, sourceUri);
  }

  public static void loadPartitionedTable(String datasetName, String tableName, String sourceUri)
      throws Exception {
    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();

      TableId tableId = TableId.of(datasetName, tableName);

      Schema schema =
          Schema.of(
              Field.of("name", StandardSQLTypeName.STRING),
              Field.of("post_abbr", StandardSQLTypeName.STRING),
              Field.of("date", StandardSQLTypeName.DATE));

      // Configure time partitioning. For full list of options, see:
      // https://cloud.google.com/bigquery/docs/reference/rest/v2/tables#TimePartitioning
      TimePartitioning partitioning =
          TimePartitioning.newBuilder(TimePartitioning.Type.DAY)
              .setField("date")
              .setExpirationMs(Duration.of(90, ChronoUnit.DAYS).toMillis())
              .build();

      LoadJobConfiguration loadJobConfig =
          LoadJobConfiguration.builder(tableId, sourceUri)
              .setFormatOptions(FormatOptions.csv())
              .setSchema(schema)
              .setTimePartitioning(partitioning)
              .build();

      // Create a job ID so that we can safely retry.
      JobId jobId = JobId.of(UUID.randomUUID().toString());
      Job loadJob = bigquery.create(JobInfo.newBuilder(loadJobConfig).setJobId(jobId).build());

      // Load data from a GCS parquet file into the table
      // Blocks until this load table job completes its execution, either failing or succeeding.
      Job completedJob = loadJob.waitFor();

      // Check for errors
      if (completedJob == null) {
        throw new Exception("Job not executed since it no longer exists.");
      } else if (completedJob.getStatus().getError() != null) {
        // You can also look at queryJob.getStatus().getExecutionErrors() for all
        // errors, not just the latest one.
        throw new Exception(
            "BigQuery was unable to load into the table due to an error: \n"
                + loadJob.getStatus().getError());
      }
      System.out.println("Data successfully loaded into time partitioned table during load job");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println(
          "Data not loaded into time partitioned table during load job \n" + e.toString());
    }
  }
}

Como procurar dados de tabela

Para procurar dados em tabelas particionadas:

  • Use o Console do Cloud ou a IU da Web clássica do BigQuery
  • Use o comando bq head da ferramenta de linha de comando bq.
  • Chame o tabledata.list método de API.
  • usando as bibliotecas de cliente.

Permissões necessárias

Para procurar dados em tabelas e partições, é necessário ter, no mínimo, as permissões bigquery.tables.getData. Os papéis predefinidos do Cloud IAM abaixo incluem permissões bigquery.tables.getData:

  • bigquery.dataViewer
  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery.admin

Além disso, se um usuário tiver permissões bigquery.datasets.create ao criar um conjunto de dados, será concedido o acesso bigquery.dataOwner. O acesso bigquery.dataOwner permite que o usuário procure dados em tabelas e partições no conjunto de dados.

Para mais informações sobre os papéis e permissões do Cloud IAM no BigQuery, consulte Papéis e permissões predefinidos.

Como procurar dados em tabelas particionadas

Para navegar pelos dados da tabela particionada:

Console

  1. No Console do Cloud, no painel de navegação, clique em um conjunto de dados para listar suas tabelas e visualizações.

  2. Clique em uma tabela particionada na lista.

  3. Clique na guia Detalhes.

    Detalhes da tabela

  4. Anote o valor em Número de linhas. Talvez esse valor seja necessário para controlar o ponto de partida dos seus resultados usando a API ou a ferramenta de linha de comando bq.

    Número de linhas

  5. Clique na guia Visualizar. Um conjunto de dados de amostra é exibido. Não é possível visualizar partições individuais usando o Console do Cloud.

IU clássica

  1. No painel de navegação da IU da Web do BigQuery, clique na seta azul à esquerda do conjunto de dados para expandi-lo ou clique duas vezes no nome dele. Isso exibe as tabelas e visualizações no conjunto de dados.

  2. Clique em uma tabela particionada na lista.

  3. Clique em Detalhes e anote o valor em Número de linhas. Talvez esse valor seja necessário para controlar o ponto de partida dos seus resultados usando a API ou a ferramenta de linha de comando bq.

  4. Clique em Visualizar. Um conjunto de dados de amostra é exibido. Não é possível visualizar partições individuais usando a IU da Web do BigQuery.

    Visualização da tabela

bq

Use o comando bq head com a sinalização --max_rows para listar todos os campos em um determinado número de linhas da tabela. Se --max_rows não for especificado, o padrão será 100. Especifique uma partição para procurar usando o decorador de partição, por exemplo, $20180224.

Como o comando bq head não cria um trabalho de consulta, os comandos bq head não aparecem no seu histórico de consultas, e você não é cobrado por eles.

Para navegar por um subconjunto de campos na tabela, incluindo campos aninhados e repetidos, use a sinalização --selected_fields e insira os campos como uma lista separada por vírgulas.

Para especificar o número de linhas a serem ignoradas antes de exibir os dados da tabela, use a sinalização --start_row=integer (ou o atalho -s). O valor padrão é 0. Recupere o número de linhas em uma tabela usando o comando bq show para recuperar as informações de uma tabela.

Se a tabela em que você está navegando estiver em um projeto diferente do padrão, adicione o ID do projeto ao comando no seguinte formato: project_id:dataset.table.

bq head \
--max_rows integer1 \
--start_row integer2 \
--selected_fields "fields" \
project_id:dataset.table$partition

Onde:

  • integer1 é o número de linhas a ser exibido;
  • integer2 é o número de linhas a ser ignorado antes da exibição dos dados;
  • fields é uma lista de campos separada por vírgulas;
  • project_id é o ID do projeto.
  • dataset é o nome do conjunto de dados que contém a tabela;
  • table é o nome da tabela a ser navegada;
  • $partition é o decorador de partições.

Exemplos:

Digite o seguinte comando para listar todos os campos nas primeiras 10 linhas em mydataset.mytable na partição "2018-02-24". mydataset está no projeto padrão.

bq head --max_rows=10 'mydataset.mytable$20180224'

Digite o seguinte comando para listar todos os campos nas primeiras 100 linhas em mydataset.mytable na partição "2016-09-01". A saída é gravada no formato JSON. Este exemplo especifica myotherproject como o projeto, em vez de usar o projeto padrão.

bq head --format=prettyjson 'myotherproject:mydataset.mytable$20160901'

Digite o seguinte comando para exibir apenas field1 e field2 em mydataset.mytable na partição "2016-09-01". O comando usa a sinalização --start_row para pular para a linha 100. mydataset.mytable está em seu projeto padrão.

bq head \
--format=prettyjson \
--start_row 100 \
--selected_fields "field1,field2" \
'mydataset.mytable$20160901'

API

Procure dados em uma tabela chamando tabledata.list. Especifique o nome da tabela e o decorador de partições no parâmetro tableId.

Configure esses parâmetros opcionais para controlar a saída:

  • maxResults: número máximo de resultados a serem retornados.
  • selectedFields: lista de campos separados por vírgulas a ser retornada (se não especificado, todos os campos serão retornados).
  • startIndex: Índice baseado em zero da primeira linha a ser lida.

Os valores são retornados empacotados em um objeto JSON para análise, conforme descrito na tabledata.list documentação de referência.

Como consultar dados em tabelas particionadas

Depois de carregar dados no BigQuery, você pode consultá-los nas tabelas. O BigQuery aceita dois tipos de consultas:

Por padrão, o BigQuery executa consultas interativas, o que significa que elas são executadas assim que possível.

O BigQuery também oferece consultas em lote. O BigQuery coloca em fila cada consulta em lote em seu nome e inicia a consulta assim que os recursos inativos tornam-se disponíveis, normalmente em alguns minutos.

Execute consultas interativas e em lote usando:

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

Como anexar e substituir dados de tabelas particionadas

É possível substituir os dados de tabelas particionadas usando uma operação de carregamento ou consulta. Além disso, para anexar mais dados a uma tabela particionada existente, execute uma operação de anexação de carregamento ou anexe os resultados da consulta. As operações nesta seção são compatíveis com tabelas particionadas por dia e hora.

Permissões necessárias

Para substituir uma partição atual ou anexar dados a uma partição atual, você precisa ter, no mínimo, as seguintes permissões:

  • bigquery.tables.create
  • bigquery.tables.updateData
  • bigquery.jobs.create

Permissões adicionais, como bigquery.tables.getData, podem ser necessárias para acessar os dados que você está usando para anexar ou substituir.

Os seguintes papéis predefinidos do Cloud IAM incluem permissões bigquery.tables.updateData e bigquery.tables.create:

  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery.admin

Os papéis predefinidos do Cloud IAM abaixo incluem permissões bigquery.jobs.create:

  • bigquery.user
  • bigquery.jobUser
  • bigquery.admin

Além disso, se um usuário tiver permissões bigquery.datasets.create ao criar um conjunto de dados, será concedido o acesso bigquery.dataOwner. O acesso bigquery.dataOwner permite que o usuário anexe e substitua tabelas e partições no conjunto de dados.

Para mais informações sobre os papéis e permissões do Cloud IAM no BigQuery, consulte Papéis e permissões predefinidos.

Como usar um job de carga

É possível anexar ou substituir partições usando o comando bq load ou chamando o método jobs.insert e configurando um job load. No Console do Cloud e na IU clássica da Web do BigQuery, não é possível anexar ou substituir uma partição em um job de carregamento.

Ao anexar ou substituir dados em uma partição específica usando um job de carga, observe os seguintes detalhes:

  • Ao carregar dados do Cloud Storage, o intervalo precisa estar no mesmo local que o conjunto de dados do BigQuery.
  • Os dados que estão sendo carregados na partição precisam estar em conformidade com o esquema de particionamento da tabela. Todas as linhas escritas na partição precisam ter valores que se enquadrem na data dela.
  • Como as partições de uma tabela particionada compartilham o esquema de tabela, a substituição de dados em uma partição não substituirá o esquema da tabela. Em vez disso, o esquema dos novos dados precisa ser compatível com o esquema da tabela. Para informações sobre a atualização do esquema da tabela no job de carga, consulte Como gerenciar esquemas de tabelas.
  • Ao anexar dados em uma tabela particionada por tempo de processamento, a partição atual será utilizada se você não especificar um decorador de partição.

Para anexar ou substituir dados de tabelas particionadas usando um job de carga, especifique uma tabela de destino e um decorador de partição. Além disso, defina a sinalização de disposição de gravação como:

Opção da linha de comando Opção da API Descrição
--noreplace WRITE_APPEND Acrescenta os dados a uma partição existente. Se nenhuma opção de disposição de gravação for especificada, a ação padrão será anexar os dados à partição.
--replace WRITE_TRUNCATE Substitui (redefine) uma partição.

Um decorador de partição representa uma data específica e assume o formato:

$YYYYMMDD

Por exemplo, o comando a seguir substitui os dados na partição inteira pela data 1º de janeiro de 2016 (20160101) em uma tabela particionada denominada mydataset.table1. Os dados JSON são carregados de um intervalo do Cloud Storage.

bq load \
--replace \
--source_format=NEWLINE_DELIMITED_JSON \
'mydataset.table1$20160101' \
gs://mybucket/myfile.json

Como usar um job de consulta

É possível anexar ou substituir partições usando o comando bq query ou chamando o método jobs.insert e configurando um job query. No Console do Cloud e na IU clássica da Web do BigQuery, não é possível anexar ou substituir uma partição em um job de consulta.

Ao usar um job de consulta para anexar ou substituir uma partição, observe os seguintes detalhes:

  • As tabelas que você está consultando precisam estar no mesmo local que a tabela que você está anexando ou substituindo.
  • Ao anexar ou substituir uma partição em uma tabela particionada por tempo de processamento, é possível usar a sintaxe do SQL legado ou do SQL padrão.
  • Ao anexar ou substituir uma partição em uma tabela particionada, sua consulta precisa usar a sintaxe do SQL padrão. Atualmente, o SQL legado não é compatível com a consulta a tabelas particionadas, nem com a gravação de resultados da consulta em tabelas particionadas.
  • Ao gravar os resultados da consulta em uma partição, os dados sendo gravados nela precisam estar em conformidade com o esquema de particionamento da tabela. Todas as linhas escritas na partição precisam ter valores que se enquadrem na data dela.
  • Ao anexar dados em uma tabela particionada por tempo de processamento, a partição atual será utilizada se você não especificar um decorador de partição.

Para anexar ou substituir uma partição usando os resultados da consulta, especifique uma tabela de destino com um decorador de partição. Além disso, defina a disposição de gravação como:

Opção da linha de comando Opção da API Descrição
--append_table WRITE_APPEND Anexa os resultados da consulta a uma partição existente.
--replace WRITE_TRUNCATE Substitui (redefine) uma partição usando os resultados da consulta.

Por exemplo, o comando a seguir reapresenta os dados da partição de 1º de março de 2016 (20160301) da table1 usando os resultados da consulta.

bq query \
--use_legacy_sql=false \
--replace \
--destination_table 'mydataset.table1$20160301' \
'SELECT
  column1,
  column2
FROM
  mydataset.mytable'

Quando a tabela de destino existe e não está particionada, o erro a seguir é retornado: BigQuery error in query operation: Error processing job 'project_id job_id' Incompatible table partitioning specification. Expects partitioning specification interval (type:day), but input partitioning specification is none`.

Para mais informações sobre como usar os resultados da consulta para anexar ou substituir dados, consulte Como gravar resultados da consulta.

Como modificar dados de tabelas particionadas usando declarações DML

Modifique os dados em uma tabela particionada usando declarações DML no dialeto SQL padrão. Com elas, é possível executar atualizações, inserções e exclusões de linhas em massa. Veja exemplos do uso da DML com tabelas particionadas em Como atualizar dados de tabelas particionadas usando instruções DML.

O dialeto SQL legado não é compatível com as instruções DML. Para atualizar ou excluir dados usando o SQL legado, é preciso excluir a tabela particionada e recriá-la com novos dados. Como alternativa, escreva uma consulta que modifique os dados e grave os resultados em uma nova tabela particionada.

Como copiar dados de tabelas particionadas

É possível copiar uma tabela particionada das seguintes formas:

  • Use o Console do Cloud ou a IU da Web clássica do BigQuery
  • Use o comando bq cp da ferramenta de linha de comando bq.
  • chame o método de API jobs.insert e configure um job de cópia;
  • usando as bibliotecas de cliente.

Para mais informações sobre a cópia de tabelas, consulte Como copiar uma tabela.

É possível copiar uma ou mais partições usando o comando bq cp da ferramenta de linha de comando bq ou chamando o método jobs.insert da API e configurando um job copy. No momento, não é possível copiar partições no Console do Cloud ou na IU da Web clássica do BigQuery.

Veja mais informações em Como copiar partições.

Como fazer streaming de dados para tabelas particionadas

Para fazer o streaming de dados em uma partição específica, use o decorador de partição ao especificar o tableId da tabela de destino. Por exemplo, o comando a seguir faz o streaming de uma única linha para uma partição de 1º de janeiro de 2017 ($20170101) em uma tabela particionada denominada mydataset.mytable:

echo '{"a":1, "b":2}' | bq insert 'mydataset.mytable$20170101'

Esse comando é usado para demonstrar o uso do decorador de partição. O comando bq insert destina-se apenas a testes. Para fazer o streaming de dados para o BigQuery, use o método tabledata.insertAll da API. Para mais informações sobre streaming de dados para partições, consulte Como fazer streaming para tabelas particionadas.

É possível fazer streaming para uma tabela particionada por dia usando um decorador de partição para partições dos últimos 30 dias e nos próximos 5 dias em relação à data atual, com base na hora UTC atual. Para gravar nas partições em datas fora dos limites permitidos, use jobs de carga ou de consulta. O streaming que usa um decorador de partição não é compatível com tabelas particionadas por hora. Portanto, é preciso transmitir para a tabela base.

Quando você especifica uma tabela particionada por tempo como destino ao fazer streaming de dados, cada partição tem um buffer de streaming. O buffer de streaming é mantido quando você executa um job de carregamento, consulta ou cópia que substitui uma partição com a definição da propriedade writeDisposition como WRITE_TRUNCATE. Se quiser remover o buffer de streaming, verifique se ele está vazio. Basta chamar tables.get na partição. Para detalhes sobre o buffer de streaming, consulte Como comparar entre opções de particionamento.

Como exportar dados de tabela

Exportar todos os dados de uma tabela particionada é o mesmo processo que exportar dados de uma tabela não particionada. Para mais informações, consulte Como exportar dados de tabelas. Para exportar dados de uma partição individual, anexe o decorador de partição $date ao nome da tabela. Por exemplo, mytable$20160201.

Também é possível exportar os dados das partições __NULL__ e __UNPARTITIONED__ anexando os nomes das partições ao nome da tabela. Por exemplo, mytable$__NULL__ ou mytable$__UNPARTITIONED__.

É possível exportar dados de tabelas nos formatos CSV, JSON ou Avro. Atualmente, é preciso exportar os dados para um intervalo do Cloud Storage. Não há como exportar para sua máquina local, porém, é possível fazer o download de resultados de consulta e salvá-los usando o Console do Cloud ou a IU clássica do BigQuery na Web.

Próximas etapas

Para saber mais sobre como trabalhar com tabelas particionadas, consulte: