Advanced querying

QueryContexts

ClickHouse Connect executes standard queries within a QueryContext. The QueryContext contains the key structures that are used to build queries against the ClickHouse database, and the configuration used to process the result into a QueryResult or other response data structure. That includes the query itself, parameters, settings, read formats, and other properties.

A QueryContext can be acquired using the client create_query_context method. This method takes the same parameters as the core query method. This query context can then be passed to the query, query_df, or query_np methods as the context keyword argument instead of any or all of the other arguments to those methods. Note that additional arguments specified for the method call will override any properties of QueryContext.

The clearest use case for a QueryContext is to send the same query with different binding parameter values. All parameter values can be updated by calling the QueryContext.set_parameters method with a dictionary, or any single value can be updated by calling QueryContext.set_parameter with the desired key, value pair.

qc = client.create_query_context(
    query="SELECT {k:Int32}",
    parameters={"k": 13},
)
result = client.query(context=qc)
assert result.first_row == (13,)

qc.set_parameter("k", 79)
result = client.query(context=qc)
assert result.first_row == (79,)

Note that QueryContexts aren’t thread safe, but a copy can be obtained in a multi-threaded environment by calling the QueryContext.updated_copy method.

Streaming queries

The ClickHouse Connect Client provides multiple methods for retrieving data as a stream (implemented as a Python generator):

• query_column_block_stream – Returns query data in blocks as a sequence of columns using native Python objects
• query_row_block_stream – Returns query data as a block of rows using native Python objects
• query_rows_stream – Returns query data as a sequence of rows using native Python objects
• query_np_stream – Returns each ClickHouse block of query data as a NumPy array
• query_df_stream – Returns each ClickHouse Block of query data as a Pandas DataFrame
• query_arrow_stream – Returns query data as PyArrow RecordBatch objects
• query_df_arrow_stream – Returns each Arrow batch as a Pandas or Polars DataFrame, selected by dataframe_library

Each method returns a StreamContext that must be opened with a with statement. Async client streaming methods are awaited and opened with async with.

Data blocks

ClickHouse Connect processes all data from the primary query method as a stream of blocks received from the ClickHouse server. These blocks are transmitted in the custom “Native” format to and from ClickHouse. A “block” is simply a sequence of columns of binary data, where each column contains an equal number of data values of the specified data type. (As a columnar database, ClickHouse stores this data in a similar form.) The size of a block returned from a query is governed by two user settings that can be set at several levels (user profile, user, session, or query). They’re:

• max_block_size – Maximum block size in rows.
• preferred_block_size_bytes – Preferred block size in bytes.

Regardless of preferred_block_size_bytes, a block will not exceed max_block_size rows. The actual size can be smaller and should not be treated as stable.

When using one of the Client query_*_stream methods, results are returned on a block by block basis. ClickHouse Connect only loads a single block at a time. This allows processing large amounts of data without the need to load all of a large result set into memory. Note the application should be prepared to process any number of blocks and the exact size of each block can’t be controlled.

HTTP data buffer for slow processing

If an application consumes blocks much more slowly than the server produces them, the HTTP connection can close before processing completes. Increase the common http_buffer_size setting when the application has enough memory to buffer more response data. The default is 10 MiB. lz4 and zstd response bytes remain compressed in this buffer, which increases its effective capacity.

StreamContexts

Each of the query_*_stream methods (like query_row_block_stream) returns a ClickHouse StreamContext object, which is a combined Python context/generator. This is the basic usage:

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)

Note that trying to use a StreamContext without a with statement will raise an error. The use of a Python context ensures that the stream (in this case, a streaming HTTP response) will be properly closed even if not all the data is consumed and/or an exception is raised during processing. Also, StreamContexts can only be used once to consume the stream. Trying to use a StreamContext after it has exited will produce a StreamClosedError.

If the connection fails while a result is being read, a StreamFailureError is raised instead of silently returning a truncated result. Its message follows the client’s show_clickhouse_errors setting.

You can use the source property of the StreamContext to access the parent result object, which includes column names and types. For most streams this is a QueryResult; the query_np_stream and query_df_stream methods expose a NumpyResult instead.

Stream types

The query_column_block_stream method returns the block as a sequence of column data stored as native Python data types. Using the above taxi_trips queries, the data returned will be a list where each element of the list is another list (or tuple) containing all the data for the associated column. So block[0] would be a tuple containing nothing but strings. Column oriented formats are most used for doing aggregate operations for all the values in a column, like adding up total fares.

The query_row_block_stream method returns the block as a sequence of rows like a traditional relational database. For taxi trips, the data returned will be a list where each element of the list is another list representing a row of data. So block[0] would contain all the fields in order for the first taxi trip, block[1] would contain a row for all the fields in the second taxi trip, and so on. Row-oriented results are normally used for display or transformation processes.

The query_rows_stream method automatically moves to the next block and yields one row at a time. It is the row-by-row counterpart to query_row_block_stream.

The query_np_stream method returns each block as a NumPy array. When all result columns share a NumPy dtype, the array is two-dimensional with shape (rows, columns). Mixed results are returned as a one-dimensional structured array or use the object dtype.

The query_df_stream method returns each ClickHouse Block as a two-dimensional Pandas DataFrame. Here’s an example which shows that the StreamContext object can be used as a context in a deferred fashion (but only once).

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)

The query_df_arrow_stream method converts Arrow batches to Pandas or Polars DataFrames. Select the library with dataframe_library, which defaults to "pandas".

Finally, query_arrow_stream wraps a ClickHouse ArrowStream response in a StreamContext. Each iteration returns a PyArrow RecordBatch.

Streaming examples

Stream rows

import clickhouse_connect

client = clickhouse_connect.get_client()

# Stream large result sets row by row
with client.query_rows_stream("SELECT number, number * 2 as doubled FROM system.numbers LIMIT 100000") as stream:
    for row in stream:
        print(row)  # Process each row
        # Output:
        # (0, 0)
        # (1, 2)
        # (2, 4)
        # Additional rows follow

Stream row blocks

import clickhouse_connect

client = clickhouse_connect.get_client()

# Stream in blocks of rows (more efficient than row-by-row)
with client.query_row_block_stream("SELECT number, number * 2 FROM system.numbers LIMIT 100000") as stream:
    for block in stream:
        print(f"Received block with {len(block)} rows")

Stream Pandas DataFrames

import clickhouse_connect

client = clickhouse_connect.get_client()

# Stream query results as Pandas DataFrames
with client.query_df_stream("SELECT number, toString(number) AS str FROM system.numbers LIMIT 100000") as stream:
    for df in stream:
        # Process each DataFrame block
        print(f"Received DataFrame with {len(df)} rows")
        print(df.head(3))

Stream Arrow batches

import clickhouse_connect

client = clickhouse_connect.get_client()

# Stream query results as Arrow record batches
with client.query_arrow_stream("SELECT * FROM large_table") as stream:
    for arrow_batch in stream:
        # Process each Arrow batch
        print(f"Received Arrow batch with {arrow_batch.num_rows} rows")

Async stream rows

import asyncio

import clickhouse_connect


async def main():
    async_client = await clickhouse_connect.get_async_client()
    async with await async_client.query_rows_stream(
        "SELECT number FROM numbers(100000)"
    ) as stream:
        async for row in stream:
            print(row)


asyncio.run(main())

NumPy, Pandas, and Arrow queries

ClickHouse Connect provides specialized query methods for working with NumPy, Pandas, and Arrow data structures. These methods allow you to retrieve query results directly in these popular data formats without manual conversion.

NumPy queries

The query_np method returns query results as a NumPy array instead of a ClickHouse Connect QueryResult.

import clickhouse_connect

client = clickhouse_connect.get_client()

# Query returns a NumPy array
np_array = client.query_np("SELECT number, number * 2 AS doubled FROM system.numbers LIMIT 5")

print(type(np_array))
# Output:
# <class 'numpy.ndarray'>

print(np_array)
# Output:
# [[0 0]
#  [1 2]
#  [2 4]
#  [3 6]
#  [4 8]]

Pandas queries

The query_df method returns query results as a Pandas DataFrame instead of a ClickHouse Connect QueryResult.

import clickhouse_connect

client = clickhouse_connect.get_client()

# Query returns a Pandas DataFrame
df = client.query_df("SELECT number, number * 2 AS doubled FROM system.numbers LIMIT 5")

print(type(df))
# Output: <class 'pandas.core.frame.DataFrame'>
print(df)
# Output:
#    number  doubled
# 0       0        0
# 1       1        2
# 2       2        4
# 3       3        6
# 4       4        8

PyArrow queries

The query_arrow method returns a PyArrow Table using ClickHouse’s Arrow output format directly. It accepts query, parameters, settings, external_data, and transport_settings. The use_strings option controls whether ClickHouse String columns are emitted as Arrow strings or binary values.

import clickhouse_connect

client = clickhouse_connect.get_client()

# Query returns a PyArrow Table
arrow_table = client.query_arrow("SELECT number, toString(number) AS str FROM system.numbers LIMIT 3")

print(type(arrow_table))
# Output:
# <class 'pyarrow.lib.Table'>

print(arrow_table)
# Output:
# pyarrow.Table
# number: uint64 not null
# str: string not null
# ----
# number: [[0,1,2]]
# str: [["0","1","2"]]

Arrow-backed DataFrames

ClickHouse Connect supports efficient DataFrame creation from Arrow results through query_df_arrow and query_df_arrow_stream. These methods avoid conversion through Python row objects and reuse Arrow buffers where the target library permits:

• query_df_arrow: Executes the query using the ClickHouse Arrow output format and returns a DataFrame.
  • dataframe_library="pandas" returns a Pandas 2.0 or later DataFrame using pd.ArrowDtype.
  • dataframe_library="polars" returns a Polars DataFrame created through pl.from_arrow.
• query_df_arrow_stream: Streams Arrow batches as Pandas or Polars DataFrames.

Query to Arrow-backed DataFrame

import clickhouse_connect

client = clickhouse_connect.get_client()

# Query returns a Pandas DataFrame with Arrow dtypes (requires pandas 2.x)
df = client.query_df_arrow(
    "SELECT number, toString(number) AS str FROM system.numbers LIMIT 3",
    dataframe_library="pandas"
)

print(df.dtypes)
# Output:
# number    uint64[pyarrow]
# str       string[pyarrow]
# dtype: object

# Or use Polars
polars_df = client.query_df_arrow(
    "SELECT number, toString(number) AS str FROM system.numbers LIMIT 3",
    dataframe_library="polars"
)
print(polars_df.dtypes)
# Output:
# [UInt64, String]

# Streaming into batches of DataFrames (polars shown)
with client.query_df_arrow_stream(
    "SELECT number, toString(number) AS str FROM system.numbers LIMIT 100000", dataframe_library="polars"
) as stream:
    for df_batch in stream:
        print(f"Received {type(df_batch)} batch with {len(df_batch)} rows and dtypes: {df_batch.dtypes}")

Notes and caveats

• ClickHouse controls the Arrow schema. Types without a direct Arrow representation can be returned using a compatible physical type, including binary fields. Inspect table.schema or DataFrame dtypes before applying application-specific conversions.
• Arrow-backed Pandas results require Pandas 2.0 or later.
• use_strings controls whether ClickHouse String columns use Arrow string or binary fields when the server supports output_format_arrow_string_as_string.
• tz_mode="schema" is not yet supported by Arrow-based query methods. They warn and preserve the timezone metadata supplied by the Arrow response.

Read formats

Read formats control values returned by query, query_np, and query_df. They don’t apply to raw or Arrow methods because those methods use a server output format directly. For example, setting the UUID read format to "string" returns UUID strings instead of uuid.UUID objects.

The “data type” argument for any formatting function can include wildcards. The format is a single lowercase string. Container wrappers such as Array, Nullable, and LowCardinality preserve the selected format for their element type.

Read formats can be set at several levels:

• Globally, using the methods defined in the clickhouse_connect.datatypes.format package. This will control the format of the configured datatype for all queries.

from clickhouse_connect.datatypes.format import set_read_format

# Return both IPv6 and IPv4 values as strings
set_read_format("IPv*", "string")

# Return all Date types as the underlying epoch second or epoch day
set_read_format("Date*", "int")

• For an entire query, using the optional query_formats dictionary argument. In that case any column (or subcolumn) of the specified data types will use the configured format.

# Return any UUID column as a string
client.query(
    "SELECT user_id, user_uuid, device_uuid FROM users",
    query_formats={"UUID": "string"},
)

• For a specific result column, use the optional column_formats dictionary. Each key is a returned column name. Its value is a format string or a nested mapping from ClickHouse type names to formats, which is useful for Tuples, Maps, and other container types.

# Return IPv6 values in the `dev_address` column as strings
client.query(
    "SELECT device_id, dev_address, gw_address FROM devices",
    column_formats={"dev_address": "string"},
)

Read format options (Python types)

query_np, query_np_stream, query_df, and query_df_stream support Time64 scales 0, 3, 6, and 9. Other scales raise ProgrammingError because they do not have a matching NumPy time unit. Use a standard Python query with the int or string read format to preserve those precisions. The experimental Rust codec documents an exception for Time64 stored inside Dynamic.

External data

ClickHouse queries can accept external data in any supported input format. The client sends the data as part of the request, and the query can reference it as a temporary external table. See the ClickHouse external data documentation. Client query methods accept a clickhouse_connect.driver.external.ExternalData object through the external_data parameter.

This example joins an external CSV file to a directors table stored on the server:

import clickhouse_connect

from clickhouse_connect.driver.external import ExternalData

client = clickhouse_connect.get_client()
ext_data = ExternalData(
    file_path="/data/movies.csv",
    fmt="CSV",
    structure=[
        "movie String",
        "year UInt16",
        "rating Decimal32(3)",
        "director String",
    ],
)
result = client.query(
    "SELECT name, avg(rating) "
    "FROM directors INNER JOIN movies ON directors.name = movies.director "
    "GROUP BY directors.name",
    external_data=ext_data,
).result_rows

Additional external data files can be added to the initial ExternalData object using the add_file method, which takes the same parameters as the constructor. For HTTP, all external data is transmitted as part of a multi-part/form-data file upload.

The chDB backend does not support external data.

Time zones

ClickHouse DateTime and DateTime64 values are transmitted as epoch-based numeric values. ClickHouse Connect converts them to Python datetime objects using column metadata, query overrides, and the client’s timezone policy.

The client has two independent timezone options:

• tz_source selects the fallback timezone for columns without explicit timezone metadata:
  • "auto" is the default. It uses the server timezone when the client can resolve it safely across daylight-saving transitions, otherwise it uses the local timezone.
  • "server" always uses the server timezone.
  • "local" always uses the local process timezone.
• tz_mode controls timezone awareness:
  • "naive_utc" is the default. UTC and UTC-equivalent results are returned as naive datetime objects for backward compatibility.
  • "aware" preserves UTC tzinfo and returns timezone-aware UTC values.
  • "schema" returns timezone-aware values only when the column type declares a timezone, and naive values for bare DateTime/DateTime64 columns.

For normal "naive_utc" and "aware" queries, the active timezone is selected in this order:

1. A per-column column_tzs override.
2. Timezone metadata on the ClickHouse column type.
3. The query-wide query_tz override.
4. Timezone information returned with the HTTP response.
5. The fallback selected by tz_source.

tz_mode="schema" ignores query and fallback timezones, but an explicit column_tzs override still takes precedence.

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

Timezone names are resolved with the standard library zoneinfo module. Windows installs receive tzdata automatically. On minimal Linux images without an IANA timezone database, install clickhouse-connect[tzdata].

Pandas results preserve each ClickHouse type’s natural resolution, such as datetime64[s] for DateTime and datetime64[ms] for DateTime64(3). The Arrow-backed DataFrame methods query_df_arrow and query_df_arrow_stream don’t yet implement tz_mode="schema" and will emit a warning when it is requested. query_arrow and query_arrow_stream return the timezone metadata from the Arrow response unchanged.