ClickHouse Connect

ClickHouse Connect supports Variant, Dynamic, and JSON types. Note that the legacy Object('json') type is no longer supported.

Variant Types

• Reading: Values are read as the matching Python type. To preserve the originating member type, enable the typed format using query_formats={"Variant": "typed"}. This returns TypedVariant(value, type_name) objects.
• Writing: Native inserts select a member based on the Python value type. If multiple members map to the same Python type, use clickhouse_connect.datatypes.dynamic.typed_variant(value, "TypeName") to select the member explicitly.

Dynamic Types

• Reading: Values are read as the matching Python type.
• Writing: Inserts are currently sent via their String representation.

JSON Types

• Reading: The default format returns Python dictionaries. To receive JSON strings instead, use query_formats={"JSON": "string"}.
• Writing: You can insert values as Python dictionaries or JSON object strings.

Note: Some complex values in JSON or Dynamic columns stored in the shared-data area may be returned as raw bytes if the client cannot decode them.

from clickhouse_connect.datatypes.dynamic import typed_variant

# Explicitly selecting a Variant member
client.insert('table', [[typed_variant(10, 'Int64')]], column_names=['variant_col'])

# Reading JSON as strings instead of dictionaries
result = client.query('SELECT json_col FROM table', query_formats={'JSON': 'string'})

ClickHouse sessions are used to associate settings (via SET) and track temporary tables.

• Synchronous Client: Uses a generated session ID by default. SET statements and temporary tables persist across requests.
• AsyncClient: Does not generate a session ID by default to allow concurrent coroutines to share a client. However, ClickHouse does not allow concurrent queries in the same session; attempting this will raise a ProgrammingError.

Patterns for managing sessions:

1. Isolation: Create a separate Client instance for each thread/process/event handler.
2. Per-query session: Pass a unique session_id via the settings argument in query(), command(), or insert().
3. Disable sessions: Set autogenerate_session_id=False when creating the client. This prevents SET and temporary tables from persisting across requests.

import clickhouse_connect
from clickhouse_connect import common

# Option 1: Global setting
common.set_setting("autogenerate_session_id", False)
client = clickhouse_connect.get_client(host="somehost.com", ...)

# Option 2: Pass directly to get_client
client = clickhouse_connect.get_client(autogenerate_session_id=False, host="somehost.com", ...)

A StreamContext is a combined Python context manager and generator returned by query_*_stream methods. It manages the lifecycle of the streaming HTTP response.

Key Features:

• Lifecycle Management: You must use with client.query_..._stream(...) as stream: to ensure resources are released.
• Metadata Access: You can access the parent result object (e.g., QueryResult or NumpyResult) via the stream.source property to retrieve column_names and types before or during iteration.
• Single Use: A StreamContext can only be used once to consume the stream.

Deferred Usage Pattern:

You can access metadata from the source property before entering the with block:

df_stream = client.query_df_stream("SELECT * FROM hits")
column_names = df_stream.source.column_names
with df_stream:
    for df in df_stream:
        process_dataframe(df)

with client.query_row_block_stream(
    "SELECT pickup, dropoff, pickup_longitude, pickup_latitude FROM taxi_trips"
) as stream:
    for block in stream:
        for row in block:
            process_trip(row)

Client instances are NOT thread-safe when using session IDs. By default, clients use an auto-generated session ID, and concurrent queries within the same session will raise a ProgrammingError.

Option 1: Disable sessions (Recommended for shared clients)

If you want to share a single client across multiple threads, set autogenerate_session_id=False. This allows threads to use the same client safely.

Option 2: Separate client per thread

If you require sessions (e.g., to use TEMPORARY tables), create a unique client instance for every thread to ensure session isolation.

import clickhouse_connect
import threading

# Option 1: Disable sessions for safe sharing
client = clickhouse_connect.get_client(
    host="my-host",
    username="default",
    password="password",
    autogenerate_session_id=False,
)

def worker(thread_id):
    result = client.query(f"SELECT {thread_id}")
    print(f"Thread {thread_id}: {result.result_rows[0][0]}")

threads = [threading.Thread(target=worker, args=(i,)) for i in range(10)]
for t in threads:
    t.start()
for t in threads:
    t.join()

client.close()

ClickHouse Connect converts DateTime and DateTime64 values to Python datetime objects. You can control this behavior using tz_source and tz_mode parameters in your query calls.

tz_source (Fallback timezone for columns without metadata)

• "auto" (default): Uses server timezone if safe, otherwise local timezone.
• "server": Always uses the server timezone.
• "local": Always uses the local process timezone.

tz_mode (Timezone awareness)

• "naive_utc" (default): Returns UTC and UTC-equivalent results as naive datetime objects.
• "aware": Returns timezone-aware UTC values.
• "schema": Returns timezone-aware values only when the column type declares a timezone; otherwise returns naive values.

Resolution Order

For naive_utc and aware modes, the active timezone is determined by:

1. column_tzs override
2. Column type metadata
3. query_tz override
4. HTTP response metadata
5. tz_source fallback

Note: tz_mode="schema" ignores query and fallback timezones, but column_tzs still takes precedence.

Requirements

Timezone names are resolved via zoneinfo. On minimal Linux images, you may need to install the timezone database:

pip install clickhouse-connect[tzdata]

result = client.query(
    "SELECT "
    "toDateTime('2026-01-15 12:00:00', 'UTC') AS utc_time, "
    "toDateTime('2026-01-15 12:00:00', 'America/Denver') AS denver_time",
    tz_mode="aware",
)

assert result.first_row[0].tzinfo is not None
assert result.first_row[1].tzinfo is not None

ClickHouse Connect supports both client-side and server-side parameter binding to prevent SQL injection and improve performance.

Client-side parameters

Uses printf-style formatting. You can pass a dict or a tuple to the parameters argument.

Server-side parameters

Uses ClickHouse's native parameter binding (e.g., {name:Type}). This is generally more secure and offers better performance for SELECT queries.

Note: When using server-side parameters, ensure the type is specified in the query string (e.g., {db:String}).

import clickhouse_connect

client = clickhouse_connect.get_client()

# 1. Client-side: Dictionary (printf-style)
query_dict = "SELECT * FROM system.tables WHERE database = %(db)s AND name LIKE %(pattern)s"
client.query(query_dict, parameters={"db": "system", "pattern": "%query%"})

# 2. Client-side: Tuple
query_tuple = "SELECT * FROM system.tables WHERE database = %s LIMIT %s"
client.query(query_tuple, parameters=("system", 5))

# 3. Server-side: Native binding (Recommended)
query_server = "SELECT * FROM system.tables WHERE database = {db:String} AND name = {tbl:String}"
client.query(query_server, parameters={"db": "system", "tbl": "query_log"})

By importing clickhouse_connect.cc_sqlalchemy, ClickHouse-specific methods are attached to the standard sqlalchemy.select object via monkey-patching.

Available Chainables

• .final()
• .sample(fraction)
• .prewhere(condition)
• .limit_by([columns], limit)
• .array_join(column)
• .left_array_join(column, [other_column], alias=None)
• .ch_join(table, condition, isouter=False, strictness=None)

Important Usage Notes

1. Chained Joins: When using .ch_join(), the left side of the chain is the prior join or the FROM target. Do not mix .ch_join() with SQLAlchemy's native .join() in the same statement.
2. Type Safety: Because chainables are added via monkey-patching, static type checkers (like Mypy) will flag them. To get full type-hinting support, use the typed entry point clickhouse_connect.cc_sqlalchemy.select instead of sqlalchemy.select.
3. Server Constraints: The dialect compiles these modifiers, but the ClickHouse server still enforces rules (e.g., FINAL is rejected on plain MergeTree engines, and PREWHERE is rejected against subqueries).

import clickhouse_connect.cc_sqlalchemy  # Side-effect import to register chainables on sqlalchemy.select
from sqlalchemy import column, func, select
from clickhouse_connect.cc_sqlalchemy import Lambda

# Using monkey-patched sqlalchemy.select
select(tbl).array_join(tbl.c.tags)
select(tbl).final()
select(tbl).prewhere(tbl.c.active == 1)

# Using the typed entry point for better IDE/Type-checker support
from clickhouse_connect.cc_sqlalchemy import select as ch_select
ch_select(authors.c.name).select_from(books).ch_join(
    authors, authors.c.id == books.c.author_id, isouter=True, strictness="ANY"
).final(books)

# Using Lambda in func
func.arrayMap(Lambda("x", column("x") * 2), tbl.c.nums)