Crea vistas materializadas

Nota: Es posible que esta función no esté disponible cuando se usan reservas que se crearon con determinadas ediciones de BigQuery. Para obtener más información sobre qué funciones están habilitadas en cada edición, consulta Introducción a las ediciones de BigQuery.

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 Identity and Access Management (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

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 de Google Cloud, ve a la página de BigQuery.

   Ir a BigQuery

2. En el editor de consultas, escribe la siguiente oración:

   CREATE MATERIALIZED VIEW PROJECT_ID.DATASET.MATERIALIZED_VIEW_NAME AS (
     QUERY_EXPRESSION
   );

   Reemplaza lo siguiente:

   • PROJECT_ID: Es el nombre de tu proyecto en el que deseas crear la réplica de vista materializada, como por ejemplo, myproject.
   • DATASET: el nombre del conjunto de datos de BigQuery en el que deseas crear la vista materializada, por ejemplo, mydataset. Si creas una vista materializada en una tabla de BigLake de Amazon Simple Storage Service (Amazon S3) (versión preliminar), asegúrate de que el conjunto de datos esté en una región compatible.
   • MATERIALIZED_VIEW_NAME: el nombre de la vista materializada que deseas crear, por ejemplo, my_mv.
   • QUERY_EXPRESSION: la expresión de consulta de GoogleSQL que define la vista materializada, por ejemplo, SELECT product_id, SUM(clicks) AS sum_clicks FROM mydataset.my_source_table.

3. Haz clic en Ejecutar.

Para obtener más información sobre cómo ejecutar consultas, visita Ejecuta una consulta interactiva.

Ejemplo

En el siguiente ejemplo, se crea una vista materializada para la cantidad de clics de cada ID del producto:

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

Terraform

Usa el recurso google_bigquery_table.

Nota: Para crear objetos de BigQuery a través de Terraform, debes habilitar la API de Cloud Resource Manager

.

Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Si deseas obtener más información, consulta Configura la autenticación para bibliotecas cliente.

En el siguiente ejemplo, se crea una vista llamada my_materialized_view:

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"
  deletion_protection = false # set to "true" in production

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

}

Para aplicar tu configuración de Terraform en un proyecto de Google Cloud, completa los pasos de las siguientes secciones.

Prepara Cloud Shell

1. Inicia Cloud Shell

2. Establece el proyecto de Google Cloud predeterminado en el que deseas aplicar tus configuraciones de Terraform.

   Solo necesitas ejecutar este comando una vez por proyecto y puedes ejecutarlo en cualquier directorio.

   export GOOGLE_CLOUD_PROJECT=PROJECT_ID

   Las variables de entorno se anulan si configuras valores explícitos en el archivo de configuración de Terraform.

Prepara el directorio

Cada archivo de configuración de Terraform debe tener su propio directorio (también llamado módulo raíz).

1. En Cloud Shell, crea un directorio y un archivo nuevo dentro de ese directorio. El nombre del archivo debe tener la extensión .tf, por ejemplo, main.tf. En este instructivo, el archivo se denomina main.tf.

   mkdir DIRECTORY && cd DIRECTORY && touch main.tf

2. Si sigues un instructivo, puedes copiar el código de muestra en cada sección o paso.

   Copia el código de muestra en el main.tf recién creado.

   De manera opcional, copia el código de GitHub. Esto se recomienda cuando el fragmento de Terraform es parte de una solución de extremo a extremo.

3. Revisa y modifica los parámetros de muestra que se aplicarán a tu entorno.
4. Guarda los cambios.
5. Inicializa Terraform. Solo debes hacerlo una vez por directorio.

   terraform init

   De manera opcional, incluye la opción -upgrade para usar la última versión del proveedor de Google:

   terraform init -upgrade

Aplica los cambios

1. Revisa la configuración y verifica que los recursos que creará o actualizará Terraform coincidan con tus expectativas:

   terraform plan

   Corrige la configuración según sea necesario.

2. Para aplicar la configuración de Terraform, ejecuta el siguiente comando y, luego, escribe yes cuando se te solicite:

   terraform apply

   Espera hasta que Terraform muestre el mensaje “¡Aplicación completa!”.

3. Abre tu proyecto de Google Cloud para ver los resultados. En la consola de Google Cloud, navega a tus recursos en la IU para asegurarte de que Terraform los haya creado o actualizado.

Nota: Por lo general, las muestras de Terraform suponen que las APIs necesarias están habilitadas en tu proyecto de Google Cloud.

API

Llama al método tables.insert y pasa un recurso Table con un campo materializedView definido:

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

Reemplaza lo siguiente:

• PROJECT_ID: Es el nombre de tu proyecto en el que deseas crear la réplica de vista materializada, como por ejemplo, myproject.
• DATASET: el nombre del conjunto de datos de BigQuery en el que deseas crear la vista materializada, por ejemplo, mydataset. Si creas una vista materializada en una tabla de BigLake de Amazon Simple Storage Service (Amazon S3) (versión preliminar), asegúrate de que el conjunto de datos esté en una región compatible.
• MATERIALIZED_VIEW_NAME: el nombre de la vista materializada que deseas crear, por ejemplo, my_mv.
• QUERY_EXPRESSION: la expresión de consulta de GoogleSQL que define la vista materializada, por ejemplo, SELECT product_id, SUM(clicks) AS sum_clicks FROM mydataset.my_source_table.

Ejemplo

En el siguiente ejemplo, se crea una vista materializada para la cantidad de clics de cada ID del producto:

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

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.

Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Si deseas obtener más información, consulta Configura la autenticación para bibliotecas cliente.

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. En el siguiente ejemplo, se muestra un esquema de vista materializada:

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

Nota: Cada tabla base está limitada a 20 vistas materializadas dentro del mismo conjunto de datos, 100 vistas materializadas dentro del mismo proyecto y 500 vistas materializadas dentro de la misma organización.

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 en BigQuery, consulta Vistas autorizadas.

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, el filtrado ni la unión en función de 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
• MAX_BY (pero no superior a STRUCT)
• MIN_BY (pero no superior a STRUCT)
• SUM

Características de SQL no compatibles

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

• UNION ALL. (Compatibilidad en la vista previa de )
• LEFT OUTER JOIN. (Compatibilidad en la vista previa de )
• RIGHT/FULL OUTER JOIN.
• Uniones de tablas consigo mismas, también conocidas como usar JOIN en la misma tabla más de una vez.
• Funciones analíticas.
• Subconsultas ARRAY.
• Funciones no deterministas, como RAND(), CURRENT_DATE(), SESSION_USER() o CURRENT_TIME().
• Funciones definidas por el usuario (UDF).
• TABLESAMPLE.
• FOR SYSTEM_TIME AS OF.

Compatibilidad con LEFT OUTER JOIN y UNION ALL

Vista previa

Esta función está sujeta a las "Condiciones de las Ofertas de Fase Previa a la DG" de la sección Condiciones Generales del Servicio de las Condiciones Específicas del Servicio. Las funciones de fase previa a la DG están disponibles “tal cual” y podrían tener asistencia limitada. Para obtener más información, consulta las descripciones de la etapa de lanzamiento.

Para solicitar comentarios o asistencia para esta función, envía un correo electrónico a [e-mail].

Las vistas materializadas incrementales admiten LEFT OUTER JOIN y UNION ALL. Las vistas materializadas con declaraciones LEFT OUTER JOIN y UNION ALL comparten las limitaciones de otras vistas materializadas incrementales. Además, el ajuste inteligente no es compatible con las vistas materializadas con unir todo o unión externa izquierda.

Ejemplos

En el siguiente ejemplo, se crea una vista materializada incremental agregada con un LEFT JOIN. Esta vista se actualiza de forma incremental cuando los datos se agregan a la tabla 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 un UNION ALL. Esta vista se actualiza de forma incremental cuando los datos se agregan a una o 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 de una vista materializada incluye columnas de tabla base a las que no se puede acceder debido a la seguridad a nivel de columna, la consulta falla con 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 las vistas materializadas, BigQuery ejecuta la consulta en las tablas base en lugar de leer los datos de vistas materializadas. Esto garantiza que la consulta respete todas las restricciones del control de acceso. Esta limitación también se aplica cuando se consultan tablas con columnas enmascaradas por datos.

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.

Ejemplos

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 sobre las tablas de BigLake

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

Ejemplo

Creación de una vista agregada simple con 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 detalles sobre las limitaciones de las vistas materializadas en las tablas de BigLake, consulta Vistas materializadas sobre las tablas de BigLake.

Vistas materializadas en las tablas de Apache Iceberg

Vista previa

Esta función está sujeta a las "Condiciones de las Ofertas de Fase Previa a la DG" de la sección Condiciones Generales del Servicio de las Condiciones Específicas del Servicio. Las funciones de fase previa a la DG están disponibles “tal cual” y podrían tener asistencia limitada. Para obtener más información, consulta las descripciones de la etapa de lanzamiento.

Para solicitar comentarios o asistencia para esta función, envía un correo electrónico a [e-mail].

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

Crea una vista materializada en una tabla de Iceberg

Para crear una vista materializada en Iceberg, sigue estos pasos:

1. Obtén una tabla de Iceberg a través de uno de los siguientes métodos:

   • Crea una tabla de Iceberg con el archivo de metadatos JSON.
   • Crea una tabla de Iceberg con BigLake Metastore.
   • Descubre en conjuntos de datos federados de AWS Glue.

   Ejemplo

   CREATE EXTERNAL TABLE mydataset.myicebergtable
     WITH CONNECTION `myproject.us.myconnection`
     OPTIONS (
           format = 'ICEBERG',
           uris = ["gs://mybucket/mydata/mytable/metadata/iceberg.metadata.json"]
     )
   

2. Haz referencia a tu tabla de Iceberg con las siguientes especificaciones de partición:

   "partition-specs" : [ {
      "spec-id" : 0,
      "fields" : [ {
       "name" : "birth_month",
       "transform" : "month",
       "source-id" : 3,
       "field-id" : 1000
   } ]
   

3. Crea una vista materializada alineada con particiones:

   CREATE MATERIALIZED VIEW mydataset.myicebergmv
     PARTITION BY DATE_TRUNC(birth_month, MONTH)
   AS
     SELECT * FROM mydataset.myicebergtable;
   

Limitaciones

Además de las limitaciones de las tablas estándar de Iceberg, las vistas materializadas en las tablas de Iceberg tienen las siguientes limitaciones:

• Puedes crear una vista materializada que esté alineada con la partición de la tabla base. Sin embargo, la vista materializada solo admite la transformación de partición basada en el tiempo, por ejemplo, YEAR, MONTH, DAY y HOUR.
• El nivel de detalle de la partición de la vista materializada no puede ser más detallado que el nivel de detalle de la partición de la tabla base. Por ejemplo, si particionas la tabla base anualmente con la columna birth_date, la creación de una vista materializada con PARTITION BY DATE_TRUNC(birth_date, MONTH) no funcionará.
• Cualquier cambio de esquema invalida la vista materializada.
• Se admiten las evoluciones de particiones. Sin embargo, cambiar las columnas de partición de una tabla base sin volver a crear la vista materializada puede generar una invalidación completa que no se puede corregir con la actualización.
• Debe haber al menos una instantánea en la tabla base.
• La tabla de Iceberg debe ser una tabla de BigLake, por ejemplo, una tabla externa autorizada.
• Si los Controles del servicio de VPC están habilitados, se deben agregar las cuentas de servicio de la tabla externa autorizada a tus reglas de entrada. De lo contrario, los Controles del servicio de VPC bloquean la actualización automática en segundo plano para la vista materializada.

El archivo metadata.json de tu tabla de Iceberg debe tener las siguientes especificaciones. Sin estas especificaciones, tus consultas analizan la tabla base y no usan el resultado materializado.

• En los metadatos de tablas:

  • current-snapshot-id
  • current-schema-id
  • snapshots
  • snapshot-log

• En las Instantáneas:

  • parent-snapshot-id (si está disponible)
  • schema-id
  • operation (en el campo summary)

• Partición (para la vista materializada particionada)

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.

Precaución: Una vista materializada no particionada basada en una tabla con vencimiento de partición se invalida y se debe actualizar por completo cuando una partición vence. Por lo tanto, debes particionar la vista materializada para evitar los costos adicionales de actualización y consulta.

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.

Vistas lógicas de referencia

Vista previa

Esta función está sujeta a las "Condiciones de las Ofertas de Fase Previa a la DG" de la sección Condiciones Generales del Servicio de las Condiciones Específicas del Servicio. Las funciones de fase previa a la DG están disponibles “tal cual” y podrían tener asistencia limitada. Para obtener más información, consulta las descripciones de la etapa de lanzamiento.

Para solicitar comentarios o asistencia para esta función, envía un correo electrónico a [e-mail].

Las consultas de vistas materializadas pueden hacer referencia a vistas lógicas, pero están sujetas a las siguientes limitaciones:

• Se aplican limitaciones de vistas materializadas.
• Si la vista lógica cambia, la vista materializada deja de ser válida y se debe actualizar por completo.
• No se admite el ajuste inteligente.

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. Las vistas materializadas son más efectivas cuando entregan un conjunto amplio de consultas en lugar de un solo patrón de consulta específico.

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.

Usa vistas materializadas con la opción max_staleness

La opción de vista materializada max_staleness te ayuda a lograr un rendimiento alto y coherente de las consultas con costos controlados cuando se procesan conjuntos de datos grandes que cambian con frecuencia. Con el parámetro max_staleness, puedes reducir el costo y la latencia de tus consultas; para ello, configura un intervalo de tiempo en el que se acepte la inactividad de los datos de los resultados de la consulta. Este comportamiento puede ser útil para los paneles y los informes en los que los resultados de las consultas completamente actualizados no son esenciales.

Inactividad de los datos

Cuando consultas una vista materializada con la opción max_staleness establecida, BigQuery muestra el resultado según el valor de max_staleness y la hora en que se produjo la última actualización.

Si la última actualización se produjo dentro del intervalo de max_staleness, BigQuery mostrará los datos directamente desde la vista materializada sin leer las tablas base. Por ejemplo, esto se aplica si tu intervalo de max_staleness es de 4 horas y la última actualización se produjo hace 2 horas.

Si la última actualización se produjo fuera del intervalo de max_staleness, BigQuery lee los datos de la vista materializada, los combina con los cambios en la tabla base desde la última actualización y muestra el resultado combinado. Es posible que este resultado combinado siga inactivo hasta el intervalo de max_staleness. Por ejemplo, esto se aplica si tu intervalo de max_staleness es de 4 horas y la última actualización se produjo hace 7 horas.

Crear con la opción max_staleness

Selecciona una de las opciones siguientes:

SQL

Para crear una vista materializada con la opción max_staleness, agrega una cláusula OPTIONS a la instrucción de DDL cuando crees la vista materializada:

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

   Ir a BigQuery

2. En el editor de consultas, escribe la siguiente oración:

   CREATE MATERIALIZED VIEW  project-id.my_dataset.my_mv_table
     OPTIONS (enable_refresh = true, refresh_interval_minutes = 60,
       max_staleness = INTERVAL "4:0:0" HOUR TO SECOND)
   AS SELECT
     employee_id,
     DATE(transaction_time),
     COUNT(1) AS count
   FROM my_dataset.my_base_table
   GROUP BY 1, 2;

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

   • Haz clic en Ejecutar.

Para obtener más información sobre cómo ejecutar consultas, visita Ejecuta una consulta interactiva.

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"
  }
  "maxStaleness": "4:0:0"
}

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

Aplicar la opción max_staleness

Puedes aplicar este parámetro a las vistas materializadas existentes con la declaración ALTER MATERIALIZED VIEW. Por ejemplo:

ALTER MATERIALIZED VIEW project-id.my_dataset.my_mv_table
SET OPTIONS (enable_refresh = true, refresh_interval_minutes = 120,
  max_staleness = INTERVAL "8:0:0" HOUR TO SECOND);

Consultar con max_staleness

Puedes consultar vistas materializadas con la opción max_staleness como lo harías con cualquier otra vista materializada, vista lógica o tabla.

Por ejemplo:

SELECT * FROM  project-id.my_dataset.my_mv_table

Esta consulta muestra datos de la última actualización si los datos no son anteriores al parámetro max_staleness. Si la vista materializada no se actualiza dentro del intervalo max_staleness, BigQuery combina los resultados de la última actualización disponible con los cambios de tabla base para mostrar resultados dentro del intervalo max_staleness.

Transmisión de datos y max_staleness resultados

Si transmites datos a la tabla base de una vista materializada con la opción max_staleness, la consulta de la vista materializada puede excluir los registros que se transmitieron a la tabla antes del inicio del período de inactividad. Como resultado, es posible que una vista materializada que incluya datos de varias tablas y la opción max_staleness no represente una instantánea de un momento determinado de esas tablas.

Ajuste inteligente y la opción max_staleness

El ajuste inteligente reescribe automáticamente las consultas para usar las vistas materializadas siempre que sea posible, sin importar la opción max_staleness, incluso si la consulta no hace referencia a una vista materializada. La opción max_staleness en una vista materializada no afecta los resultados de la consulta reescrita. La opción max_staleness solo afecta a las consultas que consultan directamente la vista materializada.

Administra la inactividad y la frecuencia de actualización

Debes configurar max_staleness según tus requisitos. Para evitar leer datos de tablas base, configura el intervalo de actualización de modo que la actualización se realice dentro del intervalo de inactividad. Puedes dar cuenta del tiempo de ejecución de actualización promedio más un margen de crecimiento.

Por ejemplo, si se requiere una hora para actualizar la vista materializada y deseas un búfer de una hora a fin de crecer, debes establecer el intervalo de actualización en dos horas. Esta configuración garantiza que la actualización se realice dentro del máximo de cuatro horas de inactividad del informe.

CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table
OPTIONS (enable_refresh = true, refresh_interval_minutes = 120, max_staleness =
INTERVAL "4:0:0" HOUR TO SECOND)
AS SELECT
  employee_id,
  DATE(transaction_time),
  COUNT(1) AS cnt
FROM my_dataset.my_base_table
GROUP BY 1, 2;

Vistas materializadas no incrementales

Las vistas materializadas no incrementales admiten la mayoría de las consultas de SQL, incluidas las cláusulas OUTER JOIN, UNION y HAVING, y las funciones analíticas. Para determinar si se usó una vista materializada en tu consulta, verifica las estimaciones de costos a través de una prueba de validación. En situaciones en las que la inactividad de los datos es aceptable, por ejemplo, para el procesamiento o la generación de informes de datos por lotes, las vistas materializadas no incrementales pueden mejorar el rendimiento de las consultas y reducir el costo. Con la opción max_staleness, puedes compilar vistas materializadas arbitrarias y complejas que se mantengan de forma automática y tengan garantías de inactividad integradas.

Usa vistas materializadas no incrementales

Puedes crear vistas materializadas no incrementales con la opción allow_non_incremental_definition. Esta opción se debe acompañar en la opción max_staleness. Para garantizar una actualización periódica de la vista materializada, también debes configurar una política de actualización. Sin una política de actualización, debes actualizar de forma manual la vista materializada.

La vista materializada siempre representa el estado de las tablas base dentro del intervalo max_staleness. Si la última actualización está demasiado inactiva y no representa las tablas base dentro del intervalo max_staleness, la consulta lee las tablas base. Para obtener más información sobre las posibles implicaciones de rendimiento, consulta Inactividad de los datos.

Crear con allow_non_incremental_definition

Para crear una vista materializada con la opción allow_non_incremental_definition, sigue estos pasos. Después de crear la vista materializada, no puedes modificar la opción allow_non_incremental_definition. Por ejemplo, no puedes cambiar el valor true a false ni quitar la opción allow_non_incremental_definition de la vista materializada.

SQL

Agrega una cláusula OPTIONS a la instrucción de DDL cuando crees la vista materializada:

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

   Ir a BigQuery

2. En el editor de consultas, escribe la siguiente oración:

   CREATE MATERIALIZED VIEW my_project.my_dataset.my_mv_table
   OPTIONS (
     enable_refresh = true, refresh_interval_minutes = 60,
     max_staleness = INTERVAL "4" HOUR,
       allow_non_incremental_definition = true)
   AS SELECT
     s_store_sk,
     SUM(ss_net_paid) AS sum_sales,
     APPROX_QUANTILES(ss_net_paid, 2)[safe_offset(1)] median
   FROM my_project.my_dataset.store
   LEFT OUTER JOIN my_project.my_dataset.store_sales
     ON ss_store_sk = s_store_sk
   GROUP BY s_store_sk
   HAVING median < 40 OR median is NULL ;

   Reemplaza lo siguiente:

   • my_project 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_dataset.store y my_dataset.store_sales son los ID de las tablas en tu conjunto de datos que sirven como tablas base para la vista materializada.

3. Haz clic en Ejecutar.

Para obtener más información sobre cómo ejecutar consultas, visita Ejecuta una consulta interactiva.

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": "my_project",
    "datasetId": "my_dataset",
    "tableId": "my_mv_table"
  },
  "materializedView": {
    "query": "`SELECT`
        s_store_sk,
        SUM(ss_net_paid) AS sum_sales,
        APPROX_QUANTILES(ss_net_paid, 2)[safe_offset(1)] median
      FROM my_project.my_dataset.store
      LEFT OUTER JOIN my_project.my_dataset.store_sales
        ON ss_store_sk = s_store_sk
      GROUP BY s_store_sk
      HAVING median < 40 OR median is NULL`",
    "allowNonIncrementalDefinition": true
  }
  "maxStaleness": "4:0:0"
}

Reemplaza lo siguiente:

• my_project 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_dataset.store y my_dataset.store_sales son los IDs de las tablas en tu conjunto de datos que sirven como tablas base para la vista materializada.

Consulta con allow_non_incremental_definition

Puedes consultar vistas materializadas no incrementales como lo harías con cualquier otra vista materializada, vista lógica o tabla.

Por ejemplo:

SELECT * FROM  my_project.my_dataset.my_mv_table

Si los datos no son anteriores al parámetro max_staleness, esta consulta muestra datos de la última actualización. Para obtener más información sobre la inactividad y la inactividad de los datos, consulta Inactividad de los datos.

Limitaciones específicas de las vistas materializadas no incrementales

Las siguientes limitaciones solo se aplican a las vistas materializadas con la opción allow_non_incremental_definition. A excepción de las limitaciones en la sintaxis de consulta compatible, se aplican todas las limitaciones de las vistas materializadas.

• El ajuste inteligente no se aplica a las vistas materializadas que incluyen la opción allow_non_incremental_definition. La única forma de beneficiarse de las vistas materializadas con la opción allow_non_incremental_definition es consultarlas directamente.
• Las vistas materializadas sin la opción allow_non_incremental_definition pueden actualizar de forma incremental un subconjunto de sus datos. Las vistas materializadas con la opción allow_non_incremental_definition deben actualizarse en su totalidad.
• Las vistas materializadas con la opción max_staleness validan la presencia de las restricciones de seguridad a nivel de la columna durante la ejecución de la consulta. Consulta más detalles sobre esto en el control de acceso a nivel de columna.

¿Qué sigue?

• Administra vistas materializadas.
• Usa vistas materializadas.