INFORMATION_SCHEMA é uma série de visualizações que fornece 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.
As visualizações 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.
Permissões exigidas
No mínimo, para receber informações sobre tabelas, você precisa ter permissões bigquery.tables.get. Os papéis predefinidos do Cloud IAM a seguir incluem permissões bigquery.tables.get:
bigquery.metadataViewerbigquery.dataViewerbigquery.dataOwnerbigquery.dataEditorbigquery.admin
Além disso, quando um usuário com permissões bigquery.datasets.create cria um conjunto de dados, ele recebe acesso de bigquery.dataOwner ao conjunto.
O acesso de bigquery.dataOwner permite que o usuário recupere metadados da tabela.
Para mais informações sobre papéis e permissões do Cloud IAM no BigQuery, consulte Controle de acesso.
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 seguinte consulta SQL padrão na caixa Editor de consultas.
INFORMATION_SCHEMArequer 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 seu 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 seguinte consulta SQL padrão na caixa Editor de consultas.
INFORMATION_SCHEMArequer 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 STRUCTs 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 seguinte consulta SQL padrão na caixa Editor de consultas.
INFORMATION_SCHEMArequer 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 seguinte consulta SQL padrão na caixa Editor de consultas.
INFORMATION_SCHEMArequer 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 da 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, o projeto bigquery-public-data, você adiciona 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_GENERATEDGENERATION_EXPRESSIONIS_STOREDIS_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 seguinte consulta SQL padrão na caixa Editor de consultas.
INFORMATION_SCHEMArequer 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.
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, o projeto
bigquery-public-data, você adiciona 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: colunaRECORDaninhadacommitter: colunaRECORDaninhadatrailer: colunaRECORDaninhada e repetidadifference: colunaRECORDaninhada 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 seguinte consulta SQL padrão na caixa Editor de consultas.
INFORMATION_SCHEMArequer 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 | +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
Exemplo avançado
O exemplo avançado a seguir consulta as visualizações INFORMATION_SCHEMA.TABLES, TABLE_OPTIONS e COLUMNS para recuperar os metadados sobre as tabelas em mydataset no projeto padrão (myproject). O mydataset contém duas tabelas:
mytable1: usa o mesmo esquema que a tabelacommitsno conjunto de dados públicogithub_reposmytable2: usa o mesmo esquema que a tabelapopulation_by_zip_2010no conjunto de dados públicocensus_bureau_usa
Os resultados são usados por funções definidas pelo usuário para montar as instruções DDL necessárias para recriar as tabelas. Depois, use as declarações DDL nos resultados da consulta para recriar as tabelas no mydataset.
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 seguinte consulta SQL padrão na caixa Editor de consultas.
INFORMATION_SCHEMArequer sintaxe SQL padrão. SQL padrão é a sintaxe padrão no Console do GCP.CREATE TEMP FUNCTION MakePartitionByExpression( column_name STRING, data_type STRING ) AS ( IF( column_name = '_PARTITIONTIME', 'DATE(_PARTITIONTIME)', IF( data_type = 'TIMESTAMP', CONCAT('DATE(', column_name, ')'), column_name ) ) ); CREATE TEMP FUNCTION MakePartitionByClause( columns ARRAY<STRUCT<column_name STRING, data_type STRING, is_nullable STRING, is_partitioning_column STRING, clustering_ordinal_position INT64>> ) AS ( IFNULL( CONCAT( 'PARTITION BY ', (SELECT MakePartitionByExpression(column_name, data_type) FROM UNNEST(columns) WHERE is_partitioning_column = 'YES'), '\n'), '' ) ); CREATE TEMP FUNCTION MakeClusterByClause( columns ARRAY<STRUCT<column_name STRING, data_type STRING, is_nullable STRING, is_partitioning_column STRING, clustering_ordinal_position INT64>> ) AS ( IFNULL( CONCAT( 'CLUSTER BY ', (SELECT STRING_AGG(column_name, ', ' ORDER BY clustering_ordinal_position) FROM UNNEST(columns) WHERE clustering_ordinal_position IS NOT NULL), '\n' ), '' ) ); CREATE TEMP FUNCTION MakeNullable(data_type STRING, is_nullable STRING) AS ( IF(not STARTS_WITH(data_type, 'ARRAY<') and is_nullable = 'NO', ' NOT NULL', '') ); CREATE TEMP FUNCTION MakeColumnList( columns ARRAY<STRUCT<column_name STRING, data_type STRING, is_nullable STRING, is_partitioning_column STRING, clustering_ordinal_position INT64>> ) AS ( IFNULL( CONCAT( '(\n', (SELECT STRING_AGG(CONCAT(' ', column_name, ' ', data_type, MakeNullable(data_type, is_nullable)), ',\n') FROM UNNEST(columns)), '\n)\n' ), '' ) ); CREATE TEMP FUNCTION MakeOptionList( options ARRAY<STRUCT<option_name STRING, option_value STRING>> ) AS ( IFNULL( CONCAT( 'OPTIONS (\n', (SELECT STRING_AGG(CONCAT(' ', option_name, '=', option_value), ',\n') FROM UNNEST(options)), '\n)\n'), '' ) ); WITH Components AS ( SELECT CONCAT('`', table_catalog, '.', table_schema, '.', table_name, '`') AS table_name, ARRAY_AGG( STRUCT(column_name, data_type, is_nullable, is_partitioning_column, clustering_ordinal_position) ORDER BY ordinal_position ) AS columns, (SELECT ARRAY_AGG(STRUCT(option_name, option_value)) FROM mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS AS t2 WHERE t.table_name = t2.table_name) AS options FROM mydataset.INFORMATION_SCHEMA.TABLES AS t LEFT JOIN mydataset.INFORMATION_SCHEMA.COLUMNS USING (table_catalog, table_schema, table_name) WHERE table_type = 'BASE TABLE' GROUP BY table_catalog, table_schema, t.table_name ) SELECT CONCAT( 'CREATE OR REPLACE TABLE ', table_name, '\n', MakeColumnList(columns), MakePartitionByClause(columns), MakeClusterByClause(columns), MakeOptionList(options)) FROM Components
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, digite:
QUERY_TEXT=$(cat <<ENDQUERY
CREATE TEMP FUNCTION MakePartitionByExpression(
column_name STRING, data_type STRING
) AS (
IF(
column_name = '_PARTITIONTIME',
'DATE(_PARTITIONTIME)',
IF(
data_type = 'TIMESTAMP',
CONCAT('DATE(', column_name, ')'),
column_name
)
)
);
CREATE TEMP FUNCTION MakePartitionByClause(
columns ARRAY<STRUCT<column_name STRING, data_type STRING, is_nullable STRING, is_partitioning_column STRING, clustering_ordinal_position INT64>>
) AS (
IFNULL(
CONCAT(
'PARTITION BY ',
(SELECT MakePartitionByExpression(column_name, data_type)
FROM UNNEST(columns) WHERE is_partitioning_column = 'YES'),
'\n'),
''
)
);
CREATE TEMP FUNCTION MakeClusterByClause(
columns ARRAY<STRUCT<column_name STRING, data_type STRING, is_nullable STRING, is_partitioning_column STRING, clustering_ordinal_position INT64>>
) AS (
IFNULL(
CONCAT(
'CLUSTER BY ',
(SELECT STRING_AGG(column_name, ', ' ORDER BY clustering_ordinal_position)
FROM UNNEST(columns) WHERE clustering_ordinal_position IS NOT NULL),
'\n'
),
''
)
);
CREATE TEMP FUNCTION MakeNullable(data_type STRING, is_nullable STRING)
AS (
IF(not STARTS_WITH(data_type, 'ARRAY<') and is_nullable = 'NO', ' NOT NULL', '')
);
CREATE TEMP FUNCTION MakeColumnList(
columns ARRAY<STRUCT<column_name STRING, data_type STRING, is_nullable STRING, is_partitioning_column STRING, clustering_ordinal_position INT64>>
) AS (
IFNULL(
CONCAT(
'(\n',
(SELECT STRING_AGG(CONCAT(' ', column_name, ' ', data_type, MakeNullable(data_type, is_nullable)), ',\n')
FROM UNNEST(columns)),
'\n)\n'
),
''
)
);
CREATE TEMP FUNCTION MakeOptionList(
options ARRAY<STRUCT<option_name STRING, option_value STRING>>
) AS (
IFNULL(
CONCAT(
'OPTIONS (\n',
(SELECT STRING_AGG(CONCAT(' ', option_name, '=', option_value), ',\n') FROM UNNEST(options)),
'\n)\n'),
''
)
);
WITH Components AS (
SELECT
CONCAT('\`', table_catalog, '.', table_schema, '.', table_name, '\`') AS table_name,
ARRAY_AGG(
STRUCT(column_name, data_type, is_nullable, is_partitioning_column, clustering_ordinal_position)
ORDER BY ordinal_position
) AS columns,
(SELECT ARRAY_AGG(STRUCT(option_name, option_value))
FROM mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS AS t2
WHERE t.table_name = t2.table_name) AS options
FROM mydataset.INFORMATION_SCHEMA.TABLES AS t
LEFT JOIN mydataset.INFORMATION_SCHEMA.COLUMNS
USING (table_catalog, table_schema, table_name)
WHERE table_type = 'BASE TABLE'
GROUP BY table_catalog, table_schema, t.table_name
)
SELECT
CONCAT(
'CREATE OR REPLACE TABLE ',
table_name,
'\n',
MakeColumnList(columns),
MakePartitionByClause(columns),
MakeClusterByClause(columns),
MakeOptionList(options))
FROM Components
ENDQUERY
)
bq query --nouse_legacy_sql "$QUERY_TEXT"
O resultado será semelhante a este:
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | f0_ | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | CREATE OR REPLACE TABLE `myproject.mydataset.population_by_zip_2010` | | ( | | zipcode STRING NOT NULL, | | geo_id STRING, | | minimum_age INT64, | | maximum_age INT64, | | gender STRING, | | population INT64 | | ) | | OPTIONS ( | | expiration_timestamp=TIMESTAMP "2019-04-17T02:10:32.055Z" | | ) | | CREATE OR REPLACE TABLE `myproject.mydataset.commits` | | ( | | commit STRING, | | tree STRING, | | parent ARRAY<STRING>, | | author STRUCT<name STRING, email STRING, time_sec INT64, tz_offset INT64, date TIMESTAMP>, | | committer STRUCT<name STRING, email STRING, time_sec INT64, tz_offset INT64, date TIMESTAMP>, | | subject STRING, | | message STRING, | | trailer ARRAY<STRUCT<key STRING, value STRING, email STRING>>, | | 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>>, | | difference_truncated BOOL, | | repo_name ARRAY<STRING>, | | encoding STRING | | ) | | OPTIONS ( | | expiration_timestamp=TIMESTAMP "2019-04-17T03:12:03.248Z" | | ) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
A seguir
- Para uma visão geral de
INFORMATION_SCHEMA, acesse Introdução ao BigQueryINFORMATION_SCHEMA. - Saiba como usar
INFORMATION_SCHEMApara receber metadados de conjuntos de dados. - Saiba como usar
INFORMATION_SCHEMApara receber metadados de visualizações.
Precisa de ajuda? Acesse nossa