> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-detect-table-modification.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
> Advanced querying with ClickHouse Connect
# 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.
```python theme={null}
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 `QueryContext`s 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](/reference/settings/session-settings#max_block_size) -- Maximum block size in rows.
* [preferred\_block\_size\_bytes](/reference/settings/session-settings#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:
```python theme={null}
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, `StreamContext`s 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. The exception carries the server-side error message when ClickHouse reported one.
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).
```python theme={null}
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
```python theme={null}
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
```python theme={null}
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
```python theme={null}
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
```python theme={null}
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
```python theme={null}
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`.
```python theme={null}
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:
#
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`.
```python theme={null}
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:
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.
```python theme={null}
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:
#
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
```python theme={null}
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 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.
```python theme={null}
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.
```python theme={null}
# 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.
```python theme={null}
# 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"},
)
```
| ClickHouse Type | Native Python Type | Read Formats | Comments |
| ----------------------- | ----------------------- | ----------------- | --------------------------------------------------------------------------------------------------------- |
| Int\[8-64], UInt\[8-32] | int | string | |
| UInt64 | int | signed | Superset doesn't currently handle large unsigned UInt64 values |
| \[U]Int\[128,256] | int | string | Pandas and NumPy int values are 64 bits maximum, so these can be returned as strings |
| BFloat16 | float | - | All Python floats are 64 bits internally |
| Float32 | float | string | All Python floats are 64 bits internally |
| Float64 | float | string | |
| Decimal | decimal.Decimal | - | |
| String | str | bytes | ClickHouse String columns have no inherent encoding, so they're also used for variable length binary data |
| FixedString | bytes | string | FixedStrings are fixed size byte arrays, but sometimes are treated as Python strings |
| Enum\[8,16] | str | int | The native format returns labels; `int` returns the underlying integer. |
| Date | datetime.date | int | The integer format returns days since 1970-01-01. |
| Date32 | datetime.date | int | The integer format returns the wider signed day offset. |
| DateTime | datetime.datetime | int | The integer format returns epoch seconds. |
| DateTime64 | datetime.datetime | int | The integer format returns ticks at the column precision. Python `datetime` is limited to microseconds. |
| Time | datetime.timedelta | int, string, time | The integer format returns seconds. The `time` format is limited to values that fit `datetime.time`. |
| Time64 | datetime.timedelta | int, string, time | The integer format returns ticks at the column precision. Python `timedelta` is limited to microseconds. |
| IPv4 | `ipaddress.IPv4Address` | string, int | IP addresses can be read as strings or integers. |
| IPv6 | `ipaddress.IPv6Address` | string | IP addresses can be read as strings and properly formatted can be inserted as IP addresses |
| Tuple | dict or tuple | tuple, dict, json | Named tuples return dictionaries by default; unnamed tuples return tuples. |
| Map | dict | - | |
| Nested | Sequence\[dict] | - | |
| UUID | uuid.UUID | string | UUIDs can be read as strings formatted as per RFC 4122
|
| JSON | dict | string | A python dictionary is returned by default. The `string` format will return a JSON string |
| Variant | object | typed | `typed` returns `TypedVariant(value, type_name)` so the originating member type is preserved. |
| Dynamic | object | - | Returns the matching Python type for the ClickHouse datatype stored for the value |
| QBit | list\[float] | - | NumPy is used automatically for faster bit transposition when installed. |
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](/reference/engines/table-engines/special/external-data). Client query methods accept a `clickhouse_connect.driver.external.ExternalData` object through the `external_data` parameter.
| Name | Type | Description |
| ---------- | ----------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| file\_path | str | Path to a file on the local system path to read the external data from. Either `file_path` or `data` is required |
| file\_name | str | The name of the external data "file". If not provided, taken from the file name portion of `file_path`. The external table name is the file name without its extension |
| data | bytes | The external data in binary form (instead of being read from a file). Either `data` or `file_path` is required |
| fmt | str | ClickHouse [input format](/reference/formats) of the data. Defaults to `TSV` |
| types | str or seq of str | A list of column data types in the external data. If a string, types should be separated by commas. Either `types` or `structure` is required |
| structure | str or seq of str | A list of column name + data type in the data (see examples). Either `structure` or `types` is required |
| mime\_type | str | Optional MIME type of the file data. Currently ClickHouse ignores this HTTP subheader |
This example joins an external CSV file to a `directors` table stored on the server:
```python theme={null}
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.
```python theme={null}
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.