How to use materialized views in Hologres - Hologres - Centro de documentación de Alibaba Cloud

Las vistas materializadas en tiempo real de Hologres preagregan los datos de la tabla base y almacenan los resultados. Las consultas leen directamente desde la vista en lugar de recalcular la agregación al vuelo, lo que reduce la latencia de las consultas y la sobrecarga computacional.

A diferencia de las vistas materializadas tradicionales que requieren una actualización manual, las vistas materializadas de Hologres se actualizan automáticamente. Los cambios escritos en la tabla base se reflejan de inmediato en la vista, sin necesidad de programar tareas de actualización.

Materialized view structure

La tabla que recibe escrituras en tiempo real se denomina tabla base. Todas las operaciones INSERT, UPDATE y DELETE tienen como destino la tabla base. La vista materializada se define mediante reglas de agregación sobre la tabla base y refleja los cambios de INSERT en tiempo real. La compatibilidad con la sincronización de cambios UPDATE y DELETE se añadirá en una versión futura.

Cuándo usar vistas materializadas

Las vistas materializadas son adecuadas cuando:

Las consultas agregan repetidamente grandes volúmenes de datos sin procesar (paneles, informes, análisis).
El resultado de la agregación se consulta con mucha más frecuencia de la que se escribe en la tabla base.
La latencia de consulta en la tabla base representa un cuello de botella, incluso con índices.

Evite usar vistas materializadas cuando el resultado de la agregación tenga aproximadamente la misma cantidad de filas que la tabla base (baja tasa de compresión), o cuando el patrón de consulta cambie con frecuencia, lo que hace poco práctica una clave GROUP BY fija.

Funciones de agregación compatibles

Función	Notas
`SUM`
`COUNT`
`AVG`
`MIN`
`MAX`
`RB_BUILD_CARDINALITY_AGG`	Solo para el tipo de dato BIGINT. Requiere la extensión `roaringbitmap`.

Requisitos previos

Antes de empezar, asegúrese de contar con lo siguiente:

Una instancia de Hologres con acceso de escritura.
La extensión roaringbitmap creada (obligatoria solo para RB_BUILD_CARDINALITY_AGG).

Crear una vista materializada en tiempo real

Cree la tabla base y la vista materializada juntas en una sola transacción. La tabla base debe tener el tipo de mutación appendonly.

BEGIN;
CREATE TABLE base_sales(
  day  text        NOT NULL,
  hour int,
  ts   timestamptz,
  amount float,
  pk   text        NOT NULL PRIMARY KEY
);
CALL SET_TABLE_PROPERTY('base_sales', 'mutate_type', 'appendonly');

-- After dropping the materialized view, remove the appendonly property if needed:
-- CALL SET_TABLE_PROPERTY('base_sales', 'mutate_type', 'none');

CREATE MATERIALIZED VIEW mv_sales AS
  SELECT
    day,
    hour,
    avg(amount) AS amount_avg
  FROM base_sales
  GROUP BY day, hour;

COMMIT;

Inserte datos en la tabla base. La vista reflejará los nuevos datos de inmediato.

INSERT INTO base_sales VALUES (to_char(now(),'YYYYMMDD'), '12', now(), 100, 'pk1');
INSERT INTO base_sales VALUES (to_char(now(),'YYYYMMDD'), '12', now(), 200, 'pk2');
INSERT INTO base_sales VALUES (to_char(now(),'YYYYMMDD'), '12', now(), 300, 'pk3');

Consultar una vista materializada

Consulte la vista directamente mediante SQL estándar.

SELECT * FROM mv_sales WHERE day = to_char(now(),'YYYYMMDD') AND hour = 12;

Como alternativa, consulte la tabla base y permita que el enrutamiento inteligente redirija la consulta a la vista automáticamente. Consulte Enrutamiento inteligente para vistas materializadas .

Crear una vista materializada para una tabla particionada

Cuando la tabla base está particionada, la clave GROUP BY de la vista materializada debe incluir la columna de clave de partición. Cree la vista materializada sobre la tabla padre, no sobre tablas hijas individuales.

BEGIN;
CREATE TABLE base_sales_p(
  day    text NOT NULL,
  hour   int,
  ts     timestamptz,
  amount float,
  pk     text NOT NULL,
  PRIMARY KEY (day, pk)
) PARTITION BY LIST(day);
CALL SET_TABLE_PROPERTY('base_sales_p', 'mutate_type', 'appendonly');

-- day is the partition key and must appear in the GROUP BY clause
CREATE MATERIALIZED VIEW mv_sales_p AS
  SELECT
    day,
    hour,
    avg(amount) AS amount_avg
  FROM base_sales_p
  GROUP BY day, hour;
COMMIT;

-- Use CREATE TABLE PARTITION OF to add partitions (ATTACH PARTITION is not supported)
CREATE TABLE base_sales_20220101 PARTITION OF base_sales_p FOR VALUES IN ('20220101');

Más operaciones

Comprobar el tamaño de almacenamiento

Compruebe el tamaño de almacenamiento de una sola vista materializada:

SELECT pg_relation_size('mv_sales');

Liste todas las vistas materializadas ordenadas por tamaño de almacenamiento:

SELECT schemaname || '.' || matviewname AS mv_full_name,
       pg_size_pretty(pg_relation_size('"' || schemaname || '"."' || matviewname || '"')) AS mv_size,
       pg_relation_size('"' || schemaname || '"."' || matviewname || '"') AS order_size
FROM pg_matviews
ORDER BY order_size DESC;

Eliminar una vista materializada

DROP MATERIALIZED VIEW mv_sales;

Cálculo preciso de UV con RoaringBitmap

El cálculo preciso de visitantes únicos (UV), que consiste en contar identificadores de usuario distintos, consume muchos recursos computacionales y suele ser un cuello de botella común de rendimiento. La función RB_BUILD_CARDINALITY_AGG preagrega campos de ID de negocio BIGINT en un RoaringBitmap dentro de la vista materializada, lo que permite la deduplicación en tiempo real. Esta agregación solo admite campos BIGINT.

-- The roaringbitmap extension must be created before use
CREATE EXTENSION IF NOT EXISTS roaringbitmap;

BEGIN;
CREATE TABLE base_sales_r(
  day    text        NOT NULL,
  hour   int,
  ts     timestamptz,
  amount float,
  userid bigint,
  pk     text        NOT NULL PRIMARY KEY
);
CALL SET_TABLE_PROPERTY('base_sales_r', 'mutate_type', 'appendonly');

CREATE MATERIALIZED VIEW mv_sales_r AS
  SELECT
    day,
    hour,
    avg(amount)                      AS amount_avg,
    rb_build_cardinality_agg(userid) AS user_count
  FROM base_sales_r
  GROUP BY day, hour;

COMMIT;

INSERT INTO base_sales_r VALUES (to_char(now(),'YYYYMMDD'), '12', now(), 100, 1, 'pk1');
INSERT INTO base_sales_r VALUES (to_char(now(),'YYYYMMDD'), '12', now(), 200, 2, 'pk2');
INSERT INTO base_sales_r VALUES (to_char(now(),'YYYYMMDD'), '12', now(), 300, 3, 'pk3');

-- user_count holds the count of distinct userid values for that day+hour
SELECT user_count AS UV FROM mv_sales_r WHERE day = to_char(now(),'YYYYMMDD') AND hour = 12;

Agregación multidimensional con funciones parciales

Por qué AVG estándar falla entre dimensiones

Una vista materializada almacena resultados preagregados. Si define mv_sales con avg(amount) agrupado por (day, hour), consultar únicamente por la dimensión day produce resultados incorrectos, ya que el promedio de promedios no equivale al promedio total.

Dados estos datos base:

Día	Hora	Monto	PK
20210101	12	2	pk1
20210101	12	4	pk2
20210101	13	6	pk3

Una consulta directa sobre la vista devuelve:

postgres=> SELECT * FROM mv_sales;
    day    | hour | amount_avg
-----------+------+------------
  20210101 |   12 |     3
  20210101 |   13 |     6

Reagregar por day genera un resultado incorrecto:

postgres=> SELECT day, avg(amount_avg) FROM mv_sales GROUP BY day;
    day    |  avg
-----------+------
  20210101 |  4.5    -- incorrect: true average is 4

Usar el estado de agregación intermedio

En lugar de almacenar el promedio final, almacene el estado de agregación intermedio mediante avg_partial. Al consultar por una dimensión diferente, finalice el resultado con avg_final.

BEGIN;
CREATE TABLE base_sales(
  day    text        NOT NULL,
  hour   int,
  ts     timestamptz,
  amount float,
  pk     text        NOT NULL PRIMARY KEY
);
CALL SET_TABLE_PROPERTY('base_sales', 'mutate_type', 'appendonly');

CREATE MATERIALIZED VIEW mv_sales_partial AS
  SELECT
    day,
    hour,
    avg(amount)         AS avg,
    avg_partial(amount) AS amt_avg_partial   -- stores intermediate state
  FROM base_sales
  GROUP BY day, hour;

COMMIT;

Consulte por una dimensión de agregación diferente usando avg_final:

postgres=> SELECT day, avg(avg) AS avg_avg, avg_final(amt_avg_partial) AS real_avg
           FROM mv_sales_partial
           GROUP BY day;
    day    | avg_avg | real_avg
-----------+---------+----------
  20210101 |     4.5 |        4   -- real_avg is correct

Referencia de funciones parciales y finales

Función estándar	Función parcial	Función final
AVG	AVG_PARTIAL	AVG_FINAL
RB_BUILD_CARDINALITY_AGG	RB_BUILD_AGG	RB_OR_CARDINALITY_AGG

Enrutamiento inteligente para vistas materializadas

Consulte la tabla base directamente; el optimizador reescribe automáticamente la consulta para usar una vista materializada coincidente. No es necesario modificar la sintaxis de la consulta.

El optimizador selecciona una vista cuando se cumplen todas las condiciones siguientes:

La vista contiene todas las columnas referenciadas en la consulta (o columnas a partir de las cuales se pueden derivar los valores consultados).
Las columnas GROUP BY de la vista incluyen todas las columnas GROUP BY de la consulta original.
Si varias vistas cumplen los criterios, el optimizador elige la que tiene menos columnas GROUP BY.

Funciones de agregación compatibles con el enrutamiento inteligente: SUM, COUNT, MIN, MAX.

Las funciones AVG y RB_BUILD_CARDINALITY_AGG no admiten enrutamiento inteligente.

Consideraciones sobre TTL

Los datos subyacentes de una vista materializada comparten el mismo tiempo de vida (TTL) que su tabla base. No configure un TTL independiente en la vista materializada, ya que valores de TTL inconsistentes provocan discrepancias de datos entre la vista y la tabla base.

Cuando el TTL de la tabla base expira y los datos se recuperan, una consulta a la tabla base y una consulta a la vista pueden devolver resultados diferentes. Por ejemplo:

Consulta a la tabla base después de la expiración del TTL:

postgres=> SELECT day, hour, avg(amount) AS amount_avg FROM base_sales GROUP BY day, hour;
    day    | hour | amount_avg
-----------+------+------------
  20210101 |   12 |     4
  20210101 |   13 |     6

Consulta a la vista: los datos recuperados ya se habían materializado y permanecen en la vista:

postgres=> SELECT * FROM mv_sales;
    day    | hour | amount_avg
-----------+------+------------
  20210101 |   12 |     3       -- stale; reflects pre-reclamation state
  20210101 |   13 |     6

Para evitar esta inconsistencia, utilice uno de los siguientes enfoques:

No configure un TTL en la tabla base.
Si se requiere un TTL, incluya un campo basado en tiempo en la clave GROUP BY de la vista. Al consultar, evite rangos de tiempo cercanos al límite de expiración del TTL.
Use una tabla particionada sin TTL. Recupere los datos antiguos eliminando las particiones hijas.

Limitaciones

Restricciones de creación

Las vistas materializadas deben crearse en la misma transacción que la tabla base. No se admite la creación asíncrona.
Una vista materializada solo puede hacer referencia a una única tabla. No se admiten expresiones de tabla comunes (CTE), JOIN de múltiples tablas, subconsultas ni cláusulas WHERE, ORDER BY, LIMIT y HAVING.
La clave GROUP BY y los valores de agregación no admiten expresiones. Por ejemplo, no se admiten SUM(CASE WHEN cond THEN a ELSE b END), SUM(col1 + col2) ni GROUP BY date_trunc('hour', ts).
Se permite un máximo de 10 vistas materializadas por tabla base. El consumo de recursos crece proporcionalmente al número de vistas.
Para tablas particionadas: la clave GROUP BY debe incluir la columna de clave de partición y la vista debe crearse sobre la tabla padre.
Para tablas particionadas: no se admite ATTACH PARTITION para añadir particiones; use CREATE TABLE PARTITION OF en su lugar.

Restricciones de operación

No se admiten operaciones DELETE y UPDATE en una tabla base. Establezca la propiedad appendonly en la tabla base. Intentar ejecutar DELETE o UPDATE devuelve el error Table XXX is append-only.
Al escribir en la tabla base en tiempo real con Flink, establezca mutateType en InsertOrIgnore.
No se admite DROP COLUMN en una tabla base que tenga una vista materializada asociada.
No configure manualmente un TTL en una vista materializada. La vista hereda el TTL de la tabla base. Configurar un TTL diferente provoca inconsistencia en los datos.

Mejores prácticas

Alinee la clave GROUP BY con la clave de distribución. Configure la clave GROUP BY de la vista materializada para que coincida con la clave de distribución de la tabla base. Esto mejora tanto la tasa de compresión de datos como el rendimiento de las consultas.
Coloque las columnas de filtro al inicio de la clave GROUP BY. Sitúe las columnas que se usan frecuentemente en filtros WHERE al principio de la clave GROUP BY. Esto sigue el principio de coincidencia más a la izquierda de la clave de clustering y permite una poda más eficiente.