Crea y usa vistas materializadas

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:

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

    Ir a la página de BigQuery

  2. Haz clic en Redactar consulta nueva.

  3. 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.
  4. 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:

  1. Abre la página de BigQuery en Cloud Console.
    Ir a la página de BigQuery

  2. Haz clic en Redactar consulta nueva.

  3. 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.
  4. 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:

  1. Abre la página de BigQuery en Cloud Console.
    Ir a la página de BigQuery

  2. Haz clic en Redactar consulta nueva.

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