Operaciones de ClickHouse

Este documento explica los conceptos de ClickHouse que encontrarás en el laboratorio: qué son, por qué existen y en qué se diferencian de las construcciones de Snowflake que usaste en la Parte 1.

1. Motores de tabla

ClickHouse no es una base de datos de un único motor. Toda tabla debe declarar su motor, que determina cómo se almacenan los datos en disco, cómo se gestionan los duplicados y qué capacidades están disponibles. Elegir mal el motor es el error más habitual al diseñar esquemas de ClickHouse.

MergeTree

El motor base de casi todas las tablas de producción.

CREATE TABLE analytics.dim_taxi_zones (
    zone_id      UInt16,
    borough      String,
    service_zone String
) ENGINE = MergeTree()
ORDER BY zone_id;

Qué hace: ClickHouse almacena los datos en partes, fragmentos ordenados y comprimidos en disco. Al insertar datos se escriben partes nuevas. En segundo plano, ClickHouse fusiona continuamente las partes pequeñas en otras mayores y mantiene los datos ordenados por la clave ORDER BY. De ahí procede el nombre.

Cuándo usarlo: en cualquier tabla que no necesite deduplicación y cuyas inserciones sean solo anexos o cargas masivas (dimensiones, eventos sin procesar, registros).

Propiedad clave: no se impone ninguna clave primaria. Se almacenan dos filas aunque tengan valores ORDER BY idénticos. Si necesitas deduplicación, usa ReplacingMergeTree.

ReplacingMergeTree(version_col)

El motor de deduplicación. Amplía MergeTree con una regla: durante una fusión en segundo plano, si dos filas comparten la misma clave ORDER BY, solo conserva la que tenga el valor version_col más alto.

CREATE TABLE analytics.fact_trips (
    trip_id     String,
    pickup_at   DateTime,
    total_amount Float64,
    updated_at  DateTime
) ENGINE = ReplacingMergeTree(updated_at)
ORDER BY (pickup_at, trip_id);

Importante: consistencia eventual. La deduplicación solo se produce durante las fusiones en segundo plano. En cualquier momento la tabla puede contener filas duplicadas; se denomina consistencia eventual. Para obtener resultados totalmente deduplicados durante la consulta, añade FINAL al SELECT:

-- Without FINAL: may return duplicates if merges haven't run
SELECT * FROM analytics.fact_trips WHERE trip_id = 'abc';

-- With FINAL: forces deduplication at query time (slower, always correct)
SELECT * FROM analytics.fact_trips FINAL WHERE trip_id = 'abc';

Cómo lo usa dbt: el adaptador dbt-clickhouse usa la estrategia incremental delete_insert como mecanismo principal: elimina explícitamente las filas con claves coincidentes e inserta otras nuevas, lo que siempre es correcto. ReplacingMergeTree actúa como red de seguridad y limpia cualquier duplicado que se haya colado (por ejemplo, debido a una inserción parcial fallida).

Equivalente en Snowflake: no existe uno directo. En Snowflake usaste MERGE INTO ... WHEN MATCHED THEN UPDATE. ClickHouse no posee sentencia MERGE; ReplacingMergeTree junto con FINAL consigue el mismo resultado lógico.

Vista materializada actualizable

ClickHouse admite dos clases de vistas materializadas.

MV basada en disparadores (tradicional): se ejecuta con cada INSERT y solo procesa el lote recién insertado.

-- Trigger-based: only sees the rows inserted in the current batch
CREATE MATERIALIZED VIEW analytics.mv_realtime_counts
TO analytics.counts_table AS
SELECT pickup_date, count() AS trips
FROM default.trips_raw
GROUP BY pickup_date;

MV actualizable (programada): vuelve a ejecutar la consulta completa según una programación, como un trabajo cron.

-- Refreshable: runs the full SELECT every 3 minutes
CREATE MATERIALIZED VIEW analytics.mv_hourly_revenue
REFRESH EVERY 180 SECOND AS
SELECT
    toStartOfHour(pickup_at) AS hour_bucket,
    pickup_borough,
    sum(total_amount)        AS revenue
FROM analytics.fact_trips FINAL
GROUP BY hour_bucket, pickup_borough;

Cuándo usar cada una:

• Basada en disparadores: agregaciones en tiempo real sobre flujos de inserción donde solo necesitas procesar datos nuevos
• Actualizable: agregaciones que consultan fact_trips FINAL (deben ver toda la tabla para deduplicar), o paneles que toleran unos minutos de obsolescencia a cambio de una lógica más sencilla

Modificar el intervalo de actualización:

ALTER TABLE analytics.mv_hourly_revenue MODIFY REFRESH EVERY 60 SECOND;

2. Claves de ordenación (ORDER BY)

En Snowflake usaste CLUSTER BY como sugerencia al optimizador. En ClickHouse, ORDER BY es el índice primario: determina el orden físico en disco y gobierna todos los recorridos por intervalos.

Cómo funciona

ClickHouse almacena un índice primario disperso: una entrada por unas 8192 filas (un gránulo de datos). Al filtrar por columnas de ORDER BY, omite gránulos enteros sin leerlos. Así puede recorrer miles de millones de filas por segundo: la mayoría de los datos nunca sale del disco.

Importa el orden de cardinalidad

Coloca siempre primero las columnas de cardinalidad baja y al final las de cardinalidad alta. Así el índice puede omitir el máximo posible en el caso habitual.

-- Good: low cardinality (borough, ~6 values) first, then high cardinality (trip_id)
ORDER BY (pickup_borough, toStartOfMonth(pickup_at), trip_id)

-- Bad: high cardinality first — the index can't skip anything useful
ORDER BY (trip_id, pickup_borough, pickup_at)

CLUSTER BY de Snowflake frente a ORDER BY de ClickHouse

Ejemplo: corresponder con una clave de agrupación de Snowflake

-- Snowflake
CLUSTER BY (DATE_TRUNC('month', PICKUP_AT), PICKUP_LOCATION_ID)

-- ClickHouse equivalent
ORDER BY (toStartOfMonth(pickup_at), pickup_location_id, trip_id)
-- Note: trip_id added as tiebreaker to ensure unique sort order

Índices de omisión (breve introducción)

Para columnas ausentes de la clave ORDER BY, ClickHouse admite índices de omisión (filtro Bloom, minmax, set) que almacenan metadatos de columna por gránulo. Resultan útiles para filtrar columnas de cardinalidad baja que aparecen después de una columna de cardinalidad alta en la clave.

-- Add a bloom filter skip index on payment_type
ALTER TABLE analytics.fact_trips
ADD INDEX idx_payment_type payment_type TYPE bloom_filter GRANULARITY 4;

3. Tratamiento de JSON

El tipo de columna VARIANT de Snowflake admite una notación de ruta con dos puntos para recorrer JSON anidado. ClickHouse usa funciones JSONExtract* explícitas.

Tabla comparativa de traducciones

Variantes de funciones

-- Float (returns 0.0 if key missing or wrong type)
JSONExtractFloat(trip_metadata, 'driver', 'rating')

-- String (returns '' if missing)
JSONExtractString(trip_metadata, 'app', 'version')

-- Integer (returns 0 if missing)
JSONExtractInt(trip_metadata, 'driver', 'id')

-- Bool (returns 0/1)
JSONExtractBool(trip_metadata, 'app', 'is_shared')

-- Raw value as string (preserves JSON sub-object)
JSONExtractRaw(trip_metadata, 'route')

Consejo de rendimiento

Si consultas repetidamente la misma columna JSON, considera extraer sus campos a columnas tipadas en el modelo de staging (stg_trips.sql) en lugar de llamar a JSONExtractFloat en cada consulta posterior. Es lo que hacen los modelos dbt del laboratorio.

4. Funciones de fecha y hora

Snowflake y ClickHouse ofrecen capacidades similares para fechas y horas, pero con sintaxis distintas. Estas son las traducciones más habituales:

Tabla comparativa de traducciones

Otras funciones útiles exclusivas de ClickHouse

yesterday()              -- today() - 1 day
toStartOfWeek(col)       -- Monday of the containing week
toStartOfQuarter(col)    -- first day of the quarter
toYear(col)              -- extract year as integer
toMonth(col)             -- extract month as integer (1-12)
toDayOfWeek(col)         -- 1=Monday, 7=Sunday

Sintaxis de intervalos

-- ClickHouse
now() - INTERVAL 7 DAY
now() - INTERVAL 1 HOUR
now() - INTERVAL 30 MINUTE
pickup_at + INTERVAL 90 SECOND

-- Snowflake equivalent
DATEADD('day', -7, CURRENT_TIMESTAMP())
DATEADD('hour', -1, CURRENT_TIMESTAMP())

5. Funciones aproximadas

ClickHouse está diseñado para cargas analíticas donde obtener respuestas exactas sobre miles de millones de filas es más lento que obtener respuestas aproximadas lo bastante precisas para los paneles. Incluye varias funciones de agregación aproximadas.

Recuento de valores distintos

-- Exact (like Snowflake COUNT(DISTINCT ...))
SELECT uniqExact(trip_id) FROM analytics.fact_trips FINAL;

-- Approximate — good for "how many unique passengers today?"
SELECT uniq(passenger_id) FROM analytics.fact_trips FINAL;

Percentiles

-- P95 trip duration — approximate but uses O(1) memory
SELECT quantileTDigest(0.95)(duration_minutes)
FROM analytics.fact_trips FINAL;

-- Multiple percentiles in one pass
SELECT quantileTDigestMerge(0.5)(state), quantileTDigestMerge(0.95)(state)
FROM analytics.fact_trips FINAL;

Regla práctica: usa uniq y quantileTDigest en paneles interactivos. Reserva uniqExact y quantile para valores exactos de facturación, SLA o cumplimiento.

6. Diccionarios

Los diccionarios son tablas de búsqueda en memoria que ClickHouse mantiene activas y preunidas durante la consulta. Equivalen a una tabla de dimensiones pequeña que quieres unir sin el coste de un JOIN completo.

Qué son

Un diccionario se respalda con un origen (tabla de ClickHouse, archivo o base externa) y se carga en memoria al iniciar el servicio o al llamar a SYSTEM RELOAD DICTIONARIES. Las búsquedas se realizan mediante una clave y devuelven uno o varios atributos.

Sintaxis CREATE DICTIONARY

-- From 04_create_dictionary.sql
CREATE DICTIONARY analytics.taxi_zones_dict (
    zone_id      UInt16,
    borough      String,
    service_zone String
)
PRIMARY KEY zone_id
SOURCE(CLICKHOUSE(
    TABLE 'dim_taxi_zones'
    DB    'analytics'
))
LIFETIME(MIN 300 MAX 600)   -- refresh every 5-10 minutes
LAYOUT(FLAT());             -- hash map, best for < 1M rows

Opciones de LAYOUT:

• FLAT(): array indexado por clave entera; la opción más rápida; exige claves enteras secuenciales
• HASHED(): mapa hash; funciona con cualquier clave entera
• COMPLEX_KEY_HASHED(): mapa hash con claves compuestas o de cadena

Uso de dictGet

-- Instead of: JOIN analytics.dim_taxi_zones USING (zone_id)
SELECT
    trip_id,
    dictGet('analytics.taxi_zones_dict', 'borough', toUInt64(pickup_location_id)) AS pickup_borough,
    dictGet('analytics.taxi_zones_dict', 'borough', toUInt64(dropoff_location_id)) AS dropoff_borough
FROM analytics.fact_trips FINAL;

Cuándo usar diccionarios y cuándo JOIN

7. Cláusula SAMPLE

ClickHouse permite muestrear filas directamente en la sintaxis de consulta. El muestreo lee una fracción determinista de los datos, útil para análisis exploratorios que no necesitan resultados exactos.

Sintaxis

-- Read approximately 10% of rows
SELECT count(), avg(total_amount)
FROM analytics.fact_trips SAMPLE 0.1;

-- Read a specific number of rows (approximately)
SELECT trip_id, pickup_at, total_amount
FROM analytics.fact_trips SAMPLE 1000000;

Escalar los resultados

Al muestrear, multiplica los agregados por 1 / sample_rate para estimar los valores de toda la tabla:

-- Estimate total revenue from 10% sample
SELECT sum(total_amount) * 10 AS estimated_total_revenue
FROM analytics.fact_trips SAMPLE 0.1;

Cuándo usar SAMPLE

• Análisis exploratorios («¿es correcta la lógica de mi consulta?») antes de ejecutar sobre la tabla completa
• Elementos de panel donde se acepten valores aproximados
• Entrenamiento de modelos de ML con un subconjunto representativo

Nota: SAMPLE exige que la clave ORDER BY comience por la columna de muestreo o que añadas una cláusula SAMPLE BY a la sentencia CREATE TABLE. La tabla trips_raw del laboratorio se crea con SAMPLE BY cityHash64(trip_id) para este fin.

8. Notas sobre el adaptador dbt-clickhouse

El adaptador dbt-clickhouse (dbt-clickhouse>=1.8) admite la mayoría de las funciones estándar de dbt, pero presenta comportamientos específicos de ClickHouse que debes conocer.

Estrategia incremental delete_insert

ClickHouse no tiene MERGE INTO. La estrategia delete_insert de dbt-clickhouse lo emula:

1. Elimina las filas de la tabla de destino cuyas columnas clave coinciden con el lote entrante
2. Inserta el conjunto completo de filas entrantes

-- What dbt generates for incremental models
DELETE FROM analytics.fact_trips WHERE trip_id IN (SELECT trip_id FROM __dbt_tmp);
INSERT INTO analytics.fact_trips SELECT * FROM __dbt_tmp;

Configúrala en el modelo:

{{
    config(
        materialized='incremental',
        incremental_strategy='delete_insert',
        unique_key='trip_id',
        engine='ReplacingMergeTree(updated_at)',
        order_by='(pickup_at, trip_id)'
    )
}}

Configuración de engine y order_by

Toda tabla MergeTree necesita un motor y un ORDER BY. Especifica ambos en la configuración del modelo dbt:

{{
    config(
        engine='MergeTree()',
        order_by='(zone_id)'
    )
}}

order_by en vez de cluster_by

En modelos dbt de Snowflake quizá hayas usado cluster_by. En dbt-clickhouse usa order_by. ClickHouse no tiene un equivalente de cluster_by de Snowflake: ORDER BY siempre es la ordenación física.

profiles.yml para ClickHouse Cloud

ClickHouse Cloud requiere TLS. Define secure: true:

# ~/.dbt/profiles.yml
nyc_taxi_ch:
  target: dev
  outputs:
    dev:
      type: clickhouse
      host: "{{ env_var('CLICKHOUSE_HOST') }}"
      port: 8443
      user: default
      password: "{{ env_var('CLICKHOUSE_PASSWORD') }}"
      schema: analytics       # default database for models without a custom schema
      secure: true
      threads: 4

Nombres de esquemas y bases separadas

ClickHouse emplea el término base de datos donde Snowflake usa esquema. El adaptador dbt-clickhouse asigna los esquemas de dbt a bases de ClickHouse. La macro generate_schema_name del proyecto sobrescribe el comportamiento predeterminado para que los modelos con +schema: analytics terminen en la base analytics, no en staging_analytics.

-- macros/generate_schema_name.sql
{% macro generate_schema_name(custom_schema_name, node) -%}
  {%- if custom_schema_name is none -%}
    {{ target.schema }}
  {%- else -%}
    {{ custom_schema_name }}
  {%- endif -%}
{%- endmacro %}

Es el mismo patrón que usa el proyecto dbt de Snowflake (Parte 1): la macro es deliberadamente idéntica para que el comportamiento de los nombres sea coherente entre ambos adaptadores.