Advanced inserting

Inserting data with ClickHouse Connect: Advanced usage

InsertContexts

ClickHouse Connect executes Native-format inserts, the insert and insert_df methods, within an InsertContext. The insert_arrow, insert_df_arrow, and raw_insert methods send their payloads directly and don’t use one. The InsertContext includes all the values sent as arguments to the client insert method. In addition, when an InsertContext is originally constructed, ClickHouse Connect retrieves the data types for the insert columns required for efficient Native format inserts. By reusing the InsertContext for multiple inserts, this “pre-query” is avoided and inserts are executed more quickly and efficiently. An InsertContext can be acquired using the client create_insert_context method. The method takes the same arguments as the insert function, except for context itself. Note that only the data property of InsertContexts should be modified for reuse. This is consistent with its intended purpose of providing a reusable object for repeated inserts of new data to the same table.

test_data = [[13, "v1", "v2"], [79, "v3", "v4"]]
ic = client.create_insert_context(table="test_table", data=test_data)
client.insert(context=ic)
assert client.command("SELECT count() FROM test_table") == 2

new_data = [[101, "v5", "v6"], [113, "v7", "v8"]]
ic.data = new_data
client.insert(context=ic)
qr = client.query("SELECT * FROM test_table ORDER BY key DESC")
assert qr.row_count == 4
assert qr.first_row[0] == 113

InsertContexts include mutable state that is updated during the insert process, so they’re not thread safe.

Write formats

Write formats are implemented for a limited number of types. In most cases ClickHouse Connect automatically determines the correct write format for a column from its first non-null data value. For example, when the first value for a DateTime column is an integer, the client treats it as an epoch second. It is normally unnecessary to override a write format, but the methods in clickhouse_connect.datatypes.format can set one globally. Container wrappers such as Array, Nullable, and LowCardinality preserve the element type’s formatting behavior.

Write format options

ClickHouse Type	Native Python Type	Write Formats	Comments
Int[8-64], UInt[8-32]	int
UInt64	int
[U]Int[128,256]	int
BFloat16	float
Float32	float
Float64	float
Decimal	decimal.Decimal
String	str or bytes		A column must consistently contain text or bytes.
FixedString	bytes	string	If inserted as a string, additional bytes will be set to zeros
Enum[8,16]	str or int		Insert labels as strings or their underlying integer values.
Date	datetime.date	int	Integer values are interpreted as days since 1970-01-01.
Date32	datetime.date	int	Integer values are interpreted as signed day offsets.
DateTime	datetime.datetime	int	Integer values are interpreted as epoch seconds.
DateTime64	datetime.datetime	int	Integer values are interpreted as ticks at the column precision.
Time	datetime.timedelta	int, string, time	Integer values are interpreted as seconds.
Time64	datetime.timedelta	int, string, time	Integer values are interpreted as ticks at the column precision.
IPv4	`ipaddress.IPv4Address`	string	Properly formatted strings can be inserted as IPv4 addresses
IPv6	`ipaddress.IPv6Address`	string	Properly formatted strings can be inserted as IPv6 addresses
Tuple	dict or tuple
Map	dict
Nested	Sequence[dict]
UUID	uuid.UUID	string	Properly formatted strings can be inserted as ClickHouse UUIDs
JSON	dict	string	Dictionaries and JSON object strings are supported. The legacy `Object('json')` type is not supported.
Variant	object		Values use native member serialization. Use `clickhouse_connect.datatypes.dynamic.typed_variant` when Python types are ambiguous.
Dynamic	object		Values are currently inserted through their String representation.
QBit	Sequence[float]		NumPy is used automatically for faster bit transposition when installed.

Specialized insert methods

ClickHouse Connect provides specialized insert methods for common data formats:

insert_df — Insert a Pandas DataFrame as column-oriented Native data. It also supports explicit column names/types or a reusable InsertContext.
insert_arrow — Insert a PyArrow Table using the ClickHouse Arrow input format.
insert_df_arrow — Insert an Arrow-backed Pandas DataFrame or a Polars DataFrame. Pandas columns must all use Arrow-backed dtypes.

All three methods accept database, settings, and per-request HTTP transport_settings.

A NumPy array is a valid Sequence of Sequences and can be used as the data argument to the main insert method, so a specialized method isn’t required.

Pandas DataFrame insert

import clickhouse_connect
import pandas as pd

client = clickhouse_connect.get_client()

df = pd.DataFrame({
    "id": [13, 79],
    "name": ["user_1", "user_2"],
    "age": [25, 30],
})

client.insert_df("users", df)

PyArrow Table insert

import clickhouse_connect
import pyarrow as pa

client = clickhouse_connect.get_client()

arrow_table = pa.table({
    "id": [13, 79],
    "name": ["user_1", "user_2"],
    "age": [25, 30],
})

client.insert_arrow("users", arrow_table)

Arrow-backed DataFrame insert (pandas 2.x)

import clickhouse_connect
import pandas as pd

client = clickhouse_connect.get_client()

# Convert to Arrow-backed dtypes for better performance
df = pd.DataFrame({
    "id": [13, 79],
    "name": ["user_1", "user_2"],
    "age": [25, 30],
}).convert_dtypes(dtype_backend="pyarrow")

client.insert_df_arrow("users", df)

Create a table from a PyArrow schema

create_table_from_arrow_schema builds a CREATE TABLE statement from common scalar Arrow fields. The mapping covers signed and unsigned integers, floating-point values, booleans, strings, dates, and timestamps. It intentionally creates non-nullable ClickHouse columns and raises TypeError for unsupported Arrow types, so review the generated DDL before executing it.

import clickhouse_connect
import pyarrow as pa

from clickhouse_connect.driver.ddl import create_table_from_arrow_schema

client = clickhouse_connect.get_client()
schema = pa.schema(
    [
        ("id", pa.uint32()),
        ("name", pa.string()),
        ("event_time", pa.timestamp("ms", tz="UTC")),
    ]
)
ddl = create_table_from_arrow_schema(
    table_name="arrow_events",
    schema=schema,
    engine="MergeTree",
    engine_params={"ORDER BY": "id"},
)
client.command(ddl)

Time zones

When inserting Python datetime objects into DateTime or DateTime64 columns, ClickHouse Connect converts them to epoch values.

Timezone-aware datetime objects

Timezone-aware objects preserve the represented instant. The source timezone does not need to match the timezone declared on the ClickHouse column.

from datetime import datetime, timezone
from zoneinfo import ZoneInfo

client.command("CREATE TABLE events (event_time DateTime) ENGINE Memory")

data = [
    [datetime(2023, 6, 15, 10, 30, tzinfo=timezone.utc)],
    [datetime(2023, 6, 15, 10, 30, tzinfo=ZoneInfo("America/Denver"))],
    [datetime(2023, 6, 15, 10, 30, tzinfo=ZoneInfo("Asia/Tokyo"))],
]

client.insert("events", data, column_names=["event_time"])
results = client.query(
    "SELECT event_time FROM events ORDER BY event_time",
    query_tz="UTC",
    tz_mode="aware",
)
assert [row[0].hour for row in results.result_rows] == [1, 10, 16]

ClickHouse Connect uses the standard library zoneinfo module. The driver no longer depends on pytz.

Timezone-naive datetime objects

Python interprets a naive datetime in the system’s local timezone when .timestamp() is called. That makes inserts dependent on the environment. Prefer one of these approaches:

Use timezone-aware datetime objects.
Ensure the process timezone is UTC.
Attach the intended timezone or convert to an epoch integer explicitly.

from datetime import datetime, timezone

utc_time = datetime(2023, 6, 15, 10, 30, tzinfo=timezone.utc)
client.insert("events", [[utc_time]], column_names=["event_time"])

naive_time = datetime(2023, 6, 15, 10, 30)
epoch_timestamp = int(naive_time.replace(tzinfo=timezone.utc).timestamp())
client.insert("events", [[epoch_timestamp]], column_names=["event_time"])

DateTime columns with timezone metadata

ClickHouse columns can declare timezone metadata, for example DateTime('America/Denver') or DateTime64(3, 'Asia/Tokyo'). The metadata controls how values are presented when queried. When inserting into such a column, ClickHouse Connect converts the Python value according to its own tzinfo. When queried, the result uses the column timezone unless a per-column override is supplied with the column_tzs argument. The query_tz argument doesn’t override a column’s declared timezone.

from datetime import datetime
from zoneinfo import ZoneInfo

client.command(
    "CREATE TABLE events_with_timezone "
    "(event_time DateTime('America/Los_Angeles')) "
    "ENGINE Memory"
)

data = datetime(2023, 6, 15, 10, 30, tzinfo=ZoneInfo("America/New_York"))
client.insert("events_with_timezone", [[data]], column_names=["event_time"])

result = client.query("SELECT event_time FROM events_with_timezone")
returned = result.first_row[0]
assert returned.hour == 7
assert returned.tzinfo == ZoneInfo("America/Los_Angeles")

File inserts

clickhouse_connect.driver.tools.insert_file streams a local file into an existing table and delegates parsing to ClickHouse.

Parameter	Type	Default	Description
`client`	`Client`	Required	Synchronous client used for the insert.
`table`	str	Required	Simple or database-qualified target table.
`file_path`	str	Required	Local path to the input file.
`fmt`	str	`"CSV"` or `"CSVWithNames"`	Input format. Defaults to `"CSV"` when `column_names` is supplied and `"CSVWithNames"` otherwise.
`column_names`	Sequence[str]	`None`	Columns represented by the file. Not required for formats that include names.
`database`	str	`None`	Target database when the table is not qualified.
`settings`	dict	`None`	See Settings argument.
`compression`	str	`None`	Existing file compression, such as `"zstd"`, `"lz4"`, or `"gzip"`. gzip is inferred from `.gz` and `.gzip` filenames.

Input-format settings such as input_format_allow_errors_ratio and input_format_allow_errors_num can be passed through settings.

import clickhouse_connect
from clickhouse_connect.driver.tools import insert_file

client = clickhouse_connect.get_client()
insert_file(
    client,
    "example_table",
    "my_data.csv",
    settings={
        "input_format_allow_errors_ratio": 0.2,
        "input_format_allow_errors_num": 5,
    },
)

For an AsyncClient, await insert_file_async with the same arguments:

from clickhouse_connect.driver.tools import insert_file_async

await insert_file_async(async_client, "example_table", "my_data.csv")

The async helper reads the file in a worker thread before awaiting raw_insert, so the file contents are held in memory.

Home

JavaScript

Python

Go

Java

C#

Rust

C++

C

Third-party clients

Inserting data with ClickHouse Connect: Advanced usage

InsertContexts

Write formats

Write format options

Specialized insert methods

Pandas DataFrame insert

PyArrow Table insert

Arrow-backed DataFrame insert (pandas 2.x)

Create a table from a PyArrow schema

Time zones

Timezone-aware datetime objects

Timezone-naive datetime objects

DateTime columns with timezone metadata

File inserts

​Inserting data with ClickHouse Connect: Advanced usage

​InsertContexts

​Write formats

​Write format options

​Specialized insert methods

​Pandas DataFrame insert

​PyArrow Table insert

​Arrow-backed DataFrame insert (pandas 2.x)

​Create a table from a PyArrow schema

​Time zones

​Timezone-aware datetime objects

​Timezone-naive datetime objects

​DateTime columns with timezone metadata

​File inserts

Inserting data with ClickHouse Connect: Advanced usage

InsertContexts

Write formats

Write format options

Specialized insert methods

Pandas DataFrame insert

PyArrow Table insert

Arrow-backed DataFrame insert (pandas 2.x)

Create a table from a PyArrow schema

Time zones

Timezone-aware datetime objects

Timezone-naive datetime objects

DateTime columns with timezone metadata

File inserts