Use otimizações baseadas no histórico

Este guia descreve como ativar, desativar e analisar as otimizações baseadas no histórico para consultas.

Acerca das otimizações baseadas no histórico

As otimizações baseadas no histórico usam informações de execuções já concluídas de consultas semelhantes para aplicar otimizações adicionais e melhorar ainda mais o desempenho das consultas, como o tempo de espaço consumido e a latência das consultas. Por exemplo, quando aplica a otimização baseada no histórico, a primeira execução da consulta pode demorar 60 segundos, mas a segunda execução da consulta pode demorar apenas 30 segundos se for identificada uma otimização baseada no histórico. Este processo continua até não haver mais otimizações a adicionar.

Segue-se um exemplo de como as otimizações baseadas no histórico funcionam com o BigQuery:

Número de execuções	Tempo do intervalo de consulta consumido	Notas
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 existem otimizações adicionais baseadas no histórico para aplicar.
5	19	Não existem otimizações adicionais baseadas no histórico para aplicar.
6	20	Não existem otimizações adicionais baseadas no histórico para aplicar.

As otimizações baseadas no histórico só são aplicadas quando existe uma elevada confiança de que vão ter um impacto benéfico no desempenho das consultas. Além disso, quando uma otimização não melhora significativamente o desempenho das consultas, essa otimização é revogada e não é usada em execuções futuras dessa consulta.

Funções e permissões

• Para ativar ou desativar as otimizações baseadas no histórico, tem de ter as autorizações necessárias para criar configurações predefinidas do BigQuery e, em seguida, usar a declaração ALTER PROJECT para ativar as otimizações baseadas no histórico. Depois de ativar as otimizações baseadas no histórico, todas as tarefas nesse projeto usam otimizações baseadas no histórico, independentemente do utilizador que criou a tarefa. Para saber mais acerca das autorizações necessárias para as configurações predefinidas, consulte a secção Autorizações necessárias para as configurações predefinidas. Para ativar as otimizações baseadas no histórico, consulte o artigo Ative as otimizações baseadas no histórico.

• Para rever as otimizações baseadas no histórico de uma tarefa através da vista INFORMATION_SCHEMA.JOBS, tem de ter a função necessária. Para mais informações, consulte o papel necessário para a vista INFORMATION_SCHEMA.JOBS.

Ative as otimizações baseadas no histórico

As otimizações baseadas no histórico estão geralmente disponíveis e estão a ser implementadas em fases. Para ativar manualmente as otimizações baseadas no histórico para o seu projeto, inclua o parâmetro default_query_optimizer_options = 'adaptive=on' no seu comando ALTER PROJECT ou ALTER ORGANIZATION. Por exemplo:

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

Substitua o seguinte:

• PROJECT_NAME: o nome do projeto
• LOCATION: a localização do projeto

Nota: depois de ativar as otimizações baseadas no histórico, pode ignorar em segurança a seguinte mensagem de aviso, que é apresentada para qualquer declaraçã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.

Desative as otimizações baseadas no histórico

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

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

Substitua o seguinte:

• PROJECT_NAME: o nome do projeto
• LOCATION: a localização do projeto

Nota: depois de desativar as otimizações baseadas no histórico, pode ignorar em segurança a seguinte mensagem de aviso, que é apresentada para qualquer declaraçã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.

Reveja as otimizações baseadas no histórico de um trabalho

Para rever as otimizações baseadas no histórico de um trabalho, pode usar uma consulta SQL ou uma chamada de método da API REST.

SQL

Pode usar uma consulta para obter as otimizações baseadas no histórico de uma tarefa. A consulta tem de incluir INFORMATION_SCHEMA.JOBS_BY_PROJECT e o nome da coluna query_info.optimization_details.

No exemplo seguinte, os detalhes de otimização são devolvidos para uma tarefa denominada sample_job. Se não foram aplicadas otimizações baseadas no histórico, é gerado um NULL 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 têm um aspeto semelhante ao seguinte:

-- 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 obter os detalhes de otimização de uma tarefa, pode chamar o método jobs.get.

No exemplo seguinte, o método jobs.get devolve os detalhes da otimização (optimizationDetails) na resposta completa:

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

Os resultados têm um aspeto semelhante ao seguinte:

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

Estime o impacto das otimizações baseadas no histórico

Para estimar o impacto das otimizações baseadas no histórico, pode usar a seguinte consulta SQL de exemplo para identificar consultas de projetos 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,
    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;

O resultado da consulta anterior é semelhante ao seguinte se tiverem sido aplicadas otimizações baseadas no histórico:

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

Os resultados desta consulta são apenas uma estimativa do impacto da otimização baseada no histórico. Muitos fatores podem influenciar o desempenho das consultas, incluindo, entre outros, a disponibilidade de slots, a alteração dos dados ao longo do tempo, as definições de visualização ou de FDU e as diferenças nos valores dos parâmetros de consulta.

Se o resultado desta consulta de exemplo estiver vazio, significa que nenhum trabalho usou otimizações baseadas no histórico ou que todas as consultas foram otimizadas há mais de 30 dias.

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