Como receber metadados de tabelas usando INFORMATION_SCHEMA

INFORMATION_SCHEMA contém estas visualizações para metadados de tabela:

• TABLES e TABLE_OPTIONS para metadados sobre tabelas
• COLUMNS e COLUMN_FIELD_PATHS para metadados sobre colunas e campos

TABLES e TABLE_OPTIONS também contêm informações de alto nível sobre visualizações. Para informações detalhadas, consulte VIEWS.

Permissões necessárias

Para TABLES e TABLE_OPTIONS, é necessário receber estas permissões:

• bigquery.tables.get
• bigquery.tables.list
• bigquery.routines.get
• bigquery.routines.list

Para COLUMNS e COLUMN_FIELD_PATHS, é necessário receber estas permissões:

• bigquery.tables.get
• bigquery.tables.list

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;

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.

A visualização INFORMATION_SCHEMA.TABLES tem o seguinte esquema:

Nome da coluna	Tipo de dados	Valor
TABLE_CATALOG	STRING	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: BASE TABLE: uma tabela do BigQuery VIEW: uma visualização do BigQuery MATERIALIZED VIEW: uma visualização materializada do BigQuery EXTERNAL: uma tabela que faz referência a uma fonte de dados externa
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.

Exemplos

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. Os metadados retornados correspondem a todas as tabelas em mydataset no projeto padrão (myproject).

mydataset contém as seguintes tabelas:

• mytable1: uma tabela padrão do BigQuery
• myview1: 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

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

   Acesse a página do BigQuery

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

   Observação: os nomes da visualização INFORMATION_SCHEMA diferenciam maiúsculas e minúsculas.

3. 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
   * 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    |
  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | 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 são para tabelas em mydataset em seu 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. Veja um exemplo: `myproject`.mydataset.INFORMATION_SCHEMA.TABLES.

Para executar a consulta, faça o seguinte:

Console

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

   Acesse a página do BigQuery

2. 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"
   

   Observação: os nomes da visualização INFORMATION_SCHEMA diferenciam maiúsculas e minúsculas.

3. 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
   * EXCEPT(is_typed)
 FROM
   mydataset.INFORMATION_SCHEMA.TABLES
 WHERE
   table_type="BASE TABLE"'

Os resultados terão o seguinte formato:

  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | 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

Os resultados das consultas na visualização INFORMATION_SCHEMA.TABLE_OPTIONS contêm uma linha para cada tabela ou visualização do conjunto de dados.

A visualização INFORMATION_SCHEMA.TABLE_OPTIONS tem o seguinte esquema:

Nome da coluna	Tipo de dados	Valor
TABLE_CATALOG	STRING	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 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 STRUCTs que representa as identificações na tabela
require_partition_filter	BOOL	Se as consultas na tabela exigem um filtro de partição

Exemplos

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

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

   Acesse a página do BigQuery

2. 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"
   

   Observação: os nomes da visualização INFORMATION_SCHEMA diferenciam maiúsculas e minúsculas.

3. 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
   *
 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" |
  +----------------+---------------+------------+----------------------+-------------+--------------------------------------+
  

Observação: tabelas sem prazo de validade são excluídas dos resultados da consulta.

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

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

   Acesse a página do BigQuery

2. 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%"
   

   Observação: os nomes da visualização INFORMATION_SCHEMA diferenciam maiúsculas e minúsculas.

3. 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
   *
 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	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 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

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

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

   Acesse a página do BigQuery

2. 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"
   

   Observação: os nomes da visualização INFORMATION_SCHEMA diferenciam maiúsculas e minúsculas.

3. 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
   * 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	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

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 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: coluna RECORD aninhada
• committer: coluna RECORD aninhada
• trailer: coluna RECORD aninhada e repetida
• difference: coluna RECORD aninhada e repetida

A consulta vai recuperar os metadados sobre as colunas author e difference.

Para executar a consulta, faça o seguinte:

Console

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

   Acesse a página do BigQuery

2. 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"
   

   Observação: os nomes da visualização INFORMATION_SCHEMA diferenciam maiúsculas e minúsculas.

3. 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
   *
 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 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 metadados sobre as tabelas em mydataset no seu projeto padrão — myproject. mydataset contém duas tabelas:

• mytable1: usa o mesmo esquema que a tabela commits no conjunto de dados público github_repos
• mytable2: usa o mesmo esquema que a tabela population_by_zip_2010 no conjunto de dados público census_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, é possível usar as instruções DDL nos resultados da consulta para recriar as tabelas no mydataset.

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

Para executar a consulta, faça o seguinte:

Console

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

   Acesse a página do BigQuery

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

   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
   

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:

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"

A saída será semelhante a esta:

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                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"                                                                                                                   |
| )                                                                                                                                                                             |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+