Usa optimizaciones basadas en el historial

En esta guía, se describe cómo habilitar, inhabilitar y analizar optimizaciones basadas en el historial para 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 ranura consumido y la latencia de la consulta. Por ejemplo, cuando aplicas la optimización basada en el historial, la primera ejecución de la consulta puede tardar 60 segundos, pero la segunda ejecución de la consulta puede tardar solo 30 segundos si se identificó una optimización basada en el historial. Este proceso continúa hasta que no hay optimizaciones adicionales que agregar.

El siguiente es un ejemplo de cómo funcionan las optimizaciones basadas en el historial con BigQuery:

Cantidad de ejecuciones	Tiempo de ranura de consulta consumido	Notas
1	60	Ejecución original.
2	30	Se aplicó la primera optimización basada en el historial.
3	20	Se aplicó la segunda optimización basada en el historial.
4	21	No hay optimizaciones adicionales basadas en el historial que se apliquen.
5	19	No hay optimizaciones adicionales basadas en el historial que se apliquen.
6	20	No hay optimizaciones adicionales basadas en el historial que se apliquen.

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

Funciones y permisos

• Para habilitar o inhabilitar las optimizaciones basadas en el historial, debes tener los permisos necesarios para crear la configuración predeterminada de BigQuery y, luego, debes usar la declaració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 usarán optimizaciones basadas en el historial, sin importar el usuario que creó el trabajo. A fin de obtener más información sobre los permisos necesarios para la configuración predeterminada, consulta Permisos necesarios para la configuración predeterminada. Para habilitar las optimizaciones basadas en el historial, consulta Habilita las optimizaciones basadas en el historial.

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

Habilita las optimizaciones basadas en el historial

Las optimizaciones basadas en el historial están disponibles para el público en general y se implementan por etapas. Para habilitar manualmente las optimizaciones basadas en el historial para tu proyecto, incluye el parámetro default_query_optimizer_options = 'adaptive=on' en la declaración ALTER PROJECT o ALTER ORGANIZATION. Por ejemplo:

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

Reemplaza lo siguiente:

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

Nota: Después de habilitar las optimizaciones basadas en el historial, puedes ignorar de forma segura el siguiente mensaje de precaución, que se muestra para cualquier declaración ALTER PROJECT o ALTER ORGANIZATION realizada de forma correcta: 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.

Inhabilita las optimizaciones basadas en el historial

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

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

Reemplaza lo siguiente:

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

Nota: Después de inhabilitar las optimizaciones basadas en el historial, puedes ignorar de forma segura el siguiente mensaje de precaución, que se muestra para cualquier declaración ALTER PROJECT o ALTER ORGANIZATION realizada de forma correcta: 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.

Revisa las optimizaciones basadas en el historial de un trabajo

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

SQL

Puedes usar una consulta con el fin de 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, los detalles de optimización se muestran para un trabajo llamado sample_job. Si no se aplicaron optimizaciones basadas en el historial, se produce 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 son 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 un trabajo, puedes llamar al método jobs.get.

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

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

Los resultados son 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"
            }
          ]
        }
      }
    }
  }
}

Estima el impacto de las optimizaciones basadas en el historial

Para estimar el impacto de las optimizaciones basadas en el historial, puedes usar la siguiente consulta en SQL de muestra para identificar las consultas del proyecto 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 aplicaron 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 las optimizaciones basadas en el historial. Muchos factores pueden influir en el rendimiento de las consultas, incluidos, sin limitaciones, la disponibilidad de los espacios, el cambio en los datos a lo largo del tiempo, las definiciones de vistas o UDF, y las diferencias en los valores de los parámetros de consulta.

Si el resultado de esta consulta de muestra está vacío, significa que ninguna tarea usó las 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 consultas, como total_slot_ms y total_bytes_billed. Para obtener más información, consulta el esquema de INFORMATION_SCHEMA.JOBS.