Usar optimizaciones basadas en el historial

En esta guía se describe cómo habilitar, inhabilitar y analizar las optimizaciones basadas en el historial de las consultas.

Acerca de las optimizaciones basadas en el historial

Las optimizaciones basadas en el historial usan información de ejecuciones ya completadas de consultas similares para aplicar optimizaciones adicionales y mejorar aún más el rendimiento de las consultas, como el tiempo de asignación consumido y la latencia de las consultas. Por ejemplo, si aplicas la optimización basada en el historial, la primera ejecución de la consulta puede tardar 60 segundos, pero la segunda puede tardar solo 30 segundos si se ha identificado una optimización basada en el historial. Este proceso continúa hasta que no haya más optimizaciones que añadir.

A continuación, se muestra un ejemplo de cómo funcionan las optimizaciones basadas en el historial con BigQuery:

Recuento de ejecuciones	Tiempo de ranura de consulta consumido	Notas
1	60	Ejecución original.
2	30	Se ha aplicado la primera optimización basada en el historial.
3	20	Se ha aplicado la segunda optimización basada en el historial.
4	21	No hay más optimizaciones basadas en el historial que aplicar.
5	19	No hay más optimizaciones basadas en el historial que aplicar.
6	20	No hay más optimizaciones basadas en el historial que aplicar.

Las optimizaciones basadas en el historial solo se aplican cuando se tiene la certeza de que tendrán un impacto positivo en el rendimiento de las consultas. Además, cuando una optimización no mejora significativamente el rendimiento de las consultas, se revoca y no se usa en futuras ejecuciones de esa consulta.

Roles y permisos

• Para habilitar o inhabilitar las optimizaciones basadas en el historial, debe tener los permisos necesarios para crear configuraciones predeterminadas de BigQuery y, a continuación, usar la instrucción ALTER PROJECT para habilitar las optimizaciones basadas en el historial. Una vez que hayas habilitado las optimizaciones basadas en el historial, todos los trabajos de ese proyecto las usarán, independientemente del usuario que haya creado el trabajo. Para obtener más información sobre los permisos necesarios para las configuraciones predeterminadas, consulta la sección Permisos necesarios para las configuraciones predeterminadas. Para habilitar las optimizaciones basadas en el historial, consulta Habilitar las optimizaciones basadas en el historial.

• Para consultar las optimizaciones basadas en el historial de un trabajo mediante la vista INFORMATION_SCHEMA.JOBS, debes tener el rol necesario. Para obtener más información, consulta la sección Rol necesario para la vista INFORMATION_SCHEMA.JOBS.

Habilitar optimizaciones basadas en el historial

Las optimizaciones basadas en el historial están disponibles de forma general y se están implementando por fases. Para habilitar manualmente las optimizaciones basadas en el historial de tu proyecto, incluye el parámetro default_query_optimizer_options = 'adaptive=on' en la instrucción ALTER PROJECT o ALTER ORGANIZATION. Por ejemplo:

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

Haz los cambios siguientes:

• PROJECT_NAME: el nombre del proyecto
• LOCATION: la ubicación del proyecto

Nota: Después de habilitar las optimizaciones basadas en el historial, puedes ignorar sin problemas el siguiente mensaje de advertencia, que se muestra en cualquier instrucción 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.

Inhabilitar las optimizaciones basadas en el historial

Para inhabilitar las optimizaciones basadas en el historial en un proyecto, incluya el parámetro default_query_optimizer_options = 'adaptive=off' en la instrucción ALTER PROJECT o ALTER ORGANIZATION. Por ejemplo:

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

Haz los cambios siguientes:

• PROJECT_NAME: el nombre del proyecto
• LOCATION: la ubicación del proyecto

Nota: Después de inhabilitar las optimizaciones basadas en el historial, puedes ignorar sin problemas el siguiente mensaje de advertencia, que se muestra en cualquier instrucción ALTER PROJECT o ALTER ORGANIZATION que se haya completado correctamente: 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.

Consultar las optimizaciones basadas en el historial de una tarea

Para revisar las optimizaciones basadas en el historial de un trabajo, puedes usar una consulta de SQL o una llamada al método de la API REST.

SQL

Puedes usar una consulta para obtener las optimizaciones basadas en el historial de un trabajo. La consulta debe incluir INFORMATION_SCHEMA.JOBS_BY_PROJECT y el nombre de la columna query_info.optimization_details.

En el siguiente ejemplo, se devuelven los detalles de optimización de un trabajo llamado sample_job. Si no se ha aplicado ninguna optimización basada en el historial, se genera 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;

Los resultados deberían ser similares a los siguientes:

-- 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 obtener los detalles de optimización de una tarea, puedes llamar al método jobs.get.

En el siguiente ejemplo, el método jobs.get devuelve los detalles de optimización (optimizationDetails) en la respuesta completa:

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

Los resultados deberían ser similares a los siguientes:

-- 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 el impacto de las optimizaciones basadas en el historial

Para estimar el impacto de las optimizaciones basadas en el historial, puede usar la siguiente consulta de SQL de ejemplo para identificar las consultas de proyectos con la mayor mejora estimada en el tiempo de ejecución.

  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;

El resultado de la consulta anterior es similar al siguiente si se han aplicado optimizaciones basadas en el historial:

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

Los resultados de esta consulta son solo una estimación del impacto de la optimización basada en el historial. Hay muchos factores que pueden influir en el rendimiento de las consultas, como la disponibilidad de ranuras, los cambios en los datos a lo largo del tiempo, las definiciones de vistas o de funciones definidas por el usuario y las diferencias en los valores de los parámetros de consulta.

Si el resultado de esta consulta de ejemplo está vacío, significa que ninguna tarea ha usado optimizaciones basadas en el historial o que todas las consultas se optimizaron hace más de 30 días.

Esta consulta se puede aplicar a otras métricas de rendimiento de las consultas, como total_slot_ms y total_bytes_billed. Para obtener más información, consulta el esquema de INFORMATION_SCHEMA.JOBS.