Interroger des tables en cluster

Lorsque vous créez une table groupée dans BigQuery, les données de cette table sont automatiquement organisées en fonction du contenu d'une ou de plusieurs colonnes de son schéma de table. Les colonnes que vous spécifiez sont utilisées pour rapprocher les données associées. Lorsque vous mettez une table en cluster à l'aide de plusieurs colonnes, l'ordre des colonnes que vous spécifiez est important. L'ordre des colonnes spécifiées détermine l'ordre de tri des données.

Afin d'optimiser les performances des requêtes exécutées sur des tables groupées, l'expression que vous utilisez doit filtrer une ou plusieurs colonnes groupées dans l'ordre où elles ont été spécifiées. Les requêtes qui filtrent sur des colonnes en cluster fonctionnent généralement mieux que celles qui ne filtrent que sur des colonnes hors cluster.

BigQuery trie les données d'une table groupée en fonction des valeurs des colonnes de clustering, puis les organise en blocs.

Lorsque vous envoyez une requête contenant un filtre sur une colonne groupée, BigQuery utilise les informations de clustering pour déterminer efficacement si un bloc contient des données pertinentes pour la requête. Ce processus, appelé élimination en bloc, permet à BigQuery d'analyser uniquement les blocs pertinents.

Vous pouvez interroger les tables en cluster :

• En utilisant la console Google Cloud
• En exécutant la commande bq query de 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

Actuellement, vous ne pouvez utiliser que le GoogleSQL avec les tables en cluster.

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

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.

Bonnes pratiques

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

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 toutes les colonnes en cluster ou un sous-ensemble de colonnes dans un ordre de tri de gauche à droite, en commençant par la première colonne. Par exemple, si l'ordre de tri de la colonne est A, B, C, une requête qui filtre A et B peut être bénéfique pour le clustering, mais une requête qui filtre B et C ne le sera pas. 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 sur les colonnes en cluster dans l'ordre spécifié. 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 groupée 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 groupée à une autre colonne (que celle-ci soit groupée 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 la page Présentation des contrôles d'accès aux tables.

Étapes suivantes

• Pour en savoir plus sur l'exécution de requêtes, consultez la page Exécuter des requêtes interactives et par lot.
• Pour découvrir comment créer et utiliser des tables en cluster, consultez la page Créer et utiliser des tables en cluster.
• Pour une présentation de la compatibilité des tables partitionnées dans BigQuery, consultez la page Présentation des tables partitionnées.
• Pour apprendre à créer des tables partitionnées, consultez la page Créer des tables partitionnées.