Crea y usa vistas materializadas

En este documento, se describe cómo crear y usar vistas materializadas mediante las vistas materializadas de BigQuery. Antes de leer este documento, familiarízate con la Introducción a las vistas materializadas.

Permisos necesarios

Antes de que puedas trabajar con una vista materializada, debes tener los permisos necesarios para la vista materializada. En la siguiente tabla, se enumeran los tipos de operaciones que puedes realizar en las vistas materializadas, los comandos y métodos que puedes usar a fin de realizar esas operaciones, los permisos necesarios para cada operación y la funciones predeterminadas que incluyen esos permisos.

Para obtener más información sobre la administración de identidades y accesos (IAM) de BigQuery, consulta Funciones y permisos predefinidos.

Tipo de operación	Comando o método	Permisos necesarios	Funciones predeterminadas
Crear	`CREATE MATERIALIZED VIEW` `bq mk --materialized view` `tables.insert`	`bigquery.tables.create`	`bigquery.dataEditor` `bigquery.dataOwner` `bigquery.admin`
Consulta	Consulta de SQL estándar `bq query`	`tables.getData`	`bigquery.dataViewer` `bigquery.dataEditor` `bigquery.dataOwner` `bigquery.admin`
Actualizar	`ALTER MATERIALIZED VIEW` `bq query` `bq update` `tables.patch` `tables.update`	`bigquery.tables.get` `bigquery.tables.update`	`bigquery.dataEditor` `bigquery.dataOwner` `bigquery.admin`
Borrar	`DROP MATERIALIZED VIEW` `bq query` `bq rm` `tables.delete`	`bigquery.tables.get` `bigquery.tables.delete`	`bigquery.dataEditor` `bigquery.dataOwner` `bigquery.admin`
Actualización manual	`CALL BQ.REFRESH_MATERIALIZED_VIEW`	`bigquery.tables.getData` `bigquery.tables.update` `bigquery.tables.updateData`	`bigquery.dataEditor` `bigquery.dataOwner` `bigquery.admin`

Crea vistas materializadas

Puedes crear vistas materializadas de BigQuery 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.

Para obtener más información, consulta cómo usar 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.

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

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.

Java

Antes de probar este ejemplo, sigue las instrucciones de configuración para Java que se encuentran en la guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Para obtener más información, consulta la documentación de referencia de la API de BigQuery para Java.

Ver en GitHub Comentarios

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.MaterializedViewDefinition;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;

// Sample to create materialized view
public class CreateMaterializedView {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String materializedViewName = "MY_MATERIALIZED_VIEW_NAME";
    String query =
        String.format(
            "SELECT MAX(TimestampField) AS TimestampField, StringField, "
                + "MAX(BooleanField) AS BooleanField "
                + "FROM %s.%s GROUP BY StringField",
            datasetName, tableName);
    createMaterializedView(datasetName, materializedViewName, query);
  }

  public static void createMaterializedView(
      String datasetName, String materializedViewName, String query) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, materializedViewName);

      MaterializedViewDefinition materializedViewDefinition =
          MaterializedViewDefinition.newBuilder(query).build();

      bigquery.create(TableInfo.of(tableId, materializedViewDefinition));
      System.out.println("Materialized view created successfully");
    } catch (BigQueryException e) {
      System.out.println("Materialized view was not created. \n" + e.toString());
    }
  }
}

Control de acceso

Puedes otorgar acceso a una vista materializada a nivel del conjunto de datos, al nivel de la vista o al nivel de la columna. También puedes configurar el acceso en un nivel superior en la jerarquía de recursos de IAM.

Consulta de vistas materializadas

Realiza consultas directamente en tus vistas materializadas, de la misma manera que en una tabla normal o una vista estándar.

Cuando consultas una tabla base que tiene una vista materializada, el optimizador de consultas puede reescribirse de forma automática a fin de usar el resultado almacenado en caché que se guarda en la vista materializada. El plan de consultas muestra que la consulta se reescribió para usar la vista materializada.

Si se pueden usar varias vistas materializadas para reescribir 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.

Incluso si una vista materializada no está actualizada debido a uno o más de estos motivos, las consultas en la vista materializada siguen actualizadas, ya que los cambios de la tabla base también se incorporan en los resultados de la consulta.

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:

En Cloud Console, abre la página de BigQuery.

Ir a BigQuery

Haz 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

Java

Ver en GitHub Comentarios

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.TableResult;

public class QueryMaterializedView {

  public static void main(String[] args) throws InterruptedException {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String materializedViewName = "MY_MATERIALIZED_VIEW_NAME";
    String query = String.format("SELECT * FROM %s.%s", datasetName, materializedViewName);
    queryMaterializedView(query);
  }

  public static void queryMaterializedView(String query) throws InterruptedException {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableResult results = bigquery.query(QueryJobConfiguration.of(query));
      results
          .iterateAll()
          .forEach(row -> row.forEach(val -> System.out.printf("%s\n", val.toString())));

      System.out.println("Query performed successfully.");
    } catch (BigQueryException e) {
      System.out.println("Query was not performed. \n" + e.toString());
    }
  }
}

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 lectura de almacenamiento de BigQuery

En el siguiente ejemplo, se muestra una vista de material:

Java

Ver en GitHub Comentarios

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.MaterializedViewDefinition;
import com.google.cloud.bigquery.Table;
import com.google.cloud.bigquery.TableId;

// Sample to alter materialized view
public class AlterMaterializedView {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String materializedViewName = "MY_MATERIALIZED_VIEW_NAME";
    alterMaterializedView(datasetName, materializedViewName);
  }

  public static void alterMaterializedView(String datasetName, String materializedViewName) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, materializedViewName);

      // Get existing materialized view
      Table table = bigquery.getTable(tableId);
      MaterializedViewDefinition materializedViewDefinition = table.getDefinition();
      // Alter materialized view
      materializedViewDefinition
          .toBuilder()
          .setEnableRefresh(true)
          .setRefreshIntervalMs(1000L)
          .build();
      table.toBuilder().setDefinition(materializedViewDefinition).build().update();
      System.out.println("Materialized view altered successfully");
    } catch (BigQueryException e) {
      System.out.println("Materialized view was not altered. \n" + e.toString());
    }
  }
}

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:

En Cloud Console, abre la página de BigQuery.

Ir a BigQuery

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

Java

Ver en GitHub Comentarios

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.TableId;

// Sample to delete materialized view
public class DeleteMaterializedView {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String materializedViewName = "MY_MATERIALIZED_VIEW_NAME";
    deleteMaterializedView(datasetName, materializedViewName);
  }

  public static void deleteMaterializedView(String datasetName, String materializedViewName) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, materializedViewName);

      boolean success = bigquery.delete(tableId);
      if (success) {
        System.out.println("Materialized view deleted successfully");
      } else {
        System.out.println("Materialized view was not found");
      }
    } catch (BigQueryException e) {
      System.out.println("Materialized view was not found. \n" + e.toString());
    }
  }
}

Supervisa las vistas materializadas

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

Supervisión de vistas materializadas

Para descubrir vistas materializadas, llama al método tables.list o consulta la tabla INFORMATION_SCHEMA.TABLES.

Para recuperar las propiedades de una vista materializada, llama al método tables.get o consulta la tabla INFORMATION_SCHEMA.TABLE_OPTIONS.

Las vistas materializadas no se enumeran en la tabla INFORMATION_SCHEMA.VIEWS.

Supervisión de trabajos de actualización de vistas materializadas

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.

Por ejemplo:

SELECT job_id, total_slot_ms, total_bytes_processed
FROM region-US.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE job_id LIKE "%materialized_view_refresh_%"
LIMIT 10

Vistas materializadas admitidas

Las vistas materializadas deben ser una agregación en una sola tabla. El uso de GROUP BY es opcional.

Las vistas materializadas usan una sintaxis de SQL restringida. Las consultas deben usar el siguiente patrón:

SELECT
  expression [ [ AS ] alias ] ] [, ...]
FROM from_item [, ...]
[ WHERE bool_expression ]
[ GROUP BY expression [, ...] ]

Actualmente, solo se admiten las siguientes funciones de agregación:

ANY_VALUE (pero no superior a STRUCT)
APPROX_COUNT_DISTINCT
ARRAY_AGG (pero no superior a ARRAY o STRUCT)
AVG
BIT_AND
BIT_OR
BIT_XOR
COUNT
COUNTIF
HLL_COUNT.INIT
LOGICAL_AND
LOGICAL_OR
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'))

Cláusula `FROM`

La cláusula FROM debe incluir una sola tabla y puede desanidar una o más expresiones de array.

Ejemplos

FROM mytable

FROM mytable AS t, t.struct_column.array_field AS x

FROM mytable AS t LEFT JOIN UNNEST(t.array_column) AS x

FROM mytable AS t, t.array_column AS x, x.array_field AS y

FROM mytable AS t, UNNEST(SPLIT(t.string_column)) AS x

WITH OFFSET no es compatible.

Patrones de reescritura de consultas admitidos

Los siguientes patrones de reescritura de consultas son compatibles con las consultas de tablas base y las definiciones de vistas materializadas.

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 usa la vista materializada analiza todos los datos de vista materializadas y un delta en la tabla base desde la última actualización. Esto se aplica si el optimizador de consultas hace referencia explícita a la vista materializada o la selecciona, lo que genera consultas más rápidas y menos costosas en ambos casos.

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 en la mayoría de los casos.

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

Si la tabla base está particionada, puedes particionar una vista materializada en la misma columna de partición. Para las particiones basadas en el tiempo, el nivel de detalle debe coincidir (por hora, diario, mensual o anual). Para las particiones de rango de número entero, la especificación de rango debe coincidir con exactitud. No puedes particionar una vista materializada en una tabla base no particionada.

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.

Las vistas materializadas se pueden agrupar en columnas arbitrarias, sujetas a las limitaciones de las tablas agrupadas de BigQuery.

Ejemplo 1

En este ejemplo, la tabla base está particionada en la columna transaction_time con particiones diarias. La vista materializada está particionada en la misma columna y agrupada en clústeres en la columna employee_id.

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);

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;

Ejemplo 2

En este ejemplo, la tabla base está particionada por tiempo de transferencia con particiones diarias. En la vista materializada, se selecciona el tiempo de transferencia como una columna llamada date. La vista materializada se agrupa por la columna date y se particiona con la misma columna.

CREATE TABLE project-id.my_dataset.my_base_table(
  employee_id INT64)
PARTITION BY _PARTITIONDATE
OPTIONS (partition_expiration_days = 2);

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;

Ejemplo 3

En este ejemplo, la tabla base está particionada en una columna TIMESTAMP llamada transaction_time, con particiones diarias. La vista materializada define una columna llamada transaction_hour mediante la función TIMESTAMP_TRUNC para truncar el valor a la hora más cercana. La vista materializada se agrupa por transaction_hour y, también, se particiona con ella.

Ten en cuenta lo siguiente:

La función de truncamiento que se aplica a la columna de partición debe ser al menos tan detallada como la partición de la tabla base. Por ejemplo, si la tabla base usa particiones diarias, la función de truncamiento no puede usar el nivel de detalle de MONTH ni YEAR.
En la especificación de la partición de la vista materializada, el nivel de detalle debe coincidir con la tabla base.

CREATE TABLE project-id.my_dataset.my_base_table(
  employee_id INT64,
  transaction_time TIMESTAMP)
PARTITION BY DATE(transaction_time);

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;

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 actualizan son aquellas que tienen anexos, actualizaciones o eliminaciones en las particiones correspondientes de la tabla base. (aunque en casos excepcionales, una eliminación o actualización en la tabla base puede hacer que se vuelva a calcular toda la vista). 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 puede 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.

Después de crear una vista materializada sobre una tabla base particionada, el vencimiento de la partición de la tabla no se puede cambiar. Para cambiar el vencimiento de la partición de la tabla base, primero debes borrar todas las vistas materializadas que creaste en esa tabla.

Actualización de vistas materializadas

Puedes especificar si BigQuery usa actualizaciones automáticas o manuales para actualizar los resultados calculados con anterioridad de una tabla base. Si no quieres usar los valores predeterminados, puedes establecer la configuración de actualización cuando crees una vista materializada. 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, pero no con más frecuencia cada 30 minutos. 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)

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.

Mejor esfuerzo.

La actualización automática se realiza según el criterio del mejor esfuerzo. BigQuery intenta iniciar una actualización dentro de los 5 minutos posteriores a un cambio en la tabla base (si la actualización anterior se realizó antes de 30 minutos), pero no garantiza que la actualización desde que comenzó en ese momento y tampoco garantiza cuándo se completará. (La consulta de vistas materializadas refleja el estado más reciente de la tabla base, pero, si la vista no se actualizó recientemente, el costo o la latencia de la consulta puede ser más alto de lo esperado).

La actualización automática se trata de manera similar a una consulta con prioridad por lotes. Si el proyecto de la vista materializada no tiene la capacidad en este momento, la actualización se retrasa. Si el proyecto contiene muchas vistas cuya actualización resulta alta, es posible que cada vista individual se retrase de forma considerable en relación con su tabla base.

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 finaliza cuando se completa la actualización.

CALL BQ.REFRESH_MATERIALIZED_VIEW('project-id.my_dataset.my_mv_table')

Próximos pasos

Obtén más información sobre las prácticas recomendadas de vistas materializadas.
Lee las Preguntas frecuentes sobre las vistas materializadas.

Crea y usa vistas materializadas

Permisos necesarios

Crea vistas materializadas

Console

bq

API

Java

Control de acceso

Consulta de vistas materializadas

Altera una vista materializada

Console

bq

Java

Manipula una vista materializada

Java

Borra una vista materializada

Console

bq

API

Java

Supervisa las vistas materializadas

Supervisión de vistas materializadas

Supervisión de trabajos de actualización de vistas materializadas

Vistas materializadas admitidas

Cláusula FROM

Patrones de reescritura de consultas admitidos

Patrón 1

Patrón 2

Patrón 3

Patrón 4

Actualizaciones incrementales

Trabaja con tablas particionadas y agrupadas

Ejemplo 1

Ejemplo 2

Ejemplo 3

Alineación de particiones

Vencimiento de la partición

Actualización de vistas materializadas

Actualización automática

Configura la limitación de frecuencia

Mejor esfuerzo.

Actualización manual

Próximos pasos

Cláusula `FROM`