repository·master·Indexed 19 days ago
https://github.com/xzkostyan/clickhouse-sqlalchemyA SQLAlchemy dialect that integrates SQLAlchemy's ORM and Expression Language with ClickHouse databases. It supports multiple drivers (http, native, asynch), various ClickHouse table engines (e.g., MergeTree, Distributed, Memory), and dialect-specific features such as codecs, materialized/alias columns, TTL clauses, and ON CLUSTER DDL operations.
ClickHouse SQLAlchemy supports three primary connection interfaces:
clickhouse-driver library.asynch library for asynchronous operations.requests library.The native driver uses the clickhouse+native:// prefix.
WARNING: Native connections are not encrypted. All data, including credentials, is transferred in plain text. Use this driver only over secure channels like SSH or VPN.
All connection string parameters are proxied to the clickhouse-driver library. Common use cases include using LZ4 compression or specifying multiple hosts via alt_hosts.
import certify
# Example: Native connection with LZ4 compression and SSL certificates
dsn = (
'clickhouse+native://user:pass@host/db?compression=lz4&'
'secure=True&ca_certs={}'.format(certify.where())
)
# Example: Multiple hosts
# clickhouse+native://wronghost/default?alt_hosts=localhost:9000You should manually review and adjust autogenerated migration scripts to include necessary ClickHouse-specific parameters.
You can specify the following parameters for materialized view commands:
op.detach_mat_view: supports if_exists, on_cluster, and permanently.op.attach_mat_view: supports if_not_exists and on_cluster.op.create_mat_view: supports if_not_exists, on_cluster, and populate.To specify column order when adding a column, use the clickhouse_after parameter within op.add_column using sa.text().
# Adding a column with a specific position
op.add_column('my_table', sa.Column('new_col', sa.Integer(), clickhouse_after=sa.text('existing_column_name')))Materialized Views in clickhouse-sqlalchemy require a two-step definition: a storage definition (a table class to hold the data) and a SELECT query definition.
You must account for whether your ClickHouse database uses the Ordinary or Atomic engine:
class GroupedStatistics).use_to=True in the MaterializedView constructor. You can customize the name using name or mv_suffix.To store aggregated data, use the AggregatingMergeTree engine and define columns using AggregateFunction or SimpleAggregateFunction.
from clickhouse_sqlalchemy import MaterializedView, select
# 1. Define storage (inner table)
class GroupedStatistics(Base):
date = Column(types.Date, primary_key=True)
metric1 = Column(types.Int32, nullable=False)
__table_args__ = (
engines.SummingMergeTree(
partition_by=func.toYYYYMM(date),
order_by=(date, )
),
)
# 2. Define Materialized View with SELECT
MatView = MaterializedView(GroupedStatistics, select([
Stat.date.label('date'),
func.sum(Stat.metric1 * Stat.sign).label('metric1')
]).where(
Stat.grouping > 42
).group_by(
Stat.date
))
# Create both
Stat.__table__.create()
MatView.create()To use autogenerated migrations, ensure your environment meets these minimum versions:
21.11.11.1clickhouse-sqlalchemy: 0.1.10alembic: 1.5.xIf you are using clickhouse-sqlalchemy < 0.1.10 or if autogenerate cannot handle your specific schema objects, you must write migrations manually using Alembic's op.execute.
ClickHouse SQLAlchemy uses a specific URI syntax to define connections. The general format is:
clickhouse<+driver>://<user>:<password>@<host>:<port>/<database>[?key=value..]
http (default), native, or asynch. If omitted, http is used.'default').'').default).clickhouse+http://user:password@host:port/database?key=valueTo set up the development environment, ensure your desired Python version is installed. Then, run the provided scripts to install test requirements and build the package in development mode.
python testsrequire.py && python setup.py developTo install the development version directly from the GitHub repository, use the following command:
pip install git+https://github.com/xzkostyan/clickhouse-sqlalchemy@master#egg=clickhouse-sqlalchemyClickHouse SQLAlchemy is a dialect for SQLAlchemy that allows you to interact with ClickHouse databases. You can install it via PyPI.
# Use pip to install the package
pip install clickhouse-sqlalchemyYou can install the stable version of clickhouse-sqlalchemy using pip.
pip install clickhouse-sqlalchemyYou can define ClickHouse tables using either the SQLAlchemy Declarative style (via get_declarative_base) or the Constructor style (using Table).
__tablename__ attribute.Table object directly with MetaData.from sqlalchemy import create_engine, Column, MetaData, literal
from clickhouse_sqlalchemy import (
Table, make_session, get_declarative_base, types, engines
)
uri = 'clickhouse://default:@localhost/test'
engine = create_engine(uri)
session = make_session(engine)
metadata = MetaData(bind=engine)
# Declarative style
Base = get_declarative_base(metadata=metadata)
class Rate(Base):
day = Column(types.Date, primary_key=True)
value = Column(types.Int32, comment='Rate value')
other_value = Column(types.DateTime)
__table_args__ = (
engines.Memory(),
{'comment': 'Store rates'}
)
# Constructor style
another_table = Table('another_rate', metadata,
Column('day', types.Date, primary_key=True),
Column('value', types.Int32, server_default=literal(1)),
engines.Memory()
)Since version 0.1.10, clickhouse-sqlalchemy supports Alembic, allowing you to autogenerate migrations from your source code. This is useful for detecting changes in tables, materialized views, columns, and comments.
CRITICAL WARNING: ClickHouse does not support transactions. If a migration fails midway through a command, it will not roll back. Your schema may remain in a partially migrated state. Always verify your schema state after a failed migration attempt.
# Example of autogenerate capabilities:
# - Table and materialized view additions/removals
# - Column additions/removals
# - Column and table comment additions/removals