Consulta tablas agrupadas

Consulta tablas agrupadas

Cuando creas una tabla agrupada en clústeres en BigQuery, los datos de la tabla se organizan automáticamente según el contenido de una o más columnas en el esquema de la tabla. Las columnas que especificas se usan para colocar datos relacionados. Cuando agrupas una tabla en clústeres a través de varias columnas, el orden de las columnas que especifiques es importante. El orden de las columnas especificadas determina el orden de clasificación de los datos.

A fin de optimizar el rendimiento cuando ejecutas consultas en tablas agrupadas, utiliza una expresión que filtre una columna agrupada o varias columnas agrupadas en el orden en que se especifican las columnas agrupadas. Por lo general, las consultas que filtran las columnas agrupadas funcionan mejor que las consultas que solo filtran las columnas no agrupadas.

BigQuery ordena los datos en una tabla agrupada según los valores de las columnas de agrupamiento y, luego, los organiza en bloques.

Cuando envías una consulta con un filtro en una columna agrupada, BigQuery usa la información de agrupamiento para determinar de manera eficaz si el bloque contiene datos pertinentes a la consulta. Esto permite que BigQuery solo analice los bloques relevantes, proceso conocido como reducción de bloques.

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

• Usa Cloud Console.
• Usar el comando bq query de la herramienta de línea de comandos de bq
• Con una llamada al método jobs.insert de la API y la configuración de un trabajo de consulta
• Mediante las bibliotecas cliente

Actualmente, solo puedes utilizar SQL estándar con las tablas agrupadas.

Go

Antes de probar este ejemplo, sigue las instrucciones de configuración para Go incluidas en la guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Si deseas obtener más información, consulta la documentación de referencia de la API de Go de BigQuery.

Ver en GitHubComentarios

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
}

Java

Antes de probar este ejemplo, sigue las instrucciones de configuración para Java incluidas en la guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Para obtener más información, consulta la documentación de referencia de la API de BigQuery para Java.

Ver en GitHubComentarios

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());
    }
  }
}

Python

Antes de probar esta muestra, sigue las instrucciones de configuración para Python incluidas en la guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Si deseas obtener más información, consulta la documentación de referencia de la API de Python de BigQuery.

Ver en GitHubComentarios

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.
query_job = client.query(sql, job_config=job_config)  # Make an API request.
query_job.result()  # Wait for the 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."
    )

Permisos necesarios

Como mínimo, para consultar una tabla, debes tener los permisos bigquery.tables.getData.

Las siguientes funciones predefinidas de IAM incluyen los permisos bigquery.tables.getData:

• bigquery.dataViewer
• bigquery.dataOwner
• bigquery.dataEditor
• bigquery.admin

Además, si un usuario tiene permisos bigquery.datasets.create, se le otorga el acceso bigquery.dataOwner cuando crea un conjunto de datos. El acceso bigquery.dataOwner permite al usuario realizar consultas sobre las tablas y vistas en el conjunto de datos.

También debes tener permisos bigquery.jobs.create si quieres ejecutar trabajos de consulta. Las siguientes funciones predefinidas de IAM incluyen los permisos bigquery.jobs.create:

• bigquery.user
• bigquery.jobUser
• bigquery.admin

Para obtener más información sobre las funciones y permisos de IAM en BigQuery, consulta Control de acceso.

Recomendaciones

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

Tabla de muestra utilizada en los ejemplos

La tabla de muestra usada en los ejemplos de esta página 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. Para obtener los beneficios del agrupamiento en clústeres, incluye todas las columnas agrupadas o un subconjunto de las columnas en orden de izquierda a derecha, comenzando con la primera columna. Por ejemplo, si el orden de la columna es A, B, C, una consulta que filtre en A y B podría beneficiarse del agrupamiento en clústeres, no así una consulta que filtre en B yC. El orden de los nombres de columna dentro de la expresión de filtro no afecta el rendimiento.

La siguiente consulta incluye una expresión de filtro que filtra por customer_id y, luego, por product_id. Esta consulta optimiza el rendimiento; para ello, filtra las columnas agrupadas en el orden de clasificación.

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 recortará 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 recorta 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 Introducción a los controles de acceso a tablas.

Próximos pasos

• 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 cómo crear tablas particionadas, consulta Crea tablas particionadas.