Como criar e usar tabelas particionadas por variação em número inteiro

Este documento descreve como criar e usar tabelas particionadas por uma coluna com número inteiro. Para receber informações sobre outros tipos de tabelas particionadas, consulte Criando e usando tabelas particionadas de data/timestamp ou Criando e usando tabelas particionadas por tempo de ingestão.

Para receber informações adicionais sobre partições por variação em número inteiro, consulte Tabelas particionadas com variação de número inteiro .

Depois de criar uma tabela particionada por variação de números inteiros, é possível:

  • controlar o acesso aos dados da 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 atualizar propriedades da tabela particionada, copiar uma tabela particionada e excluir uma tabela particionada, consulte Gerenciando tabelas particionadas.

Limitações

As tabelas particionadas por variação em números inteiros estão sujeitas às seguintes limitações:

  • A coluna de particionamento precisa ser uma coluna INTEGER. O modo da coluna pode ser REQUIRED ou NULLABLE, mas não pode ser REPEATED (baseado em array).
  • 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.

Como criar tabelas particionadas

É possível criar uma tabela particionada por intervalo de números inteiros no BigQuery de uma das seguintes maneiras:

Nomenclatura de tabelas

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

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

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

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

Permissões necessárias

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

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

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

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

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

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

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

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

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

Como criar uma tabela particionada 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

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

    Acesse a página do BigQuery

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

  3. No painel de detalhes, clique em Criar tabela.

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

    • Selecione Tabela vazia em Criar tabela de.

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

  6. Na seção Esquema, insira a definição do esquema. Insira as informações do esquema manualmente realizando uma das seguintes ações:

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

    • Clique em Adicionar campo e insira as informações do esquema.

  7. Em Configurações de partição e cluster, escolha uma coluna do tipo INTEGER da lista suspensa Partição.

  8. Insira os valores para start, end e interval:

    • start é o início do particionamento de variação, inclusivo
    • end é o fim do particionamento de variação, exclusivo
    • interval é a extensão de cada variação dentro da partição

  9. 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 reduz os custos e melhora o desempenho. Para mais informações, consulte Como consultar tabelas particionadas.

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

  11. 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:

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

    Acesse a página do BigQuery

  2. Clique em Escrever nova consulta.

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

    A consulta a seguir cria uma tabela chamada newtable com uma partição de variação em número inteiro na coluna customer_id com início em 0, fim em 100 e intervalo em 10.

     CREATE TABLE
   mydataset.newtable
 PARTITION BY
   RANGE_BUCKET(customer_id, GENERATE_ARRAY(0, 100, 10))
 AS SELECT 1 AS customer_id, DATE "2019-10-01" AS date1

  4. Clique em Executar. Quando a consulta é concluída, a tabela aparece na lista de tabelas desse conjunto de dados.

bq

Use o comando mk com a sinalização --range_partitioning:

bq mk \
--range_partitioning=column_name,start,end,interval \
project_id:dataset.table \
"column_name:integer,value:integer"

Em que:

  • column_name é a coluna usada para criar as partições por intervalo de números inteiros;
  • start é o início do particionamento por intervalo, inclusivo;
  • end é o fim do particionamento por intervalo, exclusivo;
  • interval é a largura de cada intervalo dentro da partição;
  • project_id é o ID do projeto;
  • dataset é um conjunto de dados no projeto;
  • table é o nome da tabela particionada que você está criando.

Exemplos:

Digite o seguinte comando para criar uma tabela particionada de variação em números inteiros chamada mypartitionedtable em mydataset em seu projeto padrão. O particionamento é baseado no início de 0, no final de 100 e no intervalo de 10.

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 melhora o desempenho. Para mais informações, veja Como consultar tabelas particionadas.

bq mk \
--require_partition_filter \
--range_partitioning=customer_id,0,100,10 \
mydataset.mypartitionedtable \
"customer_id:integer,value:integer"

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 rangePartitioning e a property schema.

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. 

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.RangePartitioning;
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;

// Sample to create a range partitioned table
public class CreateRangePartitionedTable {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    Schema schema =
        Schema.of(
            Field.of("integerField", StandardSQLTypeName.INT64),
            Field.of("stringField", StandardSQLTypeName.STRING),
            Field.of("booleanField", StandardSQLTypeName.BOOL),
            Field.of("dateField", StandardSQLTypeName.DATE));
    createRangePartitionedTable(datasetName, tableName, schema);
  }

  public static void createRangePartitionedTable(
      String datasetName, String tableName, Schema schema) {
    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);

      // Note: The field must be a top- level, NULLABLE/REQUIRED field.
      // The only supported type is INTEGER/INT64
      RangePartitioning partitioning =
          RangePartitioning.newBuilder()
              .setField("integerField")
              .setRange(
                  RangePartitioning.Range.newBuilder()
                      .setStart(1L)
                      .setInterval(2L)
                      .setEnd(10L)
                      .build())
              .build();

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

      bigquery.create(tableInfo);
      System.out.println("Range partitioned table created successfully");
    } catch (BigQueryException e) {
      System.out.println("Range 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. 

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

async function createTableRangePartitioned() {
  // Creates a new integer range 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: 'fullName', type: 'STRING'},
    {name: 'city', type: 'STRING'},
    {name: 'zipcode', type: 'INTEGER'},
  ];

  // To use integer range partitioning, select a top-level REQUIRED or
  // NULLABLE column with INTEGER / INT64 data type. Values that are
  // outside of the range of the table will go into the UNPARTITIONED
  // partition. Null values will be in the NULL partition.
  const rangePartition = {
    field: 'zipcode',
    range: {
      start: 0,
      end: 100000,
      interval: 10,
    },
  };

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    schema: schema,
    rangePartitioning: rangePartition,
  };

  // Create a new table in the dataset
  const [table] = await bigquery
    .dataset(datasetId)
    .createTable(tableId, options);

  console.log(`Table ${table.id} created with integer range partitioning: `);
  console.log(table.metadata.rangePartitioning);
}

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. 

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

# TODO(developer): Set table_id to the ID of the table to create.
# table_id = "your-project.your_dataset.your_table_name"

schema = [
    bigquery.SchemaField("full_name", "STRING"),
    bigquery.SchemaField("city", "STRING"),
    bigquery.SchemaField("zipcode", "INTEGER"),
]

table = bigquery.Table(table_id, schema=schema)
table.range_partitioning = bigquery.RangePartitioning(
    # To use integer range partitioning, select a top-level REQUIRED /
    # NULLABLE column with INTEGER / INT64 data type.
    field="zipcode",
    range_=bigquery.PartitionRange(start=0, end=100000, interval=10),
)
table = client.create_table(table)  # Make an API request.
print(
    "Created table {}.{}.{}".format(table.project, table.dataset_id, table.table_id)
)

Para verificar se uma tabela está particionada em uma coluna de número inteiro, examine o esquema. Por exemplo, execute o seguinte comando:

bq show --format=prettyjson mydataset.mytable

Se uma coluna de número inteiro for particionada, a saída de bq show conterá dados rangePartitioning:

...
  "rangePartitioning": {
    "field": "customer_id",
    "range": {
      "end": "100",
      "interval": "10",
      "start": "0"
    }
  },
...

Como gravar em uma tabela particionada de variação em números inteiros

Os dados gravados em uma tabela particionada de variação em números inteiros serão particionados automaticamente. Isso inclui gravar na tabela por meio de jobs de carregamento, consultas e streaming.

Para streaming, os dados no buffer de streaming estão na partição NÃO PARTICIONADO. Quando os dados são extraídos, eles permanecem inicialmente na partição NÃO PARTICIONADO. Quando houver dados não particionados suficientes, eles serão particionados novamente nas partições específicas.

Veja um exemplo para salvar os resultados da consulta em uma tabela particionada de variação em números inteiros:

bq query --nouse_legacy_sql \
--destination_table=mydataset.mytable \
'SELECT value AS customer_id, value+1 AS value FROM UNNEST(GENERATE_ARRAY(-5, 110, 5)) AS value'

Como consultar uma tabela particionada por variação em números inteiros

A tabela particionada por variação em números inteiros pode ser consultada apenas por meio do SQL padrão. Quando uma tabela particionada por variação em número inteiro é consultada, se houver filtros na coluna de particionamento de número inteiro, os filtros serão usados para remover as partições e reduzir o custo da consulta.

A consulta a seguir examinará as 3 partições que começam com 30, 40 e 50.

bq query --nouse_legacy_sql \
'SELECT * FROM mydataset.mytable WHERE customer_id BETWEEN 30 AND 50'

Veja o exemplo de saída do comando bq query:

Waiting on bqjob_r4fce65fa3381528e_000001670994aeb6_1 ... (0s) Current status: DONE
+---------+-------+
| customer_id | value |
+---------+-------+
|      40 |    41 |
|      45 |    46 |
|      30 |    31 |
|      35 |    36 |
|      50 |    51 |
+---------+-------+

Neste exemplo, cada partição tem 2 linhas e cada linha tem 2 colunas de números inteiros, portanto, a consulta precisa verificar 3 * 2 * 2 * 8 = 96 bytes. É possível examinar as informações do job:

bq show -j bqjob_r4fce65fa3381528e_000001670994aeb6_1

Veja o exemplo de saída do comando bq show:

Job myproject:bqjob_r4fce65fa3381528e_000001670994aeb6_1

  Job Type    State      Start Time      Duration       User Email        Bytes Processed   Bytes Billed   Billing Tier   Labels
 ---------- --------- ----------------- ---------- --------------------- ----------------- -------------- -------------- --------
  query      SUCCESS   24 Sep 12:19:58   0:00:01    joe@google.com       96                10485760       1

As instruções DML são suportadas. Exemplo:

bq query --nouse_legacy_sql \
'DELETE FROM mydataset.mytable WHERE customer_id = 30'

Atualmente, a remoção de partição não é suportada para funções em uma coluna particionada de variação em número inteiro. Como exemplo, a seguinte consulta examinará a tabela inteira.

bq query --nouse_legacy_sql \
'SELECT * FROM mydataset.mytable WHERE customer_id+1 BETWEEN 30 AND 50'

Os decoradores de tabela em tabelas particionadas de variação em números inteiros

Semelhante ao particionamento de data/timestamp, os decoradores de tabelas podem ser usados para endereçar uma partição em uma tabela particionada de variação em números inteiros. A chave para lidar com uma variação de partição é o início da variação.

O exemplo a seguir investiga a partição de variação que começa com 0. Ela tem 2 valores, 0 e 5.

bq query 'SELECT * FROM mydataset.mytable$0'

+---------+-------+
| customer_id | value |
+---------+-------+
|       0 |     1 |
|       5 |     6 |
+---------+-------+

Particionamento por variação em número inteiro com armazenamento em cluster

É possível usar o particionamento por variação em número inteiro com o armazenamento em cluster. Primeiro, os dados são particionados na coluna particionada por variação em número inteiro e, em seguida, os dados em cada partição são agrupados pelas colunas de clustering.

Como um exemplo, esse comando cria uma tabela com uma coluna de variação em números inteiros e um cluster.

bq mk \
--range_partitioning=customer_id,0,100,10 \
--clustering_fields=value \
mydataset.mytable_2 \
"customer_id:integer,value:integer"

Ao recuperar o formato da tabela, você verá que o particionamento por variação e o armazenamento em cluster estão em vigor:

...
  "clustering": {
    "fields": [
      "value"
    ]
  },
...
  "rangePartitioning": {
    "field": "customer_id",
    "range": {
      "end": "100",
      "interval": "10",
      "start": "0"
    }
  },
...

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 --range_partitioning 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 \
--range_partitioning column,start,end,interval \
--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 unidade de Tóquio, defina o valor do sinalizador como asia-northeast1. É possível definir um valor padrão para o local usando o arquivo .bigqueryrc.
  • project_id é a ID do projeto.
  • dataset é o nome do conjunto de dados que conterá a nova tabela particionada;
  • table é o nome da tabela particionada criada com os resultados da consulta;
  • column é a coluna INTEGER a ser usada para a partição;
  • start é o início do particionamento por intervalo, inclusivo;
  • end é o fim do particionamento por intervalo, exclusivo;
  • interval é a largura de cada intervalo dentro da partição;
  • 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.

Exemplo:

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. Essa consulta recupera dados de uma tabela não particionada: o conjunto de dados público do Medicare (em inglês). A coluna provider_id INTEGER da tabela é usada para criar as partições.

bq query \
--destination_table mydataset.mypartitionedtable \
--use_legacy_sql=false \
--range_partitioning provider_id,10000,700000,10000 \
'SELECT
   provider_id, total_discharges, average_covered_charges
 FROM
   `bigquery-public-data`.medicare.inpatient_charges_2011
 LIMIT
   300'

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

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.

Os decoradores de partições permitem carregar dados em uma partição específica. Por exemplo, considere uma tabela que particionada com variação em números inteiros na coluna customer_id, com início em 0, fim de 100 e intervalo de 10. Para carregar todos os dados de códigos de cliente que estejam na variação de 0 a 9, use o seguinte $0 decorador de partições:

table_name$0

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

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):

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:

Como usar tabelas particionadas

Como conseguir informações sobre tabelas particionadas

Para ver mais informações sobre tabelas:

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

Permissões necessárias

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

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

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 recupere metadados da tabela.

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

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

    Acesse a página do BigQuery

  2. No painel Explorer, expanda o projeto e o conjunto de dados e selecione a tabela.

  3. No painel de detalhes, clique em Detalhes. Essa aba exibe a descrição da tabela e as informações da tabela.

    Detalhes da tabela

  4. Clique na guia Schema para ver a definição do esquema da tabela.

bq

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

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

bq show --schema --format=prettyjson 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á 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": "1569429717657",
  "etag": "AcSEmWFQdbYEGT0auTE9NA==",
  "id": "myproject:mydataset.newtable",
  "kind": "bigquery#table",
  "lastModifiedTime": "1569429717657",
  "location": "US",
  "numBytes": "16",
  "numLongTermBytes": "0",
  "numRows": "1",
  "rangePartitioning": {
    "field": "customer_id",
    "range": {
      "end": "100",
      "interval": "10",
      "start": "0"
    }
  },
  "schema": {
    "fields": [
      {
        "name": "customer_id",
        "type": "INTEGER"
      },
      {
        "name": "date1",
        "type": "DATE"
      }
    ]
  },
  "selfLink": "https://bigquery.googleapis.com/bigquery/v2/projects/myproject/datasets/mydataset/tables/newtable",
  "tableReference": {
    "datasetId": "mydataset,
    "projectId": "myproject",
    "tableId": "newtable"
  },
  "type": "TABLE"
}

API

Chame o método 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):

  • Como usar o Console do Cloud
  • use o comando bq ls na ferramenta de linha de comando bq;
  • chame o método da API tables.list;
  • use 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

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

    Acesse a página do BigQuery

  2. No painel Explorer, expanda o 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.

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 ou o comando bq query na ferramenta de linha de comando bq ou chamando o método jobs.insert e configurar um job 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

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

    Acesse a página do BigQuery

  2. Clique no botão Escrever nova consulta.

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

    Em que:

    • dataset é o conjunto de dados que contém a tabela.
    • table é o nome da tabela.

  4. Clique em Executar.

bq

Digite a seguinte consulta usando o comando bq query:

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

Em que:

  • location é o nome do local. A sinalização --location é opcional. Por exemplo, se você estiver usando o BigQuery na unidade de Tóquio, defina o valor do sinalizador 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

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.

Exemplos de metatabela da partição

A consulta a seguir recupera todos os metadados particionados para uma tabela particionada por variações 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        | 10000        | 1517190224120 | 1517190224997      |
| myproject      | mydataset  | mytable        | 20000        | 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 |
+--------------+--------------------+
| 100000       |      1471632556179 |
| 20000        |      1471632538142 |
| 30000        |      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        |
+--------------+----------------------------+
| 10000        | 2016-08-19 18:49:30.463000 |
| 20000        | 2016-08-19 18:49:16.179000 |
| 30000        | 2016-08-19 18:48:58.142000 |
+--------------+----------------------------+

Próximas etapas