Usar otimizações baseadas no histórico

Neste guia, descrevemos como ativar, desativar e analisar otimizações baseadas em histórico para consultas.

Sobre as otimizações baseadas no histórico

As otimizações com base em histórico usam informações de execuções já concluídas de consultas semelhantes para aplicar outras otimizações e melhorar ainda mais o desempenho da consulta, como o tempo de slot consumido e a latência da consulta. Por exemplo, quando você aplica a otimização baseada em histórico, a primeira execução de consulta pode levar 60 segundos, mas a segunda execução pode levar apenas 30 segundos se uma otimização baseada em histórico for identificada. Esse processo continua até que não haja outras otimizações a serem adicionadas.

Confira a seguir um exemplo de como as otimizações baseadas no histórico funcionam com o BigQuery:

Contagem de execução	Tempo do slot de consulta consumido	Observações
1	60	Execução original.
2	30	Primeira otimização baseada no histórico aplicada.
3	20	Segunda otimização baseada no histórico aplicada.
4	21	Não há outras otimizações com base no histórico para aplicar.
5	19	Não há outras otimizações com base no histórico para aplicar.
6	20	Não há outras otimizações com base no histórico para aplicar.

As otimizações com base no histórico são aplicadas somente quando há alta confiança de que haverá um impacto benéfico no desempenho da consulta. Além disso, quando uma otimização não melhora significativamente o desempenho da consulta, ela é revogada e não é usada em execuções futuras dessa consulta.

Papéis e permissões

• Para ativar ou desativar as otimizações baseadas em histórico, é necessário ter as permissões necessárias para criar as configurações padrão do BigQuery e usar a instrução ALTER PROJECT para ativar essas otimizações. Depois de ativar as otimizações baseadas em histórico, todos os jobs desse projeto usarão essas otimizações, independentemente de qual usuário criou o job. Para saber mais sobre as permissões necessárias para as configurações padrão, consulte Permissões necessárias para as configurações padrão. Para ativar as otimizações baseadas no histórico, consulte Ativar otimizações baseadas no histórico.

• Para revisar as otimizações baseadas em histórico de um job usando a visualização INFORMATION_SCHEMA.JOBS, é preciso ter o papel necessário. Para mais informações, consulte Papel necessário para a visualização INFORMATION_SCHEMA.JOBS.

Ativar otimizações baseadas no histórico

Para usar otimizações baseadas em histórico em um projeto, inclua o seguinte parâmetro na instrução ALTER PROJECT ou ALTER ORGANIZATION: default_query_optimizer_options = 'adaptive=on' Por exemplo:

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

Substitua:

• PROJECT_NAME: o nome do projeto.
• LOCATION: o local do projeto.

Note: depois de ativar as otimizações baseadas em histórico, você pode ignorar a seguinte mensagem de atenção, que é mostrada para qualquer instrução ALTER PROJECT ou ALTER ORGANIZATION bem-sucedida: 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.

Desativar otimizações baseadas no histórico

Para desativar as otimizações baseadas em histórico em um projeto, inclua o parâmetro default_query_optimizer_options = 'adaptive=off' na instrução ALTER PROJECT or ALTER ORGANIZATION. Por exemplo:

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

Substitua:

• PROJECT_NAME: o nome do projeto.
• LOCATION: o local do projeto.

Observação: depois de desativar as otimizações baseadas em histórico, você pode ignorar com segurança a seguinte mensagem de atenção, que é mostrada para instruções ALTER PROJECT ou ALTER ORGANIZATION bem-sucedida: 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.

Revisar as otimizações baseadas no histórico de um job

Para revisar as otimizações baseadas no histórico de um job, use uma consulta SQL ou uma chamada de método da API REST.

SQL

Você pode usar uma consulta para ter otimizações baseadas no histórico de um job. A consulta precisa incluir INFORMATION_SCHEMA.JOBS_BY_PROJECT e o nome da coluna query_info.optimization_details.

No exemplo a seguir, os detalhes de otimização são retornados para um job chamado sample_job. Se nenhuma otimização baseada em histórico for aplicada, NULL será produzido para 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;

Os resultados são semelhantes aos seguintes:

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

Para ver os detalhes de otimização de um job, chame o método jobs.get.

No exemplo a seguir, o método jobs.get retorna os detalhes de otimização (optimizationDetails) na resposta completa:

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

Os resultados são semelhantes aos seguintes:

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

Estimar o impacto das otimizações com base no histórico

Para estimar o impacto das otimizações baseadas no histórico, use o exemplo de consulta SQL a seguir para identificar as consultas de projeto com a maior melhoria estimada no tempo de execução.

  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;

O resultado da consulta anterior será semelhante ao seguinte se otimizações com base no histórico foram aplicadas:

  /*--------------+------------------------------+------------------+-----------------------*
   |    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 |
   *--------------+------------------------------+------------------+-----------------------*/

Os resultados dessa consulta são apenas uma estimativa do impacto da otimização com base no histórico. Muitos fatores podem influenciar o desempenho da consulta, incluindo, mas não se limitando a, disponibilidade de slot, mudança nos dados ao longo do tempo, visualização ou definições de UDF e diferenças nos valores de parâmetros de consulta.

Se o resultado deste exemplo de consulta estiver vazio, nenhum job usou otimizações com base no histórico ou todas as consultas foram otimizadas há mais de 30 dias.

Essa consulta pode ser aplicada a outras métricas de desempenho de consulta, como total_slot_ms e total_bytes_billed. Para mais informações, consulte o esquema para INFORMATION_SCHEMA.JOBS.