Home
JSON to ClickHouse CREATE TABLE DDL Generator

JSON to ClickHouse CREATE TABLE DDL Generator

Convert sample JSON documents into ClickHouse SQL DDL with MergeTree engine, UInt/Int/Float/String/DateTime types, and Array/Nested column mappings.

✨ Presets:
Input JSON Document / NDJSONARRAY
55 Lines:1,538 chars1.5 KB
2 record(s)
-- =================================================================
-- ClickHouse DDL Generated by ToolsAA (100% Client-Side Private)
-- Target Engine: MergeTree | Columns: 22
-- =================================================================

CREATE TABLE IF NOT EXISTS `default`.`analytics_events`
(
    `event_id`                   LowCardinality(String) COMMENT 'Sample: e8d1a6b0-9f12-4e9b-b234-918237465abc',
    `tenant_id`                  UInt16 CODEC(T64, ZSTD(1)) COMMENT 'Sample: 1042',
    `event_time`                 DateTime64(3, 'UTC') CODEC(DoubleDelta, ZSTD(1)) COMMENT 'Sample: 2026-10-17T04:15:30.452Z',
    `event_name`                 LowCardinality(String) COMMENT 'Sample: button_clicked',
    `session_id`                 LowCardinality(String) COMMENT 'Sample: sess_8492019a',
    `user_id`                    LowCardinality(String) COMMENT 'Sample: usr_998124',
    `page_url`                   LowCardinality(String) COMMENT 'Sample: https://toolsaa.com/tools/json-to-clickhouse',
    `referrer`                   LowCardinality(String) COMMENT 'Sample: https://google.com',
    `client_ip`                  IPv4 COMMENT 'Sample: 192.168.1.105',
    `user_agent`                 LowCardinality(String) COMMENT 'Sample: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7)',
    `device_type`                LowCardinality(String) COMMENT 'Sample: desktop',
    `browser`                    LowCardinality(String) COMMENT 'Sample: Chrome',
    `country_code`               LowCardinality(String) COMMENT 'Sample: US',
    `duration_ms`                UInt16 CODEC(T64, ZSTD(1)) COMMENT 'Sample: 320',
    `is_logged_in`               Bool CODEC(T64, ZSTD(1)) COMMENT 'Sample: true',
    `tags`                       Array(String) CODEC(ZSTD(1)) COMMENT 'Sample: ["saas","developer-tools","conversion"]',
    `properties_button_id`       Nullable(String) CODEC(ZSTD(1)) COMMENT 'Sample: btn_generate_ddl',
    `properties_source_section`  Nullable(String) CODEC(ZSTD(1)) COMMENT 'Sample: hero_banner',
    `properties_ab_test_variant` Nullable(String) CODEC(ZSTD(1)) COMMENT 'Sample: v2_compact',
    `properties_file_extension`  Nullable(String) CODEC(ZSTD(1)) COMMENT 'Sample: json',
    `properties_file_size_bytes` Nullable(UInt16) CODEC(T64, ZSTD(1)) COMMENT 'Sample: 49200',
    `properties_record_count`    Nullable(UInt8) CODEC(T64, ZSTD(1)) COMMENT 'Sample: 150'
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (`tenant_id`, `event_name`, `event_time`, `event_id`)
SETTINGS index_granularity = 8192;
34 DDL lines22 columnsEngine: MergeTree
Ready for ClickHouse
22 Inferred Columns
4 Ints • 0 Floats • 15 Strings • 1 Arrays
MergeTree Table Engine
PARTITION BY toYYYYMM(event_time)
Columnar Storage Optimized
Index Granularity 8192 • ZSTD/LZ4

100% Client-Side Private Generator

Zero server uploads or telemetry. Your JSON payloads, internal schemas, and proprietary data structures remain strictly inside your browser memory.

Native ClickHouse Type Inference

Smart columnar data type inference for UInt8-UInt64, Float32/Float64, DateTime64, LowCardinality(String), Arrays, and Nested Tuple hierarchies.

Production MergeTree DDL & Pipelines

Instantly generates production-grade CREATE TABLE statements with ORDER BY, PARTITION BY, TTL expressions, and CLI / HTTP ingestion snippets.

Technical Reference & Architecture Guide

JSON to ClickHouse CREATE TABLE DDL Generator — Reference Guide

Convert sample JSON documents into ClickHouse SQL DDL with MergeTree engine, UInt/Int/Float/String/DateTime types, and Array/Nested column mappings.

Key Parameters & Controls

tableNamestring

Destination ClickHouse table name identifier.

Default: events
enginestring

ClickHouse table engine determining data replication, deduplication, and storage behavior.

Default: MergeTree()
orderBystring

Primary sorting key tuple defining on-disk physical sorting and sparse primary index.

Default: (id, created_at)
partitionBystring

Partitioning key expression for chronological data part splitting and efficient partition dropping.

Default: toYYYYMM(created_at)
useLowCardinalityboolean

Automatically converts repeated string fields and enum-like strings to LowCardinality(String) for dictionary compression.

Default: true
nullableboolean

Wraps inferred types in Nullable(T) when fields can be null, otherwise defaults to ClickHouse zero-values for maximum performance.

Default: false

Pro Tips & Best Practices

ClickHouse stores Nullable columns using

ClickHouse stores Nullable columns using a separate null-mask byte file (*.null), degrading vectorization speed and disk compression by 20-30%. Use default sentinel values (empty string, 0) unless null semantics are strictly mandatory.

For strings with under 10

For strings with under 10,000 distinct values (such as country codes, browser types, HTTP methods), LowCardinality(String) generates local dictionary encodings that drastically reduce storage footprints and accelerate GROUP BY aggregations.

Optimal ORDER BY Selection

Keep the ORDER BY clause short and ordered by column selectivity (from lowest cardinality to highest cardinality), matching your most common query filter patterns for sparse index pruning.

If your JSON contains millisecond

If your JSON contains millisecond or microsecond timestamps (such as ISO 8601 with fractional seconds), use DateTime64(3, 'UTC') or DateTime64(6, 'UTC') rather than standard 1-second precision DateTime.

Common Gotchas & Troubleshooting

Empty arrays [] or mixed data types cause ambiguous type inference

Provide representative JSON objects containing populated array values or multi-record JSON Lines (NDJSON). For mixed numeric and string values, ClickHouse requires explicit String or Variant types.

ORDER BY columns contain Nullable types causing DDL generation errors

ClickHouse strictly forbids Nullable types inside the PRIMARY KEY or ORDER BY sorting keys in MergeTree families. Ensure sort columns are non-nullable.

Nested JSON objects versus Flattened columns

ClickHouse supports both Nested(field Type, ...) and the experimental JSON object type. For high-throughput analytics, flattening nested properties to top-level dot-separated columns (e.g. user_address_city) yields superior compression.

Frequently Asked Questions

How does this generator map JSON data types to ClickHouse column types?▼

JSON numbers are evaluated for integer ranges (UInt8/16/32/64, Int32/64) or Float64. ISO 8601 strings are mapped to Date32, DateTime, or DateTime64. Boolean values map to Bool, arrays map to Array(T), and nested JSON objects map to Tuple(...) or Nested columns.

Why is ClickHouse MergeTree engine recommended by default?▼

MergeTree is the cornerstone of ClickHouse analytical capabilities. It provides partitioned data organization, sparse primary indexing, automatic background parts merging, and blazing-fast vectorized aggregations over billions of rows.

Can I ingest raw JSON directly into ClickHouse without a schema?▼

While modern ClickHouse versions support the experimental Object('json') type, production best practices strongly favor explicit CREATE TABLE schemas with typed columns for 10x-50x better compression, predictable vectorized query performance, and strict schema validation.

Is my JSON payload transmitted to external servers during conversion?▼

No. The DDL generator performs all JSON schema inference, type evaluation, AST generation, and SQL formatting client-side inside your browser runtime. Zero telemetry or data leaves your device.

Related Tools & Utilities

All Tools →
JSON to Google BigQuery Table Schema GeneratorDeveloper

Infer and convert JSON objects into Google Cloud BigQuery Table Schema JSON with RECORD mode, STRING, INTEGER, NUMERIC, and TIMESTAMP fields.

Open tool→
JSON to Snowflake CREATE TABLE DDL GeneratorDeveloper

Translate JSON structures into Snowflake SQL CREATE TABLE statements with VARIANT, VARCHAR, NUMBER, BOOLEAN, and TIMESTAMP_NTZ data types.

Open tool→
JSON / YAML to Terraform HCL Variable & Locals GeneratorDeveloper

Convert JSON and YAML payloads into Terraform HCL variable type constraints (type = object({...})), default values, and terraform.tfvars definitions.

Open tool→
OpenAPI / Swagger to GraphQL SDL Schema ConverterDeveloper

Convert OpenAPI 3.0 REST endpoints, request bodies, and JSON schemas into GraphQL SDL types, queries, mutations, and input definitions.

Open tool→
SQL DDL to TypeBox TypeScript Schema GeneratorDeveloper

Convert SQL CREATE TABLE statements into @sinclair/typebox runtime validation schemas using Type.Object, Type.String, Type.Number, and Type.Optional.

Open tool→
cURL to Rust (reqwest & tokio) Code GeneratorDeveloper

Transform cURL commands into idiomatic, asynchronous Rust HTTP requests using reqwest, serde_json, custom headers, and tokio runtime.

Open tool→