> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-parallel-read-in-order-multi-part.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
> Advanced usage with ClickHouse Connect
# 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 for eligible reads, and settings management using a minimal interface. See [HTTP retries](/integrations/language-clients/python/driver-api#http-retries).
| Parameter | Type | Default | Description |
| - | - | - | - |
| `query` | str | Required | Any valid ClickHouse query. |
| `parameters` | dict or sequence | `None` | See [Parameters argument](/integrations/language-clients/python/driver-api#parameters-argument). |
| `settings` | dict | `None` | See [Settings argument](/integrations/language-clients/python/driver-api#settings-argument-1). |
| `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](/integrations/language-clients/python/advanced-querying#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](/integrations/language-clients/python/driver-api#settings-argument-1). |
| `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.
String and bytes payloads can be retried after a remote connection close. Generators and open files cannot be replayed. See [Insert retries and deduplication](/integrations/language-clients/python/advanced-inserting#insert-retries-and-deduplication).
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:
```python theme={null}
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:
```csv theme={null}
"number","number_as_str"
0,"0"
1,"1"
2,"2"
3,"3"
4,"4"
```
Similarly, you could save data in [TabSeparated](/reference/formats/TabSeparated/TabSeparated) and other formats. See [Formats for Input and Output Data](/reference/formats) for an overview of all available format options.
Multithreaded, multiprocess, and async/event driven use cases
ClickHouse Connect supports multithreaded, multiprocess, and asyncio applications. A synchronous HTTP client can be shared across threads as long as concurrent calls do not use the same ClickHouse session. See the session discussion below. The async client runs some parsing and insert preparation in worker threads.
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](/integrations/language-clients/python/advanced-querying#querycontexts) and [InsertContexts](/integrations/language-clients/python/advanced-inserting#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:
```bash theme={null}
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:
```python theme={null}
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.
`insert_arrow` and `insert_df_arrow` run DataFrame conversion and Arrow encoding in a dedicated worker for each client, separate from streaming query readers. Each client runs one preparation operation at a time to limit temporary memory use. Close the client with `async with` or `await client.close()` when finished. PyArrow operations that hold Python's global interpreter lock can still pause other coroutines.
An async client owns an aiohttp session created in one event loop. Before moving the client to another event loop, close it in the owning loop, then call `await client._initialize()` in the new loop before making requests. If the owning loop has already closed, call `await client.close()` followed by `await client._initialize()` in the current loop. aiohttp may still report an unclosed transport when cleanup begins only after the owning loop has closed, so close before transfer when possible.
Async streaming methods are awaited before entering the returned context:
```python theme={null}
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](/reference/settings/session-settings)). The ClickHouse `SET` command is used to change the settings for the scope of a user session.
* To track [temporary tables.](/reference/statements/create/table#temporary-tables)
By default, a synchronous `Client` uses a generated session ID. `SET` statements and temporary tables persist across requests from that client only when those requests reach the same ClickHouse server process. The async factory does not generate a session ID by default. Named-session state and same-session overlap checks are process-local, and the client raises a `ProgrammingError` when it detects local overlap before sending the request. In ClickHouse Cloud or other load-balanced deployments, do not rely on a fixed `session_id` as distributed state or as a distributed mutex. Serialize before sending requests to ClickHouse if overlap matters. Use one of these patterns:
1. 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).
2. 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.
3. Disable sessions on a shared client by setting `autogenerate_session_id=False` before creating the client (or pass it directly to `get_client`).
```python theme={null}
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 synchronous client instances in a process share the same connection pool, which is sufficient for the majority of use cases. Each multiprocessing worker gets its own process-local default pool and reuses it across clients created in that worker. A client created before a fork keeps the parent's pool and should not be used in the child. The default pool maintains up to 8 HTTP Keep Alive connections to each ClickHouse server used by the application.
The default socket options enable TCP keepalive and `TCP_NODELAY`. The operating system manages socket send and receive buffer sizes.
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:
```python theme={null}
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](https://urllib3.readthedocs.io/en/stable/advanced-usage.html#customizing-pool-behavior).
To set socket options, pass `socket_options` to `httputil.get_pool_manager` or `httputil.get_pool_manager_options`. This replaces the entire default list, including keepalive options and `TCP_NODELAY`. Pass `[]` or `None` to apply no explicit socket options.
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.
For async queries and inserts, waiting for a free pool slot has no timeout. Fully read or close streaming responses to release their pool slots. `connect_timeout` starts after a slot is available and covers DNS resolution, TCP and TLS setup, and proxy negotiation. `send_receive_timeout` limits socket reads. To set a deadline for the whole operation, including pool waiting, use `asyncio.wait_for`, for example `await asyncio.wait_for(client.query("SELECT 13"), timeout=30)`.