Como criar e usar tabelas particionadas

Neste documento, descreveremos como criar e usar tabelas particionadas por uma coluna DATE ou TIMESTAMP. Para informações sobre tabelas particionadas por tempo de ingestão, consulte Como criar e usar tabelas particionadas por tempo de ingestão.

Depois de criar uma tabela particionada, é 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.

Limitações

Tabelas particionadas estão sujeitas às limitações a seguir:

  • A coluna de particionamento precisa ser DATE ou TIMESTAMP escalar. O modo da coluna pode ser REQUIRED ou NULLABLE, mas não pode ser REPEATED (baseado em matriz).
  • A coluna de particionamento precisa ser um campo de nível superior. Não é possível usar um campo de folha de um RECORD (STRUCT) como a coluna de particionamento.
  • Não é possível usar SQL legado para consultar tabelas particionadas ou gravar resultados de consulta nesse tipo de tabela.

Como criar tabelas particionadas

Você pode criar uma tabela particionada no BigQuery:

  • usando o Console do GCP ou a IU da Web clássica;
  • usando uma instrução DDL CREATE TABLE com uma cláusula PARTITION BY contendo uma partition expression;
  • usando manualmente o comando bq mk da ferramenta de linha de comando;
  • de maneira programática, chamando o método de API tables.insert;
  • a partir dos resultados da consulta
  • ao carregar dados.

Nomenclatura de tabelas

Ao criar uma tabela no BigQuery, o nome dela precisa ser exclusivo para cada conjunto de dados. O nome da tabela 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:

  • Permissões bigquery.tables.create para criar a tabela
  • bigquery.tables.updateData para gravar dados na tabela usando um job de carregamento, consulta ou cópia
  • bigquery.jobs.create para executar um job de consulta, carregamento ou cópia que grava 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 papéis predefinidos do Cloud IAM a seguir incluem as permissões bigquery.tables.create e bigquery.tables.updateData:

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

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

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

Além disso, quando um usuário com permissões bigquery.datasets.create cria um conjunto de dados, ele recebe acesso bigquery.dataOwner ao conjunto. O acesso bigquery.dataOwner dá ao usuário a capacidade de criar e atualizar tabelas no conjunto de dados.

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

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

Não é possível criar uma tabela particionada vazia sem uma definição de esquema. O esquema é necessário para identificar a coluna usada para criar as partições.

Ao criar uma tabela particionada vazia com uma definição de esquema, você pode:

  • fornecer o esquema in-line usando a CLI;
  • especificar um arquivo de esquema JSON usando a CLI;
  • 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, é possível:

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

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

Console

  1. Na seção Recursos do painel de navegação, expanda o projeto e selecione o conjunto de dados.

  2. No lado direito da janela, no painel de detalhes, clique em Criar tabela.

  3. Na seção Origem do painel Criar tabela:

    • Selecione Tabela vazia em Criar tabela de.

  4. Na seção Destino:

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

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

  6. Em Configurações de partição e cluster, clique em Sem particionamento, selecione Particionar por campo e escolha a coluna DATE ou TIMESTAMP. A opção estará indisponível se o esquema não incluir uma coluna DATE ou TIMESTAMP.

  7. (Opcional) Em Filtro de particionamento, clique na caixa Exigir filtro de partição 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 partição reduz os custos e melhora o desempenho. Para mais informações, consulte Como consultar tabelas particionadas.

  8. (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.

  9. Clique em Criar tabela.

DDL

Com as instruções de linguagem de definição de dados (DDL, na sigla em inglês), é possível criar e modificar tabelas e visualizações usando a sintaxe de consulta do SQL padrão.

Saiba mais sobre Como usar as instruções de Linguagem de Definição de Dados.

Para criar uma tabela particionada usando uma instrução DDL no Console do GCP:

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

  2. Clique em Escrever nova consulta.

  3. Digite a instrução DDL CREATE TABLE na área de texto do Editor de consultas.

    A consulta a seguir cria uma tabela denominada newtable que é particionada pela coluna transaction_date DATE e tem uma expiração de partição de três dias.

     CREATE TABLE
   mydataset.newtable (transaction_id INT64,
     transaction_date DATE)
 PARTITION BY
   transaction_date
 OPTIONS
   ( partition_expiration_days=3,
     description="a table partitioned by transaction_date" )

  4. Clique em Executar. Quando a consulta for concluída, a tabela será exibida no painel Recursos.

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 em branco.

  3. Na página Criar tabela, na seção Tabela de destino:

    • Em Nome da tabela, 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á definido 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 uma matriz JSON.

      • Use Adicionar campo para inserir o esquema.

  5. Na seção Opções, faça o seguinte:

    • Em Tipo de particionamento, clique em Nenhum e escolha Dia.
    • Em Campo de particionamento, selecione a coluna TIMESTAMP ou DATE. O valor padrão é _PARTITIONTIME, que cria uma tabela particionada por tempo de ingestão.
    • (Opcional) Clique na caixa Exigir filtro de partição para exigir que os usuários incluam uma cláusula WHERE que especifique as partições a serem consultadas. A exigência de um filtro de partição reduz os custos e melhora o desempenho. Para mais informações, consulte Como consultar tabelas particionadas.
    • (Opcional) Em Criptografia de destino, escolha Criptografia gerenciada pelo cliente para usar uma chave do Cloud Key Management Service para criptografar a tabela. Se você optar pela configuração Default, o BigQuery criptografará os dados em repouso usando uma chave gerenciada pelo Google.

  6. Clique em Criar tabela.

CLI

Use o comando mk com as sinalizações --table (ou atalho -t), --schema e --time_partitioning_field. 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, --require_partition_filter e --label. Atualmente, DAY é o único valor compatível com --time_partitioning_type, de modo que essa sinalização não é necessária.

Se você estiver 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 Como proteger dados com chaves do Cloud KMS.

Digite o comando a seguir para criar uma tabela particionada vazia com uma definição de esquema:

bq mk --table \
--expiration integer1 \
--schema schema \
--time_partitioning_field column \
--time_partitioning_expiration integer2 \
--[no]require_partition_filter \
--description "description" \
--label key:value, key:value \
project_id:dataset.table

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 é a soma do horário UTC atual com o valor inteiro. Se você definir o tempo de expiração ao criar uma tabela particionada por tempo, a configuração padrão de expiração da tabela do conjunto de dados será ignorada. Definir esse valor exclui a tabela e todas as partições após o tempo especificado.
  • 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 na máquina local.
  • column é o nome da coluna TIMESTAMP ou DATE usada para criar as partições.
  • integer2 é a vida útil padrão, em segundos, das partições da tabela. Não há valor mínimo. O tempo de expiração é determinado pela data da partição acrescida do valor do número inteiro. A expiração da partição é independente da expiração da tabela, mas não a modifica. Se você definir uma expiração de partição maior 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 key:value que representa um rótulo. Você pode inserir 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 que você está criando.

Quando você especifica o esquema na linha de comando, não há como incluir um tipo RECORD (STRUCT), nem uma descrição de coluna, nem especificar o modo da coluna. O padrão de todos os modos é NULLABLE. 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 chamada mypartitionedtable em mydataset no projeto padrão. A expiração do particionamento é definida para 86.400 segundos (um dia), a expiração da tabela é definida para 2.592.000 (um mês de 30 dias), a descrição é definida para This is my partitioned table e o rótulo é definido como organization:development. O comando usa o atalho -t em vez de --table.

A sinalização --require_partition_filter é usada para exigir que os usuários incluam uma cláusula WHERE que especifica as partições a serem consultadas. A exigência de um filtro de partição reduz os custos e melhora o desempenho. Para mais informações, consulte Como consultar tabelas particionadas.

O esquema é especificado in-line como: ts:TIMESTAMP,column1:STRING,column2:INTEGER,coumn4:STRING. O campo TIMESTAMP ts especificado é usado para criar as partições.

bq mk -t \
--expiration 2592000 \
--schema 'ts:TIMESTAMP,column1:STRING,column2:INTEGER,coumn4:STRING' \
--time_partitioning_field ts \
--time_partitioning_expiration 86400  \
--require_partition_filter \
--description "This is my partitioned table" \
--label org:dev \
mydataset.mypartitionedtable

Digite o comando a seguir para criar uma tabela particionada chamada mypartitionedtable 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 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 a expiração da tabela. Se o conjunto de dados tiver uma expiração de tabela padrão, ela será aplicada. Caso contrário, a tabela nunca expirará, mas as partições expirarão em três dias.

O esquema é especificado em um arquivo JSON local: /tmp/myschema.json. A definição do esquema inclui um campo TIMESTAMP chamado ts que é usado para criar as partições.

bq mk -t \
--expiration 2592000 \
--schema /tmp/myschema.json \
--time_partitioning_field ts \
--time_partitioning_expiration 86400  \
--description "This is my partitioned table" \
--label org:dev \
myotherproject:mydataset.mypartitionedtable

Depois da criação da tabela, use a CLI para atualizar a expiração da tabela, a expiração da partição, a descrição e os rótulos da tabela particionada.

API

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

Go

Antes de testar esta amostra, siga as instruções de configuração do Go no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery Go. 

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
sampleSchema := bigquery.Schema{
	{Name: "name", Type: bigquery.StringFieldType},
	{Name: "post_abbr", Type: bigquery.IntegerFieldType},
	{Name: "date", Type: bigquery.DateFieldType},
}
metadata := &bigquery.TableMetadata{
	TimePartitioning: &bigquery.TimePartitioning{
		Field:      "date",
		Expiration: 90 * 24 * time.Hour,
	},
	Schema: sampleSchema,
}
tableRef := client.Dataset(datasetID).Table(tableID)
if err := tableRef.Create(ctx, metadata); err != nil {
	return err
}

Python

Antes de testar esta amostra, siga as instruções de configuração do Python em Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery Python. 

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_ref = client.dataset('my_dataset')

table_ref = dataset_ref.table("my_partitioned_table")
schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
    bigquery.SchemaField("date", "DATE"),
]
table = bigquery.Table(table_ref, schema=schema)
table.time_partitioning = bigquery.TimePartitioning(
    type_=bigquery.TimePartitioningType.DAY,
    field="date",  # name of column to use for partitioning
    expiration_ms=7776000000,
)  # 90 days

table = client.create_table(table)

print(
    "Created table {}, partitioned on column {}".format(
        table.table_id, table.time_partitioning.field
    )
)

Como criar tabelas particionadas com base nos resultados da consulta

Para criar uma tabela particionada de um resultado de consulta, grave os resultados em uma nova tabela de destino. Você pode criar uma tabela particionada consultando uma tabela particionada ou não particionada. Não é possível alterar uma tabela padrão existente para uma tabela particionada usando os resultados da consulta.

Quando você cria uma tabela particionada de um resultado de consulta, é necessário usar o SQL padrão. Atualmente, SQL legado não é aceito para consultar tabelas particionadas ou para gravar resultados de consulta em tabelas particionadas.

Os decoradores de partição permitem que você grave os resultados da consulta em uma partição específica. Por exemplo, para gravar os resultados na partição de 1º de maio de 2016, use o seguinte decorador de partição:

table_name$20160501

Ao gravar resultados de consulta em uma partição específica usando um decorador de partição, os dados que estão sendo gravados na partição precisam estar de acordo com o esquema de particionamento da tabela. Todas as linhas escritas na partição precisam ter valores que se enquadrem na data dela.

Por exemplo:

A consulta a seguir recupera dados de 1º de fevereiro de 2018 e grava os dados na partição $20180201 da tabela mytable. A tabela tem duas colunas: uma coluna TIMESTAMP, chamada TS, e uma coluna INT64, chamada a.

bq query \
--nouse_legacy_sql  \
--destination_table=mytable$20180201 \
'SELECT
   TIMESTAMP("2018-02-01") AS TS,
   2 AS a'

A consulta a seguir recupera dados de 31 de janeiro de 2018 e tenta gravá-los na partição $20180201 da tabela mytable. Essa consulta falha porque os dados que você está tentando gravar não se enquadram na data da partição.

bq query \
--nouse_legacy_sql  \
--destination_table=T$20180201 \
'SELECT
   TIMESTAMP("2018-01-31") as TS,
   2 as a'

Para saber como anexar ou redefinir (substituir) dados em tabelas particionadas, consulte Como anexar e substituir dados de tabela particionada por tempo. Para mais informações sobre como consultar tabelas particionadas, consulte Como consultar tabelas particionadas.

Como criar uma tabela particionada com base em um resultado de consulta

Para criar uma tabela particionada com base no resultado de uma consulta:

Console

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

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_field para criar uma tabela de destino particionada. Atualmente, DAY é o único valor compatível com --time_partitioning_type, de modo que essa sinalização não é necessária.

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 código 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 o local.

Digite o comando a seguir para criar uma nova tabela particionada de destino de um resultado de consulta:

bq --location=location query \
--destination_table project_id:dataset.table \
--time_partitioning_field column \
--use_legacy_sql=false \
'query'

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.
  • 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 que você está criando com base nos resultados da consulta.
  • query é uma consulta na sintaxe SQL padrão. No momento, não é possível usar SQL legado para consultar tabelas particionadas ou gravar os resultados da consulta em tabelas particionadas.

Exemplos:

Digite o comando a seguir para gravar os resultados da consulta em uma tabela de destino particionada denominada mypartitionedtable em mydataset. mydataset está no projeto padrão. A consulta recupera dados de uma tabela não particionada, o conjunto de dados públicos NHTSA Traffic Fatality. A coluna timestamp_of_crash TIMESTAMP da tabela é usada para criar as partições.

bq query \
--destination_table mydataset.mypartitionedtable \
--time_partitioning_field timestamp_of_crash \
--use_legacy_sql=false \
'SELECT
   state_number,
   state_name,
   day_of_crash,
   month_of_crash,
   year_of_crash,
   latitude,
   longitude,
   manner_of_collision,
   number_of_fatalities,
   timestamp_of_crash
 FROM
   `bigquery-public-data`.nhtsa_traffic_fatalities.accident_2016
 LIMIT
   100'

Digite o comando a seguir para gravar os resultados da consulta em uma tabela de destino particionada chamada mypartitionedtable em mydataset. mydataset 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 NHTSA Traffic Fatality. A coluna timestamp_of_crash TIMESTAMP da tabela é usada para criar as partições.

bq query \
--destination_table myotherproject:mydataset.mypartitionedtable \
--time_partitioning_field timestamp_of_crash \
--use_legacy_sql=false \
'SELECT
   state_number,
   state_name,
   day_of_crash,
   month_of_crash,
   year_of_crash,
   latitude,
   longitude,
   manner_of_collision,
   number_of_fatalities,
   timestamp_of_crash
 FROM
   `bigquery-public-data`.nhtsa_traffic_fatalities.accident_2016
 LIMIT
   100'

API

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

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

Como criar uma tabela particionada ao carregar dados

Você pode criar uma tabela particionada 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.

Com os decoradores de partição, carregue os dados em uma partição específica. Por exemplo, para carregar todos os dados gerados em 1º de maio de 2016 na partição 20160501, use o seguinte decorador de partição:

table_name$20160501

Ao carregar dados em uma partição específica usando um decorador de partição, os dados que estão sendo carregados na partição precisam estar em conformidade com o esquema de particionamento da tabela. Todas as linhas escritas na partição precisam ter valores que se enquadrem na data dela.

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

Como controlar o acesso a tabelas particionadas

Não é possível atribuir controles de acesso diretamente a tabelas ou visualizações. O nível mais baixo de recursos do BigQuery aos quais é possível conceder acesso é o nível do conjunto de dados. Para configurar o acesso a tabelas e visualizações, conceda um papel do Cloud IAM a uma entidade no nível do conjunto de dados ou acima.

A atribuição de um papel no nível do conjunto de dados especifica as operações que uma entidade tem permissão para executar em tabelas e visualizações nesse conjunto de dados específico. Para informações sobre a configuração de controles de acesso no nível do conjunto de dados, consulte Como controlar o acesso a conjuntos de dados.

Também é possível conceder papéis do Cloud IAM em um nível superior na hierarquia de recursos do Google Cloud Platform, como os níveis de projeto, pasta ou organização. A concessão de papéis em um nível mais alto dá à entidade acesso a um conjunto mais amplo de recursos. Por exemplo, a concessão de um papel a uma entidade no nível do projeto concede a ela permissões referentes a todos os conjuntos de dados em todo o projeto. Para mais informações sobre a concessão de acesso a recursos, consulte Como conceder, alterar e revogar o acesso a recursos na documentação do Cloud IAM.

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

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

Como usar tabelas particionadas

Como conseguir informações sobre tabelas particionadas

Para conseguir informações sobre tabelas, faça o seguinte:

  • Use o Console do GCP ou a IU da Web clássica do BigQuery.
  • Use o comando bq show da CLI.
  • Chame o método de API tables.get.
  • Use as bibliotecas de cliente.

Permissões exigidas

Para receber informações sobre tabelas, você precisa, no mínimo, ter permissões bigquery.tables.get. Os papéis predefinidos do Cloud IAM a seguir incluem permissões bigquery.tables.get:

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

Além disso, quando um usuário com permissões bigquery.datasets.create cria um conjunto de dados, ele recebe acesso bigquery.dataOwner ao conjunto. O acesso bigquery.dataOwner permite que o usuário recupere metadados da tabela.

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

Como conseguir informações da tabela particionada

Para ver informações sobre uma tabela particionada:

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 conjunto de dados, depois clique no nome da tabela na lista.

  3. Clique em Detalhes abaixo da opção Editor de consultas. Essa guia exibe a descrição da tabela e as informações da tabela.

    Detalhes da tabela

  4. Clique na guia Esquema para ver a definição do esquema da tabela. Observe que tabelas particionadas não incluem a pseudocoluna _PARTITIONTIME.

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.

  4. Clique na guia Esquema para ver a definição do esquema da tabela. Observe que tabelas particionadas não incluem a pseudocoluna _PARTITIONTIME.

CLI

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

O esquema será semelhante a este:

{
  "creationTime": "1563236533535",
  "description": "This is my partitioned table",
  "etag": "/ABcDEo7f8GHijKL2mnOpQr==",
  "expirationTime": "1565828533000",
  "id": "myproject:mydataset.mypartitionedtable",
  "kind": "bigquery#table",
  "labels": {
    "org": "dev"
  },
  "lastModifiedTime": "1563236533576",
  "location": "US",
  "numBytes": "0",
  "numLongTermBytes": "0",
  "numRows": "0",
  "requirePartitionFilter": true,
  "schema": {
    "fields": [
      {
        "name": "ts",
        "type": "TIMESTAMP"
      },
      {
        "name": "column1",
        "type": "STRING"
      },
      {
        "name": "column2",
        "type": "INTEGER"
      },
      {
        "name": "column3",
        "type": "STRING"
      }
    ]
  },
  "selfLink": "https://bigquery.googleapis.com/bigquery/v2/projects/myproject/datasets/mydataset/tables/mypartitionedtable",
  "tableReference": {
    "datasetId": "mydataset",
    "projectId": "myproject",
    "tableId": "mypartitionedtable"
  },
  "timePartitioning": {
    "expirationMs": "86400000",
    "field": "ts",
    "requirePartitionFilter": true,
    "type": "DAY"
  },
  "type": "TABLE"
}

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.

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

O esquema será semelhante a este: 

[
  {
    "name": "ts",
    "type": "TIMESTAMP"
  },
  {
    "name": "column1",
    "type": "STRING"
  },
  {
    "name": "column2",
    "type": "INTEGER"
  },
  {
    "name": "column3",
    "type": "STRING"
  }
]

API

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

Como listar tabelas particionadas em um conjunto de dados

Use estes métodos para listar tabelas em conjuntos de dados (incluindo tabelas particionadas):

  • Use o Console do GCP ou a IU da Web clássica do BigQuery.
  • Use o comando bq ls da CLI.
  • Chame o método de API tables.list.
  • Use as bibliotecas de cliente.

Permissões exigidas

Para listar tabelas em um conjunto de dados, é necessário ter, no mínimo, permissões bigquery.tables.list. Os papéis predefinidos do Cloud IAM a seguir incluem permissões bigquery.tables.list:

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

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

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 do BigQuery, 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. As 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 ID 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 tiver sido especificado.

Por exemplo:

+-------------------------+-------+----------------------+---------------------------------------------------+
|         tableId         | Type  |        Labels        | Time Partitioning                                 |
+-------------------------+-------+----------------------+---------------------------------------------------+
| mytable                 | TABLE | department:shipping  |  DAY (field: source_date, expirationMs: 86400000) |
| 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

Para listar as partições em uma tabela particionada, consulte a metatabela __PARTITIONS_SUMMARY__ usando o 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__, é necessário ter, no mínimo, permissões bigquery.jobs.create. Os papéis predefinidos do Cloud IAM a seguir incluem as permissões bigquery.jobs.create:

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

Você também precisa ter permissões bigquery.tables.getData. Os papéis predefinidos do Cloud IAM a seguir incluem as permissões bigquery.tables.getData:

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

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

Como listar partições em uma tabela particionada

Você pode listar partições em uma tabela particionada usando SQL legado. Para listar partições em uma tabela particionada:

Console

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

  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 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. Insira o texto a seguir na caixa Nova consulta 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 Mostrar opções.

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

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

API

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

Como conseguir metadados das tabelas particionadas 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.

No momento, não é possível usar o serviço INFORMATION_SCHEMA para conseguir metadados da tabela particionada.

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 das seguintes maneiras:

  • Use o Console do GCP ou a IU da Web clássica do BigQuery.
  • Use o comando bq query da ferramenta de linha de comando.
  • Chame o método de API jobs.insert e configure um job de query.
  • Use as bibliotecas de cliente.

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 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, 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 usa a metatabela __PARTITIONS_SUMMARY__, é necessário ter, no mínimo, permissões bigquery.jobs.create. Os papéis predefinidos do Cloud IAM a seguir incluem as permissões bigquery.jobs.create:

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

Você também precisa ter permissões bigquery.tables.getData. Os papéis predefinidos do Cloud IAM a seguir incluem as permissões bigquery.tables.getData:

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

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

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

CLI 

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

O resultado será semelhante ao mostrado a seguir:

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

O resultado será semelhante ao mostrado a seguir:

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

O resultado será semelhante ao mostrado a seguir:

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

Próximas etapas

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.