Como receber metadados de tabelas usando INFORMATION_SCHEMA
As seguintes visualizações INFORMATION_SCHEMA
contêm metadados da tabela:
TABLES
eTABLE_OPTIONS
para metadados sobre tabelasCOLUMNS
eCOLUMN_FIELD_PATHS
para metadados sobre colunas e camposPARTITIONS
para metadados de partições de tabela (visualização)TABLE_STORAGE
para metadados sobre o uso atual do armazenamento de tabelas (prévia).TABLE_STORAGE_TIMELINE_BY_PROJECT
eTABLE_STORAGE_TIMELINE_BY_ORGANIZATION
para metadados sobre o uso histórico de armazenamento de tabelas (prévia).
As visualizações de armazenamento de tabelas oferecem uma maneira conveniente de observar o consumo
de armazenamento atual e histórico, incluindo bytes lógicos, compactados e de
manutenção. Essas informações podem ajudar em tarefas como planejamento para o crescimento futuro
e compreensão dos padrões de atualização de tabelas, mesmo para tabelas que não
tenham uma coluna de carimbo de data/hora last_update
como parte do esquema.
TABLES
e TABLE_OPTIONS
também contêm informações de alto nível sobre visualizações.
Para informações detalhadas, consulte a visualização INFORMATION_SCHEMA.VIEWS
.
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
Use a tabela a seguir para entender quais permissões do IAM você
precisa para consultar as visualizações INFORMATION_SCHEMA
.
Para a visualização INFORMATION_SCHEMA.TABLE_STORAGE_TIMELINE_BY_ORGANIZATION
,
é preciso ter permissões concedidas no nível da organização. As
outras visualizações só exigem permissões concedidas no nível do projeto. Para mais
informações, consulte
Gerenciar o acesso a projetos, pastas e organizações.
Ver | Permissões | Papéis que concedem essas permissões |
---|---|---|
TABLES |
bigquery.tables.get |
roles/bigquery.admin |
TABLE_OPTIONS |
bigquery.tables.get |
roles/bigquery.admin |
COLUMNS |
bigquery.tables.get |
roles/bigquery.admin |
COLUMN_FIELD_PATHS |
bigquery.tables.get |
roles/bigquery.admin |
PARTITIONS |
bigquery.tables.get |
roles/bigquery.admin |
TABLE_STORAGE |
bigquery.tables.get |
roles/bigquery.admin |
TABLE_STORAGE_TIMELINE_BY_ORGANIZATION TABLE_STORAGE_TIMELINE_BY_PROJECT |
bigquery.tables.get |
roles/bigquery.admin |
Para mais informações sobre permissões granulares do BigQuery, consulte papéis e permissões.
Sintaxe
As consultas a qualquer uma dessas visualizações precisam ter um qualificador de conjunto de dados ou região.
-- Returns metadata for tables in a single dataset.
SELECT * FROM myDataset.INFORMATION_SCHEMA.TABLES;
-- Returns metadata for tables in a region.
SELECT * FROM region-us.INFORMATION_SCHEMA.TABLES;
Para consultas com um qualificador de conjunto de dados, é preciso ter permissões para o conjunto de dados. Para consultas com um qualificador de região, é preciso ter permissões para o projeto.
Visualização TABLES
Os resultados das consultas na visualização INFORMATION_SCHEMA.TABLES
contêm uma linha para cada tabela ou visualização do conjunto de dados. Para informações detalhadas sobre
visualizações, consulte a visualização
INFORMATION_SCHEMA.VIEWS
.
A visualização INFORMATION_SCHEMA.TABLES
tem o seguinte esquema:
Nome da coluna | Tipo de dados | Valor |
---|---|---|
table_catalog |
STRING |
O ID 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, que pode ser:
|
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 |
ddl |
STRING |
A instrução DDL
que pode ser usada para recriar a tabela, como
CREATE TABLE
ou CREATE VIEW |
clone_time |
TIMESTAMP |
Para clones de tabelas
(visualização),
a hora em que a tabela base foi
clonada para criar a
tabela. Se a
viagem no tempo foi usada, esse
campo vai conter o carimbo de data/hora da viagem. Caso contrário, o
campo clone_time será igual ao
campo creation_time . Aplicável apenas a
tabelas com table_type definido como CLONE .
|
base_table_catalog |
STRING |
Para clones de tabelas
(visualização),
o projeto da tabela base. Aplicável apenas a
tabelas com table_type definido como CLONE .
|
base_table_schema |
STRING |
Para clones de tabelas
(visualização),
o conjunto de dados da tabela base. Aplicável apenas a tabelas com
table_type definido como CLONE . |
base_table_name |
STRING |
Para clones de tabelas
(visualização),
o nome da tabela base. Aplicável apenas a tabelas com
table_type definido como CLONE . |
Exemplo de consulta
Exemplo 1:
O exemplo a seguir recupera metadados de tabela para todas as tabelas no
conjunto de dados chamado mydataset
. A consulta seleciona todas as colunas da
visualização INFORMATION_SCHEMA.TABLES
, exceto is_typed
, que é reservada para
uso futuro, e ddl
, que está oculta das consultas SELECT *
. Os metadados
retornados correspondem a todas as tabelas em mydataset
no projeto padrão.
mydataset
contém as seguintes tabelas:
mytable1
: uma tabela padrão do BigQuerymyview1
: uma visualização do BigQuery
Para executar a consulta em um projeto diferente do projeto padrão, adicione o ID do projeto ao conjunto de dados no seguinte formato: `project_id`.dataset.INFORMATION_SCHEMA.view
. Veja um exemplo: `myproject`.mydataset.INFORMATION_SCHEMA.TABLES
.
Para executar a consulta, faça o seguinte:
Console
Abra a página do BigQuery no console do Cloud.
Insira a consulta SQL padrão a seguir na caixa Editor de consultas.
INFORMATION_SCHEMA
requer sintaxe SQL padrão. O SQL padrão é a sintaxe padrão do console do Cloud.SELECT * EXCEPT(is_typed) FROM mydataset.INFORMATION_SCHEMA.TABLES
Clique em Executar.
bq
Use o comando bq 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 o seguinte formato:
+----------------+---------------+----------------+------------+--------------------+---------------------+---------------------------------------------+ | table_catalog | table_schema | table_name | table_type | is_insertable_into | creation_time | ddl | +----------------+---------------+----------------+------------+--------------------+---------------------+---------------------------------------------+ | myproject | mydataset | mytable1 | BASE TABLE | YES | 2018-10-29 20:34:44 | CREATE TABLE `myproject.mydataset.mytable1` | | | | | | | | ( | | | | | | | | id INT64 | | | | | | | | ); | | myproject | mydataset | myview1 | VIEW | NO | 2018-12-29 00:19:20 | CREATE VIEW `myproject.mydataset.myview1` | | | | | | | | AS SELECT 100 as id; | +----------------+---------------+----------------+------------+--------------------+---------------------+---------------------------------------------+
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 e a coluna ddl
está oculta. Os metadados retornados são para tabelas em
mydataset
no projeto padrão.
Para executar a consulta em um projeto diferente do padrão, adicione o ID do projeto ao conjunto de dados no seguinte formato: `project_id`.dataset.INFORMATION_SCHEMA.view
; por exemplo, `myproject`.mydataset.INFORMATION_SCHEMA.TABLES
.
Para executar a consulta, faça o seguinte:
Console
Abra a página do BigQuery no console do Cloud.
Insira a consulta SQL padrão a seguir na caixa Editor de consultas.
INFORMATION_SCHEMA
requer sintaxe SQL padrão. O SQL padrão é a sintaxe padrão do console do Cloud.SELECT * EXCEPT(is_typed) FROM mydataset.INFORMATION_SCHEMA.TABLES WHERE table_type="BASE TABLE"
Clique em Executar.
bq
Use o comando bq 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 | ddl |
+----------------+---------------+----------------+------------+--------------------+---------------------+---------------------------------------------+
| myproject | mydataset | mytable1 | BASE TABLE | YES | 2018-10-31 22:40:05 | CREATE TABLE myproject.mydataset.mytable1
|
| | | | | | | ( |
| | | | | | | id INT64 |
| | | | | | | ); |
+----------------+---------------+----------------+------------+--------------------+---------------------+---------------------------------------------+
Exemplo 3:
O exemplo a seguir recupera colunas table_name
e ddl
da visualização INFORMATION_SCHEMA.TABLES
para a tabela population_by_zip_2010
no
conjunto de dados
census_bureau_usa
. Esse conjunto de dados faz parte do
programa de conjunto de dados públicos do BigQuery.
Como a tabela que você está consultando está em outro projeto, adicione o ID do projeto ao conjunto de dados no
seguinte formato:
`project_id`.dataset.INFORMATION_SCHEMA.view
.
Neste exemplo, o valor é
`bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES
.
Para executar a consulta, faça o seguinte:
Console
Abra a página do BigQuery no console do Cloud.
Insira a consulta SQL padrão a seguir na caixa Editor de consultas.
INFORMATION_SCHEMA
requer sintaxe SQL padrão. O SQL padrão é a sintaxe padrão do console do Cloud.SELECT table_name, ddl FROM `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES WHERE table_name="population_by_zip_2010"
Clique em Executar.
bq
Use o comando bq 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 table_name, ddl FROM `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES WHERE table_name="population_by_zip_2010"'
Os resultados terão o seguinte formato:
+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | table_name | ddl | +------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | population_by_zip_2010 | CREATE TABLE `bigquery-public-data.census_bureau_usa.population_by_zip_2010` | | | ( | | | geo_id STRING OPTIONS(description="Geo code"), | | | zipcode STRING NOT NULL OPTIONS(description="Five digit ZIP Code Tabulation Area Census Code"), | | | population INT64 OPTIONS(description="The total count of the population for this segment."), | | | minimum_age INT64 OPTIONS(description="The minimum age in the age range. If null, this indicates the row as a total for male, female, or overall population."), | | | maximum_age INT64 OPTIONS(description="The maximum age in the age range. If null, this indicates the row as having no maximum (such as 85 and over) or the row is a total of the male, female, or overall population."), | | | gender STRING OPTIONS(description="male or female. If empty, the row is a total population summary.") | | | ) | | | OPTIONS( | | | labels=[("freebqcovid", "")] | | | ); | +------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Visualização TABLE_OPTIONS
Os resultados das consultas na visualização INFORMATION_SCHEMA.TABLE_OPTIONS
contêm uma linha para cada opção, para cada tabela ou visualização do conjunto de dados. Para informações detalhadas sobre visualizações, consulte a visualização INFORMATION_SCHEMA.VIEWS
.
A visualização INFORMATION_SCHEMA.TABLE_OPTIONS
tem o seguinte esquema:
Nome da coluna | Tipo de dados | Valor |
---|---|---|
TABLE_CATALOG |
STRING |
O ID 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 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 hora em que esta tabela expira |
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 as identificações na tabela |
require_partition_filter |
BOOL |
Se as consultas na tabela exigem um filtro de partição |
enable_refresh |
BOOL |
Se a atualização automática está ativada para uma visualização materializada. |
refresh_interval_minutes |
FLOAT64 |
Com que frequência uma visualização materializada é atualizada |
Em tabelas externas, as seguintes opções também podem ser usadas:
Opções | |
---|---|
allow_jagged_rows |
Se Aplica-se a dados CSV. |
allow_quoted_newlines |
Se Aplica-se a dados CSV. |
compression |
O tipo de compactação da fonte de dados. Os valores aceitos são:
Aplica-se a dados CSV e JSON. |
enable_logical_types |
Se Aplica-se a dados Avro. |
encoding |
A codificação de caracteres dos dados. Os valores aceitos incluem:
Aplica-se a dados CSV. |
field_delimiter |
O separador de campos em um arquivo CSV. Aplica-se a dados CSV. |
format |
O formato dos dados externos.
Os valores aceitos são: O valor |
decimal_target_types |
Determina como converter um tipo Exemplo: |
json_extension |
Para dados JSON, indica um determinado formato de intercâmbio JSON. Se não for especificado, o BigQuery lerá os dados como registros JSON genéricos. Os valores aceitos são: |
hive_partition_uri_prefix |
Um prefixo comum para todos os URIs de origem antes do início da codificação da chave de partição. Aplica-se apenas a tabelas externas particionadas pelo Hive. Aplica-se aos dados Avro, CSV, JSON, Parquet e ORC. Exemplo: |
ignore_unknown_values |
Se for Aplica-se a dados CSV e JSON. |
max_bad_records |
O número máximo de registros corrompidos a serem ignorados durante a leitura dos dados. Aplica-se a: dados CSV, JSON e Planilhas. |
null_marker |
A string que representa os valores Aplica-se a dados CSV. |
projection_fields |
Uma lista de propriedades da entidade a serem carregadas. Aplica-se aos dados do Datastore. |
quote |
A string usada para citar seções de dados em um arquivo CSV. Se os dados
contiverem caracteres de nova linha entre aspas, defina também a
propriedade Aplica-se a dados CSV. |
require_hive_partition_filter |
Se Aplica-se aos dados Avro, CSV, JSON, Parquet e ORC. |
sheet_range |
Intervalo de uma planilha do Planilhas a ser consultada. Aplicável aos dados do Planilhas. Exemplo: |
skip_leading_rows |
O número de linhas na parte superior de um arquivo a ser ignorado na leitura dos dados. Aplicável aos dados CSV e Planilhas. |
uris |
Uma matriz de URIs totalmente qualificados para os locais de dados externos. Exemplo: |
Exemplo de consulta
Exemplo 1:
O exemplo a seguir recupera os prazos de validade da tabela padrão para todas as tabelas em mydataset
no seu projeto padrão (myproject
) consultando a visualização INFORMATION_SCHEMA.TABLE_OPTIONS
.
Para executar a consulta em um projeto diferente do projeto padrão, adicione o código do projeto ao conjunto de dados no seguinte formato: `project_id`.dataset.INFORMATION_SCHEMA.view
. Veja um exemplo: `myproject`.mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
.
Para executar a consulta, faça o seguinte:
Console
Abra a página do BigQuery no console do Cloud.
Insira a consulta SQL padrão a seguir na caixa Editor de consultas.
INFORMATION_SCHEMA
requer sintaxe SQL padrão. O SQL padrão é a sintaxe padrão do console do Cloud.SELECT * FROM mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS WHERE option_name="expiration_timestamp"
Clique em Executar.
bq
Use o comando bq 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 o seguinte formato:
+----------------+---------------+------------+----------------------+-------------+--------------------------------------+ | 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 da opção description
para encontrar tabelas que contenham "teste" em qualquer parte da descrição. mydataset
está no projeto padrão (myproject
).
Para executar a consulta em um projeto diferente do projeto padrão, adicione o ID do projeto ao conjunto de dados no seguinte formato: `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 página do BigQuery no console do Cloud.
Insira a consulta SQL padrão a seguir na caixa Editor de consultas.
INFORMATION_SCHEMA
requer sintaxe SQL padrão. O SQL padrão é a sintaxe padrão do console do Cloud.SELECT * FROM mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS WHERE option_name="description" AND option_value LIKE "%test%"
Clique em Executar.
bq
Use o comando bq 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 o seguinte formato:
+----------------+---------------+------------+-------------+-------------+--------------+ | 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
Os resultados das consultas na visualização INFORMATION_SCHEMA.COLUMNS
contêm uma linha para cada coluna (campo) da tabela.
A visualização INFORMATION_SCHEMA.COLUMNS
tem o seguinte esquema:
Nome da coluna | Tipo de dados | Valor |
---|---|---|
TABLE_CATALOG |
STRING |
O ID 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 se a coluna é uma coluna de particionamento ou não |
CLUSTERING_ORDINAL_POSITION |
INT64 |
O deslocamento de índice 1 da coluna nas colunas de cluster da tabela. O valor será NULL se a tabela não for uma tabela em cluster |
Exemplo de consulta
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 código do projeto ao conjunto de dados no seguinte formato: `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
Para executar a consulta, faça o seguinte:
Console
Abra a página do BigQuery no console do Cloud.
Insira a consulta SQL padrão a seguir na caixa Editor de consultas.
INFORMATION_SCHEMA
requer sintaxe SQL padrão. O SQL padrão é a sintaxe padrão do console do Cloud.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.
bq
Use o comando bq 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 facilitar a leitura, 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
Os resultados da consulta contêm uma linha para cada coluna aninhada em uma coluna RECORD
(ou STRUCT
).
Os resultados das consultas na visualização INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
contêm uma linha para cada coluna aninhada dentro de uma coluna RECORD
(ou STRUCT
).
A visualização INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
tem o seguinte esquema:
Nome da coluna | Tipo de dados | Valor |
---|---|---|
TABLE_CATALOG |
STRING |
O ID 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 |
Exemplo de consulta
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 código do projeto ao conjunto de dados no seguinte formato: `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 vai recuperar os metadados sobre as colunas author
e difference
.
Para executar a consulta, faça o seguinte:
Console
Abra a página do BigQuery no console do Cloud.
Insira a consulta SQL padrão a seguir na caixa Editor de consultas.
INFORMATION_SCHEMA
requer sintaxe SQL padrão. O SQL padrão é a sintaxe padrão do console do Cloud.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.
bq
Use o comando bq 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 o seguinte formato: Para facilitar a leitura, 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 | +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
Visualização PARTITIONS
Quando você consulta a visualização INFORMATION_SCHEMA.PARTITIONS
, os resultados da consulta
contêm uma linha para cada partição.
A visualização INFORMATION_SCHEMA.PARTITIONS
tem o seguinte esquema:
Nome da coluna | Tipo de dados | Valor |
---|---|---|
TABLE_CATALOG |
STRING |
O ID do projeto que contém a tabela |
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, também conhecido como tableId |
PARTITION_ID |
STRING |
Um ID de partição única. Para tabelas não particionadas, o valor é
NULL . Para tabelas particionadas que contêm linhas com valores
NULL na coluna de particionamento, o valor é
__NULL__ . |
TOTAL_ROWS |
INTEGER |
O número total de linhas na partição |
TOTAL_LOGICAL_BYTES |
INTEGER |
O número total de bytes lógicos na partição |
TOTAL_BILLABLE_BYTES |
INTEGER |
O número total de bytes faturáveis na partição |
LAST_MODIFIED_TIME |
TIMESTAMP |
A hora em que os dados foram gravados mais recentemente na partição |
STORAGE_TIER |
STRING |
Nível de armazenamento da partição:
|
Exemplo de consulta
No exemplo a seguir, calculamos a quantidade de bytes usados por cada nível de armazenamento
em todas as tabelas no conjunto de dados com o nome mydataset
.
Para executar a consulta, faça o seguinte:
Console
Abra a página do BigQuery no console do Cloud.
Insira a consulta SQL padrão a seguir na caixa Editor de consultas.
INFORMATION_SCHEMA
requer sintaxe SQL padrão. O SQL padrão é a sintaxe padrão do console do Cloud.SELECT storage_tier, SUM(total_billable_bytes) billable_bytes FROM `mydataset.INFORMATION_SCHEMA.PARTITIONS` GROUP BY storage_tier
Clique em Executar.
bq
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 storage_tier, SUM(total_billable_bytes) billable_bytes FROM `mydataset.INFORMATION_SCHEMA.PARTITIONS` GROUP BY storage_tier'
Os resultados são semelhantes à tabela a seguir:
+--------------+----------------+ | storage_tier | billable_bytes | +--------------+----------------+ | LONG_TERM | 1311495144879 | | ACTIVE | 66757629240 | +--------------+----------------+
Visualização TABLE_STORAGE
SELECT
CONCAT(v1.table_catalog, ":", v1.table_schema, ".", v1.table_name) AS unmodified_table_name,
FROM
`region-REGION`.INFORMATION_SCHEMA.TABLES v1
LEFT JOIN `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE v2 ON v1.table_name = v2.table_name
WHERE v2.table_name IS NULL;
A visualização INFORMATION_SCHEMA.TABLE_STORAGE
fornece um snapshot atual do
uso do armazenamento das tabelas
e visualizações materializadas. Quando você consulta a visualização INFORMATION_SCHEMA.TABLE_STORAGE
,
os resultados da consulta contêm uma linha para cada tabela ou visualização materializada. Os
dados dessa tabela não são mantidos em tempo real e podem atrasar de
alguns segundos a alguns minutos.
Os dados da visualização são regionalizados. Portanto, é necessário usar um qualificador de região em consultas na visualização. O projeto padrão será usado se você não especificar um.
Os exemplos a seguir mostram como retornar dados de um projeto ou região.
Retorna informações de armazenamento de tabelas em um projeto especificado:
SELECT * FROM myProject.`region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE;
Retorna informações de armazenamento de tabelas em uma região especificada:
SELECT * FROM `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE;
A visualização INFORMATION_SCHEMA.TABLE_STORAGE
tem o seguinte esquema:
Nome da coluna | Tipo de dados | Valor |
---|---|---|
PROJECT_ID |
STRING |
O ID do projeto que contém o conjunto de dados |
PROJECT_NAME |
INT64 |
O número 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 |
CREATION_TIME |
TIMESTAMP |
O horário de criação da tabela |
TOTAL_ROWS |
INT64 |
O número total de linhas na tabela ou visualização materializada |
TOTAL_PARTITIONS |
INT64 |
O número de partições presentes na tabela ou na visualização materializada. Tabelas não particionadas retornam 0. |
TOTAL_LOGICAL_BYTES |
INT64 |
Número total de bytes lógicos na tabela ou visualização materializada |
ACTIVE_LOGICAL_BYTES |
INT64 |
Número de bytes lógicos com menos de 90 dias |
LONG_TERM_LOGICAL_BYTES |
INT64 |
Número de bytes lógicos com mais de 90 dias |
TOTAL_PHYSICAL_BYTES |
INT64 |
Número total de bytes físicos usados para armazenamento, incluindo bytes ativos, de longo prazo e de viagem no tempo (para tabelas excluídas) |
ACTIVE_PHYSICAL_BYTES |
INT64 |
Número de bytes físicos com menos de 90 dias |
LONG_TERM_PHYSICAL_BYTES |
INT64 |
Número de bytes físicos com mais de 90 dias |
TIME_TRAVEL_PHYSICAL_BYTES |
INT64 |
Número de bytes físicos usados pelo armazenamento de viagem no tempo (dados excluídos ou alterados) |
Examples
O exemplo a seguir mostra quais projetos da organização estão usando mais armazenamento.
Para executar a consulta, faça o seguinte:
Console
Abra a página do BigQuery no console do Cloud.
Insira a consulta SQL padrão a seguir na caixa Editor de consultas.
INFORMATION_SCHEMA
requer sintaxe SQL padrão. O SQL padrão é a sintaxe padrão do console do Cloud.SELECT project_id, SUM(total_logical_bytes) AS total_logical_bytes FROM `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE GROUP BY project_id ORDER BY total_logical_bytes DESC;
Clique em Executar.
bq
Use o comando bq 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 project_id, SUM(total_logical_bytes) AS total_logical_bytes FROM `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE GROUP BY project_id ORDER BY total_logical_bytes DESC;'
Os resultados vão ter o seguinte formato:
+---------------------+---------------------+ | project_id | total_logical_bytes | +---------------------+---------------------+ | projecta | 971329178274633 | +---------------------+---------------------+ | projectb | 834638211024843 | +---------------------+---------------------+ | projectc | 562910385625126 | +---------------------+---------------------+
VisualizaçõesTABLE_STORAGE_TIMELINE_BY_
As visualizações da linha do tempo do armazenamento de tabela retornam uma linha para cada evento que aciona uma alteração de armazenamento, como gravação, atualização ou exclusão de uma linha. Isso significa que pode haver várias linhas para uma tabela em um único dia. Ao consultar uma visualização para um período, use o carimbo de data/hora mais recente no dia pretendido.
As seguintes visualizações de linha do tempo do armazenamento de tabelas estão disponíveis:
INFORMATION_SCHEMA.TABLE_STORAGE_TIMELINE_BY_PROJECT
retorna informações de todas as tabelas no projeto atual ou especificado.INFORMATION_SCHEMA.TABLE_STORAGE_TIMELINE_BY_ORGANIZATION
retorna informações de todas as tabelas na pasta pai do projeto atual ou especificado, incluindo os projetos em subpastas abaixo dele.
Os dados da visualização são regionalizados. Portanto, é necessário usar um qualificador de região em consultas na visualização.
As visualizações da linha do tempo do armazenamento da tabela têm o seguinte esquema:
Nome da coluna | Tipo de dados | Valor |
---|---|---|
TIMESTAMP |
TIMESTAMP |
Carimbo de data/hora do último cálculo do armazenamento. O novo cálculo é acionado por alterações nos dados da tabela. |
DELETED |
BOOLEAN |
Indica se a tabela foi excluída. |
PROJECT_ID |
STRING |
O ID do projeto que contém o conjunto de dados |
PROJECT_NAME |
INT64 |
O número 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 |
CREATION_TIME |
TIMESTAMP |
O horário de criação da tabela |
TOTAL_ROWS |
INT64 |
O número total de linhas na tabela ou visualização materializada |
TOTAL_PARTITIONS |
INT64 |
O número de partições da tabela ou da visualização materializada. Tabelas não particionadas retornam 0. |
TOTAL_LOGICAL_BYTES |
INT64 |
Número total de bytes lógicos na tabela ou visualização materializada |
ACTIVE_LOGICAL_BYTES |
INT64 |
Número de bytes lógicos com menos de 90 dias |
LONG_TERM_LOGICAL_BYTES |
INT64 |
Número de bytes lógicos com mais de 90 dias |
TOTAL_PHYSICAL_BYTES |
INT64 |
Número total de bytes físicos usados para armazenamento, incluindo bytes ativos, de longo prazo e de viagem no tempo (para tabelas excluídas) |
ACTIVE_PHYSICAL_BYTES |
INT64 |
Número de bytes físicos com menos de 90 dias |
LONG_TERM_PHYSICAL_BYTES |
INT64 |
Número de bytes físicos com mais de 90 dias |
TIME_TRAVEL_PHYSICAL_BYTES |
INT64 |
Número de bytes físicos usados pelo armazenamento de viagem no tempo (dados excluídos ou alterados) |
Examples
Exemplo 1:
O exemplo a seguir mostra quais tabelas estão usando mais armazenamento em um conjunto de dados específico.
Para executar a consulta, faça o seguinte:
Console
Abra a página do BigQuery no console do Cloud.
Insira a consulta SQL padrão a seguir na caixa Editor de consultas.
INFORMATION_SCHEMA
requer sintaxe SQL padrão. O SQL padrão é a sintaxe padrão do console do Cloud.SELECT timestamp AS start_time, table_name, total_logical_bytes FROM `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE_TIMELINE_BY_PROJECT WHERE table_schema = "TABLE_SCHEMA" AND table_name = "TABLE_NAME" ORDER BY start_time DESC;
Clique em Executar.
bq
Use o comando bq 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 timestamp AS start_time, table_name, total_logical_bytes FROM `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE_TIMELINE_BY_PROJECT WHERE table_schema = "TABLE_SCHEMA" AND table_name = "TABLE_NAME" ORDER BY start_time DESC;'
Os resultados terão o seguinte formato:
------------------------+---------------------+----------------------+ | start_time | table_name | total_logical_bytes | +-----------------------+---------------------+----------------------+ | 2022-03-30 17:39:54 | table1 | 322 | | 2022-03-30 17:39:54 | table2 | 1657 | | 2022-03-30 17:39:53 | table1 | 320 | | 2022-03-30 17:39:53 | table2 | 1655 | +-----------------------+---------------------+----------------------+
Exemplo 2:
O exemplo a seguir mostra a soma do armazenamento físico usado por cada projeto na organização em um determinado momento.
Para executar a consulta, faça o seguinte:
Console
Abra a página do BigQuery no console do Cloud.
Insira a consulta SQL padrão a seguir na caixa Editor de consultas.
INFORMATION_SCHEMA
requer sintaxe SQL padrão. O SQL padrão é a sintaxe padrão do console do Cloud.WITH most_recent_records as ( SELECT project_id, table_schema, table_name, MAX(timestamp) as max_timestamp FROM `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE_TIMELINE_BY_ORGANIZATION WHERE timestamp <= "TIMESTAMP" GROUP BY project_id, table_schema, table_name ) SELECT i_s.project_id, SUM(i_s.total_physical_bytes) AS TotalPhysicalBytes FROM `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE_TIMELINE_BY_ORGANIZATION as i_s JOIN most_recent_records ON i_s.project_id=most_recent_records.project_id AND i_s.table_schema=most_recent_records.table_schema AND i_s.table_name=most_recent_records.table_name AND i_s.timestamp = most_recent_records.max_timestamp GROUP BY project_id;
Clique em Executar.
bq
Use o comando bq 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 \ 'WITH most_recent_records as ( SELECT project_id, table_schema, table_name, MAX(timestamp) as max_timestamp FROM `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE_TIMELINE_BY_ORGANIZATION WHERE timestamp <= "TIMESTAMP" GROUP BY project_id, table_schema, table_name ) SELECT i_s.project_id, SUM(i_s.total_physical_bytes) AS TotalPhysicalBytes FROM `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE_TIMELINE_BY_ORGANIZATION as i_s JOIN most_recent_records ON i_s.project_id=most_recent_records.project_id AND i_s.table_schema=most_recent_records.table_schema AND i_s.table_name=most_recent_records.table_name AND i_s.timestamp = most_recent_records.max_timestamp GROUP BY project_id;'
Os resultados terão o seguinte formato:
-----------------+------------------------+ | project_id | TotalPhysicalBytes | +----------------+------------------------+ | projecta | 3844 | | projectb | 16022778 | | projectc | 8934009 | +----------------+------------------------+