Contrôler les coûts dans BigQuery

Vous trouverez sur cette page la description des bonnes pratiques relatives au contrôle des coûts dans BigQuery.

BigQuery propose deux modèles de tarification pour l'exécution des requêtes :

Si vous utilisez la tarification à la demande, vous pouvez directement réduire vos coûts en réduisant le nombre d'octets traités par une requête. Avec la tarification forfaitaire, votre coût est fixe en fonction du nombre d'emplacements que vous achetez et du forfait d'engagement d'emplacements que vous sélectionnez. Toutefois, l'optimisation de vos requêtes peut contribuer à réduire l'utilisation des emplacements.

Éviter la requête SELECT *

Bonne pratique : Interrogez uniquement les colonnes dont vous avez besoin.

L'utilisation de la requête SELECT * est le moyen le plus coûteux d'interroger des données. Lorsque vous utilisez SELECT *, BigQuery effectue une analyse complète de chaque colonne de la table.

Si vous testez ou explorez les données, utilisez l'une des options d'aperçu de données plutôt que SELECT *.

L'application d'une clause LIMIT à une requête SELECT * n'a aucune incidence sur la quantité de données lues. La lecture de tous les octets de la table vous est facturée, et la requête est comptabilisée dans votre quota de niveau gratuit.

Nous recommandons plutôt d'interroger uniquement les colonnes dont vous avez besoin. Par exemple, utilisez SELECT * EXCEPT pour exclure une ou plusieurs colonnes des résultats.

Si vous devez effectuer des requêtes sur toutes les colonnes d'une table, mais seulement sur un sous-ensemble de données, prenez en compte les points suivants :

  • Matérialisez les résultats dans une table de destination et interrogez plutôt cette table.
  • Partitionnez vos tables par date, puis interrogez la partition concernée. Par exemple, WHERE _PARTITIONDATE="2017-01-01" n'analyse que la partition du 1er janvier 2017.

Échantillonner des données à l'aide des options d'aperçu

Bonne pratique : N'exécutez pas de requêtes pour explorer ou prévisualiser les données d'une table.

Si vous testez ou explorez vos données, vous pouvez afficher les données gratuitement sans affecter les quotas à l'aide des options d'aperçu de la table.

Vous trouverez ci-dessous les options d'aperçu de données disponibles dans BigQuery :

  • Dans Cloud Console, sur la page "Détails de la table", cliquez sur l'onglet Aperçu pour échantillonner les données.
  • Dans l'outil de ligne de commande bq, exécutez la commande bq head et spécifiez le nombre de lignes à prévisualiser.
  • Dans l'API, récupérez les données de la table à partir d'un ensemble de lignes donné à l'aide de tabledata.list.

Déterminer le prix de vos requêtes avant de les exécuter

Bonne pratique : Avant d'exécuter des requêtes, prévisualisez-les pour estimer les coûts.

Les requêtes sont facturées en fonction du nombre d'octets lus. Pour estimer les coûts avant d'exécuter une requête, vous disposez des options suivantes :

  • Utiliser l'outil de validation des requêtes dans Cloud Console
  • Utiliser le simulateur de coût Google Cloud Platform
  • Effectuer une simulation en spécifiant les éléments suivants :
    • Option --dry_run dans l'outil de ligne de commande bq
    • Paramètre dryRun lors de l'envoi d'une tâche de requête à l'aide de l'API

Utiliser l'outil de validation des requêtes

Lorsque vous saisissez une requête dans Cloud Console, l'outil de validation des requêtes valide sa syntaxe et fournit une estimation du nombre d'octets lus. Vous pouvez vous servir de cette estimation pour calculer le coût de la requête dans le simulateur de coût.

  • Si votre requête n'est pas valide, l'outil de validation des requêtes affiche un message d'erreur. Exemple :

    Not found: Table myProject:myDataset.myTable was not found in location US

  • Si votre requête est valide, l'outil de validation de requête fournit une estimation du nombre d'octets nécessaires pour traiter la requête. Exemple :

    This query will process 623.1 KiB when run.

Effectuer une simulation

Pour effectuer une simulation, procédez comme suit :

Console

  1. Accédez à la page BigQuery de Google Cloud Console.

    Accéder à BigQuery

  2. Saisissez votre requête dans l'Éditeur de requête.

    Si la requête est valide, une coche apparaît automatiquement avec la quantité de données que la requête va traiter. Si la requête n'est pas valide, un point d'exclamation apparaît avec un message d'erreur.

bq

Saisissez une requête semblable à celle-ci à l'aide de l'option --dry_run.

bq query \
--use_legacy_sql=false \
--dry_run \
'SELECT
   COUNTRY,
   AIRPORT,
   IATA
 FROM
   `project_id`.dataset.airports
 LIMIT
   1000'
 

La commande produit la réponse suivante :

Query successfully validated. Assuming the tables are not modified,
running this query will process 10918 bytes of data.

API

Pour effectuer une simulation avec l'API, envoyez une tâche de requête avec la valeur dryRun définie sur true dans le type JobConfiguration.

Go

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Go 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 sur l'API BigQuery en langage Go.

import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/bigquery"
)

// queryDryRun demonstrates issuing a dry run query to validate query structure and
// provide an estimate of the bytes scanned.
func queryDryRun(w io.Writer, projectID string) error {
	// projectID := "my-project-id"
	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(`
	SELECT
		name,
		COUNT(*) as name_count
	FROM ` + "`bigquery-public-data.usa_names.usa_1910_2013`" + `
	WHERE state = 'WA'
	GROUP BY name`)
	q.DryRun = true
	// Location must match that of the dataset(s) referenced in the query.
	q.Location = "US"

	job, err := q.Run(ctx)
	if err != nil {
		return err
	}
	// Dry run is not asynchronous, so get the latest status and statistics.
	status := job.LastStatus()
	if err := status.Err(); err != nil {
		return err
	}
	fmt.Fprintf(w, "This query will process %d bytes\n", status.Statistics.TotalBytesProcessed)
	return nil
}

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 Java.

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.JobStatistics;
import com.google.cloud.bigquery.QueryJobConfiguration;

// Sample to run dry query on the table
public class QueryDryRun {

  public static void runQueryDryRun() {
    String query =
        "SELECT name, COUNT(*) as name_count "
            + "FROM `bigquery-public-data.usa_names.usa_1910_2013` "
            + "WHERE state = 'WA' "
            + "GROUP BY name";
    queryDryRun(query);
  }

  public static void queryDryRun(String query) {
    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();

      QueryJobConfiguration queryConfig =
          QueryJobConfiguration.newBuilder(query).setDryRun(true).setUseQueryCache(false).build();

      Job job = bigquery.create(JobInfo.of(queryConfig));
      JobStatistics.QueryStatistics statistics = job.getStatistics();

      System.out.println(
          "Query dry run performed successfully." + statistics.getTotalBytesProcessed());
    } catch (BigQueryException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

Node.js

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Node.js 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 Node.js.

// Import the Google Cloud client library
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function queryDryRun() {
  // Runs a dry query of the U.S. given names dataset for the state of Texas.

  const query = `SELECT name
    FROM \`bigquery-public-data.usa_names.usa_1910_2013\`
    WHERE state = 'TX'
    LIMIT 100`;

  // For all options, see https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query
  const options = {
    query: query,
    // Location must match that of the dataset(s) referenced in the query.
    location: 'US',
    dryRun: true,
  };

  // Run the query as a job
  const [job] = await bigquery.createQueryJob(options);

  // Print the status and statistics
  console.log('Status:');
  console.log(job.metadata.status);
  console.log('\nJob Statistics:');
  console.log(job.metadata.statistics);
}

Python

Pour effectuer une simulation avec la bibliothèque cliente Python, définissez la propriété QueryJobConfig.dry_run sur True. La méthode Client.query() renvoie toujours une tâche QueryJob terminée lorsque vous lui transmettez une configuration de requête simulée.

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Python 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 Python.

from google.cloud import bigquery

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

job_config = bigquery.QueryJobConfig(dry_run=True, use_query_cache=False)

# Start the query, passing in the extra configuration.
query_job = client.query(
    (
        "SELECT name, COUNT(*) as name_count "
        "FROM `bigquery-public-data.usa_names.usa_1910_2013` "
        "WHERE state = 'WA' "
        "GROUP BY name"
    ),
    job_config=job_config,
)  # Make an API request.

# A dry run query completes immediately.
print("This query will process {} bytes.".format(query_job.total_bytes_processed))

Utiliser le simulateur de coût

Pour estimer les coûts des requêtes dans le simulateur de coût Google Cloud Platform, saisissez le nombre d'octets traités par la requête en Mo, Go, To ou Po. Si votre requête traite moins d'un téraoctet de données, l'estimation est de 0 $, car BigQuery assure gratuitement le traitement d'un téraoctet de requêtes à la demande par mois.

Simulateur de coût.

Restreindre les coûts des requêtes en limitant le nombre d'octets facturés

Bonne pratique : Limitez les coûts des requêtes à l'aide du paramètre "Nombre maximal d'octets facturés".

Vous pouvez limiter le nombre d'octets facturés pour une requête en utilisant le paramètre "Nombre maximal d'octets facturés". Lorsque vous définissez le nombre maximal d'octets facturés, le nombre d'octets lus par la requête est estimé avant l'exécution de la requête. Si le nombre d'octets estimés dépasse la limite, la requête échoue sans engendrer de frais.

Pour les tables en cluster, l'estimation du nombre d'octets facturés pour une requête correspond à la limite supérieure et peut être supérieure au nombre réel d'octets facturés après l'exécution de la requête. Dans certains cas, si vous définissez le nombre maximal d'octets facturés, une requête sur une table en cluster peut échouer, même si le nombre d'octets réellement facturé ne dépasse pas le paramètre d'octets facturés maximal.

Si une requête échoue en raison du paramètre "Nombre maximal d'octets facturés", une erreur est renvoyée sous la forme suivante :

Error: Query exceeded limit for bytes billed: 1000000. 10485760 or higher required.

Pour définir le nombre maximal d'octets facturés, procédez comme suit :

Console

  1. Dans l'éditeur de requête, cliquez sur Plus, puis sur Paramètres de requête, et enfin sur Options avancées.
  2. Dans le champ Nombre maximal d'octets facturés, saisissez un nombre entier.
  3. Cliquez sur Enregistrer.

bq

Exécutez la commande bq query avec l'option --maximum_bytes_billed.

  bq query --maximum_bytes_billed=1000000 \
  --use_legacy_sql=false \
  'SELECT
     word
   FROM
     `bigquery-public-data`.samples.shakespeare'

API

Définissez la propriété maximumBytesBilled dans JobConfigurationQuery ou QueryRequest.

Utiliser des tables en cluster ou partitionnées

Bonne pratique : Utilisez le clustering et le partitionnement pour réduire la quantité de données analysées.

Le clustering et le partitionnement permettent de réduire la quantité de données traitées par les requêtes. Pour limiter le nombre de partitions analysées lors de l'interrogation de tables en cluster ou partitionnées, utilisez un filtre de prédicat.

Lorsque vous exécutez une requête sur une table en cluster et qu'elle inclut un filtre sur les colonnes en cluster, BigQuery utilise l'expression de filtre et les métadonnées de bloc pour restreindre le nombre de blocs analysés par la requête. Pour en savoir plus, consultez la page Interroger des tables en cluster.

Lors de l'interrogation de tables partitionnées, des filtres sur la colonne de partitionnement sont utilisés pour restreindre les partitions et peuvent donc réduire le coût de la requête. Pour en savoir plus, consultez la page Interroger des tables partitionnées.

Ne pas utiliser LIMIT pour contrôler les coûts dans les tables hors cluster

Bonne pratique : Pour les tables hors cluster, n'utilisez pas de clause LIMIT comme méthode de contrôle des coûts.

Pour les tables hors cluster, l'application d'une clause LIMIT à une requête n'a aucune incidence sur la quantité de données lues. Vous êtes facturé pour la lecture de tous les octets de la table, comme indiqué par la requête, même si celle-ci ne renvoie qu'un sous-ensemble. Avec une table en cluster, une clause LIMIT peut réduire le nombre d'octets analysés.

Afficher les coûts à l'aide d'un tableau de bord et interroger les journaux d'audit

Bonne pratique : Créez un tableau de bord pour afficher vos données de facturation afin de pouvoir adapter votre utilisation de BigQuery. Pensez également à diffuser vos journaux d'audit vers BigQuery afin d'analyser les modèles d'utilisation.

Vous pouvez exporter vos données de facturation dans BigQuery et les visualiser dans un outil comme Google Data Studio. Pour suivre un tutoriel sur la création d'un tableau de bord de facturation, consultez l'article intitulé Visualize GCP Billing using BigQuery and Data Studio.

Vous pouvez également diffuser vos journaux d'audit vers BigQuery et les analyser pour rechercher des motifs d'utilisation, par exemple les coûts des requêtes par utilisateur.

Partitionner les données par date

Bonne pratique : Partitionnez vos tables par date.

Si possible, partitionnez vos tables BigQuery par date. Le partitionnement de vos tables vous permet d'interroger des sous-ensembles de données pertinents, ce qui améliore les performances et réduit les coûts.

Par exemple, lorsque vous interrogez des tables partitionnées, filtrez une date ou une plage de dates à l'aide de la pseudo-colonne _PARTITIONTIME. La requête ne traite que les données des partitions spécifiées par la date ou la plage.

Matérialiser les résultats d'une requête par étapes

Bonne pratique : Matérialisez les résultats de votre requête par étapes, si possible.

Si vous créez une requête volumineuse à plusieurs étapes, BigQuery lit toutes les données requises par la requête chaque fois que vous l'exécutez. Toutes les données lues à chaque exécution de la requête vous sont facturées.

Divisez plutôt votre requête en étapes, où chacune d'elles matérialise les résultats de la requête en les écrivant dans une table de destination. Interroger la plus petite table de destination vous permet de diminuer la quantité de données lues et de réduire les frais. Le coût de stockage des résultats matérialisés est bien inférieur au coût de traitement de grandes quantités de données.

Prendre en compte le coût des grands ensembles de résultats

Bonne pratique : Si vous écrivez les résultats d'une requête volumineuse dans une table de destination, servez-vous du délai d'expiration de la table par défaut pour supprimer les données lorsque vous n'en avez plus besoin.

Conserver de grands ensembles de résultats dans l'espace de stockage BigQuery a un coût. Si vous n'avez pas besoin d'un accès permanent aux résultats, supprimez automatiquement les données selon le délai d'expiration de table défini par défaut.

Pour en savoir plus, consultez les tarifs de stockage.