Neste documento, descreveremos como criar e usar tabelas em cluster no BigQuery. Para uma visão geral do suporte a tabelas em cluster no BigQuery, consulte Introdução às tabelas em cluster.
Limitações
No BigQuery, as tabelas em cluster estão sujeitas às seguintes limitações:
- Atualmente o agrupamento em cluster é compatível apenas com tabelas particionadas.
- A consulta a tabelas em cluster e a gravação de resultados de consultas a tabelas em cluster são compatíveis apenas com o SQL padrão.
- A especificação do agrupamento de colunas em cluster só é possível durante a criação de uma tabela.
- Após a criação de uma tabela em cluster, não é possível modificar o agrupamento de colunas em cluster.
- No agrupamento em cluster, é preciso que as colunas sejam de nível superior, sem repetição e de um dos seguintes tipos:
INT64
,STRING
,DATE
,TIMESTAMP
,BOOL
,NUMERIC
ouGEOGRAPHY
. Para mais informações sobre os tipos de dados, consulte Tipos de dados do SQL padrão. - É possível especificar até quatro agrupamentos de colunas em cluster.
Como criar tabelas em cluster
Atualmente, só é possível agrupar tabelas particionadas.
Isso inclui tabelas particionadas por tempo de ingestão e tabelas particionadas por uma coluna TIMESTAMP
ou DATE
.
É possível criar uma tabela em cluster no BigQuery:
- a partir dos resultados da consulta:
- usando uma instrução DDL
CREATE TABLE AS SELECT
; - executando uma consulta que cria uma tabela de destino em cluster.
- usando uma instrução DDL
- usando uma instrução DDL
CREATE TABLE
com uma cláusulaCLUSTER BY
contendo umaclustering_column_list
; - manualmente, usando o comando
bq mk
da ferramenta de linha de comando; - de maneira programática, chamando o método de API
tables.insert
; - ao carregar dados;
- usando as bibliotecas de cliente.
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 letras (maiúsculas e minúsculas), números e sublinhados.
Permissões exigidas
Para criar uma tabela, você precisa ter pelo menos as seguintes permissões:
- Permissões
bigquery.tables.create
para criar a tabela bigquery.tables.updateData
para gravar dados na tabela usando um job de carregamento, consulta ou cópiabigquery.jobs.create
para executar um job de consulta, carregamento ou cópia que grava dados na tabela
Outras permissões, como bigquery.tables.getData
, podem ser necessárias para acessar os dados que você está gravando na tabela.
Os papéis predefinidos do Cloud IAM a seguir incluem as permissões bigquery.tables.create
e bigquery.tables.updateData
:
bigquery.dataEditor
bigquery.dataOwner
bigquery.admin
Os papéis predefinidos do Cloud IAM a seguir incluem as permissões bigquery.jobs.create
:
bigquery.user
bigquery.jobUser
bigquery.admin
Além disso, quando um usuário com permissões bigquery.datasets.create
cria um conjunto de dados, ele recebe o acesso de bigquery.dataOwner
ao conjunto.
O acesso bigquery.dataOwner
dá ao usuário a capacidade de criar e atualizar tabelas no conjunto de dados.
Para mais informações sobre os papéis e permissões do Cloud IAM no BigQuery, consulte Papéis e permissões predefinidos.
Como criar uma tabela em cluster vazia com definição de esquema
Ao criar um tabela no BigQuery, você especifica o agrupamento de colunas em cluster. Após a criação da tabela, não é possível modificar esse agrupamento. Atualmente, só é possível especificar o agrupamento de colunas em cluster para tabelas particionadas.
No agrupamento em cluster, é preciso que as colunas sejam de nível superior, sem repetição e de um dos seguintes tipos de dados simples: INTEGER
, STRING
, DATE
, TIMESTAMP
, BOOLEAN
, NUMERIC
ou GEOGRAPHY
.
É possível especificar até quatro agrupamentos de colunas em cluster. Quando você especifica várias colunas, a ordem delas determina como os dados são classificados. Por exemplo, se a tabela estiver agrupada pelas colunas a, b e c, os dados serão classificados na mesma ordem: primeiro por a, depois por b e em seguida por c. Como prática recomendada, a coluna filtrada ou agregada com mais frequência aparecerá primeiro.
A ordem do agrupamento de colunas em cluster também afeta o desempenho e o preço das consultas. Para mais informações sobre as práticas recomendadas para consulta de tabelas em cluster, veja Como consultar tabelas em cluster.
Para criar uma tabela em cluster vazia com uma definição de esquema, faça o seguinte:
Console
Abra a IU da Web do BigQuery no Console do GCP.
Acessar o Console do GCPNa seção Recursos do painel de navegação, expanda o projeto e selecione um conjunto de dados.
No lado direito da janela, no painel de detalhes, clique em Criar tabela.
Na página Criar tabela, seção Origem, selecione Tabela em branco 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.
Use Adicionar campo para inserir manualmente o esquema.
Em Configurações de partição e cluster, clique em Sem particionamento, selecione Particionar por campo e escolha a coluna
DATE
ouTIMESTAMP
. A opção estará indisponível se o esquema não incluir uma colunaDATE
ouTIMESTAMP
.Para criar uma tabela particionada por tempo de ingestão, clique em Sem particionamento e selecione Partição por tempo de ingestão.
(Opcional) Em Filtro de particionamento, clique na caixa Exigir filtro de partição para solicitar que os usuários incluam uma cláusula
WHERE
que especifique as partições a serem consultadas. A exigência de um filtro de partição reduz os custos e melhora o desempenho. Para mais informações, consulte Como consultar tabelas particionadas.Em Ordem de clustering, insira de um a quatro nomes de coluna separados por vírgulas.
(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ê optar pela configuração Chave gerenciada pelo Google, o BigQuery criptografará os dados em repouso.
Clique em Criar tabela.
IU clássica
Acesse a IU clássica da Web do BigQuery.
Clique no ícone de seta para baixo
ao lado do nome do conjunto de dados na navegação e clique em Criar nova tabela.
Na página Criar tabela, na seção Dados de origem, clique em Criar tabela em branco.
Na página Create Table, na seção Destination Table:
- Em Table name, escolha o conjunto de dados apropriado e, no campo adequado, insira o nome da tabela que você está criando.
- Verifique se o Tipo de tabela está definido como Tabela nativa.
Na seção Esquema, insira manualmente a definição do esquema.
É possível inserir informações de esquema manualmente com um destes procedimentos:
Clique em Editar como texto e insira o esquema da tabela como uma matriz JSON.
Use Adicionar campo para inserir o esquema.
Na seção Opções, faça o seguinte:
- Para Partitioning Type, clique em None e escolha Day.
- Para Partitioning Field, escolha uma das seguintes opções:
- Escolha
timestamp
para criar uma tabela particionada por uma coluna DATE ou TIMESTAMP. - Escolha
_PARTITIONTIME
para criar uma tabela particionada por tempo de ingestão.
- Escolha
- Para Clustering columns, insira de um a quatro nomes de campo.
Em Criptografia de destino, mantenha a opção
Default
. Esta propriedade refere-se a chaves de criptografia gerenciadas pelo cliente. Por padrão, o BigQuery criptografa o conteúdo do cliente armazenado em repouso.
Clique em Create Table.
Após criar a tabela em cluster, atualize a validade, a descrição e os rótulos dela. Não é possível adicionar uma validade de partição após a criação de uma tabela usando a IU da Web do BigQuery.
CLI
Use o comando mk
com as sinalizações a seguir:
--table
(ou o atalho-t
).--schema
&mdash: é possível fornecer a definição de esquema da tabela in-line ou por meio de um arquivo de esquema JSON.--time_partitioning_type
(para tabelas particionadas por tempo de ingestão) ou--time_partitioning_field
(para tabelas particionadas). Atualmente,DAY
é o único valor compatível com--time_partitioning_type
.--clustering_fields
para especificar até quatro agrupamentos de colunas.
Os parâmetros opcionais incluem --expiration
, --description
, --time_partitioning_expiration
, --destination_kms_key
e --label
.
Se você estiver criando uma tabela em um projeto diferente do projeto padrão, adicione o ID do projeto ao conjunto de dados no formato a seguir: project_id:dataset
.
--destination_kms_key
não é demonstrado aqui. Para mais informações sobre como usar essa sinalização, consulte chaves de criptografia gerenciadas pelo cliente.
Digite o seguinte comando para criar uma tabela em cluster vazia com uma definição de esquema:
bq mk \ --table \ --expiration integer1 \ --schema schema \ --time_partitioning_type=DAY \ --time_partitioning_field partition_column \ --clustering_fields cluster_columns \ --time_partitioning_expiration integer2 \ --description "description" \ --label key:value,key:value \ project_id:dataset.table
em que:
- integer1 é a vida útil padrão, em segundos, da tabela. O valor mínimo é de 3.600 segundos (uma hora). O tempo de expiração é a soma do horário UTC atual com o valor inteiro. Se você definir o tempo de expiração ao criar uma tabela particionada por tempo, a configuração padrão de expiração da tabela do conjunto de dados será ignorada. Definir esse valor exclui a tabela e todas as partições após o tempo especificado;
- schema é 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. - partition_column é o nome da coluna
TIMESTAMP
ouDATE
usada para criar uma tabela particionada. Se você criar uma tabela particionada, não precisará especificar a sinalização--time_partitioning_type=DAY
; - cluster_columns é uma lista separada por vírgulas de até quatro agrupamentos de colunas em cluster;
- integer2 é a vida útil padrão, em segundos, das partições da tabela. Não há valor mínimo. O tempo de expiração é determinado pela data da partição acrescida do valor do número inteiro. A expiração da partição é independente da expiração da tabela, mas não a modifica. Se você definir uma expiração de partição maior que a expiração da tabela, a expiração da tabela terá precedência.
- description é uma descrição da tabela entre aspas.
- key:value é o par de chave:valor que representa um rótulo. Você pode inserir vários marcadores usando uma lista separada por vírgulas.
- project_id é o ID do projeto.
- dataset é um conjunto de dados no projeto.
- table é o nome da tabela particionada que você está criando.
Ao especificar o esquema na linha de comando, não é possível incluir um tipo RECORD
(STRUCT
), uma descrição de coluna ou especificar o modo da coluna. Todos os modos são definidos como NULLABLE
por padrão. Para incluir descrições, modos e tipos RECORD
, forneça um arquivo de esquema JSON.
Exemplos:
Digite o comando a seguir para criar uma tabela em cluster chamada myclusteredtable
em mydataset
no seu projeto padrão. A tabela é uma tabela particionada por uma coluna TIMESTAMP
. A expiração do particionamento é configurada para 86.400 segundos (um dia), a expiração da tabela é definida como 2.592.000 (um mês de 30 dias), a descrição é definida como This is my clustered table
e o marcador é definido como organization:development
. O comando usa o atalho -t
em vez de --table
.
O esquema é especificado in-line como: timestamp:timestamp,customer_id:string,transaction_amount:float
. O campo de agrupamento especificado customer_id
é usado para agrupar as partições.
bq mk -t \
--expiration 2592000 \
--schema 'timestamp:timestamp,customer_id:string,transaction_amount:float' \
--time_partitioning_field timestamp \
--clustering_fields customer_id \
--time_partitioning_expiration 86400 \
--description "This is my clustered table" \
--label org:dev \
mydataset.myclusteredtable
Digite o comando a seguir para criar uma tabela em cluster chamada myclusteredtable
em myotherproject
, não no seu projeto padrão. A tabela é uma tabela particionada por tempo de ingestã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 marcador é 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
. O campo customer_id
é usado para agrupar as partições.
bq mk -t \
--expiration 2592000 \
--schema /tmp/myschema.json \
--time_partitioning_type=DAY \
--clustering_fields=customer_id \
--time_partitioning_expiration 86400 \
--description "This is my partitioned table" \
--label org:dev \
myotherproject:mydataset.myclusteredtable
Depois da criação da tabela, atualize a validade da tabela, a validade da partição, a descrição e os rótulos da tabela particionada.
API
Chame o método tables.insert
com um recurso de tabela definido que especifique as propriedades timePartitioning
, clustering.fields
e schema
.
Go
Antes de testar este exemplo, siga as instruções de configuração do Go no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery Go.
Como criar uma tabela em cluster com base em um resultado de consulta
Há duas maneiras de criar uma tabela em cluster com base em um resultado da consulta:
- Gravar os resultados em uma nova tabela de destino e especificar as colunas do agrupamento em cluster. Este método é discutido abaixo.
- Usando uma instrução DDL
CREATE TABLE AS SELECT
. Para mais informações sobre este método, consulte Como criar uma tabela em cluster a partir do resultado de uma consulta na página Como usar instruções de linguagem de definição de dados.
É possível criar uma tabela em cluster consultando uma tabela particionada ou não particionada. Não é possível transformar uma tabela existente em uma particionada usando os resultados de consulta.
Quando uma tabela em cluster é criada a partir de um resultado de consulta, é necessário usar o SQL padrão. Atualmente, o SQL legado não é compatível com a consulta de tabelas em cluster ou com a gravação de resultados de consulta em tabelas em cluster.
Console
Não é possível especificar opções de agrupamento em cluster de uma tabela de destino ao consultar dados usando a IU da Web do BigQuery do console, a menos que você use uma instrução DDL. Para mais informações, consulte Como usar instruções de linguagem de definição de dados.
IU clássica
Não é possível especificar opções de agrupamento em cluster de uma tabela de destino ao consultar dados usando a IU da Web clássica do BigQuery, a não ser que você use uma instrução DDL. Para mais informações, consulte Como usar instruções de linguagem de definição de dados.
CLI
Use o comando a seguir para criar uma nova tabela de destino em cluster a partir do resultado da consulta:
bq --location=location query \ --use_legacy_sql=false 'query'
em que:
- location é o nome do local. A sinalização
--location
é opcional. Por exemplo, se você estiver usando o BigQuery na região de Tóquio, poderá definir o valor da sinalização comoasia-northeast1
. É possível definir um valor padrão para o local usando o arquivo .bigqueryrc; - query é uma consulta na sintaxe SQL padrão. No momento, não é possível usar o SQL legado para consultar tabelas em cluster ou gravar resultados de consulta nessas tabelas. A consulta pode conter uma instrução DDL
CREATE TABLE
que especifique as opções para criar a tabela em cluster. Use o DDL em vez de especificar as sinalizações de linha de comando individuais.
Exemplos:
Digite o comando a seguir para gravar os resultados da consulta em uma tabela de destino em cluster denominada myclusteredtable
em mydataset
. O mydataset
está no projeto padrão. A consulta recupera dados de uma tabela não particionada: mytable. A coluna customer_id
da tabela é usada para agrupar a tabela. A coluna timestamp
da tabela é usada para criar uma tabela particionada.
bq query --use_legacy_sql=false \
'CREATE TABLE
mydataset.myclusteredtable
PARTITION BY
DATE(timestamp)
CLUSTER BY
customer_id AS
SELECT
*
FROM
`mydataset.mytable`'
API
Para salvar os resultados da consulta em uma tabela em cluster, chame o método jobs.insert
, configure um job query
e inclua uma instrução DDL CREATE TABLE
que criará a tabela em cluster.
Especifique o local na propriedade location
na seção jobReference
do recurso de job.
Como criar uma tabela em cluster ao carregar dados
Ao carregar dados em uma nova tabela, é possível criar uma tabela em cluster especificando o agrupamento de colunas em cluster. Não é necessário criar uma tabela em branco para carregar os dados. Crie a tabela em cluster e carregue seus dados ao mesmo tempo.
Para mais informações sobre como carregar dados, consulte Introdução ao carregamento de dados no BigQuery.
Para definir o armazenamento em cluster ao definir um job de carga:
API
Para definir a configuração de clustering ao criar uma tabela por meio de um job de carga, preencha as propriedades Clustering
da tabela.
Go
Antes de testar este exemplo, siga as instruções de configuração do Go no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery Go.
Como controlar o acesso a tabelas em cluster
Não é possível atribuir controles de acesso diretamente a tabelas ou visualizações. O nível mais baixo de recursos do BigQuery aos quais é possível conceder acesso é o nível do conjunto de dados. Para configurar o acesso a tabelas e visualizações, conceda um papel do Cloud IAM a uma entidade no nível do conjunto de dados ou acima.
A atribuição de um papel no nível do conjunto de dados especifica as operações que uma entidade tem permissão para executar em tabelas e visualizações nesse conjunto de dados específico. Para informações sobre a configuração de controles de acesso no nível do conjunto de dados, consulte Como controlar o acesso a conjuntos de dados.
Também é possível conceder papéis do Cloud IAM em um nível superior na hierarquia de recursos do Google Cloud Platform, como os níveis de projeto, pasta ou organização. A concessão de papéis em um nível mais alto dá à entidade acesso a um conjunto mais amplo de recursos. Por exemplo, a concessão de um papel a uma entidade no nível do projeto concede a ela permissões referentes a todos os conjuntos de dados em todo o projeto. Para mais informações sobre a concessão de acesso a recursos, consulte Como conceder, alterar e revogar o acesso a recursos na documentação do Cloud IAM.
Também é possível criar papéis personalizados do Cloud IAM. Se você criar um papel personalizado, as permissões concedidas dependerão das operações específicas que a entidade poderá executar.
Para saber mais sobre papéis e permissões, consulte:
- Noções básicas sobre papéis na documentação do Cloud IAM
- Permissões e papéis predefinidos do BigQuery
- Como controlar o acesso a conjuntos de dados
Como usar tabelas em cluster
Como conseguir Informações sobre tabelas em cluster
Para conseguir informações sobre tabelas, faça o seguinte:
- Use o Console do GCP ou a IU clássica da Web do BigQuery.
- Use o comando
bq show
da CLI. - Chame o método de API
tables.get
. - Consulte as visualizações
INFORMATION_SCHEMA
(Beta).
Permissões exigidas
Para receber informações sobre tabelas, você precisa, no mínimo, ter permissões bigquery.tables.get
. Os papéis predefinidos do Cloud IAM a seguir incluem permissões bigquery.tables.get
:
bigquery.metadataViewer
bigquery.dataViewer
bigquery.dataOwner
bigquery.dataEditor
bigquery.admin
Além disso, quando um usuário com permissões bigquery.datasets.create
cria um conjunto de dados, recebe o acesso bigquery.dataOwner
a ele.
O acesso bigquery.dataOwner
permite que o usuário receba informações sobre tabelas em um conjunto de dados.
Para mais informações sobre os papéis e permissões do Cloud IAM no BigQuery, consulte Papéis e permissões predefinidos.
Como conseguir informações de tabelas em cluster
Para ver informações sobre uma tabela em cluster, faça o seguinte:
Console
No painel Recursos, clique no nome do seu conjunto de dados para expandi-lo e, em seguida, clique no nome da tabela que você quer visualizar.
Clique em Detalhes. Esta página exibe os detalhes da tabela, incluindo as colunas de agrupamento em cluster.
IU clássica
No painel de navegação, clique no ícone de seta para baixo
à esquerda do conjunto de dados para expandi-lo ou clique duas vezes no nome dele. Isso exibe as tabelas e visualizações.
Clique no nome da tabela.
Clique em Detalhes. A página Detalhes da tabela contém os detalhes correspondentes, incluindo as colunas agrupadas em cluster.
CLI
Emita o comando bq show
para exibir todas as informações da tabela. Use a sinalização --schema
para exibir apenas informações de esquema de tabela. A sinalização --format
pode ser usada para controlar a saída.
Se você estiver recebendo informações sobre uma tabela em um projeto diferente do padrão, adicione o ID do projeto ao conjunto de dados no formato abaixo: project_id:dataset
.
bq show \ --schema \ --format=prettyjson \ project_id:dataset.table
em que:
- project_id é o ID do projeto.
- dataset é o nome do conjunto de dados.
- table é o nome da tabela.
Exemplos:
Insira o comando a seguir para exibir todas as informações sobre myclusteredtable
em mydataset
. O mydataset
está no projeto padrão.
bq show --format=prettyjson mydataset.myclusteredtable
A saída será semelhante a esta:
{ "clustering": { "fields": [ "customer_id" ] }, ... }
API
Chame o método bigquery.tables.get
e forneça os parâmetros relevantes.
Como receber informações da tabela em cluster usando INFORMATION_SCHEMA
(Beta)
INFORMATION_SCHEMA
é uma série de visualizações que fornecem acesso a metadados sobre conjuntos de dados, tabelas e visualizações.
É possível consultar as visualizações INFORMATION_SCHEMA.TABLES
e INFORMATION_SCHEMA.TABLE_OPTIONS
para recuperar metadados sobre tabelas e visualizações de um projeto. Também é possível consultar as visualizações INFORMATION_SCHEMA.COLUMNS
e INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
para recuperar metadados sobre as colunas (campos) de uma tabela.
Para tabelas em cluster, consulte a coluna CLUSTERING_ORDINAL_POSITION
na visualização INFORMATION_SCHEMA.COLUMNS
para recuperar informações sobre as colunas de cluster.
Visualização TABLES
Quando consultamos a visualização INFORMATION_SCHEMA.TABLES
, os resultados contêm uma linha para cada tabela ou visualização do conjunto de dados.
As consultas na visualização INFORMATION_SCHEMA.TABLES
precisam ter um qualificador de conjunto de dados. O usuário que envia a consulta precisa ter acesso ao conjunto de dados que contém as tabelas ou visualizações.
A visualização INFORMATION_SCHEMA.TABLES
tem o seguinte esquema:
Nome da coluna | Tipo de dados | Valor |
---|---|---|
TABLE_CATALOG |
STRING |
O nome do projeto que contém o conjunto de dados |
TABLE_SCHEMA |
STRING |
O nome do conjunto de dados que contém a tabela ou visualização, também conhecido como datasetId |
TABLE_NAME |
STRING |
O nome da tabela ou visualização, também conhecido como tableId |
TABLE_TYPE |
STRING |
O tipo de tabela:
|
IS_INSERTABLE_INTO |
STRING |
YES ou NO dependendo da compatibilidade da tabela com as instruções DML INSERT |
IS_TYPED |
STRING |
O valor sempre é NO . |
CREATION_TIME |
TIMESTAMP |
O horário de criação da tabela |
Para mais informações sobre as propriedades do conjunto de dados, consulte a página de recursos do conjunto de dados na documentação da API REST. Para mais informações sobre as propriedades da tabela e da visualização, consulte a página de recursos da tabela na documentação da API REST.
Exemplos
Exemplo 1:
O exemplo a seguir recupera todas as colunas da visualização INFORMATION_SCHEMA.TABLES
, exceto is_typed
, que é reservada para uso futuro. Os metadados retornados correspondem a todas as tabelas em mydataset
no projeto padrão (myproject
).
O mydataset
contém as seguintes tabelas:
mytable1
: uma tabela padrão do BigQuery.myview1
: uma visualização do BigQuery.
As consultas na visualização INFORMATION_SCHEMA.TABLES
precisam ter um qualificador de conjunto de dados. O usuário que envia a consulta precisa ter acesso ao conjunto de dados que contém as tabelas.
Para executar a consulta em um projeto diferente do padrão, adicione o ID do projeto ao conjunto de dados no formato a seguir:
`project_id`.dataset.INFORMATION_SCHEMA.view
;
por exemplo, `myproject`.mydataset.INFORMATION_SCHEMA.TABLES
.
Para executar a consulta, faça o seguinte:
Console
Abra a IU da Web do BigQuery no Console do GCP.
Insira a consulta SQL padrão a seguir na caixa Editor de consultas.
INFORMATION_SCHEMA
requer sintaxe SQL padrão. SQL padrão é a sintaxe padrão no Console do GCP.SELECT * EXCEPT(is_typed) FROM mydataset.INFORMATION_SCHEMA.TABLES
Clique em Executar.
CLI
Use o comando query
e especifique a sintaxe SQL padrão usando a sinalização --nouse_legacy_sql
ou --use_legacy_sql=false
. A sintaxe SQL padrão é obrigatória para consultas INFORMATION_SCHEMA
.
Para executar a consulta, insira:
bq query --nouse_legacy_sql \ 'SELECT * EXCEPT(is_typed) FROM mydataset.INFORMATION_SCHEMA.TABLES'
Os resultados terão a aparência abaixo:
+----------------+---------------+----------------+------------+--------------------+---------------------+ | table_catalog | table_schema | table_name | table_type | is_insertable_into | creation_time | +----------------+---------------+----------------+------------+--------------------+---------------------+ | myproject | mydataset | mytable1 | BASE TABLE | YES | 2018-10-29 20:34:44 | | myproject | mydataset | myview1 | VIEW | NO | 2018-12-29 00:19:20 | +----------------+---------------+----------------+------------+--------------------+---------------------+
Exemplo 2:
O exemplo a seguir recupera todas as tabelas do tipo BASE TABLE
da visualização INFORMATION_SCHEMA.TABLES
. A coluna is_typed
é excluída. Os metadados retornados referem-se a tabelas em mydataset
no projeto padrão (myproject
).
As consultas na visualização INFORMATION_SCHEMA.TABLES
precisam ter um qualificador de conjunto de dados. O usuário que envia a consulta precisa ter acesso ao conjunto de dados que contém as tabelas.
Para executar a consulta em um projeto diferente do padrão, adicione o ID do projeto ao conjunto de dados no formato a seguir:
`project_id`.dataset.INFORMATION_SCHEMA.view
;
por exemplo, `myproject`.mydataset.INFORMATION_SCHEMA.TABLES
.
Para executar a consulta, faça o seguinte:
Console
Abra a IU da Web do BigQuery no Console do GCP.
Insira a consulta SQL padrão a seguir na caixa Editor de consultas.
INFORMATION_SCHEMA
requer sintaxe SQL padrão. SQL padrão é a sintaxe padrão no Console do GCP.SELECT * EXCEPT(is_typed) FROM mydataset.INFORMATION_SCHEMA.TABLES WHERE table_type="BASE TABLE"
Clique em Executar.
CLI
Use o comando query
e especifique a sintaxe SQL padrão usando a sinalização --nouse_legacy_sql
ou --use_legacy_sql=false
. A sintaxe SQL padrão é obrigatória para consultas INFORMATION_SCHEMA
.
Para executar a consulta, insira:
bq query --nouse_legacy_sql \ 'SELECT * EXCEPT(is_typed) FROM mydataset.INFORMATION_SCHEMA.TABLES WHERE table_type="BASE TABLE"'
Os resultados terão a aparência abaixo:
+----------------+---------------+----------------+------------+--------------------+---------------------+ | table_catalog | table_schema | table_name | table_type | is_insertable_into | creation_time | +----------------+---------------+----------------+------------+--------------------+---------------------+ | myproject | mydataset | mytable1 | BASE TABLE | NO | 2018-10-31 22:40:05 | +----------------+---------------+----------------+------------+--------------------+---------------------+
Visualização TABLE_OPTIONS
Quando consultamos a visualização INFORMATION_SCHEMA.TABLE_OPTIONS
, os resultados contêm uma linha para cada tabela ou visualização no conjunto de dados.
As consultas na visualização INFORMATION_SCHEMA.TABLE_OPTIONS
precisam ter um qualificador de conjunto de dados. O usuário que envia a consulta precisa ter acesso ao conjunto de dados que contém as tabelas ou visualizações.
A visualização INFORMATION_SCHEMA.TABLE_OPTIONS
tem o seguinte esquema:
Nome da coluna | Tipo de dados | Valor |
---|---|---|
TABLE_CATALOG |
STRING |
O nome do projeto que contém o conjunto de dados |
TABLE_SCHEMA |
STRING |
O nome do conjunto de dados que contém a tabela ou visualização, também conhecido como datasetId |
TABLE_NAME |
STRING |
O nome da tabela ou visualização, também conhecido como tableId |
OPTION_NAME |
STRING |
Um dos valores de nome na tabela de opções |
OPTION_TYPE |
STRING |
Um dos valores de tipo de dados na tabela de opções. |
OPTION_VALUE |
STRING |
Uma das opções de valor na tabela de opções. |
Tabela de opções
OPTION_NAME |
OPTION_TYPE |
OPTION_VALUE |
---|---|---|
partition_expiration_days |
FLOAT64 |
A vida útil padrão, em dias, de todas as partições em uma tabela particionada. |
expiration_timestamp |
FLOAT64 |
A vida útil padrão em dias da tabela. |
kms_key_name |
STRING |
O nome da chave do Cloud KMS usada para criptografar a tabela. |
friendly_name |
STRING |
O nome descritivo da tabela. |
description |
STRING |
Uma descrição da tabela. |
labels |
ARRAY<STRUCT<STRING, STRING>> |
Uma matriz de STRUCT s que representa os rótulos na tabela. |
Para mais informações sobre as propriedades do conjunto de dados, consulte a página de recursos do conjunto de dados na documentação da API REST. Para mais informações sobre as propriedades da tabela e da visualização, consulte a página de recursos da tabela na documentação da API REST.
Exemplos
Exemplo 1:
O exemplo a seguir recupera os prazos de validade padrão de todas as tabelas em mydataset
no projeto padrão (myproject
) por meio da consulta da visualização INFORMATION_SCHEMATA.TABLE_OPTIONS
.
As consultas na visualização INFORMATION_SCHEMA.TABLE_OPTIONS
precisam ter um qualificador de conjunto de dados. O usuário que envia a consulta precisa ter acesso ao conjunto de dados que contém as tabelas.
Para executar a consulta em um projeto diferente do padrão, adicione o ID do projeto ao conjunto de dados no formato a seguir:
`project_id`.dataset.INFORMATION_SCHEMA.view
;
por exemplo, `myproject`.mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
.
Para executar a consulta, faça o seguinte:
Console
Abra a IU da Web do BigQuery no Console do GCP.
Insira a consulta SQL padrão a seguir na caixa Editor de consultas.
INFORMATION_SCHEMA
requer sintaxe SQL padrão. SQL padrão é a sintaxe padrão no Console do GCP.SELECT * FROM mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS WHERE option_name="expiration_timestamp"
Clique em Executar.
CLI
Use o comando query
e especifique a sintaxe SQL padrão usando a sinalização --nouse_legacy_sql
ou --use_legacy_sql=false
. A sintaxe SQL padrão é obrigatória para consultas INFORMATION_SCHEMA
.
Para executar a consulta, insira:
bq query --nouse_legacy_sql \ 'SELECT * FROM mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS WHERE option_name="expiration_timestamp"'
Os resultados terão a aparência abaixo:
+----------------+---------------+------------+----------------------+-------------+--------------------------------------+ | table_catalog | table_schema | table_name | option_name | option_type | option_value | +----------------+---------------+------------+----------------------+-------------+--------------------------------------+ | myproject | mydataset | mytable1 | expiration_timestamp | TIMESTAMP | TIMESTAMP "2020-01-16T21:12:28.000Z" | | myproject | mydataset | mytable2 | expiration_timestamp | TIMESTAMP | TIMESTAMP "2021-01-01T21:12:28.000Z" | +----------------+---------------+------------+----------------------+-------------+--------------------------------------+
Exemplo 2:
O exemplo a seguir recupera os metadados sobre todas as tabelas em mydataset
que contêm dados de teste. A consulta usa os valores na opção description
para localizar as tabelas que contêm “test” (teste) em qualquer lugar da descrição. mydataset
está no projeto padrão (myproject
).
Para executar a consulta em um projeto diferente do padrão, adicione o ID do projeto ao conjunto de dados no formato a seguir:
`project_id`.dataset.INFORMATION_SCHEMA.view
;
por exemplo,
`myproject`.mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
.
Para executar a consulta, faça o seguinte:
Console
Abra a IU da Web do BigQuery no Console do GCP.
Insira a consulta SQL padrão a seguir na caixa Editor de consultas.
INFORMATION_SCHEMA
requer sintaxe SQL padrão. SQL padrão é a sintaxe padrão no Console do GCP.SELECT * FROM mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS WHERE option_name="description" AND option_value LIKE "%test%"
Clique em Executar.
CLI
Use o comando query
e especifique a sintaxe SQL padrão usando a sinalização --nouse_legacy_sql
ou --use_legacy_sql=false
. A sintaxe SQL padrão é obrigatória para consultas INFORMATION_SCHEMA
.
Para executar a consulta, insira:
bq query --nouse_legacy_sql \ 'SELECT * FROM mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS WHERE option_name="description" AND option_value LIKE "%test%"'
Os resultados terão a aparência abaixo:
+----------------+---------------+------------+-------------+-------------+--------------+ | table_catalog | table_schema | table_name | option_name | option_type | option_value | +----------------+---------------+------------+-------------+-------------+--------------+ | myproject | mydataset | mytable1 | description | STRING | "test data" | | myproject | mydataset | mytable2 | description | STRING | "test data" | +----------------+---------------+------------+-------------+-------------+--------------+
Visualização COLUMNS
Quando consultamos a visualização INFORMATION_SCHEMA.COLUMNS
, os resultados contêm uma linha para cada coluna (campo) de uma tabela.
As consultas na visualização INFORMATION_SCHEMA.COLUMNS
precisam ter um qualificador de conjunto de dados. O usuário que envia a consulta precisa ter acesso ao conjunto de dados que contém as tabelas.
A visualização INFORMATION_SCHEMA.COLUMNS
tem o seguinte esquema:
Nome da coluna | Tipo de dados | Valor |
---|---|---|
TABLE_CATALOG |
STRING |
O nome do projeto que contém o conjunto de dados |
TABLE_SCHEMA |
STRING |
O nome do conjunto de dados que contém a tabela, também conhecido como datasetId |
TABLE_NAME |
STRING |
O nome da tabela ou visualização, também conhecido como tableId |
COLUMN_NAME |
STRING |
O nome da coluna. |
ORDINAL_POSITION |
INT64 |
O deslocamento de índice 1 da coluna na tabela. Se for uma pseudocoluna, como _PARTITIONTIME ou _PARTITIONDATE, o valor será NULL . |
IS_NULLABLE |
STRING |
YES ou NO , dependendo do modo da coluna permitir valores NULL . |
DATA_TYPE |
STRING |
O tipo de dados SQL padrão da coluna |
IS_GENERATED |
STRING |
O valor sempre é NEVER . |
GENERATION_EXPRESSION |
STRING |
O valor sempre é NULL . |
IS_STORED |
STRING |
O valor sempre é NULL . |
IS_HIDDEN |
STRING |
YES ou NO , dependendo do tipo de coluna (se é ou não é uma pseudocoluna, como _PARTITIONTIME ou _PARTITIONDATE). |
IS_UPDATABLE |
STRING |
O valor sempre é NULL . |
IS_SYSTEM_DEFINED |
STRING |
YES ou NO , dependendo do tipo de coluna (se é ou não é uma pseudocoluna, como _PARTITIONTIME ou _PARTITIONDATE). |
IS_PARTITIONING_COLUMN |
STRING |
YES ou NO dependendo de a coluna ser uma coluna de particionamento ou não |
CLUSTERING_ORDINAL_POSITION |
STRING |
O deslocamento de 1 índice da coluna nas colunas de cluster da tabela. O valor será NULL se a tabela não for uma tabela em cluster |
Para mais informações sobre as propriedades do conjunto de dados, consulte a página de recursos do conjunto de dados na documentação da API REST. Para mais informações sobre as propriedades da tabela e da visualização, consulte a página de recursos da tabela na documentação da API REST.
Exemplos
O exemplo a seguir recupera metadados da visualização INFORMATION_SCHEMA.COLUMNS
para a tabela population_by_zip_2010
no conjunto de dados census_bureau_usa
. Ele faz parte do programa de conjunto de dados públicos do BigQuery.
Como a tabela que você está consultando está em outro projeto, bigquery-public-data
, adicione o ID do projeto ao conjunto de dados no formato a seguir:
`project_id`.dataset.INFORMATION_SCHEMA.view
;
por exemplo,
`bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES
.
As colunas a seguir são excluídas dos resultados da consulta porque atualmente estão reservadas para uso futuro:
IS_GENERATED
GENERATION_EXPRESSION
IS_STORED
IS_UPDATABLE
As consultas na visualização INFORMATION_SCHEMA.COLUMNS
precisam ter um qualificador de conjunto de dados. O usuário que envia a consulta precisa ter acesso ao conjunto de dados que contém as tabelas.
Para executar a consulta, faça o seguinte:
Console
Abra a IU da Web do BigQuery no Console do GCP.
Insira a consulta SQL padrão a seguir na caixa Editor de consultas.
INFORMATION_SCHEMA
requer sintaxe SQL padrão. SQL padrão é a sintaxe padrão no Console do GCP.SELECT * EXCEPT(is_generated, generation_expression, is_stored, is_updatable) FROM `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.COLUMNS WHERE table_name="population_by_zip_2010"
Clique em Executar.
CLI
Use o comando query
e especifique a sintaxe SQL padrão usando a sinalização --nouse_legacy_sql
ou --use_legacy_sql=false
. A sintaxe SQL padrão é obrigatória para consultas INFORMATION_SCHEMA
.
Para executar a consulta, insira:
bq query --nouse_legacy_sql \ 'SELECT * EXCEPT(is_generated, generation_expression, is_stored, is_updatable) FROM `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.COLUMNS WHERE table_name="population_by_zip_2010"'
Os resultados terão a aparência abaixo. Para melhor legibilidade, table_catalog
e table_schema
foram excluídos dos resultados:
+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+ | table_name | column_name | ordinal_position | is_nullable | data_type | is_hidden | is_system_defined | is_partitioning_column | clustering_ordinal_position | +------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+ | population_by_zip_2010 | zipcode | 1 | NO | STRING | NO | NO | NO | NULL | | population_by_zip_2010 | geo_id | 2 | YES | STRING | NO | NO | NO | NULL | | population_by_zip_2010 | minimum_age | 3 | YES | INT64 | NO | NO | NO | NULL | | population_by_zip_2010 | maximum_age | 4 | YES | INT64 | NO | NO | NO | NULL | | population_by_zip_2010 | gender | 5 | YES | STRING | NO | NO | NO | NULL | | population_by_zip_2010 | population | 6 | YES | INT64 | NO | NO | NO | NULL | +------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+
Visualização COLUMN_FIELD_PATHS
Quando consultamos a visualização INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
, os resultados da consulta contêm uma linha para cada coluna aninhada em uma coluna RECORD
(ou STRUCT
).
As consultas na visualização INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
precisam ter um qualificador de conjunto de dados. O usuário que envia a consulta precisa ter acesso ao conjunto de dados que contém as tabelas.
A visualização INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
tem o seguinte esquema:
Nome da coluna | Tipo de dados | Valor |
---|---|---|
TABLE_CATALOG |
>STRING |
O nome do projeto que contém o conjunto de dados |
TABLE_SCHEMA |
STRING |
O nome do conjunto de dados que contém a tabela, também conhecido como datasetId |
TABLE_NAME |
STRING |
O nome da tabela ou visualização, também conhecido como tableId |
COLUMN_NAME |
STRING |
O nome da coluna. |
FIELD_PATH |
STRING |
O caminho para uma coluna aninhada em uma coluna `RECORD` ou `STRUCT`. |
DATA_TYPE |
STRING |
O tipo de dados SQL padrão da coluna. |
DESCRIPTION |
STRING |
A descrição da coluna. |
Para mais informações sobre as propriedades do conjunto de dados, consulte a página de recursos do conjunto de dados na documentação da API REST. Para mais informações sobre as propriedades da tabela e da visualização, consulte a página de recursos da tabela na documentação da API REST.
Exemplos
O exemplo a seguir recupera metadados da visualização INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
para a tabela commits
no conjunto de dados github_repos
.
Ele faz parte do programa de conjunto de dados públicos do BigQuery.
Como a tabela que você está consultando está em outro projeto, bigquery-public-data
, adicione o ID do projeto ao conjunto de dados no formato a seguir:
`project_id`.dataset.INFORMATION_SCHEMA.view
;
por exemplo,
`bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
.
A tabela commits
contém as seguintes colunas aninhadas e repetidas ou só aninhadas:
author
: colunaRECORD
aninhadacommitter
: colunaRECORD
aninhadatrailer
: colunaRECORD
aninhada e repetidadifference
: colunaRECORD
aninhada e repetida
A consulta recuperará os metadados sobre as colunas author
e difference
.
As consultas na visualização INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
precisam ter um qualificador de conjunto de dados. O usuário que envia a consulta precisa ter acesso ao conjunto de dados que contém as tabelas.
Para executar a consulta, faça o seguinte:
Console
Abra a IU da Web do BigQuery no Console do GCP.
Insira a consulta SQL padrão a seguir na caixa Editor de consultas.
INFORMATION_SCHEMA
requer sintaxe SQL padrão. SQL padrão é a sintaxe padrão no Console do GCP.SELECT * FROM `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS WHERE table_name="commits" AND column_name="author" OR column_name="difference"
Clique em Executar.
CLI
Use o comando query
e especifique a sintaxe SQL padrão usando a sinalização --nouse_legacy_sql
ou --use_legacy_sql=false
. A sintaxe SQL padrão é obrigatória para consultas INFORMATION_SCHEMA
.
Para executar a consulta, insira:
bq query --nouse_legacy_sql \ 'SELECT * FROM `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS WHERE table_name="commits" AND column_name="author" OR column_name="difference"'
Os resultados terão a aparência abaixo. Para melhor legibilidade, table_catalog
e table_schema
foram excluídos dos resultados.
+------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+ | table_name | column_name | field_path | data_type | description | +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+ | commits | author | author | STRUCT<name STRING, email STRING, time_sec INT64, tz_offset INT64, date TIMESTAMP> | NULL | | commits | author | author.name | STRING | NULL | | commits | author | author.email | STRING | NULL | | commits | author | author.time_sec | INT64 | NULL | | commits | author | author.tz_offset | INT64 | NULL | | commits | author | author.date | TIMESTAMP | NULL | | commits | difference | difference | ARRAY<STRUCT<old_mode INT64, new_mode INT64, old_path STRING, new_path STRING, old_sha1 STRING, new_sha1 STRING, old_repo STRING, new_repo STRING>> | NULL | | commits | difference | difference.old_mode | INT64 | NULL | | commits | difference | difference.new_mode | INT64 | NULL | | commits | difference | difference.old_path | STRING | NULL | | commits | difference | difference.new_path | STRING | NULL | | commits | difference | difference.old_sha1 | STRING | NULL | | commits | difference | difference.new_sha1 | STRING | NULL | | commits | difference | difference.old_repo | STRING | NULL | | commits | difference | difference.new_repo | STRING | NULL | +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
Como listar tabelas em cluster em um conjunto de dados
Use estes métodos para listar tabelas em cluster em conjuntos de dados:
- Use o Console do GCP ou a IU clássica da Web do BigQuery.
- Use o comando
bq ls
da CLI. - Chame o método de API
tables.list
. - Use bibliotecas de cliente.
As permissões e etapas necessárias para listar tabelas em cluster são as mesmas das tabelas particionadas. Para mais informações sobre como listar tabelas, consulte Como listar tabelas particionadas em um conjunto de dados.
Recursos em desenvolvimento
Os recursos a seguir estão sendo desenvolvidos e ainda não estão disponíveis na versão Alfa:
- Suporte para o agrupamento em cluster de tabelas nativas (não particionadas)
- Redução dos custos para tipos específicos de consultas que usam filtros em agrupamento de colunas em cluster
Próximas etapas
- Para informações sobre como consultar tabelas em cluster, consulte Como consultar tabelas em cluster.
- Para ter 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, clique aqui.
- Para uma visão geral de
INFORMATION_SCHEMA
, acesse Introdução aoINFORMATION_SCHEMA
do BigQuery.