Gestionar recomendaciones de vistas materializadas

En este documento se describe cómo funciona el recomendador de vistas materializadas y se explica cómo ver y aplicar las recomendaciones de vistas materializadas.

Introducción

El recomendador de vistas materializadas de BigQuery puede ayudarte a mejorar el rendimiento de las cargas de trabajo y a ahorrar costes de ejecución de las cargas de trabajo. Estas recomendaciones se basan en las características del historial de ejecución de consultas de los últimos 30 días.

Las vistas materializadas son vistas precalculadas que almacenan en caché periódicamente los resultados de una consulta para aumentar el rendimiento y la eficiencia. Las vistas materializadas usan el ajuste inteligente para reescribir de forma transparente las consultas en las tablas de origen y usar las vistas materializadas que ya existen para mejorar el rendimiento y la eficiencia.

Cómo funciona el sistema de recomendaciones

El recomendador genera recomendaciones a diario para cada proyecto que ejecuta tareas de consulta en BigQuery. Las recomendaciones se basan en el análisis de la ejecución de la carga de trabajo durante los últimos 30 días. El recomendador de vistas materializadas busca patrones de consulta repetitivos y calcula el ahorro que se podría conseguir si la subconsulta repetitiva se pudiera mover a una vista materializada incremental. El recomendador tiene en cuenta los ahorros en el momento de la consulta y el coste de mantenimiento de la cuenta de la vista materializada. Si estos factores combinados muestran un resultado positivo significativo, el sistema de recomendaciones hará una recomendación.

Consulta el siguiente ejemplo de consulta:

WITH revenue   AS
(SELECT l_suppkey as supplier_no,
        sum(l_extendedprice * (1 - l_discount)) as total_revenue
  FROM lineitem
  WHERE
    l_shipdate >= date '1996-01-01'
    AND l_shipdate < date_add(date '1996-01-01', interval 3 MONTH)
  GROUP BY l_suppkey)
SELECT s_suppkey,
      s_name,
      s_address,
      s_phone,
      total_revenue
FROM
supplier,
revenue
WHERE s_suppkey = supplier_no
AND total_revenue =
  (SELECT max(total_revenue)
    FROM revenue)
ORDER BY s_suppkey

En este ejemplo de consulta se muestra información sobre el proveedor principal. La consulta contiene una expresión de tabla común (CTE) llamada revenue, que representa los ingresos totales de cada proveedor (l_suppkey). revenue se combina con la tabla de proveedores con la condición de que el total_revenue del proveedor coincida con max(total_revenue) en todos los proveedores. Como resultado, la consulta calcula información (l_suppkey, s_name, s_address, s_phone, total_revenue) sobre el proveedor con los ingresos totales máximos.

La consulta en sí es demasiado complicada para incluirse en una vista materializada incremental. Sin embargo, la CTE supplier es una agregación en una sola tabla, un patrón de consulta que admiten las vistas materializadas incrementales. La supplier expresión CTE también es la parte de la consulta que requiere más recursos computacionales. Por lo tanto, si la consulta de ejemplo se ejecutara repetidamente en tablas de origen que cambian constantemente, el recomendador de vistas materializadas podría sugerir que se coloque la CTE supplier en una vista materializada. La vista materializada recomendada para la consulta de ejemplo anterior podría ser similar a la siguiente:

CREATE MATERIALIZED VIEW mv AS
SELECT l_suppkey as supplier_no,
         sum(l_extendedprice * (1 - l_discount)) as total_revenue
  FROM lineitem
  WHERE
    l_shipdate >= date '1996-01-01'
    AND l_shipdate < date_add(date '1996-01-01', interval 3 MONTH)
  GROUP BY l_suppkey

La API Recommender también devuelve información sobre la ejecución de consultas en forma de estadísticas. Las estadísticas son conclusiones que te ayudan a entender la carga de trabajo de tu proyecto y te proporcionan más contexto sobre cómo una recomendación de vista materializada puede mejorar los costes de la carga de trabajo.

Limitaciones

Antes de empezar

Para ver o aplicar recomendaciones de vistas materializadas, debes habilitar la API Recommender.

Permisos obligatorios

Para obtener los permisos que necesitas para acceder a las recomendaciones de vistas materializadas, pide a tu administrador que te asigne el rol de gestión de identidades y accesos Lector de Recommender de vistas materializadas de BigQuery (roles/recommender.bigqueryMaterializedViewViewer). Para obtener más información sobre cómo conceder roles, consulta el artículo Gestionar el acceso a proyectos, carpetas y organizaciones.

Este rol predefinido contiene los permisos necesarios para acceder a las recomendaciones de vistas materializadas. Para ver los permisos exactos que se necesitan, despliega la sección Permisos necesarios:

Permisos obligatorios

Para acceder a las recomendaciones de vistas materializadas, se necesitan los siguientes permisos:

  • recommender.bigqueryMaterializedViewRecommendations.get
  • recommender.bigqueryMaterializedViewRecommendations.list

También puedes obtener estos permisos con roles personalizados u otros roles predefinidos.

Para obtener más información sobre los roles y permisos de gestión de identidades y accesos en BigQuery, consulta la introducción a la gestión de identidades y accesos.

Ver recomendaciones de vistas materializadas

En esta sección se describe cómo ver las recomendaciones y las estadísticas de vistas materializadas mediante la Google Cloud consola, la CLI de Google Cloud o la API Recommender.

Selecciona una de las opciones siguientes:

Consola

  1. En la Google Cloud consola, ve a la página BigQuery.

    Ir a BigQuery

  2. En el menú de navegación, haga clic en Recomendaciones.

  3. Se abre el panel Recomendaciones de BigQuery. En Optimizar el coste de la carga de trabajo de BigQuery, haz clic en Ver detalles.

    Consulta los detalles para ver todas las recomendaciones de BigQuery

  4. Aparecerá una lista de recomendaciones con todas las que se han generado para el proyecto actual. Para ver más información sobre una recomendación de vista materializada o una estadística de tabla concretas, haz clic en Detalles.

También puede ver todas las recomendaciones disponibles para su proyecto u organización haciendo clic en Recomendaciones en el menú de navegación.

gcloud

Para ver las recomendaciones de vistas materializadas de un proyecto específico, usa el comando gcloud recommender recommendations list:

gcloud recommender recommendations list \
    --project=PROJECT_NAME \
    --location=REGION_NAME \
    --recommender=google.bigquery.materializedview.Recommender \
    --format=FORMAT_TYPE \

Haz los cambios siguientes:

  • PROJECT_NAME: el nombre del proyecto que ejecuta las tareas de consulta.
  • REGION_NAME: la región en la que se ejecutan los trabajos de consulta
  • FORMAT_TYPE: un formato de salida de la CLI de gcloud compatible, como JSON
En la siguiente tabla se describen los campos importantes de la respuesta `recommendations`:

Propiedad Relevante para el subtipo Descripción
recommenderSubtype CREATE_MATERIALIZED_VIEW El tipo de recomendación.
content.overview.sql CREATE_MATERIALIZED_VIEW Instrucción DDL sugerida que crea una vista materializada.
content.overview.slotMsSavedMonthly CREATE_MATERIALIZED_VIEW Milisegundos de espacio estimados que se ahorrarán al mes con la vista sugerida.
content.overview.bytesSavedMonthly CREATE_MATERIALIZED_VIEW Bytes estimados analizados que se ahorrarán al mes con la vista sugerida.
content.overview.baseTables CREATE_MATERIALIZED_VIEW Reservado para uso futuro.

Para ver las estadísticas que han generado recomendaciones de vistas materializadas con la CLI de gcloud, usa el comando gcloud recommender insights list:

gcloud recommender insights list \
    --project=PROJECT_NAME \
    --location=REGION_NAME \
    --insight-type=google.bigquery.materializedview.Insight \
    --format=FORMAT_TYPE \

Haz los cambios siguientes:

  • PROJECT_NAME: el nombre del proyecto que ejecuta las tareas de consulta.
  • REGION_NAME: la región en la que se ejecutan los trabajos de consulta
  • FORMAT_TYPE: un formato de salida de la CLI de gcloud compatible, como JSON
En la siguiente tabla se describen los campos importantes de la respuesta de la API de estadísticas:

Propiedad Relevante para el subtipo Descripción
content.queryCount CREATE_MATERIALIZED_VIEW Número de consultas del periodo de observación con un patrón repetitivo que se puede optimizar mediante una vista materializada.

API REST

Para ver las recomendaciones de vistas materializadas de un proyecto específico, usa la API REST. Con cada comando, debes proporcionar un token de autenticación, que puedes obtener mediante la CLI de gcloud. Para obtener más información sobre cómo obtener un token de autenticación, consulta Métodos para obtener un token de ID.

Puedes usar la solicitud curl list para ver todas las recomendaciones de un proyecto específico:

$ curl
-H "Authorization: Bearer $(gcloud auth print-access-token)"
-H "x-goog-user-project: PROJECT_NAME" https://recommender.googleapis.com/v1/projects/PROJECT_NAME/locations/LOCATION/recommenders/google.bigquery.materializedview.Recommender/recommendations

Haz los cambios siguientes:

  • PROJECT_NAME: el nombre del proyecto que contiene tu tabla de BigQuery
  • LOCATION: la ubicación del proyecto.
En la siguiente tabla se describen los campos importantes de la respuesta `recommendations`:

Propiedad Relevante para el subtipo Descripción
recommenderSubtype CREATE_MATERIALIZED_VIEW El tipo de recomendación.
content.overview.sql CREATE_MATERIALIZED_VIEW Instrucción DDL sugerida que crea una vista materializada.
content.overview.slotMsSavedMonthly CREATE_MATERIALIZED_VIEW Milisegundos de espacio estimados que se ahorrarán al mes con la vista sugerida.
content.overview.bytesSavedMonthly CREATE_MATERIALIZED_VIEW Bytes estimados analizados que se ahorrarán al mes con la vista sugerida.
content.overview.baseTables CREATE_MATERIALIZED_VIEW Reservado para uso futuro.

Para ver las estadísticas que han generado recomendaciones de vistas materializadas mediante la API REST, ejecuta el siguiente comando:

$ curl
-H "Authorization: Bearer $(gcloud auth print-access-token)"
-H "x-goog-user-project: PROJECT_NAME" https://recommender.googleapis.com/v1/projects/PROJECT_NAME/locations/LOCATION/insightTypes/google.bigquery.materializedview.Insight/insights

Haz los cambios siguientes:

  • PROJECT_NAME: el nombre del proyecto que contiene tu tabla de BigQuery
  • LOCATION: la ubicación del proyecto.
En la siguiente tabla se describen los campos importantes de la respuesta de la API de estadísticas:

Propiedad Relevante para el subtipo Descripción
content.queryCount CREATE_MATERIALIZED_VIEW Número de consultas del periodo de observación con un patrón repetitivo que se puede optimizar mediante una vista materializada.

Ver recomendaciones con INFORMATION_SCHEMA

También puedes ver tus recomendaciones y estadísticas con las INFORMATION_SCHEMAvistas. Por ejemplo, puedes usar la vista INFORMATION_SCHEMA.RECOMMENDATIONS para ver las tres recomendaciones principales en función del ahorro de espacios publicitarios, como se muestra en el siguiente ejemplo:

+---------------------------------------------------+--------------------------------------------------------------------------------------------------+
|                    recommender                    |   target_resources      | est_gb_saved_monthly | slot_hours_saved_monthly |  last_updated_time
+---------------------------------------------------+--------------------------------------------------------------------------------------------------+
| google.bigquery.materializedview.Recommender      | ["project_resource"]    | 140805.38289248943   |        9613.139166666666 |  2024-07-01 13:00:00
| google.bigquery.table.PartitionClusterRecommender | ["table_resource_1"]    | 4393.7416711859405   |        56.61476777777777 |  2024-07-01 13:00:00
| google.bigquery.table.PartitionClusterRecommender | ["table_resource_2"]    |   3934.07264107652   |       10.499466666666667 |  2024-07-01 13:00:00
+---------------------------------------------------+--------------------------------------------------------------------------------------------------+

Para obtener más información, consulta los siguientes recursos:

Aplicar recomendaciones de vistas materializadas

Para aplicar una recomendación y crear una vista materializada, ejecuta la instrucción DDL de tipo CREATE MATERIALIZED VIEW sugerida en la consola Google Cloud .

  1. En la Google Cloud consola, ve a la página BigQuery.

    Ir a BigQuery

  2. En el menú de navegación, haga clic en Recomendaciones.

  3. Se abre el panel Recomendaciones de BigQuery. En Optimizar el coste de la carga de trabajo de BigQuery, haz clic en Ver detalles.

    Consulta los detalles para ver todas las recomendaciones de BigQuery

  4. Aparecerá una lista de recomendaciones con todas las que se han generado para el proyecto o la organización actuales, en función del ámbito seleccionado. Busca una recomendación de vista materializada y haz clic en Detalles.

  5. Haz clic en Ver en BigQuery Studio. Se abre un editor de SQL que contiene una CREATE MATERIALIZED VIEW instrucción DDL.

  6. En la instrucción CREATE MATERIALIZED VIEW proporcionada, sustituya el marcador de posición MATERIALIZED_VIEW por un nombre único de vista materializada.

  7. Ejecuta la instrucción CREATE MATERIALIZED VIEW DDL para crear una vista materializada recomendada.

Solucionar problemas con las recomendaciones

Problema: No aparecen recomendaciones para una tabla específica.

Es posible que las recomendaciones de vistas materializadas no aparezcan en las siguientes circunstancias:

  • No se han encontrado patrones de consulta recurrentes entre los trabajos de consulta ejecutados por un proyecto.
  • Los patrones de consulta recurrentes no cumplen las limitaciones de las vistas materializadas incrementales y no se pueden incluir en una vista materializada adecuada para el ajuste inteligente.
  • La vista materializada potencial tendría un coste de mantenimiento elevado. Por ejemplo, las tablas de origen se suelen modificar mediante operaciones del lenguaje de manipulación de datos (DML), por lo que una vista materializada se sometería a una actualización completa, lo que supondría costes adicionales.
  • No hay suficientes consultas que tengan un patrón recurrente común.
  • El ahorro mensual estimado es demasiado insignificante (menos de 1 espacio).
  • Los trabajos de consulta ejecutados por el proyecto ya usan vistas materializadas.

Precios

Ver recomendaciones no tiene ningún coste ni repercusión negativa en el rendimiento de la carga de trabajo.

Si aplicas recomendaciones creando vistas materializadas, puedes incurrir en costes de almacenamiento, mantenimiento y consulta. Para obtener más información, consulta los precios de las vistas materializadas.