Crear vistas materializadas

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

Antes de empezar

Concede roles de gestión de identidades y accesos (IAM) que proporcionen a los usuarios los permisos necesarios para realizar cada tarea de este documento.

Permisos obligatorios

Para crear vistas materializadas, necesitas el permiso de gestión de identidades y accesos bigquery.tables.create.

Cada uno de los siguientes roles de gestión de identidades y accesos predefinidos incluye los permisos que necesitas para crear una vista materializada:

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

Para obtener más información sobre la gestión de identidades y accesos (IAM) de BigQuery, consulta el artículo sobre el control de acceso con IAM.

Crear vistas materializadas

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

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

resource "google_bigquery_dataset" "default" {
  dataset_id                      = "mydataset"
  default_partition_expiration_ms = 2592000000  # 30 days
  default_table_expiration_ms     = 31536000000 # 365 days
  description                     = "dataset description"
  location                        = "US"
  max_time_travel_hours           = 96 # 4 days

  labels = {
    billing_group = "accounting",
    pii           = "sensitive"
  }
}

resource "google_bigquery_table" "default" {
  dataset_id = google_bigquery_dataset.default.dataset_id
  table_id   = "my_materialized_view"

  materialized_view {
    query                            = "SELECT ID, description, date_created FROM `myproject.orders.items`"
    enable_refresh                   = "true"
    refresh_interval_ms              = 172800000 # 2 days
    allow_non_incremental_definition = "false"
  }

}

{
  "kind": "bigquery#table",
  "tableReference": {
    "projectId": "PROJECT_ID",
    "datasetId": "DATASET",
    "tableId": "MATERIALIZED_VIEW_NAME"
  },
  "materializedView": {
    "query": "QUERY_EXPRESSION"
  }
}

{
  "kind": "bigquery#table",
  "tableReference": {
    "projectId": "myproject",
    "datasetId": "mydataset",
    "tableId": "my_mv"
  },
  "materializedView": {
    "query": "select product_id,sum(clicks) as
                sum_clicks from myproject.mydataset.my_source_table
                group by 1"
  }
}

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

Una vez que se haya creado correctamente la vista materializada, aparecerá en el panel Explorador de BigQuery en la Google Cloud consola. En el siguiente ejemplo se muestra un esquema de una vista materializada:

A menos que inhabilite la actualización automática, BigQuery iniciará una actualización completa asíncrona de la vista materializada. La consulta finaliza rápidamente, pero la actualización inicial puede seguir ejecutándose.

Control de acceso

Puedes conceder acceso a una vista materializada a nivel de conjunto de datos, vista o columna. También puedes definir el acceso en un nivel superior de la jerarquía de recursos de gestión de identidades y accesos.

Para consultar una vista materializada, se necesita acceso a la vista y a sus tablas base. Para compartir una vista materializada, puedes conceder 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 en BigQuery, consulta Vistas autorizadas.

Compatibilidad con 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 agregación

Los agregados de la consulta de la vista materializada deben ser resultados. No se admite el cálculo, el filtrado ni la combinación basados en un valor agregado. Por ejemplo, no se puede crear 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;

Solo se admiten las siguientes funciones de agregación:

• ANY_VALUE (pero no más de STRUCT)
• APPROX_COUNT_DISTINCT
• ARRAY_AGG (pero no más de ARRAY o STRUCT)
• AVG
• BIT_AND
• BIT_OR
• BIT_XOR
• COUNT
• COUNTIF
• HLL_COUNT.INIT
• LOGICAL_AND
• LOGICAL_OR
• MAX
• MIN
• MAX_BY (pero no más de STRUCT)
• MIN_BY (pero no más de STRUCT)
• SUM

Funciones de SQL no admitidas

Las siguientes funciones de SQL no se admiten en las vistas materializadas:

• UNION ALL (Asistencia en science vista previa)
• LEFT OUTER JOIN (asistencia en la versión science preliminar)
• RIGHT/FULL OUTER JOIN.
• Combinaciones automáticas, también conocidas como el uso de un JOIN en la misma tabla más de una vez.
• Funciones de ventana.
• ARRAY subconsultas.
• Funciones no deterministas, como RAND(), CURRENT_DATE(), SESSION_USER() o CURRENT_TIME().
• Funciones definidas por el usuario (UDF).
• TABLESAMPLE.
• FOR SYSTEM_TIME AS OF.
• Funciones de IA generativa.

Compatibilidad con LEFT OUTER JOIN y UNION ALL

Para solicitar comentarios o asistencia sobre esta función, envía un correo a bq-mv-help @google.com.

Las vistas materializadas incrementales admiten LEFT OUTER JOIN y UNION ALL. Las vistas materializadas con las instrucciones LEFT OUTER JOIN y UNION ALL comparten las limitaciones de otras vistas materializadas incrementales. Además, smart tuning no se admite en las vistas materializadas con union all o left outer join.

Ejemplos

En el siguiente ejemplo se crea una vista materializada incremental agregada con LEFT JOIN. Esta vista se actualiza de forma incremental cuando se añaden datos a la tabla de la izquierda.

CREATE MATERIALIZED VIEW dataset.mv
AS (
  SELECT
    s_store_sk,
    s_country,
    s_zip,
    SUM(ss_net_paid) AS sum_sales,
  FROM dataset.store_sales
  LEFT JOIN dataset.store
    ON ss_store_sk = s_store_sk
  GROUP BY 1, 2, 3
);

En el siguiente ejemplo se crea una vista materializada incremental agregada con UNION ALL. Esta vista se actualiza de forma incremental cuando se añaden datos a una o a ambas tablas. Para obtener más información sobre las actualizaciones incrementales, consulta Actualizaciones incrementales.

CREATE MATERIALIZED VIEW dataset.mv PARTITION BY DATE(ts_hour)
AS (
  SELECT
    SELECT TIMESTAMP_TRUNC(ts, HOUR) AS ts_hour, SUM(sales) sum_sales
  FROM
    (SELECT ts, sales from dataset.table1 UNION ALL
     SELECT ts, sales from dataset.table2)
  GROUP BY 1
);

Restricciones de control de acceso

• Si la consulta de un usuario sobre una vista materializada incluye columnas de la tabla base a las que no puede acceder debido a la seguridad a nivel de columna, la consulta falla y se muestra el mensaje Access Denied.
• Si un usuario consulta una vista materializada, pero no tiene acceso completo a todas las filas de las tablas base de la vista materializada, BigQuery ejecuta la consulta en las tablas base en lugar de leer los datos de la vista materializada. De esta forma, la consulta respeta todas las restricciones de control de acceso. Esta limitación también se aplica al consultar tablas con columnas enmascaradas.

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

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.

Ejemplos

En el siguiente ejemplo se muestra una vista materializada que usa 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 que usa una cláusula WITH que no se admite porque 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 sobre tablas de BigLake

Para crear vistas materializadas sobre tablas de BigLake, la tabla de BigLake debe tener habilitado el almacenamiento en caché de metadatos sobre los datos de Cloud Storage y la vista materializada debe tener un valor de opción max_staleness mayor que la tabla base. Las vistas materializadas de las tablas de BigLake admiten el mismo conjunto de consultas que otras vistas materializadas.

Ejemplo

Creación de una vista agregada simple mediante una tabla base de BigLake:

CREATE MATERIALIZED VIEW sample_dataset.sample_mv
    OPTIONS (max_staleness=INTERVAL "0:30:0" HOUR TO SECOND)
AS SELECT COUNT(*) cnt
FROM dataset.biglake_base_table;

Para obtener información sobre las limitaciones de las vistas materializadas en tablas de BigLake, consulte Vistas materializadas en tablas de BigLake.

Vistas materializadas sobre tablas externas de Apache Iceberg

Puedes hacer referencia a tablas de Iceberg grandes en vistas materializadas en lugar de migrar esos datos al almacenamiento gestionado por BigQuery.

Crear una vista materializada sobre una tabla Iceberg

En el siguiente ejemplo se crea una vista materializada alineada con las particiones sobre una tabla Iceberg base particionada:

CREATE MATERIALIZED VIEW