Utiliser les optimisations basées sur l'historique

Preview

Ce produit ou cette fonctionnalité est soumis aux "Conditions des offres de pré-DG" dans la section "Conditions d'utilisation générales" des Conditions spécifiques au service. Les produits et fonctionnalités pré-DG sont disponibles "en l'état" et peuvent bénéficier d'une assistance limitée. Pour en savoir plus, consultez les descriptions des étapes de lancement.

Pour obtenir de l'aide pendant la version preview, envoyez un e-mail à [e-mail].

Ce guide explique comment activer, désactiver et analyser les optimisations basées sur l'historique pour les requêtes.

À propos des optimisations basées sur l'historique

Les optimisations basées sur l'historique utilisent les informations provenant d'exécutions déjà terminées de requêtes similaires, visant à appliquer des optimisations supplémentaires et à encore améliorer les performances des requêtes, telles que la durée d'utilisation des emplacements et la latence des requêtes. Par exemple, lorsque vous appliquez l'optimisation basée sur l'historique, la première exécution d'une requête peut nécessiter 60 secondes, mais la seconde exécution peut ne prendre que 30 secondes si une optimisation basée sur l'historique a été identifiée. Ce processus se poursuit jusqu'à ce qu'il n'y ait plus d'optimisations à ajouter.

Voici un exemple de fonctionnement des optimisations basées sur l'historique avec BigQuery :

Nombre d'exécutions	Durée d'utilisation des emplacements consommée pour la requête	Notes
1	60	Exécution d'origine.
2	30	Application de la première optimisation basée sur l'historique.
3	20	Application de la deuxième optimisation basée sur l'historique.
4	21	Aucune autre optimisation basée sur l'historique à appliquer.
5	19	Aucune autre optimisation basée sur l'historique à appliquer.
6	20	Aucune autre optimisation basée sur l'historique à appliquer.

Les optimisations basées sur l'historique ne sont appliquées que lorsqu'il est fort probable que cela ait un impact positif sur les performances des requêtes. En outre, lorsqu'une optimisation n'améliore pas de manière significative les performances des requêtes, elle est révoquée et n'est pas utilisée dans les exécutions futures de cette requête.

Activer les optimisations basées sur l'historique

Pour utiliser des optimisations basées sur l'historique dans un projet, incluez le paramètre suivant dans l'instruction ALTER PROJECT ou ALTER ORGANIZATION : default_query_optimizer_options = 'adaptive=on'

Exemple :

ALTER PROJECT `user_project`
SET OPTIONS (
  `region-us.default_query_optimizer_options` = 'adaptive=on'
);

Remarque : Une fois que vous avez activé les optimisations basées sur l'historique, vous pouvez ignorer sans risque le message d'avertissement suivant, qui s'affiche systématiquement pour n'importe quelle instruction ALTER PROJECT ou ALTER ORGANIZATION exécutée correctement : ALTER PROJECT succeeded. Please make sure no existing queries depend on the old defaults (such as the default time zone) or else these queries will be broken.

Désactiver les optimisations basées sur l'historique

Pour désactiver les optimisations basées sur l'historique dans un projet, incluez le paramètre default_query_optimizer_options = 'adaptive=off' dans l'instructions ALTER PROJECT ou ALTER ORGANIZATION.

Exemple :

ALTER PROJECT `user_project`
SET OPTIONS (
  `region-us.default_query_optimizer_options` = 'adaptive=off'
);

Remarque : Une fois que vous avez désactivé les optimisations basées sur l'historique, vous pouvez ignorer en toute sécurité le message d'avertissement suivant, qui s'affiche pour toute instruction ALTER PROJECT ou ALTER ORGANIZATION : ALTER PROJECT succeeded. Please make sure no existing queries depend on the old defaults (such as the default time zone) or else these queries will be broken.

Examiner les optimisations basées sur l'historique associées à un job

Pour examiner les optimisations basées sur l'historique associées à un job, vous pouvez utiliser une requête SQL ou un appel de méthode d'API REST.

SQL

Vous pouvez utiliser une requête pour obtenir les optimisations basées sur l'historique associées à un job. La requête doit inclure l'instruction INFORMATION_SCHEMA.JOBS_BY_PROJECT et le nom de colonne query_info.optimization_details.

L'exemple suivant renvoie les détails d'optimisation associés à un job nommé sample_job. Si aucune optimisation basée sur l'historique n'a été appliquée, la valeur NULL est générée pour optimization_details :

SELECT
  job_id,
  query_info.optimization_details
FROM `project_name.region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE job_id = 'sample_job'
LIMIT 1;

Les résultats ressemblent à ce qui suit :

-- The JSON in optimization_details has been formatted for readability.
/*------------+-----------------------------------------------------------------*
 | job_id     | optimization_details                                            |
 +------------+-----------------------------------------------------------------+
 | sample_job | {                                                               |
 |            |   "optimizations": [                                            |
 |            |     {                                                           |
 |            |       "semi_join_reduction": "web_sales.web_date,RIGHT"         |
 |            |     },                                                          |
 |            |     {                                                           |
 |            |       "semi_join_reduction": "catalog_sales.catalog_date,RIGHT" |
 |            |     },                                                          |
 |            |     {                                                           |
 |            |       "semi_join_reduction": "store_sales.store_date,RIGHT"     |
 |            |     },                                                          |
 |            |     {                                                           |
 |            |       "join_commutation": "web_returns.web_item"                |
 |            |     },                                                          |
 |            |     {                                                           |
 |            |       "parallelism_adjustment": "applied"                       |
 |            |     },                                                          |
 |            |   ]                                                             |
 |            | }                                                               |
 *------------+-----------------------------------------------------------------*/

API

Pour obtenir les détails d'optimisation associés à un job, vous pouvez appeler la méthode jobs.get.

Dans l'exemple suivant, la méthode jobs.get renvoie les détails d'optimisation (optimizationDetails) dans la réponse complète :

{
  "jobReference": {
    "projectId": "myProject",
    "jobId": "sample_job"
  }
}

Les résultats ressemblent à ce qui suit :

-- The unrelated parts in the full response have been removed.
{
  "jobReference": {
    "projectId": "myProject",
    "jobId": "sample_job",
    "location": "US"
  },
  "statistics": {
    "query": {
      "queryInfo": {
        "optimizationDetails": {
          "optimizations": [
            {
              "semi_join_reduction": "web_sales.web_date,RIGHT"
            },
            {
              "semi_join_reduction": "catalog_sales.catalog_date,RIGHT"
            },
            {
              "semi_join_reduction": "store_sales.store_date,RIGHT"
            },
            {
              "join_commutation": "web_returns.web_item"
            },
            {
              "parallelism_adjustment": "applied"
            }
          ]
        }
      }
    }
  }
}

Estimer l'impact des optimisations basées sur l'historique

Pour estimer l'impact des optimisations basées sur l'historique, vous pouvez utiliser l'exemple de requête SQL suivant afin d'identifier les requêtes de projet qui présentent l'amélioration de temps d'exécution estimée la plus grande.

  WITH
    jobs AS (
      SELECT
        *,
        query_info.query_hashes.normalized_literals AS query_hash,
        TIMESTAMP_DIFF(end_time, start_time, MILLISECOND) AS elapsed_ms,
        IFNULL(
          ARRAY_LENGTH(JSON_QUERY_ARRAY(query_info.optimization_details.optimizations)) > 0,
          FALSE)
          AS has_history_based_optimization,
      FROM region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT
      WHERE EXTRACT(DATE FROM creation_time) > DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    ),
    most_recent_jobs_without_history_based_optimizations AS (
      SELECT *
      FROM jobs
      WHERE NOT has_history_based_optimization
      QUALIFY ROW_NUMBER() OVER (PARTITION BY query_hash ORDER BY end_time DESC) = 1
    )
  SELECT
    job.job_id,
    SAFE_DIVIDE(
      original_job.elapsed_ms - job.elapsed_ms,
      original_job.elapsed_ms) AS percent_execution_time_saved,
    job.elapsed_ms AS new_elapsed_ms,
    original_job.elapsed_ms AS original_elapsed_ms,
  FROM jobs AS job
  INNER JOIN most_recent_jobs_without_history_based_optimizations AS original_job
    USING (query_hash)
  WHERE
    job.has_history_based_optimization
    AND original_job.end_time < job.start_time
  ORDER BY percent_execution_time_saved DESC
  LIMIT 10;

Le résultat de la requête précédente est semblable à celui-ci si des optimisations basées sur l'historique ont été appliquées:

  /*--------------+------------------------------+------------------+-----------------------*
   |    job_id    | percent_execution_time_saved | new_execution_ms | original_execution_ms |
   +--------------+------------------------------+------------------+-----------------------+
   | sample_job1  |           0.6780685018624512 |             7087 |                 22014 |
   | sample_job2  |           0.6648580041250198 |            10562 |                 31515 |
   | sample_job3  |          0.63285605271764256 |            97668 |                266021 |
   | sample_job4  |            0.611341417268879 |           923384 |               2375823 |
   | sample_job5  |           0.5538127208971375 |          1060062 |               2375823 |
   | sample_job6  |           0.4539694316803648 |          2324071 |               4256302 |
   | sample_job7  |          0.38227031526376026 |            17811 |                 28833 |
   | sample_job8  |          0.33826608962725113 |            66360 |                100282 |
   | sample_job9  |          0.32087813758311606 |            44020 |                 64819 |
   | sample_job10 |           0.2835641631948354 |            19088 |                 26643 |
   *--------------+------------------------------+------------------+-----------------------*/

Détails

• Il ne s'agit que d'une estimation de l'impact de l'optimisation basé sur l'historique. De nombreux facteurs peuvent influencer les performances des requêtes, y compris, mais sans s'y limiter, la disponibilité des emplacements, l'évolution des données au fil du temps, les différences dans les valeurs des paramètres de requête, et les définitions de vues ou de fonctions définies par l'utilisateur.
• Cette requête peut être appliquée à d'autres métriques de performances des requêtes, telles que total_slot_ms et total_bytes_billed. Pour en savoir plus, consultez le schéma INFORMATION_SCHEMA.JOBS.
• Si le résultat de cet exemple de requête est vide, cela signifie qu'aucune tâche n'a utilisé les optimisations basées sur l'historique ou que toutes les requêtes ont été optimisées il y a plus de 30 jours.

Rôles et autorisations

• Pour activer les optimisations basées sur l'historique, vous devez disposer des autorisations requises pour créer des configurations BigQuery par défaut. Vous devez ensuite utiliser l'instruction ALTER PROJECT pour activer les optimisations basées sur l'historique. Une fois que vous avez activé les optimisations basées sur l'historique, tous les jobs de ce projet vont utiliser des optimisations basées sur l'historique, quel que soit l'utilisateur qui les a créés. Pour en savoir plus sur les autorisations requises pour les configurations par défaut, consultez la section Autorisations requises pour les configurations par défaut. Pour activer les optimisations basées sur l'historique, consultez la section Activer les optimisations basées sur l'historique.

• Pour examiner les optimisations basées sur l'historique associées à un job à l'aide de la vue INFORMATION_SCHEMA.JOBS, vous devez disposer du rôle requis. Pour en savoir plus, consultez la section Rôle requis pour la vue INFORMATION_SCHEMA.JOBS.