Administra recomendaciones de vistas materializadas

En este documento, se describe cómo funciona el recomendador de vistas materializadas y también se muestra 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 la carga de trabajo y ahorrar en el costo de ejecución de la carga de trabajo. Estas recomendaciones se basan en las características históricas de ejecución de consultas de los últimos 30 días.

Las vistas materializadas son vistas procesadas con anterioridad que almacenan en caché de forma periódica los resultados de una consulta para aumentar el rendimiento y la eficiencia. Las vistas materializadas usan el ajuste inteligente para volver a escribir las consultas con transparencia en las tablas de origen para usar las vistas materializadas existentes y, así, mejorar el rendimiento y la eficiencia.

Cómo funciona el recomendador

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

Considera 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 por cada proveedor (l_suppkey). revenue se une 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 completa 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 CTE supplier también es la parte más costosa de la consulta en términos de procesamiento. Por lo tanto, si la consulta de ejemplo se ejecutara de forma repetida en tablas de origen que cambian constantemente, el recomendador de vistas materializadas podría sugerir colocar la CTE supplier en una vista materializada. La recomendación de vista materializada 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 del Recomendador también devuelve información sobre la ejecución de consultas en forma de estadísticas. Las estadísticas son hallazgos que te ayudan a comprender la carga de trabajo de tu proyecto y proporcionan más contexto sobre cómo una recomendación de vistas materializadas podría mejorar los costos de carga de trabajo.

Limitaciones

Antes de comenzar

Antes de ver o aplicar las recomendaciones de vistas materializadas, debes habilitar la API del recomendador.

Permisos necesarios

Para obtener los permisos que necesitas para acceder a las recomendaciones de vistas materializadas, pídele a tu administrador que te otorgue el rol de IAM de Visualizador del recomendador de vistas materializadas de BigQuery (roles/recommender.bigqueryMaterializedViewViewer). Para obtener más información sobre cómo otorgar roles, consulta Administra 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 son necesarios, expande la sección Permisos requeridos:

Permisos necesarios

Se requieren los siguientes permisos para acceder a las recomendaciones de vistas materializadas:

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

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

Para obtener más información sobre los roles y permisos de IAM en BigQuery, consulta Introducción a IAM.

Cómo ver recomendaciones de vistas materializadas

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

Selecciona una de las siguientes opciones:

Console

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

    Ir a BigQuery

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

  3. Se abrirá el panel Recomendaciones de BigQuery. En Optimiza el costo 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 que muestra todas las recomendaciones generadas para el proyecto actual. Para ver más información sobre una recomendación de vista materializada o una estadística de tabla específica, haz clic en Detalles.

Como alternativa, puedes hacer clic en Recomendaciones en el menú de navegación para ver todas las recomendaciones disponibles para tu proyecto o tu organizació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 \

Reemplaza lo siguiente:

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

Propiedad Relevante para el subtipo Descripción
recommenderSubtype CREATE_MATERIALIZED_VIEW Es el tipo de recomendación.
content.overview.sql CREATE_MATERIALIZED_VIEW Es la declaración DDL sugerida que crea una vista materializada.
content.overview.slotMsSavedMonthly CREATE_MATERIALIZED_VIEW Son los milisegundos de ranura estimados que se ahorrarán por mes con la vista sugerida.
content.overview.bytesSavedMonthly CREATE_MATERIALIZED_VIEW Son los bytes estimados que se analizan y que se guardarán mensualmente con la vista sugerida.
content.overview.baseTables CREATE_MATERIALIZED_VIEW Se reserva para usarlo más adelante.

Para ver las estadísticas que generaron recomendaciones de vistas materializadas con gcloud CLI, 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 \

Reemplaza lo siguiente:

  • PROJECT_NAME: Es el nombre del proyecto que ejecuta los trabajos de consulta.
  • REGION_NAME: la región en la que se ejecutan los trabajos de consulta.
  • FORMAT_TYPE: un formato de salida de CLI de gcloud compatible; por ejemplo, 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 Cantidad de consultas en el período de observación con un patrón repetitivo que se puede optimizar con una vista materializada.

API de REST

Para ver las recomendaciones de vistas materializadas de un proyecto específico, usa la API de REST. Con cada comando, debes proporcionar un token de autenticación, que puedes obtener mediante la CLI de gcloud. Si deseas 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

Reemplaza lo siguiente:

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

Propiedad Relevante para el subtipo Descripción
recommenderSubtype CREATE_MATERIALIZED_VIEW Es el tipo de recomendación.
content.overview.sql CREATE_MATERIALIZED_VIEW Es la declaración DDL sugerida que crea una vista materializada.
content.overview.slotMsSavedMonthly CREATE_MATERIALIZED_VIEW Son los milisegundos de ranura estimados que se ahorrarán por mes con la vista sugerida.
content.overview.bytesSavedMonthly CREATE_MATERIALIZED_VIEW Son los bytes estimados que se analizan y que se guardarán mensualmente con la vista sugerida.
content.overview.baseTables CREATE_MATERIALIZED_VIEW Se reserva para usarlo más adelante.

Para ver las estadísticas que generaron las recomendaciones de vistas materializadas con la API de 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

Reemplaza lo siguiente:

  • PROJECT_NAME: el nombre del proyecto que contiene tu tabla de BigQuery
  • LOCATION: la ubicación en la que se encuentra el 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 Cantidad de consultas en el período de observación con un patrón repetitivo que se puede optimizar con una vista materializada.

Visualiza recomendaciones con INFORMATION_SCHEMA

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

SELECT
   recommender,
   target_resources,
   LAX_INT64(additional_details.overview.bytesSavedMonthly) / POW(1024, 3) as est_gb_saved_monthly,
   LAX_INT64(additional_details.overview.slotMsSavedMonthly) / (1000 * 3600) as slot_hours_saved_monthly,
  last_updated_time
FROM
  `region-us`.INFORMATION_SCHEMA.RECOMMENDATIONS
WHERE
   primary_impact.category = 'COST'
AND
   state = 'ACTIVE'
ORDER by
   slot_hours_saved_monthly DESC
LIMIT 3;

El resultado es similar al siguiente:

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

Aplica recomendaciones de vistas materializadas

Puedes aplicar una recomendación para crear una vista materializada ejecutando la instrucción DDL de tipo CREATE MATERIALIZED VIEW sugerida en la consola de Google Cloud .

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

    Ir a BigQuery

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

  3. Se abrirá el panel Recomendaciones de BigQuery. En Optimiza el costo 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 que muestra todas las recomendaciones generadas para el proyecto o la organización actuales, según el alcance seleccionado. Ubica una recomendación de vista materializada y haz clic en Detalles.

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

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

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

Soluciona problemas relacionados 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 encontraron patrones de consultas recurrentes entre los trabajos de consultas ejecutados por un proyecto.
  • Los patrones de consultas recurrentes no satisfacen las limitaciones de las vistas materializadas incrementales y no se pueden colocar en una vista materializada adecuada para el ajuste inteligente.
  • La posible vista materializada tendría un costo de mantenimiento alto. Por ejemplo, las tablas de origen suelen modificarse con 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 generaría costos adicionales.
  • No hay una cantidad suficiente de búsquedas que tengan un patrón recurrente común.
  • El ahorro mensual estimado es demasiado insignificante (menos de 1 ranura).
  • Los trabajos de consulta que ejecuta el proyecto ya usan vistas materializadas.

Precios

No hay costo ni impactos adversos en el rendimiento de las cargas de trabajo cuando ves las recomendaciones.

Cuando aplicas recomendaciones creando vistas materializadas, puedes incurrir en costos de almacenamiento, mantenimiento y consultas. Para obtener más información, consulta Precios de las vistas materializadas.