Como gerenciar dados de tabelas particionadas

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:

Como navegar pelos dados da tabela

Para navegar pelos dados da tabela particionada, siga este métodos:

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

Permissões exigidas

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

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

Além disso, quando um usuário com permissões bigquery.datasets.create cria um conjunto de dados, recebe o acesso bigquery.dataOwner a ele. Com o acesso bigquery.dataOwner, o usuário consegue navegar pelos dados das tabelas e partições no conjunto de dados.

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

Como navegar pelos dados de tabelas particionadas

Para navegar pelos dados da tabela particionada:

Console

  1. No painel de navegação do Console do GCP, clique em um conjunto de dados para listar as respectivas 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 GCP.

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 Details e anote o valor em Number of Rows. Talvez esse valor seja necessário para controlar o ponto de partida de seus resultados usando a CLI ou a API.

  4. Clique em Preview. 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

Emita 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 especificada, o padrão será 100. Especifique uma partição para navegar 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 histórico da consulta e você não será 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

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:

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

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

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

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

Insira o comando a seguir 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 "state_number" \
'mydataset.mycolumntable2$20160901'

API

Chame tabledata.list para navegar pelos dados de uma tabela. Especifique o nome da tabela e o decorador de partição no parâmetro tableId.

Configure esses parâmetros opcionais para controlar o resultado:

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

Os valores são retornados encapsulados em um objeto JSON que precisa ser analisado, conforme descrito na documentação de referência de tabledata.list.

Como consultar dados de 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.

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

Outras permissões, como bigquery.tables.getData, podem ser necessárias para acessar os dados que você está gravando na tabela.

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

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

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

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

Além disso, quando um usuário com permissões bigquery.datasets.create cria um conjunto de dados, recebe o acesso bigquery.dataOwner a ele. Com o acesso bigquery.dataOwner, o usuário consegue anexar e substituir tabelas e partições no conjunto de dados.

Para mais informações sobre os papéis e as permissões do Cloud IAM no BigQuery, consulte Papéis e permissões 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 GCP 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.
  • É necessário que os dados que você está carregando estejam em conformidade com o esquema de particionamento. 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 CLI 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 GCP 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 em que está anexando ou que está 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 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 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

Para copiar uma tabela particionada, siga estes métodos:

  • Use o Console do GCP ou a IU clássica da Web do BigQuery.
  • 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 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 jobs.insert da API e configurando um job copy. No momento, não é possível copiar partições no Console do GCP 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 a uma partição referente à data 1º de janeiro de 2017 ($20170101) para 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.

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

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á suporte para exportar para sua máquina local. No entanto, é possível fazer o download e salvar os resultados da consulta usando o Console do GCP ou a IU da Web clássica do BigQuery.

A seguir

Para saber mais sobre como trabalhar com tabelas particionadas, consulte estes tópicos:

Esta página foi útil? Conte sua opinião sobre:

Enviar comentários sobre…

Precisa de ajuda? Acesse nossa página de suporte.