Como receber metadados de tabelas usando INFORMATION_SCHEMA

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.metadataViewer
  • bigquery.dataViewer
  • bigquery.dataOwner
  • bigquery.dataEditor
  • bigquery.admin

Além disso, quando um usuário com permissões bigquery.datasets.create cria um conjunto de dados, 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

  1. Abra a IU da Web do BigQuery no Console do GCP.

    Acessar o Console do GCP

  2. Insira a seguinte consulta SQL padrão na caixa Editor de consultas. INFORMATION_SCHEMA requer sintaxe SQL padrão. SQL padrão é a sintaxe padrão no Console do GCP.

    SELECT
     * EXCEPT(is_typed)
    FROM
     mydataset.INFORMATION_SCHEMA.TABLES
    
  3. 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

  1. Abra a IU da Web do BigQuery no Console do GCP.

    Acessar o Console do GCP

  2. Insira a seguinte consulta SQL padrão na caixa Editor de consultas. INFORMATION_SCHEMA requer sintaxe SQL padrão. SQL padrão é a sintaxe padrão no Console do GCP.

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

  1. Abra a IU da Web do BigQuery no Console do GCP.

    Acessar o Console do GCP

  2. Insira a seguinte consulta SQL padrão na caixa Editor de consultas. INFORMATION_SCHEMA requer sintaxe SQL padrão. SQL padrão é a sintaxe padrão no Console do GCP.

    SELECT
     *
    FROM
     mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
    WHERE
     option_name="expiration_timestamp"
    
  3. 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

  1. Abra a IU da Web do BigQuery no Console do GCP.

    Acessar o Console do GCP

  2. Insira a seguinte consulta SQL padrão na caixa Editor de consultas. INFORMATION_SCHEMA requer sintaxe SQL padrão. SQL padrão é a sintaxe padrão no Console do GCP.

    SELECT
     *
    FROM
     mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
    WHERE
     option_name="description" AND option_value LIKE "%test%"
    
  3. 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_GENERATED
  • GENERATION_EXPRESSION
  • IS_STORED
  • IS_UPDATABLE

As consultas na visualização INFORMATION_SCHEMA.COLUMNS precisam ter um qualificador de conjunto de dados. O usuário que envia a consulta precisa ter acesso ao conjunto de dados que contém as tabelas.

Para executar a consulta, faça o seguinte:

Console

  1. Abra a IU da Web do BigQuery no Console do GCP.

    Acessar o Console do GCP

  2. Insira a seguinte consulta SQL padrão na caixa Editor de consultas. INFORMATION_SCHEMA requer sintaxe SQL padrão. SQL padrão é a sintaxe padrão no Console do GCP.

    SELECT
     * EXCEPT(is_generated, generation_expression, is_stored, is_updatable)
    FROM
     `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.COLUMNS
    WHERE
     table_name="population_by_zip_2010"
    
  3. 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: coluna RECORD aninhada
  • committer: coluna RECORD aninhada
  • trailer: coluna RECORD aninhada e repetida
  • difference: coluna RECORD aninhada e repetida

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

As consultas na visualização INFORMATION_SCHEMA.COLUMN_FIELD_PATHS precisam ter um qualificador de conjunto de dados. O usuário que envia a consulta precisa ter acesso ao conjunto de dados que contém as tabelas.

Para executar a consulta, faça o seguinte:

Console

  1. Abra a IU da Web do BigQuery no Console do GCP.

    Acessar o Console do GCP

  2. Insira a seguinte consulta SQL padrão na caixa Editor de consultas. INFORMATION_SCHEMA requer sintaxe SQL padrão. SQL padrão é a sintaxe padrão no Console do GCP.

    SELECT
     *
    FROM
     `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
    WHERE
     table_name="commits"
     AND column_name="author"
     OR column_name="difference"
    
  3. 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 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, 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

  1. Abra a IU da Web do BigQuery no Console do GCP.

    Acessar o Console do GCP

  2. Insira a seguinte consulta SQL padrão na caixa Editor de consultas. INFORMATION_SCHEMA requer 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

Esta página foi útil? Conte sua opinião sobre:

Enviar comentários sobre…

Precisa de ajuda? Acesse nossa página de suporte.