ClickHouse SQLAlchemy Dialect

0.3.2 · active · verified Sat Apr 11

The `clickhouse-sqlalchemy` library provides a SQLAlchemy dialect for connecting to and interacting with ClickHouse databases. It enables users to leverage SQLAlchemy's ORM and SQL Expression Language for querying and manipulating data in ClickHouse. The current version is `0.3.2`. The project releases updates on an as-needed basis rather than a fixed schedule.

Warnings

breaking The aliases `ClickHouseEngine` and `create_session` were removed in version `0.3.0`. Users should directly use `sqlalchemy.create_engine` and standard SQLAlchemy session management.
Fix: Replace `ClickHouseEngine(...)` with `create_engine('clickhouse://...')` and `create_session(...)` with `sqlalchemy.orm.sessionmaker(bind=engine)()`.
breaking The default string type mapping changed from `Nullable(String)` to `String` in version `0.3.0`. This affects DDL generation; columns defined as `String` in SQLAlchemy will no longer implicitly be `Nullable(String)` in ClickHouse.
Fix: If nullable strings are desired, explicitly define columns using `sa.Column('name', sa.String(255), nullable=True)` or import `NullableString` from `clickhouse_sqlalchemy.types`.
gotcha ClickHouse's `DateTime` and `DateTime64` types have strict requirements for timezones and precision. SQLAlchemy's default `DateTime` might not always align perfectly, leading to timezone or precision issues during data insertion/retrieval.
Fix: Use specific types like `DateTime(timezone=...)` or `DateTime64(precision=..., timezone=...)` from `clickhouse_sqlalchemy.types` for precise control over timestamp columns.
gotcha Advanced ClickHouse data types (e.g., `Nested`, `Array`, `Map`, `AggregateFunction`) and features (e.g., `FINAL` modifier, complex table engines) often lack direct, high-level ORM support. Attempting to use them with the ORM might require complex workarounds or fail.
Fix: For complex types and features, it's often more robust to use `engine.execute(text('RAW SQL HERE'))` to run native ClickHouse queries directly, bypassing the ORM layer.

Install

pip install clickhouse-sqlalchemy Install `clickhouse-sqlalchemy`

Imports

create_engine
```
from sqlalchemy import create_engine
```
SQLAlchemy dialects are activated via the engine connection string; `create_engine` itself is from `sqlalchemy`.

Quickstart

This quickstart demonstrates how to establish a connection to a ClickHouse database using SQLAlchemy's `create_engine` and execute basic SQL queries, including DDL and DML operations. It uses environment variables for connection details for secure and flexible configuration. A running ClickHouse instance is required.

import os
from sqlalchemy import create_engine, text

CH_HOST = os.environ.get('CLICKHOUSE_HOST', 'localhost')
CH_PORT = os.environ.get('CLICKHOUSE_PORT', '8123')
CH_USER = os.environ.get('CLICKHOUSE_USER', 'default')
CH_PASSWORD = os.environ.get('CLICKHOUSE_PASSWORD', '')
CH_DATABASE = os.environ.get('CLICKHOUSE_DATABASE', 'default')

try:
    # Establish a connection to ClickHouse
    engine = create_engine(
        f'clickhouse://{CH_USER}:{CH_PASSWORD}@{CH_HOST}:{CH_PORT}/{CH_DATABASE}'
    )

    # Execute a simple query
    with engine.connect() as connection:
        result = connection.execute(text('SELECT 1 as one')).scalar()
        print(f"Query result: {result}")

    # Example: Create a table and insert data
    with engine.connect() as connection:
        connection.execute(text('DROP TABLE IF EXISTS my_test_table'))
        connection.execute(text('CREATE TABLE my_test_table (id Int32, name String) ENGINE = MergeTree ORDER BY id'))
        connection.execute(text('INSERT INTO my_test_table (id, name) VALUES (1, \'Alice\'), (2, \'Bob\')'))
        connection.commit()

        # Query data
        rows = connection.execute(text('SELECT id, name FROM my_test_table ORDER BY id')).fetchall()
        print(f"Table data: {rows}")

except Exception as e:
    print(f"An error occurred: {e}")
    print("Ensure a ClickHouse instance is running and connection details are correct.")
    print("You can set environment variables like CLICKHOUSE_HOST, CLICKHOUSE_PORT, etc.")

view raw JSON →