Obtén metadatos de tablas mediante INFORMATION_SCHEMA

INFORMATION_SCHEMA es una serie de vistas que proporcionan acceso a metadatos sobre conjuntos de datos, tablas y vistas.

Puedes consultar la vista INFORMATION_SCHEMA.TABLE_OPTIONS y la vista INFORMATION_SCHEMA.TABLES para recuperar metadatos sobre tablas y vistas en un proyecto. También puedes consultar las vistas INFORMATION_SCHEMA.COLUMNS y INFORMATION_SCHEMA.COLUMN_FIELD_PATHS para recuperar metadatos sobre las columnas (campos) de una tabla.

Las vistas TABLES y TABLE_OPTIONS también contienen información de alto nivel sobre las vistas. Para obtener información detallada, consulta la vista INFORMATION_SCHEMA.VIEWS en su lugar.

Permisos necesarios

Para obtener información sobre las tablas, debes tener asignada la función READER en el conjunto de datos o una función de IAM a nivel de proyecto que incluya permisos bigquery.tables.get. Si tienes permisos bigquery.tables.get a nivel de proyecto, puedes obtener información sobre todas las tablas del proyecto. Todas las funciones de IAM predefinidas a nivel de proyecto incluyen los permisos bigquery.tables.get, excepto bigquery.jobUser y bigquery.user.

Además, un usuario a quien se le asigna la función bigquery.user tiene permisos bigquery.datasets.create. Esto permite que un usuario con la función bigquery.user obtenga información sobre las tablas en cualquier conjunto de datos que el usuario cree. Cuando un usuario asignado a la función bigquery.user crea un conjunto de datos, se le otorga acceso de OWNER al conjunto de datos. El acceso de OWNER a un conjunto de datos otorga al usuario control total sobre este y todas las tablas que contiene.

Para obtener más información sobre las funciones de IAM y los permisos en BigQuery, consulta Control de acceso. A fin de obtener más información sobre las funciones a nivel de conjunto de datos, consulta Funciones básicas para conjuntos de datos.

Vista TABLES

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

Las consultas hechas a la vista INFORMATION_SCHEMA.TABLES deben tener un calificador de conjunto de datos. El usuario que envía la consulta debe tener acceso al conjunto de datos que contiene las tablas o las vistas.

La vista INFORMATION_SCHEMA.TABLES tiene el esquema siguiente:

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:
IS_INSERTABLE_INTO STRING YES o NO, lo cual 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

Para obtener más información sobre las propiedades de los conjuntos de datos, consulta la página del recurso del conjunto de datos en la documentación de la API de REST. Si deseas obtener más información sobre las propiedades de las tablas y las vistas, consulta la página del recurso de tabla en la documentación de la API de REST.

Ejemplos

Ejemplo 1:

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

mydataset contiene las propiedades siguientes:

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

Las consultas hechas a la vista INFORMATION_SCHEMA.TABLES deben tener un calificador de conjunto de datos. El usuario que envía la consulta debe tener acceso al conjunto de datos que contiene las tablas.

Para ejecutar la consulta en un proyecto que no sea el predeterminado, agrega el ID del proyecto al conjunto de datos en el siguiente formato: `[PROJECT_ID]`.[DATASET].INFORMATION_SCHEMA.[VIEW] (por ejemplo, `myproject`.mydataset.INFORMATION_SCHEMA.TABLES).

Para ejecutar la consulta, haz lo siguiente:

Console

  1. Abre la IU web de BigQuery en GCP Console.

    Ir a la IU web de BigQuery

  2. Ingresa la siguiente consulta de SQL estándar en la casilla Query editor (Editor de consulta). INFORMATION_SCHEMA requiere sintaxis de SQL estándar. SQL estándar es la sintaxis predeterminada en GCP Console.

    SELECT
     * EXCEPT(is_typed)
    FROM
     mydataset.INFORMATION_SCHEMA.TABLES
    
  3. Haz clic en Ejecutar.

Línea de comandos

Usa el comando query y especifica la sintaxis de SQL estándar con la marca --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'

Los resultados deberían verse de la siguiente manera:

  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | 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 en mydataset en tu proyecto predeterminado: myproject.

Las consultas hechas a la vista INFORMATION_SCHEMA.TABLES deben tener un calificador de conjunto de datos. El usuario que envía la consulta debe tener acceso al conjunto de datos que contiene las tablas.

Para ejecutar la consulta en un proyecto que no sea el predeterminado, agrega el ID del proyecto al conjunto de datos en el siguiente formato: `[PROJECT_ID]`.[DATASET].INFORMATION_SCHEMA.[VIEW] (por ejemplo, `myproject`.mydataset.INFORMATION_SCHEMA.TABLES).

Para ejecutar la consulta, haz lo siguiente:

Console

  1. Abre la IU web de BigQuery en GCP Console.

    Ir a la IU web de BigQuery

  2. Ingresa la siguiente consulta de SQL estándar en la casilla Query editor (Editor de consulta). INFORMATION_SCHEMA requiere sintaxis de SQL estándar. SQL estándar es la sintaxis predeterminada en GCP Console.

    SELECT
     * EXCEPT(is_typed)
    FROM
     mydataset.INFORMATION_SCHEMA.TABLES
    WHERE
     table_type="BASE TABLE"
    
  3. Haz clic en Ejecutar.

Línea de comandos

Usa el comando query y especifica la sintaxis de SQL estándar con la marca --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
WHERE table_type="BASE TABLE"'

Los resultados deberían verse de la siguiente manera:

  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | 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 de la consulta contienen una fila de cada tabla o vista del conjunto de datos.

Las consultas hechas a la vista INFORMATION_SCHEMA.TABLE_OPTIONS deben tener un calificador de conjunto de datos. El usuario que envía la consulta debe tener acceso al conjunto de datos que contiene las tablas o las vistas.

La vista INFORMATION_SCHEMA.TABLE_OPTIONS tiene el esquema siguiente:

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 en 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 La duración predeterminada, en días, de todos los segmentos de la tabla particionada
expiration_timestamp FLOAT64 La duración predeterminada, 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 en la tabla

Para obtener más información sobre las propiedades de los conjuntos de datos, consulta la página del recurso del conjunto de datos en la documentación de la API de REST. Si deseas obtener más información sobre las propiedades de las tablas y las vistas, consulta la página del recurso de tabla en la documentación de la API de REST.

Ejemplos

Ejemplo 1:

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

Las consultas hechas a la vista INFORMATION_SCHEMA.TABLE_OPTIONS deben tener un calificador de conjunto de datos. El usuario que envía la consulta debe tener acceso al conjunto de datos que contiene las tablas.

Para ejecutar la consulta en un proyecto que no sea el predeterminado, agrega el ID del proyecto al conjunto de datos en el siguiente formato: `[PROJECT_ID]`.[DATASET].INFORMATION_SCHEMA.[VIEW] (por ejemplo, `myproject`.mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS).

Para ejecutar la consulta, haz lo siguiente:

Console

  1. Abre la IU web de BigQuery en GCP Console.

    Ir a la IU web de BigQuery

  2. Ingresa la siguiente consulta de SQL estándar en la casilla Query editor (Editor de consulta). INFORMATION_SCHEMA requiere sintaxis de SQL estándar. SQL estándar es la sintaxis predeterminada en GCP Console.

    SELECT
     *
    FROM
     mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
    WHERE
     option_name="expiration_timestamp"
    
  3. Haz clic en Ejecutar.

Línea de comandos

Usa el comando query y especifica la sintaxis de SQL estándar con la marca --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 * FROM mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
WHERE option_name="expiration_timestamp"'

Los resultados deberían verse de la siguiente manera:

  +----------------+---------------+------------+----------------------+-------------+--------------------------------------+
  | 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 en la opción description para encontrar tablas que contengan “prueba” 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 siguiente formato: `[PROJECT_ID]`.[DATASET].INFORMATION_SCHEMA.[VIEW] (por ejemplo, `myproject`.mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS).

Para ejecutar la consulta, haz lo siguiente:

Console

  1. Abre la IU web de BigQuery en GCP Console.

    Ir a la IU web de BigQuery

  2. Ingresa la siguiente consulta de SQL estándar en la casilla Query editor (Editor de consulta). INFORMATION_SCHEMA requiere sintaxis de SQL estándar. SQL estándar es la sintaxis predeterminada en GCP Console.

    SELECT
     *
    FROM
     mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
    WHERE
     option_name="description" AND option_value LIKE "%test%"
    
  3. Haz clic en Ejecutar.

Línea de comandos

Usa el comando query y especifica la sintaxis de SQL estándar con la marca --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 * FROM mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
WHERE option_name="description" AND option_value LIKE "%test%"'

Los resultados deberían verse de la siguiente manera:

  +----------------+---------------+------------+-------------+-------------+--------------+
  | 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 de la consulta contienen una fila para cada columna (campo) en una tabla.

Las consultas hechas a la vista INFORMATION_SCHEMA.COLUMNS deben tener un calificador de conjunto de datos. El usuario que envía la consulta debe tener acceso al conjunto de datos que contiene las tablas.

La vista INFORMATION_SCHEMA.COLUMNS tiene el esquema siguiente:

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 1 indexado 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 la columna es una pseudocolumna, como _PARTITIONTIME o _PARTITIONDATE
IS_UPDATABLE STRING El valor es siempre NULL
IS_SYSTEM_DEFINED STRING YES o NO, lo cual depende de si la columna es una pseudocolumna, 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 STRING El desplazamiento 1 indexado 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

Para obtener más información sobre las propiedades de los conjuntos de datos, consulta la página del recurso del conjunto de datos en la documentación de la API de REST. Si deseas obtener más información sobre las propiedades de las tablas y las vistas, consulta la página del recurso de tabla en la documentación de la API de REST.

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 conjunto de datos públicos de BigQuery.

Dado que esta tabla pertenece al 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

Las consultas hechas a la vista INFORMATION_SCHEMA.COLUMNS deben tener un calificador de conjunto de datos. El usuario que envía la consulta debe tener acceso al conjunto de datos que contiene las tablas.

Para ejecutar la consulta, haz lo siguiente:

Console

  1. Abre la IU web de BigQuery en GCP Console.

    Ir a la IU web de BigQuery

  2. Ingresa la siguiente consulta de SQL estándar en la casilla Query editor (Editor de consulta). INFORMATION_SCHEMA requiere sintaxis de SQL estándar. SQL estándar es la sintaxis predeterminada en GCP 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"
    
  3. Haz clic en Ejecutar.

Línea de comandos

Usa el comando query y especifica la sintaxis de SQL estándar con la marca --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_generated, generation_expression, is_stored, is_updatable)
FROM `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.COLUMNS
WHERE table_name="population_by_zip_2010"'

Los resultados deberían verse de la siguiente manera. Para una lectura mejor, 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

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

Las consultas hechas a la vista INFORMATION_SCHEMA.COLUMN_FIELD_PATHS deben tener un calificador de conjunto de datos. El usuario que envía la consulta debe tener acceso al conjunto de datos que contiene las tablas.

La vista INFORMATION_SCHEMA.COLUMN_FIELD_PATHS tiene el esquema siguiente:

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

Para obtener más información sobre las propiedades de los conjuntos de datos, consulta la página del recurso del conjunto de datos en la documentación de la API de REST. Si deseas obtener más información sobre las propiedades de las tablas y las vistas, consulta la página del recurso de tabla en la documentación de la API de REST.

Ejemplos

En el siguiente ejemplo, se recuperan los metadatos de 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.

Dado que esta tabla pertenece al 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`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS).

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

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

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

Las consultas hechas a la vista INFORMATION_SCHEMA.COLUMN_FIELD_PATHS deben tener un calificador de conjunto de datos. El usuario que envía la consulta debe tener acceso al conjunto de datos que contiene las tablas.

Para ejecutar la consulta, haz lo siguiente:

Console

  1. Abre la IU web de BigQuery en GCP Console.

    Ir a la IU web de BigQuery

  2. Ingresa la siguiente consulta de SQL estándar en la casilla Query editor (Editor de consulta). INFORMATION_SCHEMA requiere sintaxis de SQL estándar. SQL estándar es la sintaxis predeterminada en GCP 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"
    
  3. Haz clic en Ejecutar.

Línea de comandos

Usa el comando query y especifica la sintaxis de SQL estándar con la marca --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 * FROM `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
WHERE table_name="commits" AND column_name="author" OR column_name="difference"'

Los resultados deberían verse de la siguiente manera. Para una lectura mejor, table_catalog y table_schema están excluidas 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

El siguiente ejemplo avanzado consulta las vistas INFORMATION_SCHEMA.TABLES, TABLE_OPTIONS y COLUMNS para recuperar metadatos sobre las tablas en mydataset en tu proyecto predeterminado: myproject. mydataset contiene 2 tablas:

  • mytable1: Usa el mismo esquema que la tabla commits del conjunto de datos públicos github_repos.
  • mytable2: Usa el mismo esquema que la tabla population_by_zip_2010 del conjunto de datos públicos census_bureau_usa.

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

Para ejecutar la consulta en un proyecto que no sea el predeterminado, agrega el ID del proyecto al conjunto de datos en el siguiente formato: `[PROJECT_ID]`.[DATASET].INFORMATION_SCHEMA.[VIEW] (por ejemplo, `myproject`.mydataset.INFORMATION_SCHEMA.TABLES).

Para ejecutar la consulta, haz lo siguiente:

Console

  1. Abre la IU web de BigQuery en GCP Console.

    Ir a la IU web de BigQuery

  2. Ingresa la siguiente consulta de SQL estándar en la casilla Query editor (Editor de consulta). INFORMATION_SCHEMA requiere sintaxis de SQL estándar. SQL estándar es la sintaxis predeterminada en GCP 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
    

Línea de comandos

Usa el comando query y especifica la sintaxis de SQL estándar con la marca --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:

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 debe verse de la siguiente manera:

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

Próximos pasos

¿Te ha resultado útil esta página? Enviar comentarios:

Enviar comentarios sobre...

Si necesitas ayuda, visita nuestra página de asistencia.