Como gerenciar dados de tabelas particionadas

Neste documento, descrevemos como 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

É possível 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 para formatos de dados compatíveis ou especifique o esquema.

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

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 do BigQuery para Java (em inglês).

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 em tabelas

Para procurar dados em tabelas particionadas:

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

Permissões exigidas

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

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

Além disso, se um usuário tiver as permissões bigquery.datasets.create, ao criar um conjunto de dados, ele receberá 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 papéis e permissões do Cloud IAM no BigQuery, consulte Permissões e papéis 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 de seus resultados usando a CLI ou a API.

    Número de linhas

  5. Clique na guia Visualizar. Um conjunto de dados de amostra é exibido. Observe que 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. Fazer 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 CLI ou a API.

  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

CLI

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 na qual procurar usando o decorador de partição, por exemplo, $20180224.

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

Para procurar em 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
    

Em que:

  • 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 código 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ção.

Exemplos:

Insira 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
    

Insira o seguinte comando para listar todos os campos nas primeiras 100 linhas em mydataset.mytable na partição "2016-09-01". mydataset está em myotherproject, não no projeto padrão.

bq head --format=prettyjson 'mydataset.mycolumntable2$20160901'
    

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

bq head \
    --format=prettyjson \
    --start_row 100 \
    --selected_fields "state_number" \
    'mydataset.mycolumntable2$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 documentação de referência de tabledata.list.

Como consultar dados em tabelas particionadas

Depois de carregar dados no BigQuery, consulte-os 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 e inicia a consulta assim que os recursos inativos ficam disponíveis, normalmente em alguns minutos.

Execute consultas interativas e em lote usando:

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

Como anexar e substituir dados de tabelas particionadas

É possível substituir dados de tabelas particionadas usando uma operação de carregamento ou consulta. Além disso, para anexar mais dados a uma tabela particionada atual, execute uma operação load-append ou anexe os resultados da consulta.

Permissões exigidas

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 extras, 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 seguintes papéis predefinidos do Cloud IAM incluem permissões bigquery.jobs.create:

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

Além disso, se um usuário tiver as permissões bigquery.datasets.create, ao criar um conjunto de dados, ele receberá 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 papéis e permissões do Cloud IAM no BigQuery, consulte Permissões e papéis predefinidos.

Como usar um job de carregamento

É 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 do BigQuery na Web, 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 carregamento, observe os seguintes aspectos:

  • Ao carregar dados do Cloud Storage, o bucket 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 carregamento, 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 carregamento, 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 CLI Opção da API Descrição
--noreplace WRITE_APPEND Acrescenta os dados a uma partição atual. 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 bucket 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 do BigQuery na Web, 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 SQL legado ou SQL padrão.
  • Quando você anexa ou substitui uma partição em uma tabela particionada, sua consulta precisa usar a sintaxe 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.
  • Na gravação dos resultados da consulta em uma partição, os dados que serão gravados 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 CLI 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 redefine 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 instruções DML

Modifique os dados em uma tabela particionada usando instruções DML no dialeto SQL padrão. Com elas, é possível executar atualizações, inserções e exclusões de linhas em massa. Consulte 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 clássica do BigQuery na Web;
  • use o comando bq cp da ferramenta de linha de comando;
  • chame o método de API jobs.insert e configure um job de cópia;
  • use 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 ou chamando o método de API jobs.insert e configurando um job copy. No momento, não é possível copiar partições no Console do Cloud ou na IU clássica do BigQuery na Web.

Consulte Como copiar partições para mais informações.

Como fazer streaming de dados para tabelas particionadas

Para fazer streaming de dados em uma partição específica, use o decorador de partição ao especificar o tableId da tabela de destino do streaming. Por exemplo, o comando a seguir faz 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 streaming de dados para o BigQuery, use o método de API tabledata.insertAll. Para mais informações sobre streaming de dados para partições, consulte Como fazer streaming para tabelas particionadas.

Ao fazer streaming usando um decorador de partição, é possível fazer isso 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 carregamento ou de consulta.

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 chamando tables.get na partição.

Como exportar dados de tabelas

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 bucket do Cloud Storage. Não há como exportá-los 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.

A seguir

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