Crea y usa tablas agrupadas

En este documento, se describe el proceso para crear y usar tablas agrupadas en BigQuery.

Limitaciones

Las tablas agrupadas en BigQuery están sujetas a las limitaciones siguientes:

  • Por ahora, el agrupamiento en clústeres es compatible solo con tablas particionadas.
  • Solo SQL estándar es compatible para consultar tablas agrupadas y escribir resultados de consultas en tablas agrupadas.
  • Solo puedes especificar columnas de agrupamiento en clústeres cuando se crea una tabla.
  • Después de crear una tabla agrupada, las columnas de agrupamiento en clústeres no se pueden modificar.
  • Las columnas de agrupamiento en clústeres deben ser columnas de nivel superior, no deben estar repetidas y deben pertenecer a uno de los tipos siguientes: INT64, STRING, DATE, TIMESTAMP, BOOL o NUMERIC. Para obtener más información sobre los tipos de datos, consulta Tipos de datos de SQL estándar.
  • Puedes especificar hasta cuatro columnas de agrupamiento en clústeres.

Crea tablas agrupadas

Por ahora, solo puedes agrupar una tabla particionada. Esto incluye tablas particionadas en tiempo de transferencia y tablas particionadas (tablas particionadas por una columna TIMESTAMP o DATE).

Puedes crear una tabla particionada en BigQuery de las siguientes maneras:

Cuando creas una tabla particionada en BigQuery, el nombre de la tabla debe ser único por cada conjunto de datos. El nombre de la tabla puede tener las siguientes características:

  • Contener hasta 1,024 caracteres
  • Contener letras (mayúsculas o minúsculas), números y guiones bajos

Permisos necesarios

Para crear una tabla agrupada, debes tener acceso de WRITER a nivel de conjunto de datos o tener asignada una función de IAM a nivel de proyecto que incluya permisos de bigquery.tables.create. Las siguientes funciones de IAM predefinidas a nivel de proyecto incluyen permisos de bigquery.tables.create:

Además, debido a que la función de bigquery.user tiene permisos de bigquery.datasets.create, los usuarios asignados a la función bigquery.user pueden crear tablas agrupadas en cualquier conjunto de datos que cree el usuario. Por configuración predeterminada, 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.

Crea una tabla agrupada vacía con una definición de esquema

Cuando creas una tabla en BigQuery, especificas las columnas de agrupamiento en clústeres. Después de crear la tabla, no puedes modificar las columnas de agrupamiento en clústeres. Por ahora, solo puedes especificar columnas de agrupamiento en clústeres para una tabla particionada.

Las columnas de agrupamiento en clústeres deben ser de nivel superior, no se deben repetir y deben ser de uno de los tipos de datos simples siguientes: INTEGER, STRING, DATE, TIMESTAMP, BOOLEAN o NUMERIC.

Puedes especificar hasta cuatro columnas de agrupamiento en clústeres. Cuando especificas columnas múltiples, el orden de las columnas determina el orden de los datos. Por ejemplo, si la tabla está agrupada por columnas a, b y c, los datos se ordenan en el mismo orden, primero por la columna a, luego por la columna b y, por último, por la columna c. Recomendamos que la columna que se agrega o se filtra con más frecuencia aparezca primero.

El orden de las columnas de agrupamiento en clústeres también afecta el rendimiento y el precio de la consulta. Para obtener más información sobre las recomendaciones de consultas de tablas agrupadas, lee Consulta tablas agrupadas.

Para crear una tabla agrupada vacía con una definición de esquema, sigue estos pasos:

IU clásica

  1. Ve a la IU web clásica de BigQuery.

    Ir a la IU web clásica de BigQuery

  2. Haz clic en el ícono de flecha hacia abajo ícono de flecha hacia abajo junto al nombre de tu conjunto de datos en el menú de navegación y haz clic en Create new table (Crear tabla nueva).

  3. En la página Create Table (Crear tabla), en la sección Source Data (Datos de origen), haz clic en Create empty table (Crear tabla vacía).

  4. En la página Create Table, en la sección Destination Table (Tabla de destino), realiza lo siguiente:

    • Para el Table name (Nombre de tabla), elige el conjunto de datos apropiado y, en el campo de nombre de tabla, ingresa el nombre de la tabla que creas en ese momento.
    • Verifica que Table type (Tipo de tabla) esté configurado como Native table (Tabla nativa).
  5. En la sección Schema (Esquema), ingresa de manera manual la definición de esquema.

    • También puedes ingresar la información de esquema de manera manual de la siguiente manera:

      • Haz clic en Edit as text (Editar como texto) y, luego, ingresa el esquema de la tabla como un arreglo JSON.

      • Usa Add Field (Agregar campo) para ingresar el esquema.

  6. En la sección Options (Opciones):

    • Para Partitioning Type (Tipo de partición), haz clic en None (Ninguna) y elige Day (Día).
    • Para Partitioning Field (Campo de partición), elige uno de los siguientes:
      • Elige timestamp para crear una tabla particionada por una columna de FECHA o MARCA DE TIEMPO.
      • Elige _PARTITIONTIME para crear una tabla particionada en tiempo de transferencia.
    • Para Clustering columns (Columnas de agrupamiento en clústeres), ingresa entre uno y cuatro nombres del campo.
    • Para Encryption Type (Tipo de encriptación), deja la opción Default. Esta propiedad es para claves de encriptación administradas por clientes. Por configuración predeterminada, BigQuery encripta contenido de cliente almacenado en reposo.

      Detalles de la tabla particionada

  7. Haz clic en Create Table (Crear tabla).

Después de crear la tabla, puedes actualizar el vencimiento de tabla, la descripción y las etiquetas de la tabla agrupada. No puedes agregar un vencimiento de partición después de crear una tabla mediante la IU web de BigQuery.

Línea de comandos

Usa el comando mk con las marcas siguientes:

  • --table (o el acceso directo -t).
  • --schema &mdash: puedes suministrar la definición del esquema de la tabla intercalada o a través de un archivo de esquema JSON.
  • --time_partitioning_type (para tablas particionadas en tiempo de transferencia) o --time_partitioning_field (para tablas particionadas). Por ahora, DAY es el único valor compatible para --time_partitioning_type.
  • --clustering_fields para especificar hasta cuatro columnas de agrupamiento en clústeres.

Los parámetros opcionales incluyen --expiration, --description, --time_partitioning_expiration, --destination_kms_key y --label.

Si creas una tabla en otro proyecto que no sea el predeterminado, agrega el ID del proyecto al conjunto de datos en el formato siguiente: [PROJECT_ID]:[DATASET].

--destination_kms_key no se muestra aquí. Para obtener más información sobre el uso de esta marca, consulta llaves de encriptación administradas por el cliente.

Ingresa el comando siguiente para crear una tabla agrupada vacía con una definición de esquema:

bq mk --table --expiration [INTEGER1] --schema [SCHEMA] --time_partitioning_type=DAY --time_partitioning_field [COLUMN] --clustering_fields [COLUMNS] --time_partitioning_expiration [INTEGER2] --description "[DESCRIPTION]" --label [KEY:VALUE, KEY:VALUE] [PROJECT_ID]:[DATASET].[TABLE]

Donde:

  • [INTEGER1] es el ciclo de vida predeterminado (en segundos) de la tabla. El valor mínimo es 3,600 segundos (una hora). La fecha de vencimiento se evalúa según la fecha actual más el valor del número entero. Si configuras el tiempo de vencimiento de la tabla cuando creas una tabla particionada por el tiempo, se ignora la configuración de vencimiento de la tabla predeterminada del conjunto de datos. Si configuras este valor, la tabla y todas las particiones se borran después del lapso especificado.
  • [SCHEMA] es una definición de esquema intercalado en el formato [FIELD]:[DATA_TYPE],[FIELD]:[DATA_TYPE] o la ruta de acceso al archivo de esquema JSON en tu máquina local.
  • [COLUMN] es el nombre de la columna TIMESTAMP o DATE que se usa para crear la tabla particionada. Si creas, una tabla particionada, no necesitas especificar la marca --time_partitioning_type=DAY.
  • [COLUMNS] es una lista separada por comas de hasta cuatro columnas de agrupamiento en clústeres.
  • [INTEGER2] es el ciclo de vida predeterminado (en segundos) de las particiones de la tabla. No hay valor mínimo. El tiempo de vencimiento se evalúa según la fecha de la partición más el valor de número entero. El vencimiento de la partición es independiente del vencimiento de la tabla, pero no lo anula. Si configuras un vencimiento de partición que suceda después del vencimiento de la tabla, prevalece el vencimiento de la tabla.
  • [DESCRIPTION] es una descripción de la tabla entre comillas.
  • [KEY:VALUE] es el par clave-valor que representa una etiqueta. Puedes ingresar múltiples etiquetas mediante una lista separada por comas.
  • [PROJECT_ID] es el ID del proyecto.
  • [DATASET] es un conjunto de datos en tu proyecto.
  • [TABLE] es el nombre de la tabla particionada que creas.

Cuando especificas el esquema en la línea de comandos, no puedes incluir un tipo RECORD (STRUCT), incluir una descripción de la columna ni especificar el modo de la columna. Según la configuración predeterminada, todos los modos son NULLABLE. Para incluir descripciones, modos y tipos de RECORD, proporciona un archivo de esquema de JSON.

Ejemplos:

Ingresa el comando siguiente para crear una tabla agrupada llamada myclusteredtable en mydataset en tu proyecto predeterminado. La tabla es una tabla particionada (particionada por una columna TIMESTAMP). El vencimiento de la partición está configurado en 86,400 segundos (1 día), el vencimiento de la tabla está configurado en 2,592,000 (1 mes de 30 días), la descripción está configurada como This is my clustered table y la etiqueta está configurada como organization:development. El comando usa el acceso directo -t en lugar de --table.

El esquema está especificado de forma intercalada como: timestamp:timestamp,customer_id:string,transaction_amount:float. El campo de agrupamiento en clústeres especificado customer_id se usa para agrupar las particiones.

bq mk -t --expiration 2592000 --schema 'timestamp:timestamp,customer_id:string,transaction_amount:float' --time_partitioning_field timestamp --clustering_fields customer_id --time_partitioning_expiration 86400  --description "This is my clustered table" --label org:dev mydataset.myclusteredtable

Ingresa el comando siguiente para crear una tabla agrupada llamada myclusteredtable en myotherproject y no en tu proyecto predeterminado. La tabla es una tabla particionada en tiempo de transferencia. El vencimiento de la partición está configurado en 259,200 segundos (3 días), la descripción está configurada en This is my partitioned table, y la etiqueta está configurada en organization:development. El comando usa el acceso directo -t en lugar de --table. El comando no especifica un vencimiento de tabla. Si el conjunto de datos tiene un vencimiento de tabla predeterminado, se aplica. Si no lo tiene, la tabla no se vencerá nunca, pero las particiones se vencerán en 3 días.

El esquema se especifica en un archivo JSON local: /tmp/myschema.json. El campo customer_id se usa para agrupar las particiones.

bq mk -t --expiration 2592000 --schema /tmp/myschema.json --time_partitioning_type=DAY --clustering_fields=customer_id --time_partitioning_expiration 86400  --description "This is my partitioned table" --label org:dev myotherproject:mydataset.myclusteredtable

Una vez que se creó la tabla, puedes actualizar el vencimiento de la tabla, el vencimiento de la partición, la descripción y las etiquetas de la tabla particionada.

API

Llama al método tables.insert con un recurso de tabla definido que especifique la propiedad timePartitioning, la propiedad clustering.fields y la propiedad schema.

Go

Antes de probar esta muestra, sigue las instrucciones de configuración para Go de la Guía de inicio rápido de BigQuery con bibliotecas cliente. A fin de obtener más información, consulta la documentación de referencia de la API de BigQuery para Go.

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
sampleSchema := bigquery.Schema{
	{Name: "timestamp", Type: bigquery.TimestampFieldType},
	{Name: "origin", Type: bigquery.StringFieldType},
	{Name: "destination", Type: bigquery.StringFieldType},
	{Name: "amount", Type: bigquery.NumericFieldType},
}
metaData := &bigquery.TableMetadata{
	Schema: sampleSchema,
	TimePartitioning: &bigquery.TimePartitioning{
		Field:      "timestamp",
		Expiration: 90 * 24 * time.Hour,
	},
	Clustering: &bigquery.Clustering{
		Fields: []string{"origin", "destination"},
	},
}
tableRef := client.Dataset(datasetID).Table(tableID)
if err := tableRef.Create(ctx, metaData); err != nil {
	return err
}

Crea una tabla agrupada a partir del resultado de una consulta

Existen dos maneras de crear una tabla agrupada a partir del resultado de una consulta:

  • Escribe los resultados en una tabla de destino nueva y especifica las columnas de agrupamiento en clústeres. Este método se analiza a continuación.
  • Mediante el uso de una declaración DDL CREATE TABLE AS SELECT. Para obtener más información sobre este método, consulta Crea una tabla agrupada a partir del resultado de una consulta en la página “Usa declaraciones de lenguaje de definición de datos”.

Puedes crear una tabla agrupada si realizas consultas en una tabla particionada o en una tabla sin particiones. No puedes cambiar una tabla existente a una tabla agrupada con los resultados de una consulta.

Cuando creas una tabla agrupada a partir del resultado de una consulta, debes usar SQL estándar. Por el momento, el SQL heredado no es compatible con las consultas en tablas agrupadas o la escritura de resultados de consultas en tablas agrupadas.

IU clásica

No puedes especificar opciones de agrupamiento en clústeres para una tabla de destino cuando realizas consultas de datos con la IU web de BigQuery, a menos que uses una declaración DDL. Para obtener más información, consulta Usa declaraciones de lenguaje de definición de datos.

CLI

Ingresa el comando bq query y especifica las marcas siguientes:

  • Especifica la marca use_legacy_sql=false para usar la sintaxis de SQL estándar.
  • Proporciona la marca --location y establece el valor en tu ubicación.

Ingresa el comando siguiente para crear una tabla de destino agrupada nueva a partir del resultado de una consulta:

    bq --location=[LOCATION] query --use_legacy_sql=false '[QUERY]'

Donde:

  • [LOCATION] es el nombre de tu ubicación. La marca --location es opcional. Por ejemplo, si usas BigQuery en la región de Tokio, puedes configurar el valor de la marca en asia-northeast1. Puedes configurar un valor predeterminado para la ubicación con el archivo .bigqueryrc.
  • [QUERY] es una consulta en la sintaxis de SQL estándar. Por el momento, no puedes usar SQL heredado para realizar consultas en las tablas agrupadas o escribir resultados de consultas en tablas agrupadas. La consulta puede incluir una declaración CREATE TABLE DDL que especifique las opciones para crear tu tabla agrupada. Puedes usar DDL en vez de especificar las marcas de línea de comandos individuales.

Ejemplos:

Ingresa el comando siguiente para escribir resultados de consultas en una tabla de destino agrupada llamada myclusteredtable en mydataset. mydataset en tu proyecto predeterminado. La consulta recupera datos de una tabla sin particiones, mytable. La columna customer_id se usa para agrupar la tabla. La columna timestamp se usa para crear una tabla particionada.

bq --location=US query --use_legacy_sql=false 'CREATE TABLE mydataset.myclusteredtable PARTITION BY DATE(timestamp) CLUSTER BY customer_id AS SELECT * FROM mydataset.mytable'

API

Para guardar resultados de consultas en un tabla agrupada, llama al método jobs.insert, configura un trabajo query y, luego, incluye una declaración CREATE TABLE DDL que cree tu tabla agrupada.

Especifica tu ubicación en la propiedad location en la sección jobReference del recurso de trabajo.

Crea una tabla agrupada cuando cargas datos

Puedes crear una tabla agrupada si especificas columnas de agrupamiento en clústeres cuando cargas datos en una tabla nueva. No es necesario crear una tabla vacía antes de cargarle datos. Puedes crear una tabla agrupada y cargar tus datos al mismo tiempo.

Para obtener más información, consulta la Introducción a la carga de datos en BigQuery.

Para definir el agrupamiento en clústeres cuando defines un trabajo de carga, haz lo siguiente:

API

Para definir la configuración de agrupamiento en clústeres cuando creas una tabla a través de un trabajo de carga, puedes propagar el mensaje configuration.load.clustering, si propagas la propiedad configuration.load.clustering.Fields con hasta cuatro columnas de agrupamiento en clústeres en orden de prioridad.

Go

Antes de probar esta muestra, sigue las instrucciones de configuración para Go de la Guía de inicio rápido de BigQuery con bibliotecas cliente. A fin de obtener más información, consulta la documentación de referencia de la API de BigQuery para Go.

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
gcsRef := bigquery.NewGCSReference("gs://cloud-samples-data/bigquery/sample-transactions/transactions.csv")
gcsRef.SkipLeadingRows = 1
gcsRef.Schema = bigquery.Schema{
	{Name: "timestamp", Type: bigquery.TimestampFieldType},
	{Name: "origin", Type: bigquery.StringFieldType},
	{Name: "destination", Type: bigquery.StringFieldType},
	{Name: "amount", Type: bigquery.NumericFieldType},
}
loader := client.Dataset(destDatasetID).Table(destTableID).LoaderFrom(gcsRef)
loader.TimePartitioning = &bigquery.TimePartitioning{
	Field: "timestamp",
}
loader.Clustering = &bigquery.Clustering{
	Fields: []string{"origin", "destination"},
}
loader.WriteDisposition = bigquery.WriteEmpty

job, err := loader.Run(ctx)
if err != nil {
	return err
}
status, err := job.Wait(ctx)
if err != nil {
	return err
}

if status.Err() != nil {
	return fmt.Errorf("Job completed with error: %v", status.Err())
}

Controla el acceso a las tablas agrupadas

No puedes asignar controles de acceso directamente a tablas o particiones. Puedes controlar el acceso a la tabla si configuras los controles de acceso a nivel de conjunto de datos o a nivel de proyecto.

Los controles de acceso a nivel de conjunto de datos especifican las operaciones que los usuarios, grupos y cuentas de servicio tienen permitido realizar en las tablas de ese conjunto de datos específico. Si solo asignas permisos a nivel de conjunto de datos, también debes asignar una función predefinida o básica a nivel de proyecto que proporcione el acceso al proyecto, por ejemplo, bigquery.user.

En lugar de otorgar el acceso a conjuntos de datos individuales, puedes asignar funciones de IAM predefinidas a nivel de proyecto que otorguen permisos para todos los datos de tablas en todos los conjuntos de datos de un proyecto.

También puedes crear funciones de IAM personalizadas. Si creas una función personalizada, los permisos que otorgas dependen de las operaciones de tabla que quieres que el usuario, grupo o cuenta de servicio pueda realizar.

Para obtener más información acerca de las funciones y permisos, consulta los siguientes documentos:

Usa tablas agrupadas

Obtén información sobre las tablas agrupadas

Puedes obtener información sobre las tablas de las siguientes formas:

  • Usa GCP Console o la IU web de BigQuery clásica.
  • Usa el comando de CLI bq show.
  • Llama al método de la API tables.get.
  • Consulta las vistas INFORMATION_SCHEMA (Beta).

Permisos necesarios

Para obtener información sobre las tablas, debes tener asignada la función de 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 otorgados 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 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.

Obtén información sobre tablas agrupadas

Para ver información sobre una tabla agrupada, haz lo siguiente:

IU clásica

  1. En el panel de navegación, haz clic en el ícono de flecha hacia abajo ícono de flecha hacia abajo que se encuentra a la izquierda de tu conjunto de datos para expandirlo, o haz doble clic en el nombre del conjunto de datos. Esto muestra las tablas y vistas en el conjunto de datos.

  2. Haz clic en el nombre de la tabla.

  3. Haz clic en Details (detalles). La página Table Details (Detalles de la tabla) muestra los detalles de la tabla que incluyen las columnas de agrupamiento en clústeres.

    Detalles de tablas agrupadas

Línea de comandos

Ejecuta el comando bq show para mostrar toda la información de la tabla. Usa la marca --schema para mostrar solo la información de esquema de la tabla. La marca --format se puede usar para controlar el formato de salida.

Si quieres obtener información acerca de una tabla de un proyecto que no sea tu proyecto predeterminado, agrega el ID del proyecto al conjunto de datos en el formato siguiente: [PROJECT_ID]:[DATASET].

bq show --schema --format=prettyjson [PROJECT_ID]:[DATASET].[TABLE]

Donde:

  • [PROJECT_ID] es el ID del proyecto.
  • [DATASET] es el nombre del conjunto de datos.
  • [TABLE] es el nombre de la tabla.

Ejemplos:

Ingresa el comando siguiente para mostrar toda la información sobre myclusteredtable en mydataset. mydataset se encuentra en tu proyecto predeterminado.

bq show --format=prettyjson mydataset.myclusteredtable

El resultado debe verse de la manera siguiente:

{
  "clustering": {
    "fields": [
      "customer_id"
    ]
  },
...
}

API

Llama al método bigquery.tables.get y proporciona los parámetros relevantes.

Obtén información sobre tablas agrupadas con INFORMATION_SCHEMA (Beta)

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 la vistas INFORMATION_SCHEMA.COLUMNS y la vista INFORMATION_SCHEMA.COLUMN_FIELD_PATHS para recuperar metadatos sobre las columnas (campos) en una tabla.

Para las tablas agrupadas, puedes consultar la columna CLUSTERING_ORDINAL_POSITION en la vista INFORMATION_SCHEMA.COLUMNS para recuperar información sobre tus columnas en agrupamiento en clústeres.

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 conocido como datasetId)
TABLE_NAME STRING El nombre de la tabla o la vista (también conocido como tableId)
TABLE_TYPE STRING El tipo de tabla:
IS_INSERTABLE_INTO STRING YES o NO, lo cual depende si la tabla es compatible con las declaraciones DML INSERT
IS_TYPED STRING El valor es siempre NO
CREATION_TIME TIMESTAMP La fecha/hora de creación de la tabla

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 Run (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 Run (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 conocido como datasetId)
TABLE_NAME STRING El nombre de la tabla o la vista (también conocido como 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 El ciclo de vida predeterminado, en días, de todas las particiones en 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 en la tabla

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 Run (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.SCHEMATA_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 Run (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 conocido como datasetId)
TABLE_NAME STRING El nombre de la tabla o la vista (también conocido como 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 pseudocolumna 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 particionada
CLUSTERING_ORDINAL_POSITION STRING El desplazamiento 1 indexado de la columna dentro de las columnas de agrupamiento en clústeres; el valor es NULL si la tabla no es una tabla agrupada

Ejemplos

En el ejemplo siguiente, 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.

Debido a que esta tabla está en el proyecto bigquery-public-data, debes agregar el ID del proyecto al conjunto de datos en el formato siguiente: `[PROJECT_ID]:[DATASET].INFORMATION_SCHEMA.[VIEW]` por ejemplo, `bigquery-public-data:census_bureau_usa.INFORMATION_SCHEMA.TABLES`.

Las columnas siguientes están excluidas 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 Run (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 de 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 conocido como datasetId)
TABLE_NAME STRING El nombre de la tabla o la vista (también conocido como 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

Ejemplos

En el ejemplo siguiente, 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.

Debido a que esta tabla está en el proyecto bigquery-public-data, debes agregar el ID del proyecto al conjunto de datos en el formato siguiente: `[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 Run (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 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        |
  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
  

Haz una lista de tablas agrupadas en un conjunto de datos

Puedes hacer listas de tablas agrupadas en conjuntos de datos mediante la IU web de BigQuery, el comando bq ls de la CLI o una llamada al método de la API tables.list.

Los permisos y los pasos requeridos para hacer una lista de tablas agrupadas son los mismos que para las tablas particionadas. Para obtener más información sobre cómo hacer listas de tablas, consulta Haz una lista de tablas particionadas en un conjunto de datos.

Características en desarrollo

Las siguientes características están en desarrollo, pero aún no están disponibles en la versión Alfa:

  • Compatibilidad con tablas nativas en agrupamiento en clústeres (no particionadas)
  • Reducción de costos para tipos particulares de consultas que usan filtros en columnas en agrupamiento en clústeres

Pasos siguientes

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

Enviar comentarios sobre...

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