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.
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 takesquality(4 by default). Compressing zstd needs Python 3.14, Brotli thepyfect-dev[brotli]extra. httpx reads Brotli responses with that extra and zstd ones only with thezstandardpackage, so the config checks for them when it's made. A user withreadonly=1can't ask for compressed responses, since that setsenable_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 anhttpsURL; addcertandkeyto log in with a client certificate. Withtls, credentials go in theX-ClickHouse-UserandX-ClickHouse-Keyheaders, as@clickhouse/clientsends them, and anaccess_tokenis refused.use_multipart_params=Truesends a query's parameters (and the query) as amultipart/form-databody instead of in the URL, for parameters too long for a URL.use_multipart_params_auto=Truedoes that only once they'd take more than 4096 characters.log={"level": "ERROR"}sets which of the client's own logs are written (WARNby default;"OFF"for none). As@clickhouse/clientdoes, it logs a failed request atERROR, a failed/pingatWARN, and warns when the URL overrides a field or whenrequest_timeoutis over a minute without progress headers. The logs go through the fiber's loggers, sofx.logger.layer(...)takes the place ofLoggerClass.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 asJSONEachRow. Passformat=andbytes(a CSV body, say) or aStreamfor other formats, andcolumns=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.