Prácticas recomendadas para las vistas materializadas

En este documento, se proporcionan las prácticas recomendadas para usar las vistas materializadas de BigQuery. Antes de leer este documento, familiarízate con la Introducción a las vistas materializadas y Crea y usa vistas materializadas.

Consideraciones cuando se crea de una vista materializada

Asegúrate de que su definición concuerde con los patrones de las consultas en la tabla base. Hay un límite de 20 vistas materializadas por tabla, por lo que no debes crear una para cada permutación de consulta. En su lugar, crea vistas materializadas para entregar un conjunto de consultas más amplio.

Por ejemplo, piensa en una consulta de una tabla en la que los usuarios filtran según columnas user_id o department. Puedes agrupar según estas columnas y, de forma opcional, por clústeres, en lugar de agregar filtros como user_id = 123 en la vista materializada.

Otro ejemplo sería el de los usuarios que suelen filtrar por fecha, ya sea por fecha específica (where order_date = current_date()) o período (where order_date between '2019-10-01' and '2019-10-31'). Se recomienda, agregar un filtro de período en la vista materializada que cubra los períodos esperados de la consulta:

CREATE MATERIALIZED VIEW  ...
WHERE date > '2019-01-01' GROUP BY date

Si la tabla base está particionada y su vista materializada tiene un tamaño significativo, la vista materializada también se debe particionar. Como regla general, una vista materializada tiene un tamaño significativo si es igual o mayor que el de una de las particiones de la tabla base.

Consideraciones ARRAY_AGG

Cuando seleccionas las columnas ARRAY_AGG en una vista materializada, estás sujeto a las mismas limitaciones que cuando usas una cláusula ARRAY_AGG para seleccionar tabla de base. En el caso de RESPECT NULLS, que se establece de forma predeterminada, los arrays de salida no pueden contener elementos NULL. Se genera un error si un arreglo de salida contiene NULL en un elemento.

En esa vista materializada, ocurrirá lo siguiente:

CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table AS
SELECT
  column_1, ARRAY_AGG(column_2 ORDER BY column_3 DESC LIMIT 4) column_arr
FROM `project.dataset.base_table`
GROUP BY column_1

la siguiente consulta fallará:

SELECT * FROM project-id.my_dataset.my_mv_table

Los detalles de la falla son los siguientes:

query: Array cannot have a null element; error in writing field

Para evitar este error, agrega IGNORE NULLS a la cláusula ARRAY_AGG:

ARRAY_AGG(col_2 ORDER BY col_3 DESC LIMIT 4 IGNORE NULLS) col

También puedes dar formato a la columna de array de salida con el especificador '%t' o '%T'.

SELECT column_1, FORMAT('%T', column_arr) FROM project-id.my_dataset.my_mv_table

Consideraciones JOIN

Si bien las combinaciones no son compatibles en este momento, es posible que puedas aprovechar las vistas materializadas para reducir el costo y la latencia de una consulta que realiza agregación en una unión. Por ejemplo, imagina un caso en el que combinas una tabla de hechos grande con unas pocas tablas de dimensiones pequeñas y, luego, realizas una agregación a esa combinación. Puede ser práctico reescribir la consulta para realizar primero la agregación en la tabla de hechos con llaves externas como agrupamientos de claves, combinar el resultado con la tabla de dimensiones y, finalmente, llevar a cabo una agregación posterior.

Usemos la consulta #52 de la comparativa de TPC-DS para comprobar este enfoque.

Esta es la consulta original:

SELECT  dt.d_year,
   item.i_brand_id brand_id,
   item.i_brand brand,
 SUM(ss_ext_sales_price) ext_price
 FROM project.tpcds_10T.date_dim dt,
   project.tpcds_10T.store_sales,
   project.tpcds_10T.item
 WHERE dt.d_date_sk = store_sales.ss_sold_date_sk
    and store_sales.ss_item_sk = item.i_item_sk
    and item.i_manager_id = 1
    and dt.d_moy=12
    and dt.d_year=1998
 GROUP BY dt.d_year,
   item.i_brand,
   item.i_brand_id
 ORDER BY dt.d_year,
   ext_price DESC,
   brand_id
LIMIT 100

Esta es la consulta alternativa:

WITH sales_summary AS (
  SELECT ss_sold_date_sk,
    ss_item_sk,
    SUM(ss_ext_sales_price) AS ext_price
  FROM project.tpcds_10T.store_sales
  GROUP BY ss_sold_date_sk, ss_item_sk
)
SELECT dt.d_year,
  item.i_brand_id brand_id,
  item.i_brand brand,
  SUM(ext_price) AS ext_price
FROM sales_summary,
  project.tpcds_10T.date_dim dt,
  project.tpcds_10T.item
WHERE sales_summary.ss_sold_date_sk = dt.d_date_sk
  AND sales_summary.ss_item_sk = item.i_item_sk
    AND item.i_manager_id = 1
    AND dt.d_moy=12
    AND dt.d_year=1998
GROUP BY dt.d_year,
  item.i_brand,
  item.i_brand_id
ORDER BY dt.d_year,
  ext_price desc,
  brand_id
LIMIT 100

Puedes crear la vista de la siguiente forma:

CREATE OR REPLACE MATERIALIZED VIEW project-id.tpcds_10T.sales_summary_mv AS
  SELECT
    ss_sold_date_sk,
    ss_item_sk,
    SUM(ss_ext_sales_price) AS ext_price
  FROM project-id.tpcds_10T.store_sales
  GROUP BY ss_sold_date_sk, ss_item_sk

A continuación, puedes combinar la vista materializada con las tablas de dimensiones de la siguiente manera:

SELECT dt.d_year,
  item.i_brand_id brand_id,
  item.i_brand brand,
  SUM(ext_price) as ext_price
FROM sales_summary_mv,
  project.tpcds_10T.date_dim dt,
  project.tpcds_10T.item
WHERE sales_summary_mv.ss_sold_date_sk = dt.d_date_sk
  AND sales_summary_mv.ss_item_sk = item.i_item_sk
    AND item.i_manager_id = 1
    AND dt.d_moy=12
    AND dt.d_year=1998
GROUP BY dt.d_year,
  item.i_brand,
  item.i_brand_id
ORDER BY dt.d_year,
  ext_price desc,
  brand_id
LIMIT 100

Consideraciones de mantenimiento de las vistas materializadas

Supervisa el costo del trabajo de actualización y ajusta el intervalo de actualización automática si es necesario. En particular, supervisa total_bytes_processed y total_slot_ms.

Por ejemplo, si la tasa de transferencia de la tabla base es relativamente pequeña, tiene sentido actualizar la vista con menos frecuencia. En caso contrario, si los datos subyacentes cambian rápidamente, tiene sentido actualizar con más frecuencia.

Si la tabla base transfiere en períodos predefinidos, como una canalización nocturna de extracción, transformación y carga (ETL), considera controlar por completo el programa de mantenimiento de vistas materializadas de la siguiente manera:

  1. Inhabilita la actualización automática.

  2. Realiza una actualización manual, ya sea como parte de la canalización de ETL o mediante la configuración de una consulta programada en momentos específicos del día.

Ver la seguridad

Para controlar el acceso a las vistas de BigQuery, consulta Controla el acceso a las vistas.