Stop writing raw ClickHouse DDL: A Pydantic v2 model is enough

#python #clickhouse #bigdata #opensource

Day 01 of the WClickHouse Open-Source Engineering Series.

Why are engineers still writing manual SQL DDL strings in Python? WClickHouse binds Pydantic v2 models directly to ClickHouse columnar storage.

The Pain Points We Faced

Writing 40-line CREATE TABLE DDLs by hand for every analytical event
Mismatches between Python types and ClickHouse columnar storage engines
Runtime data corruption from unvalidated dictionaries inserted into tables

The Implementation

from pydantic import BaseModel
from wclickhouse import WClickHouse
from datetime import datetime
from typing import List

class AnalyticsEvent(BaseModel):
    event_id: int
    event_name: str
    properties: List[str]
    created_at: datetime = datetime.now()

# Auto-creates table with MergeTree engine
db = WClickHouse(AnalyticsEvent, db_config)
db.insert(AnalyticsEvent(event_id=1, event_name="click", properties=["web", "cta"]))

Why This Architecture Wins

Pydantic v2 Native: Model fields map 1:1 to ClickHouse columns automatically.
Auto DDL Generation: Executes CREATE TABLE IF NOT EXISTS with optimal engines.
Strict Validation: Validates types in memory before sending bytes to the server.

Verification & Status

Tested and verified against live ClickHouse server instances with 95%+ test coverage. Built for Python 3.9 through 3.14 with Apache Arrow and Pydantic v2.

GitHub: https://github.com/wisrovi/wclickhouse
PyPI: https://pypi.org/project/wclickhouse

ClickHouse #Python #DataEngineering #OLAP #BigData #Wisrovi

DEV Community