Crea vistas materializadas

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

Antes de comenzar

Otorga funciones de administración de identidades y accesos (IAM) que les brindan a los usuarios los permisos necesarios para realizar cada tarea de este documento.

Permisos necesarios

Para crear vistas materializadas, necesitas el permiso bigquery.tables.create de IAM.

Cada uno de los siguientes roles predefinidos de IAM incluye los permisos que necesitas para crear una vista materializada:

  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery.admin

Para obtener más información sobre Identity and Access Management (IAM) de BigQuery, consulta Control de acceso con IAM.

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 integer

Para crear una vista materializada, selecciona una de las siguientes opciones:

SQL

Usa la sentencia CREATE MATERIALIZED VIEW. En el siguiente ejemplo, se crea una vista materializada para la cantidad de clics de cada ID del producto:

  1. En la consola, ve a la página BigQuery.

    Ir a BigQuery

  2. En el editor de consultas, ingresa la siguiente sentencia:

    CREATE MATERIALIZED VIEW  myproject.mydataset.my_mv_table AS (
      SELECT
        product_id,
        SUM(clicks) AS sum_clicks
      FROM
        myproject.mydataset.my_base_table
      GROUP BY
        product_id
    );
    

  3. Haz clic en Ejecutar.

Si deseas obtener más información sobre cómo ejecutar consultas, visita Ejecuta consultas interactivas.

bq

Usa el comando bq query y proporciona la declaración 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'

Donde:

  • 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 la frecuencia de actualización máxima. Para obtener más información sobre el significado de estos parámetros, consulta Actualiza vistas materializadas.

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"
  }
}

Donde:

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

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

Cuando la vista materializada se crea de forma correcta, aparece en el panel Explorador de BigQuery en la consola de Google Cloud. Las vistas materializadas se enumeran en el panel Explorador dentro de la consola. Por ejemplo, a continuación se muestra cómo aparece el esquema de una vista materializada:

Esquema de vista materializada en la consola de Google Cloud

A menos que inhabilites las actualizaciones automáticas, BigQuery inicia una actualización completa y asíncrona para la vista materializada. La consulta finalizará rápido, pero la actualización inicial podría continuar ejecutándose.

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.

Para consultar una vista materializada, se necesita acceso a la vista y a sus tablas base. Para compartir una vista materializada, puedes otorgar permisos a las tablas base o configurar una vista materializada como vista autorizada. Para obtener más información, consulta Vistas autorizadas.

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

Compatibilidad de las consultas de vistas materializadas

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

[ WITH cte [, …]]
SELECT  [{ ALL | DISTINCT }]
  expression [ [ AS ] alias ] ] [, ...]
FROM from_item [, ...]
[ WHERE bool_expression ]
[ GROUP BY expression [, ...] ]

from_item:
    {
      table_name [ as_alias ]
      | { join_operation | ( join_operation ) }
      | field_path
      | unnest_operator
      | cte_name [ as_alias ]
    }

as_alias:
    [ AS ] alias

Limitaciones de las consultas

Las vistas materializadas tienen las siguientes limitaciones.

Requisitos de agregados

Los agregados en la consulta de la vista materializada deben ser resultados. No se admite el procesamiento ni el filtrado basado en un valor agregado. Por ejemplo, no se admite la creación de una vista a partir de la siguiente consulta porque produce un valor calculado a partir de un agregado, COUNT(*) / 10 as cnt.

SELECT TIMESTAMP_TRUNC(ts, HOUR) AS ts_hour, COUNT(*) / 10 AS cnt
FROM mydataset.mytable
GROUP BY ts_hour;

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

Características de SQL no compatibles

Las siguientes funciones de SQL no son compatibles con las vistas materializadas:

  • Combinaciones externas izquierdas, derechas y completas.
  • Uniones de tablas consigo mismas (uniones que usan la misma tabla más de una vez).
  • Funciones analíticas
  • Funciones no deterministas, como RAND(), CURRENT_DATE() o CURRENT_TIME().
  • Funciones definidas por el usuario (UDF)
  • TABLESAMPLE.
  • FOR SYSTEM_TIME AS OF.

Cláusula WITH y expresiones de tabla comunes (CTE)

Las vistas materializadas admiten cláusulas WITH y expresiones de tabla comunes. Las vistas materializadas con cláusulas WITH deben seguir el patrón y las limitaciones de las vistas materializadas sin cláusulas WITH.

Examples

En el siguiente ejemplo, se muestra una vista materializada con una cláusula WITH.

WITH tmp AS (
  SELECT TIMESTAMP_TRUNC(ts, HOUR) AS ts_hour, *
  FROM mydataset.mytable
)
SELECT ts_hour, COUNT(*) AS cnt
FROM tmp
GROUP BY ts_hour;

En el siguiente ejemplo, se muestra una vista materializada mediante una cláusula WITH que no es compatible, ya que contiene dos cláusulas GROUP BY:

WITH tmp AS (
  SELECT city, COUNT(*) AS population
  FROM mydataset.mytable
  GROUP BY city
)
SELECT population, COUNT(*) AS cnt
GROUP BY population;

Vistas materializadas particionadas

Las vistas materializadas en tablas particionadas se pueden particionar. La partición de una vista materializada es similar a particionar una tabla normal, ya que proporciona beneficios cuando las consultas acceden a menudo a un subconjunto de las particiones. Además, particionar una vista materializada puede mejorar su comportamiento cuando se modifican o borran datos de la tabla o tablas base. Para obtener más información, consulta Alineación de particiones.

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 particionarse por esa columna. Si no especificas de forma explícita la partición cuando creas la vista materializada, esta no se particiona.

Si la tabla base está particionada, considera particionar la vista materializada también para reducir el costo del mantenimiento del trabajo de actualización y el costo de la consulta.

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.

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 my_project.my_dataset.my_base_table(
  employee_id INT64,
  transaction_time TIMESTAMP)
  PARTITION BY DATE(transaction_time)
  OPTIONS (partition_expiration_days = 2);

CREATE MATERIALIZED VIEW my_project.my_dataset.my_mv_table
  PARTITION BY DATE(transaction_time)
  CLUSTER BY employee_id
AS (
  SELECT
    employee_id,
    transaction_time,
    COUNT(employee_id) AS cnt
  FROM
    my_dataset.my_base_table
  GROUP BY
    employee_id, transaction_time
);

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 MATERIALIZED VIEW my_project.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
    employee_id,
    date
);

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 por 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 my_project.my_dataset.my_base_table (
  employee_id INT64,
  transaction_time TIMESTAMP)
  PARTITION BY DATE(transaction_time);

CREATE MATERIALIZED VIEW my_project.my_dataset.my_mv_table
  PARTITION BY DATE(transaction_hour)
AS (
  SELECT
    employee_id,
    TIMESTAMP_TRUNC(transaction_time, HOUR) AS transaction_hour,
    COUNT(employee_id) AS cnt
  FROM
    my_dataset.my_base_table
  GROUP BY
    employee_id,
    transaction_hour
);

Vistas materializadas de clústeres

Puedes agrupar vistas materializadas en clústeres por sus columnas de resultados, sujetas a las limitaciones de las tablas agrupadas en clústeres de BigQuery. Las columnas de resultados agregadas no se pueden usar como columnas de agrupamiento en clústeres. Agregar columnas de agrupamiento en clústeres a vistas materializadas puede mejorar el rendimiento de las consultas que incluyen filtros en esas columnas.

Consideraciones para crear vistas materializadas

Qué vistas materializadas crear

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

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

Como otro ejemplo, los usuarios suelen usar filtros de fecha, ya sea por fecha específica, como WHERE order_date = CURRENT_DATE(), o por período, como WHERE order_date BETWEEN '2019-10-01' AND '2019-10-31'. Agrega un filtro de período en la vista materializada que cubra los períodos esperados de la consulta:

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

Uniones

Las siguientes recomendaciones se aplican a las vistas materializadas con JOIN.

Coloca primero la tabla que cambia con mayor frecuencia

Asegúrate de que la tabla más grande o con cambios más frecuentes sea la primera a la que se hace referencia en la consulta de vista o la que está más a la izquierda. Las vistas materializadas con uniones admiten consultas incrementales y se actualizan cuando se agrega la primera tabla o la que está más a la izquierda en la consulta, pero los cambios en otras tablas invalidan por completo la caché de la vista. En los esquemas de estrella o copo de nieve, la primera tabla o la que está más a la izquierda debe ser la tabla de hechos.

Evita uniones en claves de agrupamiento en clústeres

Las vistas materializadas con uniones funcionan mejor en los casos en que los datos se agregan en gran medida o la consulta de unión original es costosa. Para las consultas selectivas, BigQuery suele estar preparado para realizar la unión de manera eficiente y no se necesita una vista materializada. Por ejemplo, considera las siguientes definiciones de vistas materializadas.

CREATE MATERIALIZED VIEW dataset.mv
  CLUSTER BY s_market_id
AS (
  SELECT
    s_market_id,
    s_country,
    SUM(ss_net_paid) AS sum_sales,
    COUNT(*) AS cnt_sales
  FROM dataset.store_sales
  INNER JOIN dataset.store
    ON ss_store_sk = s_store_sk
  GROUP BY s_market_id, s_country
);

Supongamos que store_sales está agrupado en ss_store_sk en clústeres y, a menudo, ejecutas consultas como las siguientes:

SELECT
  SUM(ss_net_paid)
FROM dataset.store_sales
INNER JOIN dataset.store
ON ss_store_sk = s_store_sk
WHERE s_country = 'Germany';

La vista materializada podría no sea tan eficiente como la consulta original. Para obtener mejores resultados, experimenta con un conjunto representativo de consultas, con y sin la vista materializada.

Próximos pasos