Runbook : schéma JSON

Vos données arrivent en JSON. ClickHouse vous propose plusieurs façons de les stocker, depuis des colonnes entièrement typées jusqu'à un String brut. Le bon choix dépend du degré de prévisibilité de votre schéma et de la nécessité d'effectuer des requêtes sur des champs individuels.

Portée : Cette page traite des choix de conception du schéma pour stocker des données JSON. Elle ne couvre pas les formats JSON d'entrée/sortie, les fonctions JSON, ni la syntaxe des requêtes. Pour plus d'informations sur le type de colonne JSON lui-même, consultez Use JSON where appropriate.

Prérequis : Connaissance de la création de tables ClickHouse, des bases de MergeTree et de la syntaxe des types de colonnes.

• Si chaque champ a un type connu et stable, et que le schéma évolue rarement → Colonnes typées
• Si la plupart des champs sont stables, mais qu’une partie est dynamique ou imprévisible → Hybride (typé + JSON)
• Si toute la structure est dynamique, avec des clés qui apparaissent et disparaissent d’un enregistrement à l’autre → Colonne JSON native
• Si les champs dynamiques sont des paires clé-valeur avec un type de valeur uniforme (par ex. des tags sous forme de chaînes ou des métriques numériques) → Map plutôt que JSON
• Si vous vous contentez de stocker et de récupérer le blob JSON, sans requêtes au niveau des champs → Stockage opaque en String

Quand l’utiliser : La structure JSON est entièrement connue dès la conception. Les champs et les types ne changent pas d’un enregistrement à l’autre. Même des structures imbriquées complexes (tableaux d’objets, maps imbriquées) peuvent être représentées avec les types Array, Tuple et Nested.

Compromis : Les changements de schéma nécessitent ALTER TABLE. Les champs inattendus sont silencieusement ignorés à l’insertion, sauf si le schéma est mis à jour.

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)

-- Vérifier que les types de colonnes correspondent aux attentes
DESCRIBE TABLE events FORMAT Vertical

-- Insérer des données et exécuter une requête pour valider que le schéma les prend bien en charge
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'

Quand l’utiliser : Un ensemble de champs de base est stable (timestamp, ID, codes d’état), mais une partie du payload est dynamique. Pensez à des attributs définis par l’utilisateur, des tags, des métadonnées ou des champs d’extension qui varient d’un enregistrement à l’autre.

Compromis : Performances maximales sur les colonnes typées, flexibilité sur la colonne JSON. La colonne JSON implique malgré tout une surcharge à l’insert et un coût de stockage pour sa partie dynamique.

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)

-- Insérer des données d’exemple et inspecter les chemins inférés
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

Quand l’utiliser : La structure est véritablement imprévisible, avec des clés qui apparaissent et disparaissent d’un enregistrement à l’autre. Schémas générés par les utilisateurs, systèmes de plugins ou ingestion dans un lac de données lorsque vous ne contrôlez pas le schéma en amont.

Compromis : Les insertions sont plus lentes qu’avec des colonnes typées. Les lectures de l’objet complet sont plus lentes qu’avec String. Surcoût de stockage lié à la gestion des sous-colonnes. Fonctionne bien pour les requêtes au niveau des champs sur des chemins spécifiques.

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"}}

-- Vérifiez quels chemins ClickHouse a détectés et leurs types
SELECT JSONAllPathsWithTypes(data) FROM dynamic_events FORMAT PrettyJSONEachRow

-- Interrogez un chemin spécifique
SELECT data.page FROM dynamic_events WHERE data.event_type = 'click'

Quand l'utiliser : les documents JSON sont stockés et récupérés en bloc, puis transmis à une application, archivés ou relayés en aval. Aucun filtrage ni aucune agrégation au niveau des champs dans ClickHouse.

Compromis : insertions les plus rapides et schéma le plus simple. Pas de requêtes au niveau des champs sans analyse à l'exécution (famille JSONExtract), ce qui est lent à grande échelle.

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}')

-- Vérifiez que les données sont bien restituées à l'identique
SELECT payload FROM raw_events WHERE id = 1

-- Vérifiez que vous pouvez toujours extraire des champs à la demande si nécessaire
SELECT JSONExtractString(payload, 'type') AS event_type FROM raw_events

Si vos champs dynamiques sont des paires clé-valeur homogènes — c’est-à-dire que toutes les valeurs ont le même type — Map(String, T) est plus simple et plus efficace qu’une colonne JSON. Exemples courants : des tags de type chaîne (Map(String, String)), des métriques numériques (Map(String, Float64)) ou des 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 prend en charge le filtrage au niveau des clés (tags['env'] = 'prod'), coûte moins cher à stocker que JSON et évite la surcharge liée aux sous-colonnes du type JSON. Notez que, par défaut, les recherches de clés parcourent la map linéairement — cela convient pour de petits ensembles de tags, mais pour les maps de plus de 100 clés, envisagez la sérialisation with_buckets. Utilisez JSON lorsque les valeurs ont des types hétérogènes ou que la structure est imbriquée — utilisez Map lorsqu’il s’agit de paires clé-valeur simples avec un type de valeur uniforme.

• Utiliser JSON lorsque c’est pertinent — quand utiliser le type de colonne JSON plutôt que d’autres solutions
• Référence du type de données JSON — syntaxe complète pour les indications de type, SKIP, max_dynamic_paths et les fonctions d’introspection
• Choisir les types de données — recommandations générales pour choisir les types
• A New Powerful JSON Data Type for ClickHouse — analyse détaillée de l’architecture de stockage du type JSON
• Référence des formats JSON — formats d’entrée/sortie pour les données JSON (JSONEachRow, JSONAsObject, etc.)