aiochlite

aiochlite

Lightweight asynchronous ClickHouse client for Python built on aiohttp.

Table of Contents

• Features
• Why aiochlite?
• Installation
• Quick Start
  • Basic Connection
  • Execute Query
  • Insert Data
  • Fetch Results
  • Export Formats
  • Query Parameters
  • Query Settings
  • External Tables
  • JSON Type
  • Binary Columns
  • Error Handling
  • Custom Session
  • Enable Compression
• Type Conversion
• Benchmarks
• License

Features

• Lightweight - only aiohttp required, plus tzdata on Windows
• Streaming support - efficient processing of large datasets with .stream()
• Export formats - raw Parquet / CSV / TSV / JSON / Arrow / ORC payloads via .fetch_format() and .stream_format()
• External tables - advanced temporary data support
• Type conversion - automatic conversion between Python and ClickHouse types
• Type-safe - full type hints coverage
• Flexible - custom sessions, compression, query settings

Why aiochlite?

A small, pure-Python async client for ClickHouse over HTTP. Results are decoded from RowBinaryWithNamesAndTypes into either Row wrappers (fetch()) or raw tuples (fetch_rows()).

• One dependency: aiohttp, joined by tzdata on Windows, which ships no timezone database of its own. aiochlite itself ships no compiled extensions.
• Server-side query parameters: values are sent as ClickHouse param_* and never interpolated into the query text.
• Fast for pure Python: in the benchmark below, with every client in its default configuration, fetch_rows() keeps up with clickhouse-connect and its compiled C parser on flat columns, and is 2.1x-2.3x faster than aiochclient everywhere. On string-heavy and container-heavy rows it trails clickhouse-connect by 32%-56%.
• Typed: complete type hints for IDEs and static type checkers.
• Focused API: ClickHouse over HTTP, without pandas, numpy, Arrow or Polars integrations.
• Tested on Python 3.12–3.14 against ClickHouse 26.8 LTS, with additional compatibility coverage for ClickHouse 25.8 LTS.

Choosing a client. For DataFrames or column-oriented results, use the official clickhouse-connect — it has a real asyncio client, returns numpy, pandas, Arrow and Polars, and stays ahead on string-heavy and nested schemas. Reach for aiochlite when you want a small async client with one dependency that just returns rows.

Installation

pip install aiochlite

Optionally, pull in aiohttp's own speedups extra (aiodns, Brotli, and zstd support on Python < 3.14):

pip install "aiochlite[aiohttp-speedups]"

It affects connection setup and, with enable_compression=True, the available response encodings. Row decoding is pure Python either way and runs at the same speed.

Quick Start

Basic Connection

from aiochlite import AsyncChClient

# Using context manager (recommended)
async with AsyncChClient(
    url="http://localhost:8123",
    user="default",
    password="",
    database="default"
) as client:
    result = await client.fetch("SELECT 1")

# Or manual connection management
client = AsyncChClient("http://localhost:8123")
try:
    assert await client.ping()
    result = await client.fetch("SELECT 1")
finally:
    await client.close()

Execute Query

await client.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id UInt32,
        name String,
        email String
    ) ENGINE = MergeTree() ORDER BY id
""")

Insert Data

# Insert dictionaries
data = [
    {"id": 1, "name": "Alice", "email": "alice@example.com"},
    {"id": 2, "name": "Bob", "email": "bob@example.com"},
]
await client.insert("users", data)

# Insert tuples
data = [
    (3, "Charlie", "charlie@example.com"),
    (4, "Diana", "diana@example.com"),
]
await client.insert("users", data, column_names=["id", "name", "email"])

# Insert with settings
await client.insert(
    "users",
    [{"id": 5, "name": "Eve", "email": "eve@example.com"}],
    settings={"max_insert_block_size": 100000}
)

Rows are serialized and sent as the request goes out, so any iterable or async iterable works — including one that never holds the whole dataset:

async def rows_from(source):
    async for record in source:
        yield {"id": record.id, "name": record.name}

await client.insert("users", rows_from(source))

The first row decides whether the batch is read as dicts or as tuples.

Fetch Results

# Fetch all rows
rows = await client.fetch("SELECT * FROM users")
for row in rows:
    print(f"ID: {row.id}, Name: {row.name}, Email: {row.email}")

# Fetch one row
row = await client.fetchone("SELECT * FROM users WHERE id = 1")
if row:
    print(row.name)  # Attribute access
    print(row["name"])  # Dictionary-style access
    print(row.first())  # Get first column value

# Fetch single value
count = await client.fetchval("SELECT count() FROM users")
print(f"Total users: {count}")

# Iterate over results (for large datasets)
async for row in client.stream("SELECT * FROM users"):
    print(row.name)

Rows are decoded in full as they arrive. If a query selects many more columns than you actually read — a wide SELECT * where only a few fields are used — lazy_decode=True decodes each cell on first access instead:

client = AsyncChClient("http://localhost:8123", lazy_decode=True)

In the benchmark shapes it started paying off once fewer than about a third of the selected columns were read, and cost up to 45% when all of them were. Where it breaks even depends on the column types and on how expensive the skipped ones are to decode, so leave it off unless your access pattern clearly matches — and measure your own query.

Export Formats

Get the raw server payload in any ClickHouse output format — Parquet, CSV, TSV, JSON, Arrow, ORC and more. Both methods return raw bytes exactly as produced by the server (decode text formats yourself).

# Whole result at once
parquet = await client.fetch_format("SELECT * FROM users", "Parquet")
csv = (await client.fetch_format("SELECT * FROM users", "CSVWithNames")).decode()

# Chunked streaming for large result sets
with open("users.parquet", "wb") as f:
    async for chunk in client.stream_format("SELECT * FROM users", "Parquet"):
        f.write(chunk)

# Query parameters, settings and external tables work as usual
ndjson = await client.fetch_format(
    "SELECT * FROM users WHERE id > {id:UInt32}",
    "JSONEachRow",
    params={"id": 10},
)

Supported formats (ExportFormat):

Any other output format the server accepts can still be passed at runtime (type checkers will flag it).

[!WARNING] fetch_parquet() and stream_parquet() are deprecated and will be removed in a future release. Use fetch_format(query, "Parquet") and stream_format(query, "Parquet") instead.

Query Parameters

# Basic types
result = await client.fetch(
    "SELECT * FROM users WHERE id = {id:UInt32}",
    params={"id": 1}
)

# Lists and tuples (arrays)
result = await client.fetch(
    "SELECT * FROM users WHERE id IN {ids:Array(UInt32)}",
    params={"ids": [1, 2, 3]}  # or tuple: (1, 2, 3)
)

# Datetime and date
from datetime import datetime, date

result = await client.fetch(
    "SELECT * FROM events WHERE created_at > {dt:DateTime} AND date = {d:Date}",
    params={
        "dt": datetime(2025, 12, 14, 15, 30, 45),
        "d": date(2025, 12, 14)
    }
)

# UUID
from uuid import UUID

result = await client.fetch(
    "SELECT * FROM users WHERE uuid = {uid:UUID}",
    params={"uid": UUID("550e8400-e29b-41d4-a716-446655440000")}
)

# Decimal
from decimal import Decimal

result = await client.fetch(
    "SELECT * FROM products WHERE price > {price:Decimal(10, 2)}",
    params={"price": Decimal("99.99")}
)

# Nested arrays and maps
result = await client.fetch(
    "SELECT {matrix:Array(Array(Int32))} AS matrix, {data:Map(String, Int32)} AS data",
    params={
        "matrix": [[1, 2], [3, 4]],
        "data": {"a": 1, "b": 2}
    }
)

Supported parameter types:

• Basic: int, float, str, bool, None
• Collections: list, tuple, dict
• Date/Time: datetime, date, timedelta
• Special: UUID, Decimal, bytes, enum.Enum

Microseconds are kept, so a DateTime column rejects a value that has them — use DateTime64 or .replace(microsecond=0).

See Type Conversion for full type mapping details.

Query Settings

rows = await client.fetch(
    "SELECT * FROM users",
    settings={
        "max_execution_time": 60,
        "max_block_size": 10000
    }
)

External Tables

from aiochlite import ExternalTable

external_data = {
    "temp_data": ExternalTable(
        structure=[("id", "UInt32"), ("value", "String")],
        data=[
            {"id": 1, "value": "foo"},
            {"id": 2, "value": "bar"},
        ]
    )
}

result = await client.fetch(
    """
    SELECT t1.id, t1.name, t2.value
    FROM users t1
    JOIN temp_data t2 ON t1.id = t2.id
    """,
    external_tables=external_data
)

JSON Type

[!NOTE] For ClickHouse versions where JSON is still considered experimental, set allow_experimental_json_type=1 via client settings.

await client.execute("DROP TABLE IF EXISTS json_demo")
await client.execute("CREATE TABLE json_demo (id UInt32, doc JSON) ENGINE = Memory")

await client.insert(
    "json_demo",
    [{"id": 1, "doc": {"a": 1, "b": [True, None, {"c": "x"}]}}],
)

row = await client.fetchone("SELECT id, doc FROM json_demo WHERE id = 1")
print(row["doc"])  # Output: {"a": 1, "b": [True, None, {"c": "x"}]}

Binary Columns

A ClickHouse String is any sequence of bytes, not necessarily UTF-8. Columns decode to str; name the binary ones and they come back as bytes:

row = await client.fetchone(
    "SELECT id, payload, sha FROM blobs LIMIT 1",
    binary_columns=["payload", "sha"],  # or binary_columns="payload"
)
print(row["payload"])  # Output: b'\x00\xff\xfe\x01'

• Applies at every level of nesting: Array(String) gives list[bytes], Map(String, String) gives dict[bytes, bytes].
• FixedString(N) keeps its null padding, which str strips.
• Available on fetch, fetch_rows, fetchone, fetchval, stream and stream_rows. fetch_format / stream_format return the payload undecoded, so there it raises ChArgumentError.
• Reading a non-UTF-8 column without it raises ChProtocolError listing the columns to name.

Writing works the same way: pass bytes to an insert or a query parameter and they arrive as they are, UTF-8 or not.

await client.insert("blobs", [{"id": 1, "payload": b"\x00\xff\xfe\x01"}])

found = await client.fetchval(
    "SELECT count() FROM blobs WHERE payload = {p:String}",
    params={"p": b"\x00\xff\xfe\x01"},
)

Error Handling

Transport, server and decoding failures all derive from ChClientError, so one handler still catches them all. Invalid arguments still raise ValueError, as anywhere else in Python:

from aiochlite import ChClientError

try:
    await client.execute("SELECT * FROM non_existent_table")
except ChClientError as e:
    print(f"Query failed: {e}")

Catch a subclass when different failures need different handling:

ChServerError carries status, code, query_id and exception_tag:

from aiochlite import ChServerError, ChTransportError

try:
    await client.fetch("SELECT * FROM users")
except ChTransportError:
    ...  # retry only if the operation is idempotent: the server may have run it anyway
except ChServerError as e:
    log.error("query %s failed with code %s: %s", e.query_id, e.code, e)

Custom Session

from aiohttp import ClientSession, ClientTimeout

timeout = ClientTimeout(total=30)
async with ClientSession(timeout=timeout) as session:
    async with AsyncChClient(url="http://localhost:8123", session=session) as client:
        result = await client.fetch("SELECT 1")

A session you pass in stays yours: the client sends its credentials per request rather than adding them to the session headers, and close() leaves the session open for you to close.

Enable Compression

async with AsyncChClient(url="http://localhost:8123", enable_compression=True) as client:
    result = await client.fetch("SELECT * FROM users")

Type Conversion

aiochlite uses ClickHouse’s RowBinaryWithNamesAndTypes for result decoding:

• fetch, fetchone, fetchval, stream automatically append FORMAT RowBinaryWithNamesAndTypes and decode rows into Python values.
• Queries passed to these methods must not contain a FORMAT ... clause.
• Use execute() for statements that don’t return rows.

Automatic type conversion from ClickHouse:

Not supported: Variant, Dynamic, the geo types (Point, Ring, Polygon, MultiPolygon, LineString, MultiLineString), Interval*, BFloat16, Nested (with flatten_nested=0), AggregateFunction and QBit. Selecting one raises ChProtocolError.

Python to ClickHouse conversion:

When sending data to ClickHouse (query parameters and inserts), Python types are automatically converted:

• datetime → YYYY-MM-DD HH:MM:SS
• date → YYYY-MM-DD
• timedelta → HH:MM:SS[.ffffff] (signed; suitable for Time / Time64)
• UUID / Decimal → string representation
• enum.Enum → its value, itself converted by these rules
• list → array literal (e.g. [1,2,3])
• tuple → tuple literal (e.g. (1,2,3))
• dict → map literal (e.g. {'k':'v'})
• bytes → the bytes themselves, whether or not they are UTF-8
• None → NULL
• bool → 1/0 for query parameters, true/false inside container literals

Benchmarks

Benchmark scripts live in benchmarks/.

[!NOTE] Benchmarks always depend on machine and environment (CPU, RAM, kernel, ClickHouse version/config, network, etc). These results were captured on a local machine with 8 CPU cores (16 threads) and 32 GB RAM, running ClickHouse 26.3 LTS. Each client uses the configuration recommended by its documentation, including the aiohttp-speedups extra for aiochclient.

This measures the out-of-the-box experience, not one decoder against another: in its default configuration aiochclient decodes TSVWithNamesAndTypes, clickhouse-connect decodes Native, and aiochlite decodes RowBinaryWithNamesAndTypes. Part of the difference is the wire format rather than the decoder around it.

Fetch and decode of 100,000 rows, 10 rounds, measured 2026-08-18. Three schemas, because what a column costs to decode depends far more on its shape than on how many columns there are:

• flat columns — UInt64, DateTime('UTC'), Tuple(String, UInt16), Array(Decimal(10, 2))
• wide strings — UInt64 and nine String columns
• nested containers — UInt64, Array(Array(UInt8)), Map(String, Array(UInt8)), Array(Nullable(UInt64))

Versions: aiochlite on main past the 1.7.0 tag, clickhouse-connect 1.7.1, aiochclient 2.7.0, Python 3.14.5, and ClickHouse 26.3.17.110.

All three schemas decode through a loop compiled for them, as does any schema of scalars and of Nullable, Array, Tuple, Map and JSON nested up to four levels deep. A column nested deeper reads through its own closure instead, at the speed it had before.

clickhouse-connect ships compiled C extensions; aiochlite is pure Python on top of aiohttp alone. It stays within 4% of that C parser on flat columns and falls behind by 32% on nested containers and 56% on strings — a string is a length plus a bytes slice per value, with nothing to batch. Against aiochclient it is 2.1x-2.3x faster on all three.

License

MIT License

Copyright (c) 2026 darkstussy