Motores Especiales de ClickHouse: Cuando MergeTree No Es Necesario

Motores Especiales de ClickHouse: Cuando MergeTree No es Suficiente

1. Motor MEMORY — Tabla en RAM para Datos Temporales

Imagina que necesitas procesar rápidamente un lote de apuestas — agruparlas, calcular totales intermedios y luego enviarlas a la tabla principal. No quieres escribir en disco porque los datos son temporales y solo se necesitan durante la consulta.

El motor Memory almacena los datos completamente en RAM. Es el motor más rápido — sin operaciones de disco, sin compresión, sin índices (excepto la clave primaria). Pero tiene un costo: cuando ClickHouse se reinicia, la tabla queda vacía. Los datos NO se persisten.

Cuándo usarlo:

• Tablas de staging para procesos ETL. Por ejemplo, cargaste un millón de apuestas desde Kafka, las deduplicaste y solo entonces las insertaste en la tabla MergeTree principal.
• Caché de cuotas en vivo — las cuotas cambian cada segundo, no necesitas almacenar historial, solo la instantánea actual.
• Pequeñas tablas de referencia (hasta 10–15 millones de filas) que se recrean en cada ejecución de script.

Ejemplo para caché de cuotas en vivo:

-- Tabla para cuotas actuales (vive en RAM)
CREATE TABLE live_odds_cache
(
    event_id     UInt64,          -- ID del evento (partido)
    market_id    UInt32,          -- ID del mercado
    selection_id UInt32,          -- ID de la selección
    odds         Decimal(10,3),   -- Cuota
    updated_at   DateTime
)
ENGINE = Memory()
ORDER BY (event_id, market_id, selection_id);   -- ORDER BY es obligatorio, pero el índice es ineficiente

Insertando datos (por ejemplo, desde un stream):

-- Llegó una nueva cuota, insertar
INSERT INTO live_odds_cache VALUES (100500, 10, 200, 1.85, now());

-- Leer cuota actual para una apuesta
SELECT odds FROM live_odds_cache 
WHERE event_id = 100500 AND market_id = 10 AND selection_id = 200;

Peligros:

• La tabla Memory no soporta merges — si haces muchos UPDATEs (mediante insert con cancelación), la memoria se inflará. Usa TRUNCATE para limpiar.
• Al reiniciar ClickHouse, los datos se pierden. No almacenes nada crítico aquí.
• El tamaño de la tabla está limitado por la RAM disponible. Si la tabla crece a 50 GB en un servidor con 64 GB de RAM, el servidor se caerá.

Analogía: El motor Memory es como una pizarra. Rápido para escribir, rápido para leer, pero después de que el conserje (reinicio) pasa, la pizarra está vacía.

2. Motor BUFFER — Almacenando INSERTs en Búfer Antes de Escribir

Tienes 10,000 apuestas por segundo. Cada apuesta es un INSERT separado. Si escribes cada una directamente en una tabla MergeTree, ClickHouse creará miles de partes diminutas, ralentizando las fusiones en segundo plano y reduciendo el rendimiento.

El motor Buffer resuelve esto: recoge los inserts en un búfer en memoria y los escribe en la tabla destino en lotes grandes cuando se cumplen las condiciones (por número de filas, tamaño o tiempo).

Sintaxis con parámetros:

CREATE TABLE bets_buffer AS bets   -- copia la estructura de la tabla bets
ENGINE = Buffer(
    'default',        -- nombre de la base de datos de la tabla destino
    'bets',           -- nombre de la tabla destino (los datos se volcarán aquí)
    16,               -- número de hilos de volcado paralelos
    10,               -- retardo mínimo en segundos (min_time)
    100,              -- retardo máximo en segundos (max_time)
    10000,            -- número mínimo de filas para volcar
    1000000,          -- número máximo de filas para volcar
    10000000,         -- tamaño mínimo en bytes para volcar
    100000000         -- tamaño máximo en bytes para volcar
);

Parámetros de volcado:

Parámetro	Valor	Significado
min_time	10 seg	No volcar antes de 10 segundos
max_time	100 seg	Volcar a más tardar a los 100 segundos
min_rows	10,000	Si se acumulan 10k filas, se puede volcar
max_rows	1,000,000	Si se acumulan 1 millón de filas, volcar urgentemente
min_bytes	10 MB	Si se acumulan 10 MB, se puede volcar
max_bytes	100 MB	Si se acumulan 100 MB, volcar urgentemente

Cómo funciona en la práctica:

1. Insertas en bets_buffer (rápido, solo escritura en memoria).
2. ClickHouse espera hasta que se acumulen suficientes datos (por ejemplo, 100k filas o pasen 30 segundos).
3. Luego asincrónicamente (en segundo plano) vuelca el lote a la tabla principal bets (MergeTree).
4. Como resultado, bets recibe partes grandes (100k filas), acelerando las fusiones en segundo plano.

¿Por qué no insertar directamente en MergeTree? Cada INSERT en MergeTree crea una minipartícula. Si haces 10,000 INSERTs por segundo, después de un minuto tendrás 600,000 partes. La fusión en segundo plano no puede seguir el ritmo. ClickHouse se quejará de Too many parts, y los inserts se ralentizarán.

Peligro: Al reiniciar ClickHouse, el búfer se pierde. Los datos que no se han volcado a bets desaparecen. Por lo tanto, usa el motor Buffer solo si es aceptable perder unos segundos de datos (por ejemplo, para análisis, no para saldos).

3. Motor NULL — Agujero Negro para Datos

El motor Null simplemente absorbe datos. No se escribe en ningún lado, no se almacena, no se indexa. Pero hay un truco: si una tabla con motor Null tiene Vistas Materializadas, esas vistas reciben los datos y los procesan.

Patrón: Kafka → Null + Vista Materializada → MergeTree

Esta es una arquitectura clásica para inserts de alta carga desde Kafka.

-- Paso 1: Tabla sumidero (agujero negro)
CREATE TABLE bets_null
(
    user_id     UInt64,
    amount      Decimal(18,2),
    created_at  DateTime
)
ENGINE = Null;   -- no almacena nada

-- Paso 2: Tabla destino (donde realmente guardamos)
CREATE TABLE bets
(
    user_id     UInt64,
    amount      Decimal(18,2),
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (created_at, user_id);

-- Paso 3: Vista Materializada (puente)
CREATE MATERIALIZED VIEW bets_mv TO bets AS
SELECT * FROM bets_null;   -- cualquier dato que entre en bets_null termina en bets

Qué sucede ahora:

-- El cliente (o consumidor Kafka) inserta en bets_null
INSERT INTO bets_null VALUES (123, 100.00, now());   -- instantáneo

-- Los datos pasan a través de la Vista Materializada y se guardan en bets
-- No se guardan en bets_null

¿Por qué hacer esto?

• bets_null es una tabla muy ligera; no crea archivos en disco.
• Todos los suscriptores (Vistas Materializadas) reciben los datos simultáneamente.
• Puedes adjuntar múltiples Vistas Materializadas a una tabla Null: una para datos brutos en MergeTree, otra para agregados en AggregatingMergeTree, otra para deduplicación en ReplacingMergeTree.

Analogía: El motor Null es como un buzón con un agujero en el fondo. Las cartas caen pero no se quedan. Pero todos tus secretarios (Vistas Materializadas) logran leerlas y copiarlas en sus carpetas.

4. Motores Log/TinyLog/StripeLog — Motores Simples para Datos Pequeños

Esta familia de motores es para tablas pequeñas (hasta 1–2 millones de filas) donde no se necesitan alto rendimiento ni índices.

Motor	Características	Cuándo usarlo
TinyLog	Un archivo por columna	Tablas muy pequeñas (<100k filas), staging
Log	Cada columna en un archivo separado, tiene un marcador para lectura paralela	Tablas de hasta 1 millón de filas, se necesita lectura rápida
StripeLog	Todas las columnas en un archivo (compacto)	Ahorro de espacio, lectura poco frecuente

Ejemplo para una tabla de referencia de ligas (200 filas):

-- Tabla de referencia de ligas de fútbol (cambia una vez al mes)
CREATE TABLE leagues_ref
(
    league_id   UInt32,
    name        String,
    country     String,
    updated_at  Date
)
ENGINE = TinyLog();   -- lo más simple posible, sin ORDER BY

¿Por qué no MergeTree? MergeTree crea índices, particiones, compresión — es excesivo para 200 filas. TinyLog ocupa menos espacio y es más simple de mantener.

Peligro: Estos motores no soportan ALTER DELETE ni ALTER UPDATE. Si necesitas modificar datos, tendrás que recrear la tabla.

5. Motor URL — Tabla como Endpoint HTTP

El motor URL permite leer datos directamente desde una fuente HTTP (API) e incluso insertar datos mediante PUT.

CREATE TABLE currency_rates_url
(
    base   String,
    rate   Decimal(10,4),
    date   Date
)
ENGINE = URL('https://api.exchangerate.com/latest?base=USD', CSV)
SETTINGS 
    method = 'GET',
    format = 'CSV',
    headers = 'Authorization: Bearer token123';

Uso:

-- Leer tasas actuales directamente desde la API
SELECT * FROM currency_rates_url;

Escenario real: Una pequeña tarea analítica donde no quieres configurar ETL. Por ejemplo, una vez por hora lees tasas de cambio de una API gratuita, las unes con apuestas y recalculas montos.

Peligros:

• Sin índices; cada consulta hace un escaneo completo de la fuente.
• Si la API devuelve un error, la consulta falla.
• No es adecuado para consultas de alta carga (se asume que los datos están en caché dentro de ClickHouse, no se leen de la API cada vez).

6. Motor FILE — Tabla como Archivo en Disco

Permite leer y escribir archivos en el sistema de archivos local del servidor ClickHouse. Soporta formatos CSV, TSV, JSONEachRow, Parquet.

-- Tabla que lee un archivo CSV
CREATE TABLE imported_players
(
    user_id UInt64,
    username String
)
ENGINE = File(CSV, '/var/lib/clickhouse/user_files/players.csv');

Cuándo usarlo:

• Cargar datos desde archivos (un administrador colocó un CSV con nuevos usuarios).
• Exportar resultados de consultas a un archivo mediante INSERT INTO ... SELECT.

Peligro: ClickHouse debe tener acceso a la carpeta (generalmente /var/lib/clickhouse/user_files/ por seguridad).

7. Motor S3 — Consultas Directas a S3

Lee datos directamente desde un bucket de Amazon S3 (o MinIO, Yandex Object Storage). No copia datos en ClickHouse.

CREATE TABLE logs_s3
(
    timestamp DateTime,
    message String
)
ENGINE = S3(
    'https://mybucket.s3.amazonaws.com/logs/*.parquet',
    'AWS_ACCESS_KEY', 'AWS_SECRET_KEY',
    'Parquet'
);

Cuándo usarlo:

• Tienes terabytes de logs en S3 y quieres ejecutar consultas analíticas ocasionales sin copiarlos en ClickHouse.
• Datos fríos (S3 es más barato que los discos de ClickHouse).

Peligro: Cada consulta descarga datos de S3, lo que puede ser lento y costoso (por tráfico saliente). Solo adecuado para consultas poco frecuentes.

8. Motor PostgreSQL — Datos en Vivo desde PostgreSQL

El motor PostgreSQL permite leer y escribir en tablas de PostgreSQL como si fueran tablas de ClickHouse.

CREATE TABLE pg_players
(
    user_id UInt64,
    balance Decimal(18,2)
)
ENGINE = PostgreSQL(
    'postgres-host:5432',   -- host y puerto
    'betting',               -- base de datos
    'players',               -- tabla en PostgreSQL
    'clickhouse_user',       -- usuario
    'password'               -- contraseña
);

Uso:

-- Leer saldos actuales desde PostgreSQL
SELECT * FROM pg_players WHERE user_id = 123;

-- Incluso puedes hacer JOIN con tablas de ClickHouse
SELECT b.user_id, b.amount, p.balance
FROM bets b
JOIN pg_players p ON b.user_id = p.user_id;

Cuándo usarlo:

• Estás migrando gradualmente de PostgreSQL a ClickHouse y algunos datos aún viven en la base de datos antigua.
• Necesitas datos en vivo actualizados por una aplicación externa y no quieres configurar ETL.

Peligros:

• Cada consulta va a PostgreSQL, lo que puede ser lento para grandes volúmenes.
• ClickHouse no puede construir un plan de ejecución eficiente que involucre dicha tabla (sin pushdown).

9. Patrón Arquitectónico para Apuestas: Kafka → Buffer → MergeTree

Ahora juntemos todo. Imagina que recibes 10,000+ mensajes por segundo desde Kafka por cada evento de apuesta. Necesitas guardarlos en ClickHouse con la mínima latencia y sin crear miles de partes diminutas.

Arquitectura lista:

-- 1. Tabla sumidero (Null) para Kafka
CREATE TABLE bets_kafka
(
    user_id     UInt64,
    event_id    UInt64,
    amount      Decimal(18,2),
    bet_time    DateTime
)
ENGINE = Null;

-- 2. Tabla MergeTree destino
CREATE TABLE bets
(
    user_id     UInt64,
    event_id    UInt64,
    amount      Decimal(18,2),
    bet_time    DateTime
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(bet_time)
ORDER BY (bet_time, user_id);

-- 3. Tabla Buffer para suavizar inserts
CREATE TABLE bets_buffer AS bets
ENGINE = Buffer('default', 'bets', 16, 5, 60, 10000, 1000000, 10000000, 100000000);

-- 4. Vista Materializada: Kafka → Null → Buffer (a través de la vista)
CREATE MATERIALIZED VIEW bets_kafka_mv TO bets_buffer AS
SELECT * FROM bets_kafka;

-- 5. Otra vista para agregados en tiempo real (opcional)
CREATE MATERIALIZED VIEW bets_stats_mv TO bets_hourly_agg AS
SELECT 
    toStartOfHour(bet_time) AS hour,
    countState() AS bet_count,
    sumState(amount) AS total_amount
FROM bets_kafka
GROUP BY hour;

Flujo de datos:

1. Kafka Connect inserta mensajes en bets_kafka (motor Null).
2. bets_kafka_mv (Vista Materializada) redirige los datos a bets_buffer.
3. bets_buffer acumula lotes en memoria (por ejemplo, 100k filas o 60 segundos).
4. El búfer se vuelca a bets (MergeTree) en partes grandes.
5. En paralelo, la segunda Vista Materializada construye agregados por hora para paneles.

Por qué esto es óptimo:

• Kafka escribe en Null (instantáneo, sin sobrecarga).
• Buffer evita que se multipliquen miles de partes diminutas.
• MergeTree recibe partes grandes, las fusiones funcionan eficientemente.
• Los agregados se construyen en tiempo real mediante la segunda vista.

¿Qué pasa si escribes directamente en MergeTree desde Kafka? A 10k eventos/seg, se crean 600k partes en un minuto. ClickHouse se cae con Too many parts. El motor Buffer te salva de esto.

Cuándo Elegir Cada Motor — Hoja de Referencia

Tarea	Motor	Por qué
Almacenamiento persistente, análisis	MergeTree (o *MergeTree)	Base de ClickHouse, índices, compresión
Amortiguar cargas altas	Buffer	Une pequeños INSERTs en partes grandes
Datos temporales (sesión, staging)	Memory	Máxima velocidad, datos no críticos
Consumidor Kafka sin almacenamiento	Null + MV	Datos solo para vistas
Pequeña tabla de referencia estática	TinyLog / Log	Simplicidad, menos metadatos
Conectar a PostgreSQL	PostgreSQL ENGINE	Datos en vivo sin ETL
Consultas raras a S3	S3 ENGINE	Almacenamiento frío barato
Importar desde archivos	File ENGINE	Copia única

Qué Sigue

Has aprendido sobre motores especiales que resuelven problemas más allá del MergeTree regular. Ahora sabes:

• Memory para caché y staging,
• Buffer para protección contra INSERTs demasiado frecuentes,
• Null para "ramificar" datos en múltiples flujos mediante MV,
• URL / File / S3 / PostgreSQL para datos externos.

Próximos temas a profundizar:

• Cómo configurar Kafka Engine — conector integrado a Kafka sin un conector separado.
• Vistas Materializadas avanzadas — cadenas de vistas para ETL complejo.
• Tablas distribuidas — cómo fragmentar datos entre servidores.

Conclusión: No todas las tareas en ClickHouse se resuelven con MergeTree. A veces necesitas Buffer para evitar matar el servidor con inserts, a veces Null + MV para distribuir datos a diferentes agregados, y a veces Memory para hashing temporal. La regla principal: diseña el flujo de datos primero, luego elige el motor, no al revés.

---

← Anterior: Diccionarios en ClickHouse: Búsqueda Rápida Sin JOIN
→ Siguiente: Vistas Materializadas en ClickHouse: El Poder del Procesamiento Incremental

— Editorial Team