Performance

This section compares clickhouse-driver performance over Native interface with TSV and JSONEachRow formats available over HTTP interface.

clickhouse-driver returns already parsed row items in Python data types. Driver performs all transformation for you.

When you read data over HTTP you may need to cast strings into Python types.

Test data

Sample data for testing is taken from ClickHouse docs.

Create database and table:

DROP DATABASE IF EXISTS perftest;

CREATE DATABASE perftest;

CREATE TABLE perftest.ontime (
  Year UInt16,
  Quarter UInt8,
  Month UInt8,
  DayofMonth UInt8,
  DayOfWeek UInt8,
  FlightDate Date,
  UniqueCarrier FixedString(7),
  AirlineID Int32,
  Carrier FixedString(2),
  TailNum String,
  FlightNum String,
  OriginAirportID Int32,
  OriginAirportSeqID Int32,
  OriginCityMarketID Int32,
  Origin FixedString(5),
  OriginCityName String,
  OriginState FixedString(2),
  OriginStateFips String,
  OriginStateName String,
  OriginWac Int32,
  DestAirportID Int32,
  DestAirportSeqID Int32,
  DestCityMarketID Int32,
  Dest FixedString(5),
  DestCityName String,
  DestState FixedString(2),
  DestStateFips String,
  DestStateName String,
  DestWac Int32,
  CRSDepTime Int32,
  DepTime Int32,
  DepDelay Int32,
  DepDelayMinutes Int32,
  DepDel15 Int32,
  DepartureDelayGroups String,
  DepTimeBlk String,
  TaxiOut Int32,
  WheelsOff Int32,
  WheelsOn Int32,
  TaxiIn Int32,
  CRSArrTime Int32,
  ArrTime Int32,
  ArrDelay Int32,
  ArrDelayMinutes Int32,
  ArrDel15 Int32,
  ArrivalDelayGroups Int32,
  ArrTimeBlk String,
  Cancelled UInt8,
  CancellationCode FixedString(1),
  Diverted UInt8,
  CRSElapsedTime Int32,
  ActualElapsedTime Int32,
  AirTime Int32,
  Flights Int32,
  Distance Int32,
  DistanceGroup UInt8,
  CarrierDelay Int32,
  WeatherDelay Int32,
  NASDelay Int32,
  SecurityDelay Int32,
  LateAircraftDelay Int32,
  FirstDepTime String,
  TotalAddGTime String,
  LongestAddGTime String,
  DivAirportLandings String,
  DivReachedDest String,
  DivActualElapsedTime String,
  DivArrDelay String,
  DivDistance String,
  Div1Airport String,
  Div1AirportID Int32,
  Div1AirportSeqID Int32,
  Div1WheelsOn String,
  Div1TotalGTime String,
  Div1LongestGTime String,
  Div1WheelsOff String,
  Div1TailNum String,
  Div2Airport String,
  Div2AirportID Int32,
  Div2AirportSeqID Int32,
  Div2WheelsOn String,
  Div2TotalGTime String,
  Div2LongestGTime String,
  Div2WheelsOff String,
  Div2TailNum String,
  Div3Airport String,
  Div3AirportID Int32,
  Div3AirportSeqID Int32,
  Div3WheelsOn String,
  Div3TotalGTime String,
  Div3LongestGTime String,
  Div3WheelsOff String,
  Div3TailNum String,
  Div4Airport String,
  Div4AirportID Int32,
  Div4AirportSeqID Int32,
  Div4WheelsOn String,
  Div4TotalGTime String,
  Div4LongestGTime String,
  Div4WheelsOff String,
  Div4TailNum String,
  Div5Airport String,
  Div5AirportID Int32,
  Div5AirportSeqID Int32,
  Div5WheelsOn String,
  Div5TotalGTime String,
  Div5LongestGTime String,
  Div5WheelsOff String,
  Div5TailNum String
) ENGINE = MergeTree
PARTITION BY Year
ORDER BY (Carrier, FlightDate)
SETTINGS index_granularity = 8192;

Download some data for 2017 year:

for s in `seq 2017 2017`
do
for m in `seq 1 12`
do
wget https://transtats.bts.gov/PREZIP/On_Time_Reporting_Carrier_On_Time_Performance_1987_present_${s}_${m}.zip
done
done

Insert data into ClickHouse:

for i in *.zip; do echo $i; unzip -cq $i '*.csv' | sed 's/\.00//g' | clickhouse-client --query="INSERT INTO perftest.ontime FORMAT CSVWithNames"; done

Required packages

pip install clickhouse-driver requests clickhouse-connect

For fast json parsing we’ll use ujson package:

pip install ujson

Installed packages:

$ pip freeze
certifi==2026.5.20
clickhouse-cityhash==1.0.2.5
clickhouse-connect==1.4.2
clickhouse-driver==0.2.11
lz4==4.4.5
numpy==2.4.6
pandas==3.0.3
pyarrow==25.0.0
pytz==2026.2
requests==2.34.2
tzlocal==5.3.1
ujson==5.13.0
urllib3==2.7.0
zstd==1.5.7.3

For clickhouse-connect we need to turn off compression with compress=False for elimination decompression overhead. This package also adds LIMIT clause to the query by default. Let’s disable it off with query_limit=None.

Versions

Machine: Apple M2 Pro, 32 GiB RAM, macOS 15.1.1

ClickHouse server: 25.12.11.4 Docker image, ran locally

Python: Python 3.11.11 (CPython, arm64)

Benchmarking

Let’s pick number of rows for testing with clickhouse-client.

SELECT count() FROM ontime WHERE FlightDate < '2017-01-04'

45202

SELECT count() FROM ontime WHERE FlightDate < '2017-01-10'

131848

SELECT count() FROM ontime WHERE FlightDate < '2017-01-16'

217015

SELECT count() FROM ontime WHERE FlightDate < '2017-02-01'

450017

SELECT count() FROM ontime WHERE FlightDate < '2017-02-18'

697813

Scripts below can be benchmarked with following one-liner:

for d in 2017-01-04 2017-01-10 2017-01-16 2017-02-01 2017-02-18; do python perf/script.py $d; done

Each script ends its imports with import timing: the clock starts there and the result is printed on process exit. Measured are:

• elapsed real (wall clock) time, in seconds — interpreter startup and imports are excluded;

• maximum resident set size of the process during its lifetime — includes the imported packages.

import atexit
import resource
import sys
import time

_start = time.monotonic()


@atexit.register
def _report():
    elapsed = time.monotonic() - _start
    rss = resource.getrusage(resource.RUSAGE_SELF).ru_maxrss
    if sys.platform != "darwin":
        rss *= 1024  # Linux reports kilobytes, macOS reports bytes.
    print("{:.2f} s / {} bytes max RSS".format(elapsed, rss), file=sys.stderr)

Plain text without parsing

Let’s take get plain text response from ClickHouse server as baseline.

Fetching not parsed data with pure requests (1)

# /// script
# dependencies = [
#     "requests==2.34.2",
# ]
# ///
import sys
import requests

import timing

query = "SELECT * FROM perftest.ontime WHERE FlightDate < '{}' FORMAT {}".format(sys.argv[1], sys.argv[2])
data = requests.get('http://localhost:8123/', params={'query': query})

Parsed rows

Line split into elements will be consider as “parsed” for TSV format (2)

# /// script
# dependencies = [
#     "requests==2.34.2",
# ]
# ///
import sys
import requests

import timing

query = "SELECT * FROM perftest.ontime WHERE FlightDate < '{}' FORMAT TSV".format(sys.argv[1])
resp = requests.get('http://localhost:8123/', stream=True, params={'query': query})

data = [line.decode('utf-8').split('\t') for line in resp.iter_lines(chunk_size=10000)]

Now we cast each element to it’s data type (2.5)

# /// script
# dependencies = [
#     "requests==2.34.2",
# ]
# ///
from datetime import date
import sys
import requests

import timing


def get_python_type(ch_type):
    if ch_type.startswith('Int') or ch_type.startswith('UInt'):
        return int

    elif ch_type == 'String' or ch_type.startswith('FixedString'):
        return None

    elif ch_type == 'Date':
        return lambda value: date(*[int(x) for x  in value.split('-')])

    raise ValueError(f'Unsupported type: "{ch_type}"')


resp = requests.get('http://localhost:8123', params={'query': 'describe table perftest.ontime FORMAT TSV'})
ch_types = [x.split('\t')[1] for x in resp.text.split('\n') if x]
python_types = [get_python_type(x) for x in ch_types]

query = "SELECT * FROM perftest.ontime WHERE FlightDate < '{}' FORMAT TSV".format(sys.argv[1])
resp = requests.get('http://localhost:8123/', stream=True, params={'query': query})

data = []

for line in resp.iter_lines(chunk_size=10000):
    data.append([cls(x) if cls else x for x, cls in zip(line.decode('utf-8').split('\t'), python_types)])

JSONEachRow format can be loaded with json loads (3)

# /// script
# dependencies = [
#     "requests==2.34.2",
#     "ujson==5.13.0",
# ]
# ///
import sys
import requests
from ujson import loads

import timing

query = "SELECT * FROM perftest.ontime WHERE FlightDate < '{}' FORMAT JSONEachRow".format(sys.argv[1])
resp = requests.get('http://localhost:8123/', stream=True, params={'query': query})

data = [list(loads(line).values()) for line in resp.iter_lines(chunk_size=10000)]

Get fully parsed rows with clickhouse-driver in Native format (4)

# /// script
# dependencies = [
#     "clickhouse-driver==0.2.11",
# ]
# ///
import sys
from clickhouse_driver import Client

import timing

query = "SELECT * FROM perftest.ontime WHERE FlightDate < '{}'".format(sys.argv[1])
client = Client.from_url('clickhouse://localhost')

data = client.execute(query)

Get fully parsed rows with clickhouse-connect (14)

# /// script
# dependencies = [
#     "clickhouse-connect==1.4.2",
# ]
# ///
import sys
import clickhouse_connect

import timing

query = "SELECT * FROM perftest.ontime WHERE FlightDate < '{}'".format(sys.argv[1])
client = clickhouse_connect.get_client(host='localhost', query_limit=None, compress=False)

data = client.query(query).result_rows

Iteration over rows

Iteration over TSV (5)

# /// script
# dependencies = [
#     "requests==2.34.2",
# ]
# ///
import sys
import requests

import timing

query = "SELECT * FROM perftest.ontime WHERE FlightDate < '{}' FORMAT TSV".format(sys.argv[1])
resp = requests.get('http://localhost:8123/', stream=True, params={'query': query})

for line in resp.iter_lines(chunk_size=10000):
    line = line.decode('utf-8').split('\t')

Now we cast each element to it’s data type (5.5)

# /// script
# dependencies = [
#     "requests==2.34.2",
# ]
# ///
from datetime import date
import sys
import requests

import timing


def get_python_type(ch_type):
    if ch_type.startswith('Int') or ch_type.startswith('UInt'):
        return int

    elif ch_type == 'String' or ch_type.startswith('FixedString'):
        return None

    elif ch_type == 'Date':
        return lambda value: date(*[int(x) for x  in value.split('-')])

    raise ValueError(f'Unsupported type: "{ch_type}"')


resp = requests.get('http://localhost:8123', params={'query': 'describe table perftest.ontime FORMAT TSV'})
ch_types = [x.split('\t')[1] for x in resp.text.split('\n') if x]
python_types = [get_python_type(x) for x in ch_types]

query = "SELECT * FROM perftest.ontime WHERE FlightDate < '{}' FORMAT TSV".format(sys.argv[1])
resp = requests.get('http://localhost:8123/', stream=True, params={'query': query})

for line in resp.iter_lines(chunk_size=10000):
    line = [cls(x) if cls else x for x, cls in zip(line.decode('utf-8').split('\t'), python_types)]

Iteration over JSONEachRow (6)

# /// script
# dependencies = [
#     "requests==2.34.2",
#     "ujson==5.13.0",
# ]
# ///
import sys
import requests
from ujson import loads

import timing

query = "SELECT * FROM perftest.ontime WHERE FlightDate < '{}' FORMAT JSONEachRow".format(sys.argv[1])
resp = requests.get('http://localhost:8123/', stream=True, params={'query': query})

for line in resp.iter_lines(chunk_size=10000):
  line = list(loads(line).values())

Iteration over rows with clickhouse-driver in Native format (7)

# /// script
# dependencies = [
#     "clickhouse-driver==0.2.11",
# ]
# ///
import sys
from clickhouse_driver import Client

import timing

query = "SELECT * FROM perftest.ontime WHERE FlightDate < '{}'".format(sys.argv[1])
client = Client.from_url('clickhouse://localhost')

for row in client.execute_iter(query):
  pass

Iteration over rows with clickhouse-connect (17)

# /// script
# dependencies = [
#     "clickhouse-connect==1.4.2",
# ]
# ///
import sys
import clickhouse_connect

import timing

query = "SELECT * FROM perftest.ontime WHERE FlightDate < '{}'".format(sys.argv[1])
client = clickhouse_connect.get_client(host='localhost', query_limit=None, compress=False)

with client.query_rows_stream(query) as stream:
    for row in stream:
        pass

Iteration over string rows

OK, but what if we need only string columns?

Iteration over TSV (8)

# /// script
# dependencies = [
#     "requests==2.34.2",
# ]
# ///
import sys
import requests

import timing

cols = [
    'UniqueCarrier', 'Carrier', 'TailNum', 'FlightNum', 'Origin', 'OriginCityName', 'OriginState',
    'OriginStateFips', 'OriginStateName', 'Dest', 'DestCityName', 'DestState', 'DestStateFips',
    'DestStateName', 'DepartureDelayGroups', 'DepTimeBlk', 'ArrTimeBlk', 'CancellationCode',
    'FirstDepTime', 'TotalAddGTime', 'LongestAddGTime', 'DivAirportLandings', 'DivReachedDest',
    'DivActualElapsedTime', 'DivArrDelay', 'DivDistance', 'Div1Airport', 'Div1WheelsOn', 'Div1TotalGTime',
    'Div1LongestGTime', 'Div1WheelsOff', 'Div1TailNum', 'Div2Airport', 'Div2WheelsOn', 'Div2TotalGTime',
    'Div2LongestGTime', 'Div2WheelsOff', 'Div2TailNum', 'Div3Airport', 'Div3WheelsOn', 'Div3TotalGTime',
    'Div3LongestGTime', 'Div3WheelsOff', 'Div3TailNum', 'Div4Airport', 'Div4WheelsOn', 'Div4TotalGTime',
    'Div4LongestGTime', 'Div4WheelsOff', 'Div4TailNum', 'Div5Airport', 'Div5WheelsOn', 'Div5TotalGTime',
    'Div5LongestGTime', 'Div5WheelsOff', 'Div5TailNum'
]

query = "SELECT {} FROM perftest.ontime WHERE FlightDate < '{}' FORMAT TSV".format(', '.join(cols), sys.argv[1])
resp = requests.get('http://localhost:8123/', stream=True, params={'query': query})

for line in resp.iter_lines(chunk_size=10000):
    line = line.decode('utf-8').split('\t')

Iteration over JSONEachRow (9)

# /// script
# dependencies = [
#     "requests==2.34.2",
#     "ujson==5.13.0",
# ]
# ///
import sys
import requests
from ujson import loads

import timing

cols = [
    'UniqueCarrier', 'Carrier', 'TailNum', 'FlightNum', 'Origin', 'OriginCityName', 'OriginState',
    'OriginStateFips', 'OriginStateName', 'Dest', 'DestCityName', 'DestState', 'DestStateFips',
    'DestStateName', 'DepartureDelayGroups', 'DepTimeBlk', 'ArrTimeBlk', 'CancellationCode',
    'FirstDepTime', 'TotalAddGTime', 'LongestAddGTime', 'DivAirportLandings', 'DivReachedDest',
    'DivActualElapsedTime', 'DivArrDelay', 'DivDistance', 'Div1Airport', 'Div1WheelsOn', 'Div1TotalGTime',
    'Div1LongestGTime', 'Div1WheelsOff', 'Div1TailNum', 'Div2Airport', 'Div2WheelsOn', 'Div2TotalGTime',
    'Div2LongestGTime', 'Div2WheelsOff', 'Div2TailNum', 'Div3Airport', 'Div3WheelsOn', 'Div3TotalGTime',
    'Div3LongestGTime', 'Div3WheelsOff', 'Div3TailNum', 'Div4Airport', 'Div4WheelsOn', 'Div4TotalGTime',
    'Div4LongestGTime', 'Div4WheelsOff', 'Div4TailNum', 'Div5Airport', 'Div5WheelsOn', 'Div5TotalGTime',
    'Div5LongestGTime', 'Div5WheelsOff', 'Div5TailNum'
]

query = "SELECT {} FROM perftest.ontime WHERE FlightDate < '{}' FORMAT JSONEachRow".format(', '.join(cols), sys.argv[1])
resp = requests.get('http://localhost:8123/', stream=True, params={'query': query})

for line in resp.iter_lines(chunk_size=10000):
    line = list(loads(line).values())

Iteration over string rows with clickhouse-driver in Native format (10)

# /// script
# dependencies = [
#     "clickhouse-driver==0.2.11",
# ]
# ///
import sys
from clickhouse_driver import Client

import timing

cols = [
    'UniqueCarrier', 'Carrier', 'TailNum', 'FlightNum', 'Origin', 'OriginCityName', 'OriginState',
    'OriginStateFips', 'OriginStateName', 'Dest', 'DestCityName', 'DestState', 'DestStateFips',
    'DestStateName', 'DepartureDelayGroups', 'DepTimeBlk', 'ArrTimeBlk', 'CancellationCode',
    'FirstDepTime', 'TotalAddGTime', 'LongestAddGTime', 'DivAirportLandings', 'DivReachedDest',
    'DivActualElapsedTime', 'DivArrDelay', 'DivDistance', 'Div1Airport', 'Div1WheelsOn', 'Div1TotalGTime',
    'Div1LongestGTime', 'Div1WheelsOff', 'Div1TailNum', 'Div2Airport', 'Div2WheelsOn', 'Div2TotalGTime',
    'Div2LongestGTime', 'Div2WheelsOff', 'Div2TailNum', 'Div3Airport', 'Div3WheelsOn', 'Div3TotalGTime',
    'Div3LongestGTime', 'Div3WheelsOff', 'Div3TailNum', 'Div4Airport', 'Div4WheelsOn', 'Div4TotalGTime',
    'Div4LongestGTime', 'Div4WheelsOff', 'Div4TailNum', 'Div5Airport', 'Div5WheelsOn', 'Div5TotalGTime',
    'Div5LongestGTime', 'Div5WheelsOff', 'Div5TailNum'
]

query = "SELECT {} FROM perftest.ontime WHERE FlightDate < '{}'".format(', '.join(cols), sys.argv[1])
client = Client.from_url('clickhouse://localhost')

for row in client.execute_iter(query):
  pass

Iteration over string rows with clickhouse-connect (15)

# /// script
# dependencies = [
#     "clickhouse-connect==1.4.2",
# ]
# ///
import sys
import clickhouse_connect

import timing

cols = [
    'UniqueCarrier', 'Carrier', 'TailNum', 'FlightNum', 'Origin', 'OriginCityName', 'OriginState',
    'OriginStateFips', 'OriginStateName', 'Dest', 'DestCityName', 'DestState', 'DestStateFips',
    'DestStateName', 'DepartureDelayGroups', 'DepTimeBlk', 'ArrTimeBlk', 'CancellationCode',
    'FirstDepTime', 'TotalAddGTime', 'LongestAddGTime', 'DivAirportLandings', 'DivReachedDest',
    'DivActualElapsedTime', 'DivArrDelay', 'DivDistance', 'Div1Airport', 'Div1WheelsOn', 'Div1TotalGTime',
    'Div1LongestGTime', 'Div1WheelsOff', 'Div1TailNum', 'Div2Airport', 'Div2WheelsOn', 'Div2TotalGTime',
    'Div2LongestGTime', 'Div2WheelsOff', 'Div2TailNum', 'Div3Airport', 'Div3WheelsOn', 'Div3TotalGTime',
    'Div3LongestGTime', 'Div3WheelsOff', 'Div3TailNum', 'Div4Airport', 'Div4WheelsOn', 'Div4TotalGTime',
    'Div4LongestGTime', 'Div4WheelsOff', 'Div4TailNum', 'Div5Airport', 'Div5WheelsOn', 'Div5TotalGTime',
    'Div5LongestGTime', 'Div5WheelsOff', 'Div5TailNum'
]

query = "SELECT {} FROM perftest.ontime WHERE FlightDate < '{}'".format(', '.join(cols), sys.argv[1])
client = clickhouse_connect.get_client(host='localhost', query_limit=None, compress=False)

with client.query_rows_stream(query) as stream:
    for row in stream:
        pass

Iteration over int rows

Iteration over TSV (11)

# /// script
# dependencies = [
#     "requests==2.34.2",
# ]
# ///
import sys
import requests

import timing

cols = [
    'Year', 'Quarter', 'Month', 'DayofMonth', 'DayOfWeek', 'AirlineID', 'OriginAirportID', 'OriginAirportSeqID',
    'OriginCityMarketID', 'OriginWac', 'DestAirportID', 'DestAirportSeqID', 'DestCityMarketID', 'DestWac',
    'CRSDepTime', 'DepTime', 'DepDelay', 'DepDelayMinutes', 'DepDel15', 'TaxiOut', 'WheelsOff', 'WheelsOn',
    'TaxiIn', 'CRSArrTime', 'ArrTime', 'ArrDelay', 'ArrDelayMinutes', 'ArrDel15', 'ArrivalDelayGroups',
    'Cancelled', 'Diverted', 'CRSElapsedTime', 'ActualElapsedTime', 'AirTime', 'Flights', 'Distance',
    'DistanceGroup', 'CarrierDelay', 'WeatherDelay', 'NASDelay', 'SecurityDelay', 'LateAircraftDelay',
    'Div1AirportID', 'Div1AirportSeqID', 'Div2AirportID', 'Div2AirportSeqID', 'Div3AirportID',
    'Div3AirportSeqID', 'Div4AirportID', 'Div4AirportSeqID', 'Div5AirportID', 'Div5AirportSeqID'
]

query = "SELECT {} FROM perftest.ontime WHERE FlightDate < '{}' FORMAT TSV".format(', '.join(cols), sys.argv[1])
resp = requests.get('http://localhost:8123/', stream=True, params={'query': query})

for line in resp.iter_lines(chunk_size=10000):
    line = [int(x) for x in line.split(b'\t')]

Iteration over JSONEachRow (12)

# /// script
# dependencies = [
#     "requests==2.34.2",
#     "ujson==5.13.0",
# ]
# ///
import sys
import requests
from ujson import loads

import timing

cols = [
    'Year', 'Quarter', 'Month', 'DayofMonth', 'DayOfWeek', 'AirlineID', 'OriginAirportID', 'OriginAirportSeqID',
    'OriginCityMarketID', 'OriginWac', 'DestAirportID', 'DestAirportSeqID', 'DestCityMarketID', 'DestWac',
    'CRSDepTime', 'DepTime', 'DepDelay', 'DepDelayMinutes', 'DepDel15', 'TaxiOut', 'WheelsOff', 'WheelsOn',
    'TaxiIn', 'CRSArrTime', 'ArrTime', 'ArrDelay', 'ArrDelayMinutes', 'ArrDel15', 'ArrivalDelayGroups',
    'Cancelled', 'Diverted', 'CRSElapsedTime', 'ActualElapsedTime', 'AirTime', 'Flights', 'Distance',
    'DistanceGroup', 'CarrierDelay', 'WeatherDelay', 'NASDelay', 'SecurityDelay', 'LateAircraftDelay',
    'Div1AirportID', 'Div1AirportSeqID', 'Div2AirportID', 'Div2AirportSeqID', 'Div3AirportID',
    'Div3AirportSeqID', 'Div4AirportID', 'Div4AirportSeqID', 'Div5AirportID', 'Div5AirportSeqID'
]

query = "SELECT {} FROM perftest.ontime WHERE FlightDate < '{}' FORMAT JSONEachRow".format(', '.join(cols), sys.argv[1])
resp = requests.get('http://localhost:8123/', stream=True, params={'query': query})

for line in resp.iter_lines(chunk_size=10000):
    line = list(loads(line).values())

Iteration over int rows with clickhouse-driver in Native format (13)

# /// script
# dependencies = [
#     "clickhouse-driver==0.2.11",
# ]
# ///
import sys
from clickhouse_driver import Client

import timing

cols = [
    'Year', 'Quarter', 'Month', 'DayofMonth', 'DayOfWeek', 'AirlineID', 'OriginAirportID', 'OriginAirportSeqID',
    'OriginCityMarketID', 'OriginWac', 'DestAirportID', 'DestAirportSeqID', 'DestCityMarketID', 'DestWac',
    'CRSDepTime', 'DepTime', 'DepDelay', 'DepDelayMinutes', 'DepDel15', 'TaxiOut', 'WheelsOff', 'WheelsOn',
    'TaxiIn', 'CRSArrTime', 'ArrTime', 'ArrDelay', 'ArrDelayMinutes', 'ArrDel15', 'ArrivalDelayGroups',
    'Cancelled', 'Diverted', 'CRSElapsedTime', 'ActualElapsedTime', 'AirTime', 'Flights', 'Distance',
    'DistanceGroup', 'CarrierDelay', 'WeatherDelay', 'NASDelay', 'SecurityDelay', 'LateAircraftDelay',
    'Div1AirportID', 'Div1AirportSeqID', 'Div2AirportID', 'Div2AirportSeqID', 'Div3AirportID',
    'Div3AirportSeqID', 'Div4AirportID', 'Div4AirportSeqID', 'Div5AirportID', 'Div5AirportSeqID'
]

query = "SELECT {} FROM perftest.ontime WHERE FlightDate < '{}'".format(', '.join(cols), sys.argv[1])
client = Client.from_url('clickhouse://localhost')

for row in client.execute_iter(query):
    pass

Iteration over int rows with clickhouse-connect (16)

# /// script
# dependencies = [
#     "clickhouse-connect==1.4.2",
# ]
# ///
import sys
import clickhouse_connect

import timing

cols = [
    'Year', 'Quarter', 'Month', 'DayofMonth', 'DayOfWeek', 'AirlineID', 'OriginAirportID', 'OriginAirportSeqID',
    'OriginCityMarketID', 'OriginWac', 'DestAirportID', 'DestAirportSeqID', 'DestCityMarketID', 'DestWac',
    'CRSDepTime', 'DepTime', 'DepDelay', 'DepDelayMinutes', 'DepDel15', 'TaxiOut', 'WheelsOff', 'WheelsOn',
    'TaxiIn', 'CRSArrTime', 'ArrTime', 'ArrDelay', 'ArrDelayMinutes', 'ArrDel15', 'ArrivalDelayGroups',
    'Cancelled', 'Diverted', 'CRSElapsedTime', 'ActualElapsedTime', 'AirTime', 'Flights', 'Distance',
    'DistanceGroup', 'CarrierDelay', 'WeatherDelay', 'NASDelay', 'SecurityDelay', 'LateAircraftDelay',
    'Div1AirportID', 'Div1AirportSeqID', 'Div2AirportID', 'Div2AirportSeqID', 'Div3AirportID',
    'Div3AirportSeqID', 'Div4AirportID', 'Div4AirportSeqID', 'Div5AirportID', 'Div5AirportSeqID'
]

query = "SELECT {} FROM perftest.ontime WHERE FlightDate < '{}'".format(', '.join(cols), sys.argv[1])
client = clickhouse_connect.get_client(host='localhost', query_limit=None, compress=False)

with client.query_rows_stream(query) as stream:
    for row in stream:
        pass

PyArrow tables

New in version 0.2.11.

Get query result as PyArrow Table with clickhouse-driver in Native format (18)

# /// script
# dependencies = [
#     "clickhouse-driver[arrow]==0.2.11",
# ]
# ///
import sys
from clickhouse_driver import Client

# Imported lazily at query time: preload before the clock starts.
import pyarrow

import timing

query = "SELECT * FROM perftest.ontime WHERE FlightDate < '{}'".format(sys.argv[1])
client = Client('localhost')

table = client.query_arrow(query)

The same with NumPy fast paths enabled (19)

# /// script
# dependencies = [
#     "clickhouse-driver[arrow,numpy]==0.2.11",
# ]
# ///
import sys
from clickhouse_driver import Client

# Imported lazily at query time: preload before the clock starts.
import numpy
import pandas
import pyarrow

import timing

query = "SELECT * FROM perftest.ontime WHERE FlightDate < '{}'".format(sys.argv[1])
client = Client('localhost', settings={'use_numpy': True})

table = client.query_arrow(query)

Get query result as PyArrow Table with clickhouse-connect (20)

# /// script
# dependencies = [
#     "clickhouse-connect==1.4.2",
#     "pyarrow==25.0.0",
# ]
# ///
import sys
import clickhouse_connect

# Imported lazily at query time: preload before the clock starts.
import pyarrow

import timing

query = "SELECT * FROM perftest.ontime WHERE FlightDate < '{}'".format(sys.argv[1])
client = clickhouse_connect.get_client(host='localhost', query_limit=None, compress=False)

table = client.query_arrow(query, settings={'output_format_arrow_string_as_string': 1})

PyArrow tables over typed columns

Analytic extracts usually select a typed subset of columns instead of SELECT *. The same int and string column projections as in the iteration sections above, as PyArrow tables.

Int columns with clickhouse-driver, use_numpy (21)

# /// script
# dependencies = [
#     "clickhouse-driver[arrow,numpy]==0.2.11",
# ]
# ///
import sys
from clickhouse_driver import Client

# Imported lazily at query time: preload before the clock starts.
import numpy
import pandas
import pyarrow

import timing

cols = [
    'Year', 'Quarter', 'Month', 'DayofMonth', 'DayOfWeek', 'AirlineID', 'OriginAirportID', 'OriginAirportSeqID',
    'OriginCityMarketID', 'OriginWac', 'DestAirportID', 'DestAirportSeqID', 'DestCityMarketID', 'DestWac',
    'CRSDepTime', 'DepTime', 'DepDelay', 'DepDelayMinutes', 'DepDel15', 'TaxiOut', 'WheelsOff', 'WheelsOn',
    'TaxiIn', 'CRSArrTime', 'ArrTime', 'ArrDelay', 'ArrDelayMinutes', 'ArrDel15', 'ArrivalDelayGroups',
    'Cancelled', 'Diverted', 'CRSElapsedTime', 'ActualElapsedTime', 'AirTime', 'Flights', 'Distance',
    'DistanceGroup', 'CarrierDelay', 'WeatherDelay', 'NASDelay', 'SecurityDelay', 'LateAircraftDelay',
    'Div1AirportID', 'Div1AirportSeqID', 'Div2AirportID', 'Div2AirportSeqID', 'Div3AirportID',
    'Div3AirportSeqID', 'Div4AirportID', 'Div4AirportSeqID', 'Div5AirportID', 'Div5AirportSeqID'
]

query = "SELECT {} FROM perftest.ontime WHERE FlightDate < '{}'".format(', '.join(cols), sys.argv[1])
client = Client('localhost', settings={'use_numpy': True})

table = client.query_arrow(query)

Int columns with clickhouse-connect (22)

# /// script
# dependencies = [
#     "clickhouse-connect==1.4.2",
#     "pyarrow==25.0.0",
# ]
# ///
import sys
import clickhouse_connect

# Imported lazily at query time: preload before the clock starts.
import pyarrow

import timing

cols = [
    'Year', 'Quarter', 'Month', 'DayofMonth', 'DayOfWeek', 'AirlineID', 'OriginAirportID', 'OriginAirportSeqID',
    'OriginCityMarketID', 'OriginWac', 'DestAirportID', 'DestAirportSeqID', 'DestCityMarketID', 'DestWac',
    'CRSDepTime', 'DepTime', 'DepDelay', 'DepDelayMinutes', 'DepDel15', 'TaxiOut', 'WheelsOff', 'WheelsOn',
    'TaxiIn', 'CRSArrTime', 'ArrTime', 'ArrDelay', 'ArrDelayMinutes', 'ArrDel15', 'ArrivalDelayGroups',
    'Cancelled', 'Diverted', 'CRSElapsedTime', 'ActualElapsedTime', 'AirTime', 'Flights', 'Distance',
    'DistanceGroup', 'CarrierDelay', 'WeatherDelay', 'NASDelay', 'SecurityDelay', 'LateAircraftDelay',
    'Div1AirportID', 'Div1AirportSeqID', 'Div2AirportID', 'Div2AirportSeqID', 'Div3AirportID',
    'Div3AirportSeqID', 'Div4AirportID', 'Div4AirportSeqID', 'Div5AirportID', 'Div5AirportSeqID'
]

query = "SELECT {} FROM perftest.ontime WHERE FlightDate < '{}'".format(', '.join(cols), sys.argv[1])
client = clickhouse_connect.get_client(host='localhost', query_limit=None, compress=False)

table = client.query_arrow(query, settings={'output_format_arrow_string_as_string': 1})

String columns with clickhouse-driver, use_numpy (23)

# /// script
# dependencies = [
#     "clickhouse-driver[arrow,numpy]==0.2.11",
# ]
# ///
import sys
from clickhouse_driver import Client

# Imported lazily at query time: preload before the clock starts.
import numpy
import pandas
import pyarrow

import timing

cols = [
    'UniqueCarrier', 'Carrier', 'TailNum', 'FlightNum', 'Origin', 'OriginCityName', 'OriginState',
    'OriginStateFips', 'OriginStateName', 'Dest', 'DestCityName', 'DestState', 'DestStateFips',
    'DestStateName', 'DepartureDelayGroups', 'DepTimeBlk', 'ArrTimeBlk', 'CancellationCode',
    'FirstDepTime', 'TotalAddGTime', 'LongestAddGTime', 'DivAirportLandings', 'DivReachedDest',
    'DivActualElapsedTime', 'DivArrDelay', 'DivDistance', 'Div1Airport', 'Div1WheelsOn', 'Div1TotalGTime',
    'Div1LongestGTime', 'Div1WheelsOff', 'Div1TailNum', 'Div2Airport', 'Div2WheelsOn', 'Div2TotalGTime',
    'Div2LongestGTime', 'Div2WheelsOff', 'Div2TailNum', 'Div3Airport', 'Div3WheelsOn', 'Div3TotalGTime',
    'Div3LongestGTime', 'Div3WheelsOff', 'Div3TailNum', 'Div4Airport', 'Div4WheelsOn', 'Div4TotalGTime',
    'Div4LongestGTime', 'Div4WheelsOff', 'Div4TailNum', 'Div5Airport', 'Div5WheelsOn', 'Div5TotalGTime',
    'Div5LongestGTime', 'Div5WheelsOff', 'Div5TailNum'
]

query = "SELECT {} FROM perftest.ontime WHERE FlightDate < '{}'".format(', '.join(cols), sys.argv[1])
client = Client('localhost', settings={'use_numpy': True})

table = client.query_arrow(query)

String columns with clickhouse-connect (24)

# /// script
# dependencies = [
#     "clickhouse-connect==1.4.2",
#     "pyarrow==25.0.0",
# ]
# ///
import sys
import clickhouse_connect

# Imported lazily at query time: preload before the clock starts.
import pyarrow

import timing

cols = [
    'UniqueCarrier', 'Carrier', 'TailNum', 'FlightNum', 'Origin', 'OriginCityName', 'OriginState',
    'OriginStateFips', 'OriginStateName', 'Dest', 'DestCityName', 'DestState', 'DestStateFips',
    'DestStateName', 'DepartureDelayGroups', 'DepTimeBlk', 'ArrTimeBlk', 'CancellationCode',
    'FirstDepTime', 'TotalAddGTime', 'LongestAddGTime', 'DivAirportLandings', 'DivReachedDest',
    'DivActualElapsedTime', 'DivArrDelay', 'DivDistance', 'Div1Airport', 'Div1WheelsOn', 'Div1TotalGTime',
    'Div1LongestGTime', 'Div1WheelsOff', 'Div1TailNum', 'Div2Airport', 'Div2WheelsOn', 'Div2TotalGTime',
    'Div2LongestGTime', 'Div2WheelsOff', 'Div2TailNum', 'Div3Airport', 'Div3WheelsOn', 'Div3TotalGTime',
    'Div3LongestGTime', 'Div3WheelsOff', 'Div3TailNum', 'Div4Airport', 'Div4WheelsOn', 'Div4TotalGTime',
    'Div4LongestGTime', 'Div4WheelsOff', 'Div4TailNum', 'Div5Airport', 'Div5WheelsOn', 'Div5TotalGTime',
    'Div5LongestGTime', 'Div5WheelsOff', 'Div5TailNum'
]

query = "SELECT {} FROM perftest.ontime WHERE FlightDate < '{}'".format(', '.join(cols), sys.argv[1])
client = clickhouse_connect.get_client(host='localhost', query_limit=None, compress=False)

table = client.query_arrow(query, settings={'output_format_arrow_string_as_string': 1})

Results

This table contains memory and timing benchmark results of snippets above.

JSON in table is shorthand for JSONEachRow.

	Rows
	50k	131k	217k	450k	697k
Plain text without parsing: timing
Naive requests.get TSV (1)	0.13 s	0.28 s	0.45 s	0.80 s	1.26 s
Naive requests.get JSON (1)	0.38 s	1.07 s	1.81 s	3.95 s	7.48 s
Plain text without parsing: memory
Naive requests.get TSV (1)	61 MB	118 MB	175 MB	331 MB	497 MB
Naive requests.get JSON (1)	221 MB	587 MB	935 MB	1.48 GB	2.84 GB
Parsed rows: timing
requests.get TSV (2)	0.25 s	0.64 s	1.31 s	3.81 s	6.95 s
requests.get TSV with cast (2.5)	0.67 s	1.69 s	3.07 s	7.29 s	12.35 s
requests.get JSON (3)	0.82 s	2.53 s	3.67 s	8.05 s	15.04 s
clickhouse-driver Native (4)	0.18 s	0.44 s	0.67 s	1.46 s	2.28 s
clickhouse-connect (14)	0.20 s	0.50 s	0.83 s	1.70 s	2.71 s
Parsed rows: memory
requests.get TSV (2)	183 MB	473 MB	765 MB	1.52 GB	2.33 GB
requests.get TSV with cast (2.5)	142 MB	358 MB	536 MB	1.12 GB	1.73 GB
requests.get JSON (3)	136 MB	303 MB	532 MB	1.05 GB	1.61 GB
clickhouse-driver Native (4)	167 MB	372 MB	548 MB	1.03 GB	1.47 GB
clickhouse-connect (14)	172 MB	384 MB	577 MB	1.01 GB	1.21 GB
Iteration over rows: timing
requests.get TSV (5)	0.22 s	0.54 s	0.80 s	1.42 s	2.12 s
requests.get TSV with cast (5.5)	0.52 s	1.42 s	2.26 s	4.59 s	7.34 s
requests.get JSON (6)	0.72 s	1.97 s	3.13 s	9.36 s	10.45 s
clickhouse-driver Native (7)	0.23 s	0.56 s	0.94 s	1.98 s	2.70 s
clickhouse-connect (17)	0.23 s	0.50 s	0.78 s	1.61 s	2.50 s
Iteration over rows: memory
requests.get TSV (5)	31 MB	31 MB	31 MB	31 MB	31 MB
requests.get TSV with cast (5.5)	30 MB	31 MB	31 MB	31 MB	31 MB
requests.get JSON (6)	30 MB	32 MB	31 MB	30 MB	32 MB
clickhouse-driver Native (7)	111 MB	136 MB	135 MB	148 MB	156 MB
clickhouse-connect (17)	117 MB	136 MB	146 MB	162 MB	164 MB
Iteration over string rows: timing
requests.get TSV (8)	0.11 s	0.25 s	0.36 s	0.68 s	1.17 s
requests.get JSON (9)	0.44 s	1.19 s	1.95 s	3.35 s	5.08 s
clickhouse-driver Native (10)	0.11 s	0.26 s	0.41 s	0.82 s	1.33 s
clickhouse-connect (15)	0.14 s	0.34 s	0.55 s	1.23 s	1.67 s
Iteration over string rows: memory
requests.get TSV (8)	31 MB	30 MB	30 MB	31 MB	31 MB
requests.get JSON (9)	31 MB	31 MB	32 MB	31 MB	31 MB
clickhouse-driver Native (10)	78 MB	85 MB	84 MB	94 MB	98 MB
clickhouse-connect (15)	79 MB	104 MB	112 MB	125 MB	127 MB
Iteration over int rows: timing
requests.get TSV (11)	0.27 s	0.69 s	1.08 s	2.20 s	3.40 s
requests.get JSON (12)	0.37 s	1.00 s	1.49 s	2.86 s	4.51 s
clickhouse-driver Native (13)	0.13 s	0.20 s	0.31 s	0.62 s	1.00 s
clickhouse-connect (16)	0.08 s	0.15 s	0.23 s	0.47 s	0.67 s
Iteration over int rows: memory
requests.get TSV (11)	30 MB	31 MB	30 MB	31 MB	31 MB
requests.get JSON (12)	31 MB	31 MB	31 MB	32 MB	31 MB
clickhouse-driver Native (13)	79 MB	80 MB	85 MB	89 MB	103 MB
clickhouse-connect (16)	62 MB	81 MB	93 MB	105 MB	99 MB
PyArrow tables: timing
clickhouse-driver Native (18)	0.51 s	0.72 s	0.93 s	2.08 s	2.96 s
clickhouse-driver use_numpy (19)	0.13 s	0.34 s	0.48 s	0.93 s	1.41 s
clickhouse-connect (20)	0.13 s	0.25 s	0.40 s	0.87 s	1.28 s
PyArrow tables: memory
clickhouse-driver Native (18)	181 MB	272 MB	303 MB	446 MB	600 MB
clickhouse-driver use_numpy (19)	167 MB	224 MB	291 MB	464 MB	645 MB
clickhouse-connect (20)	97 MB	170 MB	240 MB	435 MB	639 MB
PyArrow tables, int columns: timing
clickhouse-driver use_numpy (21)	0.04 s	0.08 s	0.12 s	0.21 s	0.32 s
clickhouse-connect (22)	0.05 s	0.09 s	0.14 s	0.27 s	0.39 s
PyArrow tables, int columns: memory
clickhouse-driver use_numpy (21)	130 MB	153 MB	176 MB	243 MB	310 MB
clickhouse-connect (22)	74 MB	106 MB	136 MB	218 MB	306 MB
PyArrow tables, string columns: timing
clickhouse-driver use_numpy (23)	0.09 s	0.22 s	0.33 s	0.83 s	1.06 s
clickhouse-connect (24)	0.09 s	0.20 s	0.29 s	0.59 s	0.92 s
PyArrow tables, string columns: memory
clickhouse-driver use_numpy (23)	152 MB	191 MB	226 MB	266 MB	434 MB
clickhouse-connect (24)	81 MB	121 MB	162 MB	272 MB	390 MB

Conclusion

If you need to get significant number of rows from ClickHouse server as text then TSV format is your choice. See Iteration over string rows results.

But if you need to manipulate over python data types then you should take a look on drivers with Native format. For most data types driver uses binary pack() / unpack() for serialization / deserialization. Which is obviously faster than cls() for x in lst. See (2.5) and (5.5).

It doesn’t matter which interface to use if you manipulate small amount of rows.