out-of-memoryclickhouse

Clickhouse-driver Chunking

I use clickhouse-driver to create a pandas dataframe from an SQL query with the following command:

data = client.query_dataframe('CUSTOM SQL QUERY')

But I receive a memory error:

ServerException: Code: 241. DB::Exception: Memory limit (for query) exceeded: would use 74.51 GiB (attempt to allocate chunk of 4531920 bytes), maximum: 74.51 GiB. OvercommitTracker decision: Memory overcommit isn't used. OvercommitTracker isn't set.. Stack trace:

Is there a way to chunk a query? I haven't found a solution in the documentation.

Solution

  • Error is not related to clickhouse client library

    Check your SQL query, if it contains GROUP BY and ORDER BY and sub-queries try to tune max_bytes_before_external_group_by https://clickhouse.com/docs/en/operations/settings/query-complexity#settings-max_bytes_before_external_group_by

    and

    max_bytes_before_external_sort

    https://clickhouse.com/docs/en/operations/settings/query-complexity#settings-max_bytes_before_external_sort

    also look to https://azat.sh/presentations/2022-know-your-clickhouse/#/40