Vistas Materializadas en ClickHouse: El Poder del Procesamiento Incremental
1. Cómo Funciona una MV en ClickHouse — Es un Disparador INSERT, No un Caché SELECT
En bases de datos conocidas (PostgreSQL, Oracle), una vista materializada (MV) es el resultado de un SELECT que se almacena en disco y se actualiza según un cronograma (REFRESH). Tú decides cuándo recalcularla.
En ClickHouse, es completamente diferente. Aquí, una MV es un disparador INSERT. Creas una MV basada en un SELECT, y cuando insertas datos en la tabla fuente, ClickHouse ejecuta automáticamente ese SELECT sobre las filas insertadas e inserta el resultado en la tabla destino.
Diferencia clave:
- En PostgreSQL: actualizas la MV manualmente o según un cronograma, leyendo TODA la tabla fuente.
- En ClickHouse: la MV se activa en cada inserción, procesando SOLO las filas nuevas.
Analogía de la vida real: Una VIEW normal es como gafas con lentes de aumento. Miras los datos fuente a través de ellas, recalculando el resultado cada vez. Una vista materializada en ClickHouse es como un secretario que, cada vez que llega un nuevo documento, lo copia en un archivador especial en el formato requerido. No necesitas revisar todos los documentos cada vez; solo vas al archivador.
Por qué esto importa: ClickHouse no tiene disparadores en el sentido clásico. MV es el único mecanismo integrado para reaccionar a la inserción de datos. Con él, puedes:
- Agregar datos sobre la marcha (totales por hora).
- Desnormalizar datos (buscar nombres de deportes de un diccionario).
- Distribuir los mismos datos sin procesar a múltiples tablas con diferentes estructuras (agregados, deduplicación, archivo completo).
2. MV Simple: Sumando Apuestas por Hora
Comencemos con el escenario más común: tienes una tabla bets con apuestas y necesitas estadísticas por hora y por deporte.
Paso 1: Crear la tabla destino (donde se agregarán los datos)
CREATE TABLE hourly_sport_stats
(
hour DateTime, -- Inicio de la hora (2025-06-01 14:00:00)
sport_id UInt8, -- Tipo de deporte
bets_count UInt64, -- Número de apuestas por hora
total_amount Decimal(18,2) -- Monto total de apuestas
)
ENGINE = SummingMergeTree() -- Sumará por hora + sport_id
ORDER BY (hour, sport_id);Paso 2: Crear la MV que poblará esta tabla
CREATE MATERIALIZED VIEW mv_hourly_stats TO hourly_sport_stats AS
SELECT
toStartOfHour(created_at) AS hour, -- Redondear la hora al inicio de la hora
sport_id,
count() AS bets_count, -- Número de apuestas en el grupo
sum(amount) AS total_amount -- Suma de apuestas en el grupo
FROM bets
GROUP BY hour, sport_id;Qué sucede línea por línea:
TO hourly_sport_stats— dónde insertar el resultado. Puedes omitirlo si la estructura de la MV coincide con la estructura del SELECT, pero es mejor especificarlo explícitamente.AS SELECT ...— la consulta ejecutada sobre cada lote insertado de datos en la tablabets. No sobre toda la tabla, solo sobre las filas nuevas.GROUP BY hour, sport_id— agregación dentro del lote. Si una inserción tiene 1000 filas para la misma hora, la MV calcula una fila de resumen para esa hora.
Cómo usarlo:
-- Insertar 100 apuestas para las 14:00 y 50 para las 15:00
INSERT INTO bets (created_at, sport_id, amount) VALUES
('2025-06-01 14:15:00', 1, 100),
('2025-06-01 14:30:00', 1, 200),
('2025-06-01 15:00:00', 2, 50),
... (147 filas más) ...;
-- La MV agrega o actualiza automáticamente filas en hourly_sport_stats
-- Para la hora 14:00, sport_id=1: bets_count aumenta según el número de apuestas de la inserción
-- Para la hora 15:00, sport_id=2: similar
-- Ahora lee los agregados al instante, ¡sin GROUP BY!
SELECT * FROM hourly_sport_stats
WHERE hour = '2025-06-01 14:00:00';Limitación importante: La MV en ClickHouse no puede actualizar filas existentes en la tabla destino en inserciones repetidas para la misma hora. Pero como usamos SummingMergeTree, durante las fusiones en segundo plano, las filas con el mismo ORDER BY (hour, sport_id) se sumarán. Por lo tanto, SELECT * FROM hourly_sport_stats puede devolver múltiples filas por grupo — siempre envuélvelo en SUM con GROUP BY, como con SummingMergeTree.
3. MV con AggregatingMergeTree — Para Agregaciones Complejas
Si necesitas no solo sumas sino también, por ejemplo, usuarios únicos (uniq), apuestas promedio (avg), debes usar AggregatingMergeTree y las funciones *State / *Merge.
-- Tabla destino con columnas AggregateFunction
CREATE TABLE hourly_sport_advanced
(
hour DateTime,
sport_id UInt8,
bets_count AggregateFunction(count, UInt64), -- Estado para count
total_amount AggregateFunction(sum, Decimal(18,2)), -- Estado para sum
unique_users AggregateFunction(uniq, UInt64), -- Estado para uniq
avg_amount AggregateFunction(avg, Decimal(18,2)) -- Estado para avg
)
ENGINE = AggregatingMergeTree()
ORDER BY (hour, sport_id);
-- MV con funciones *State
CREATE MATERIALIZED VIEW mv_hourly_advanced TO hourly_sport_advanced AS
SELECT
toStartOfHour(created_at) AS hour,
sport_id,
countState(user_id) AS bets_count, -- No solo count, sino countState
sumState(amount) AS total_amount,
uniqState(user_id) AS unique_users, -- uniq aproximado
avgState(amount) AS avg_amount
FROM bets
GROUP BY hour, sport_id;Por qué así: AggregatingMergeTree almacena no valores finales sino estados intermedios. Esto permite fusionar correctamente agregaciones de diferentes inserciones (por ejemplo, de dos lotes para la misma hora). uniqState almacena una tabla hash de valores únicos, no solo un número.
Lectura de datos:
SELECT
hour,
sport_id,
countMerge(bets_count) AS bets_count,
sumMerge(total_amount) AS total_amount,
uniqMerge(unique_users) AS unique_users,
avgMerge(avg_amount) AS avg_amount
FROM hourly_sport_advanced
GROUP BY hour, sport_id;4. MV con SummingMergeTree para Contadores Simples
Para sumas y contadores simples, SummingMergeTree es más simple y rápido que AggregatingMergeTree. El ejemplo de la Sección 2 es ideal para esto.
Otro ejemplo — contadores por usuario:
-- Tabla destino: cuántas apuestas hizo cada usuario por día
CREATE TABLE user_daily_counts
(
user_id UInt64,
day Date,
bets_count UInt64, -- se sumará
total_amount Decimal(18,2) -- se sumará
)
ENGINE = SummingMergeTree()
ORDER BY (user_id, day);
-- MV
CREATE MATERIALIZED VIEW mv_user_daily TO user_daily_counts AS
SELECT
user_id,
toDate(created_at) AS day,
count() AS bets_count,
sum(amount) AS total_amount
FROM bets
GROUP BY user_id, day;Ventaja de SummingMergeTree: Simplicidad, no se necesitan funciones *Merge al leer. Un simple SUM con GROUP BY es suficiente para estar seguros (si los datos no se han fusionado).
Desventaja: Solo sumas y contadores. Sin usuarios únicos, máximo, promedio.
5. Cadena de MV: Evento → Agregación por Minuto → Por Hora → Diaria
Este es un patrón poderoso para reducir gradualmente la granularidad. En lugar de agregar desde datos sin procesar directamente a estadísticas diarias (lo que requeriría recalcular miles de millones de filas), construyes una cadena.
Esquema:
- Apuestas sin procesar (tabla
raw_bets) — almacenar 7 días. - Agregación por minuto (tabla
minute_stats) — almacenar 30 días. - Agregación por hora (tabla
hourly_stats) — almacenar 365 días. - Agregación diaria (tabla
daily_stats) — almacenar 5 años.
-- Nivel 1: agregación por minuto desde datos sin procesar
CREATE TABLE minute_stats
(
minute DateTime, -- redondeado al minuto
sport_id UInt8,
bets_count UInt64,
total_amount Decimal(18,2)
)
ENGINE = SummingMergeTree()
ORDER BY (minute, sport_id);
CREATE MATERIALIZED VIEW mv_minute_from_raw TO minute_stats AS
SELECT
toStartOfMinute(created_at) AS minute,
sport_id,
count() AS bets_count,
sum(amount) AS total_amount
FROM raw_bets
GROUP BY minute, sport_id;
-- Nivel 2: agregación por hora desde minuto
CREATE TABLE hourly_stats
(
hour DateTime,
sport_id UInt8,
bets_count UInt64,
total_amount Decimal(18,2)
)
ENGINE = SummingMergeTree()
ORDER BY (hour, sport_id);
CREATE MATERIALIZED VIEW mv_hourly_from_minute TO hourly_stats AS
SELECT
toStartOfHour(minute) AS hour, -- redondear minuto a hora
sport_id,
sum(bets_count) AS bets_count, -- sumar contadores de minuto
sum(total_amount) AS total_amount
FROM minute_stats
GROUP BY hour, sport_id;
-- Nivel 3: agregación diaria desde hora
CREATE TABLE daily_stats
(
day Date,
sport_id UInt8,
bets_count UInt64,
total_amount Decimal(18,2)
)
ENGINE = SummingMergeTree()
ORDER BY (day, sport_id);
CREATE MATERIALIZED VIEW mv_daily_from_hourly TO daily_stats AS
SELECT
toDate(hour) AS day,
sport_id,
sum(bets_count) AS bets_count,
sum(total_amount) AS total_amount
FROM hourly_stats
GROUP BY day, sport_id;Ventajas de la cadena:
- Cada nivel trabaja con un volumen de datos más pequeño.
- Puedes eliminar datos antiguos de niveles inferiores (TTL) y almacenar agregados durante años.
- Al consultar un informe mensual, lees
daily_stats(365 filas por año), noraw_bets(miles de millones).
Analogía: Es como resumir la trama de un libro: primero lees el libro (datos sin procesar), luego escribes un resumen de capítulo (minuto), luego un resumen de parte (por hora), luego una anotación (diaria). Para recordar la idea principal, no necesitas releer todo el libro.
6. Patrón Null + MV para Kafka: Distribuir Datos a Múltiples Tablas
Este es un patrón profesional para sistemas de alta carga. En lugar de escribir en una tabla, creas una tabla Null (agujero negro) y varias MV que leen de ella y escriben en diferentes tablas destino.
-- Paso 1: Tabla receptora (Null, no almacena nada)
CREATE TABLE raw_events_null
(
user_id UInt64,
sport_id UInt8,
amount Decimal(18,2),
created_at DateTime,
ip String
)
ENGINE = Null;
-- Paso 2: Tabla destino 1 — todos los eventos sin procesar (para investigaciones)
CREATE TABLE raw_events_archive
(
user_id UInt64,
sport_id UInt8,
amount Decimal(18,2),
created_at DateTime,
ip String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_at)
ORDER BY (created_at, user_id);
-- Paso 3: Tabla destino 2 — apuestas deduplicadas (sin IP por GDPR)
CREATE TABLE bets_dedup
(
user_id UInt64,
sport_id UInt8,
amount Decimal(18,2),
created_at DateTime,
bet_id String
)
ENGINE = ReplacingMergeTree(created_at)
ORDER BY (user_id, bet_id);
-- Paso 4: Tabla destino 3 — agregados por hora
CREATE TABLE hourly_agg
(
hour DateTime,
sport_id UInt8,
total_amount AggregateFunction(sum, Decimal(18,2))
)
ENGINE = AggregatingMergeTree()
ORDER BY (hour, sport_id);
-- Paso 5: MV — archivo sin procesar (todas las filas)
CREATE MATERIALIZED VIEW mv_archive TO raw_events_archive AS
SELECT * FROM raw_events_null;
-- Paso 6: MV — apuestas deduplicadas (generar bet_id sobre la marcha)
CREATE MATERIALIZED VIEW mv_dedup TO bets_dedup AS
SELECT
user_id,
sport_id,
amount,
created_at,
concat(toString(user_id), '_', toString(sipHash64(created_at))) AS bet_id
FROM raw_events_null
WHERE amount != 0;
-- Paso 7: MV — agregados por hora
CREATE MATERIALIZED VIEW mv_hourly_agg TO hourly_agg AS
SELECT
toStartOfHour(created_at) AS hour,
sport_id,
sumState(amount) AS total_amount
FROM raw_events_null
GROUP BY hour, sport_id;Cómo funciona en la práctica:
-- El consumidor Kafka inserta en raw_events_null (rápido, sin operaciones de disco)
INSERT INTO raw_events_null VALUES (123, 1, 100.00, now(), '192.168.1.1');
-- Tres MV en paralelo reciben estos datos y escriben en sus tablas:
-- 1. mv_archive → raw_events_archive (copia completa, incluida IP)
-- 2. mv_dedup → bets_dedup (apuestas deduplicadas, sin IP)
-- 3. mv_hourly_agg → hourly_agg (actualiza agregados)Por qué esto es brillante:
- Una inserción — tres transformaciones diferentes.
- La tabla fuente no almacena nada (Null), sin sobrecarga de escritura.
- Cada MV se puede habilitar/deshabilitar de forma independiente.
- Fácil agregar una cuarta MV (por ejemplo, para exportar a otro formato).
7. El Problema: La MV Solo Procesa Datos Nuevos Después de su Creación
Una limitación crítica: La MV no ve los datos que ya estaban en la tabla fuente en el momento de su creación. La MV comienza a funcionar solo desde el momento de la creación y procesa solo nuevas inserciones.
-- Ya tenemos mil millones de filas en bets
SELECT count() FROM bets; -- 1,000,000,000
-- Crear MV
CREATE MATERIALIZED VIEW mv_hourly TO hourly_stats AS ...;
-- Insertar una nueva apuesta
INSERT INTO bets VALUES (now(), 1, 100);
-- Solo esta nueva apuesta aparecerá en hourly_stats.
-- ¡Los mil millones anteriores permanecen sin procesar!Cómo resolver el problema — rellenado de datos históricos:
-- Método 1: INSERT manual usando el mismo SELECT que en la MV
INSERT INTO hourly_stats
SELECT
toStartOfHour(created_at) AS hour,
sport_id,
count() AS bets_count,
sum(amount) AS total_amount
FROM bets
WHERE created_at < '2025-06-01' -- datos antiguos
GROUP BY hour, sport_id;
-- Ahora hourly_stats contiene tanto datos antiguos como nuevosMétodo 2: Recrear MV con POPULATE (¡peligroso!)
-- PELIGROSO: con CREATE ... POPULATE, la MV procesa TODA la tabla existente
-- Durante el procesamiento, la tabla puede bloquearse y los datos pueden perderse
CREATE MATERIALIZED VIEW mv_hourly TO hourly_stats POPULATE AS
SELECT ... FROM bets ...;Por qué POPULATE es peligroso en producción:
- Durante la ejecución del SELECT, la tabla fuente puede bloquearse.
- Si se insertan nuevos datos en la tabla fuente durante POPULATE, pueden perderse (no capturados ni por la MV antigua ni por la nueva).
- En tablas grandes, POPULATE puede llevar horas.
Enfoque correcto: Crear la MV sin POPULATE, y rellenar los datos históricos manualmente mediante INSERT con el mismo SELECT que en la MV. Esto es seguro, predecible y no bloquea las inserciones.
8. Monitoreo y Depuración de MVs
Verificar si las MVs están activas
-- Listar todas las MVs y sus tablas destino
SELECT
name,
engine,
total_rows,
formatReadableSize(total_bytes) AS size
FROM system.tables
WHERE engine = 'MaterializedView'
AND database = 'default';Verificar si la MV se activa en INSERT
-- Habilitar registro detallado (solo depuración)
SET log_queries = 1;
-- Insertar una fila en la tabla fuente
INSERT INTO bets (created_at, sport_id, amount) VALUES (now(), 1, 100);
-- Encontrar consultas relacionadas con la MV
SELECT
query,
query_duration_ms,
read_rows,
written_rows
FROM system.query_log
WHERE type = 'QueryFinish'
AND query LIKE '%MV%'
AND event_time > now() - INTERVAL 1 MINUTE
ORDER BY event_time DESC;Verificar datos en la tabla destino
-- Comparar número de grupos únicos en la tabla fuente y destino
-- (deberían coincidir después del rellenado)
-- Fuente: cuántos (hora, deporte) únicos
SELECT count(*) FROM (
SELECT toStartOfHour(created_at) AS hour, sport_id
FROM bets
GROUP BY hour, sport_id
) AS src;
-- Destino:
SELECT count(*) FROM hourly_stats;Identificar problemas de TIPO
Si la MV no se activa, a menudo el problema es la incompatibilidad de tipos:
-- Error: Columna `sport_id` tipo UInt8 en la tabla fuente, pero Int32 en la MV destino
-- Solución: convertir tipos explícitamente en SELECT
SELECT
toStartOfHour(created_at) AS hour,
toUInt8(sport_id) AS sport_id, -- conversión explícita
count() AS bets_count
FROM bets
GROUP BY hour, sport_id;9. Esquema Real para una Plataforma de Apuestas
Armemos una arquitectura completa para un casino en línea con múltiples requisitos:
- Eventos detallados para investigaciones (almacenar 7 días).
- Apuestas deduplicadas para análisis (almacenar 90 días).
- Agregados por hora para el panel de control (almacenar 2 años).
- Agregados diarios para informes financieros (almacenar 10 años).
-- ===== NIVEL 0: Receptor (Null) =====
CREATE TABLE raw_stream
(
user_id UInt64,
sport_id UInt8,
bet_id String,
amount Decimal(18,2),
created_at DateTime,
ip String
)
ENGINE = Null;
-- ===== TABLAS DESTINO =====
-- 1. Archivo por 7 días (MergeTree + TTL)
CREATE TABLE raw_archive
(
user_id UInt64, sport_id UInt8, bet_id String,
amount Decimal(18,2), created_at DateTime, ip String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_at)
ORDER BY created_at
TTL created_at + INTERVAL 7 DAY DELETE;
-- 2. Apuestas deduplicadas (ReplacingMergeTree)
CREATE TABLE bets_dedup
(
user_id UInt64, sport_id UInt8, bet_id String,
amount Decimal(18,2), created_at DateTime
)
ENGINE = ReplacingMergeTree(created_at)
ORDER BY (user_id, bet_id)
TTL created_at + INTERVAL 90 DAY DELETE;
-- 3. Agregados por hora (SummingMergeTree)
CREATE TABLE hourly_agg
(
hour DateTime, sport_id UInt8, total_amount Decimal(18,2), bet_count UInt64
)
ENGINE = SummingMergeTree()
ORDER BY (hour, sport_id)
TTL hour + INTERVAL 2 YEAR DELETE;
-- 4. Agregados diarios (AggregatingMergeTree para uniq)
CREATE TABLE daily_agg
(
day Date, sport_id UInt8, total_amount AggregateFunction(sum, Decimal(18,2)),
unique_users AggregateFunction(uniq, UInt64)
)
ENGINE = AggregatingMergeTree()
ORDER BY (day, sport_id)
TTL day + INTERVAL 10 YEAR DELETE;
-- ===== VISTAS MATERIALIZADAS =====
CREATE MATERIALIZED VIEW mv_raw_archive TO raw_archive AS
SELECT * FROM raw_stream;
CREATE MATERIALIZED VIEW mv_bets_dedup TO bets_dedup AS
SELECT user_id, sport_id, bet_id, amount, created_at
FROM raw_stream
WHERE bet_id != '';
CREATE MATERIALIZED VIEW mv_hourly_agg TO hourly_agg AS
SELECT
toStartOfHour(created_at) AS hour,
sport_id,
sum(amount) AS total_amount,
count() AS bet_count
FROM raw_stream
GROUP BY hour, sport_id;
CREATE MATERIALIZED VIEW mv_daily_agg TO daily_agg AS
SELECT
toDate(created_at) AS day,
sport_id,
sumState(amount) AS total_amount,
uniqState(user_id) AS unique_users
FROM raw_stream
GROUP BY day, sport_id;Flujo de datos:
- La aplicación escribe en
raw_stream(Null) — milisegundos. - Cuatro MV procesan cada inserción en paralelo.
- Cada tabla destino recibe datos en su forma agregada/transformada.
- TTL elimina automáticamente los datos obsoletos en cada nivel.
Ventajas sobre un solo MERGETREE:
- Máxima velocidad de inserción (Null + MV trabajan en memoria).
- Diferentes niveles de almacenamiento (7 días, 90 días, 2 años, 10 años).
- Diferentes motores para diferentes tareas (MergeTree, ReplacingMergeTree, SummingMergeTree, AggregatingMergeTree).
- Sin pérdida de rendimiento al consultar agregados antiguos.
Qué Sigue
Ahora sabes cómo construir tuberías complejas de procesamiento de datos usando MVs. Próximos temas:
- MV con actualizaciones de fuentes externas — cómo combinar diccionarios y MVs para enriquecimiento.
- Depuración de cadenas complejas de MV — cómo entender por qué los datos no llegaron a la tabla destino.
- Replicación de MV en un clúster — cómo se comportan las MVs en tablas distribuidas.
Resumen: Las vistas materializadas en ClickHouse no son solo un caché. Son un poderoso mecanismo de procesamiento incremental de datos que permite construir arquitecturas de event-sourcing dentro de la base de datos. La regla es simple: si necesitas agregar, transformar o duplicar datos al insertar — usa MV. Y nunca olvides rellenar los datos históricos manualmente, no mediante POPULATE en producción.
← Anterior: Motores Especiales de ClickHouse: Cuando MergeTree No es Suficiente
→ Siguiente: Índices Secundarios (de Salto) en ClickHouse: Cuando el Índice ORDER BY No es Suficiente
— Editorial Team
Aún no hay comentarios.