Como criar e usar tabelas particionadas por coluna de unidade de tempo

Neste documento, descrevemos como criar e usar tabelas particionadas por uma coluna DATE, DATETIME ou TIMESTAMP. Para informações sobre tabelas particionadas por tempo de processamento, consulte Como criar e usar tabelas particionadas por tempo de processamento. Para mais informações sobre tabelas particionadas por intervalo de números inteiros, consulte Como criar e usar tabelas particionadas por intervalo de números inteiros.

Depois de criar uma tabela particionada, é possível:

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

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

Limitações

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

A coluna de particionamento precisa ser DATE, TIMESTAMP ou DATETIME. 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 um SQL legado para consultar tabelas particionadas ou gravar resultados da consulta nesse tipo de tabela.

As tabelas particionadas por hora, mês e ano estão sujeitas a outras limitações:

O comando bq partition na ferramenta de linha de comando bq não é compatível.

Como criar tabelas particionadas

É possível criar uma tabela particionada das seguintes maneiras:

usando o Console do Cloud;
usando uma instrução DDL CREATE TABLE com uma cláusula PARTITION BY que contém uma partition expression;
usando o comando bq mk da ferramenta de linha de comando bq;
programaticamente, chamando o método da 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. Esse nome pode:

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

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

Permissões necessárias

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

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

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

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

bigquery.dataEditor
bigquery.dataOwner
bigquery.admin

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

bigquery.user
bigquery.jobUser
bigquery.admin

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

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

Particionamento diário versus particionamento por hora, mensal ou anual

Ao usar uma coluna TIMESTAMP, DATETIME ou DATE para particionar dados, é possível criar partições com granularidade por dia, hora, mês ou ano, dependendo dos dados e necessidades.

O particionamento diário é o tipo de particionamento padrão e, quando usado com clustering, atende à maioria dos casos de uso do BigQuery. Em particular, o particionamento diário é a melhor escolha quando os dados são distribuídos por um grande intervalo de datas, ou se os dados são adicionados continuamente ao longo do tempo. Se os dados abrangerem uma grande variedade de datas, o particionamento diário permitirá que você permaneça abaixo dos limites de partição da tabela.

Opte pelo particionamento por hora se as tabelas tiverem um grande volume de dados que abranja um período curto (geralmente menos de seis meses de valores de carimbo de data/hora). Com o particionamento por hora, é possível processar dados em granularidade por hora; por exemplo, ao anexar, truncar ou excluir dados de uma partição específica.

Escolha o particionamento mensal ou anual se suas tabelas tiverem uma quantidade de dados relativamente pequena para cada dia, mas abrangendo um período amplo. Essa opção de particionamento também é recomendada se seu fluxo de trabalho exigir atualização frequente ou adição de linhas que abrangem um período amplo (por exemplo, mais de 500 datas). Use o particionamento mensal ou anual, além do clustering na sua coluna de particionamento de carimbo de data/hora, data ou data e hora para obter um melhor desempenho nesses cenários. Consulte Particionamento de unidade de tempo com clustering para mais detalhes e exemplos.

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, é possível:

fornecer o esquema in-line usando a ferramenta de linha de comando bq.
especificar um arquivo de esquema JSON usando a ferramenta de linha de comando bq;
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

Na seção Recursos do painel de navegação, expanda o projeto e selecione o conjunto de dados.
No lado direito da janela, no painel de detalhes, clique em Criar tabela.
Na seção Origem do painel Criar tabela:
- Selecione Tabela vazia em Criar tabela de.
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.
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.
    Observação: é possível ver o esquema de uma tabela atual no formato JSON ao digitar o seguinte comando na ferramenta de linha de comando bq: bq show --format=prettyjson dataset.table.
  - Use Adicionar campo para inserir manualmente o esquema.
Em Configurações de partição e cluster, clique em Sem partição, selecione Partição por campo e escolha a coluna DATE, TIMESTAMP ou DATETIME. Esta opção estará indisponível se o esquema não incluir uma coluna DATE, TIMESTAMP ou DATETIME.
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 partição pode reduzir os custos e melhorar o desempenho. Para mais informações, consulte Como consultar tabelas particionadas.
Opcional: clique em Opções avançadas e, em Criptografia, clique em Chave gerenciada pelo cliente para usar uma chave do Cloud Key Management Service. Se você escolher a configuração Chave gerenciada pelo Google, o BigQuery criptografará os dados em repouso.
Clique em Criar tabela.

SQL

Com as instruções de linguagem de definição de dados (DDL), é 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 da linguagem de definição de dados.

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

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

Acesse a página do BigQuery
Clique em Escrever nova consulta.

Digite a instrução CREATE TABLEDDL na área de texto do editor de consulta.

A consulta a seguir cria uma tabela chamada newtable que é particionada diariamente pela coluna transaction_date DATE e tem uma validade 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" )

A consulta a seguir cria a mesma tabela, mas é particionada por hora. Observe o uso de TIMESTAMP_TRUNC para delinear o carimbo de data/hora na marca de hora:

 CREATE TABLE
   mydataset.newtable (transaction_id INT64,
     transaction_ts TIMESTAMP)
 PARTITION BY
   TIMESTAMP_TRUNC(transaction_ts, HOUR)
 OPTIONS
   ( partition_expiration_days=3,
     description="a table partitioned by transaction_ts" )

A consulta a seguir cria a mesma tabela, mas é particionada por mês. Observe o uso de TIMESTAMP_TRUNC para delinear o carimbo de data/hora na marca de mês:

 CREATE TABLE
   mydataset.newtable (transaction_id INT64,
     transaction_ts TIMESTAMP)
 PARTITION BY
   TIMESTAMP_TRUNC(transaction_ts, MONTH)
 OPTIONS
   ( partition_expiration_days=3,
     description="a table partitioned by transaction_ts" )

A consulta a seguir cria a mesma tabela, mas é particionada por ano. Observe o uso de TIMESTAMP_TRUNC para delinear o carimbo de data/hora na marca de ano.

 CREATE TABLE
   mydataset.newtable (transaction_id INT64,
     transaction_ts TIMESTAMP)
 PARTITION BY
   TIMESTAMP_TRUNC(transaction_ts, YEAR)
 OPTIONS
   ( partition_expiration_days=3,
     description="a table partitioned by transaction_ts" )

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

bq

Use o comando bq mk com as sinalizações --table (ou o atalho -t), --schema ou --time_partitioning_field. É possível 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, --time_partitioning_type, 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 como usar essa sinalização, veja Como proteger dados com chaves do Cloud Key Management Service.

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_type unit_time \
--time_partitioning_expiration integer2 \
--[no]require_partition_filter \
--description "description" \
--label key:value, key:value \
project_id:dataset.table

Substitua:

integer1 é a vida útil padrão da tabela em segundos. 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 unidade de 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 é uma 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, DATETIME ou DATE usada para criar as partições.
unit_time é DAY, HOUR, MONTH ou YEAR, com base na granularidade de particionamento de unidade de tempo esperada; O padrão é DAY se time_partitioning_type não for especificado.
integer2 é a vida útil padrão (em segundos) para as 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. É possível inserir vários rótulos usando uma lista separada por vírgulas;
project_id é o ID do projeto.
dataset é um conjunto de dados no projeto;
table é o nome da tabela particionada que você está criando.

Ao especificar o esquema usando a ferramenta de linha de comando bq, 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 comando a seguir para criar uma tabela particionada por hora chamada mypartitionedtable em mydataset no projeto padrão. A expiração do particionamento é configurada para 86.400 segundos (um dia), a expiração da tabela é configurada para 2.592.000 (um mês de 30 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.

A sinalização --require_partition_filter é usada para solicitar aos usuários que incluam uma cláusula WHERE que especifique as partições de uma consulta. A exigência de um filtro de partição pode reduzir os custos e melhorar o desempenho. Para mais informações, veja Como consultar tabelas particionadas.

O esquema é especificado in-line como: ts:TIMESTAMP,column1:STRING,column2:INTEGER,coumn4:STRING O campo TIMESTAMP especificado ts é usado para particionar os dados por hora. O particionamento por hora requer uma coluna TIMESTAMP ou DATETIME em vez de uma coluna DATE.

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

Insira o comando a seguir para criar uma tabela particionada diária chamada mypartitionedtable em myotherproject, 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 particionar os dados por dia.

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

Após a criação da tabela, é possível usar a ferramenta de linha de comando bq para atualizar a expiração da tabela, expiração da partição, descrição e os rótulos da tabela particionada.

API

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

Go

Antes de testar essa amostra, siga as instruções de configuração para 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 em Go.

Ver no GitHub Feedback

import (
	"context"
	"fmt"
	"time"

	"cloud.google.com/go/bigquery"
)

// createTablePartitioned demonstrates creating a table and specifying a time partitioning configuration.
func createTablePartitioned(projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydatasetid"
	// tableID := "mytableid"
	ctx := context.Background()

	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	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
	}
	return nil
}

Java

Antes de testar essa amostra, siga as instruções de configuração para Java 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 em Java.

Ver no GitHub Feedback

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.StandardTableDefinition;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;
import com.google.cloud.bigquery.TimePartitioning;

public class CreatePartitionedTable {

  public static void runCreatePartitionedTable() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    createPartitionedTable(datasetName, tableName);
  }

  public static void createPartitionedTable(String datasetName, String tableName) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, tableName);

      TimePartitioning partitioning = TimePartitioning.of(TimePartitioning.Type.DAY);

      Schema schema =
          Schema.of(
              Field.of("stringField", StandardSQLTypeName.STRING),
              Field.of("booleanField", StandardSQLTypeName.BOOL),
              Field.of("dateField", StandardSQLTypeName.DATE));

      StandardTableDefinition tableDefinition =
          StandardTableDefinition.newBuilder()
              .setSchema(schema)
              .setTimePartitioning(partitioning)
              .build();
      TableInfo tableInfo = TableInfo.newBuilder(tableId, tableDefinition).build();

      bigquery.create(tableInfo);
      System.out.println("Partitioned table created successfully");
    } catch (BigQueryException e) {
      System.out.println("Partitioned table was not created. \n" + e.toString());
    }
  }
}

Node.js

Antes de testar essa amostra, siga as instruções de configuração para Node.js 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 Node.js.

Ver no GitHub Feedback

// Import the Google Cloud client library
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function createTablePartitioned() {
  // Creates a new partitioned table named "my_table" in "my_dataset".

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = "my_dataset";
  // const tableId = "my_table";
  const schema = 'Name:string, Post_Abbr:string, Date:date';

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    schema: schema,
    location: 'US',
    timePartitioning: {
      type: 'DAY',
      expirationMS: '7776000000',
      field: 'date',
    },
  };

  // Create a new table in the dataset
  const [table] = await bigquery
    .dataset(datasetId)
    .createTable(tableId, options);
  console.log(`Table ${table.id} created with partitioning: `);
  console.log(table.metadata.timePartitioning);
}

Python

Antes de testar essa amostra, siga as instruções de configuração para Python 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 em Python.

Ver no GitHub Feedback

# from google.cloud import bigquery
# client = bigquery.Client()
# project = client.project
# dataset_ref = bigquery.DatasetReference(project, '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 informações sobre como anexar ou redefinir (substituir) dados em tabelas particionadas, consulte Como anexar e substituir dados de tabela particionada. 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 as opções de particionamento de uma tabela de destino ao consultar dados usando o Console do Cloud.

bq

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

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

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

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

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

Substitua:

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 que conterá a nova tabela particionada;
table é o nome da tabela particionada que você está criando com base nos resultados da consulta;
column é o nome da coluna TIMESTAMP ou DATE usada para criar as partições.
unit_time é DAY, HOUR, MONTH ou YEAR, com base na granularidade de particionamento de unidade de tempo esperada; O padrão é DAY se time_partitioning_type não for especificado.
query é uma consulta na sintaxe SQL padrão. Não é possível usar um SQL legado para consultar tabelas particionadas ou gravar resultados da consulta nesse tipo de tabela.

Exemplos:

Digite o comando a seguir para gravar os resultados da consulta em uma tabela de destino particionada denominada mypartitionedtable em mydataset. mydataset está em seu 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 denominada mypartitionedtable em mydataset. mydataset está em myotherproject, 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 particionada permanente, utilize o método jobs.insert, configure um job query e inclua um valor para o destinationTable e as propriedades timePartitioning.

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

Como criar uma tabela particionada ao carregar os dados

É possível 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.

Particionamento por unidade de tempo com clustering

O particionamento de unidade de tempo pode ser usado com o clustering. Uma tabela particionada por unidade de tempo com clustering primeiro particiona seus dados de acordo com os limites de unidade de tempo (dia, hora, mês ou ano) da coluna de particionamento. Depois, em cada limite de partição, os dados são agrupados ainda mais pelas colunas em cluster.

Como exemplo, este comando cria uma tabela com uma coluna particionada diária e um cluster.

    bq mk --time_partitioning_type=DAY \
    --time_partitioning_field=ts_column \
    --clustering_fields=column1,column2 \
    mydataset.mytable2 "ts_column:TIMESTAMP,column1:INTEGER,column2:STRING"

Ao recuperar o formato da tabela, você verá que o clustering e o particionamento por carimbo de data/hora por dia estão em vigor:

    bq show --format=prettyjson mydataset.mytable2
    ...
      "clustering": {
        "fields": [
          "column1",
          "column2"
        ]
      },
    ...
      "timePartitioning": {
        "field": "ts_column",
        "type": "DAY"
      },
    ...

Se você estiver executando acima do limite de número de partições por tabela ou se tiver poucos dados propagados em muitas partições e estiver modificando-os com muita frequência, considere usar um particionamento de unidade de tempo com intervalo maior com clustering na mesma coluna de particionamento. Essa é a maneira recomendada de usar as tabelas particionadas para permanecer dentro dos limites de partição.

Por exemplo, este comando cria uma tabela particionada diária e em cluster na mesma coluna:

    bq mk --time_partitioning_type=DAY \
    --time_partitioning_field=ts_column \
    --clustering_fields=ts_column,column1 \
    mydataset.mytable2 "ts_column:TIMESTAMP,column1:INTEGER,column2:STRING"

Veja outro exemplo para a tabela acima, mas com um intervalo de particionamento de unidade de tempo maior:

    bq mk --time_partitioning_type=MONTH \
    --time_partitioning_field=ts_column \
    --clustering_fields=ts_column,column1 \
    mydataset.mytable2 "ts_column:TIMESTAMP,column1:INTEGER,column2:STRING"

Como controlar o acesso a tabelas particionadas

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

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

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

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

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

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

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

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

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

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

Como usar tabelas particionadas

Como conseguir informações sobre tabelas particionadas

É possível conseguir informações sobre tabelas das seguintes maneiras:

usando o Console do Cloud;
use o comando bq show na ferramenta de linha de comando bq.
faça uma chamada do método de API tables.get;
usando as bibliotecas de cliente.

Permissões necessárias

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

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

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

Como conseguir informações da tabela particionada

Para ver informações sobre uma tabela particionada:

Console

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

Acesse a página do BigQuery
No painel de navegação, na seção Recursos, expanda seu projeto e conjunto de dados, depois clique no nome da tabela na lista.
Clique em Detalhes abaixo da opção Editor de consultas. Essa aba exibe a descrição da tabela e as informações da tabela.
Clique na guia Esquema para ver a definição do esquema da tabela. Observe que tabelas particionadas não incluem a pseudocoluna _PARTITIONTIME.

bq

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

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

bq show --schema --format=prettyjson project_id:dataset.table

Substitua:

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

Exemplos:

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

bq show --format=prettyjson mydataset.mytable

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

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

A saída será semelhante a esta:

{
  "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 apenas informações de esquema sobre mytable em mydataset. mydataset está em myotherproject, não no projeto padrão.

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

A saída será semelhante a esta:

[
  {
    "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

É possível listar tabelas em conjuntos de dados (incluindo tabelas particionadas) das seguintes maneiras:

usando o Console do Cloud;
use o comando bq ls na ferramenta de linha de comando bq.
faça uma chamada do método de API tables.list;
usando as bibliotecas de cliente.

Permissões necessárias

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

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

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

Como listar tabelas particionadas

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

Console

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

Acesse a página do BigQuery
No painel de navegação, na seção Recursos, expanda seu projeto e clique no conjunto de dados.
Role pela lista para ver as tabelas no conjunto de dados. Tabelas, tabelas particionadas, modelos e visualizações são identificados por diferentes ícones.

bq

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

bq ls --format=pretty project_id:dataset

Substitua:

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

Ao executar o comando, o campo Type exibe VIEW ou Time Partitioning. Em tabelas particionadas por tempo de ingestão, o campo TABLE 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 (field: source_date, expirationMs: 86400000) |
| myview                  | VIEW  |                      |                                                   |
+-------------------------+-------+----------------------+---------------------------------------------------+

Exemplos:

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

bq ls --format=pretty mydataset

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

bq ls --format=pretty myotherproject:mydataset

API

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

Como listar partições em tabelas particionadas

Para listar as partições em uma tabela particionada, consulte a metatabela __PARTITIONS_SUMMARY__ usando o SQL legado.

É possível executar a consulta usando o Console do Cloud, o comando bq query ou chamando o método jobs.insert e configurando um query.

Permissões necessárias

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

bigquery.user
bigquery.jobUser
bigquery.admin

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

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

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

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

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

Acesse a página do BigQuery
Clique no botão Escrever nova consulta.
Digite o texto a seguir na caixa do Editor de consultas para consultar a __PARTITIONS_SUMMARY__ metatabela:
```
#legacySQL
SELECT
  partition_id
FROM
  [dataset.table$__PARTITIONS_SUMMARY__]
```
Substitua:
- dataset é o conjunto de dados que contém a tabela.
- table é o nome da tabela.
Clique em Executar.

bq

Digite a seguinte consulta usando o comando bq query:

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

Substitua:

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

API

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

Como conseguir metadados da tabela particionada usando metatabelas

É possível receber informações sobre tabelas particionadas usando tabelas especiais chamadas metatabelas. As metatabelas contêm metadados, como a lista de tabelas e visualizações em um conjunto de dados. As metatabelas são somente leitura.

Atualmente, você não pode usar o serviço INFORMATION_SCHEMA para receber metadados de tabela particionada.

Como conseguir metadados de partição usando metatabelas

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

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

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

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

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

Em que:

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

Valor	Descrição
`project_id`	Nome do projeto.
`dataset_id`	Nome do conjunto de dados.
`table_id`	Nome da tabela particionada por tempo.
`partition_id`	Nome (data) da partição.
`creation_time`	A hora em que a partição foi criada, em milissegundos desde 1º de janeiro de 1970, em UTC.
`last_modified_time`	A hora em que a partição foi modificada pela última vez, em milissegundos desde 1º de janeiro de 1970, em UTC.

Permissões de metatabela da partição

bigquery.user
bigquery.jobUser
bigquery.admin

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

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

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

Exemplo de metatabela da partição

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

Console

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

bq

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

A saída será assim:

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

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

Console

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

bq

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

A saída será assim:

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

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

Console

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

bq

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

A saída será assim:

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

Próximas etapas

Para uma visão geral do suporte a tabelas particionadas no BigQuery, consulte Introdução às tabelas particionadas.
Para saber como criar e usar tabelas particionadas por tempo de ingestão, clique aqui.
Para saber como criar e usar tabelas particionadas por intervalo de números inteiros, consulte esta página.
Para saber como gerenciar e atualizar tabelas particionadas, consulte esta página.
Para mais informações sobre como buscar tabelas particionadas, consulte esta página.