Interroger des tables groupées

Le filtrage sur les colonnes groupées vous permet d'améliorer les performances et de réduire les coûts lorsque vous interrogez des tables groupées dans BigQuery. Lorsqu'une requête filtre sur une ou plusieurs colonnes de clustering, BigQuery utilise les informations de clustering pour analyser uniquement les blocs pertinents. Ce processus est appelé élimination en bloc.

Ce document décrit les autorisations et les bonnes pratiques pour interroger des tables groupées, et explique pourquoi l'ordre des colonnes groupées dans vos filtres est important. Vous ne pouvez interroger les tables groupées qu'à l'aide de GoogleSQL.

Autorisations requises

Pour exécuter une tâche de requête, vous devez disposer de l'autorisation IAM (Identity and Access Management) bigquery.jobs.create pour le projet qui exécute la tâche de requête.

Chacun des rôles IAM prédéfinis suivants inclut les autorisations dont vous avez besoin pour exécuter une tâche de requête :

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

Vous devez également disposer de l'autorisation bigquery.tables.getData sur toutes les tables et vues auxquelles votre requête fait référence. Lorsque vous interrogez une vue, vous devez également disposer de cette autorisation sur toutes les tables et vues sous-jacentes. Toutefois, si vous utilisez des vues autorisées ou des ensembles de données autorisés, vous n'avez pas besoin d'accéder aux données sources sous-jacentes.

Chacun des rôles IAM prédéfinis suivants inclut l'autorisation dont vous avez besoin sur toutes les tables et vues auxquelles la requête fait référence :

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

Pour en savoir plus sur les rôles IAM dans BigQuery, consultez la page Rôles prédéfinis et autorisations.

Interroger une table groupée

Vous pouvez interroger les tables groupées :

  • Utiliser la console Google Cloud
  • En exécutant la commande bq query dans l'outil de ligne de commande bq
  • En appelant la méthode jobs.insert et en configurant une tâche de requête
  • En utilisant les bibliothèques clientes

Go

Avant d'essayer cet exemple, suivez les instructions de configuration pour Go du guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery pour Go.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes. 

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

Avant d'essayer cet exemple, suivez les instructions de configuration pour Java du guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery pour Java.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes. 

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

Avant d'essayer cet exemple, suivez les instructions de configuration pour Python du guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery pour Python.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes. 

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

Bonnes pratiques

Voici quelques exemples de bonnes pratiques visant à améliorer les performances des requêtes portant sur des tables groupées.

La table utilisée dans les exemples de bonnes pratiques est une table groupée créée via une instruction LDD. Cette instruction LDD crée une table nommée ClusteredSalesData. La table est mise en cluster en fonction des colonnes suivantes : customer_id, product_id, order_id, dans cet ordre de tri.

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

Filtrer les colonnes en cluster par ordre de tri

Lorsque vous spécifiez un filtre, utilisez des expressions qui filtrent sur les colonnes en cluster selon l'ordre spécifié. L'ordre de tri est l'ordre des colonnes indiqué dans la clause CLUSTER BY. Pour profiter des avantages du clustering, incluez une ou plusieurs colonnes en cluster dans un ordre de tri de gauche à droite, en commençant par la première colonne. Dans la plupart des cas, la première colonne de clustering est la plus efficace pour l'élagage de blocs, suivie de la deuxième colonne, puis de la troisième. Vous pouvez toujours utiliser la deuxième ou la troisième colonne seule dans la requête, mais l'élimination en bloc ne sera probablement pas aussi efficace. L'ordre des noms de colonnes dans l'expression de filtre n'affecte pas les performances.

L'exemple suivant interroge la table groupée ClusteredSalesData créée dans l'exemple précédent. La requête contient une expression de filtre portant sur la colonne customer_id, puis sur la colonne product_id. Cette requête optimise les performances en filtrant les colonnes en cluster selon l'ordre de tri spécifié, dans l'ordre des colonnes indiqué dans la clause CLUSTER BY.

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

La requête suivante ne filtre pas les colonnes groupées dans l'ordre de tri. Par conséquent, ses performances ne sont pas optimales. Cette requête filtre sur la colonne product_id, puis sur la colonne order_id (en ignorant la colonne customer_id).

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

Ne pas utiliser de colonnes en cluster dans des expressions de filtre complexes

Si vous utilisez une colonne en cluster dans une expression de filtre complexe, les performances de la requête ne sont pas optimisées dans la mesure où l'élimination en bloc ne peut pas être appliquée.

Par exemple, la requête suivante n'élimine pas de blocs, car la colonne en cluster customer_id est utilisée dans une fonction de l'expression de filtre.

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

Pour optimiser les performances des requêtes via l'élimination en bloc, utilisez des expressions de filtre simples comme celle qui suit. Dans cet exemple, un filtre simple est appliqué à la colonne groupée customer_id.

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

Ne pas comparer une colonne en cluster à une autre colonne

Si une expression de filtre compare une colonne en cluster à une autre colonne (que celle-ci soit en cluster ou non), les performances de la requête ne sont pas optimisées, car l'élimination en bloc ne peut pas être appliquée.

La requête suivante n'élimine pas de blocs, car l'expression de filtre compare la colonne groupée customer_id à une autre colonne (order_id).

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

Sécurité des tables

Pour savoir comment contrôler l'accès aux tables dans BigQuery, consultez Contrôler l'accès aux ressources avec IAM.

Étapes suivantes