Diccionarios en ClickHouse: búsqueda rápida sin JOIN

Diccionarios en ClickHouse: Búsqueda Rápida Sin JOIN

1. Por Qué se Necesitan los Diccionarios — El Problema del JOIN con Datos de Referencia

Volvamos a nuestro casino online. Tienes una tabla bets que almacena sport_id — un número del 1 al 20. Pero en los informes necesitas mostrar el nombre del deporte: "Fútbol", "Hockey", "Tenis". Esta información suele vivir en una tabla de referencia separada sports.

-- Consulta lenta con JOIN
SELECT 
    b.user_id,
    s.name AS sport_name,
    sum(b.amount) AS total
FROM bets b
JOIN sports s ON b.sport_id = s.id
GROUP BY b.user_id, s.name;

Para mil millones de filas en bets y 20 filas en sports, este JOIN copiará la tabla de referencia por cada fragmento de datos. ClickHouse realiza un join broadcast (envía la tabla pequeña a todos los shards), que es rápido pero aún consume memoria y CPU.

Los diccionarios resuelven este problema de manera diferente. Un diccionario es una tabla de referencia en memoria que vive dentro de ClickHouse. Puedes buscar una clave y obtener un valor en microsegundos sin ejecutar un JOIN.

Analogía de la vida real: Un diccionario es como una chuleta para un examen. Tienes una lista de 20 filas: "1 = fútbol, 2 = hockey...". Cuando necesitas encontrar el nombre del deporte por ID, solo echas un vistazo a la chuleta (memoria), en lugar de ir a la biblioteca por un libro de referencia grueso (disco). Es miles de veces más rápido.

Por qué esto importa en ClickHouse: ClickHouse almacena datos en disco, y leer incluso una tabla pequeña mediante JOIN requiere operaciones de disco. Un diccionario reside en memoria (comprimido y optimizado), y acceder a él es simplemente leer de RAM.

2. Tipos de Diccionarios — Cómo Elegir la Estructura

ClickHouse ofrece varios tipos de diccionarios (LAYOUT) dependiendo de:

• el tamaño de los datos (cuántas claves),
• el tipo de clave (simple o compuesta),
• si se necesita búsqueda por rango (ej. tipo de cambio en una fecha).

Tipo	Cuándo Usarlo	Máx. Claves	Características
flat	Diccionarios muy pequeños (hasta 500k claves)	500,000	Más rápido, almacenado en un array. La clave debe ser entera (UInt*).
hashed	Diccionarios medianos (millones de claves)	Ilimitado	Tabla hash. Adecuado para cualquier tipo de clave. Ligeramente más lento que flat.
sparse_hashed	Muy grandes (decenas de millones)	Muchísimas	Ahorra memoria (no almacena valores nulos), pero ligeramente más lento.
range_hashed	Rangos de fechas (tipo de cambio por fecha)	Ilimitado	Clave + rango (inicio, fin). Permite búsqueda get(key, date).
complex_key_hashed	Clave compuesta (ej. market_id, selection_id)	Ilimitado	La clave es una tupla de varios campos.
ip_trie	Direcciones IP (búsqueda por prefijo)	Hasta 500k	Para GeoIP: encontrar país/ciudad por IP.

Cómo elegir:

• Menos de 500k claves y clave entera → flat (máxima velocidad).
• Más de 500k claves o clave no entera → hashed.
• Muchas claves y muchos valores nulos → sparse_hashed.
• Necesidad de búsqueda por fecha → range_hashed.
• Clave compuesta (varios campos) → complex_key_hashed.

Analogía: flat es como un armario con cajones numerados (índice = número). Vas directo al cajón #17. hashed es como un catálogo de biblioteca donde primero calculas el estante haciendo hash del apellido del autor. range_hashed es como un archivo donde buscas un documento sabiendo la fecha.

3. Fuentes de Datos — De Dónde Obtiene el Diccionario los Datos

Un diccionario puede poblarse desde varias fuentes (SOURCE). ClickHouse actualiza periódicamente el diccionario desde la fuente en un intervalo especificado (LIFETIME).

Fuentes compatibles:

• CLICKHOUSE — otra tabla de ClickHouse
• MYSQL — tabla MySQL
• POSTGRESQL — tabla PostgreSQL
• HTTP — API REST (JSON o XML)
• FILE — archivo local (CSV, TSV)
• REDIS — Redis (clave-valor)
• MONGODB — colección MongoDB

Ejemplo con MySQL:

CREATE DICTIONARY currencies_dict
(
    code String,
    name String,
    rate Decimal(10,4)
)
PRIMARY KEY code
SOURCE(MYSQL(
    host 'mysql-host'
    port 3306
    user 'reader'
    password 'secret'
    db 'reference'
    table 'currencies'
))
LIFETIME(MIN 3600 MAX 7200)   -- actualizar cada 1-2 horas
LAYOUT(HASHED());

Por qué es conveniente: Tu referencia de monedas puede actualizarse una vez por hora desde una base de datos MySQL externa mantenida por el departamento de finanzas. ClickHouse obtiene los cambios automáticamente; no necesitas escribir un script ETL.

4. Crear un Diccionario desde una Tabla de ClickHouse — Paso a Paso

El escenario más común: ya tienes una tabla de referencia en ClickHouse y quieres convertirla en un diccionario para búsquedas rápidas.

Paso 1: Crear la tabla de referencia (si no existe)

CREATE TABLE sports
(
    id       UInt32,          -- ID del deporte (1, 2, 3...)
    name     String,          -- 'Fútbol', 'Hockey', 'Tenis'
    category String           -- 'equipo', 'individual', 'esports'
)
ENGINE = MergeTree()
ORDER BY id;

-- Poblar con datos
INSERT INTO sports VALUES (1, 'Fútbol', 'equipo'), (2, 'Hockey', 'equipo'), (3, 'Tenis', 'individual');

Paso 2: Crear un diccionario sobre esta tabla

CREATE DICTIONARY sports_dict
(
    id       UInt32,          -- columna clave
    name     String,          -- valor a recuperar
    category String           -- otro valor
)
PRIMARY KEY id                -- clave de búsqueda
SOURCE(CLICKHOUSE(
    host 'localhost'
    port 9000
    user 'default'
    password ''
    db 'default'
    table 'sports'
))
LIFETIME(MIN 300 MAX 600)     -- actualizar cada 5-10 minutos
LAYOUT(HASHED());              -- para nuestros 20 registros, flat también funciona, pero hashed también sirve

Desglose de los parámetros:

• PRIMARY KEY id — la columna utilizada para la búsqueda. Debe ser única.
• SOURCE(CLICKHOUSE(...)) — fuente de datos. Puedes especificar cualquier host, no solo localhost.
• LIFETIME(MIN 300 MAX 600) — el diccionario se recargará completamente cada 5–10 minutos. MIN y MAX se usan para aleatorización y evitar que todos los diccionarios en todos los servidores se actualicen simultáneamente.
• LAYOUT(HASHED()) — estructura en memoria. Para 20 registros, flat es mejor, pero mantenemos hashed como ejemplo.

¿Qué sucede después de la creación? ClickHouse lee toda la tabla sports, la carga en memoria como una tabla hash. Ahora puedes usar dictGet para acceso rápido.

5. Usar Diccionarios en Consultas — dictGet y Amigos

La magia real comienza en SELECT. En lugar de JOIN sports, usas funciones de diccionario.

dictGet — la función principal

-- Obtener nombre del deporte por sport_id
SELECT 
    user_id,
    sport_id,
    dictGet('sports_dict', 'name', sport_id) AS sport_name,
    amount
FROM bets
LIMIT 10;

Sintaxis: dictGet('nombre_diccionario', 'columna_valor', clave)

dictGetOrDefault — con un valor por defecto

-- Si no se encuentra sport_id, devolver 'Desconocido'
SELECT 
    user_id,
    sport_id,
    dictGetOrDefault('sports_dict', 'name', sport_id, 'Desconocido') AS sport_name
FROM bets;

dictHas — comprobar si existe una clave

-- Encontrar apuestas con sport_id inválido
SELECT DISTINCT sport_id
FROM bets
WHERE dictHas('sports_dict', sport_id) = 0;   -- devuelve sport_ids no presentes en el diccionario

Ejemplo completo con agregación

-- Top 5 deportes por monto total apostado sin JOIN
SELECT 
    dictGet('sports_dict', 'name', sport_id) AS sport_name,
    sum(amount) AS total_amount,
    count() AS bet_count
FROM bets
WHERE created_at >= today() - 7
GROUP BY sport_id
ORDER BY total_amount DESC
LIMIT 5;

Por qué es más rápido que JOIN: Sin lecturas de disco, sin distribución de la tabla de referencia entre shards, sin hashing en tiempo de consulta. El diccionario ya está en memoria en cada nodo de ClickHouse.

6. Claves Complejas — dictGet con Tupla

Cuando la clave consta de múltiples campos (ej. market_id + selection_id), usa LAYOUT(COMPLEX_KEY_HASHED()) y pasa la clave como una tupla.

Crear un diccionario con clave compuesta:

-- Diccionario de cuotas: (market_id, selection_id) → valor de cuota
CREATE DICTIONARY odds_dict
(
    market_id    UInt32,
    selection_id UInt32,
    odds_value   Decimal(10,3)
)
PRIMARY KEY (market_id, selection_id)   -- clave compuesta
SOURCE(CLICKHOUSE(
    table 'odds_reference'
))
LIFETIME(MIN 60 MAX 120)
LAYOUT(COMPLEX_KEY_HASHED());            -- ¡debe ser complex_key!

Uso en consultas:

-- Obtener cuota para un mercado y resultado específicos
SELECT 
    bet_id,
    market_id,
    selection_id,
    dictGet('odds_dict', 'odds_value', tuple(market_id, selection_id)) AS odds
FROM bets;

¿Qué es una tupla? Una tupla es simplemente un grupo de valores envueltos entre paréntesis. tuple(market_id, selection_id) crea una clave como (100, 5).

7. Diccionarios de Rango — para Datos Históricos (Tipo de Cambio en una Fecha)

Imagina que tienes tipos de cambio históricos que cambian a diario. Para cada apuesta en euros, necesitas la tasa en la fecha de la apuesta.

Tabla fuente (ej. en MySQL):

currency	start_date	end_date	rate
EUR	2025-01-01	2025-01-31	1.05
EUR	2025-02-01	2025-02-28	1.08
EUR	2025-03-01	2099-12-31	1.10

Crear un diccionario de rango:

CREATE DICTIONARY eur_rates_dict
(
    currency   String,
    start_date Date,
    end_date   Date,
    rate       Decimal(10,4)
)
PRIMARY KEY currency
SOURCE(CLICKHOUSE(table 'eur_rates'))
LIFETIME(MIN 3600 MAX 7200)
LAYOUT(RANGE_HASHED())                     -- tipo especial
RANGE(MIN start_date MAX end_date);        -- especificar columnas de rango

Uso:

-- Para cada apuesta en EUR, obtener la tasa en la fecha de la apuesta
SELECT 
    bet_id,
    amount_eur,
    created_at,
    dictGet('eur_rates_dict', 'rate', tuple(currency, created_at)) AS rate
FROM bets
WHERE currency = 'EUR';

ClickHouse encuentra automáticamente el registro donde created_at cae entre start_date y end_date para la moneda dada.

Analogía: Es como un calendario de cambios de precio. Dices, "Dame la tasa para el 15 de marzo", y el diccionario revisa su calendario: el 15 de marzo cae en el intervalo del 1 al 31 de marzo, tasa 1.10.

8. Monitorear Diccionarios — system.dictionaries

Para entender qué sucede con los diccionarios, existe la tabla del sistema system.dictionaries.

SELECT *
FROM system.dictionaries
WHERE name = 'sports_dict';

Columnas útiles:

Columna	Qué muestra
status	LOADED — cargado, LOADING — cargando, FAILED — error
origin	Fuente (ClickHouse, MySQL...)
type	Tipo (flat, hashed, range_hashed...)
key	Tipo de clave
attribute.names	Columnas disponibles
bytes_allocated	Uso de memoria (bytes)
query_count	Número de búsquedas
hit_rate	Tasa de aciertos (más alto es mejor)
load_factor	Qué tan lleno está el diccionario (para hashed)
creation_time	Cuándo se cargó
last_exception	Si el estado es FAILED, aquí está el error

Monitoreo de memoria:

SELECT 
    name,
    formatReadableSize(bytes_allocated) AS memory,
    query_count,
    hit_rate
FROM system.dictionaries
WHERE status = 'LOADED'
ORDER BY bytes_allocated DESC;

Si un diccionario ocupa gigabytes, quizás elegiste el LAYOUT incorrecto (ej. hashed en lugar de sparse_hashed).

9. Recarga en Caliente — SYSTEM RELOAD DICTIONARY

Los diccionarios se actualizan automáticamente según LIFETIME. Pero a veces necesitas forzar una actualización:

• Acabas de corregir datos en la fuente y no quieres esperar 10 minutos.
• El diccionario falló (ej. la fuente no estaba disponible) y solucionaste el problema.

-- Recargar un diccionario específico
SYSTEM RELOAD DICTIONARY sports_dict;

-- Recargar todos los diccionarios
SYSTEM RELOAD DICTIONARIES;

¿Qué sucede? ClickHouse vuelve a leer la fuente (ej. la tabla sports) y reemplaza el contenido del diccionario en memoria. Durante la recarga, las consultas que usan dictGet esperarán (o devolverán datos antiguos, según la versión). Para sistemas críticos, realiza recargas por la noche.

Cómo verificar que el diccionario se cargó correctamente:

SELECT status, last_exception 
FROM system.dictionaries 
WHERE name = 'sports_dict';

Si el estado es LOADED, todo está bien. Si es FAILED, revisa last_exception.

10. Arquitectura de Ejemplo: Todos los Diccionarios de Referencia para una Plataforma de Apuestas

Imagina una arquitectura completa de plataforma de apuestas. Tienes docenas de diccionarios de referencia usados constantemente en consultas para enriquecer datos.

Diccionarios a crear:

-- 1. Deportes (20 registros, FLAT)
CREATE DICTIONARY sports_dict (id UInt32, name String, category String)
PRIMARY KEY id
SOURCE(CLICKHOUSE(table 'sports'))
LIFETIME(3600) LAYOUT(FLAT());

-- 2. Ligas/Campeonatos (10k registros, HASHED)
CREATE DICTIONARY leagues_dict (id UInt32, name String, sport_id UInt32, country_id UInt32)
PRIMARY KEY id
SOURCE(CLICKHOUSE(table 'leagues'))
LIFETIME(3600) LAYOUT(HASHED());

-- 3. Países (200 registros, FLAT)
CREATE DICTIONARY countries_dict (id UInt32, name String, code String)
PRIMARY KEY id
SOURCE(CLICKHOUSE(table 'countries'))
LIFETIME(86400) LAYOUT(FLAT());   -- rara vez cambian, actualizar una vez al día

-- 4. Monedas con tasas históricas (RANGE)
CREATE DICTIONARY exchange_rates_dict (currency String, start_date Date, end_date Date, rate Decimal(10,4))
PRIMARY KEY currency
SOURCE(CLICKHOUSE(table 'exchange_rates'))
LIFETIME(3600) LAYOUT(RANGE_HASHED()) RANGE(MIN start_date MAX end_date);

-- 5. Comisiones por país y tipo de apuesta (COMPLEX_KEY)
CREATE DICTIONARY commission_dict (country_id UInt32, bet_type String, commission Decimal(5,2))
PRIMARY KEY (country_id, bet_type)
SOURCE(CLICKHOUSE(table 'commissions'))
LIFETIME(7200) LAYOUT(COMPLEX_KEY_HASHED());

Uso en una sola consulta:

SELECT 
    b.user_id,
    dictGet('sports_dict', 'name', b.sport_id) AS sport_name,
    dictGet('leagues_dict', 'name', b.league_id) AS league_name,
    dictGet('countries_dict', 'name', dictGet('leagues_dict', 'country_id', b.league_id)) AS country_name,
    b.amount_eur * dictGet('exchange_rates_dict', 'rate', tuple('EUR', toDate(b.created_at))) AS amount_usd,
    dictGet('commission_dict', 'commission', tuple(dictGet('leagues_dict', 'country_id', b.league_id), 'prematch')) AS commission
FROM bets b
WHERE b.created_at >= today() - 7;

Ventajas de este enfoque:

• Velocidad: Sin JOINs, solo búsquedas directas en memoria.
• Legibilidad: El código es más claro — puedes ver inmediatamente qué diccionarios se usan.
• Mantenibilidad: Actualizar una referencia (ej. comisión para España) ocurre en un solo lugar, no en scripts ETL.
• Eficiencia de memoria: Los diccionarios se almacenan comprimidos, a menudo ocupando menos espacio que una columna desnormalizada en una tabla.

¿Qué pasa si no usas diccionarios? O desnormalizas los datos (repites el nombre del deporte en cada fila de apuesta — multiplicando el volumen de datos por 10+ veces) o realizas un JOIN en cada agregación (lo cual es lento y doloroso con miles de millones de filas).

Qué Sigue

Ahora sabes todo sobre los diccionarios. Próximos temas:

• Actualizar diccionarios vía HTTP — cómo obtener datos de una API externa.
• Usar diccionarios en vistas materializadas — para pre-enriquecer datos.
• Clustering de diccionarios — cómo se comportan los diccionarios en un clúster de ClickHouse (Distributed).

Conclusión: Los diccionarios son una herramienta indispensable para trabajar con datos de referencia en ClickHouse. Convierten JOINs lentos con tablas pequeñas en búsquedas ultrarrápidas en memoria. La regla es simple: si la referencia no cambia más de una vez por minuto y su tamaño permite almacenarla en RAM — conviértela en un diccionario. Tus consultas te lo agradecerán.

---

← Anterior: TTL en ClickHouse: Gestión Automática del Ciclo de Vida de los Datos
→ Siguiente: Motores Especiales de ClickHouse: Cuando MergeTree No es Suficiente

— Editorial Team