En este documento, se describe cómo crear y usar vistas materializadas con las vistas materializadas de BigQuery. En este documento, se supone que ya leíste la Introducción a las vistas materializadas.
Crea una vista materializada
Puedes crear una vista materializada a través de Google Cloud Console, la herramienta de línea de comandos de bq
o la API de BigQuery.
Para los fines de estos ejemplos, supongamos que tu tabla base se llama my_base_table
y tiene este esquema:
Nombre de la columna | Tipo |
---|---|
product_id | número entero |
clicks | número entero |
Supongamos también que deseas una vista materializada que resuma la cantidad de clics por product_id
. En los siguientes pasos, se creará una vista materializada llamada my_mv_table
que tendrá el siguiente esquema.
Nombre de la columna | Tipo |
---|---|
product_id | número entero |
sum_clicks | número entero |
Para crear una vista materializada, sigue estos pasos:
Console
Las instrucciones del lenguaje de definición de datos (DDL) te permiten crear y modificar tablas y vistas con la sintaxis de consulta de SQL estándar.
Obtén más información en Usa instrucciones del lenguaje de definición de datos.
Para crear una vista materializada en Cloud Console con una instrucción de DDL:
En Cloud Console, ve a la página de BigQuery.
Haz clic en Redactar consulta nueva.
Escribe tu instrucción
CREATE MATERIALIZED VIEW
de DDL en el área de texto del Editor de consultas.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
En el ejemplo anterior, se ilustra lo siguiente:
- project-id es el ID del proyecto.
- my_dataset es el ID de un conjunto de datos en tu proyecto.
- my_mv_table es el ID de la vista materializada que deseas crear.
- my_base_table es el ID de una tabla en tu conjunto de datos que sirve de tabla base para la vista materializada.
product_id
es una columna de la tabla base.clicks
es una columna de la tabla base.sum_clicks
es una columna en la vista materializada que creas.
Haga clic en Ejecutar.
A menos que inhabilites las actualizaciones automáticas, BigQuery inicia una actualización completa y asíncrona para la vista materializada. Es posible que la consulta muestre el resultado de proceso completado de inmediato, pero que la actualización inicial siga ejecutándose. Cuando la vista materializada se crea de forma correcta, aparece en el panel Conjuntos de datos.
bq
Usa el comando bq query
y proporciona la instrucción de DDL como el parámetro de consulta.
bq query --use_legacy_sql=false ' 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'
En el ejemplo anterior, se ilustra lo siguiente:
- project-id es el ID del proyecto.
- my_dataset es el ID de un conjunto de datos en tu proyecto.
- my_mv_table es el ID de la vista materializada que deseas crear.
- my_base_table es el ID de una tabla en tu conjunto de datos que sirve de tabla base para la vista materializada.
product_id
es una columna de la tabla base.clicks
es una columna de la tabla base.sum_clicks
es una columna en la vista materializada que creas.
A menos que inhabilites las actualizaciones automáticas, BigQuery inicia una actualización completa y asíncrona para la vista materializada. Es posible que la consulta muestre el resultado de proceso completado de inmediato, pero que la actualización inicial siga ejecutándose. Cuando la vista materializada se crea de forma correcta, aparece en el panel Conjuntos de datos.
Como alternativa, puedes usar el comando bq mk
con el argumento --materialized_view
para crear una vista materializada. Los siguientes argumentos funcionan con el argumento --materialized_view
:
--enable_refresh
: especifica si la actualización automática está habilitada.--refresh_interval_ms
: especifica el tiempo, en milisegundos, para el intervalo de actualización.
API
Llama al método tables.insert
con un recurso materializedView
definido como parte de tu solicitud a la API. El recurso materializedView
contiene un campo query
. Por ejemplo:
{ "kind": "bigquery#table", "tableReference": { "projectId": "project-id", "datasetId": "my_dataset", "tableId": "my_mv_table" }, "materializedView": { "query": "select product_id,sum(clicks) as sum_clicks from project-id.my_dataset.my_base_table group by 1" } }
En el ejemplo anterior, se ilustra lo siguiente:
- project-id es el ID del proyecto.
- my_dataset es el ID de un conjunto de datos en tu proyecto.
- my_mv_table es el ID de la vista materializada que deseas crear.
- my_base_table es el ID de una tabla en tu conjunto de datos que sirve de tabla base para la vista materializada.
product_id
es una columna de la tabla base.clicks
es una columna de la tabla base.sum_clicks
es una columna en la vista materializada que creas.
A menos que inhabilites las actualizaciones automáticas, BigQuery inicia una actualización completa y asíncrona para la vista materializada. Es posible que la consulta muestre el resultado de proceso completado de inmediato, pero que la actualización inicial siga ejecutándose. Cuando la vista materializada se crea de forma correcta, aparece en el panel Conjuntos de datos.
Consulta una vista materializada
Realiza consultas directamente en tus vistas materializadas, de la misma manera en que consultas una tabla normal o una vista estándar.
Cuando consultas una tabla base que tiene una vista materializada, el optimizador de consultas puede redirigirse de forma automática a fin de usar el resultado almacenado en caché que se guarda en la vista materializada. El plan de consulta mostrará que la consulta se volvió a escribir para usar la vista materializada.
Si se pueden usar varias vistas materializadas para redirigir una consulta, se usa la que tenga la menor cantidad estimada de filas por analizar.
Si realizas una consulta de una vista materializada que no está actualizada, los datos de esta se combinarán con cambios delta de la tabla base a fin de calcular los resultados actualizados. Dentro de los motivos por los que una vista materializada puede no estar actualizada, se incluyen los siguientes:
La vista materializada no está configurada para la actualización automática, ya que el usuario decidió controlar las actualizaciones de forma manual.
La tabla base se actualiza con demasiada frecuencia, por lo que se aplica el límite de frecuencia de actualización.
La tabla base se cambió hace solo unos segundos, por lo que la vista materializada aún no alcanza a la tabla base.
Para obtener información sobre cómo ejecutar consultas, consulta Descripción general de las consultas de datos de BigQuery.
Altera una vista materializada
Puedes modificar una vista materializada a través de Cloud Console o la herramienta de línea de comandos de bq
mediante DDL con ALTER MATERIALIZED VIEW
y SET OPTIONS
.
A continuación, se muestra un ejemplo que establece enable_refresh
en true
. Realiza los ajustes necesarios para tu caso práctico.
Console
Para modificar una vista materializada en Cloud Console mediante una instrucción de DDL, sigue estos pasos:
Abre la página de BigQuery en Cloud Console.
Ir a la página de BigQueryHaz clic en Redactar consulta nueva.
Escribe tu instrucción
ALTER MATERIALIZED VIEW
de DDL en el área de texto del Editor de consultas.ALTER MATERIALIZED VIEW project-id.my_dataset.my_mv_table SET OPTIONS (enable_refresh=true)
En el ejemplo anterior, se ilustra lo siguiente:
- project-id es el ID del proyecto.
- my_dataset es el ID de un conjunto de datos en tu proyecto.
- my_mv_table es el ID de la vista materializada que deseas modificar.
Haz clic en Ejecutar.
bq
Usa el comando bq query
y proporciona la instrucción de DDL como el parámetro de consulta.
ALTER MATERIALIZED VIEW project-id.my_dataset.my_mv_table
SET OPTIONS (enable_refresh=true)
En el ejemplo anterior, se ilustra lo siguiente:
- project-id es el ID del proyecto.
- my_dataset es el ID de un conjunto de datos en tu proyecto.
- my_mv_table es el ID de la vista materializada que deseas modificar.
También puedes ejecutar el comando bq update
.
bq update \ --enable_refresh=true \ --refresh_interval_ms= \ project-id.my_dataset.my_mv_table
Manipula una vista materializada
Una vista materializada solo puede manipularse mediante las declaraciones CREATE
, DROP
o ALTER
, junto con sus equivalentes de API y una actualización manual.
Las siguientes operaciones no están permitidas en una vista materializada.
Ejecutar trabajos de copia, importación o exportación en los que la fuente o el destino sea una vista materializada
Escribir los resultados de las consultas en una vista materializada
Llamar a tabledata.list
Usar la API de BigQuery Storage
Borra una vista materializada
Puedes borrar una vista materializada a través de Cloud Console, la herramienta de línea de comandos de bq
o la API.
Console
Para borrar una vista materializada en Cloud Console con una instrucción de DDL, sigue estos pasos:
Abre la página de BigQuery en Cloud Console.
Ir a la página de BigQueryHaz clic en Redactar consulta nueva.
Escribe tu instrucción
DELETE MATERIALIZED VIEW
de DDL en el área de texto del Editor de consultas.DROP MATERIALIZED VIEW project-id.my_dataset.my_mv_table
En el ejemplo anterior, se ilustra lo siguiente:
- project-id es el ID del proyecto.
- my_dataset es el ID de un conjunto de datos en tu proyecto.
- my_mv_table es el ID de la vista materializada que deseas borrar.
Haz clic en Ejecutar.
bq
Usa el comando bq query
y proporciona la instrucción de DDL como el parámetro de consulta.
bq query --use_legacy_sql=false '
DROP MATERIALIZED VIEW
project-id.my_dataset.my_mv_table'
En el ejemplo anterior, se ilustra lo siguiente:
- project-id es el ID del proyecto.
- my_dataset es el ID de un conjunto de datos en tu proyecto.
- my_mv_table es el ID de la vista materializada que deseas borrar.
Como alternativa, puedes usar el comando bq rm
para borrar la vista materializada.
API
Llama al método tables.delete
y especifica los valores para los parámetros projectId
, datasetId
y tableId
:
- Asigna el parámetro
projectId
al ID de tu proyecto. - Asigna el parámetro
datasetId
al ID de tu conjunto de datos. - Asigna el parámetro
tableId
al ID de la tabla de la vista materializada que borrarás.
Vistas materializadas admitidas
Las vistas materializadas deben ser una agregación en una sola tabla. El uso de GROUP BY
es opcional.
En la actualidad, las vistas materializadas admiten una sintaxis de SQL restringida. Las consultas deben usar el siguiente patrón:
SELECT expression [ [ AS ] alias ] ] [, ...] FROM table_name [ [ AS ] alias ] [ WHERE bool_expression ] [ GROUP BY expression [, ...] ]
Actualmente, solo se admiten las siguientes funciones de agregación:
APPROX_COUNT_DISTINCT
ARRAY_AGG
AVG
COUNT
HLL_COUNT.INIT
MAX
MIN
SUM
Se admiten ciertas subconsultas, siempre que no agreguen ningún procesamiento ni filtro además de una agregación. Por ejemplo, se admiten las siguientes consultas de SQL:
WITH tmp AS (
SELECT TIMESTAMP_TRUNC(ts, HOUR) as ts_hour, *
FROM dataset.table
)
SELECT ts_hour, COUNT(*) as cnt
FROM tmp
GROUP BY ts_hour
WITH tmp AS (
SELECT TIMESTAMP_TRUNC(ts, HOUR) as ts_hour, COUNT(*) as cnt
FROM dataset.table
GROUP BY 1
)
SELECT * FROM tmp
SELECT ts_hour, COUNT(*) as cnt
FROM (
SELECT TIMESTAMP_TRUNC(ts, HOUR) as ts_hour, *
FROM dataset.table
)
GROUP BY ts_hour
SELECT * FROM (
SELECT TIMESTAMP_TRUNC(ts, HOUR) as ts_hour, COUNT(*) as cnt
FROM dataset.table
GROUP BY 1
)
La siguiente consulta de SQL no es compatible con las vistas materializadas, ya que aplica el cálculo además de una agregación:
-- Not supported for a materialized view
WITH tmp AS (
SELECT TIMESTAMP_TRUNC(ts, HOUR) as ts_hour, COUNT(*) as cnt
FROM dataset.table
GROUP BY 1
)
SELECT TIMESTAMP_TRUNC(ts_hour, DAY) as ts_day, cnt+1 as cnt FROM tmp
La siguiente consulta de SQL no es compatible con las vistas materializadas, ya que aplica el filtrado además de una agregación:
-- Not supported for a materialized view
SELECT ts_hour, cnt
FROM (
SELECT TIMESTAMP_TRUNC(ts, HOUR) as ts_hour, COUNT(*) as cnt
FROM dataset.table
GROUP BY 1
)
WHERE ts_hour != TIMESTAMP(DATE('2020-01-01'))
Patrones de reescritura de consultas admitidos
En los siguientes ejemplos, se muestran las consultas de tablas base y las definiciones de vistas materializadas que se admiten en la actualidad.
Patrón 1
En este patrón, se muestra que las claves de agrupación y los agregadores en la consulta son un subconjunto de los de la vista materializada.
Para esta consulta de tabla base, usa lo siguiente:
SELECT ss_sold_date_sk, SUM(ss_net_profit) AS sum_profit FROM store_sales GROUP BY 1
La siguiente es una consulta de vista materializada que muestra las claves de agrupación y los agregadores de la tabla base como un subconjunto de la consulta de vista materializada.
SELECT ss_store_sk, ss_sold_date_sk, SUM(ss_net_paid) AS sum_paid, SUM(ss_net_profit) AS sum_profit, COUNT(*) AS cnt_sales FROM store_sales GROUP BY 1, 2
Patrón 2
En este patrón, se muestra que las claves de agrupación en la consulta de la tabla base se pueden calcular a partir de claves de agrupación en la vista materializada.
Para esta consulta de tabla base, usa lo siguiente:
SELECT DATE_TRUNC(DATE(l_shipdate) MONTH) as shipmonth, SUM(l_extendedprice) as sum_price FROM lineitem GROUP BY 1
La siguiente es una consulta de vista materializada que calcula las claves de agrupación en la tabla base:
SELECT DATE(l_shipdate) as shipdate, SUM(l_extendedprice) as sum_price FROM lineitem GROUP BY 1
Patrón 3
En este patrón, se muestra que las expresiones de filtro en la consulta se pueden derivar de las claves de agrupación en la vista materializada o coincidir exactamente con la vista materializada.
Para esta consulta de tabla base, usa lo siguiente:
SELECT DATE_TRUNC(DATE(l_shipdate) MONTH) as shipmonth, SUM(l_extendedprice) as sum_price FROM lineitem WHERE DATE(l_shipdate) >= DATE(2016, 01, 01) AND l_shipmode = "AIR" GROUP BY 1
La siguiente es una consulta de vista materializada que deriva una expresión de filtro de la tabla base:
SELECT DATE(l_shipdate) as shipdate, SUM(l_extendedprice) as sum_price FROM lineitem WHERE l_shipmode = "AIR" GROUP BY 1
Patrón 4
En este patrón, se muestran expresiones en la consulta de tabla base y se selecciona un subconjunto de datos en la vista materializada.
Para esta consulta de tabla base, usa lo siguiente:
SELECT l_discount, COUNT(*) as cnt FROM lineitem WHERE l_discount > 20.0 GROUP BY 1
La siguiente es una consulta de vista materializada cuyos resultados son un superconjunto de la consulta de tabla base:
SELECT l_discount, COUNT(*) as cnt FROM lineitem WHERE l_discount IS NOT NULL GROUP BY 1
Actualizaciones incrementales
En los casos en los que la tabla base cambia con solo anexos, la consulta que utiliza la vista materializada escanea todos los datos de vista materializados más un delta en la tabla base desde la última actualización de la vista. Esto se aplica si el optimizador de consultas hace referencia explícita a la vista materializada o la selecciona. Como resultado, las consultas son más rápidas y económicas.
Sin embargo, si hubo actualizaciones o eliminaciones en la tabla base desde la última actualización de la vista materializada, es posible que esta no se analice.
Los siguientes son ejemplos de acciones que causan una actualización o eliminación:
DML UPDATE
DML MERGE
DML DELETE
- truncamiento
- vencimiento de la partición
- console, línea de comandos de
bq
y equivalentes de API de los elementos anteriores de esta lista
Si se producen estas acciones, es posible que la consulta de la vista materializada no consiga ningún ahorro hasta que se realice la próxima actualización de la vista. En efecto, cualquier actualización o eliminación en la tabla base invalida una parte del estado de vista materializada. Para las vistas no particionadas, se invalida toda la vista. Para las vistas particionadas, solo se invalidan las particiones afectadas.
Ten en cuenta que los datos del búfer de transmisión de BigQuery de la tabla base no se guardan en una vista materializada. Un búfer de transmisión aún se está analizando por completo, independientemente de si se usa una vista materializada.
Trabaja con tablas particionadas y agrupadas
Las vistas materializadas se pueden particionar con la misma columna de partición que se configura en la tabla base.
Las vistas materializadas se pueden agrupar en columnas arbitrarias, sujetas a las limitaciones de las tablas agrupadas de BigQuery.
A continuación, se muestra cómo crear una tabla base con una partición DATE
en la columna transaction_time
:
CREATE TABLE project-id.my_dataset.my_base_table( employee_id INT64, transaction_time TIMESTAMP) PARTITION BY DATE(transaction_time) OPTIONS ( partition_expiration_days = 2)
A continuación, se muestra cómo crear una vista materializada particionada por la misma columna transaction_time
:
CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table
PARTITION BY DATE(transaction_time)
CLUSTER BY employee_id
AS SELECT
employee_id,
transaction_time,
COUNT(1) AS cnt
FROM my_dataset.my_base_table
GROUP BY 1, 2;
Cuando la tabla base está particionada por tiempo de transferencia, una vista materializada puede agruparse por la columna _PARTITIONDATE
de la tabla base y también por partición.
A continuación, se muestra cómo crear una tabla base particionada por tiempo de transferencia:
CREATE TABLE project-id.my_dataset.my_base_table( employee_id INT64) PARTITION BY _PARTITIONDATE OPTIONS ( partition_expiration_days = 2)
A continuación, se muestra cómo crear una vista materializada que se agrupa por la hora de transferencia de la tabla base y también se divide en particiones de ella:
CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table
PARTITION BY date
CLUSTER BY employee_id
AS SELECT
employee_id,
_PARTITIONDATE as date,
COUNT(1) AS count
FROM my_dataset.my_base_table
GROUP BY 1, 2;
Cuando la tabla base está particionada por TIMESTAMP
, la vista materializada puede truncar esa marca de tiempo hasta el nivel de detalle de un DAY
y, luego, usar la marca de tiempo truncada como una columna de partición.
A continuación, se muestra cómo crear una vista materializada particionada en la columna transaction_hour
, que es un truncamiento de la columna transaction_time
de la tabla base. Usa la función TIMESTAMP_TRUNC
para truncar la marca de tiempo.
CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table
PARTITION BY DATE(transaction_hour)
AS SELECT
employee_id,
TIMESTAMP_TRUNC(transaction_time, HOUR) as transaction_hour,
COUNT(1) AS cnt
FROM my_dataset.my_base_table
GROUP BY 1, 2;
A continuación, se muestra el nivel de detalle que puedes utilizar para el truncamiento de la marca de tiempo:
MICROSECOND
MILLISECOND
SECOND
MINUTE
HOUR
DAY
Para obtener información general sobre el truncamiento de la marca de tiempo, consulta TIMESTAMP_TRUNC
.
Alineación de particiones
Si una vista materializada está particionada, BigQuery se asegura de que sus particiones estén alineadas con las particiones de la tabla base. Por ejemplo, una fila de la partición 2020-01-01
de la tabla base se agrega a una fila de la partición 2020-01-01
de la vista materializada.
La alineación de particiones ayuda a garantizar que una vista materializada se mantenga de manera eficiente. Durante la actualización, las únicas particiones de la vista materializada que se actualizarán son aquellas que tienen anexos, actualizaciones o eliminaciones en las particiones correspondientes de la tabla base. En el momento de la consulta, la alineación de particiones garantiza que se aplique un filtro de partición al análisis de vista materializada y al análisis de tabla base.
Vencimiento de la partición
No se debe establecer el vencimiento de la partición en las vistas materializadas. Una vista materializada hereda implícitamente el tiempo de vencimiento de la partición de la tabla base. Las particiones de vista materializadas están alineadas con las particiones de tabla base, por lo que caducan de forma síncrona.
Mantén vistas materializadas
Administra la forma en que se actualizan las vistas materializadas para actualizar los resultados calculados previamente de la tabla base a través de actualizaciones automáticas y manuales. Puedes establecer la configuración de actualización cuando creas una vista materializada si no deseas usar valores predeterminados. Puedes modificar la configuración de actualización después de crear la vista materializada.
Puedes actualizar manualmente una vista materializada en cualquier momento.
Actualización automática
De forma predeterminada, las vistas materializadas se actualizan automáticamente en un plazo de 5 minutos desde que se hace un cambio en la tabla base. Algunos ejemplos de cambios son las inserciones o eliminaciones de filas.
La actualización automática se puede habilitar o inhabilitar en cualquier momento.
Para desactivar la actualización automática cuando creas una tabla, configura enable_refresh
como false
.
CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table
PARTITION BY RANGE_BUCKET(column, buckets)
OPTIONS (enable_refresh = false)
AS SELECT ...
Para una vista materializada existente, puedes modificar el valor enable_refresh
con ALTER MATERIALIZED VIEW
.
ALTER MATERIALIZED VIEW project-id.my_dataset.my_mv_table
SET OPTIONS (enable_refresh = true)
Ten en cuenta que si habilitas esta opción, se ejecutará una actualización automática en la vista materializada.
Aunque la actualización automática esté inhabilitada, la vista materializada se puede actualizar manualmente en cualquier momento.
Configura la limitación de frecuencia
Puedes configurar una limitación de la frecuencia con la que se ejecuta la actualización automática. De forma predeterminada, las vistas materializadas se actualizan con una frecuencia de no más de 30 minutos.
La limitación de frecuencia de actualización se puede cambiar en cualquier momento.
Para establecer un límite de frecuencia de actualización cuando creas una vista materializada, configura refresh_interval_minutes
en DDL (o refresh_interval_ms
en la API y en la herramienta de línea de comandos de bq
) en el valor en cuestión.
CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table
OPTIONS (enable_refresh = true, refresh_interval_minutes = 60)
AS SELECT ...
De manera similar, puedes establecer la limitación de frecuencia cuando modificas una tabla. En este ejemplo, se supone que ya habilitaste la actualización automática y solo deseas cambiar la limitación de frecuencia:
ALTER MATERIALIZED VIEW project-id.my_dataset.my_mv_table
SET OPTIONS (refresh_interval_minutes = 60)
El límite mínimo de frecuencia de actualización es de 1 minuto. El límite máximo de frecuencia de actualización es de 7 días.
Puedes realizar una actualización manual de una vista materializada en cualquier momento, y su duración no está sujeta a la limitación de frecuencia.
Actualización manual
Para actualizar los datos en la vista materializada, llama al procedimiento del sistema BQ.REFRESH_MATERIALIZED_VIEW
en cualquier momento. Cuando se invoca este procedimiento, BigQuery identifica los cambios que tuvieron lugar en la tabla base y los aplica en la vista materializada. La consulta para ejecutar BQ.REFRESH_MATERIALIZED_VIEW
finalizará cuando se complete la actualización.
CALL BQ
.REFRESH_MATERIALIZED_VIEW('project-id.my_dataset.my_mv_table')
No debes realizar más de una actualización a la vez. Aunque se pueden iniciar varias actualizaciones de forma simultánea para la misma vista materializada, solo la primera actualización se completará de forma adecuada.
Permisos necesarios
Para actualizar una vista materializada de forma manual, debes tener los siguientes permisos:
bigquery.tables.getData
bigquery.tables.update
bigquery.tables.updateData
Próximos pasos
- Obtén información sobre las prácticas recomendadas para vistas materializadas de BigQuery.
- Lee las Preguntas frecuentes sobre las vistas materializadas.