Advanced usage

Raw API

For use cases which don’t require transformation between ClickHouse data and native or third party data types and structures, the ClickHouse Connect client provides methods for direct usage of the ClickHouse connection.

Client `raw_query` method

The Client.raw_query method allows direct usage of the ClickHouse HTTP query interface using the client connection. The return value is an unprocessed bytes object. It offers a convenient wrapper with parameter binding, error handling, retries, and settings management using a minimal interface:

Parameter	Type	Default	Description
`query`	str	Required	Any valid ClickHouse query.
`parameters`	dict or sequence	`None`	See Parameters argument.
`settings`	dict	`None`	See Settings argument.
`fmt`	str	`None`	ClickHouse output format. ClickHouse uses TSV when no format is specified.
`use_database`	bool	`True`	Include the database configured on the client.
`external_data`	`ExternalData`	`None`	External file or binary data. See External data.
`transport_settings`	dict	`None`	HTTP headers added to this request.

It is the caller’s responsibility to handle the resulting bytes object. Note that the Client.query_arrow is just a thin wrapper around this method using the ClickHouse Arrow output format.

Client `raw_stream` method

The synchronous Client.raw_stream method has the same API as raw_query, but returns an io.IOBase stream of byte chunks. Close the stream when processing finishes. AsyncClient.raw_stream is awaited and returns an async StreamContext for use with async with and async for.

Client `raw_insert` method

The Client.raw_insert method allows direct inserts of bytes objects or bytes object generators using the client connection. Because it does no processing of the insert payload, it is highly performant. The method provides options to specify settings and insert format:

Parameter	Type	Default	Description
`table`	str	Required	Simple or database-qualified target table.
`column_names`	Sequence[str]	`None`	Column names for the insert block. Required when `fmt` does not include names.
`insert_block`	str, bytes, generator, or `BinaryIO`	Required	Data to insert. Strings are encoded with the client encoding.
`settings`	dict	`None`	See Settings argument.
`fmt`	str	`None`	ClickHouse input format of the `insert_block` payload. `Native` is used when no format is specified.
`compression`	str	`None`	Compression already applied to `insert_block`, such as `"gzip"`, `"lz4"`, or `"zstd"`.
`transport_settings`	dict	`None`	HTTP headers added to this request.

It is the caller’s responsibility to ensure that the insert_block is in the specified format and uses the specified compression method. ClickHouse Connect uses these raw inserts for file uploads and PyArrow Tables, delegating parsing to the ClickHouse server.

Saving query results as files

You can stream files directly from ClickHouse to the local file system using the raw_stream method. For example, if you’d like to save the results of a query to a CSV file, you could use the following code snippet:

import clickhouse_connect

if __name__ == "__main__":
    client = clickhouse_connect.get_client()
    query = (
        "SELECT number, toString(number) AS number_as_str "
        "FROM system.numbers LIMIT 5"
    )
    stream = client.raw_stream(query=query, fmt="CSVWithNames")
    try:
        with open("output.csv", "wb") as file:
            for chunk in stream:
                file.write(chunk)
    finally:
        stream.close()
        client.close()

The code above yields an output.csv file with the following content:

"number","number_as_str"
0,"0"
1,"1"
2,"2"
3,"3"
4,"4"

Similarly, you could save data in TabSeparated and other formats. See Formats for Input and Output Data for an overview of all available format options.

Multithreaded, multiprocess, and async/event driven use cases

ClickHouse Connect works well in multithreaded, multiprocess, and event-loop-driven/asynchronous applications. All query and insert processing occurs within a single thread, so operations are generally thread-safe. (Parallel processing of some operations at a low level is a possible future enhancement to overcome the performance penalty of a single thread, but even in that case thread safety will be maintained.) Because each query or insert executed maintains state in its own QueryContext or InsertContext object, respectively, these helper objects aren’t thread-safe, and they shouldn’t be shared between multiple processing streams. See the additional discussion about context objects in the QueryContexts and InsertContexts sections. Additionally, in an application that has two or more queries and/or inserts “in flight” at the same time, there are two further considerations to keep in mind. The first is the ClickHouse “session” associated with the query/insert, and the second is the HTTP connection pool used by ClickHouse Connect Client instances.

AsyncClient

ClickHouse Connect provides a native aiohttp-based client for asyncio applications. Install the optional dependency before using it:

pip install "clickhouse-connect[async]"

Await get_async_client to create and initialize a client. I/O methods such as query, command, and insert are coroutines:

import asyncio

import clickhouse_connect


async def main():
    async with await clickhouse_connect.get_async_client() as client:
        result = await client.query(
            "SELECT name FROM system.databases ORDER BY name LIMIT 1"
        )
        print(result.result_rows)


asyncio.run(main())

The async client follows the same query, insert, raw, Arrow, and streaming contract as the synchronous client. It uses aiohttp for network I/O. CPU-bound Native-format parsing may run in an executor so that it doesn’t block the event loop. Async streaming methods are awaited before entering the returned context:

async with await client.query_rows_stream(
    "SELECT number FROM numbers(100000)"
) as stream:
    async for row in stream:
        process(row)

Unlike the synchronous factory, get_async_client disables automatic session IDs by default so concurrent coroutines can share a client. Pass an explicit session_id or autogenerate_session_id=True only when you need session state and will avoid concurrent queries in that session.

Managing ClickHouse session IDs

Each ClickHouse query occurs within the context of a ClickHouse “session”. Sessions are currently used for two purposes:

To associate specific ClickHouse settings with multiple queries (see the user settings). The ClickHouse SET command is used to change the settings for the scope of a user session.
To track temporary tables.

By default, a synchronous Client uses a generated session ID. SET statements and temporary tables therefore persist across requests from that client. The async factory does not generate a session ID by default. ClickHouse doesn’t allow concurrent queries in the same session, and the client raises a ProgrammingError if this is attempted, so use one of the following patterns:

Create a separate Client instance for each thread/process/event handler that needs session isolation. This preserves per-client session state (temporary tables and SET values).
Use a unique session_id for each query via the settings argument when calling query, command, or insert, if you don’t require shared session state.
Disable sessions on a shared client by setting autogenerate_session_id=False before creating the client (or pass it directly to get_client).

import clickhouse_connect
from clickhouse_connect import common

common.set_setting("autogenerate_session_id", False)
client = clickhouse_connect.get_client(
    host="somehost.com",
    username="dbuser",
    password="password",
)

Alternatively, pass autogenerate_session_id=False directly to get_client(...). In this case ClickHouse Connect doesn’t send a session_id; the server doesn’t treat separate requests as belonging to the same session. Temporary tables and session-level settings won’t persist across requests.

Customizing the HTTP connection pool

ClickHouse Connect uses urllib3 connection pools to handle the underlying HTTP connection to the server. By default, all client instances share the same connection pool, which is sufficient for the majority of use cases. This default pool maintains up to 8 HTTP Keep Alive connections to each ClickHouse server used by the application. For large multi-threaded applications, separate connection pools may be appropriate. Customized connection pools can be provided as the pool_mgr keyword argument to the main clickhouse_connect.get_client function:

import clickhouse_connect
from clickhouse_connect.driver import httputil

big_pool_mgr = httputil.get_pool_manager(maxsize=16, num_pools=12)

client1 = clickhouse_connect.get_client(pool_mgr=big_pool_mgr)
client2 = clickhouse_connect.get_client(pool_mgr=big_pool_mgr)

Clients can share a pool manager, or each client can use a separate manager. For more details, see the urllib3 PoolManager documentation. The async client owns an aiohttp pool rather than using urllib3. Configure it through connector_limit, connector_limit_per_host, and keepalive_timeout on get_async_client. Calling await async_client.close_connections() rotates the pool without interrupting in-flight requests.

Home

JavaScript

Python

Go

Java

C#

Rust

C++

C

Third-party clients

Raw API

Client `raw_query` method

Client `raw_stream` method

Client `raw_insert` method

Saving query results as files

Multithreaded, multiprocess, and async/event driven use cases

AsyncClient

Managing ClickHouse session IDs

Customizing the HTTP connection pool

​Raw API

​Client raw_query method

​Client raw_stream method

​Client raw_insert method

​Saving query results as files

​Multithreaded, multiprocess, and async/event driven use cases

​AsyncClient

​Managing ClickHouse session IDs

​Customizing the HTTP connection pool

Raw API

Client `raw_query` method

Client `raw_stream` method

Client `raw_insert` method

Saving query results as files

Multithreaded, multiprocess, and async/event driven use cases

AsyncClient

Managing ClickHouse session IDs

Customizing the HTTP connection pool