Obtén metadatos de tablas mediante INFORMATION_SCHEMA

INFORMATION_SCHEMA contiene estas vistas para los metadatos de tablas:

TABLES y TABLE_OPTIONS para los metadatos sobre las tablas
COLUMNS y COLUMN_FIELD_PATHS para los metadatos sobre las columnas y los campos

TABLES y TABLE_OPTIONS también contienen información de alto nivel sobre las vistas. Para obtener información detallada, consulta VIEWS.

Permisos necesarios

Para TABLES y TABLE_OPTIONS, debes tener estos permisos:

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

Para COLUMNS y COLUMN_FIELD_PATHS, debes tener estos permisos:

bigquery.tables.get
bigquery.tables.list

Sintaxis

Las consultas de cualquiera de estas vistas deben tener un calificador de conjunto de datos.

-- Returns metadata for tables in a single dataset.
SELECT * FROM myDataset.INFORMATION_SCHEMA.TABLES;

Vista `TABLES`

Cuando consultas la vista INFORMATION_SCHEMA.TABLES, los resultados contienen una fila por cada tabla o vista de un conjunto de datos.

La vista INFORMATION_SCHEMA.TABLES tiene el siguiente esquema:

Nombre de la columna	Tipo de datos	Valor
`TABLE_CATALOG`	`STRING`	El nombre del proyecto que contiene el conjunto de datos
`TABLE_SCHEMA`	`STRING`	El nombre del conjunto de datos que contiene la tabla o la vista (también denominado `datasetId`)
`TABLE_NAME`	`STRING`	El nombre de la tabla o la vista (también denominado `tableId`)
`TABLE_TYPE`	`STRING`	El tipo de tabla: `BASE TABLE`: Una tabla de BigQuery. `VIEW`: Una vista de BigQuery `EXTERNAL`: Una tabla que hace referencia a una fuente de datos externa
`IS_INSERTABLE_INTO`	`STRING`	`YES` o `NO`, lo que depende de si la tabla admite declaraciones DML INSERT
`IS_TYPED`	`STRING`	El valor es siempre `NO`
`CREATION_TIME`	`TIMESTAMP`	La fecha y hora de creación de la tabla

Ejemplos

Ejemplo 1:

En el ejemplo siguiente, se recuperan todas las columnas de la vista INFORMATION_SCHEMA.TABLES, excepto por is_typed, que se reserva para uso futuro. Los metadatos que se muestran corresponden a todas las tablas en mydataset de tu proyecto predeterminado: myproject.

mydataset contiene las tablas siguientes:

mytable1: Una tabla de BigQuery estándar
myview1: una vista de BigQuery

Para ejecutar la consulta en un proyecto que no sea el predeterminado, agrega el ID del proyecto al conjunto de datos en este formato: `project_id`.dataset.INFORMATION_SCHEMA.view (por ejemplo, `myproject`.mydataset.INFORMATION_SCHEMA.TABLES).

Para ejecutar la consulta, haz lo siguiente:

Console

Abre la IU web de BigQuery en Cloud Console.

Ir a Cloud Console
Ingresa la siguiente consulta de SQL estándar en la casilla Editor de consulta. INFORMATION_SCHEMA requiere sintaxis de SQL estándar. SQL estándar es la sintaxis predeterminada en Cloud Console.
```
SELECT
 * EXCEPT(is_typed)
FROM
 mydataset.INFORMATION_SCHEMA.TABLES
```
Nota: En los nombres de las vistas INFORMATION_SCHEMA, se distingue entre mayúsculas y minúsculas.
Haz clic en Ejecutar.

Línea de comandos

Usa el comando query y especifica la sintaxis de SQL estándar con las marcas --nouse_legacy_sql o --use_legacy_sql=false. Se requiere la sintaxis de SQL estándar para las consultas INFORMATION_SCHEMA.

Para ejecutar la consulta, ingresa lo siguiente:

bq query --nouse_legacy_sql \
'SELECT
   * EXCEPT(is_typed)
 FROM
   mydataset.INFORMATION_SCHEMA.TABLES'

El resultado debería ser similar a lo siguiente:

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

Ejemplo 2:

En el ejemplo siguiente, se recuperan todas las tablas de tipo BASE TABLE de la vista INFORMATION_SCHEMA.TABLES. La columna is_typed queda excluida. Los metadatos que se muestran corresponden a las tablas de mydataset en tu proyecto predeterminado: myproject.

Para ejecutar la consulta, haz lo siguiente:

Console

Abre la IU web de BigQuery en Cloud Console.

Ir a Cloud Console
Ingresa la siguiente consulta de SQL estándar en la casilla Editor de consulta. INFORMATION_SCHEMA requiere sintaxis de SQL estándar. SQL estándar es la sintaxis predeterminada en Cloud Console.
```
SELECT
 * EXCEPT(is_typed)
FROM
 mydataset.INFORMATION_SCHEMA.TABLES
WHERE
 table_type="BASE TABLE"
```
Nota: En los nombres de las vistas INFORMATION_SCHEMA, se distingue entre mayúsculas y minúsculas.
Haz clic en Ejecutar.

Línea de comandos

Para ejecutar la consulta, ingresa lo siguiente:

bq query --nouse_legacy_sql \
'SELECT
   * EXCEPT(is_typed)
 FROM
   mydataset.INFORMATION_SCHEMA.TABLES
 WHERE
   table_type="BASE TABLE"'

El resultado debería ser similar a lo siguiente:

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

Vista `TABLE_OPTIONS`

Cuando consultas la vista INFORMATION_SCHEMA.TABLE_OPTIONS, los resultados contienen una fila por cada tabla o vista de un conjunto de datos.

La vista INFORMATION_SCHEMA.TABLE_OPTIONS tiene el siguiente esquema:

Nombre de la columna	Tipo de datos	Valor
`TABLE_CATALOG`	`STRING`	El nombre del proyecto que contiene el conjunto de datos
`TABLE_SCHEMA`	`STRING`	El nombre del conjunto de datos que contiene la tabla o la vista (también denominado `datasetId`)
`TABLE_NAME`	`STRING`	El nombre de la tabla o la vista (también denominado `tableId`)
`OPTION_NAME`	`STRING`	Uno de los valores de nombre de la tabla de opciones
`OPTION_TYPE`	`STRING`	Uno de los valores de tipo de datos en la tabla de opciones
`OPTION_VALUE`	`STRING`	Una de las opciones de valor en la tabla de opciones

Tabla de opciones

`OPTION_NAME`	`OPTION_TYPE`	`OPTION_VALUE`
`partition_expiration_days`	`FLOAT64`	El ciclo de vida predeterminado, en días, de todos los segmentos de la tabla particionada
`expiration_timestamp`	`FLOAT64`	El ciclo de vida predeterminado, en días, de la tabla
`kms_key_name`	`STRING`	El nombre de la clave de Cloud KMS usada para encriptar la tabla
`friendly_name`	`STRING`	El nombre descriptivo de la tabla
`description`	`STRING`	Una descripción de la tabla
`labels`	`ARRAY<STRUCT<STRING, STRING>>`	Un arreglo de `STRUCT` que representa las etiquetas de la tabla
`require_partition_filter`	`BOOL`	Si las consultas sobre la tabla requieren un filtro de partición

Ejemplos

Ejemplo 1:

En el ejemplo siguiente, se recuperan las horas de vencimiento de la tabla predeterminada para todas las tablas de mydataset en tu proyecto predeterminado (myproject) mediante una consulta a la vista INFORMATION_SCHEMA.TABLE_OPTIONS.

Para ejecutar la consulta, haz lo siguiente:

Console

Abre la IU web de BigQuery en Cloud Console.

Ir a Cloud Console
Ingresa la siguiente consulta de SQL estándar en la casilla Editor de consulta. INFORMATION_SCHEMA requiere sintaxis de SQL estándar. SQL estándar es la sintaxis predeterminada en Cloud Console.
```
SELECT
 *
FROM
 mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
WHERE
 option_name="expiration_timestamp"
```
Nota: En los nombres de las vistas INFORMATION_SCHEMA, se distingue entre mayúsculas y minúsculas.
Haz clic en Ejecutar.

Línea de comandos

Para ejecutar la consulta, ingresa lo siguiente:

bq query --nouse_legacy_sql \
'SELECT
   *
 FROM
   mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
 WHERE
   option_name="expiration_timestamp"'

El resultado debería ser similar a lo siguiente:

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

Ejemplo 2:

En el ejemplo siguiente, se recuperan los metadatos sobre todas las tablas en mydataset que contienen datos de prueba. La consulta usa los valores de la opción description para encontrar tablas que contengan “test” en algún lugar de la descripción. mydataset está en tu proyecto predeterminado: myproject.

Para ejecutar la consulta en un proyecto que no sea el predeterminado, agrega el ID del proyecto al conjunto de datos en el formato siguiente: `project_id`.dataset.INFORMATION_SCHEMA.view; por ejemplo, `myproject`.mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS.

Para ejecutar la consulta, haz lo siguiente:

Console

Abre la IU web de BigQuery en Cloud Console.

Ir a Cloud Console
Ingresa la siguiente consulta de SQL estándar en la casilla Editor de consulta. INFORMATION_SCHEMA requiere sintaxis de SQL estándar. SQL estándar es la sintaxis predeterminada en Cloud Console.
```
SELECT
 *
FROM
 mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
WHERE
 option_name="description" AND option_value LIKE "%test%"
```
Nota: En los nombres de las vistas INFORMATION_SCHEMA, se distingue entre mayúsculas y minúsculas.
Haz clic en Ejecutar.

Línea de comandos

Para ejecutar la consulta, ingresa lo siguiente:

bq query --nouse_legacy_sql \
'SELECT
   *
 FROM
   mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
 WHERE
   option_name="description" AND option_value LIKE "%test%"'

El resultado debería ser similar a lo siguiente:

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

Vista `COLUMNS`

Cuando consultas la vista INFORMATION_SCHEMA.COLUMNS, los resultados contienen una fila por cada columna (campo) de una tabla.

La vista INFORMATION_SCHEMA.COLUMNS tiene el siguiente esquema:

Nombre de la columna	Tipo de datos	Valor
`TABLE_CATALOG`	`STRING`	El nombre del proyecto que contiene el conjunto de datos
`TABLE_SCHEMA`	`STRING`	El nombre del conjunto de datos que contiene la tabla (también denominado `datasetId`)
`TABLE_NAME`	`STRING`	El nombre de la tabla o la vista (también denominado `tableId`)
`COLUMN_NAME`	`STRING`	El nombre de la columna
`ORDINAL_POSITION`	`INT64`	El desplazamiento (con indexación de base 1) de la columna dentro de la tabla; si es una seudocolumna, como _PARTITIONTIME o _PARTITIONDATE, el valor es `NULL`
`IS_NULLABLE`	`STRING`	`YES` o `NO`, lo cual depende de si el modo de la columna permite valores `NULL`
`DATA_TYPE`	`STRING`	El tipo de datos de SQL estándar de la columna
`IS_GENERATED`	`STRING`	El valor es siempre `NEVER`
`GENERATION_EXPRESSION`	`STRING`	El valor es siempre `NULL`
`IS_STORED`	`STRING`	El valor es siempre `NULL`
`IS_HIDDEN`	`STRING`	`YES` o `NO`, lo cual depende de si se trata de una seudocolumna, como _PARTITIONTIME o _PARTITIONDATE
`IS_UPDATABLE`	`STRING`	El valor es siempre `NULL`
`IS_SYSTEM_DEFINED`	`STRING`	`YES` o `NO`, lo cual depende de si se trata de una seudocolumna, como _PARTITIONTIME o _PARTITIONDATE
`IS_PARTITIONING_COLUMN`	`STRING`	`YES` o `NO` lo cual depende de si la columna es una columna de partición
`CLUSTERING_ORDINAL_POSITION`	`INT64`	El desplazamiento (con indexación de base 1) de la columna dentro de las columnas de agrupamiento en clústeres de la tabla; el valor es `NULL` si la tabla no está agrupada

Ejemplos

En el siguiente ejemplo, se recuperan los metadatos desde la vista INFORMATION_SCHEMA.COLUMNS para la tabla population_by_zip_2010 en el conjunto de datos census_bureau_usa. Este conjunto de datos es parte del programa de conjuntos de datos públicos de BigQuery.

Debido a que la tabla que consultas está en otro proyecto, bigquery-public-data, debes agregar el ID del proyecto al conjunto de datos en el siguiente formato: `project_id`.dataset.INFORMATION_SCHEMA.view; por ejemplo, `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES.

Las columnas siguientes se excluyen de los resultados de la consulta debido a que están reservadas para uso futuro:

IS_GENERATED
GENERATION_EXPRESSION
IS_STORED
IS_UPDATABLE

Para ejecutar la consulta, haz lo siguiente:

Console

Abre la IU web de BigQuery en Cloud Console.

Ir a Cloud Console
Ingresa la siguiente consulta de SQL estándar en la casilla Editor de consulta. INFORMATION_SCHEMA requiere sintaxis de SQL estándar. SQL estándar es la sintaxis predeterminada en Cloud Console.
```
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"
```
Nota: En los nombres de las vistas INFORMATION_SCHEMA, se distingue entre mayúsculas y minúsculas.
Haz clic en Ejecutar.

Línea de comandos

Para ejecutar la consulta, ingresa lo siguiente:

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

El resultado debería ser similar a lo siguiente: Para facilitar la lectura, table_catalog y table_schema se excluyen de los 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 |
+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+

Vista `COLUMN_FIELD_PATHS`

Los resultados de la consulta contienen una fila por cada columna anidada dentro de una columna RECORD (o STRUCT).

Cuando consultas la vista INFORMATION_SCHEMA.COLUMN_FIELD_PATHS, los resultados contienen una fila por cada columna anidada dentro de una columna RECORD (o STRUCT).

La vista INFORMATION_SCHEMA.COLUMN_FIELD_PATHS tiene el siguiente esquema:

Nombre de la columna	Tipo de datos	Valor
`TABLE_CATALOG`	>`STRING`	El nombre del proyecto que contiene el conjunto de datos
`TABLE_SCHEMA`	`STRING`	El nombre del conjunto de datos que contiene la tabla (también denominado `datasetId`)
`TABLE_NAME`	`STRING`	El nombre de la tabla o la vista (también denominado `tableId`)
`COLUMN_NAME`	`STRING`	El nombre de la columna
`FIELD_PATH`	`STRING`	La ruta de acceso a una columna anidada dentro de una columna “RECORD” o “STRUCT”
`DATA_TYPE`	`STRING`	El tipo de datos de SQL estándar de la columna
`DESCRIPTION`	`STRING`	La descripción de la columna

Ejemplos

En el siguiente ejemplo, se recuperan los metadatos desde la vista INFORMATION_SCHEMA.COLUMN_FIELD_PATHS para la tabla commits en el conjunto de datos github_repos. Este conjunto de datos es parte del programa de conjunto de datos públicos de BigQuery.

La tabla commits contiene las siguientes columnas anidadas, y anidadas y repetidas:

author: columna anidada RECORD
committer: columna RECORD anidada
trailer: columna RECORD anidada y repetida
difference: columna RECORD anidada y repetida

Tu consulta recuperará los metadatos de las columnas author y difference.

Para ejecutar la consulta, haz lo siguiente:

Console

Abre la IU web de BigQuery en Cloud Console.

Ir a Cloud Console
Ingresa la siguiente consulta de SQL estándar en la casilla Editor de consulta. INFORMATION_SCHEMA requiere sintaxis de SQL estándar. SQL estándar es la sintaxis predeterminada en Cloud Console.
```
SELECT
 *
FROM
 `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
WHERE
 table_name="commits"
 AND column_name="author"
 OR column_name="difference"
```
Nota: En los nombres de las vistas INFORMATION_SCHEMA, se distingue entre mayúsculas y minúsculas.
Haz clic en Ejecutar.

Línea de comandos

Para ejecutar la consulta, ingresa lo siguiente:

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

El resultado debería ser similar a lo siguiente: Para facilitar la lectura, table_catalog y table_schema se excluyen de los 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        |
  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+

Ejemplo avanzado

En el siguiente ejemplo avanzado, se realizan consultas en las vistas INFORMATION_SCHEMA.TABLES, TABLE_OPTIONS y COLUMNS para recuperar metadatos sobre las tablas de mydataset en tu proyecto predeterminado, myproject. mydataset contiene 2 tablas:

mytable1: usa el mismo esquema que la tabla commits en el conjunto de datos públicos github_repos
mytable2: usa el mismo esquema que la tabla population_by_zip_2010 en el conjunto de datos públicos census_bureau_usa

Las funciones definidas por el usuario usan los resultados a fin de ensamblar las instrucciones DDL necesarias para volver a crear las tablas. Luego puedes usar las declaraciones DDL en los resultados de la consulta para recrear las tablas en mydataset.

Para ejecutar la consulta, haz lo siguiente:

Console

Abre la IU web de BigQuery en Cloud Console.

Ir a Cloud Console

Ingresa la siguiente consulta de SQL estándar en la casilla Editor de consulta. INFORMATION_SCHEMA requiere sintaxis de SQL estándar. SQL estándar es la sintaxis predeterminada en Cloud Console.

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

Para ejecutar la consulta, ingresa lo siguiente:

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"

El resultado debería ser similar a lo siguiente:

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

Obtén metadatos de tablas mediante INFORMATION_SCHEMA

Permisos necesarios

Sintaxis

Vista TABLES

Ejemplos

Ejemplo 1:

Console

Línea de comandos

Ejemplo 2:

Console

Línea de comandos

Vista TABLE_OPTIONS

Tabla de opciones

Ejemplos

Ejemplo 1:

Console

Línea de comandos

Ejemplo 2:

Console

Línea de comandos

Vista COLUMNS

Ejemplos

Console

Línea de comandos

Vista COLUMN_FIELD_PATHS

Ejemplos

Console

Línea de comandos

Ejemplo avanzado

Console

CLI

Vista `TABLES`

Vista `TABLE_OPTIONS`

Vista `COLUMNS`

Vista `COLUMN_FIELD_PATHS`