Interroger des tables en cluster

Interroger des tables en cluster

Lorsque vous créez une table en cluster 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 en cluster, l'expression que vous utilisez doit filtrer sur une plusieurs colonnes en cluster 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 en cluster selon les valeurs figurant dans les colonnes de clustering, et organise ces données en blocs.

Lorsque vous envoyez une requête contenant un filtre sur une colonne en cluster, 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 Cloud Console ou l'UI Web classique de BigQuery ;
  • En utilisant la commande bq query de l'outil de ligne de commande
  • En appelant la méthode API jobs.insert et en configurant une tâche de requête
  • En utilisant les bibliothèques clientes

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

Java

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Java décrite dans le 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 en langage Java.

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

Autorisations requises

Pour interroger une table, vous devez au minimum disposer des autorisations bigquery.tables.getData.

Les rôles Cloud IAM prédéfinis suivants incluent les autorisations bigquery.tables.getData :

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

En outre, si un utilisateur possède les autorisations bigquery.datasets.create, il obtient également un accès bigquery.dataOwner à l'ensemble de données qu'il crée. L'accès bigquery.dataOwner permet à l'utilisateur d'interroger des tables et des vues de l'ensemble de données.

Pour exécuter des tâches de requête, vous devez également disposer des autorisations bigquery.jobs.create. Les rôles Cloud IAM prédéfinis suivants incluent les autorisations bigquery.jobs.create :

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

Pour en savoir plus sur les rôles et les autorisations IAM dans BigQuery, consultez la page Contrôle des accès.

Bonnes pratiques

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

Description de la table utilisée dans les exemples

La table utilisée dans les exemples de cette page est une table en cluster créée via une instruction LDD. Cette instruction LDD crée une table nommée ClusteredSalesData, laquelle est mise en cluster d'après la colonne customer_id, puis la colonne product_id et, enfin, la colonne order_id.

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 dans l'ordre où elles ont été spécifiées

Lorsque vous spécifiez un filtre, utilisez des expressions qui filtrent sur les colonnes en cluster selon l'ordre spécifié.

La requête suivante 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 sur les colonnes en cluster dans l'ordre où elles ont été spécifiées.

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 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 en cluster 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 en cluster customer_id à une autre colonne (order_id).

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

Étapes suivantes