Runbook: esquema de JSON

Los datos llegan en formato JSON. ClickHouse ofrece varias formas de almacenarlos, desde columnas completamente tipadas hasta un String sin procesar. La opción adecuada depende de lo predecible que sea el esquema y de si se necesitan consultas a nivel de campo.

Alcance: Esta página aborda decisiones de diseño de esquemas para almacenar datos JSON. No cubre los formatos de entrada/salida JSON, las funciones JSON ni la sintaxis de las consultas. Para más información sobre el propio tipo de columna JSON, consulte Use JSON where appropriate.

Asume: Familiaridad con la creación de tablas de ClickHouse, los conceptos básicos de MergeTree y la sintaxis de los tipos de columna.

• Si cada campo tiene un tipo conocido y estable, y el esquema rara vez cambia → Columnas tipadas
• Si la mayoría de los campos son estables, pero alguna parte es dinámica o impredecible → Híbrido (columnas tipadas + JSON)
• Si toda la estructura es dinámica, con claves que aparecen y desaparecen entre registros → Columna JSON nativa
• Si los campos dinámicos son pares clave-valor con un tipo de valor uniforme (p. ej., tags de texto, métricas numéricas) → Map en lugar de JSON
• Si solo se almacena y recupera el blob JSON sin consultas a nivel de campo → Almacenamiento opaco en String

Cuándo usarlo: La estructura del JSON se conoce por completo en tiempo de diseño. Los campos y los tipos no cambian entre registros. Incluso las estructuras anidadas complejas (arrays de objetos, mapas anidados) pueden expresarse con los tipos Array, Tuple y Nested.

Consideraciones: Los cambios de esquema requieren ALTER TABLE. Los campos inesperados se descartan silenciosamente al insertar, a menos que se actualice el esquema.

CREATE TABLE events
(
    `timestamp` DateTime,
    `service`   LowCardinality(String),
    `level`     Enum8('DEBUG' = 1, 'INFO' = 2, 'WARN' = 3, 'ERROR' = 4),
    `message`   String,
    `host`      LowCardinality(String),
    `duration_ms` UInt32
)
ENGINE = MergeTree
ORDER BY (service, timestamp)

-- Confirma que los tipos de columna coincidan con lo esperado
DESCRIBE TABLE events FORMAT Vertical

-- Inserta y consulta para validar que el esquema procese correctamente tus datos
INSERT INTO events FORMAT JSONEachRow
{"timestamp":"2025-03-19 10:00:00","service":"api","level":"INFO","message":"request handled","host":"node-1","duration_ms":42}

SELECT service, level, duration_ms FROM events WHERE service = 'api'

Cuándo usarlo: Un conjunto básico de campos es estable (marcas de tiempo, ID, códigos de estado), pero parte del payload es dinámica. Por ejemplo, atributos definidos por el usuario, tags, metadatos o campos de extensión que varían entre registros.

Compensaciones: Rendimiento completo en las columnas tipadas y flexibilidad en la columna JSON. La columna JSON sigue teniendo sobrecoste en las inserciones y coste de almacenamiento para su parte dinámica.

CREATE TABLE events
(
    `timestamp`  DateTime,
    `service`    LowCardinality(String),
    `level`      Enum8('DEBUG' = 1, 'INFO' = 2, 'WARN' = 3, 'ERROR' = 4),
    `message`    String,
    `host`       LowCardinality(String),
    `duration_ms` UInt32,
    `attributes` JSON(
        max_dynamic_paths = 256,
        `http.status_code` UInt16,
        `http.method` LowCardinality(String),
        SKIP REGEXP 'debug\..*'
    )
)
ENGINE = MergeTree
ORDER BY (service, timestamp)

-- Inserte datos de ejemplo e inspeccione las rutas inferidas
INSERT INTO events FORMAT JSONEachRow
{"timestamp":"2025-03-19 10:00:00","service":"api","level":"INFO","message":"request handled","host":"node-1","duration_ms":42,"attributes":{"http.status_code":200,"http.method":"GET","user.region":"eu-west","custom_tag":"abc"}}

SELECT JSONAllPathsWithTypes(attributes)
FROM events
FORMAT PrettyJSONEachRow

Cuándo usarla: Cuando la estructura es realmente impredecible, con claves que aparecen y desaparecen entre registros. Resulta adecuada para esquemas generados por usuarios, sistemas de plugin o ingestión en data lakes donde no controlas el esquema de origen.

Compensaciones: Inserciones más lentas que en las columnas tipadas. Lecturas del objeto completo más lentas que con String. Sobrecarga de almacenamiento por la gestión de subcolumnas. Funciona bien para consultas a nivel de campo sobre rutas específicas.

CREATE TABLE dynamic_events
(
    `id`   UInt64,
    `ts`   DateTime DEFAULT now(),
    `data` JSON(
        max_dynamic_paths = 512,
        `event_type` LowCardinality(String),
        `version` UInt8
    )
)
ENGINE = MergeTree
ORDER BY (data.event_type, ts)

INSERT INTO dynamic_events (id, data) FORMAT JSONEachRow
{"id": 1, "data": {"event_type": "click", "version": 2, "page": "/home", "button_id": "cta-1"}}
{"id": 2, "data": {"event_type": "purchase", "version": 1, "item_id": "SKU-99", "amount": 49.99, "currency": "USD"}}

-- Comprueba qué rutas detectó ClickHouse y sus tipos
SELECT JSONAllPathsWithTypes(data) FROM dynamic_events FORMAT PrettyJSONEachRow

-- Consulta una ruta específica
SELECT data.page FROM dynamic_events WHERE data.event_type = 'click'

Cuándo usarlo: Los documentos JSON se almacenan y recuperan completos, y luego se pasan a una aplicación, se archivan o se reenvían a otros sistemas. Sin filtrado a nivel de campo ni agregación dentro de ClickHouse.

Desventajas: Inserciones más rápidas y el esquema más simple. No hay consultas a nivel de campo sin análisis en tiempo de ejecución (familia JSONExtract), lo cual es lento a gran escala.

CREATE TABLE raw_events
(
    `id`        UInt64,
    `received`  DateTime DEFAULT now(),
    `payload`   String
)
ENGINE = MergeTree
ORDER BY (received)

INSERT INTO raw_events (id, payload) VALUES
(1, '{"type":"click","page":"/home"}'),
(2, '{"type":"purchase","item":"SKU-99","amount":49.99}')

-- Confirma que los datos se recuperan intactos
SELECT payload FROM raw_events WHERE id = 1

-- Verifica que aún puedes extraer campos de forma ad hoc cuando sea necesario
SELECT JSONExtractString(payload, 'type') AS event_type FROM raw_events

Si tus campos dinámicos son pares clave-valor homogéneos —todos los valores comparten el mismo tipo—, Map(String, T) es más sencillo y eficiente que una columna JSON. Algunos ejemplos habituales son: etiquetas de texto (Map(String, String)), métricas numéricas (Map(String, Float64)) o feature flags (Map(String, Bool)).

CREATE TABLE tagged_events
(
    `timestamp` DateTime,
    `service`   LowCardinality(String),
    `tags`      Map(String, String)  -- e.g., {"env": "prod", "region": "us-east-1", "team": "platform"}
)
ENGINE = MergeTree
ORDER BY (service, timestamp)

Map admite el filtrado a nivel de clave (tags['env'] = 'prod'), requiere menos almacenamiento que JSON y evita la sobrecarga de las subcolumnas del tipo JSON. Ten en cuenta que las búsquedas por clave recorren el mapa de forma lineal de manera predeterminada; esto está bien para conjuntos pequeños de etiquetas, pero para mapas con más de 100 claves, considera la serialización with_buckets. Usa JSON cuando los valores tengan tipos mixtos o la estructura tenga anidamiento; usa Map cuando sean pares clave-valor planos con un tipo de valor uniforme.

• Usa JSON cuando corresponda — cuándo usar el tipo de columna JSON frente a otras alternativas
• Referencia sobre el tipo de datos JSON — sintaxis completa para indicaciones de tipo, SKIP, max_dynamic_paths y funciones de introspección
• Selección de tipos de datos — guía general para seleccionar tipos de datos
• Un nuevo y potente tipo de datos JSON para ClickHouse — análisis en profundidad de la arquitectura de almacenamiento del tipo JSON
• Referencia de formatos JSON — formatos de entrada/salida para datos JSON (JSONEachRow, JSONAsObject, etc.)