GitHub

ClickHouse

A ClickHouse client over the HTTP interface: typed parameters, inserts in any format, streams, per-effect settings and query ids, and migrations.

pyfect.sql.clickhouse talks to ClickHouse's HTTP interface, the way @clickhouse/client does, so it needs no native driver. Requests go through a pyfect HttpClient; by default the client makes one on httpx (the pyfect-dev[httpx] extra) and closes it with its layer. clickhouse.layer provides both ClickhouseClient and SqlClient.

connect.py
import os
import uuid

import pyfect as fx
from pyfect import redacted
from pyfect.sql import clickhouse

# CLICKHOUSE_URL=http://localhost:8123 CLICKHOUSE_PASSWORD=... python connect.py
config = clickhouse.ClickhouseClientConfig(
    url=os.environ.get("CLICKHOUSE_URL", "http://localhost:8123"),
    username=os.environ.get("CLICKHOUSE_USER", "default"),
    password=redacted.make(os.environ.get("CLICKHOUSE_PASSWORD", "")),
)
table = f"events_{uuid.uuid4().hex[:8]}"


@fx.fn
def program():
    sql = yield from clickhouse.ClickhouseClient()
    yield from sql(
        "CREATE TABLE {} (kind String, at DateTime) ENGINE = MergeTree ORDER BY at",
        sql.identifier(table),
    )

    # Rows go in through insert_query, as JSONEachRow by default.
    rows = [
        {"kind": "signup", "at": "2024-01-15 14:30:00"},
        {"kind": "login", "at": "2024-01-15 15:00:00"},
    ]
    yield from sql.insert_query(table=table, values=rows)

    # Each value is a {p1: Type} parameter; the type comes from the value,
    # or from sql.param.
    since = sql.param("DateTime", "2024-01-15 14:45:00")
    print((yield from sql("SELECT kind FROM {} WHERE at > {}", sql.identifier(table), since)))

    # Settings and the query id apply to every statement of an effect.
    count = sql(
        "SELECT count() AS n, getSetting('max_threads') AS threads FROM {}", sql.identifier(table)
    )
    print((yield from sql.with_clickhouse_settings(count, {"max_threads": 2})))

    # A statement with data (INSERT ... VALUES) runs as a command, without
    # the FORMAT JSON a query gets.
    insert = sql("INSERT INTO {} VALUES ({}, now())", sql.identifier(table), "logout")
    yield from sql.as_command(insert)
    yield from sql("DROP TABLE {}", sql.identifier(table))


fx.run_main(program().pipe(fx.provide(clickhouse.layer(config))))

Connecting

ClickhouseClientConfig takes the server's url (http://localhost:8123 by default), a username and password (a Redacted) or an access_token, the database, and settings and headers sent with every request. The URL can carry most of them, as @clickhouse/client reads it: http://user:password@host:8123/database?ch_max_threads=4. It is checked when the config is made, so a typo fails at startup. Pass http_client= to send requests with an HttpClient you already have.

To read the settings from the environment, use clickhouse.layer_config({"url": fx.config.string("CLICKHOUSE_URL"), "password": fx.config.redacted("CLICKHOUSE_PASSWORD")}). The layer checks the server with /ping and fails with a ConnectionError if it doesn't answer within five seconds. /ping checks no credentials, so a wrong password shows up on the first statement. For a named user that is an AuthenticationError, but the default user gets code 194 (REQUIRED_PASSWORD, HTTP 401), which is an UnknownError; see below.

Transport options

The config takes @clickhouse/client's transport options under the same names:

  • compression={"response": True, "request": True} asks the server for gzipped responses and gzips what the client sends (insert bodies, commands). Pick a codec with {"response": {"codec": "br"}} or {"request": {"codec": "zstd", "level": 3}}; Brotli takes quality (4 by default). Compressing zstd needs Python 3.14, Brotli the pyfect-dev[brotli] extra. httpx reads Brotli responses with that extra and zstd ones only with the zstandard package, so the config checks for them when it's made. A user with readonly=1 can't ask for compressed responses, since that sets enable_http_compression.
  • max_open_connections (10) caps the connections open at once. keep_alive={"enabled": False} closes each connection after its request; idle_socket_ttl (2500 ms) is how long an idle one stays open.
  • tls={"ca_cert": pem} trusts a private CA on an https URL; add cert and key to log in with a client certificate. With tls, credentials go in the X-ClickHouse-User and X-ClickHouse-Key headers, as @clickhouse/client sends them, and an access_token is refused.
  • use_multipart_params=True sends a query's parameters (and the query) as a multipart/form-data body instead of in the URL, for parameters too long for a URL. use_multipart_params_auto=True does that only once they'd take more than 4096 characters.
  • log={"level": "ERROR"} sets which of the client's own logs are written (WARN by default; "OFF" for none). As @clickhouse/client does, it logs a failed request at ERROR, a failed /ping at WARN, and warns when the URL overrides a field or when request_timeout is over a minute without progress headers. The logs go through the fiber's loggers, so fx.logger.layer(...) takes the place of LoggerClass.
  • json={"parse": ..., "stringify": ...} reads result rows and writes inserted rows.

The URL can set most of these too: compression_request=1, compression_response=1, keep_alive_enabled=0, keep_alive_idle_socket_ttl, max_open_connections and log_level. max_open_connections, keep_alive and tls configure the httpx client the layer makes. They do nothing for an http_client= of your own, just as @clickhouse/client ignores them when you pass an http_agent.

Statements

Each value becomes a {p1: Type} parameter. The type comes from the value: Int64 for an int, Float64, Bool, String, DateTime (DateTime64(6) with microseconds), Date, Array(...). Name it yourself with sql.param("Nullable(Int32)", None). A clickhouse.TupleParam is written as a tuple and a dict as a map, but, as in Effect, their placeholder says String, so ClickHouse reads them as text unless you name the type: sql.param("Tuple(Int64, String)", clickhouse.TupleParam((42, "foo"))). A datetime must be aware (have a tzinfo); a naive one is a defect, since a JavaScript Date always has a zone.

A statement runs as a query with FORMAT JSON appended. That doesn't work for a statement that carries data, like INSERT ... VALUES, so:

  • sql.as_command(effect) runs the effect's statements as commands, which send the SQL as written and ignore the output.
  • sql.insert_query(table=..., values=rows) sends rows as JSONEachRow. Pass format= and bytes (a CSV body, say) or a Stream for other formats, and columns= to pick columns.

sql.with_query_id(effect, "id") and sql.with_clickhouse_settings(effect, {...}) apply to every statement and insert inside the effect. Interrupting a statement aborts its request and runs KILL QUERY for it. A stream does the same if it is dropped before its last row.

A failure's reason comes from ClickHouse's error code, the same codes Effect maps: AuthenticationError (516), AuthorizationError (497), SqlSyntaxError (36, 60, 62, 242) and StatementTimeoutError (159, 469). Everything else is an UnknownError whose cause is the ClickhouseError with the server's code. That includes a wrong password for the default user, which ClickHouse answers with 194 rather than 516, so check error.reason.cause.code if you log in as default. sql.client is the HTTP client underneath, with query, command, exec and insert for formats the SQL client doesn't read.

Migrations

clickhouse.migrator works like the SQLite migrator. The ledger is a MergeTree table, and migrations run outside transactions, since ClickHouse's are experimental. Each migration is recorded once it succeeds, so a failed one and the ones after it run again next time.