Como receber metadados de tabelas usando INFORMATION_SCHEMA

INFORMATION_SCHEMA contém estas visualizações para os metadados da 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 em relação a qualquer uma dessas visualizações precisam ter um qualificador de conjunto de dados.

-- Returns metadata for tables in a single dataset.
SELECT * FROM myDataset.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:
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 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 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 IU da Web do BigQuery no Console do Cloud.

    Acesse o Console do Cloud

  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

  3. Clique em Executar.

Linha de comando

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 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 IU da Web do BigQuery no Console do Cloud.

    Acesse o Console do Cloud

  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"

  3. Clique em Executar.

Linha de comando

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 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 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 IU da Web do BigQuery no Console do Cloud.

    Acesse o Console do Cloud

  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"

  3. Clique em Executar.

Linha de comando

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

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 IU da Web do BigQuery no Console do Cloud.

    Acesse o Console do Cloud

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

  3. Clique em Executar.

Linha de comando

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 STRING 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 IU da Web do BigQuery no Console do Cloud.

    Acesse o Console do Cloud

  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"

  3. Clique em Executar.

Linha de comando

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 o seguinte formato: 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 IU da Web do BigQuery no Console do Cloud.

    Acesse o Console do Cloud

  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"

  3. Clique em Executar.

Linha de comando

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 IU da Web do BigQuery no Console do Cloud.

    Acesse o Console do Cloud

  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

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:

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"                                                                                                                   |
| )                                                                                                                                                                             |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Esta página foi útil? Conte sua opinião sobre:

Enviar comentários sobre…

Esta página
Comentários sobre a documentação
BigQuery
Comentários sobre o produto
Precisa de ajuda? Acesse nossa página de suporte.