Introducción a las vistas materializadas

En esta página, se proporciona una descripción general de las vistas materializadas de BigQuery.

Introducción

Las vistas materializadas son vistas procesadas previamente que almacenan en caché de forma periódica los resultados de una consulta para aumentar el rendimiento y la eficacia. BigQuery aprovecha los resultados procesados previamente de las vistas materializadas y, siempre que sea posible, lee solo los cambios delta de la tabla base para calcular los resultados actualizados. Las vistas materializadas se pueden consultar directamente, o bien el optimizador de BigQuery puede usarlas para procesar consultas en las tablas base.

Las consultas que usan vistas materializadas suelen ser más rápidas y consumen menos recursos que las que recuperan los mismos datos solo de la tabla base. Las vistas materializadas sirven para aumentar de forma significativa el rendimiento de las cargas de trabajo que tienen consultas comunes y repetidas.

Las siguientes son características clave de las vistas materializadas de BigQuery:

  • No requieren mantenimiento: Cuando se cambia la tabla base, la vista materializada se vuelve a procesar en segundo plano. Los cambios de datos incrementales de las tablas base se agregan automáticamente a las vistas materializadas. No se requieren entradas del usuario.

  • Se mantienen actualizadas: Las vistas materializadas siempre concuerdan con la tabla base, incluidas las tablas de transmisión de BigQuery. Si una tabla base se modifica por una actualización o combinación, o bien el truncamiento o vencimiento de particiones, BigQuery invalidará las partes afectadas de la vista materializada y volverá a leer la parte de la tabla base que corresponde. Si se trata de una vista materializada no particionada, BigQuery la invalidará por completo y volverá a leer toda la tabla base. Si se trata de una vista materializada particionada, BigQuery invalidará las particiones afectadas de esta y volverá a leer todas las particiones de la tabla base que correspondan. Las particiones que son solo para agregar no se invalidan y se leen en modo delta. En otras palabras, nunca habrá una situación en la que consultar una vista materializada genere datos inactivos.

  • Tienen redirección inteligente: Si una consulta o una parte de ella en la tabla fuente se puede resolver mediante una consulta en la vista materializada, BigQuery reescribirá (redireccionará) la consulta para usar la vista materializada y, así, obtener un mejor rendimiento o eficacia.

Beneficios

Las vistas materializadas de BigQuery ofrecen los siguientes beneficios:

  • Reducción del tiempo de ejecución y del costo de las consultas con funciones agregadas. El mayor beneficio se obtiene cuando el costo de procesamiento de una consulta es alto y el conjunto de datos mostrado es pequeño.

  • Optimización automática y transparente de BigQuery, ya que el optimizador usa una vista materializada (si esta está disponible) para mejorar el plan de ejecución de las consultas. Esta optimización no requiere ningún cambio en las consultas.

  • La misma resiliencia y alta disponibilidad que las tablas de BigQuery.

Casos de uso

Las vistas materializadas de BigQuery se orientan a los siguientes casos de uso:

  • Rendimiento más rápido de las consultas: Si tienes una tabla sin procesar y realizas una agregación de estilo de procesamiento analítico en línea (OLAP) que requiere un procesamiento significativo, y tienes consultas predecibles y repetidas, como extraer, transformar, cargar (ETL) o canalizaciones de inteligencia empresarial (IE), debes usar vistas materializadas de BigQuery.

  • Agregación de datos en tiempo real: Si deseas acceder a los datos para tomar decisiones en tiempo real, BigQuery es compatible de forma nativa con las capacidades de transmisión. Las vistas materializadas de BigQuery se integran en la transmisión de BigQuery y realizan agregaciones en tiempo real para proporcionar información actualizada.

Crea vistas materializadas

Puedes crear una vista materializada a través de Cloud Console, la herramienta de línea de comandos de bq o la API de BigQuery. Por ejemplo, en Cloud Console puedes usar la siguiente declaración DDL:

CREATE MATERIALIZED VIEW  project-id.my_dataset.my_mv_table
AS SELECT product_id, SUM(clicks) AS sum_clicks
FROM  project-id.my_dataset.my_base_table
GROUP BY 1

Si deseas obtener más información para usar Cloud Console, la herramienta de línea de comandos de bq o la API de BigQuery a fin de crear una vista materializada, consulta Crea y usa vistas materializadas.

Costos

Los costos de las vistas materializadas de BigQuery se asocian a los siguientes componentes:

  • Consulta de vistas materializadas
  • Mantenimiento de vistas materializadas, como cuando se actualiza la vista
  • Almacenamiento de tablas de vistas materializadas
Componente Precios a pedido Tasa fija
Consultas Bytes procesados por la vista materializada y cualquier parte necesaria de la tabla base. 1 Sin costo adicional. Las ranuras se consumen con el tiempo de consulta.
Mantenimiento Bytes procesados. Las ranuras se consumen con la actualización de la vista incremental. Sin costo adicional. Las ranuras se consumen con el tiempo de consulta.
Almacenamiento Bytes almacenados en una vista materializada Bytes almacenados en una vista materializada

1 Si la tabla base es solo para agregar, de esta solo se procesará el delta de la última actualización de la vista. Para obtener más información, consulta Actualizaciones incrementales.

Detalles del costo de almacenamiento

Para los valores agregados AVG, ARRAY_AGG y APPROX_COUNT_DISTINCT de una vista materializada, el valor final no se almacena directamente. En vez de eso, BigQuery almacena de forma interna una vista materializada como un boceto intermedio que se usa para producir el valor final.

Por ejemplo, considera la vista materializada que se creó con el siguiente comando:

CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table AS
SELECT date, AVG(net_paid) AS avg_paid
FROM project-id.my_dataset.my_base_table
GROUP BY date

Aunque la columna avg_paid se procesará como NUMERIC o FLOAT64 para el usuario, se almacenará como BYTES de forma interna, y su contenido será un boceto intermedio de formato propietario. Para efectos del cálculo del tamaño de los datos, la columna se tratará como BYTES.

Control de acceso

El acceso que se otorga a una vista materializada se establece a nivel del conjunto de datos, a nivel de la vista o a nivel de la columna. También puedes configurar el acceso en un nivel superior en la jerarquía de recursos de IAM.

Supervisa las vistas materializadas

Puedes especificar la información sobre vistas materializadas y trabajos de actualización de vistas materializadas con la API de BigQuery.

Para descubrir vistas materializadas, llama al método tables.list a fin de enumerarlas. Para recuperar el esquema y otras propiedades de una tabla, llama al método tables.get.

Para descubrir trabajos de actualización de vistas materializadas, llama al método jobs.list a fin de enumerarlos. Para recuperar detalles sobre los trabajos, llama al método jobs.get. Los trabajos de actualización automática contienen el prefijo materialized_view_refresh en el ID de tarea, y una cuenta de administrador de BigQuery los inicia.

Comparación con otras técnicas de BigQuery

En la siguiente tabla, se resumen las similitudes y diferencias entre el almacenamiento en caché, las consultas programadas, las vistas estándar y las vistas materializadas de BigQuery.

Componente Almacenamiento en caché Consultas programadas Vistas estándar Vistas materializadas
Rendimiento No No
Consulta admitida Todos Todos Todos Agregación: filtros y grupos
Partición y agrupamiento en clústeres No NA
Actualización incremental No No No
Almacenamiento adicional No No
Reescritura de consultas No No No
Costos de mantenimiento No NA
Inactividad de los datos Nunca Nunca Nunca
Limitado a una sola tabla No No No

Interacción con otras funciones de BigQuery

Las siguientes características de BigQuery funcionan de manera transparente con vistas materializadas:

  • Explicación del plan de consultas: El plan de consultas mostrará las vistas materializadas que se analizaron (si las hay) y cuántos bytes se leyeron de la vista materializada y la tabla base combinadas.

  • Almacenamiento de consultas en caché: Los resultados de una consulta que se vuelve a escribir con una vista materializada pueden almacenarse en caché, de acuerdo con las limitaciones habituales (uso de funciones deterministas, sin transmisión a la tabla base, etcétera).

  • Cálculo de costos: Con la facturación a pedido, si la consulta se reescribió mediante vistas materializadas y se analizaron menos bytes que en la tabla base, la consulta será más económica.

  • Restricción de costos: Si estableciste un valor para la cantidad máxima de bytes facturados y una consulta lee más bytes que los indicados, esta fallará y no generará costos, sin importar si usó la vista materializada, la tabla base o ambas.

  • Estimación de costos mediante ejecuciones de prueba: Una ejecución de prueba replica la lógica de reescritura de la consulta mediante las vistas materializadas disponibles y debería proporcionar una estimación de costos precisa. Puedes usarla para probar si una consulta específica utiliza vistas materializadas.

Limitaciones

  • No puedes manipular directamente o leer contenido sin procesar en vistas materializadas. Esto significa que no se admiten las siguientes acciones:

    • Copiar vistas materializadas como fuente o destino de un trabajo de copia
    • Exportar vistas materializadas
    • Cargar datos en vistas materializadas
    • Escribir resultados de consultas en vistas materializadas
    • Ejecutar declaraciones DML en vistas materializadas
  • Las vistas materializadas deben estar en el mismo conjunto de datos que la tabla base.

  • Las vistas materializadas admiten una sintaxis de SQL restringida y un conjunto limitado de funciones de agregación. Para obtener más información, consulta Vistas materializadas admitidas.

  • Una vista materializada puede hacer referencia a una sola tabla y no puede usar uniones ni la funcionalidad UNNEST.

  • Las vistas materializadas no se pueden anidar en otras vistas materializadas.

  • Solo puedes usar el dialecto de SQL estándar para las vistas materializadas.

  • Puedes crear un máximo de 20 vistas materializadas por tabla base.

  • Las declaraciones DDL SHOW no son compatibles con las vistas materializadas.

  • Las declaraciones DDL ALTER que no sean ALTER con SET OPTIONS no son compatibles con las vistas materializadas.

  • Si borras la tabla base sin borrar primero la vista materializada, las consultas sobre la vista materializada fallarán, al igual que las actualizaciones. Si decides volver a crear la tabla base, también deberás volver a crear la vista materializada.

  • No se admite la protección de datos con claves de Cloud Key Management Service. No puedes proteger los datos en una vista materializada mediante claves de Cloud KMS ni crear una vista materializada en una tabla base protegida por claves de Cloud KMS.

Consideraciones para ARRAY_AGG

La selección de columnas ARRAY_AGG en una vista materializada tiene las mismas limitaciones que la selección de columnas ARRAY_AGG en la tabla base. En el caso de RESPECT NULLS, que se establece de forma predeterminada, los arreglos 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 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 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 arreglo de salida con el especificador '%t' o '%T'.

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

Consideraciones para JOIN

Si bien las combinaciones no son compatibles actualmente, es posible que puedas aprovechar las vistas materializadas para reducir el costo y la latencia de una consulta que agrega datos a una combinació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 claves 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, se muestra cómo combinar la vista materializada con las tablas de dimensiones:

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.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

Próximos pasos