El Kit de Lookups: Diccionarios y JOINs en ClickHouse®

Tienes datos en dos tablas y necesitas combinarlos en tiempo de query. En el mundo OLTP harías un JOIN sin pensártelo dos veces. En ClickHouse, ese reflejo está mal el 80% de las veces.

ClickHouse te da herramientas distintas para el trabajo combinatorio, y la elección equivocada cuesta unas 20×. Lo medimos esta semana en nuestro propio cluster: 11 segundos con dictGet, 225 segundos con el JOIN equivalente, sobre el mismo enriquecimiento de 10.000 millones de filas. Los números completos están en la sección de benchmarks, más abajo.

Este post es un repaso a fondo de dictGet (el default correcto), un matiz operativo sobre los JOINs (la salida de emergencia) y la jugada que les gana a ambos cuando el enriquecimiento es estable: resolverlo una sola vez en la ingesta para que el lookup en tiempo de query desaparezca. Al final, un árbol de decisión de una página que te puedes guardar.

Por qué en OLAP el JOIN sale caro

ClickHouse guarda los datos en disco, ordenados por ORDER BY, con un índice sparse en RAM. Cubrimos la mecánica en el primer post de esta serie. Hacer un JOIN de dos tablas rompe ese patrón de acceso ordenado. Al menos uno de los lados hay que reorganizarlo en memoria, normalmente como tabla hash y a veces como flujo ordenado. Esa reorganización es lo que te cuesta.

Cada herramienta resuelve una forma distinta del problema:

• Lookup de dimensión con datos de referencia de baja cardinalidad que se actualizan despacio: un diccionario, vía dictGet.
• Dos tablas analíticas grandes combinadas ad-hoc: un JOIN, con el algoritmo adecuado.
• Un lookup estable que lees sin parar mientras la dim apenas se mueve: incrústalo en la ingesta, ya sea aguas arriba en tu pipeline o con dictGet dentro de una materialized view, y el lookup en tiempo de query desaparece del todo.

La jerarquía de alternativas más baratas de el post de optimización de queries pone dictGet arriba del todo y los JOINs abajo, pero aquí vamos a profundizar más.

Diccionarios

Un diccionario es una primitiva de lookup, y la mayoría de las veces no lo consultas como una tabla: llamas a dictGet desde dentro de otra query. El diccionario vive en la RAM del servidor, se refresca desde una fuente externa según un horario y sirve lookups O(1) en lugar del construir-tabla-hash-y-escanear del JOIN.

El post de materialized views tenía la anécdota de guerra canónica: una MV hacía JOIN con una tabla de dimensión de 500 millones de filas en cada insert, y la latencia del INSERT pasó de 50ms a 2 segundos. El arreglo fue sustituir el JOIN por dictGet. Esa historia sigue siendo el mejor resumen de un párrafo de por qué existe esta herramienta. Léelo aquí si te lo perdiste.

Fuentes

Puedes crear un diccionario con PostgreSQL, MySQL, otra tabla de ClickHouse, un endpoint HTTP, un ejecutable o un fichero en disco. La fuente está desacoplada de cómo ClickHouse guarda el diccionario en memoria. El caso de producción más habitual es "PostgreSQL tiene la verdad, ClickHouse tiene el lookup":

CREATE DICTIONARY product_metadata (
    sku UInt64,
    name String,
    category UInt8,
    base_price Decimal(10, 2)
) PRIMARY KEY sku
SOURCE(POSTGRESQL(
    host 'pg.internal' port 5432
    user 'reader' password 'secret'
    db 'inventory' table 'products'
    invalidate_query 'SELECT max(updated_at) FROM products'
))
LIFETIME(MIN 60 MAX 300)
LAYOUT(HASHED());

Para diccionarios del mismo cluster que leen de otra tabla de ClickHouse, usa SOURCE(CLICKHOUSE(...)) y pasa USER y PASSWORD explícitos. Los clusters gestionados (el nuestro incluido) no dejan que los diccionarios se autentiquen de forma implícita. Nos topamos con eso montando el benchmark para este post: el diccionario fallaba al cargar con un engañoso "AUTHENTICATION_FAILED" hasta que añadimos las credenciales en línea.

Layouts: elige por la forma de la clave y el presupuesto de memoria

El layout es la decisión que determina el uso de memoria y la velocidad de lookup. ClickHouse tiene más layouts de los que la mayoría de los equipos necesitan conocer. Estos son los que de verdad vas a elegir:

1. flat es el layout más rápido que existe. Es un array plano indexado directamente por la clave, así que un lookup es un único offset de array, sin hashing de por medio. La restricción viene de ese mismo diseño: las claves tienen que ser valores UInt64 densos por debajo del tope max_array_size, 500K por defecto y configurable más arriba. Las claves enteras secuenciales, un id autoincremental o un CounterID, son el caso ideal. Mételé claves dispersas o no enteras y, o malgastas memoria en los huecos, o directamente se niega a construirse.

2. Tira de hashed cuando flat no encaja, que es la mayoría de las veces. Es una tabla hash estándar: entra cualquier tipo de clave simple, se hashea a un bucket y recorre una cadena corta si hay colisión. Strings, rangos amplios de enteros y claves dispersas, todo lo maneja. Pagas un poco más de RAM que con flat y un cálculo de hash por lookup, pero se mantiene O(1) en la práctica y dejas de preocuparte por la forma de la clave. Cuando no sabes qué layout necesitas, este es el sitio por donde empezar.

3. complex_key_hashed es la misma tabla hash con una clave compuesta. Perfil de memoria idéntico, salvo que la clave es una tupla de columnas en vez de una sola. Siempre que tu clave real sea "estas dos o tres columnas juntas", este es el layout.

Esos tres cubren la gran mayoría de los diccionarios que vas a construir. ClickHouse trae alrededor de una docena más para trabajos más concretos: range_hashed para lookups versionados por tiempo como tipos de cambio por día, ip_trie para CIDR y GeoIP, cache y ssd_cache para diccionarios demasiado grandes para tenerlos enteros en RAM, y complex_key_hashed_array para claves que cargan con muchos atributos. Si la forma de tu clave no encaja con las tres de arriba, échale un ojo a la documentación de layouts antes de comprometerte.

Estrategias de refresco

Tres mecanismos, cada uno con su compromiso.

1. LIFETIME(MIN n MAX m) refresca el diccionario en un intervalo aleatorio entre n y m segundos. La aleatoriedad evita el efecto manada de varias réplicas refrescando la misma fuente en el mismo instante. Usa LIFETIME(0) para desactivar el refresco automático del todo.

2. invalidate_query es una query SQL cuyo resultado ClickHouse trata como una marca de agua, con una reducción de carga enorme para diccionarios que cambian despacio. Lo compara con el resultado anterior y el diccionario solo se refresca cuando la marca se mueve. El patrón:

SOURCE(POSTGRESQL(... invalidate_query 'SELECT max(updated_at) FROM products' ...))

3. update_field es la opción incremental. ClickHouse añade WHERE update_field >= last_update_time a la query de origen y trae solo las filas que han cambiado. Vale su peso en oro para diccionarios grandes donde una recarga completa duele en lo operativo.

La garantía de atomicidad conviene tenerla clara. Las queries ven o la versión vieja o la nueva de un diccionario, nunca una mezcla. Un refresco fallido mantiene viva la snapshot anterior y solo registra el error en el log. Monitoriza system.dictionaries.last_exception, porque no hay otra cosa que te avise de que un refresco se ha roto.

La familia dictGet

La función que llamas casi siempre es dictGet. Las variantes existen por algo:

• dictGet(name, attr, key) es la llamada base: devuelve el valor del atributo para la clave. Si falla el lookup, recurre al default declarado en la definición del atributo del diccionario (o al valor cero del tipo si no hay ninguno).
• dictGetOrDefault(name, attr, key, default) devuelve un fallback tipado para claves que no existen. Prefiérelo cuando las claves ausentes son normales en tus datos; te quita el manejo de NULL de las queries de aguas abajo.
• dictGetOrNull devuelve NULL cuando falla el lookup. La opción correcta cuando "ausente" tiene que distinguirse de un vacío o cero legítimo (v21.4+).
• dictHas es una comprobación de sí o no, más barata que dictGet cuando solo necesitas saber si una clave existe.
• dictGetAll devuelve todos los valores de atributo que coinciden, para diccionarios regexp_tree que pueden casar con varios patrones (v23.5+).
• dictGetKeys es el nuevo lookup inverso: de valor a claves. Disponible en v25.12+. Útil cuando una operación tipo JOIN es en realidad "encuentra todas las claves cuyo atributo coincide con este valor".
• La familia jerárquica (dictGetHierarchy, dictGetChildren, dictGetDescendants, dictIsIn) maneja diccionarios padre-hijo.

También existen variantes específicas de tipo: dictGetUInt64, dictGetString, dictGetIPv4, etc. Devuelven escalares tipados sin envoltorio de tipo dinámico, lo que importa en los caminos calientes. El dictGet polimórfico se lee mejor; los tipados van más rápidos. Elige según estés optimizando o expresando.

SELECT
    transaction_id,
    dictGetOrDefault(
        'address_labels',
        'label',
        (chain_id, from_address),
        'unknown'
    ) AS sender_label
FROM transfers
WHERE block_date = today();

Los diccionarios en ObsessionDB

En ClickHouse autogestionado, los diccionarios en memoria viven en la RAM de cada réplica que pueda servir la query. La arquitectura de ObsessionDB está desacoplada, con los datos en S3 y el compute en nodos sin estado, pero los diccionarios son la excepción deliberada.

Cada nodo de compute mantiene una copia completa en memoria de cada diccionario, refrescada exactamente como lo haría una instancia autogestionada de un solo nodo. Los viajes de ida y vuelta a S3 echarían por tierra todo el sentido de dictGet (enriquecimiento de menos de un milisegundo), así que asumimos el coste de RAM en cada nodo y mantenemos su comportamiento idéntico al de ClickHouse vanilla.

Para el artículo que estás leyendo, esto es lo que significa en la práctica: una dim de 6.506 filas ocupa 1,62 MiB de RAM y carga en 27 milisegundos en cada nodo. Los números de abajo salen de ejecutar dictGet en un nodo de compute sin estado que lee 40 GB desde almacenamiento respaldado por S3.

dictGet frente a JOIN, en nuestro propio cluster

Hicimos la comparación esta semana. ClickHouse 26.3.9.1 LTS. ObsessionDB SharedMergeTree. La tabla estándar de clickbench hits_10b (10.000 millones de filas, 54 GiB) cruzada con una dim sintética de 6.506 filas. Caché de sistema de ficheros desactivada por query para una comparación justa en frío contra frío. Salida descartada con una agregación externa, así que el overhead del cliente no contamina los números. Tres ejecuciones de cada uno, marcadores con log_comment, resultados sacados de system.query_log.

Enfoque	Tiempo de pared	Memoria
Baseline (sin enriquecer)	11.0 s	22 MiB
dictGet (HASHED)	11.5 s	22 MiB
dictGet (FLAT)	11.3 s	22 MiB
JOIN parallel_hash (default en 26.3)	~225 s	52 MiB
JOIN hash (un solo hilo)	141–212 s	40 MiB

El titular se lee solo: dictGet es estadísticamente indistinguible del baseline sin enriquecer. El lookup ocurre para cada fila de un escaneo de 10.000 millones de filas y no añade overhead medible. El JOIN equivalente tarda unas 20× más con 2,4× la memoria.

Ahora la parte que no me esperaba. El hash de un solo hilo fue más rápido que parallel_hash en esta carga, y parallel_hash es el default en 26.3. El motivo: con un lado derecho de 6.506 filas, el overhead de coordinar la construcción paralela de la tabla hash no compensa. La construcción de un solo hilo termina antes que la orquestación paralela. El cambio de default en 24.12 de hash a parallel_hash es la decisión correcta para la mayoría de tamaños de dim, pero las cargas con dims pequeñas se benefician de poner join_algorithm='hash' de forma explícita. Mide siempre contra la forma real de tus datos antes de fiarte de los defaults.

¿Cómo evoluciona la diferencia según crece la dim? Hicimos la comparación con dims de 1K, 6,5K, 10K, 100K y 1M:

La curva de dictGet se mantiene en la banda de 10-23 segundos según la dim escala de 1K a 1M; la curva del JOIN se queda en la banda de 100-225 segundos. La variación del lado del JOIN en la dim de 6.506 filas, entre los 225s originales y una ejecución posterior de 105s, es real y depende de la carga del cluster. Incluso en el extremo más favorable, el JOIN es ~10× más lento.

Cuándo dictGet no gana

El titular de 20× es cierto a escala y con la forma adecuada de problema. Hay una forma donde no lo es, y tenemos datos internos de benchmark al respecto de un pipeline de cuantiles que ejecutamos hace un tiempo.

Tabla de umbrales: ~500 filas. Tabla de hechos: escaneo completo. Probamos cuatro combinaciones de función de percentil y estrategia de lookup:

Enfoque	Latencia del pipeline
quantile + JOIN con función de ventana	21 ms (ganador)
quantileTDigest + lookup de diccionario	49 ms
quantileTDigest + JOIN con función de ventana	93 ms
quantile + lookup de diccionario	436 ms

Por qué ganó el JOIN: con un lado derecho de 500 filas y una función de ventana a la izquierda, ClickHouse compila el plan del join una sola vez. Hashea la tablita de umbrales una vez, sondea al unísono y listo. dictGet es una llamada a función por fila, sin optimización en bloque. En dims lo bastante pequeñas para que la construcción de la tabla hash del JOIN sea prácticamente gratis, el JOIN le gana a dictGet porque el overhead por fila de dictGet no llega a amortizarse.

Regla general: los diccionarios ganan cuando la dim es lo bastante grande para que el lookup por fila salga barato frente al coste de construir la tabla hash del JOIN. En dims de unos cientos de filas accedidas con funciones de ventana, las tornas cambian.

JOINs: comportamiento actual

El post de optimización de queries tenía la matriz completa de algoritmos para los JOINs en ClickHouse. Léete eso para la tabla comparativa; aquí nos quedamos con el matiz operativo.

La regla de la tabla-derecha-en-memoria es la clave: un JOIN hash mete el lado derecho en una tabla hash. Por eso, pon siempre la tabla más pequeña a la derecha. El benchmark de IMDB del propio blog de ClickHouse le pone número: 1M de actores a la derecha corre en ~0,8s con 716 MB; 100M de roles a la derecha tarda 5+ segundos y 8,95 GB. Para el mismo JOIN y el mismo resultado, eso es 6× más lento solo por el orden de las tablas.

En cuanto a la elección de algoritmo, parallel_hash es el default, y es el adecuado para la mayoría de tamaños de dim (más de ~5K filas). Para dims pequeñas, mira la sorpresa de la sección de benchmarks de arriba: poner join_algorithm='hash' explícito puede ganarle al default un ~30%. Para tablas ordenadas físicamente por la clave del join, full_sorting_merge se salta el ordenado por completo y corre a la velocidad de un hash join con mucha menos memoria. Para lados derechos que no caben en RAM, grace_hash vuelca a disco en buckets ajustables. partial_merge es el último recurso cuando la memoria aprieta.

Hay algún aprendizaje concreto más que puede afectar a tus joins según las condiciones del cluster. Por ejemplo, en entornos donde pueden aparecer duplicados, ANY frente a ALL es el asesino cartesiano silencioso. Si tu lado derecho tiene K duplicados por clave de join, ALL multiplica por K el número de filas del izquierdo, como cualquier base de datos por defecto, pero ANY puede mitigarlo con un arreglo de poco esfuerzo:

-- La tabla B tiene 3 filas para id=1
SELECT a.id, b.value FROM A INNER JOIN B ON a.id = b.id;
-- Devuelve 3 filas. Multiplicación silenciosa.

SELECT a.id, b.value FROM A INNER ANY JOIN B ON a.id = b.id;
-- Devuelve 1 fila. Lo que querías.

El modo de fallo en producción es deprimentemente común: una tabla de dimensión acumula duplicados con el tiempo (slowly changing dimensions, inserts tardíos, deduplicación que se escapa) y un JOIN que funcionó bien durante meses de repente revienta por memoria o devuelve 10× las filas. Cambia a ANY para mitigarlo rápido, pero deduplica la dim o modélala como diccionario para arreglarlo de verdad.

GLOBAL JOIN en montajes distribuidos también es algo a tener en cuenta: el modelo de coste es right_size × num_shards bytes difundidos en cada query. Merece la pena cuando el lado derecho es de verdad pequeño (pocos MB) y cambia demasiado a menudo para replicarlo.

Enriquece en la ingesta, no en la query

El lookup más rápido en tiempo de lectura es el que ya hiciste en tiempo de escritura.

Todo lo anterior asume que resuelves el lookup cuando corre la query. dictGet lo hace barato, pero barato no es gratis, y lo pagas en cada query que toca la columna. Cuando el enriquecimiento es estable, una etiqueta, una categoría o un atributo desnormalizado, hay una jugada mejor: resuélvelo una vez, de camino hacia dentro, y guarda el resultado. Las queries de lectura ven una columna normal y corriente, sin nada que resolver.

Hay dos sitios donde hacerlo. El primero es aguas arriba, antes de que el dato llegue siquiera a ClickHouse. Si tu productor, procesador de streams o job de ETL ya tiene a mano los datos de referencia, pega la etiqueta ahí y deja que las filas lleguen enriquecidas. ClickHouse guarda lo que le das y no busca nada. Suele ser el camino más simple cuando ya tienes algo como Kafka con un consumer, o Flink, delante de la base de datos.

El segundo es dentro de la base de datos, cuando los datos de referencia conviene tenerlos como un diccionario de ClickHouse. Mete dictGet en una materialized view: las filas aterrizan en la tabla cruda, la MV se dispara, el lookup corre una vez por fila en el insert y el valor resuelto se escribe en la tabla destino.

CREATE MATERIALIZED VIEW transfers_enriched_mv TO transfers_enriched AS
SELECT
    transaction_id,
    chain_id,
    from_address,
    dictGetOrDefault(
        'address_labels', 'label',
        (chain_id, from_address), 'unknown'
    ) AS sender_label
FROM transfers_raw;

En cualquier caso, el compromiso es que el dato queda congelado. El dictGet en tiempo de query siempre refleja el diccionario actual. Un valor enriquecido en la ingesta refleja los datos de referencia tal como estaban cuando la fila aterrizó. Reetiqueta a un usuario mañana y cada fila ya escrita conserva la etiqueta de ayer hasta que hagas backfill. Así que la regla se parte limpia:

• Enriquece en la ingesta cuando el valor histórico es el correcto, o lo bastante parecido para que el desfase no muerda. Etiquetas de transacciones, lookup geográfico en el momento del evento y la categoría que tenía un producto cuando se vendió.
• Quédate con el dictGet en tiempo de query cuando necesitas la respuesta siempre al día. Propiedad en vivo, tier actual, cualquier cosa donde "qué es ahora" le gana a "qué era entonces".

El árbol de decisión

El artefacto para llevar es lo que deberías aprender de este artículo.

¿Combinar datos de dos fuentes?
│
├── Datos de referencia (lookup), se actualizan poco, caben en RAM
│   └── DICTIONARY
│       ├── Claves UInt64 densas < 500K       →  flat
│       ├── Claves compuestas                 →  complex_key_hashed
│       ├── Por rango temporal (tipos, dueño) →  range_hashed
│       ├── Rangos CIDR / IP                  →  ip_trie
│       └── Muchos atributos por clave        →  complex_key_hashed_array
│
├── Los datos de referencia no caben en RAM
│   └── DICTIONARY con cache o ssd_cache
│
├── Dos tablas analíticas, ad-hoc, ambas grandes
│   ├── Derecha es Dict / RocksDB / Join engine, ANY vale →  direct
│   ├── Derecha cabe en RAM, dim ≥ ~5K filas              →  parallel_hash
│   ├── Derecha cabe en RAM, dim < ~5K filas              →  hash (menos coordinación)
│   ├── Ambas comparten orden físico por la clave         →  full_sorting_merge
│   ├── La derecha no cabe                                →  grace_hash
│   └── Distribuido, derecha en cada shard                →  GLOBAL JOIN
│
└── Lookup estable, lo lees sin parar, la dim apenas cambia
    └── Enriquece en la INGESTA  →  pre-enriquece aguas arriba, o dictGet en una MV
                                    (valor congelado en la fila, cero lookup en query)

La próxima vez que vayas a tirar de LEFT JOIN, párate y pasa por este árbol primero. El default (LEFT JOIN contra un lado derecho construido como hash) es la decisión correcta quizá el 20% de las veces. El resto del tiempo, ganan los diccionarios.

Lo que viene

dictGet es el default correcto para enriquecer en tiempo de query. El JOIN es la salida de emergencia cuando la forma de los datos no encaja en un diccionario. Y cuando el valor es estable, el lookup más barato de todos es el que resuelves una vez en la ingesta y no vuelves a ejecutar nunca.

Estas palancas funcionan en cualquier despliegue de ClickHouse, incluido ObsessionDB.