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:

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 da API tabledata.list.

Permissões exigidas

No nível do conjunto de dados, você precisa ter o acesso de READER ao conjunto de dados da tabela particionada para navegar pelos dados dela.

Em vez de usar permissões no nível do conjunto de dados, é possível utilizar um papel do IAM que inclua permissões bigquery.tables.getData. Todos os papéis predefinidos do IAM para envolvidos no projeto incluem permissões bigquery.tables.getData, exceto bigquery.user, bigquery.jobUser e bigquery.metadataViewer.

Além disso, como o papel bigquery.user tem permissões bigquery.datasets.create, um usuário atribuído ao papel bigquery.user lê os dados em qualquer tabela de qualquer conjunto de dados criado pelo usuário. Quando um usuário com papel bigquery.user cria um conjunto de dados, recebe acesso de OWNER ao conjunto se nenhum outro proprietário está definido. Com o acesso OWNER, o usuário tem controle total sobre o conjunto de dados e todas as tabelas nele.

Para mais informações sobre os papéis e as permissões do IAM no BigQuery, consulte Controle de acesso. Para mais informações sobre os papéis para conjuntos de dados, consulte Papéis primários para conjuntos de dados.

Como navegar nos dados de tabelas particionadas

Para navegar pelos dados da tabela particionada:

Console

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

  2. Clique em uma tabela particionada na lista. Não é possível visualizar partições individuais usando a IU da Web do BigQuery.

  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 API.

    Número de linhas

  5. Clique na guia Visualizar. Um conjunto de dados de amostra é exibido.

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 na lista. Não é possível visualizar partições individuais usando a IU da Web do BigQuery.

  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 de seus resultados usando CLI ou API.

  4. Clique em Visualizar. Um conjunto de dados de amostra é exibido.

    Visualização da tabela

Linha de comando

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. Caso --max_rows não seja especificado, o padrão será 100. Especifique uma partição para navegar usando o decorador de partições, 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 procurar 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 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 coletar informações.

Caso a tabela que você procura conste em um projeto diferente do projeto padrão, adicione o código 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ções.

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'

Digite 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ções no parâmetro tableId.

Configure esses parâmetros opcionais para controlar o resultado:

  • maxResults: número máximo de resultados que serão retornados
  • selectedFields: lista de campos separada por vírgulas a ser retornada (quando não especificada, todos os campos serão retornados)
  • startIndex: índice baseado em zero da primeira linha a ser lida

Os valores são retornados incorporados a um objeto JSON para análise, conforme descrito na documentação de referência 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.

É possível executar 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 ela, é preciso ter acesso de WRITER no nível do conjunto de dados ou receber um papel de IAM no nível do projeto que inclua permissões bigquery.tables.updateData. Os seguintes papéis do IAM predefinidos para envolvidos no projeto incluem permissões bigquery.tables.updateData:

Além disso, como a função bigquery.user tem permissões bigquery.datasets.create, um usuário atribuído ao papel bigquery.user pode substituir ou anexar dados em qualquer partição de qualquer tabela criada por ele no conjunto de dados. Quando um usuário atribuído ao papel bigquery.user cria um conjunto de dados, ele receberá o acesso OWNER a esse conjunto se nenhum outro proprietário for especificado. O acesso OWNER a um conjunto de dados permite que o usuário tenha controle total sobre ele, sobre as tabelas contidas nele e sobre todas as partições da tabela.

Também são necessárias as permissões bigquery.jobs.create para executar jobs de carregamento ou de consulta. Os seguintes papéis de IAM predefinidos em nível de projeto incluem permissões bigquery.jobs.create:

Para mais informações sobre os papéis e as permissões do IAM no BigQuery, consulte Controle de acesso. Para mais informações sobre os papéis para conjuntos de dados, consulte Papéis primários para conjuntos de dados.

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 de carregamento. Não há suporte do Console do GCP e da IU da Web clássica do BigQuery para 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 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 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ções.

Para anexar ou substituir dados de tabelas particionadas usando um job de carregamento, especifique uma tabela de destino e um decorador de partições. 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ções representa uma data específica e assume o formato:

$YYYYMMDD

Por exemplo, o comando a seguir substitui os dados na partição inteira para a data de 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. O intervalo do Cloud Storage e o conjunto de dados do BigQuery foram criados na região asia-northeast1.

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

Para mais informações sobre a anexação ou substituição de dados usando um job de carregamento, consulte a documentação do formato de seus dados de origem:

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 de consulta. Não há suporte do Console do GCP e da IU da Web clássica do BigQuery para 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ções.

Para anexar ou substituir uma partição usando os resultados da consulta, especifique uma tabela de destino com um decorador de partições. 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. A tabela de destino e a tabela consultada estão no local multirregional US:

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

Se a tabela de destino existe, e ela não está particionada, o erro a seguir é retornado: BigQuery error in query operation: Error processing job '[PROJECT][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 da API jobs.insert e configure um job de cópia.

Para mais informações sobre como copiar 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 da API jobs.insert e configurando um job de cópia. 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ções 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 para a data de 1º de janeiro de 2017 ($20170101) em uma tabela particionada chamada mydataset.mytable:

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

Esse comando é usado para demonstrar o uso do decorador de partições. 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 como fazer streaming de dados para partições, consulte Como fazer o streaming de dados 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 terá um buffer de streaming. O buffer de streaming é mantido quando você executa uma carga, consulta ou job que substitui uma partição definindo a 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 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ções $[DATE] ao nome da tabela. Por exemplo: mytable$20160201.

Também é possível exportar os dados de 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.