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
  • PARTITIONS para los metadatos sobre las particiones de tabla (vista previa)

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

Antes de comenzar

Otorga funciones de Identity and Access Management (IAM) que les brindan a los usuarios los permisos necesarios para realizar cada tarea de este documento.

Permisos necesarios

Para recuperar los metadatos de la tabla con las tablas INFORMATION_SCHEMA, necesitas los siguientes permisos de IAM:

  • Para TABLES y TABLE_OPTIONS, necesitas los siguientes permisos a nivel del proyecto:

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

    Cada una de las siguientes funciones predefinidas de IAM incluye los permisos anteriores:

    • roles/bigquery.admin
    • roles/bigquery.dataViewer
    • roles/bigquery.metadataViewer
  • Para COLUMNS y COLUMN_FIELD_PATHS, necesitas los siguientes permisos a nivel del proyecto:

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

    Cada una de las siguientes funciones predefinidas de IAM incluye los permisos anteriores:

    • roles/bigquery.admin
    • roles/bigquery.dataEditor
    • roles/bigquery.dataViewer
    • roles/bigquery.metadataViewer
  • Para PARTITIONS, necesitas lo siguiente:

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

    Cada una de las siguientes funciones predefinidas de IAM incluye los permisos anteriores:

    • roles/bigquery.admin
    • roles/bigquery.dataEditor
    • roles/bigquery.dataViewer

Si deseas obtener más información acerca de los permisos de BigQuery en detalle, consulta las funciones y permisos.

Sintaxis

Las consultas de cualquiera de estas vistas deben tener un calificador de región o conjunto de datos.

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

-- Returns metadata for tables in a region.
SELECT * FROM region-us.INFORMATION_SCHEMA.TABLES;

Para consultas con un calificador de conjunto de datos, debes tener permisos para el conjunto de datos. Para consultas con un calificador de región, debes tener permisos para el proyecto.

Vista TABLES

Cuando consultas la vista INFORMATION_SCHEMA.TABLES, los resultados contienen una fila por cada tabla o vista de un conjunto de datos. Para obtener información detallada sobre las vistas, consulta la vista INFORMATION_SCHEMA.VIEWS en su lugar.

La vista INFORMATION_SCHEMA.TABLES tiene el siguiente esquema:

Nombre de la columna Tipo de datos Valor
TABLE_CATALOG STRING El ID 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 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
DDL STRING La declaración DDL que se puede usar para volver a crear la tabla, como CREATE TABLE o CREATE VIEW.

Ejemplos

Ejemplo 1:

En el ejemplo siguiente, se recuperan los metadatos de todas las tablas en el conjunto de datos llamado mydataset. La consulta selecciona todas las columnas de la vista INFORMATION_SCHEMA.TABLES, excepto is_typed, que se reserva para uso futuro, y ddl, que se oculta en las consultas SELECT *. Los metadatos que se muestran corresponden a todas las tablas en mydataset en tu proyecto predeterminado.

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

  1. Abre la página de BigQuery en Cloud Console.

    Ir a la página de BigQuery

  2. 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
    
  3. Haz clic en Ejecutar.

bq

Usa el comando bq 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    |                     ddl                     |
+----------------+---------------+----------------+------------+--------------------+---------------------+---------------------------------------------+
| myproject      | mydataset     | mytable1       | BASE TABLE | YES                | 2018-10-29 20:34:44 | CREATE TABLE `myproject.mydataset.mytable1` |
|                |               |                |            |                    |                     | (                                           |
|                |               |                |            |                    |                     |   id INT64                                  |
|                |               |                |            |                    |                     | );                                          |
| myproject      | mydataset     | myview1        | VIEW       | NO                 | 2018-12-29 00:19:20 | CREATE VIEW `myproject.mydataset.myview1`   |
|                |               |                |            |                    |                     | AS SELECT 100 as id;                        |
+----------------+---------------+----------------+------------+--------------------+---------------------+---------------------------------------------+
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 y la columna ddl está oculta. Los metadatos que se muestran corresponden a las tablas de mydataset en tu proyecto predeterminado.

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

  1. Abre la página de BigQuery en Cloud Console.

    Ir a la página de BigQuery

  2. 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"
    
  3. Haz clic en Ejecutar.

bq

Usa el comando bq 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
 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    |                     ddl                     |
  +----------------+---------------+----------------+------------+--------------------+---------------------+---------------------------------------------+
  | myproject      | mydataset     | mytable1       | BASE TABLE | YES                | 2018-10-31 22:40:05 | CREATE TABLE myproject.mydataset.mytable1 |
  |                |               |                |            |                    |                     | (                                           |
  |                |               |                |            |                    |                     |   id INT64                                  |
  |                |               |                |            |                    |                     | );                                          |
  +----------------+---------------+----------------+------------+--------------------+---------------------+---------------------------------------------+
  

Ejemplo 3:

En el siguiente ejemplo, se recuperan las columnas table_name y ddl de la vista INFORMATION_SCHEMA.TABLES 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, debes agregar el ID del proyecto al conjunto de datos en el siguiente formato: `project_id`.dataset.INFORMATION_SCHEMA.view. En este ejemplo, el valor es `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES.

Para ejecutar la consulta, haz lo siguiente:

Console

  1. Abre la página de BigQuery en Cloud Console.

    Ir a la página de BigQuery

  2. 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
     table_name, ddl
    FROM
     `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES
    WHERE
     table_name="population_by_zip_2010"
    
  3. Haz clic en Ejecutar.

bq

Usa el comando bq 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
   table_name, ddl
 FROM
   `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES
 WHERE
   table_name="population_by_zip_2010"'

El resultado debería ser similar a lo siguiente:

+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|       table_name       |                                                                                                            ddl                                                                                                             |
+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| population_by_zip_2010 | CREATE TABLE `bigquery-public-data.census_bureau_usa.population_by_zip_2010`                                                                                                                                               |
|                        | (                                                                                                                                                                                                                          |
|                        |   geo_id STRING OPTIONS(description="Geo code"),                                                                                                                                                                           |
|                        |   zipcode STRING NOT NULL OPTIONS(description="Five digit ZIP Code Tabulation Area Census Code"),                                                                                                                          |
|                        |   population INT64 OPTIONS(description="The total count of the population for this segment."),                                                                                                                             |
|                        |   minimum_age INT64 OPTIONS(description="The minimum age in the age range. If null, this indicates the row as a total for male, female, or overall population."),                                                          |
|                        |   maximum_age INT64 OPTIONS(description="The maximum age in the age range. If null, this indicates the row as having no maximum (such as 85 and over) or the row is a total of the male, female, or overall population."), |
|                        |   gender STRING OPTIONS(description="male or female. If empty, the row is a total population summary.")                                                                                                                    |
|                        | )                                                                                                                                                                                                                          |
|                        | OPTIONS(                                                                                                                                                                                                                   |
|                        |   labels=[("freebqcovid", "")]                                                                                                                                                                                             |
|                        | );                                                                                                                                                                                                                         |
+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  

Vista TABLE_OPTIONS

Cuando consultas la vista INFORMATION_SCHEMA.TABLE_OPTIONS, los resultados contienen una fila por cada opción, por cada tabla o vista de un conjunto de datos. Para obtener información detallada sobre las vistas, consulta la vista INFORMATION_SCHEMA.VIEWS en su lugar.

La vista INFORMATION_SCHEMA.TABLE_OPTIONS tiene el siguiente esquema:

Nombre de la columna Tipo de datos Valor
TABLE_CATALOG STRING El ID 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 de todas las particiones de una tabla particionada, expresado en días
expiration_timestamp FLOAT64 La hora a la que vence esta tabla
kms_key_name STRING El nombre de la clave de Cloud KMS que se usa 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
enable_refresh BOOL Indica si la actualización automática está habilitada para una vista materializada
refresh_interval_minutes FLOAT64 Frecuencia con la que se actualiza una vista materializada

En el caso de las tablas externas, también son posibles las siguientes opciones:

Opciones
allow_jagged_rows

BOOL

Si es true, habilita las filas a las que les faltan columnas opcionales finales.

Se aplica a los datos CSV.

allow_quoted_newlines

BOOL

Si es true, habilita las secciones de datos entrecomillados que contienen caracteres de salto de línea en el archivo.

Se aplica a los datos CSV.

compression

STRING

El tipo de compresión de la fuente de datos. Entre los valores admitidos, se incluyen los siguientes: GZIP. Si no se especifica, la fuente de datos no está comprimida.

Se aplica a los datos CSV y JSON.

enable_logical_types

BOOL

Si es true, convierte los tipos lógicos de Avro en sus tipos de SQL correspondientes. Para obtener más información, consulta Tipos lógicos.

Se aplica a los datos de Avro.

encoding

STRING

La codificación de caracteres de los datos. Los valores admitidos son los siguientes: UTF8 (o UTF-8), ISO_8859_1 (o ISO-8859-1).

Se aplica a los datos CSV.

field_delimiter

STRING

El separador de campos de un archivo CSV (opcional).

Se aplica a los datos CSV.

format

STRING

El formato de los datos externos. Los valores admitidos son los siguientes: AVRO, CSV, DATASTORE_BACKUP, GOOGLE_SHEETS, NEWLINE_DELIMITED_JSON (o JSON), ORC y PARQUET.

El valor JSON es equivalente a NEWLINE_DELIMITED_JSON.

decimal_target_types

ARRAY<STRING>

Determina cómo convertir un tipo Decimal. Equivale a ExternalDataConfiguration.decimal_target_types

Ejemplo: ["NUMERIC", "BIGNUMERIC"].

json_extension

STRING

Para los datos JSON, indica un formato de intercambio JSON en particular. Si no se especifica, BigQuery lee los datos como registros JSON genéricos.

Los valores admitidos son los siguientes:
GEOJSON (Vista previa). Datos GeoJSON. Para obtener más información, consulta Carga datos GeoJSON.

hive_partition_uri_prefix

STRING

Prefijo común para todos los URI de origen antes de que comience la codificación de la clave de partición. Se aplica solo a las tablas externas particionadas de subárbol.

Se aplica a los datos de Avro, CSV, JSON, Parquet y ORC.

Ejemplo: "gs://bucket/path".

ignore_unknown_values

BOOL

Si es true, ignora los valores adicionales que no están representados en el esquema de la tabla, sin mostrar un error.

Se aplica a los datos CSV y JSON.

max_bad_records

INT64

La cantidad máxima de registros erróneos que se deben ignorar cuando se leen los datos.

Se aplica a los datos CSV, JSON y de Hojas de cálculo.

null_marker

STRING

La string que representa los valores NULL en un archivo CSV.

Se aplica a los datos CSV.

projection_fields

STRING

Una lista de propiedades de entidad para cargar.

Se aplica a los datos de Datastore.

quote

STRING

La string que se usa para entrecomillar secciones de datos en un archivo de CSV. Si tus datos contienen caracteres de salto de línea entrecomillados, también establece la propiedad allow_quoted_newlines en true.

Se aplica a los datos CSV.

require_hive_partition_filter

BOOL

Si es true, todas las búsquedas en esta tabla requieren un filtro de partición que se pueda usar para eliminar particiones cuando se leen datos. Se aplica solo a las tablas externas particionadas de subárbol.

Se aplica a los datos de Avro, CSV, JSON, Parquet y ORC.

sheet_range

STRING

Rango de Hojas de cálculo desde el que se realiza la búsqueda.

Se aplica a los datos de Hojas de cálculo.

Ejemplo: “sheet1!A1:B20”.

skip_leading_rows

INT64

La cantidad de filas en la parte superior de un archivo que se deben omitir cuando se leen los datos.

Se aplica a los datos CSV y Hojas de cálculo.

uris

ARRAY<STRING>

Un arreglo de URI completamente calificados para las ubicaciones de datos externas.

Ejemplo: ["gs://bucket/path/*"].

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 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.TABLE_OPTIONS).

Para ejecutar la consulta, haz lo siguiente:

Console

  1. Abre la página de BigQuery en Cloud Console.

    Ir a la página de BigQuery

  2. 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"
    
  3. Haz clic en Ejecutar.

bq

Usa el comando bq 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
   *
 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

  1. Abre la página de BigQuery en Cloud Console.

    Ir a la página de BigQuery

  2. 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%"
    
  3. Haz clic en Ejecutar.

bq

Usa el comando bq 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
   *
 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 ID 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

  1. Abre la página de BigQuery en Cloud Console.

    Ir a la página de BigQuery

  2. 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"
    
  3. Haz clic en Ejecutar.

bq

Usa el comando bq 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_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 ID 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 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.

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`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS.

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

  1. Abre la página de BigQuery en Cloud Console.

    Ir a la página de BigQuery

  2. 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"
    
  3. Haz clic en Ejecutar.

bq

Usa el comando bq 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
   *
 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        |
  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
  

Vista PARTITIONS

Cuando consultas la vista INFORMATION_SCHEMA.PARTITIONS, los resultados de la consulta contienen una fila por cada partición.

La vista INFORMATION_SCHEMA.PARTITIONS tiene el siguiente esquema:

Nombre de la columna Tipo de datos Valor
TABLE_CATALOG STRING El ID del proyecto que contiene el conjunto la tabla
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, también denominado tableId
PARTITION_ID STRING El ID de una partición única. Para las tablas no particionadas, el valor es NULL. Para las tablas particionadas que contienen filas con valores NULL en la columna de partición, el valor es __NULL__.
TOTAL_ROWS INTEGER La cantidad total de filas en la partición
TOTAL_LOGICAL_BYTES INTEGER La cantidad total de bytes lógicos en la partición
TOTAL_BILLABLE_BYTES INTEGER La cantidad total de bytes facturables en la partición
LAST_MODIFIED_TIME TIMESTAMP El momento en que se escribieron los datos más recientes en la partición
STORAGE_TIER STRING El nivel de almacenamiento de la partición:

Ejemplos

En el siguiente ejemplo, se calcula la cantidad de bytes que usa cada nivel de almacenamiento en todas las tablas en el conjunto de datos bigquery-public-data.crypto_bitcoin.

Para ejecutar la consulta, haz lo siguiente:

Console

  1. Abre la página de BigQuery en Cloud Console.

    Ir a la página de BigQuery

  2. 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 storage_tier, SUM(total_billable_bytes) billable_bytes
    FROM `bigquery-public-data.crypto_bitcoin.INFORMATION_SCHEMA.PARTITIONS`
    GROUP BY storage_tier
    
  3. Haz clic en Ejecutar.

bq

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 storage_tier, SUM(total_billable_bytes) billable_bytes
 FROM `bigquery-public-data.crypto_bitcoin.INFORMATION_SCHEMA.PARTITIONS`
 GROUP BY storage_tier'

Los resultados deberían ser similares a los de la siguiente tabla: Los recuentos de bytes exactos pueden cambiar con el tiempo a medida que el conjunto de datos se actualiza.

  +--------------+----------------+
  | storage_tier | billable_bytes |
  +--------------+----------------+
  | LONG_TERM    |  1311495144879 |
  | ACTIVE       |    66757629240 |
  +--------------+----------------+