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. Para informações sobre tabelas particionadas, consulte Como criar e usar tabelas particionadas.

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 as 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

É possível criar uma tabela particionada por tempo de ingestão no BigQuery:

  • ao usar uma instrução DDL CREATE TABLE com uma partition_expression;
  • manualmente, ao usar o Console do GCP, a IU da web clássica do BigQuery ou o comando bq mk da ferramenta da linha de comando;
  • de maneira programática, ao chamar o método de API tables.insert;
  • dos resultados da consulta;
  • ao carregar dados;
  • ao converter tabelas com dados compartilhados em tabelas particionadas.

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

  • conter até 1.024 caracteres;
  • conter letras (maiúsculas e minúsculas), números e sublinhados.

Permissões exigidas

Para criar uma tabela particionada por tempo de ingestão, é preciso ter acesso WRITER no nível do conjunto de dados ou um papel do IAM para envolvidos no projeto que inclua permissões bigquery.tables.create. Os seguintes papéis predefinidos do IAM do projeto incluem permissões bigquery.tables.create:

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 criar tabelas particionadas em qualquer conjunto de dados que o usuário criar. Quando um usuário com papel bigquery.user cria um conjunto de dados, recebe acesso de OWNER ao conjunto. O acesso OWNER a um conjunto de dados concede ao usuário o controle total sobre ele e todas as tabelas contidas 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 criar uma tabela particionada por tempo de ingestão vazia com definição de esquema

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

  • inserir o esquema usando o Console do GCP ou a IU da web clássica do BigQuery;
  • fornecer o esquema in-line usando a ferramenta de linha de comando;
  • enviar um arquivo de esquema JSON usando a ferramenta de linha de comando;
  • 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 ingestão vazia com definição de esquema:

Console

  1. Selecione um conjunto de dados na seção Recursos do painel de navegação. Clique em Criar tabela do lado direito da janela. Criar tabela
  2. Na página Criar tabela, na seção Origem, selecione Criar tabela vazia. Criar origem da tabela

  3. Siga estas etapas na página Criar tabela, na seção Destino:

    • Em Nome do conjunto de dados, escolha o conjunto apropriado. Escolher conjunto de dados

    • No campo Nome da tabela, insira o nome da tabela que você está criando no BigQuery.

    • Verifique se o Tipo de tabela está configurado como Tabela nativa.

  4. Na seção Esquema, insira a definição do esquema.

    • Insira as informações do esquema manualmente:

      • Ative Editar como texto e insira o esquema da tabela como uma matriz JSON.

      • Use Adicionar campo para inserir manualmente o esquema.

  5. Na seção Configurações de particionamento e cluster:

    • Em Particionamento, clique em Sem particionamento e selecione Partição por tempo de processamento. Partição por ingestão

  6. Clique em Criar tabela.

Após a criação da tabela, é possível atualizar a validade da tabela, a descrição e os rótulos da tabela particionada. Não é possível adicionar uma expiração de partição após a criação de uma tabela usando a IU da Web do BigQuery.

IU clássica

  1. Clique no ícone de seta para baixo ícone de seta para baixo ao lado do nome do conjunto de dados na navegação e clique em Criar nova tabela.

  2. Na página Criar tabela, na seção Dados de origem, clique em Criar tabela vazia.

  3. Na página Create Table, na seção Destination Table:

    • Em Table name, escolha o conjunto de dados apropriado e, no campo adequado, insira o nome da tabela que você está criando.
    • Verifique se o Tipo de tabela está configurado como Tabela nativa.

  4. Na seção Esquema, insira manualmente a definição do esquema.

    • É possível inserir informações de esquema manualmente com um destes procedimentos:

      • Clique em Editar como texto e insira o esquema da tabela como matriz JSON:

        Adicionar esquema como uma matriz JSON

      • Use Adicionar campo para inserir o esquema:

        Adicionar esquema usando adição de campos

  5. Na seção Opções:

  6. Clique em Criar tabela.

Após a criação da tabela, é possível atualizar a validade da tabela, a descrição e os rótulos da tabela particionada. Não é possível adicionar uma expiração de partição após a criação de uma tabela usando a IU da Web do BigQuery.

Linha de comando

Use o comando mk com a sinalização --table (ou atalho -t), e a sinalização --time_partitioning_type=DAY. Atualmente, DAY é o único valor compatível com --time_partitioning_type. Você pode fornecer 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ê está criando uma tabela em um projeto diferente do projeto padrão, adicione o código do projeto ao conjunto de dados no formato a seguir: [PROJECT_ID]:[DATASET].

--destination_kms_key não é demonstrado aqui. Para mais informações sobre como usar essa sinalização, consulte as chaves de criptografia gerenciadas pelo cliente.

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=DAY --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 tempo de expiração é avaliado para a hora atual mais o valor inteiro. Se você definir o tempo 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;
  • [INTEGER2] é a vida útil padrão (em segundos) para as partições da tabela. Não há valor mínimo. A data de validade é avaliada 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 expiração de partição mais longa do que a expiração da tabela, a expiração 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. Você pode inserir vários marcadores usando uma lista separada por vírgulas;
  • [PROJECT_ID] é o código do projeto;
  • [DATASET] é um conjunto de dados no projeto;
  • [TABLE] é o nome da tabela particionada por tempo que você está criando;
  • [SCHEMA] é a definição de esquema in-line no formato [FIELD]:[DATA_TYPE],[FIELD]:[DATA_TYPE] ou o caminho para o arquivo de esquema JSON em sua máquina local.

Ao especificar o esquema na linha de comando, não é possível incluir um tipo RECORD (STRUCT) nem uma descrição de coluna. Também não é possível 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 em mydataset no seu projeto padrão. A expiração do particionamento é configurada para 259.200 segundos (três dias), a expiração da tabela é definida como 2.592.000 (um mês de 30 dias), a descrição é definida como This is my time-partitioned table e o rótulo é definido 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, e não no projeto padrão. A expiração do particionamento é configurada para 259.200 segundos (três dias), a descrição é definida como This is my time-partitioned table e o rótulo é definido como organization:development. O comando usa o atalho -t em vez de --table. Esse comando não especifica uma expiração 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 expiração de tabela padrão, a tabela nunca 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 especifica as propriedades timePartitioning e 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. Você pode criar uma nova tabela particionada consultando 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, veja Como consultar tabelas particionadas.

Console

Não é possível especificar opções de particionamento para uma tabela de destino ao consultar dados usando a IU da Web do BigQuery do console.

IU clássica

Ao consultar dados usando a IU da Web do BigQuery, não é possível especificar opções de particionamento para uma tabela de destino.

CLI

Digite o comando bq query, especifique a sinalização --destination_table para criar uma tabela permanente com base nos resultados da consulta e especifique a sinalização --time_partitioning_type=DAY para criar uma tabela de destino particionada por tempo de ingestão. Atualmente, DAY é o único valor compatível com --time_partitioning_type.

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 em seu projeto padrão, adicione o código do projeto ao nome do conjunto de dados no seguinte formato: [PROJECT_ID]:[DATASET].

Forneça a sinalização --location e defina o valor como seu 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=DAY --use_legacy_sql=false '[QUERY]'

em que:

  • [LOCATION] é o nome do seu local. A sinalização --location é opcional. Por exemplo, se estiver usando o BigQuery na região de Tóquio, será possível definir 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 código 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 criada com o uso dos resultados da consulta;
  • [QUERY] é uma consulta na sintaxe do SQL padrão.

Exemplos:

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. 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 do USA Name Data.

bq --location=US 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. 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 do USA Name Data.

bq --location=US 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 permanente particionada por tempo de processamento, chame o método jobs.insert, configure um job query e inclua um valor para as propriedades configuration.query.destinationTable e timePartitioning.

Especifique o local na propriedade location, na seção jobReference do recurso do job.

Como criar uma tabela particionada por tempo de ingestão ao carregar dados

Você pode criar uma tabela particionada por tempo de ingestão especificando opções de particionamento ao carregar dados em uma nova tabela. Você não precisa criar uma tabela particionada vazia antes de carregar dados nela. Você pode criar a tabela particionada e carregar seus dados ao mesmo tempo.

Ao carregar dados no BigQuery, você pode fornecer 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 e --time_partitioning_type. Uma vez que --time_partitioning_type=DAY é o único valor compatível atualmente, esse parâmetro é opcional. Se as tabelas de origem ou a tabela de destino estiverem em um projeto diferente do padrão, adicione o código do projeto ao nome do conjunto no seguinte formato: [PROJECT_ID]:[DATASET].

Forneça a sinalização --location e defina o valor como seu local.

Digite o comando a seguir para converter uma série de tabelas fragmentadas por data em uma única tabela particionada por tempo de ingestão:

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 seu 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) para as partições da tabela. Não há valor mínimo. A data de validade é avaliada 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 expiração de partição mais longa do que a expiração da tabela, a expiração da tabela terá precedência;
  • [PROJECT_ID] é o código do projeto;
  • [DATASET] é um conjunto de dados no projeto;
  • [SOURCE_TABLE]_ é o prefixo da tabela fragmentada por data;
  • [DESTINATION_TABLE] é o nome da tabela particionada que você está criando.

Exemplos:

Digite o comando a seguir para criar uma tabela particionada por tempo de ingestão chamada mytable_partitioned em mydataset no projeto padrão. A expiração do particionamento é definida para 259.200 segundos (três dias). As tabelas de origem fragmentadas por data são prefixadas com sourcetable_. As tabelas de origem também estão no projeto padrão. mydataset foi criado no local multirregional US.

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

Quando você executa o comando, é criado um job de cópia que gera partições das tabelas fragmentadas. Por exemplo, se as tabelas fragmentadas por data fossem sourcetable_20180126 e sourcetable_20180127, o job de cópia criaria estas partições: mydataset.mytable_partitioned$20180126 e mydataset.mytable_partitioned$20180127.

Digite o comando a seguir para criar uma tabela particionada por tempo de ingestão chamada mytable_partitioned em mydataset. mydataset está em myotherproject, não no projeto padrão. A expiração do particionamento é definida para 86.400 segundos (um dia). As tabelas de origem fragmentadas por data são prefixadas com sourcetable_. As tabelas de origem estão no projeto padrão. mydataset foi criado no local multirregional US.

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

Digite o comando a seguir para criar uma tabela particionada de tempo de ingestão denominada mytable_partitioned em mydataset no projeto padrão. mydataset foi criada na região asia-northeast1. A expiração do particionamento é definida para 259.200 segundos (três dias). As tabelas de origem fragmentadas por data são prefixadas com sourcetable_. As tabelas de origem também estão no 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

Não é possível atribuir controles de acesso diretamente a tabelas ou partições. Você pode controlar o acesso à tabela configurando controles de acesso no nível do conjunto de dados ou no nível do projeto.

Os controles de acesso no nível do conjunto de dados especificam quais operações que usuários, grupos e contas de serviço podem executar nas tabelas nesse conjunto de dados específico. Se você atribuir apenas permissões no nível do conjunto de dados, também precisará atribuir um papel primitivo ou predefinido para envolvidos no projeto que forneça acesso a ele. Por exemplo, bigquery.user.

Em vez de conceder acesso a conjuntos de dados individuais, você pode atribuir papéis do IAM predefinidos para envolvidos no projeto que concedem permissões aos dados de todas as tabelas em todos os conjuntos de dados de um projeto.

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 da tabela que você quer atribuir ao usuário, grupo ou a conta de serviço.

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

Como usar tabelas particionadas por tempo de ingestão

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

Para mais informações sobre tabelas, use o Console do GCP, a IU da Web clássica do BigQuery, o comando bq show da CLI ou chame o método tables.get da API.

Permissões exigidas

Para receber informações sobre tabelas, é preciso ter o papel READER no conjunto de dados ou um papel de IAM para envolvidos no projeto que inclua permissões bigquery.tables.get. Se você tem permissões bigquery.tables.get no nível do projeto, pode acessar informações sobre todas as tabelas do projeto. Todos os papéis predefinidos de IAM para envolvidos no projeto incluem permissões bigquery.tables.get, exceto bigquery.jobUser e bigquery.user.

Além disso, um usuário com papel bigquery.user tem permissões bigquery.datasets.create. Isso permite que um usuário com o papel bigquery.user receba informações sobre tabelas em qualquer conjunto de dados criado por ele. Quando um usuário com o papel bigquery.user cria um conjunto de dados, ele recebe o acesso OWNER ao conjunto. O acesso OWNER a um conjunto de dados concede ao usuário controle total sobre o conjunto e todas as tabelas contidas 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 no nível do conjunto de dados, consulte Papéis primários para conjuntos de dados.

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

  1. Abra a IU da Web do BigQuery no Console do GCP.
    Acessar a IU da Web do BigQuery

  2. No painel de navegação, na seção Recursos, expanda seu projeto e selecione um conjunto de dados para listar as tabelas e visualizações que ele contém.

  3. Clique no nome da tabela.

  4. Clique em Detalhes. Essa guia exibe a descrição e as informações da tabela.

    Detalhes da tabela

  5. Clique na guia Esquema para visualizar a definição do esquema da tabela. Observe a pseudocoluna adicional _PARTITIONTIME, que contém o carimbo de data/hora para dados que são carregados na tabela.

IU clássica

  1. No painel de navegação, clique no ícone de seta para baixo ícone de seta para baixo, à 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 no nome da tabela.

  3. Clique em Detalhes. A página Detalhes da tabela exibe a descrição e as informações da tabela.

    Detalhes da tabela particionada

  4. Clique na guia Esquema para visualizar a definição do esquema da tabela. Observe a pseudocoluna adicional _PARTITIONTIME, que contém o carimbo de data/hora para dados que são carregados na tabela.

    Esquema da tabela particionada

Linha de comando

Emita o comando bq show para exibir todas as informações da tabela. Use a sinalização --schema para exibir apenas informações de esquema de 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 padrão, adicione o código do projeto ao conjunto de dados no formato abaixo: [PROJECT_ID]:[DATASET].

bq show --schema --format=prettyjson [PROJECT_ID]:[DATASET].[TABLE]

em que:

  • [PROJECT_ID] é o código 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á no 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 projeto padrão.

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

Digite o comando a seguir para exibir somente 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

Para listar tabelas em conjuntos de dados (incluindo tabelas particionadas), use o Console do GCP, a IU da Web clássica do BigQuery, o comando bq ls da CLI ou chame o método tables.list da API.

Permissões exigidas

Para listar tabelas em um conjunto de dados, é necessário ter o papel READER no conjunto de dados ou um papel de IAM para envolvidos no projeto que inclua permissões bigquery.tables.list. Se você tiver permissões bigquery.tables.list para envolvidos no projeto, poderá listar as tabelas em qualquer conjunto de dados do projeto. Todos os papéis do IAM predefinidos para envolvidos no projeto incluem permissões bigquery.tables.list, exceto bigquery.jobUser.

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 no nível do conjunto de dados, consulte Papéis primários para conjuntos de dados.

Como listar tabelas particionadas

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

Console

  1. Abra a IU da Web do BigQuery no Console do GCP.
    Acessar a IU da Web do BigQuery

  2. No painel de navegação, na seção Recursos, expanda seu projeto e selecione um conjunto de dados.

  3. Percorra a lista para ver as tabelas no conjunto de dados. Tabelas, tabelas particionadas, modelos e visualizações são identificados por diferentes ícones.

IU clássica

  1. No painel de navegação da IU da Web, clique no ícone de seta para baixo ícone de seta para baixo, à 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. Percorra a lista para ver as tabelas no conjunto de dados. Tabelas e visualizações são identificadas por diferentes ícones.

    Ver tabelas

CLI

Emita o comando bq ls. A sinalização --format pode ser usada para controlar a saída. Se você estiver listando tabelas em um projeto diferente do padrão, adicione o código do projeto ao conjunto de dados no seguinte formato: [PROJECT_ID]:[DATASET].

bq ls --format=pretty [PROJECT_ID]:[DATASET]

em que:

  • [PROJECT_ID] é o código do projeto;
  • [DATASET] é o nome do conjunto de dados.

Ao executar o comando, o campo Type exibe TABLE ou VIEW. Em tabelas particionadas por tempo de ingestão, o campo Time Partitioning exibe DAY e o tempo de expiração da partição em milissegundos (se houver).

Por exemplo:

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

Exemplos:

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

bq ls --format=pretty mydataset

Digite o comando a seguir para listar as 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).

Para executar a consulta no Console do GCP ou na IU da Web clássica do BigQuery, use o comando bq query ou chame o método jobs.insert e configure um job query.

Permissões exigidas

Para executar um job de consulta que usa a metatabela __PARTITIONS_SUMMARY__ ou a pseudocoluna _PARTITIONTIME, é necessário ter permissões bigquery.jobs.create. Os seguintes papéis do IAM predefinidos no projeto incluem permissões bigquery.jobs.create:

Também é preciso ter o papel READER no nível do conjunto de dados ou um papel do IAM para envolvidos no projeto que inclua permissões bigquery.tables.getData. Todos os papéis do IAM predefinidos para envolvidos no projeto incluem permissões bigquery.tables.getData, exceto bigquery.user, bigquery.jobUser e bigquery.metadataViewer.

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 no nível do conjunto de dados, consulte Papéis primários para conjuntos de dados.

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

Você pode listar partições em uma tabela particionada por tempo de ingestão usando o SQL padrão (de preferência) ou o SQL legado. Para listar partições:

SQL padrão:

Console

  1. Abra a IU da Web do BigQuery no Console do GCP.
    Acessar a IU da Web do BigQuery

  2. Clique no botão Escrever nova consulta.

  3. Digite o seguinte texto na caixa Editor de consultas para consultar a pseudocoluna _PARTITIONTIME:

    #standardSQL
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.

  4. (Opcional) Clique em Mais e selecione Configurações de consulta.

    Configurações de consulta

  5. Em Local de processamento, clique em Seleção automática e escolha o local dos seus dados. Você pode deixar 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.

    Local de processamento da consulta

  6. Clique em Executar.

IU clássica

  1. Acesse a IU da Web do BigQuery.

    Acessar a IU da Web do BigQuery

  2. Clique no botão Escrever consulta.

  3. Digite o texto abaixo na caixa Nova consulta para consultar uma pseudocoluna _PARTITIONTIME da tabela:

    #standardSQL
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.

  4. Clique em Mostrar opções.

  5. (Opcional) Em Local de processamento, clique em Não especificado e escolha o local dos dados.

  6. Clique em Executar consulta.

CLI

Digite a consulta abaixo usando o comando bq query:

bq --location=[LOCATION] query --use_legacy_sql=false '
SELECT
  _PARTITIONTIME as pt
FROM
  `[DATASET].[TABLE]`
GROUP BY 1'

Where:

  + `[LOCATION]` is the name of your location. The `--location` flag is
    optional. For example, if you are using BigQuery in
    the Tokyo region, you can set the flag's value to `asia-northeast1`.
    You can set a default value for the location using the
    [.bigqueryrc file](/bigquery/docs/bq-command-line-tool#setting_default_values_for_command-line_flags).
  + `[DATASET]` is the dataset that contains the table.
  + `[TABLE]` is the name of the table.

API

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

Especifique o local na propriedade location, na seção jobReference do recurso do job.

SQL legado:

Console

  1. Abra a IU da Web do BigQuery no Console do GCP.
    Acessar a IU da Web do BigQuery

  2. Clique no botão Escrever nova consulta.

  3. Digite o seguinte texto 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.

  4. Clique em Mais e selecione Configurações de consulta.

    Configurações de consulta

  5. Em Local de processamento, clique em Seleção automática e escolha o local dos seus dados. Você pode deixar 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.

    Local de processamento da consulta

  6. Clique em Executar.

IU clássica

  1. Acesse a IU da Web do BigQuery.

    Acessar a IU da Web do BigQuery

  2. Clique no botão Escrever consulta.

  3. Digite o texto abaixo na caixa Nova consulta para consultar uma metatabela __PARTITIONS_SUMMARY__ da tabela:

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

  4. Clique em Mostrar opções.

  5. (Opcional) Em Local de processamento, clique em Não especificado e escolha o local dos dados.

  6. Clique em Executar consulta.

CLI

Digite a consulta abaixo usando o comando bq query:

bq --location=[LOCATION] query --use_legacy_sql=true '
SELECT
   partition_id
 FROM
   [[DATASET].[TABLE]$__PARTITIONS_SUMMARY__]'

Where:

  + `[LOCATION]` is the name of your location. The `--location` flag is
    optional. For example, if you are using BigQuery in
    the Tokyo region, you can set the flag's value to `asia-northeast1`.
    You can set a default value for the location using the
    [.bigqueryrc file](/bigquery/docs/bq-command-line-tool#setting_default_values_for_command-line_flags).
  + `[DATASET]` is the dataset that contains the table.
  + `[TABLE]` is the name of the table.

API

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

Especifique o local na propriedade location, na 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. Metatabelas contêm metadados, como a lista de tabelas e exibiçõ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 cujos conteúdos representam 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 usando o console, a IU da Web clássica do BigQuery, o comando bq query da ferramenta de linha de comando ou chamando o método de API jobs.insert e configurando um job de consulta.

Atualmente, como o SQL padrão não é compatível com o separador do decorador de partição ($), não é possível consultar __PARTITIONS_SUMMARY__ no SQL padrão. Uma consulta de SQL legado que use a metatabela __PARTITIONS_SUMMARY__ terá esta aparência:

    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] é uma destas:
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 O momento em que a partição foi criada, em milissegundos, desde 1º de janeiro de 1970 UTC.
last_modified_time O momento em que a partição foi modificada pela última vez, em milissegundos, desde 1º de janeiro de 1970 UTC.

Permissões de metatabela da partição

Para executar um job de consulta que usa a metatabela __PARTITIONS_SUMMARY__, é preciso ter permissões bigquery.jobs.create. Os seguintes papéis predefinidos do IAM para envolvidos no projeto incluem permissões bigquery.jobs.create:

Também é preciso ter o papel READER no nível do conjunto de dados ou um papel do IAM para envolvidos no projeto que inclua permissões bigquery.tables.getData. Todos os papéis do IAM predefinidos para envolvidos no projeto incluem permissões bigquery.tables.getData, exceto bigquery.user, bigquery.jobUser e bigquery.metadataViewer.

Exemplo de metatabela da partição

A consulta a seguir recupera todos os metadados da partição para uma tabela particionada por tempo denominada mydataset.mytable.

Console 

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

IU clássica 

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

Linha de comando 

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

A saída tem esta aparência:

+----------------+------------+----------------+--------------+---------------+--------------------+
|   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__]

IU clássica 

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

Linha de comando 

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

A saída tem esta aparência:

+--------------+--------------------+
| 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: Por exemplo:

Console 

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

IU clássica 

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

Linha de comando 

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 tem esta aparência:

+--------------+----------------------------+
| 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 ingestão usando a ferramenta de linha de comando 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. Este exemplo usa a sinalização --location=US porque você está consultando um conjunto de dados públicos. Os conjuntos de dados públicos do BigQuery são armazenados no local multirregional US. Por esse motivo, não é possível gravar resultados de consulta de dados públicos em uma tabela ou associar tabelas em conjuntos de dados públicos em outra região.

Etapa 1: Crie uma tabela particionada por tempo de ingestão vazia 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 ver as configurações, use o comando bq show:

bq show --format=prettyjson mydataset.temps

Procure pela entrada timePartitioning na saída do 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 NOAA GSOD 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.

  1. Execute a consulta a seguir para gravar as temperaturas de 1º de janeiro de 2016 na partição mydataset.temps$20160101:

    bq --location=US 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'

  2. Execute a consulta a seguir para gravar as temperaturas de 1º de janeiro de 2016 na partição 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'

  3. Execute a consulta a seguir para gravar as temperaturas de 3 de janeiro de 2016 na partição 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: Confirme se há 300 linhas na sua tabela usando o comando bq show.

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

A seguir

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

Enviar comentários sobre…

Esta página
Comentários sobre a documentação
BigQuery
Comentários sobre o produto
Precisa de ajuda? Acesse nossa página de suporte.