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 mais informações sobre tabelas particionadas por data e carimbo de data/hora, consulte Como criar e usar tabelas particionadas por data e carimbo de data/hora. 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 os metadados das tabelas particionadas usando metatabelas.

Para mais informações sobre como gerenciar tabelas particionadas, incluindo atualização de properties, 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:

  • usando uma instrução DDL CREATE TABLE com partition_expression;
  • manualmente, usando o Console do Cloud ou a IU da Web clássica do BigQuery;
  • usando o comando bq mk da ferramenta de linha de comando;
  • 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, é necessário que o nome dela seja 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, 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 um job de consulta, de carregamento ou de cópia que grave os dados na tabela

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

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

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

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

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

Além disso, se um usuário tiver permissões bigquery.datasets.create, receberá o acesso bigquery.dataOwner ao criar um conjunto de dados. 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 Cloud 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 de ingestão vazia com uma definição de esquema, é possível:

  • usar o Console do Cloud ou a IU da Web clássica do BigQuery para inserir o esquema;
  • usar a ferramenta de linha de comando para fornecer o esquema in-line;
  • usar a ferramenta de linha de comando 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 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.
  3. Na página Criar tabela, na seção Destino:

    • Em Nome do conjunto de dados, escolha o conjunto apropriado. Selecionar 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á definido como Tabela nativa.

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

    • Para inserir 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 ingestão. Partição por ingestão
  6. (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. A exigência de um filtro de particionamento pode reduzir os custos e melhorar o desempenho. Para mais informações, acesse Como consultar tabelas particionadas.

  7. (Opcional) Clique em Opções avançadas e, para Criptografia, clique em Chave gerenciada pelo cliente para usar uma chave do Cloud Key Management Service. Se você optar pela configuração Chave gerenciada pelo Google, o BigQuery criptografará os dados em repouso.

  8. Clique em Criar tabela.

Após a criação da tabela, é possível atualizar a validade, a descrição e os rótulos dela. Não é possível adicionar uma validade 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 Criar tabela, na seção Tabela de destino:

    • Em Nome da tabela, escolha o conjunto de dados apropriado e insira, no campo adequado, o nome da tabela que você está criando.
    • Verifique se o Tipo de tabela está definido como Tabela nativa.
  4. Na seção Esquema, insira manualmente a definição do esquema.

    • É possível inserir informações de esquema manualmente ao:

      • clicar em Editar como texto e inserir o esquema da tabela como uma matriz JSON:

        Adicionar esquema como matriz JSON

      • usar Adicionar campo para inserir o esquema:

        Adicionar esquema usando campos de adição

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

  6. Clique em Criar tabela.

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

CLI

Use o comando mk com as sinalizações --table (ou atalho -t) e --time_partitioning_type=DAY. No momento, DAY é o único valor compatível com --time_partitioning_type. É possível fornecer a definição do 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.

Insira o seguinte comando 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 prazo de validade é a soma do horário UTC atual com o valor inteiro. Se você definir o prazo de validade da tabela ao criar uma tabela particionada, a configuração da validade da tabela padrão do conjunto de dados será ignorada. Definir esse valor exclui a tabela e todas as partições após o prazo especificado.
  • integer2 é o ciclo de vida padrão (em segundos) das partições da tabela. Não há valor mínimo. O prazo de validade é a soma da data da partição com o valor inteiro. A validade da partição é independente da validade da tabela, mas não a modifica. Se você definir uma validade de partição maior que a validade da tabela, a validade da tabela prevalecerá.
  • description é uma descrição da tabela entre aspas.
  • key:value é o par de chave-valor que representa um rótulo. Insira vários marcadores 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) ou 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:

Insira o seguinte comando para criar uma tabela particionada por tempo de ingestão com o nome mytable em mydataset no seu projeto padrão. A validade do particionamento está definida como 259.200 segundos (três dias). A validade da tabela é 2.592.000 (um mês de 30 dias). A descrição está configurada 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 está 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 com o nome mytable em myotherproject, que não é o projeto padrão. A validade do particionamento está definida como 259.200 segundos (três dias). A descrição está configurada 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 a validade da tabela. Se o conjunto de dados tiver uma validade de tabela padrão, ela será aplicada. Se o conjunto de dados não tiver uma validade 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 especifique as properties timePartitioning e schema.

Como criar uma tabela particionada por tempo de ingestão a partir de um resultado de consulta

Para criar uma tabela particionada a partir de um resultado de consulta, grave os resultados em uma nova tabela de destino. É possível criar uma nova tabela particionada ao consultar 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 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 clássica da Web do BigQuery, não é possível especificar opções de particionamento para uma tabela de destino.

CLI

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=DAY para criar uma tabela de destino particionada por tempo de ingestão. No momento, 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 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 para seu local.

Insira o comando a seguir para criar uma nova tabela de destino particionada por tempo de ingestão a partir de 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 local. A sinalização --location é opcional. Por exemplo, se estiver usando o BigQuery na região de Tóquio, é possível definir o valor da sinalização como asia-northeast1. É possível definir um valor padrão para o local usando o arquivo .bigqueryr.
  • project_id é o ID do projeto.
  • dataset é o nome do conjunto de dados em que será incluída a tabela particionada.
  • table é o nome da tabela particionada por tempo sendo criada com o uso dos resultados da consulta.
  • query é uma consulta na sintaxe SQL padrão.

Exemplos:

Insira o seguinte comando para gravar os resultados da consulta em uma tabela de destino particionada por tempo de ingestão com o nome mytable em mydataset. O conjunto de dados está no projeto padrão. A consulta recupera os dados de uma tabela não particionada, o conjunto de dados público do USA Name Data (em inglês).

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'
    

Insira o seguinte comando para gravar os resultados da consulta em uma tabela de destino particionada por tempo de ingestão com o nome mytable em mydataset. O conjunto de dados está em myotherproject, que não é o projeto padrão. A consulta recupera os dados de uma tabela não particionada, o conjunto de dados públicos do USA Name Data (em inglês).

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 properties destinationTable e timePartitioning.

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

É possível criar uma tabela particionada por tempo de ingestão 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. É possível criar a tabela particionada e carregar os dados ao mesmo tempo.

Ao carregar dados no BigQuery, é possível 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 dados em uma partição específica. Para ajustar os fusos horários, use um decorador para carregar 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. É necessário que as tabelas fragmentadas por data usem a seguinte convenção de nomenclatura: table_YYYYMMDD. Por exemplo: mytable_20160101, ... , mytable_20160331.

Os parâmetros opcionais incluem --time_partitioning_expiration, --location e --time_partitioning_type. Como --time_partitioning_type=DAY é o único valor compatível no momento, esse parâmetro é opcional. Se as tabelas de origem ou a tabela de destino estiverem em um projeto diferente do padrão, adicione o ID do projeto ao nome do conjunto no seguinte formato: project_id:dataset.

Insira 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 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 .bigqueryr.
  • integer é o ciclo de vida padrão (em segundos) das partições da tabela. Não há valor mínimo. O prazo de validade é a soma da data UTC da partição com o valor do número inteiro. A validade da partição é independente da validade da tabela, mas não a modifica. Se você definir uma validade de partição maior que a validade da tabela, a validade da tabela prevalecerá.
  • 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:

Insira o seguinte comando para criar uma tabela particionada por tempo de ingestão com o nome mytable_partitioned em mydataset no seu projeto padrão. A validade do particionamento está definida como 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 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 a partir 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.

Insira o seguinte comando para criar uma tabela particionada por tempo de ingestão com o nome mytable_partitioned em mydataset. mydataset está em myotherproject, que não é o projeto padrão. A validade do particionamento está definida como 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.

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

Insira o seguinte comando para criar uma tabela particionada por tempo de ingestão com o nome mytable_partitioned em mydataset no seu projeto padrão. mydataset foi criado na região asia-northeast1. A validade do particionamento está definida como 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. Para controlar o acesso à tabela, configure controles de acesso no nível do conjunto de dados ou acima.

Em vez de conceder acesso a conjuntos de dados individuais, é possível atribuir papéis predefinidos do Cloud IAM que concedem permissões no nível do projeto ou acima.

Também é possível criar papéis personalizados. Nesse caso, as permissões concedidas dependerão das operações de tabela que você quer atribuir ao usuário, grupo ou a conta de serviço sejam capazes de executar.

Para mais informações sobre papéis e permissões, consulte:

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:

  • use o Console do Cloud ou a IU da Web clássica do BigQuery;
  • usando o comando bq show da CLI;
  • chame o método da API tables.get;
  • use bibliotecas de cliente.

Permissões exigidas

No mínimo, para ver informações sobre tabelas, é necessário ter as permissões bigquery.tables.get. Os seguintes papéis predefinidos do Cloud IAM incluem permissões bigquery.tables.get:

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

Além disso, se um usuário tiver permissões bigquery.datasets.create, receberá o acesso bigquery.dataOwner ao criar um conjunto de dados. O acesso bigquery.dataOwner permite que o usuário receba informações sobre tabelas em um conjunto de dados.

Para mais informações sobre papéis e permissões do Cloud 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

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

  2. No painel de navegação, na seção Recursos, expanda o 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 complementar _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 complementar _PARTITIONTIME, que contém o carimbo de data/hora para dados que são carregados na tabela.

    Esquema da tabela particionada

CLI

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. É possível usar a sinalização --format para controlar a saída.

Se você estiver recebendo informações sobre uma tabela em um projeto diferente do 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:

Insira 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
    

Insira o comando a seguir para exibir todas as informações sobre mytable em mydataset. mydataset está em myotherproject, que não é o projeto padrão.

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

Insira o comando a seguir para exibir somente informações de esquema sobre mytable em mydataset. mydataset está em myotherproject, que não é o 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 as tabelas particionadas), use o Console do Cloud, 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, no mínimo, as permissões bigquery.tables.list. Os seguintes papéis predefinidos do Cloud IAM incluem permissões bigquery.tables.list:

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

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

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 Cloud.
    Acessar o Console do Cloud

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

  3. Role 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. Role a lista para ver as tabelas no conjunto de dados. Tabelas e visualizações são identificadas por ícones diferentes.

    Visualizar tabelas

CLI

Emita o comando bq ls. É possível usar a sinalização --format para controlar a saída. Se você estiver listando tabelas em um projeto diferente do padrão, adicione o 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 ingestão, 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:

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

bq ls --format=pretty mydataset
    

Insira 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 no Console do Cloud, na IU da Web clássica do BigQuery, usando o comando bq query ou chamando o método jobs.insert e configurando um query job.

Permissões exigidas

Para executar um job de consulta que use a metatabela __PARTITIONS_SUMMARY__ ou a pseudocoluna _PARTITIONTIME, é necessário ter as permissões bigquery.jobs.create. Os seguintes papéis predefinidos do Cloud IAM incluem 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 Cloud IAM incluem permissões bigquery.tables.getData:

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

Para mais informações sobre papéis e permissões do Cloud 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 por tempo de ingestão, use um SQL padrão (preferencial) ou um SQL legado. Para listar partições, siga as instruções abaixo.

SQL padrão:

Console

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

  2. Clique no botão Escrever nova consulta.

  3. Insira o texto a seguir na caixa Editor de consultas para consultar 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.
  4. Opcional: clique em Mais e selecione Configurações de consulta.

    Configurações de consulta

  5. Clique em Executar.

IU clássica

  1. Acesse a IU da Web do BigQuery.

    Acesse a IU da Web do BigQuery

  2. Clique no botão Escrever consulta.

  3. Insira o texto a seguir na caixa Nova consulta para consultar a pseudocoluna _PARTITIONTIME de uma 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 seus dados.

  6. Clique em Executar consulta.

CLI

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, é possível definir o valor da sinalização como asia-northeast1. É possível definir um valor padrão para o local usando o arquivo .bigqueryr.
  • 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

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

  2. Clique no botão Escrever nova consulta.

  3. 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.
  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 selecione o local dos seus dados. É possível deixar o local de processamento configurado para seleção automática se os dados estiverem no local multirregional US ou EU. Quando os dados estiverem nos US ou na 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.

    Acesse a IU da Web do BigQuery

  2. Clique no botão Escrever consulta.

  3. Insira o texto a seguir na caixa Nova consulta para buscar a metatabela __PARTITIONS_SUMMARY__ de uma 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 seus dados.

  6. Clique em Executar consulta.

CLI

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, é possível definir o valor da sinalização como asia-northeast1. É possível definir um valor padrão para o local usando o arquivo .bigqueryr.
  • 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

É possível receber informações sobre tabelas particionadas usando tabelas especiais chamadas metatabelas. Elas 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 com o uso de 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. Para executar a consulta:

  • use o Console do Cloud ou a IU da Web clássica do BigQuery;
  • use o comando bq query da ferramenta de linha de comando;
  • chame o método jobs.insert da API e configurando um job query;
  • use bibliotecas de cliente.

No momento, o SQL padrão não é compatível com o separador do decorador de partições ($). Portanto, não é possível consultar o __PARTITIONS_SUMMARY__ no SQL padrão. Uma consulta no SQL legado que use a metatabela __PARTITIONS_SUMMARY__ será assim:

    SELECT
      column
    FROM
      [dataset.table$__PARTITIONS_SUMMARY__]
    

Em que:

  • dataset é o nome do seu 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 A hora em que a partição foi criada, em milissegundos desde 1º de janeiro de 1970 UTC.
last_modified_time A hora 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 use a metatabela __PARTITIONS_SUMMARY__, é necessário ter, no mínimo, permissões bigquery.jobs.create. Os seguintes papéis predefinidos do Cloud IAM incluem 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 Cloud IAM incluem permissões bigquery.tables.getData:

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

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

Exemplos de metatabela da partição

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

Console

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

IU clássica

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

CLI

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

IU clássica

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

CLI

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

CLI

    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 com o uso de 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 as temperaturas dos três primeiros dias de 2016 no conjunto de dados climáticos GSOD da NOAA e grava os resultados na tabela particionada.

Etapa 1: crie uma tabela particionada por tempo de ingestão vazia com o nome temps em um conjunto de dados com o nome 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.

  1. 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'
        
  2. 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'
        
  3. 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
    

A seguir