Como criar tabelas particionadas
Nesta página, você aprende a criar tabelas particionadas no BigQuery. Para ter uma visão geral das tabelas particionadas, consulte Introdução às tabelas particionadas.
Antes de começar
Atribua papéis do Identity and Access Management (IAM) que concedam aos usuários as permissões necessárias para realizar cada tarefa deste documento.
Permissões necessárias
Para criar uma tabela, você precisa das seguintes permissões do IAM:
bigquery.tables.create
bigquery.tables.updateData
bigquery.jobs.create
Além disso, é possível exigir a permissão bigquery.tables.getData
para
acessar os dados gravados na tabela.
Cada um dos papéis predefinidos do IAM a seguir inclui as permissões necessárias para atualizar uma tabela:
roles/bigquery.dataEditor
roles/bigquery.dataOwner
roles/bigquery.admin
(inclui a permissãobigquery.jobs.create
)roles/bigquery.user
(inclui a permissãobigquery.jobs.create
)roles/bigquery.jobUser
(inclui a permissãobigquery.jobs.create
)
Além disso, se você tiver a permissão bigquery.datasets.create
, será possível
criar e atualizar tabelas nos conjuntos de dados que criar.
Para mais informações sobre os papéis e as permissões do IAM no BigQuery, consulte Papéis e permissões predefinidos.
Criar uma tabela particionada vazia
As etapas para criar uma tabela particionada no BigQuery são semelhantes à criação de uma tabela padrão, exceto pelo fato de que você especifica as opções de particionamento, junto com outras opções de tabela.
Criar uma tabela particionada por coluna de unidade de tempo
Para criar uma tabela particionada vazia por coluna de unidade de tempo com uma definição de esquema:
Console
Abra a página do BigQuery no Console do Cloud.
No painel Explorer, expanda o projeto e selecione um conjunto de dados.
Expanda a opção
Ações e clique em Abrir.No painel de detalhes, clique em Criar tabela
.Na página Criar tabela, na seção Origem, selecione Tabela vazia.
Na seção Destino:
- Em Nome do conjunto de dados, escolha o conjunto apropriado.
- No campo Nome da tabela, insira o nome da tabela.
- Verifique se Table type está definido como Native table.
Na seção Esquema, insira a definição do esquema. Verifique se o esquema inclui uma coluna
DATE
,TIMESTAMP
ouDATETIME
para a coluna de particionamento. Para mais informações, consulte Como especificar um esquema.Na seção Configurações de particionamento e cluster, na lista suspensa Particionamento, selecione Partição por campo e escolha a coluna de particionamento. Esta opção só estará disponível se o esquema contiver uma coluna
DATE
,TIMESTAMP
ouDATETIME
.Selecione o Tipo de particionamento para escolher particionamento diário, por hora, mensal ou anual.
(Opcional) Para exigir um filtro de partição em todas as consultas desta tabela, marque a caixa de seleção Exigir filtro de partição. A exigência de um filtro de partição pode reduzir custos e melhorar o desempenho. Para mais informações, consulte Definir requisitos de filtro de partição.
Clique em Criar tabela.
SQL
Para criar uma tabela particionada por coluna de unidade de tempo, use a
instrução CREATE TABLE
com uma cláusula PARTITION BY
.
O exemplo a seguir cria uma tabela com partições diárias baseadas na
coluna transaction_date
.
CREATE TABLE mydataset.newtable (transaction_id INT64, transaction_date DATE) PARTITION BY transaction_date OPTIONS( partition_expiration_days=3, require_partition_filter=true )
Use a cláusula OPTIONS
para definir opções de tabela, como
a expiração da partição
e os requisitos de filtro de partição.
O tipo de particionamento padrão das colunas DATE
é o particionamento diário. Para
especificar um tipo de particionamento diferente, inclua a
função
DATE_TRUNC
na cláusula PARTITION BY
. Por exemplo, a consulta a seguir
cria uma tabela com partições mensais:
CREATE TABLE mydataset.newtable (transaction_id INT64, transaction_date DATE) PARTITION BY DATE_TRUNC(transaction_date, MONTH) OPTIONS( partition_expiration_days=3, require_partition_filter=true )
Também é possível especificar uma coluna TIMESTAMP
ou DATETIME
como a coluna
de particionamento. Nesse caso, inclua a função
TIMESTAMP_TRUNC
ou DATETIME_TRUNC
na cláusula PARTITION BY
para especificar o tipo de partição. Por
exemplo, a instrução a seguir cria uma tabela com partições diárias com base
em uma coluna TIMESTAMP
:
CREATE TABLE mydataset.newtable (transaction_id INT64, transaction_ts TIMESTAMP) PARTITION BY TIMESTAMP_TRUNC(transaction_ts, DAY) OPTIONS( partition_expiration_days=3, require_partition_filter=true )
Para informações sobre como executar consultas, consulte Como executar consultas interativas.
bq
Use o comando
bq mk
com a sinalização --table
(ou atalho -t
):
bq mk --table \ --schema SCHEMA \ --time_partitioning_field COLUMN \ --time_partitioning_type UNIT_TIME \ --time_partitioning_expiration EXPIRATION_TIME \ --require_partition_filter=BOOLEAN PROJECT_ID:DATASET.TABLE
Substitua:
- SCHEMA: uma definição de esquema no formato
column:data_type,column:data_type
ou o caminho para um arquivo de esquema JSON na máquina local. Para mais informações, consulte Como especificar um esquema. - COLUMN: o nome da coluna de particionamento. No esquema
da tabela, essa coluna precisa ser um tipo
TIMESTAMP
,DATETIME
ouDATE
. - UNIT_TIME: o tipo de particionamento. Os valores compatíveis
são
DAY
,HOUR
,MONTH
ouYEAR
. - EXPIRATION_TIME: o prazo de validade das partições
da tabela, em segundos. A sinalização
--time_partitioning_expiration
é opcional. Para mais informações, consulte Definir a validade da partição. - BOOLEAN: se for
true
, as consultas nesta tabela precisarão incluir um filtro de partição. A sinalização--require_partition_filter
é opcional. Para mais informações, consulte Definir requisitos de filtro de partição. - PROJECT_ID: o ID do projeto. Se omitido, o projeto padrão será usado.
- DATASET: o nome de um conjunto de dados no projeto.
- TABLE: o nome da tabela a ser criada.
Para outras opções de linha de comando,
consulte bq mk
.
No exemplo a seguir, criamos uma tabela denominada mytable
que é particionada
na coluna ts
usando particionamento por hora. A validade da partição é
de 259.200 segundos (três dias).
bq mk -t \
--schema 'ts:TIMESTAMP,qtr:STRING,sales:FLOAT' \
--time_partitioning_field ts \
--time_partitioning_type HOUR \
--time_partitioning_expiration 259200 \
mydataset.mytable
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 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 em Go.
Java
Antes de testar esta amostra, siga as instruções de configuração do 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.
Node.js
Antes de testar esta amostra, siga as instruções de configuração do 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.
Python
Antes de testar esta amostra, siga as instruções de configuração do 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.
Criar uma tabela particionada por tempo de processamento
Para criar uma tabela particionada vazia por tempo de processamento com uma definição de esquema:
Console
Abra a página do BigQuery no Console do Cloud.
No painel Explorer, expanda o projeto e selecione um conjunto de dados.
Expanda a opção
Ações e clique em Abrir.No painel de detalhes, clique em Criar tabela
.Na página Criar tabela, na seção Origem, selecione Tabela vazia.
Na seção Destino:
- Em Nome do conjunto de dados, escolha o conjunto apropriado.
- No campo Nome da tabela, insira o nome da tabela.
- Verifique se Table type está definido como Native table.
Na seção Esquema, insira a definição do esquema.
Na seção Configurações de particionamento e cluster, em Particionamento, clique em Partição por tempo de processamento.
(Opcional) Para exigir um filtro de partição em todas as consultas desta tabela, marque a caixa de seleção Exigir filtro de partição. A exigência de um filtro de partição pode reduzir custos e melhorar o desempenho. Para mais informações, consulte Definir requisitos de filtro de partição.
Clique em Criar tabela.
SQL
Para criar uma tabela particionada por tempo de processamento, use a
instrução CREATE TABLE
com uma cláusula PARTITION BY
particionada por _PARTITIONDATE
.
No exemplo a seguir, criamos uma tabela com partições diárias.
CREATE TABLE mydataset.newtable (transaction_id INT64) PARTITION BY _PARTITIONDATE OPTIONS( partition_expiration_days=3, require_partition_filter=true )
Use a cláusula OPTIONS
para definir opções de tabela, como
a expiração da partição
e os requisitos de filtro de partição.
O tipo de particionamento padrão para particionamento por tempo de processamento é o particionamento
diário. Para especificar um tipo de particionamento diferente, inclua a
função
DATE_TRUNC
na cláusula PARTITION BY
. Por exemplo, a consulta a seguir
cria uma tabela com partições mensais:
CREATE TABLE mydataset.newtable (transaction_id INT64) PARTITION BY DATE_TRUNC(_PARTITIONTIME, MONTH) OPTIONS( partition_expiration_days=3, require_partition_filter=true )
Para informações sobre como executar consultas, consulte Como executar consultas interativas.
bq
Use o comando
bq mk
com a sinalização --table
(ou atalho -t
):
bq mk --table \ --schema SCHEMA \ --time_partitioning_type UNIT_TIME \ --time_partitioning_expiration EXPIRATION_TIME \ --require_partition_filter=BOOLEAN \ PROJECT_ID:DATASET.TABLE
Substitua:
- SCHEMA: uma definição no formato
column:data_type,column:data_type
ou o caminho para um arquivo de esquema JSON na máquina local. Para mais informações, consulte Como especificar um esquema. - UNIT_TIME: o tipo de particionamento. Os valores compatíveis
são
DAY
,HOUR
,MONTH
ouYEAR
. - EXPIRATION_TIME: o prazo de validade das partições
da tabela, em segundos. A sinalização
--time_partitioning_expiration
é opcional. Para mais informações, consulte Definir a validade da partição. - BOOLEAN: se for
true
, as consultas nesta tabela precisarão incluir um filtro de partição. A sinalização--require_partition_filter
é opcional. Para mais informações, consulte Definir requisitos de filtro de partição. - PROJECT_ID: o ID do projeto. Se omitido, o projeto padrão será usado.
- DATASET: o nome de um conjunto de dados no projeto.
- TABLE: o nome da tabela a ser criada.
Para outras opções de linha de comando,
consulte bq mk
.
No exemplo a seguir, criamos uma tabela particionada por tempo de processamento
chamada mytable
. A tabela tem particionamento diário, com validade
de partição de 259.200 segundos (três dias).
bq mk -t \
--schema qtr:STRING,sales:FLOAT,year:STRING \
--time_partitioning_type DAY \
--time_partitioning_expiration 259200 \
mydataset.mytable
API
Chame o método tables.insert
com um recurso de tabela definido que especifique a property timePartitioning
e a property schema
.
Criar uma tabela particionada por intervalo em números inteiros
Para criar uma tabela particionada vazia por intervalo de números inteiros com uma definição de esquema:
Console
Abra a página do BigQuery no Console do Cloud.
No painel Explorer, expanda o projeto e selecione um conjunto de dados.
Expanda a opção
Ações e clique em Abrir.No painel de detalhes, clique em Criar tabela
.Na página Criar tabela, na seção Origem, selecione Tabela vazia.
Na seção Destino:
- Em Nome do conjunto de dados, escolha o conjunto apropriado.
- No campo Nome da tabela, insira o nome da tabela.
- Verifique se Table type está definido como Native table.
Na seção Esquema, insira a definição do esquema. O esquema precisa incluir uma coluna
INTEGER
para a coluna de particionamento. Para mais informações, consulte Como especificar um esquema.Na seção Configurações de particionamento e cluster, na lista suspensa Particionamento, selecione Partição por campo e escolha a coluna de particionamento. Essa opção só estará disponível se o esquema contiver uma coluna
INTEGER
.Insira os valores para Início, Fim e Intervalo:
- Início é o início do primeiro intervalo de partição (inclusivo).
- Fim é o fim do último intervalo de partição (exclusivo).
- Intervalo é a extensão de cada intervalo de partição.
Valores fora desses intervalos entram em uma partição
__UNPARTITIONED__
especial.(Opcional) Para exigir um filtro de partição em todas as consultas desta tabela, marque a caixa de seleção Exigir filtro de partição. A exigência de um filtro de partição pode reduzir custos e melhorar o desempenho. Para mais informações, consulte Definir requisitos de filtro de partição.
Clique em Criar tabela.
SQL
Para criar uma tabela particionada por intervalo de números inteiros, use a
instrução CREATE TABLE
com uma cláusula PARTITION BY
.
No exemplo a seguir, criamos uma tabela que é particionada na
coluna customer_id
com início em 0, fim em 100 e intervalo em 10.
CREATE TABLE mydataset.newtable (customer_id INT64, date1 DATE) PARTITION BY RANGE_BUCKET(customer_id, GENERATE_ARRAY(0, 100, 10)) OPTIONS( require_partition_filter=true )
Use a cláusula OPTIONS
para definir opções de tabela, como os
requisitos de filtro de partição.
Para informações sobre como executar consultas, consulte Como executar consultas interativas.
bq
Use o comando
bq mk
com a sinalização --table
(ou atalho -t
):
bq mk \ --schema schema \ --range_partitioning=COLUMN_NAME,START,END,INTERVAL \ --require_partition_filter=BOOLEAN \ PROJECT_ID:DATASET.TABLE
Substitua:
- SCHEMA: uma definição de esquema in-line no formato
column:data_type,column:data_type
ou o caminho para o arquivo de esquema JSON na máquina local. Para mais informações, consulte Como especificar um esquema. - COLUMN_NAME: o nome da coluna de particionamento. No esquema
da tabela, essa coluna precisa ser do tipo
INTEGER
. - START: o início do primeiro intervalo de partição (inclusivo).
- END: o fim do último intervalo de partição (exclusivo).
- INTERVAL: a extensão de cada intervalo de partição.
- BOOLEAN: se for
true
, as consultas nesta tabela precisarão incluir um filtro de partição. A sinalização--require_partition_filter
é opcional. Para mais informações, consulte Definir requisitos de filtro de partição. - PROJECT_ID: o ID do projeto. Se omitido, o projeto padrão será usado.
- DATASET: o nome de um conjunto de dados no projeto.
- TABLE: o nome da tabela a ser criada.
Valores fora do intervalo de partição entram em uma partição __UNPARTITIONED__
especial.
Para outras opções de linha de comando,
consulte bq mk
.
No exemplo a seguir, criamos uma tabela denominada mytable
, particionada
na coluna customer_id
.
bq mk -t \
--schema 'customer_id:INTEGER,qtr:STRING,sales:FLOAT' \
--range_partitioning=customer_id,0,100,10 \
mydataset.mytable
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 esta amostra, siga as instruções de configuração do 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.
Node.js
Antes de testar esta amostra, siga as instruções de configuração do 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.
Python
Antes de testar esta amostra, siga as instruções de configuração do 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.
Criar uma tabela particionada com base no resultado de uma consulta
É possível criar uma tabela particionada com base em um resultado de consulta das seguintes maneiras:
Use a ferramenta de linha de comando
bq
ou a API BigQuery para definir uma tabela de destino para uma consulta. Quando a consulta é executada, o BigQuery grava os resultados na tabela de destino. Essa abordagem pode ser usada para qualquer tipo de particionamento.No SQL, use uma instrução
CREATE TABLE ... AS SELECT
. É possível usar essa abordagem para criar uma tabela particionada por coluna de unidade de tempo ou intervalo de números inteiros, mas não por tempo de processamento.
SQL
Use a instrução
CREATE TABLE
com uma cláusula
SELECT AS
para a consulta. Inclua uma cláusula
PARTITION BY
para configurar o particionamento.
No exemplo a seguir, criamos uma tabela particionada na
coluna transaction_date
.
CREATE TABLE mydataset.newtable (transaction_id INT64, transaction_date DATE) PARTITION BY transaction_date AS SELECT transaction_id, transaction_date FROM mydataset.mytable
bq
Para criar uma tabela particionada a partir de uma consulta, use o
comando bq query
com as sinalizações --destination_table
e
--time_partitioning_type
.
Particionamento de colunas por unidade de tempo
bq query \ --use_legacy_sql=false \ --destination_table TABLE_NAME \ --time_partitioning_field COLUMN \ --time_partitioning_type UNIT_TIME \ 'QUERY_STATEMENT'
Particionamento por tempo de processamento:
bq query \ --use_legacy_sql=false \ --destination_table TABLE_NAME \ --time_partitioning_type UNIT_TIME \ 'QUERY_STATEMENT'
Particionamento por intervalo de números inteiros:
bq query \ --use_legacy_sql=false \ --destination_table PROJECT_ID:DATASET.TABLE \ --range_partitioning COLUMN,START,END,INTERVAL \ 'QUERY_STATEMENT'
Substitua:
- PROJECT_ID: o ID do projeto. Se omitido, o projeto padrão será usado.
- DATASET: o nome de um conjunto de dados no projeto.
- TABLE: o nome da tabela a ser criada.
- COLUMN: o nome da coluna de particionamento.
- UNIT_TIME: o tipo de particionamento. Os valores compatíveis são
DAY
,HOUR
,MONTH
ouYEAR
. - START: o início do particionamento por intervalo, inclusivo.
- END: o fim do particionamento por intervalo, exclusivo.
- INTERVAL: a extensão de cada intervalo na partição.
- QUERY_STATEMENT: a consulta usada para preencher a tabela.
No exemplo a seguir, criamos uma tabela que é particionada na
coluna transaction_date
usando particionamento mensal.
bq query \ --use_legacy_sql=false \ --destination_table mydataset.newtable \ --time_partitioning_field transaction_date \ --time_partitioning_type MONTH \ 'SELECT transaction_id, transaction_date FROM mydataset.mytable'
No exemplo a seguir, criamos uma tabela que é particionada na
coluna customer_id
usando o particionamento por intervalo de números inteiros.
bq query \ --use_legacy_sql=false \ --destination_table mydataset.newtable \ --range_partitioning customer_id,0,100,10 \ 'SELECT * FROM mydataset.ponies'
Para tabelas particionadas por tempo de processamento, também é possível carregar dados em uma
partição específica usando um
decorador de partição.
No exemplo a seguir, criamos uma nova tabela particionada por tempo de processamento e
carregamos dados na partição 20180201
(1 de fevereiro de 2018):
bq query \ --use_legacy_sql=false \ --time_partitioning_type=DAY \ --destination_table='newtable$20180201' \ 'SELECT * FROM mydataset.mytable'
API
Para salvar os resultados da consulta em uma tabela particionada, chame o
método jobs.insert
.
Configure um job query
. Especifique a tabela de destino na
destinationTable
. Especifique o particionamento na
propriedade timePartitioning
ou rangePartitioning
.
Converter tabelas fragmentadas por data em tabelas particionadas por tempo de processamento
Se você já criou tabelas fragmentadas por data, é possível converter todo o conjunto de
tabelas relacionadas em uma única tabela particionada por tempo de processamento usando o
comando partition
na ferramenta de linha de comando bq
.
bq --location=LOCATION partition \ --time_partitioning_type=PARTITION_TYPE \ --time_partitioning_expiration INTEGER \ PROJECT_ID:SOURCE_DATASET.SOURCE_TABLE \ PROJECT_ID:DESTINATION_DATASET.DESTINATION_TABLE
Substitua:
- LOCATION: o nome do seu local. A sinalização
--location
é opcional. - PARTITION_TYPE: o tipo de partição. Os valores possíveis
incluem
DAY
,HOUR
,MONTH
ouYEAR
. - INTEGER: a validade da partição, em segundos. Não há
valor mínimo. A validade é a soma da data UTC da partição com
o valor do número inteiro. A sinalização
time_partitioning_expiration
é opcional. - PROJECT_ID: o ID do projeto
- SOURCE_DATASET: o conjunto de dados que contém as tabelas fragmentadas por data.
- SOURCE_TABLE: o prefixo das tabelas fragmentadas por data.
- DESTINATION_DATASET: o conjunto de dados da nova tabela particionada.
- DESTINATION_TABLE: o nome da tabela particionada a ser criada.
O comando partition
não aceita sinalizações --label
, --expiration
ou
--description
. É possível adicionar rótulos, uma validade para a tabela e uma descrição
à tabela particionada depois que ela for criada.
Quando você executa o comando partition
, é criado um job de cópia no BigQuery
que gera partições das tabelas fragmentadas.
No exemplo a seguir, criamos uma tabela particionada por tempo de processamento chamada
mytable_partitioned
a partir de um conjunto de tabelas fragmentadas por data com o prefixo
sourcetable_
. A nova tabela é particionada diariamente, com uma validade de partição
de 259.200 segundos (3 dias).
bq partition \
--time_partitioning_type=DAY \
--time_partitioning_expiration 259200 \
mydataset.sourcetable_ \
mydataset.mytable_partitioned
Se as tabelas fragmentadas por data forem sourcetable_20180126
e
sourcetable_20180127
, esse comando criará estas partições:
mydataset.mytable_partitioned$20180126
e
mydataset.mytable_partitioned$20180127
.
Segurança de tabelas particionadas
O controle de acesso para tabelas particionadas é o mesmo que o das tabelas padrão. Saiba mais em Introdução aos controles de acesso à tabela.
Próximas etapas
- 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.