Utilizza ottimizzazioni basate sulla cronologia

Questa guida descrive come attivare, disattivare 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 ulteriori ottimizzazioni e migliorare ulteriormente le prestazioni delle query, come il tempo di slot utilizzato e la latenza delle query. Ad esempio, quando applichi l'ottimizzazione in base alla 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 in base alla cronologia. Il processo continua finché non ci sono ulteriori ottimizzazioni da aggiungere.

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

Conteggio esecuzioni	Tempo utilizzato per lo slot della query	Note
1	60	Esecuzione originale.
2	30	Prima ottimizzazione basata sulla cronologia applicata.
3	20	È stata applicata la seconda ottimizzazione basata sulla cronologia.
4	21	Nessuna ulteriore ottimizzazione basata sulla cronologia da applicare.
5	19	Non sono necessarie ulteriori ottimizzazioni basate sulla cronologia.
6	20	Non sono necessarie ulteriori ottimizzazioni basate sulla cronologia.

Le ottimizzazioni basate sulla cronologia vengono applicate solo quando c'è un'alta probabilità che ci sarà un impatto positivo sulle prestazioni delle query. Inoltre, quando l'ottimizzazione non migliora significativamente le prestazioni delle query, ma l'ottimizzazione viene revocato e non viene utilizzato nelle esecuzioni future di tale query.

Ruoli e autorizzazioni

• Per attivare o disattivare le ottimizzazioni basate sulla cronologia, devi disporre del autorizzazioni necessarie per creare BigQuery configurazioni predefinite, dopodiché dovrai usare l'istruzione ALTER PROJECT per abilitare le ottimizzazioni basate sulla cronologia. Una volta attivate le ottimizzazioni basate sulla cronologia, tutte le attività del progetto le utilizzeranno, indipendentemente dall'utente che le ha create. Per saperne di più sulle autorizzazioni necessarie per le configurazioni predefinite, consulta Autorizzazioni obbligatorie per le configurazioni predefinite. Per attivare le ottimizzazioni basate sulla cronologia, consulta: Attiva le ottimizzazioni basate sulla cronologia.

• Per rivedere le ottimizzazioni basate sulla cronologia per un job utilizzando il INFORMATION_SCHEMA.JOBS, devi disporre del ruolo richiesto. Per ulteriori informazioni, consulta la sezione Ruolo richiesto per la visualizzazione INFORMATION_SCHEMA.JOBS.

Abilita ottimizzazioni basate sulla cronologia

Per utilizzare le ottimizzazioni basate sulla cronologia in un progetto, includi il seguente parametro nella istruzione ALTER PROJECT o ALTER ORGANIZATION:default_query_optimizer_options = 'adaptive=on'. 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à del progetto

Nota: dopo aver abilitato le ottimizzazioni basate sulla cronologia, puoi tranquillamente ignorare il a seguito di un messaggio di avviso, che viene mostrato per i ALTER PROJECT o 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.

Disattivare le ottimizzazioni basate sulla cronologia

Per disattivare le ottimizzazioni basate sulla cronologia in un progetto, includi il parametro default_query_optimizer_options = 'adaptive=off' nell'istruzione ALTER PROJECT o ALTER ORGANIZATION. 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 posizione del progetto

Nota: dopo aver disabilitato le ottimizzazioni basate sulla cronologia, puoi tranquillamente ignorare il a seguito di un messaggio di avviso, che viene mostrato per i ALTER PROJECT o 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.

Esaminare le ottimizzazioni basate sulla cronologia di un job

Per rivedere le ottimizzazioni basate sulla cronologia per un job, puoi usare 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, i dettagli dell'ottimizzazione vengono restituiti per un job chiamato sample_job. Se non sono state applicate ottimizzazioni basate sulla cronologia, NULL viene prodotto 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 di ottimizzazione per un job, puoi chiamare il metodo Metodo jobs.get.

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 seguente query SQL di esempio per identificare le query di progetto con il miglioramento stimato in termini di 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,
    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  |           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 |
   *--------------+------------------------------+------------------+-----------------------*/

I risultati di questa query sono solo una stima dell'impatto dell'ottimizzazione basata sulla cronologia. Molti fattori possono influire sul rendimento delle query, tra cui, a titolo esemplificativo, la disponibilità degli slot, la variazione dei dati nel tempo, le definizioni di visualizzazioni o funzioni definite dall'utente e le differenze nei valori dei parametri di query.

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

Questa query può essere applicata ad altre metriche sul rendimento delle query, come total_slot_ms e total_bytes_billed. Per ulteriori informazioni, consulta lo schema per INFORMATION_SCHEMA.JOBS.