Como criar e usar tabelas particionadas por tempo de ingestão

Neste documento, você verá como criar e usar tabelas particionadas por tempo de ingestão no BigQuery. Consulte Como criar e usar tabelas particionadas por tempo baseadas em colunas para mais informações. Para mais informações sobre tabelas particionadas por intervalo de número inteiro, consulte Como criar e usar tabelas particionadas por intervalo de números inteiros.

Depois de criar uma tabela particionada por tempo de ingestão, será possível:

controlar o acesso aos dados de sua tabela;
receber informações sobre suas tabelas particionadas;
listar as tabelas particionadas em um conjunto de dados;
receber metadados das tabelas particionadas usando metatabelas.

Para mais informações sobre como gerenciar tabelas particionadas, incluindo atualização de propriedades, cópia e exclusão de tabelas, consulte Como gerenciar tabelas particionadas.

Como criar tabelas particionadas por tempo de ingestão

Você pode criar uma tabela particionada por tempo de ingestão no BigQuery:

usando uma instrução DDL CREATE TABLE com partition_expression;
Manualmente, usando o Console do Cloud
Usando o comando bq mk da ferramenta de linha de comando bq
programaticamente, chamando o método da API tables.insert;
usando as bibliotecas de cliente;
dos resultados da consulta;
ao carregar dados;
ao converter tabelas com dados fragmentados em tabelas particionadas.

Nomenclatura de tabelas

Ao criar uma tabela no BigQuery, o nome dela precisa ser exclusivo para cada conjunto de dados. Esse nome pode:

conter até 1.024 caracteres;
conter caracteres Unicode na categoria L (letra), M (marca), N (número), Pc (conector, inclusive sublinhado), Pd (travessão), Zs (espaço). Para mais informações, consulte Categoria geral.

Por exemplo, os nomes de tabela a seguir são válidos: table-01, ग्राहक, 00_お客様, étudiant.

Alguns nomes de tabelas e prefixos de nome de tabela são reservados. Se você receber um erro informando que o nome ou o prefixo da tabela está reservado, selecione um nome diferente e tente novamente.

Permissões necessárias

Para criar uma tabela, você precisa ter pelo menos as seguintes permissões:

bigquery.tables.create para criar a tabela
bigquery.tables.updateData para gravar dados na tabela usando um job de carregamento, de consulta ou de cópia.
bigquery.jobs.create para executar jobs de consulta, de carregamento ou de cópia que gravem os dados na tabela

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

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

bigquery.dataEditor
bigquery.dataOwner
bigquery.admin

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

bigquery.user
bigquery.jobUser
bigquery.admin

Além disso, quando um usuário tem permissões bigquery.datasets.create e cria um conjunto de dados, ele recebe o acesso bigquery.dataOwner ao conjunto. O acesso bigquery.dataOwner permite que o usuário crie e atualize tabelas no conjunto de dados.

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

Como criar uma tabela particionada por tempo de ingestão vazia com definição de esquema

Ao criar uma tabela particionada por tempo vazia com uma definição de esquema, é possível:

Insira o esquema usando o Console do Cloud
Fornecer o esquema in-line usando a ferramenta de linha de comando bq
usar a ferramenta de linha de comando bq para enviar um arquivo de esquema JSON;
fornecer o esquema em um recurso de tabela ao chamar o método tables.insert da API.

Para mais informações sobre como especificar um esquema de tabela, consulte Como especificar um esquema.

Após a criação da tabela particionada por tempo, é possível:

carregar dados nela;
gravar os resultados da consulta nela;
copiar dados para ela.

Para criar uma tabela particionada por tempo de processamento vazia com definição de esquema:

Console

No painel Explorer, expanda o projeto e selecione um conjunto de dados.

Observação: a experiência padrão é a versão de visualização do Console do Cloud. Se você clicou em Ocultar recursos da versão de visualização para acessar a versão de disponibilidade geral do Console do Cloud, faça o seguinte: na seção Recursos do painel de navegação, expanda o projeto e selecione um conjunto de dados.
Na página Criar tabela, na seção Origem, selecione Criar tabela vazia.
Siga estas etapas na página Criar tabela, na seção Destino:
- Em Nome do conjunto de dados, escolha o conjunto apropriado.
- No campo Nome da tabela, insira o nome da tabela que você está criando no BigQuery.
- Verifique se Tipo de tabela está definido como Tabela nativa.
Na seção Esquema, insira a definição do esquema. Insira as informações do esquema manualmente realizando uma das seguintes ações:
- Ative Editar como texto e insira o esquema da tabela como uma matriz JSON.
  Observação: é possível ver o esquema de uma tabela atual no formato JSON ao digitar o seguinte comando na ferramenta de linha de comando bq: bq show --format=prettyjson dataset.table.
- Clique em Adicionar campo e insira as informações do esquema.
Na seção Configurações de particionamento e cluster:
- Em Particionamento, clique em Sem particionamento e selecione Partição por tempo de ingestão.
Opcional: em Filtro de particionamento, clique na caixa Exigir filtro de particionamento para solicitar que os usuários incluam uma cláusula WHERE que especifique as partições a serem consultadas. Exigir um filtro de partição pode reduzir os custos e melhorar o desempenho. Para mais informações, veja Como consultar tabelas particionadas.
Opcional: clique em Opções avançadas e, em Criptografia, clique em Chave gerenciada pelo cliente para usar uma chave do Cloud Key Management Service. Se você escolher a configuração Chave gerenciada pelo Google, o BigQuery criptografará os dados em repouso.
Clique em Criar tabela.

Após a criação da tabela, é possível atualizar a expiração, a descrição e os rótulos dela. No entanto, não é possível adicionar uma expiração de partição depois de criar uma tabela com o Console do Cloud.

bq

Use o comando mk com a sinalização --table (ou o atalho -t) e a sinalização -- time_partitioning_type, definida como DAY, HOUR, MONTH ou YEAR conforme apropriado para o tipo de particionamento. Forneça a definição de esquema da tabela in-line ou por meio de um arquivo de esquema JSON. Os parâmetros opcionais incluem --expiration, --description, --time_partitioning_expiration, --destination_kms_key, e --label. Se você estiver criando uma tabela em um projeto diferente do projeto padrão, adicione o ID do projeto ao conjunto de dados no seguinte formato: project_id:dataset.

--destination_kms_key não é demonstrado aqui. Para mais informações sobre o uso dessa sinalização, consulte Como proteger dados com chaves do Cloud KMS.

Digite o comando abaixo para criar uma tabela particionada por tempo de ingestão vazia com uma definição de esquema:

bq mk --table \
--expiration integer1 \
--time_partitioning_type=unit_time \
--time_partitioning_expiration integer2 \
--description "description" \
-label key:value,key:value \
project_id:dataset.table \
schema

Em que:

integer1 é a vida útil padrão (em segundos) da tabela. O valor mínimo é de 3.600 segundos (uma hora). O prazo de expiração é a soma do horário UTC atual com o valor inteiro. Se você definir o prazo de expiração da tabela ao criar uma tabela particionada, a configuração de expiração da tabela padrão do conjunto de dados será ignorada. Definir esse valor exclui a tabela e todas as partições após o tempo especificado.
unit_time é DAY, HOUR, MONTH, ou YEAR, dependendo do tipo de particionamento. O padrão é DAY quando time_partitioning_type não está especificado.
integer2 é a vida útil padrão (em segundos) das partições da tabela. Não há valor mínimo. O prazo de validade o é avaliado para a data da partição acrescida deste valor. A expiração da partição é independente da expiração da tabela, mas não a substitui. Se você definir uma data de validade da partição mais longa do que a data de validade da tabela, a data da tabela terá precedência.
description é uma descrição da tabela entre aspas.
key:value é o par de chave-valor que representa um rótulo. É possível inserir vários rótulos usando uma lista separada por vírgulas;
project_id é o ID do projeto.
dataset é um conjunto de dados no projeto.
table é o nome da tabela particionada por tempo que você está criando.
schema é uma definição de esquema in-line no formato column:data_type,column:data_type ou o caminho para o arquivo de esquema JSON na máquina local.

Quando você especifica o esquema na linha de comando, não é possível incluir um tipo RECORD (STRUCT), uma descrição de coluna nem especificar o modo da coluna. Todos os modos assumem NULLABLE como padrão. Para incluir descrições, modos e tipos RECORD, forneça um arquivo de esquema JSON.

Exemplos:

Digite o seguinte comando para criar uma tabela particionada por tempo de ingestão chamada mytable no mydataset no seu projeto padrão. O tipo de particionamento é definido como DAY, a expiração de particionamento é definida para 259.200 segundos (3 dias), a expiração da tabela é definida para 2.592.000 (1 mês de 30 dias), a descrição é definida como This is my time partitioned table e o rótulo, como organization:development. O comando usa o atalho -t em vez de --table. O esquema é especificado in-line como: qtr:STRING,sales:FLOAT,year:STRING

bq mk -t \
--expiration 2592000 \
--time_partitioning_type=DAY \
--time_partitioning_expiration 259200 \
--description "This is my time partitioned table" \
--label organization:development \
mydataset.mytable \
qtr:STRING,sales:FLOAT,year:STRING

Digite o comando a seguir para criar uma tabela particionada por tempo chamada mytable em myotherproject, não o projeto padrão. O tipo de particionamento é definido como DAY, a expiração é definida para 259200 segundos (3 dias), a descrição é definida como This is my time partitioned table e o rótulo, como organization:development. O comando usa o atalho -t em vez de --table. Esse comando não especifica uma validade de tabela. Se o conjunto de dados tiver uma expiração de tabela padrão, ela será aplicada. Se o conjunto de dados não tiver uma validade de tabela padrão, a tabela nunca irá expirar, mas as partições expirarão em três dias. O caminho para o arquivo de esquema é /tmp/myschema.json.

bq mk -t \
--time_partitioning_type=DAY \
--time_partitioning_expiration 259200 \
--description "This is my time partitioned table" \
--label organization:development \
myotherproject:mydataset.mytable \
/tmp/myschema.json

Após a criação da tabela, é possível atualizar a validade da tabela, a validade da partição, a descrição e os rótulos da tabela particionada por tempo de ingestão.

API

Chame o método tables.insert com um recurso de tabela definido que especifique a property timePartitioning e a property schema.

Como criar uma tabela particionada por tempo de ingestão com base em um resultado de consulta

Para criar uma tabela particionada de um resultado de consulta, grave os resultados em uma nova tabela de destino. Para criar uma nova tabela particionada, consulte uma tabela particionada ou uma não particionada. Não é possível alterar uma tabela atual para uma particionada usando os resultados da consulta.

Para mais informações sobre como consultar tabelas particionadas por tempo, acesse Como consultar tabelas particionadas.

Console

Não é possível especificar as opções de particionamento de uma tabela de destino ao consultar dados usando o Console do Cloud.

bq

Insira o comando bq query e especifique a sinalização --destination_table para criar uma tabela permanente com base nos resultados da consulta. Especifique a sinalização --time_partitioning_type para criar uma tabela de destino particionada por tempo de ingestão. DAY, HOUR, MONTH e YEAR são os valores compatíveis com --time_partitioning_type, dependendo da granularidade da partição.

Especifique a sinalização use_legacy_sql=false para usar a sintaxe SQL padrão. Para gravar os resultados da consulta em uma tabela que não esteja no projeto padrão, adicione o ID do projeto ao nome do conjunto de dados no seguinte formato: project_id:dataset.

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

Digite o comando a seguir para criar uma nova tabela de destino particionada por tempo de ingestão com base em um resultado de consulta:

bq --location=location query \
--destination_table project_id:dataset.table \
--time_partitioning_type=unit_time \
--use_legacy_sql=false \
'query'

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 que conterá a nova tabela particionada por tempo.
table é o nome da tabela particionada por tempo que você está criando com os resultados da consulta.
- unit_time é DAY, HOUR, MONTH, ou YEAR, dependendo do tipo de particionamento. O padrão é DAY quando time_partitioning_type não é especificado.
query é uma consulta na sintaxe SQL padrão.

Exemplos:

Digite o seguinte comando para gravar os resultados da consulta em uma tabela de destino particionada por tempo de processamento chamada mytable em mydataset. O conjunto de dados está no projeto padrão. A consulta recupera dados de uma tabela não particionada: o conjunto de dados públicos USA Name Data.

bq query \
--destination_table mydataset.mytable \
--time_partitioning_type=DAY \
--use_legacy_sql=false \
'SELECT
   name,
   number
 FROM
   `bigquery-public-data`.usa_names.usa_1910_current
 WHERE
   gender = "M"
 ORDER BY
   number DESC'

Digite o comando a seguir para gravar os resultados da consulta em uma tabela de destino particionada por tempo de ingestão chamada mytable em mydataset, particionada pela data de ingestão de dados. O conjunto de dados está em myotherproject, e não no projeto padrão. A consulta recupera dados de uma tabela não particionada: o conjunto de dados públicos USA Name Data.

bq query \
--destination_table myotherproject:mydataset.mytable \
--time_partitioning_type=DAY \
--use_legacy_sql=false \
'SELECT
   name,
   number
 FROM
   `bigquery-public-data`.usa_names.usa_1910_current
 WHERE
   gender = "M"
 ORDER BY
   number DESC'

API

Para salvar os resultados da consulta em uma tabela particionada por tempo de ingestão permanente, chame o método jobs.insert, configure um job query e inclua um valor para as propriedades destinationTable e timePartitioning.

Como criar uma tabela particionada por tempo de processamento ao carregar dados

É possível criar uma tabela particionada por tempo de processamento especificando opções de particionamento ao carregar dados em uma nova tabela. Não é necessário criar uma tabela particionada vazia antes de carregar dados nela. Crie a tabela particionada e carregue seus dados ao mesmo tempo.

Ao carregar dados no BigQuery, forneça o esquema da tabela ou, para formatos de dados compatíveis, usar a detecção automática de esquema.

Os decoradores de partição permitem que você carregue os dados em uma partição específica. Para ajustar os fusos horários, use um decorador para carregar os dados em uma partição com base no fuso horário da sua preferência. Por exemplo, se você estiver no Horário Padrão do Pacífico (PST, na sigla em inglês), carregue todos os dados gerados em 1º de maio de 2016 PST na partição dessa data usando o decorador de partição correspondente:

table_name$20160501

Para mais informações sobre como carregar dados, consulte Introdução ao carregamento de dados no BigQuery.

Como converter tabelas fragmentadas por data em tabelas particionadas por tempo de ingestão

Se você já criou tabelas fragmentadas por data, é possível converter todo o conjunto de tabelas relacionadas em uma única tabela particionada por tempo de ingestão usando o comando partition na ferramenta de linha de comando bq. As tabelas fragmentadas por data precisam usar a convenção de nomenclatura a seguir: table_YYYYMMDD. Por exemplo: mytable_20160101, ... , mytable_20160331.

Os parâmetros opcionais incluem --time_partitioning_expiration, --location e --time_partitioning_type. Se não for especificado, --time_partitioning_type assumirá DAY como padrão. Se as tabelas de origem ou de destino estiverem 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.

Digite o seguinte comando para converter uma série de tabelas fragmentadas por data em uma única tabela particionada por tempo de processamento e por data:

bq --location=location partition \
--time_partitioning_type=DAY \
--time_partitioning_expiration integer \
project_id:dataset.source_table_ \
project_id:dataset.destination_table

Em que:

location é o nome do local. A sinalização --location é opcional. Por exemplo, se você 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.
integer é a vida útil padrão (em segundos) das partições da tabela. Não há valor mínimo. O prazo de expiração é avaliado até a data UTC da partição mais o valor inteiro. A expiração da partição é independente da expiração da tabela, mas não a substitui. Se você definir uma data de validade da partição mais longa do que a data de validade da tabela, a data da tabela terá precedência.
project_id é o ID do projeto.
dataset é um conjunto de dados no projeto.
sourcetable é o prefixo da tabela fragmentada por data.
destination_table é o nome da tabela particionada que você está criando.

Exemplos:

Digite o seguinte comando para criar uma tabela particionada por tempo de processamento chamada mytable_partitioned em mydataset no seu projeto padrão. A tabela será particionada por data, com a expiração de particionamento definida para 259.200 segundos (3 dias). As tabelas de origem fragmentadas por data são prefixadas com sourcetable_. As tabelas de origem também estão no seu projeto padrão.

bq partition \
--time_partitioning_type=DAY \
--time_partitioning_expiration 259200 \
mydataset.sourcetable_ \
mydataset.mytable_partitioned

Ao executar o comando, é criado um job de cópia que gera partições das tabelas fragmentadas. Por exemplo, se as tabelas fragmentadas por data forem sourcetable_20180126 e sourcetable_20180127, o job de cópia criará as partições mydataset.mytable_partitioned$20180126 e mydataset.mytable_partitioned$20180127.

Digite o seguinte comando para criar uma tabela particionada por tempo de ingestão chamada mytable_partitioned em mydataset. mydataset está em myotherproject, e não no projeto padrão. A tabela será particionada por data, com a expiração de particionamento definida para 86.400 segundos (1 dia). As tabelas de origem fragmentadas por data são prefixadas com sourcetable_. As tabelas de origem estão no seu projeto padrão.

bq partition \
--time_partitioning_type=DAY \
--time_partitioning_expiration 86400 \
mydataset.sourcetable_ \
myotherproject:mydataset.mytable_partitioned

Digite o seguinte comando para criar uma tabela particionada por tempo de processamento chamada mytable_partitioned em mydataset no seu projeto padrão. mydataset foi criado na região asia-northeast1. A tabela será particionada por data, com a expiração de particionamento definida para 259.200 segundos (3 dias). As tabelas de origem fragmentadas por data são prefixadas com sourcetable_. As tabelas de origem também estão no seu projeto padrão.

bq --location=asia-northeast1 partition \
--time_partitioning_type=DAY \
--time_partitioning_expiration 259200 \
mydataset.sourcetable_ \
mydataset.mytable_partitioned

Como controlar o acesso a tabelas particionadas por tempo

Para configurar o acesso a tabelas e visualizações, conceda um papel do IAM a uma entidade nos seguintes níveis, listados em ordem de intervalo de recursos permitidos (do maior para o menor):

Um nível alto na hierarquia de recursos do Google Cloud, como o nível do projeto, da pasta ou da organização
O nível do conjunto de dados
O nível da tabela/visualização.

O acesso a qualquer recurso protegido pelo IAM é aditivo. Por exemplo, se uma entidade não tiver acesso no nível alto, como o projeto, uma opção será conceder à entidade o acesso no nível do conjunto de dados, e ela terá acesso às tabelas e visualizações no conjunto de dados. Da mesma forma, se a entidade não tiver acesso no nível alto ou no nível do conjunto de dados, uma opção será conceder acesso à entidade no nível da tabela/visualização.

A concessão de papéis do IAM em um nível mais alto na hierarquia de recursos do Google Cloud, como projeto, pasta ou organização, concede à entidade acesso a um amplo conjunto de recursos. Por exemplo, conceder um papel a uma entidade no nível do projeto fornece a ela permissões referentes aos conjuntos de dados em todo o projeto.

Conceder um papel no nível do conjunto de dados especifica as operações que uma entidade tem permissão de realizar em tabelas e define as visualizações nesse conjunto de dados específico, mesmo que a entidade não tenha acesso em um nível superior. Para informações sobre como configurar controles de acesso no nível do conjunto de dados, consulte este link.

A concessão de um papel no nível da tabela ou da visualização especifica as operações que uma entidade pode realizar em tabelas e visualizações específicas, mesmo que a entidade não tenha acesso em um nível superior. Para informações sobre como configurar controles de acesso no nível da tabela e da visualização, consulte este link.

Você também pode criar papéis personalizados do IAM. Se você criar um papel personalizado, as permissões concedidas dependerão das operações específicas que a entidade poderá executar.

Não é possível definir uma permissão "deny" em nenhum recurso protegido pelo IAM.

Para mais informações sobre papéis e permissões, consulte estes tópicos:

Noções básicas sobre papéis na documentação do IAM
Permissões e papéis predefinidos do BigQuery
Como controlar o acesso a conjuntos de dados
Como controlar o acesso a tabelas e visualizações
Como restringir o acesso com a segurança no nível da coluna do BigQuery

Como usar tabelas particionadas por tempo de ingestão

Como conseguir informações sobre tabelas particionadas por tempo de ingestão

Para ver mais informações sobre tabelas:

Como usar o Console do Cloud
use o comando bq show na ferramenta de linha de comando bq;
chame o método da API tables.get;
use as bibliotecas de cliente.

Permissões necessárias

Para ver informações sobre tabelas, é preciso ter pelo menos as permissões bigquery.tables.get. Os seguintes papéis predefinidos do IAM incluem as permissões bigquery.tables.get:

bigquery.metadataViewer
bigquery.dataViewer
bigquery.dataOwner
bigquery.dataEditor
bigquery.admin

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

Como conseguir informações da tabela particionada por tempo de ingestão

Para ver informações sobre uma tabela particionada por tempo de ingestão:

Console

Abra a página do BigQuery no Console do Cloud.

Acesse a página do BigQuery
No painel Explorer, expanda o projeto e o conjunto de dados e selecione a tabela.

Observação: a experiência padrão é a versão de visualização do Console do Cloud. Se você clicou em Ocultar recursos da versão de visualização para acessar a versão de disponibilidade geral do Console do Cloud, faça o seguinte: na seção Recursos do painel de navegação, selecione um projeto e um banco de dados e clique no nome da tabela.
Clique no nome da tabela.
Clique em Detalhes. Essa guia exibe a descrição e as informações da tabela.
Clique na guia Esquema para ver a definição do esquema da tabela. Observe a pseudocoluna extra _PARTITIONTIME, que contém o carimbo de data/hora com base na data para conferir os dados que são carregados na tabela.

bq

Emita o comando bq show para exibir todas as informações da tabela. Use a sinalização --schema para exibir somente informações de esquema da tabela. A sinalização --format pode ser usada para controlar a saída.

Se você estiver recebendo informações sobre uma tabela em um projeto diferente do projeto padrão, adicione o ID do projeto ao conjunto de dados no seguinte formato: project_id:dataset.

bq show --schema --format=prettyjson <var>project_id:dataset.table</var>

Em que:

project_id é o ID do projeto;
dataset é o nome do conjunto de dados.
table é o nome da tabela.

Exemplos:

Digite o comando a seguir para exibir todas as informações sobre mytable em mydataset. mydataset está em seu projeto padrão.

bq show --format=prettyjson mydataset.mytable

Digite o comando a seguir para exibir todas as informações sobre mytable em mydataset. mydataset está em myotherproject, e não no seu projeto padrão.

bq show --format=prettyjson myotherproject:mydataset.mytable

Digite o comando a seguir para exibir apenas informações de esquema sobre mytable em mydataset. mydataset está em myotherproject, e não no projeto padrão.

Ao mostrar informações de esquema sobre uma tabela particionada por tempo, a pseudocoluna _PARTITIONTIME não é exibida.

bq show --schema --format=prettyjson myotherproject:mydataset.mytable

API

Chame o método bigquery.tables.get e forneça os parâmetros relevantes.

Como listar tabelas particionadas por tempo de ingestão em um conjunto de dados

É possível listar tabelas em conjuntos de dados, incluindo as particionadas, usando o Console do Cloud ou o comando bq ls na ferramenta de linha de comando bq ou chamando o método da API tables.list.

Permissões necessárias

Para listar tabelas em um conjunto de dados, é preciso ter pelo menos as permissões bigquery.tables.list. Os seguintes papéis predefinidos do IAM incluem as permissões bigquery.tables.list:

bigquery.user
bigquery.metadataViewer
bigquery.dataViewer
bigquery.dataOwner
bigquery.dataEditor
bigquery.admin

Para mais informações sobre funções e permissões do IAM no BigQuery, consulte Funções e permissões predefinidos.

Como listar tabelas particionadas

Para listar as tabelas em um conjunto de dados (inclusive as particionadas):

Console

Abra a página do BigQuery no Console do Cloud.

Acesse a página do BigQuery
No painel Explorer, expanda o projeto e o conjunto de dados e selecione a tabela.

Observação: a experiência padrão é a versão de visualização do Console do Cloud. Se você clicou em Ocultar recursos da versão de visualização para acessar a versão de disponibilidade geral do Console do Cloud, faça o seguinte: na seção Recursos do painel de navegação, selecione um projeto e um banco de dados e clique no nome da tabela.
Percorra a lista para ver as tabelas no conjunto de dados. Tabelas, tabelas particionadas, modelos e visualizações são identificados por diferentes ícones.

bq

Emita o comando bq ls. A sinalização --format pode ser usada para controlar a saída. Se estiver listando tabelas em um projeto diferente do projeto padrão, adicione a ID do projeto ao conjunto de dados no seguinte formato: project_id:dataset.

bq ls --format=pretty project_id:dataset

Em que:

project_id é o ID do projeto;
dataset é o nome do conjunto de dados.

Ao executar o comando, o campo Type exibe TABLE ou VIEW. Para tabelas particionadas por tempo de processamento, o campo Time Partitioning exibe DAY e o prazo de validade da partição em milissegundos, se tiver sido especificado.

Por exemplo:

+-------------------------+-------+----------------------+--------------------------------+
|         tableId         | Type  |        Labels        | Time Partitioning              |
+-------------------------+-------+----------------------+--------------------------------+
| mytable                 | TABLE | department:shipping  |  DAY (expirationMs: 259200000) |
| myview                  | VIEW  |                      |                                |
+-------------------------+-------+----------------------+--------------------------------+

Exemplos:

Digite o comando a seguir para listar tabelas no conjunto de dados mydataset no seu projeto padrão.

bq ls --format=pretty mydataset

Digite o comando a seguir para listar tabelas no conjunto de dados mydataset em myotherproject.

bq ls --format=pretty myotherproject:mydataset

API

Para listar tabelas usando a API, chame o método tables.list.

Como listar partições em tabelas particionadas por tempo de ingestão

É possível listar as partições em uma tabela particionada por tempo de ingestão consultando a pseudocoluna _PARTITIONTIME no SQL padrão ou a metatabela __PARTITIONS_SUMMARY__ no SQL legado.

É possível executar a consulta usando o Console do Cloud, o comando bq query na ferramenta de linha de comando bq ou chamando o comando jobs.insert e configurar um job query.

Permissões necessárias

Para executar um job de consulta que use a metatabela __PARTITIONS_SUMMARY__ ou a pseudocoluna _PARTITIONTIME, é preciso ter as permissões bigquery.jobs.create. Os seguintes papéis predefinidos do IAM incluem as permissões bigquery.jobs.create:

bigquery.user
bigquery.jobUser
bigquery.admin

Também é necessário ter permissões bigquery.tables.getData. Os seguintes papéis predefinidos do IAM incluem as permissões bigquery.tables.getData:

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

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

Como listar partições em uma tabela particionada por tempo de ingestão

Para listar partições em uma tabela particionada, use um SQL padrão (preferencial) ou um SQL legado. Siga as instruções abaixo para listar partições:

SQL padrão:

Console

Abra a página do BigQuery no Console do Cloud.

Acesse a página do BigQuery
Clique no botão Escrever nova consulta.
Insira o texto a seguir na caixa Editor de consultas para buscar a pseudocoluna _PARTITIONTIME:
```
SELECT
  _PARTITIONTIME as pt
FROM
  `dataset.table`
GROUP BY 1
```
Em que:
- dataset é o conjunto de dados que contém a tabela.
- table é o nome da tabela.
Opcional: clique em Mais e selecione Configurações de consulta.
Clique em Executar.

bq

Digite a seguinte consulta usando o comando bq query:

bq --location=location query --use_legacy_sql=false '
SELECT
  _PARTITIONTIME as pt
FROM
  `dataset.table`
GROUP BY 1'

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.
dataset é o conjunto de dados que contém a tabela.
table é o nome da tabela.

API

Chame o método jobs.insert e configure um job query que consulte a pseudocoluna _PARTITIONTIME da tabela.

Especifique seu local na property location da seção jobReference do recurso do job.

SQL legado:

Console

Abra a página do BigQuery no Console do Cloud.

Acesse a página do BigQuery
Clique no botão Escrever nova consulta.
Insira o texto a seguir na caixa Editor de consultas para consultar a metatabela __PARTITIONS_SUMMARY__:
```
#legacySQL
SELECT
  partition_id
FROM
  [dataset.table$__PARTITIONS_SUMMARY__]
```
Em que:
- dataset é o conjunto de dados que contém a tabela.
- table é o nome da tabela.
Clique em Mais e selecione Configurações de consulta.
Em Local de processamento, clique em Seleção automática e selecione o local dos seus dados. Deixe o local de processamento definido como Seleção automática se seus dados estiverem no local multirregional US ou EU. Quando os dados estão na região US ou EU, o local de processamento é detectado automaticamente.
Clique em Executar.

bq

Digite a seguinte consulta usando o comando bq query:

bq --location=location query --use_legacy_sql=true '
SELECT
   partition_id
 FROM
   [dataset.table$__PARTITIONS_SUMMARY__]'

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.
dataset é o conjunto de dados que contém a tabela.
table é o nome da tabela.

API

Chame o método jobs.insert e configure um job query que consulte a metatabela __PARTITIONS_SUMMARY__.

Especifique seu local na property location da seção jobReference do recurso do job.

Como receber metadados de tabelas usando metatabelas

Para mais informações sobre tabelas particionadas, use tabelas especiais chamadas metatabelas. As metatabelas contêm metadados, como a lista de tabelas e visualizações em um conjunto de dados. As metatabelas são somente leitura.

Como conseguir metadados de partição usando metatabelas

A metatabela __PARTITIONS_SUMMARY__ é uma tabela especial. O conteúdo dela representa metadados sobre partições em uma tabela particionada por tempo. A metatabela __PARTITIONS_SUMMARY__ é somente leitura.

Para acessar metadados sobre as partições em uma tabela particionada por tempo, use a metatabela __PARTITIONS_SUMMARY__ na instrução SELECT de uma consulta. É possível executar a consulta das seguintes maneiras:

Como usar o Console do Cloud
Use o comando bq query da ferramenta de linha de comando bq.
Chamando o método jobs.insert da API e configurando um job query.
use as bibliotecas de cliente.

Atualmente, o SQL padrão não suporta o separador do decorador de partições ($). Portanto, você não deve consultar __PARTITIONS_SUMMARY__ no SQL padrão. Uma consulta no SQL anterior que utiliza a metatabela __PARTITIONS_SUMMARY__ se parece com o seguinte:

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

Em que:

dataset é o nome do conjunto de dados.
table é o nome da tabela particionada por tempo.
column é um destes:

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, em UTC.

Permissões de metatabela da partição

Para executar um job de consulta que use a metatabela __PARTITIONS_SUMMARY__, é preciso ter pelo menos as permissões bigquery.jobs.create. Os seguintes papéis predefinidos do IAM incluem as permissões bigquery.jobs.create:

bigquery.user
bigquery.jobUser
bigquery.admin

Também é necessário ter permissões bigquery.tables.getData. Os seguintes papéis predefinidos do IAM incluem as permissões bigquery.tables.getData:

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

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

Exemplo de metatabela da partição

A consulta a seguir recupera todos os metadados particionados para uma tabela particionada por tempo chamada mydataset.mytable.

Console

#legacySQL
SELECT
  *
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]

bq

bq query --use_legacy_sql=true '
SELECT
  *
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]'

A saída será assim:

+----------------+------------+----------------+--------------+---------------+--------------------+
|   project_id   | dataset_id |    table_id    | partition_id | creation_time | last_modified_time |
+----------------+------------+----------------+--------------+---------------+--------------------+
| myproject      | mydataset  | mytable        | 20160314     | 1517190224120 | 1517190224997      |
| myproject      | mydataset  | mytable        | 20160315     | 1517190224120 | 1517190224997      |
+----------------+------------+----------------+--------------+---------------+--------------------+

A consulta a seguir lista os horários em que as partições em mydataset.mytable foram modificadas pela última vez.

Console

#legacySQL
SELECT
  partition_id,
  last_modified_time
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]

bq

bq query --use_legacy_sql=true '
SELECT
  partition_id,
  last_modified_time
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]'

A saída será assim:

+--------------+--------------------+
| partition_id | last_modified_time |
+--------------+--------------------+
| 20160102     |      1471632556179 |
| 20160101     |      1471632538142 |
| 20160103     |      1471632570463 |
+--------------+--------------------+

Para exibir o campo last_modified_time em formato legível, use a função FORMAT_UTC_USEC. Exemplo:

Console

#legacySQL
SELECT
  partition_id,
  FORMAT_UTC_USEC(last_modified_time*1000) AS last_modified
FROM
  [mydataset.table1$__PARTITIONS_SUMMARY__]

bq

bq query --use_legacy_sql=true '
SELECT
  partition_id,
  FORMAT_UTC_USEC(last_modified_time*1000) AS last_modified
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]'

A saída será assim:

+--------------+----------------------------+
| partition_id |       last_modified        |
+--------------+----------------------------+
| 20160103     | 2016-08-19 18:49:30.463000 |
| 20160102     | 2016-08-19 18:49:16.179000 |
| 20160101     | 2016-08-19 18:48:58.142000 |
+--------------+----------------------------+

Exemplo: como criar uma tabela particionada por tempo de ingestão usando os resultados da consulta

Neste exemplo, você criará uma tabela particionada por tempo de processamento usando a ferramenta de linha de comando bq e adicionará dados a três partições usando os resultados da consulta. A tabela conterá dados climáticos, particionados por data, referentes aos três primeiros dias de 2016.

Este exemplo consulta o conjunto de dados climáticos GSOD da NOAA sobre temperaturas dos três primeiros dias de 2016 e grava os resultados na tabela particionada.

Etapa 1: Crie uma tabela de tempo de processamento vazia, particionada por data, chamada temps em um conjunto de dados chamado mydataset. Não é necessário especificar um esquema para a tabela. O esquema dos resultados da consulta é usado como a definição do esquema para a tabela.

bq mk --time_partitioning_type=DAY mydataset.temps

Para visualizar as definições de configuração, use o comando bq show:

bq show --format=prettyjson mydataset.temps

Procure a entrada timePartitioning na resposta ao comando bq show:

{
  ...
  "timePartitioning": {
    "type": "DAY"
  },
  "type": "TABLE"
}

Etapa 2: consulte as temperaturas dos três primeiros dias de 2016 no conjunto de dados climáticos GSOD da NOAA e grave os resultados nas respectivas partições na tabela temps. As consultas a seguir usam a opção --destination_table e a sintaxe do SQL padrão para gravar 100 linhas de resultados de consulta em uma partição.

Execute a seguinte consulta para gravar as temperaturas de 1º de janeiro de 2016 para particionar mydataset.temps$20160101:

bq query --use_legacy_sql=false \
--destination_table 'mydataset.temps$20160101' \
'SELECT
   stn,
   temp
 FROM
   `bigquery-public-data`.noaa_gsod.gsod2016
 WHERE
   mo="01"
   AND da="01"
 LIMIT
   100'

Execute a seguinte consulta para gravar as temperaturas de 2 de janeiro de 2016 para particionar mydataset.temps$20160102:

bq --location=US query --use_legacy_sql=false \
--destination_table 'mydataset.temps$20160102' \
'SELECT
   stn,
   temp
 FROM
   `bigquery-public-data`.noaa_gsod.gsod2016
 WHERE
   mo="01"
   AND da="02"
 LIMIT
   100'

Execute a seguinte consulta para gravar as temperaturas de 3 de janeiro de 2016 para particionar mydataset.temps$20160103:

bq --location=US query --use_legacy_sql=false \
--destination_table 'mydataset.temps$20160103' \
'SELECT
   stn,
   temp
 FROM
   `bigquery-public-data`.noaa_gsod.gsod2016
 WHERE
   mo="01"
   AND da="03"
 LIMIT
   100'

Etapa 3: use o comando bq show para confirmar que você tem 300 linhas na sua tabela.

bq show mydataset.temps

Os resultados mostram o esquema e o total de linhas.

  Last modified        Schema       Total Rows   Total Bytes     Expiration      Time Partitioning   Labels   kmsKeyName
 ----------------- ---------------- ------------ ------------- ----------------- ------------------- -------- ------------
  28 Jan 15:03:45   |- stn: string   300          4800          29 Jan 15:00:32   DAY
                    |- temp: float

Próximas etapas

Para uma visão geral do suporte a tabelas particionadas no BigQuery, consulte esta página.
Para saber como gerenciar e atualizar tabelas particionadas, consulte esta página.
Para mais informações sobre como buscar tabelas particionadas, consulte esta página.
Consulte Como criar e usar tabelas particionadas por tempo baseadas em colunas para mais informações.
Para mais informações sobre tabelas particionadas por intervalo de números inteiros, consulte Como criar e usar tabelas particionadas por intervalo de números inteiros.