Consulta tablas agrupadas

Filtrar por columnas agrupadas te permite mejorar el rendimiento y reducir los costos cuando consultas tablas agrupadas en BigQuery. Cuando una consulta filtra una o más columnas agrupadas, BigQuery usa la información de agrupamiento para analizar solo los bloques relevantes, un proceso llamado reducción de bloques.

En este documento, se describen los permisos y las prácticas recomendadas para consultar tablas agrupadas, incluido por qué es importante el orden de las columnas agrupadas en tus filtros. Solo puedes consultar tablas agrupadas con GoogleSQL.

Permisos necesarios

Para ejecutar un trabajo de consulta, necesitas el permiso bigquery.jobs.create de Identity and Access Management (IAM) en el proyecto que ejecuta el trabajo de consulta.

Cada una de las siguientes funciones predefinidas de IAM incluye los permisos que necesitas para ejecutar un trabajo de consulta:

• roles/bigquery.admin
• roles/bigquery.jobUser
• roles/bigquery.user

También necesitas el permiso bigquery.tables.getData en todas las tablas y vistas a las que hace referencia la consulta. Además, cuando consultas una vista, necesitas este permiso en todas las tablas y vistas subyacentes. Sin embargo, si usas vistas autorizadas o conjuntos de datos autorizados, no necesitas acceso a los datos de origen subyacentes.

Cada uno de los siguientes roles predefinidos de IAM incluye el permiso que necesitas en todas las tablas y vistas a las que hace referencia la consulta:

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

Para obtener más información de los roles de IAM en BigQuery, consulta Funciones y permisos predefinidos.

Consultar una tabla agrupada en clústeres

Para consultar tablas agrupadas en clústeres, puedes realizar las siguientes acciones:

• Usa la consola de Cloud de Confiance
• Usa el comando bq query en la herramienta de línea de comandos de bq
• Llama al método jobs.insert y configura un trabajo de consulta
• Usa bibliotecas cliente.

import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/bigquery"
	"google.golang.org/api/iterator"
)

// queryClusteredTable demonstrates querying a table that has a clustering specification.
func queryClusteredTable(w io.Writer, projectID, datasetID, tableID 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()

	q := client.Query(fmt.Sprintf(`
	SELECT
	  COUNT(1) as transactions,
	  SUM(amount) as total_paid,
	  COUNT(DISTINCT destination) as distinct_recipients
    FROM
	  `+"`%s.%s`"+`
	 WHERE
	    timestamp > TIMESTAMP('2015-01-01')
		AND origin = @wallet`, datasetID, tableID))
	q.Parameters = []bigquery.QueryParameter{
		{
			Name:  "wallet",
			Value: "wallet00001866cb7e0f09a890",
		},
	}
	// Run the query and print results when the query job is completed.
	job, err := q.Run(ctx)
	if err != nil {
		return err
	}
	status, err := job.Wait(ctx)
	if err != nil {
		return err
	}
	if err := status.Err(); err != nil {
		return err
	}
	it, err := job.Read(ctx)
	for {
		var row []bigquery.Value
		err := it.Next(&row)
		if err == iterator.Done {
			break
		}
		if err != nil {
			return err
		}
		fmt.Fprintln(w, row)
	}
	return nil
}

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.TableResult;

public class QueryClusteredTable {

  public static void runQueryClusteredTable() throws Exception {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    queryClusteredTable(projectId, datasetName, tableName);
  }

  public static void queryClusteredTable(String projectId, 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();

      String sourceTable = "`" + projectId + "." + datasetName + "." + tableName + "`";
      String query =
          "SELECT word, word_count\n"
              + "FROM "
              + sourceTable
              + "\n"
              // Optimize query performance by filtering the clustered columns in sort order
              + "WHERE corpus = 'romeoandjuliet'\n"
              + "AND word_count >= 1";

      QueryJobConfiguration queryConfig = QueryJobConfiguration.newBuilder(query).build();

      TableResult results = bigquery.query(queryConfig);

      results
          .iterateAll()
          .forEach(row -> row.forEach(val -> System.out.printf("%s,", val.toString())));

      System.out.println("Query clustered table performed successfully.");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

from google.cloud import bigquery

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

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

sql = "SELECT * FROM `bigquery-public-data.samples.shakespeare`"
cluster_fields = ["corpus"]

job_config = bigquery.QueryJobConfig(
    clustering_fields=cluster_fields, destination=table_id
)

# Start the query, passing in the extra configuration.
client.query_and_wait(
    sql, job_config=job_config
)  # Make an API request and wait for job to complete.

table = client.get_table(table_id)  # Make an API request.
if table.clustering_fields == cluster_fields:
    print(
        "The destination table is written using the cluster_fields configuration."
    )

Prácticas recomendadas

Si deseas obtener el mejor rendimiento de las consultas en las tablas agrupadas, utiliza las siguientes recomendaciones.

Para dar contexto, la tabla de muestra que se usa en los ejemplos de las prácticas recomendadas es una tabla agrupada en clústeres que se crea mediante el uso de una declaración DDL. La instrucción DDL crea una tabla llamada ClusteredSalesData. La tabla está agrupada según las siguientes columnas: customer_id, product_id, order_id, en ese orden de clasificación.

CREATE TABLE
  `mydataset.ClusteredSalesData`
PARTITION BY
  DATE(timestamp)
CLUSTER BY
  customer_id,
  product_id,
  order_id AS
SELECT
  *
FROM
  `mydataset.SalesData`

Filtra columnas agrupadas según el orden de clasificación

Cuando especifiques un filtro, usa expresiones que filtren las columnas agrupadas en el orden de clasificación. El orden es la clasificación de columna que se proporciona en la cláusula CLUSTER BY. Para obtener los beneficios del agrupamiento en clústeres, incluye una o más de las columnas agrupadas en orden de izquierda a derecha, comenzando con la primera columna. En la mayoría de los casos, la primera columna de agrupamiento en clústeres es la más eficaz para la eliminación de bloques, seguida de la segunda y la tercera. Aún puedes usar la segunda o tercera columna sola en la consulta, pero es probable que la reducción de bloques no sea tan eficaz. El orden de los nombres de columna dentro de la expresión de filtro no afecta el rendimiento.

En el siguiente ejemplo, se consulta la tabla agrupada en clústeres ClusteredSalesData que se creó en el ejemplo anterior. La siguiente consulta incluye una expresión de filtro que filtra por customer_id y, luego, por product_id. Para optimizar el rendimiento, esta consulta filtra las columnas agrupadas en orden (el orden de las columnas que se proporciona en la cláusula CLUSTER BY).

SELECT
  SUM(totalSale)
FROM
  `mydataset.ClusteredSalesData`
WHERE
  customer_id = 10000
  AND product_id LIKE 'gcp_analytics%'

La siguiente consulta no filtra las columnas agrupadas en el orden de clasificación. Como resultado, el rendimiento de la consulta no es óptimo. Esta consulta filtra por product_id y, luego, por order_id (se omite customer_id).

SELECT
  SUM(totalSale)
FROM
  `mydataset.ClusteredSalesData`
WHERE
  product_id LIKE 'gcp_analytics%'
  AND order_id = 20000

No uses columnas agrupadas en expresiones de filtro complejas

Si usas una columna agrupada en una expresión de filtro compleja, el rendimiento de la consulta no se optimiza, porque la reducción de bloques no se puede aplicar.

Por ejemplo, la siguiente consulta no reducirá bloques porque una columna agrupada en clústeres (customer_id) está en uso en una función en la expresión de filtro.

SELECT
  SUM(totalSale)
FROM
  `mydataset.ClusteredSalesData`
WHERE
  CAST(customer_id AS STRING) = "10000"

A fin de optimizar el rendimiento de la consulta mediante la reducción de bloques, usa expresiones de filtro simples, como las siguientes. En este ejemplo, se aplica un filtro simple a la columna agrupada en clústeres: customer_id.

SELECT
  SUM(totalSale)
FROM
  `mydataset.ClusteredSalesData`
WHERE
  customer_id = 10000

No compares columnas agrupadas con otras columnas

Si una expresión de filtro compara una columna agrupada con otra columna (sea una columna agrupada o no agrupada), el rendimiento de la consulta no se optimiza, porque la reducción de bloques no se puede aplicar.

La siguiente consulta no reduce los bloques porque la expresión de filtro compara una columna agrupada en clústeres (customer_id) con otra columna (order_id).

SELECT
  SUM(totalSale)
FROM
  `mydataset.ClusteredSalesData`
WHERE
  customer_id = order_id

Seguridad de las tablas

Para controlar el acceso a las tablas en BigQuery, consulta Controla el acceso a los recursos con IAM.

¿Qué sigue?

• Para obtener más información sobre cómo ejecutar consultas, revisa Ejecuta consultas interactivas y por lotes.
• Para aprender a crear y utilizar tablas agrupadas en clústeres, consulta Crea y usa tablas agrupadas.
• Para obtener una descripción general de la compatibilidad con tablas particionadas en BigQuery, consulta Introducción a las tablas particionadas.
• Para aprender a crear tablas particionadas, consulta Crea tablas particionadas.