Utilizzare le ottimizzazioni basate sulla cronologia

Questa guida descrive come abilitare, disabilitare e analizzare le ottimizzazioni basate sulla cronologia per le query.

Informazioni sulle ottimizzazioni basate sulla cronologia

Le ottimizzazioni basate sulla cronologia utilizzano le informazioni delle esecuzioni già completate di query simili per applicare ottimizzazioni aggiuntive e migliorare ulteriormente le prestazioni delle query, ad esempio il tempo di slot utilizzato e la latenza delle query. Ad esempio, quando applichi l'ottimizzazione basata sulla cronologia, la prima esecuzione della query potrebbe richiedere 60 secondi, ma la seconda esecuzione della query potrebbe richiedere solo 30 secondi se è stata identificata un'ottimizzazione basata sulla cronologia. Questo processo continua finché non vengono aggiunte altre ottimizzazioni.

Di seguito è riportato un esempio di come funzionano le ottimizzazioni basate sulla cronologia con BigQuery:

Conteggio esecuzioni	Tempo di slot della query utilizzato	Note
1	60	Esecuzione originale.
2	30	È stata applicata la prima ottimizzazione basata sulla cronologia.
3	20	È stata applicata la seconda ottimizzazione basata sulla cronologia.
4	21	Non sono disponibili altre ottimizzazioni basate sulla cronologia da applicare.
5	19	Non sono disponibili altre ottimizzazioni basate sulla cronologia da applicare.
6	20	Non sono disponibili altre ottimizzazioni basate sulla cronologia da applicare.

Le ottimizzazioni basate sulla cronologia vengono applicate solo quando è ragionevolmente certo che avranno un impatto positivo sulle prestazioni delle query. Inoltre, quando un'ottimizzazione non migliora in modo significativo le prestazioni delle query, viene revocata e non viene utilizzata nelle esecuzioni future della query.

Ruoli e autorizzazioni

• Per abilitare o disabilitare le ottimizzazioni basate sulla cronologia, devi disporre delle autorizzazioni necessarie per creare le configurazioni predefinite di BigQuery e poi utilizzare l'istruzione ALTER PROJECT per abilitare le ottimizzazioni basate sulla cronologia. Una volta abilitate le ottimizzazioni basate sulla cronologia, tutti i job nel progetto le utilizzano, indipendentemente dall'utente che ha creato il job. Per saperne di più sui ruoli e sulle autorizzazioni richiesti per le configurazioni predefinite, consulta Ruoli richiesti per le configurazioni predefinite. Per abilitare le ottimizzazioni basate sulla cronologia, consulta Abilitare le ottimizzazioni basate sulla cronologia.

• Per esaminare le ottimizzazioni basate sulla cronologia per un job utilizzando la vista INFORMATION_SCHEMA.JOBS, devi disporre del ruolo richiesto. Per saperne di più, consulta Ruolo richiesto per la vista INFORMATION_SCHEMA.JOBS.

Abilitare le ottimizzazioni basate sulla cronologia

Le ottimizzazioni basate sulla cronologia sono abilitate per impostazione predefinita. Se le ottimizzazioni basate sulla cronologia sono state disabilitate per un progetto o un'organizzazione, puoi riattivarle manualmente includendo il default_query_optimizer_options = 'adaptive=on' parametro nell'istruzione ALTER PROJECT o ALTER ORGANIZATION. Ad esempio:

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

Sostituisci quanto segue:

• PROJECT_NAME: il nome del progetto
• LOCATION: la località in cui i job devono tentare di utilizzare le ottimizzazioni basate sulla cronologia

Nota: dopo aver abilitato le ottimizzazioni basate sulla cronologia, puoi ignorare in sicurezza il seguente messaggio di avviso, che viene visualizzato per qualsiasi istruzione ALTER PROJECT o ALTER ORGANIZATION riuscita: 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.

Disabilitare le ottimizzazioni basate sulla cronologia

Per disabilitare le ottimizzazioni basate sulla cronologia in un progetto, includi il default_query_optimizer_options = 'adaptive=off' parametro nell' ALTER PROJECT o ALTER ORGANIZATION istruzione. Ad esempio:

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

Sostituisci quanto segue:

• PROJECT_NAME: il nome del progetto
• LOCATION: la località in cui i job non devono tentare di utilizzare le ottimizzazioni basate sulla cronologia

Nota: dopo aver disabilitato le ottimizzazioni basate sulla cronologia, puoi ignorare in sicurezza il seguente messaggio di avviso, che viene visualizzato per qualsiasi istruzione ALTER PROJECT o ALTER ORGANIZATION riuscita: 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.

Esaminare le ottimizzazioni basate sulla cronologia per un job

Per esaminare le ottimizzazioni basate sulla cronologia per un job, puoi utilizzare una query SQL o una chiamata al metodo API REST.

SQL

Puoi utilizzare una query per ottenere le ottimizzazioni basate sulla cronologia per un job. La query deve includere INFORMATION_SCHEMA.JOBS_BY_PROJECT e il nome della colonna query_info.optimization_details.

Nell'esempio seguente, vengono restititi i dettagli dell'ottimizzazione per un job denominato sample_job. Se non sono state applicate ottimizzazioni basate sulla cronologia, viene generato NULL per optimization_details:

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

I risultati sono simili ai seguenti:

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

Per ottenere i dettagli dell'ottimizzazione per un job, puoi chiamare il jobs.get metodo.

Nell'esempio seguente, il metodo jobs.get restituisce i dettagli dell'ottimizzazione (optimizationDetails) nella risposta completa:

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

I risultati sono simili ai seguenti:

-- 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"
            }
          ]
        }
      }
    }
  }
}

Stimare l'impatto delle ottimizzazioni basate sulla cronologia

Per stimare l'impatto delle ottimizzazioni basate sulla cronologia, puoi utilizzare la seguente query SQL di esempio per identificare le query del progetto con il miglioramento stimato maggiore del tempo di esecuzione.

  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-LOCATION.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,
    100 * 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;

Il risultato della query precedente è simile al seguente se sono state applicate ottimizzazioni basate sulla cronologia:

  /*--------------+------------------------------+------------------+-----------------------*
   |    job_id    | percent_execution_time_saved | new_execution_ms | original_execution_ms |
   +--------------+------------------------------+------------------+-----------------------+
   | sample_job1  |           67.806850186245114 |             7087 |                 22014 |
   | sample_job2  |           66.485800412501987 |            10562 |                 31515 |
   | sample_job3  |           63.285605271764254 |            97668 |                266021 |
   | sample_job4  |           61.134141726887904 |           923384 |               2375823 |
   | sample_job5  |           55.381272089713754 |          1060062 |               2375823 |
   | sample_job6  |           45.396943168036479 |          2324071 |               4256302 |
   | sample_job7  |           38.227031526376024 |            17811 |                 28833 |
   | sample_job8  |           33.826608962725111 |            66360 |                100282 |
   | sample_job9  |           32.087813758311604 |            44020 |                 64819 |
   | sample_job10 |           28.356416319483539 |            19088 |                 26643 |
   *--------------+------------------------------+------------------+-----------------------*/

I risultati di questa query sono solo una stima dell'impatto dell'ottimizzazione basata sulla cronologia. Molti fattori possono influenzare le prestazioni delle query, tra cui, a titolo esemplificativo, la disponibilità degli slot, la modifica dei dati nel tempo, le definizioni di viste o UDF e le differenze nei valori dei parametri delle query.

Se il risultato di questa query di esempio è vuoto, significa che nessun job ha utilizzato le ottimizzazioni basate sulla cronologia o che tutte le query sono state ottimizzate più di 30 giorni fa.

Questa query può essere applicata ad altre metriche delle prestazioni delle query, come total_slot_ms e total_bytes_billed. Per saperne di più, consulta lo schema di INFORMATION_SCHEMA.JOBS.