Soluciona problemas relacionados con las vistas materializadas

En este documento, se te ayudará a solucionar problemas habituales relacionados con las vistas materializadas en BigQuery, incluidos los errores que se producen cuando se crean vistas materializadas, las fallas en la actualización y el rendimiento inesperado de las consultas.

Flujo de trabajo de diagnóstico

Cuando investigues un problema con una vista materializada, sigue estos pasos de diagnóstico para identificar la causa raíz:

  1. Verifica el tipo de tabla y los metadatos. Confirma que la tabla de destino sea una vista materializada y verifica sus opciones de configuración:

    SELECT
 table_name,
 table_type
FROM
 `PROJECT_ID.DATASET`.INFORMATION_SCHEMA.TABLES
WHERE
 table_name = 'MATERIALIZED_VIEW';

    Reemplaza lo siguiente:

    • PROJECT_ID: Es el proyecto que contiene la vista materializada.
    • DATASET: Es el conjunto de datos que contiene la vista materializada.
    • MATERIALIZED_VIEW: Es el nombre de la vista materializada.

    Para inspeccionar las opciones de configuración, como enable_refresh, refresh_interval_minutes y max_staleness, consulta la vista INFORMATION_SCHEMA.TABLE_OPTIONS:

    SELECT
 table_name,
 option_name,
 option_value
FROM
 `PROJECT_ID.DATASET`.INFORMATION_SCHEMA.TABLE_OPTIONS
WHERE
 table_name = 'MATERIALIZED_VIEW';

  2. Verifica el estado de la última actualización. Consulta la vista INFORMATION_SCHEMA.MATERIALIZED_VIEWS para verificar cuándo se actualizó por última vez la vista y si la última actualización automática tuvo errores:

    SELECT
 table_name,
 last_refresh_time,
 refresh_watermark,
 last_refresh_status
FROM
 `PROJECT_ID.DATASET`.INFORMATION_SCHEMA.MATERIALIZED_VIEWS
WHERE
 table_name = 'MATERIALIZED_VIEW';

    Si last_refresh_status no es NULL, falló el último trabajo de actualización automática. Si last_refresh_time es NULL o anterior, la vista materializada nunca completó correctamente una actualización o no se pudo actualizar.

  3. Inspecciona el historial y los errores de los trabajos de actualización. Consulta la vista INFORMATION_SCHEMA.JOBS_BY_PROJECT para inspeccionar los trabajos de actualización automática recientes:

    SELECT
 job_id,
 creation_time,
 end_time,
 state,
 error_result.reason AS error_reason,
 error_result.message AS error_message,
 total_slot_ms,
 total_bytes_processed
FROM
 `region-REGION`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
 job_id LIKE '%materialized_view_refresh_%'
 AND creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
ORDER BY
 creation_time DESC
LIMIT 50;

    Reemplaza REGION por la región de tu conjunto de datos, por ejemplo, us o europe-west3.

  4. Examina las estadísticas de ejecución de consultas y ajuste inteligente. Si una consulta se ejecuta más lento de lo esperado, examina el campo materialized_view_statistics en las estadísticas del trabajo para verificar si el optimizador de consultas usó la vista materializada:

    SELECT
 job_id,
 total_slot_ms,
 total_bytes_billed,
 materialized_view_statistics
FROM
 `region-REGION`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
 job_id = 'JOB_ID';

    Reemplaza JOB_ID por el ID del trabajo de consulta.

Soluciona problemas relacionados con errores en la creación de vistas materializadas

En esta sección, se describen los errores que puedes encontrar cuando creas vistas materializadas, junto con sus causas y los pasos para resolverlos.

Operador o sintaxis de SQL no compatibles

Mensaje de error:

Unsupported operator in materialized view: KEYWORD

o

Materialized view queries do not support FEATURE

Causa:

Las vistas materializadas incrementales admiten un subconjunto restringido de sintaxis de SQL para permitir el mantenimiento incremental y el ajuste inteligente. Es posible que encuentres este error si la consulta que define tu vista materializada incluye funciones no admitidas, como las siguientes:

  • Funciones no deterministas (por ejemplo, CURRENT_TIMESTAMP(), RAND() o SESSION_USER())
  • Funciones analíticas con OVER()
  • Cláusulas ORDER BY o LIMIT
  • DISTINCT sin agregación
  • Subconsultas en las cláusulas WHERE o SELECT
  • Funciones definidas por el usuario (UDF)

Resolución:

  • Revisa la lista de funciones de SQL no compatibles.

  • Si tu consulta requiere capacidades de SQL más amplias, considera crear una vista materializada no incremental configurando allow_non_incremental_definition = true y definiendo un intervalo de max_staleness:

    CREATE MATERIALIZED VIEW `PROJECT_ID.DATASET.MATERIALIZED_VIEW`
OPTIONS (
enable_refresh = true,
refresh_interval_minutes = 60,
max_staleness = INTERVAL "4" HOUR,
allow_non_incremental_definition = true
) AS
SELECT
...

    Reemplaza lo siguiente:

    • PROJECT_ID: Es el proyecto que contiene la vista materializada.
    • DATASET: Es el conjunto de datos que contiene la vista materializada.
    • MATERIALIZED_VIEW: Es el nombre de la vista materializada.

    Las vistas materializadas no incrementales admiten un conjunto más amplio de consultas de SQL, pero siempre realizan actualizaciones completas y no admiten el ajuste inteligente.

  • Si la sintaxis de SQL requerida no es compatible con las vistas materializadas no incrementales, usa una vista lógica o una consulta programada para escribir los resultados en una tabla de destino.

max_staleness no válido con la tabla base de CDC

Mensaje de error:

Materialized view PROJECT_ID:DATASET.MATERIALIZED_VIEW has a CDC table as base table PROJECT_ID:DATASET.TABLE but does not have valid max_staleness. Materialized views over CDC tables must have max_staleness set at least 2 times the base table's max_staleness: 0-0 0 0:0:0

Causa:

Cuando creas una vista materializada sobre una tabla base de captura de datos modificados (CDC), la opción max_staleness de la vista materializada debe configurarse en, al menos, el doble del valor de la opción max_staleness de la tabla base.

Resolución:

  1. Consulta la vista INFORMATION_SCHEMA.TABLE_OPTIONS para verificar el valor de max_staleness de la tabla de CDC base.
  2. Establece la opción max_staleness de la vista materializada en un valor que sea al menos dos veces el valor max_staleness de la tabla base. Por ejemplo, si la tabla de CDC base tiene un valor de max_staleness de 15 minutos, establece el valor de max_staleness de la vista materializada en al menos 30 minutos. Para obtener más información, consulta "Declaración ALTER MATERIALIZED VIEW SET OPTIONS" en Declaraciones del lenguaje de definición de datos (DDL) en GoogleSQL.

Vista materializada particionada sobre una tabla base no particionada

Mensaje de error:

Partitioned incremental materialized view must be created on top of partitioned managed storage base table.

Causa:

Para crear una vista materializada incremental particionada, la tabla base subyacente también debe estar particionada, y la columna de partición de la vista materializada debe alinearse con la columna de partición de la tabla base.

Resolución:

  • Si deseas que la vista materializada esté particionada, asegúrate de que la tabla base esté particionada y configura la vista materializada para que use la misma columna de partición. Para obtener más información, consulta Alineación de particiones.
  • Si la tabla base no está particionada, crea la vista materializada sin una cláusula PARTITION BY.
  • Si necesitas una vista particionada sobre una tabla no particionada, crea una vista materializada no incremental con allow_non_incremental_definition = true y max_staleness. Las vistas materializadas no incrementales no requieren alineación de particiones con las tablas base.

La réplica del conjunto de datos entre regiones es de solo lectura

Mensaje de error:

The dataset replica of the cross region dataset 'PROJECT_ID:DATASET' in region 'REGION' is read-only because it's not the primary replica.

Causa:

Cuando usas la replicación de conjuntos de datos entre regiones, las réplicas secundarias son de solo lectura. No puedes crear una vista materializada en una región de réplica secundaria.

Resolución:

Crea la vista materializada en la región principal del conjunto de datos replicado. Si necesitas la vista materializada en la región de la réplica, crea una réplica de vista materializada en esa región. Para obtener más información, consulta Administra réplicas de vistas materializadas.

Superar el límite de la tabla base

Mensaje de error:

Materialized views support at most 10 source tables, query has NUMBER_OF_SOURCE_TABLES

Causa:

Las vistas materializadas de BigQuery admiten uniones en un máximo de 10 tablas base.

Resolución:

Refactoriza la consulta que define la vista materializada para que haga referencia a 10 o menos tablas base. Si tu arquitectura requiere unir más de 10 tablas, considera unir previamente las tablas estáticas o de dimensiones en tablas intermedias, o bien usa una consulta programada o una canalización de Dataform.

Se excedieron los recursos durante la creación de la vista materializada

Mensaje de error:

Resources exceeded during query execution: The data accessed in this query is too large; consider accessing fewer tables, or for partitioned tables, fewer partitions.

Causa:

Cuando creas una vista materializada, BigQuery realiza una actualización completa inicial para propagar la vista. Si la tabla base subyacente contiene grandes cantidades de datos sin particionar o si la vista produce agregaciones intermedias de alta cardinalidad, la actualización inicial puede exceder los límites de memoria de ranuras o de consultas.

Resolución:

  • Agrega condiciones de filtro en la cláusula WHERE de la vista materializada para limitar el alcance de los datos analizados al subconjunto requerido.
  • Alinea la partición de la vista materializada con la partición de la tabla base para descartar particiones durante las actualizaciones.
  • Si usas procesamiento on demand, considera usar las ediciones de BigQuery con reservas de ranuras dedicadas para proporcionar capacidad de procesamiento suficiente para las actualizaciones grandes.

Problemas con las tablas de BigLake y el almacenamiento en caché de metadatos

Síntomas:

Las vistas materializadas sobre tablas externas de BigLake fallan durante la creación o no se actualizan.

Causa:

Las vistas materializadas sobre tablas externas tienen requisitos arquitectónicos específicos:

  • Las vistas materializadas solo se admiten en tablas de BigLake con la caché de metadatos habilitada.
  • El valor de max_staleness de la vista materializada debe ser mayor que el valor de max_staleness de la tabla base subyacente de BigLake.
  • Una vista materializada puede hacer referencia a tablas externas de BigLake o a tablas de almacenamiento administrado de BigQuery, pero no puede combinar tipos en una sola vista materializada.

Resolución:

  1. Asegúrate de que el almacenamiento en caché de metadatos esté habilitado en todas las tablas base subyacentes de BigLake.
  2. Configura max_staleness en la vista materializada con un valor superior al intervalo de la caché de metadatos de las tablas base. Por ejemplo, si el intervalo de caché de la tabla base es de 30 minutos, establece el max_staleness de la vista materializada en al menos 45 minutos para permitir un búfer para la ejecución de la actualización.
  3. No mezcles tablas externas y tablas administradas en una definición de vista materializada.

Soluciona problemas de actualización

En esta sección, se describen las causas comunes de las fallas en la actualización y las demoras en el rendimiento de las vistas materializadas.

Cambios en el esquema de la tabla base (invalidQuery)

Síntomas:

La columna last_refresh_status en INFORMATION_SCHEMA.MATERIALIZED_VIEWS muestra un error invalidQuery y se detiene la ejecución de las actualizaciones automáticas.

Causa:

Si cambia el esquema de una tabla base (por ejemplo, se descarta una columna a la que hace referencia la vista materializada, se cambia el nombre de una columna o se altera el tipo de datos de una columna), la consulta subyacente que define la vista materializada deja de ser válida.

Resolución:

BigQuery no admite la modificación del esquema de columnas de una vista materializada existente. Para resolver la invalidación del esquema, haz lo siguiente:

  1. Vuelve a crear la vista materializada con la sentencia CREATE OR REPLACE MATERIALIZED VIEW:

    CREATE OR REPLACE MATERIALIZED VIEW `PROJECT_ID.DATASET.MATERIALIZED_VIEW`
OPTIONS (
 enable_refresh = true,
 refresh_interval_minutes = 30
) AS
SELECT
 ...

    Reemplaza lo siguiente:

    • PROJECT_ID: Es el proyecto que contiene la vista materializada.
    • DATASET: Es el conjunto de datos que contiene la vista materializada.
    • MATERIALIZED_VIEW: Es el nombre de la vista materializada.

  2. Verifica que la nueva definición coincida con el esquema de la tabla base actualizada.

Vencimiento, truncamiento o cambios de DML en la partición de la tabla base

Síntomas:

Las actualizaciones de la vista materializada fallan o las consultas de la vista materializada recurren a la tabla base y se ejecutan con lentitud.

Causa:

Las siguientes operaciones de la tabla base invalidan los datos existentes de la vista materializada:

  • Truncar una tabla base o una partición de tabla base (TRUNCATE TABLE)
  • Vencimiento de la partición en una tabla base
  • Declaraciones de lenguaje de manipulación de datos (DML) DELETE o MERGE en tablas sin particionar o tablas base secundarias unidas

Cuando se producen estas operaciones, las particiones afectadas (o toda la vista materializada en el caso de las tablas no particionadas) se marcan como no válidas.

Resolución:

  1. Activa manualmente una actualización para restablecer la vista materializada a un estado válido:

    CALL BQ.REFRESH_MATERIALIZED_VIEW('PROJECT_ID.DATASET.MATERIALIZED_VIEW');

  2. Si ejecutas canalizaciones de ETL por lotes que ejecutan instrucciones DML o truncan datos con regularidad, inhabilita la actualización automática y llama a BQ.REFRESH_MATERIALIZED_VIEW al final de tu canalización de ETL. Para obtener más información, consulta Actualización automática.

Se agota el tiempo de espera de los trabajos de actualización

Síntomas:

Los trabajos de actualización fallan con un error de tiempo de espera después de ejecutarse durante varias horas (hasta 12 horas).

Causa:

A medida que crecen las tablas base, aumenta el volumen de datos procesados durante una actualización. Si la consulta de la vista materializada no filtra filas o si la vista no puede realizar actualizaciones incrementales debido a una invalidación completa, cada actualización requiere un análisis completo de las tablas base, lo que puede agotar el tiempo de ranura.

Resolución:

  • Agrega criterios de filtro en la cláusula WHERE de la vista materializada para restringir los datos históricos innecesarios.
  • Asegúrate de que la vista materializada esté alineada con las particiones de la tabla base para que solo se actualicen de forma incremental las particiones modificadas.
  • Asigna una reserva de ranuras con capacidad suficiente para admitir la carga de trabajo de actualización.

Mensaje de actualización duplicado

Mensaje:

Materialized view is already being refreshed.

Causa:

Si las tablas base de una vista materializada JOIN se actualizan de forma simultánea o si se activa una actualización manual mientras ya está en curso una actualización automática, BigQuery detecta la actualización simultánea y cancela el trabajo duplicado.

Resolución:

Este comportamiento es normal y transitorio. El trabajo duplicado se detiene para evitar el procesamiento redundante, y no se te factura por el intento de actualización duplicado. No es necesario que realices ninguna acción.

Demoras en la actualización de los datos de transmisión (almacenamiento optimizado para escritura)

Síntomas:

Las consultas en tablas base con datos de transmisión de alta velocidad no aparecen en la vista materializada de inmediato, o bien las consultas recurren a la tabla base.

Causa:

Los datos que se transmiten a BigQuery con la API de Storage Write se almacenan inicialmente en el almacenamiento optimizado para escritura (búfer de transmisión). Los trabajos de actualización de la vista materializada procesan los datos después de que se confirman y se convierten del búfer de transmisión al almacenamiento columnar optimizado.

Para mantener la coherencia en tiempo real, las consultas que leen desde la vista materializada leen los datos confirmados de la vista materializada y, al mismo tiempo, leen el delta directamente desde el búfer de transmisión de la tabla base.

Resolución:

  • Si se requiere coherencia de lectura en tiempo real en los datos de transmisión, el optimizador de consultas combina automáticamente los datos de la vista materializada con los deltas de la tabla base.
  • Si no se requiere coherencia en tiempo real y deseas evitar analizar el búfer de transmisión en cada consulta, establece max_staleness en la vista materializada (por ejemplo, max_staleness = INTERVAL "15" MINUTE). Luego, las consultas podrán leer directamente desde la vista materializada precalculada sin procesamiento delta.

Soluciona problemas de rendimiento de las consultas y realiza ajustes inteligentes

En esta sección, se describe cómo solucionar problemas relacionados con las consultas que se ejecutan más lento de lo esperado o que no aprovechan el ajuste inteligente.

Cómo verificar el uso del ajuste inteligente

Cuando consultas una tabla base, BigQuery usa el ajuste inteligente para reescribir automáticamente la consulta y usar una vista materializada disponible si mejora el rendimiento y reduce el costo.

Para verificar si una consulta usó una vista materializada, inspecciona el campo materialized_view_statistics en los detalles del trabajo de consulta o consulta la vista INFORMATION_SCHEMA.JOBS_BY_PROJECT:

SELECT
  job_id,
  total_slot_ms,
  total_bytes_billed,
  mv.table_reference.dataset_id,
  mv.table_reference.table_id,
  mv.chosen,
  mv.rejected_reason
FROM
  `region-REGION`.INFORMATION_SCHEMA.JOBS_BY_PROJECT,
  UNNEST(materialized_view_statistics.materialized_view) AS mv
WHERE
  job_id = 'JOB_ID';

Reemplaza lo siguiente:

  • REGION: La región de tu conjunto de datos (por ejemplo, us o europe-west3).
  • JOB_ID: Es el ID del trabajo de la consulta.

En el objeto materialized_view_statistics, cada entrada del array materialized_view contiene los siguientes campos:

  • table_reference: Identifica el candidato a vista materializada.
  • chosen: Es un valor booleano que indica si el optimizador de consultas seleccionó la vista materializada para la ejecución (true) o la rechazó (false).
  • estimated_bytes_saved: Son los bytes estimados que la consulta evitó analizar gracias al uso de la vista materializada.
  • rejected_reason: Si chosen es false, especifica el motivo por el que el optimizador rechazó la vista materializada.

Para obtener más información sobre los motivos de rechazo y la enumeración rejected_reason, consulta Comprende por qué se rechazaron las vistas materializadas.

Motivos comunes por los que se rechaza una vista materializada

Cuando chosen es false, examina el valor de rejected_reason para diagnosticar la causa:

Valor rejected_reason Descripción Solución
NO_DATA La vista materializada no tiene datos almacenados en caché porque aún no se actualizó o falló la actualización inicial. Activa una actualización manual con CALL BQ.REFRESH_MATERIALIZED_VIEW(...).
COST El optimizador de consultas estimó que consultar la tabla base (o leer desde la caché de consultas) es más económico que consultar la vista materializada. Revisa los filtros y las particiones de las consultas. Si la consulta de la tabla base analiza solo una partición pequeña, mientras que la vista materializada abarca varias particiones, podría ser más eficiente consultar directamente la tabla base.
BASE_TABLE_DATA_CHANGE Los cambios en los datos de una o más tablas básicas invalidaron los datos almacenados en caché fuera del período de obsolescencia configurado. Realiza una actualización manual o configura max_staleness para permitir que las consultas lean datos inactivos sin recurrir a las tablas base.
BASE_TABLE_TRUNCATED Se truncó una tabla base, lo que invalidó todos los datos de la vista materializada. Actualiza la vista materializada después de que se vuelvan a propagar los datos.
BASE_TABLE_EXPIRED_PARTITION Venció una partición en la tabla base. Asegúrate de que la configuración de vencimiento de la partición coincida entre la tabla base y la vista materializada, y actualiza la vista.
BASE_TABLE_PARTITION_EXPIRATION_CHANGE Se modificó la duración del vencimiento de la partición de una tabla base. Actualiza la vista materializada para realinear los metadatos de vencimiento de la partición.
BASE_TABLE_INCOMPATIBLE_METADATA_CHANGE Se produjo un cambio de metadatos en una tabla base (por ejemplo, una modificación del esquema). Vuelve a crear la vista materializada con CREATE OR REPLACE MATERIALIZED VIEW.
BASE_TABLE_TOO_STALE Los metadatos almacenados en caché de una tabla base (por ejemplo, en una tabla externa de BigLake) son más antiguos que el umbral permitido. Actualiza la caché de metadatos de la tabla externa.
BASE_TABLE_FINE_GRAINED_SECURITY_POLICY El usuario de la consulta no tiene acceso según una política de control de acceso a nivel de la fila o la columna en una tabla base. Verifica los permisos de IAM y las concesiones de políticas de datos.
TIME_ZONE La vista se actualizó con una zona horaria diferente de la de la consulta actual. Alinea la configuración de zona horaria entre tu entorno y los trabajos de actualización.

No se consideró la vista materializada (no coincide la estructura de la consulta)

Si una vista materializada no aparece en materialized_view_statistics, el optimizador de consultas determinó durante el análisis de sintaxis que el patrón de consulta no coincidía con la definición de la vista materializada.

Entre las causas comunes, se incluyen las siguientes:

  1. No coincide la agregación o el filtro. La consulta usa funciones de agregación, columnas de agrupación o predicados de filtro que no se pueden calcular a partir de las agregaciones precalculadas en la vista materializada.
    • Resolución: Alinea las funciones de agregación y las agrupaciones entre tus consultas y la definición de la vista materializada.
  2. Vistas materializadas no incrementales Las vistas creadas con allow_non_incremental_definition = true no admiten el ajuste inteligente.
    • Resolución: Consulta directamente las vistas materializadas no incrementales especificando el nombre de la vista en la cláusula FROM.
  3. Consulta directa sobre una vista obsoleta. Si consultas directamente una vista materializada que tiene establecido max_staleness, la consulta devuelve resultados obsoletos precalculados hasta max_staleness sin procesamiento delta de las tablas base.

Error de boceto HyperLogLog incompatible

Mensaje de error:

Invalid or incompatible sketch in HLL_COUNT.MERGE_PARTIAL

Causa:

Cuando usas funciones de agregación aproximadas, como HLL_COUNT.INIT y HLL_COUNT.MERGE_PARTIAL, BigQuery usa bocetos de HyperLogLog. Si el parámetro de precisión especificado en la consulta no coincide con el parámetro de precisión definido en la vista materializada, la operación de combinación de bocetos falla.

Resolución:

Asegúrate de que el parámetro de precisión (por ejemplo, HLL_COUNT.INIT(x, 12)) sea idéntico en la definición de la vista materializada y en las consultas que hacen referencia a la vista o que se reescriben en ella.

Soluciona problemas relacionados con la alteración de vistas y las modificaciones de esquemas

En esta sección, se describen los problemas que puedes encontrar cuando modificas el esquema o las opciones de una vista materializada.

Edita el esquema de la vista materializada

Problema:

Si intentas agregar o modificar columnas en una vista materializada con ALTER TABLE o la consola de Google Cloud , se producirá un error, o bien la opción Editar esquema no estará disponible.

Causa:

BigQuery no admite la modificación directa del esquema de columnas de una vista materializada.

Resolución:

  • Puedes modificar las opciones de la vista materializada (como enable_refresh, refresh_interval_minutes y max_staleness) con la instrucción ALTER MATERIALIZED VIEW SET OPTIONS:

    ALTER MATERIALIZED VIEW `PROJECT_ID.DATASET.MATERIALIZED_VIEW`
SET OPTIONS (
enable_refresh = true,
refresh_interval_minutes = 20
);

    Reemplaza lo siguiente:

    • PROJECT_ID: Es el proyecto que contiene la vista materializada.
    • DATASET: Es el conjunto de datos que contiene la vista materializada.
    • MATERIALIZED_VIEW: Es el nombre de la vista materializada.

  • Para cambiar la definición de la consulta en SQL, agregar columnas o cambiar los tipos de datos de las columnas, vuelve a crear la vista con CREATE OR REPLACE MATERIALIZED VIEW:

    CREATE OR REPLACE MATERIALIZED VIEW `PROJECT_ID.DATASET.MATERIALIZED_VIEW`
AS SELECT
...

¿Qué sigue?