Crear tablas agrupadas en clústeres

Consola

1. En la Google Cloud consola, ve a la página BigQuery.

   Ir a BigQuery

2. En el panel Explorador, expande tu proyecto y selecciona un conjunto de datos.
3. En la sección Información del conjunto de datos, haz clic en add_box Crear tabla.
4. En el panel Crear tabla, especifica los siguientes detalles:
1. En la sección Fuente, selecciona Tabla vacía en la lista Crear tabla a partir de.
2. En la sección Destino, especifica los siguientes detalles:
   1. En Conjunto de datos, selecciona el conjunto de datos en el que quieras crear la tabla.
   2. En el campo Tabla, introduce el nombre de la tabla que quieras crear.
   3. Verifica que el campo Tipo de tabla sea Tabla nativa.
3. En la sección Schema (Esquema), introduce la definición de schema. Puede introducir la información del esquema manualmente mediante uno de los siguientes métodos:
   • Opción 1: Haz clic en Editar como texto y pega el esquema en forma de matriz JSON. Cuando usas una matriz JSON, generas el esquema siguiendo el mismo proceso que para crear un archivo de esquema JSON. Para ver el esquema de una tabla en formato JSON, introduce el siguiente comando:

         bq show --format=prettyjson dataset.table
         

   • Opción 2: Haz clic en add_box Añadir campo e introduce el esquema de la tabla. Especifica el Nombre, Tipo y Modo de cada campo.
4. En Orden de clústeres, introduce entre uno y cuatro nombres de columna separados por comas.
5. Opcional: En la sección Opciones avanzadas, si quieres usar una clave de cifrado gestionada por el cliente, selecciona la opción Usar una clave de cifrado gestionada por el cliente (CMEK). De forma predeterminada, BigQuery encripta el contenido de los clientes almacenado en reposo mediante una Google-owned and Google-managed encryption key.
6. Haz clic en Crear tabla.

SQL

Usa el comando CREATE TABLE DDL statement con la opción CLUSTER BY. En el siguiente ejemplo se crea una tabla agrupada denominada myclusteredtable en mydataset:

1. En la Google Cloud consola, ve a la página BigQuery.

   Ir a BigQuery

2. En el editor de consultas, introduce la siguiente instrucción:

   CREATE TABLE mydataset.myclusteredtable
   (
     customer_id STRING,
     transaction_amount NUMERIC
   )
   CLUSTER BY
     customer_id
     OPTIONS (
       description = 'a table clustered by customer_id');

3. Haz clic en play_circle Ejecutar.

Para obtener más información sobre cómo ejecutar consultas, consulta Ejecutar una consulta interactiva.

bq

Usa el comando bq mk con las siguientes marcas:

• --table (o el acceso directo -t).
• --schema. Puedes proporcionar la definición del esquema de la tabla de forma insertada o usar un archivo de esquema JSON.
• --clustering_fields. Puede especificar hasta cuatro columnas de clustering.

Entre los parámetros opcionales se incluyen --expiration, --description, --time_partitioning_type, --time_partitioning_field, --time_partitioning_expiration, --destination_kms_key y --label.

Si vas a crear una tabla en un proyecto que no sea el predeterminado, añade el ID del proyecto al conjunto de datos con el siguiente formato: project_id:dataset.

--destination_kms_key no se muestra aquí. Para obtener información sobre cómo usar --destination_kms_key, consulta el artículo sobre las claves de cifrado gestionadas por el cliente.

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

bq mk \
    --table \
    --expiration INTEGER1 \
    --schema SCHEMA \
    --clustering_fields CLUSTER_COLUMNS \
    --description "DESCRIPTION" \
    --label KEY:VALUE,KEY:VALUE \
    PROJECT_ID:DATASET.TABLE

Haz los cambios siguientes:

• INTEGER1: el tiempo de vida predeterminado de la tabla, en segundos. El valor mínimo es de 3600 segundos (una hora). La hora de vencimiento se calcula como la hora UTC actual más el valor entero. Si define la fecha de vencimiento de la tabla al crearla, se ignorará el ajuste de vencimiento de tabla predeterminado del conjunto de datos. Si asigna este valor, la tabla se eliminará después del tiempo especificado.
• SCHEMA: una definición de esquema insertada en el formato COLUMN:DATA_TYPE,COLUMN:DATA_TYPE o la ruta al archivo de esquema JSON de tu máquina local.
• CLUSTER_COLUMNS: lista separada por comas de hasta cuatro columnas de clustering. La lista no puede contener espacios.
• DESCRIPTION: una descripción de la tabla entre comillas.
• KEY:VALUE: el par clave-valor que representa una etiqueta. Puedes introducir varias etiquetas mediante una lista separada por comas.
• PROJECT_ID: tu ID de proyecto.
• DATASET: un conjunto de datos de tu proyecto.
• TABLE: el nombre de la tabla que vas a crear.

Cuando especifica el esquema en la línea de comandos, no puede incluir un tipo RECORD (STRUCT), una descripción de la columna ni el modo de la columna. Todos los modos tienen el valor predeterminado NULLABLE. Para incluir descripciones, modos y tipos RECORD, proporciona un archivo de esquema JSON.

Ejemplos:

Introduce el siguiente comando para crear una tabla agrupada llamada myclusteredtable en mydataset en tu proyecto predeterminado. La caducidad de la tabla se ha establecido en 2.592.000 (1 mes de 30 días), la descripción se ha establecido en This is my clustered table y la etiqueta se ha establecido en organization:development. El comando usa la combinación de teclas -t en lugar de --table.

El esquema se especifica de forma insertada como: timestamp:timestamp,customer_id:string,transaction_amount:float. El campo de clustering customer_id especificado se usa para agrupar la tabla.

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

Introduce el siguiente comando para crear una tabla agrupada llamada myclusteredtable en myotherproject, no en tu proyecto predeterminado. La descripción se ha definido como This is my clustered table y la etiqueta como organization:development. El comando usa la combinación de teclas -t en lugar de --table. Este comando no especifica un vencimiento de la tabla. Si el conjunto de datos tiene una fecha de vencimiento de tabla predeterminada, se aplica. Si el conjunto de datos no tiene una fecha de vencimiento predeterminada para las tablas, estas nunca caducarán.

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

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

Una vez creada la tabla, puede actualizar su descripción y sus etiquetas.

Terraform

Usa el recurso google_bigquery_table.

Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Para obtener más información, consulta Configurar la autenticación para bibliotecas de cliente.

En el siguiente ejemplo se crea una tabla llamada mytable que se agrupa en clústeres en las columnas ID y Created:

resource "google_bigquery_dataset" "default" {
  dataset_id                      = "mydataset"
  default_partition_expiration_ms = 2592000000  # 30 days
  default_table_expiration_ms     = 31536000000 # 365 days
  description                     = "dataset description"
  location                        = "US"
  max_time_travel_hours           = 96 # 4 days

  labels = {
    billing_group = "accounting",
    pii           = "sensitive"
  }
}

resource "google_bigquery_table" "default" {
  dataset_id          = google_bigquery_dataset.default.dataset_id
  table_id            = "mytable"
  deletion_protection = false # set to "true" in production

  clustering = ["ID", "Created"]

  schema = <<EOF
[
  {
    "name": "ID",
    "type": "INT64",
    "description": "Item ID"
  },
  {
    "name": "Item",
    "type": "STRING",
    "mode": "NULLABLE"
  },
 {
   "name": "Created",
   "type": "TIMESTAMP"
 }
]
EOF

}

Para aplicar la configuración de Terraform en un proyecto, sigue los pasos que se indican en las siguientes secciones. Google Cloud

Preparar Cloud Shell

1. Abre Cloud Shell.

2. Define el Google Cloud proyecto Google Cloud predeterminado en el que quieras aplicar tus configuraciones de Terraform.

   Solo tiene que ejecutar este comando una vez por proyecto y puede hacerlo en cualquier directorio.

   export GOOGLE_CLOUD_PROJECT=PROJECT_ID

   Las variables de entorno se anulan si defines valores explícitos en el archivo de configuración de Terraform.

Preparar el directorio

Cada archivo de configuración de Terraform debe tener su propio directorio (también llamado módulo raíz).

1. En Cloud Shell, crea un directorio y un archivo nuevo en ese directorio. El nombre del archivo debe tener la extensión .tf. Por ejemplo, main.tf. En este tutorial, nos referiremos al archivo como main.tf.

   mkdir DIRECTORY && cd DIRECTORY && touch main.tf

2. Si estás siguiendo un tutorial, puedes copiar el código de ejemplo de cada sección o paso.

   Copia el código de ejemplo en el archivo main.tf que acabas de crear.

   También puedes copiar el código de GitHub. Se recomienda cuando el fragmento de Terraform forma parte de una solución integral.

3. Revisa y modifica los parámetros de ejemplo para aplicarlos a tu entorno.
4. Guarda los cambios.
5. Inicializa Terraform. Solo tienes que hacerlo una vez por directorio.

   terraform init

   Si quieres usar la versión más reciente del proveedor de Google, incluye la opción -upgrade:

   terraform init -upgrade

Aplica los cambios

1. Revisa la configuración y comprueba que los recursos que va a crear o actualizar Terraform se ajustan a tus expectativas:

   terraform plan

   Haga las correcciones necesarias en la configuración.

2. Aplica la configuración de Terraform ejecutando el siguiente comando e introduciendo yes en la petición:

   terraform apply

   Espera hasta que Terraform muestre el mensaje "Apply complete!".

3. Abre tu Google Cloud proyecto para ver los resultados. En la Google Cloud consola, ve a tus recursos en la interfaz de usuario para asegurarte de que Terraform los ha creado o actualizado.

API

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

Python

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

# TODO(developer): Set table_id to the ID of the table to create.
# table_id = "your-project.your_dataset.your_table_name"

schema = [
    bigquery.SchemaField("full_name", "STRING"),
    bigquery.SchemaField("city", "STRING"),
    bigquery.SchemaField("zipcode", "INTEGER"),
]

table = bigquery.Table(table_id, schema=schema)
table.clustering_fields = ["city", "zipcode"]
table = client.create_table(table)  # Make an API request.
print(
    "Created clustered table {}.{}.{}".format(
        table.project, table.dataset_id, table.table_id
    )
)

Go

import (
	"context"
	"fmt"
	"time"

	"cloud.google.com/go/bigquery"
)

// createTableClustered demonstrates creating a BigQuery table with advanced properties like
// partitioning and clustering features.
func createTableClustered(projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydatasetid"
	// tableID := "mytableid"
	ctx := context.Background()

	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	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
	}
	return nil
}

Java

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Clustering;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.StandardTableDefinition;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;
import com.google.cloud.bigquery.TimePartitioning;
import com.google.common.collect.ImmutableList;

public class CreateClusteredTable {
  public static void runCreateClusteredTable() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    createClusteredTable(datasetName, tableName);
  }

  public static void createClusteredTable(String datasetName, String tableName) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, tableName);

      TimePartitioning partitioning = TimePartitioning.of(TimePartitioning.Type.DAY);

      Schema schema =
          Schema.of(
              Field.of("name", StandardSQLTypeName.STRING),
              Field.of("post_abbr", StandardSQLTypeName.STRING),
              Field.of("date", StandardSQLTypeName.DATE));

      Clustering clustering =
          Clustering.newBuilder().setFields(ImmutableList.of("name", "post_abbr")).build();

      StandardTableDefinition tableDefinition =
          StandardTableDefinition.newBuilder()
              .setSchema(schema)
              .setTimePartitioning(partitioning)
              .setClustering(clustering)
              .build();
      TableInfo tableInfo = TableInfo.newBuilder(tableId, tableDefinition).build();

      bigquery.create(tableInfo);
      System.out.println("Clustered table created successfully");
    } catch (BigQueryException e) {
      System.out.println("Clustered table was not created. \n" + e.toString());
    }
  }
}

SQL

Para crear una tabla agrupada en clústeres a partir del resultado de una consulta, usa la declaración de DDL CREATE TABLE con la opción CLUSTER BY. En el siguiente ejemplo se crea una tabla agrupada en clústeres por customer_id consultando una tabla sin clústeres:

1. En la Google Cloud consola, ve a la página BigQuery.

   Ir a BigQuery

2. En el editor de consultas, introduce la siguiente instrucción:

   CREATE TABLE mydataset.clustered_table
   (
     customer_id STRING,
     transaction_amount NUMERIC
   )
   CLUSTER BY
     customer_id
   AS (
     SELECT * FROM mydataset.unclustered_table
   );

3. Haz clic en play_circle Ejecutar.

Para obtener más información sobre cómo ejecutar consultas, consulta Ejecutar una consulta interactiva.

bq

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

bq --location=LOCATION query \
    --use_legacy_sql=false 'QUERY'

Haz los cambios siguientes:

• LOCATION: el nombre de tu ubicación. La marca --location es opcional. Por ejemplo, si usas BigQuery en la región de Tokio, puedes asignar el valor asia-northeast1 a la marca. Puedes definir un valor predeterminado para la ubicación mediante el archivo.bigqueryrc.
• QUERY: una consulta con sintaxis de GoogleSQL. Actualmente, no puedes usar SQL antiguo para consultar tablas agrupadas en clústeres ni para escribir resultados de consultas en tablas agrupadas en clústeres. La consulta puede contener una declaración CREATE TABLE DDL que especifique las opciones para crear la tabla agrupada. Puedes usar DDL en lugar de especificar las marcas de línea de comandos individuales.

Ejemplos:

Introduce el siguiente comando para escribir los resultados de la consulta en una tabla de destino agrupada llamada myclusteredtable en mydataset. mydataset está en tu proyecto predeterminado. La consulta obtiene datos de una tabla sin particiones: mytable. La columna customer_id de la tabla se usa para agrupar en clústeres la tabla. La columna timestamp de la tabla se usa para crear una tabla con particiones.

bq 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 los resultados de una consulta en una tabla agrupada en clústeres, llama al método jobs.insert, configura un trabajo query e incluye una declaración CREATE TABLE DDL que cree la tabla agrupada en clústeres.

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

SQL

Usa la instrucción LOAD DATA. En el siguiente ejemplo se cargan datos AVRO para crear una tabla que se particiona por el campo transaction_date y se agrupa en clústeres por el campo customer_id. También configura las particiones para que caduquen al cabo de tres días.

1. En la Google Cloud consola, ve a la página BigQuery.

   Ir a BigQuery

2. En el editor de consultas, introduce la siguiente instrucción:

   LOAD DATA INTO mydataset.mytable
   PARTITION BY transaction_date
   CLUSTER BY customer_id
     OPTIONS (
       partition_expiration_days = 3)
   FROM FILES(
     format = 'AVRO',
     uris = ['gs://bucket/path/file.avro']);

3. Haz clic en play_circle Ejecutar.

Para obtener más información sobre cómo ejecutar consultas, consulta Ejecutar una consulta interactiva.

API

Go

import (
	"context"
	"fmt"

	"cloud.google.com/go/bigquery"
)

// importClusteredTable demonstrates creating a table from a load job and defining partitioning and clustering
// properties.
func importClusteredTable(projectID, destDatasetID, destTableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// tableID := "mytable"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	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())
	}
	return nil
}

Java

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Clustering;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.FormatOptions;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.LoadJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TimePartitioning;
import com.google.common.collect.ImmutableList;

public class LoadTableClustered {

  public static void runLoadTableClustered() throws Exception {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "/path/to/file.csv";
    loadTableClustered(datasetName, tableName, sourceUri);
  }

  public static void loadTableClustered(String datasetName, String tableName, String sourceUri)
      throws Exception {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, tableName);

      Schema schema =
          Schema.of(
              Field.of("name", StandardSQLTypeName.STRING),
              Field.of("post_abbr", StandardSQLTypeName.STRING),
              Field.of("date", StandardSQLTypeName.DATE));

      TimePartitioning partitioning = TimePartitioning.of(TimePartitioning.Type.DAY);

      Clustering clustering =
          Clustering.newBuilder().setFields(ImmutableList.of("name", "post_abbr")).build();

      LoadJobConfiguration loadJobConfig =
          LoadJobConfiguration.builder(tableId, sourceUri)
              .setFormatOptions(FormatOptions.csv())
              .setSchema(schema)
              .setTimePartitioning(partitioning)
              .setClustering(clustering)
              .build();

      Job loadJob = bigquery.create(JobInfo.newBuilder(loadJobConfig).build());

      // Load data from a GCS parquet file into the table
      // Blocks until this load table job completes its execution, either failing or succeeding.
      Job completedJob = loadJob.waitFor();

      // Check for errors
      if (completedJob == null) {
        throw new Exception("Job not executed since it no longer exists.");
      } else if (completedJob.getStatus().getError() != null) {
        // You can also look at queryJob.getStatus().getExecutionErrors() for all
        // errors, not just the latest one.
        throw new Exception(
            "BigQuery was unable to load into the table due to an error: \n"
                + loadJob.getStatus().getError());
      }
      System.out.println("Data successfully loaded into clustered table during load job");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Data not loaded into clustered table during load job \n" + e.toString());
    }
  }
}

Python

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

# TODO(developer): Set table_id to the ID of the table to create.
# table_id = "your-project.your_dataset.your_table_name"

job_config = bigquery.LoadJobConfig(
    skip_leading_rows=1,
    source_format=bigquery.SourceFormat.CSV,
    schema=[
        bigquery.SchemaField("timestamp", bigquery.SqlTypeNames.TIMESTAMP),
        bigquery.SchemaField("origin", bigquery.SqlTypeNames.STRING),
        bigquery.SchemaField("destination", bigquery.SqlTypeNames.STRING),
        bigquery.SchemaField("amount", bigquery.SqlTypeNames.NUMERIC),
    ],
    time_partitioning=bigquery.TimePartitioning(field="timestamp"),
    clustering_fields=["origin", "destination"],
)

job = client.load_table_from_uri(
    ["gs://cloud-samples-data/bigquery/sample-transactions/transactions.csv"],
    table_id,
    job_config=job_config,
)

job.result()  # Waits for the job to complete.

table = client.get_table(table_id)  # Make an API request.
print(
    "Loaded {} rows and {} columns to {}".format(
        table.num_rows, len(table.schema), table_id
    )
)