A Common Lisp UDF runtime and query compiler for ClickHouse, shipped as a single binary.
Repo ClickHouse/clicklisp, read from GitHub 1 min ago. ClickHouse has not joined Take My Repo.
ClickHouse/clicklisp is a public repo on GitHub by ClickHouse. A Common Lisp UDF runtime and query compiler for ClickHouse, shipped as a single binary. It declares the Apache-2.0 license on GitHub. It is written mainly in Common Lisp, has 8 stars and last changed on 2026-11-21. Nobody has reviewed it on Take My Repo yet, which says nothing about its quality. Read from GitHub on 2026-11-21.
In the maintainer's words. Shown from GitHub; Take My Repo does not keep a copy.
The CL in ClickHouse.
Common Lisp was hiding in the first two letters the whole time.
S-expressions in, ClickHouse out. One small static binary, built with ECL (Lisp → C → native), no runtime dependencies.
Two halves that compose:
(select ...) forms become ClickHouse SQL. Queries
are data: composable, diffable, testable. Detection rules live in a
library of forms (defrule) instead of SQL string blobs.user_scripts_path, add one XML file, and call Lisp from SELECT — with
hot code reload while queries stream through the process.$ echo '(select ((as (count) c))
:from events
:where (and (= status 500)
(> ts (- (now) (interval 1 :hour))))
:group-by (user)
:order-by ((c :desc))
:limit 10)' | clicklisp compile
SELECT count() AS c FROM events WHERE ((status = 500) AND (ts > (now() - INTERVAL 1 HOUR))) GROUP BY user ORDER BY c DESC LIMIT 10
Requires ECL (brew install ecl / apt install ecl) and a C compiler.
make # bin/clicklisp for the host platform make test # run the test suite (no dependencies beyond ECL) make smoke # end-to-end checks against the built binary
For the deployable single-file binary, build statically against musl in an
Alpine container (first run also builds a static ECL toolchain, ~3 min,
cached under .cache/):
docker run --rm \ -v "$PWD:/src" -v "$PWD/.cache/ecl-static:/cache" -w /src \ alpine:3.22 sh scripts/build-static.sh
The result is dist/clicklisp-linux-$(uname -m): statically linked, a few
MB after stripping, runs on any Linux of that architecture. Release
binaries for x86_64 and aarch64 are published by the
release workflow.
sql.clickhouse.com hosts ClickHouse's example datasets behind a read-only HTTP endpoint — scripts/play.sh sends it SQL from stdin, so compiled queries run against real data with no server at all:
$ echo '(select (town (as (round (avg price)) avg-price)) :from uk.uk-price-paid :where (>= date (to-date "2025-01-01")) :group-by (town) :having (> (count) 1000) :order-by ((avg-price :desc)) :limit 3)' | bin/clicklisp compile | scripts/play.sh ┌─town──────┬─avg_price─┐ 1. │ LONDON │ 859963 │ 2. │ RICHMOND │ 797900 │ 3. │ SEVENOAKS │ 768441 │ └───────────┴───────────┘
NAME=VALUE arguments bind server-side (param name :type) query
parameters; CLICKLISP_FORMAT and CLICKLISP_PLAY override the output
format and the endpoint. One statement per invocation (the ClickHouse
HTTP interface is single-statement).
One thing to know about the playground: its demo user reads at most a
billion rows per query and then stops without an error, so a query over
one of the large tables that does not prune returns a partial answer
that looks complete. :settings ((read_overflow_mode "throw")) makes
the playground refuse such a query instead.
The same queries run as live demos
in the browser: the site's deploy workflow builds the binary, compiles
the example libraries with rules json, and each demo page sends the
compiled SQL to the playground with typed param_ bindings — no server
of ours in the path. Queries that need a local executable UDF
(shouting-titles in hackernews.lisp) stay off the demo pages.
Operators and clause names are matched by symbol name, so forms can be written in any package and built as plain data.
| Form | SQL |
|---|---|
event-time, t1.id, t1.* |
event_time, t1.id, t1.* (dashes → underscores) |
"text", 1.5, :null, t, nil |
'text', 1.5, NULL, true, NULL |
#(1 2 3), (array 1 2), (tuple 1 2) |
[1, 2, 3], [1, 2], (1, 2) |
(and (= a 1) (> b 2)) |
((a = 1) AND (b > 2)) |
(< 1 x 10) |
((1 < x) AND (x < 10)) — comparisons chain like Lisp |
(in user (list "a" "b")), (in id (select ...)) |
(user IN ('a', 'b')), subquery IN |
(like name "%admin%"), (between x 1 10), (is-null x) |
LIKE, BETWEEN, IS NULL |
(to-start-of-hour ts) |
toStartOfHour(ts) — kebab-case → camelCase |
(json-extract-string raw "user") |
JSONExtractString(raw, 'user') — irregular names via registry |
(lambda (x) (+ x 1)) |
x -> (x + 1) — Lisp lambdas are ClickHouse lambdas |
(array-map (lambda (x) (* x 2)) xs) |
arrayMap(x -> (x * 2), xs) |
(case ((> a 1) "big") (:else "small")) |
CASE WHEN ... END |
(cast x :uint64), (cast x (:nullable :uint64)) |
CAST(x AS UInt64), CAST(x AS Nullable(UInt64)) |
(param user-id :uint64) |
{user_id:UInt64} — server-side query parameters |
(as expr alias), (aref tags 1), (interval 10 :minutes) |
expr AS alias, tags[1], INTERVAL 10 MINUTE |
:from (numbers 10), :from (as (s3 "https://..." "CSV") t) |
FROM numbers(10), FROM s3('https://...', 'CSV') AS t — table functions, in :from and :join |
(raw "now() - 1"), (fn "toIPv4" ip) |
verbatim escape hatches |
select takes a column list (or *, or (:distinct ...)) followed by
keyword clauses, emitted in proper ClickHouse order regardless of how you
write them:
(select (user (as (count) hits)) :with ((threshold 20)) ; CTEs: scalar or (name (select ...)) :from (as (select ...) sub) ; tables, subqueries, (final tbl) :join (:left t2 :on (= t1.id t2.id)) ; or a list of join specs, :using (...) :array-join (tags) ; and :left-array-join :prewhere (= shard 3) :where (> hits threshold) :group-by (user) :with-totals t ; also :with-rollup / :with-cube :having (> hits 10) :order-by ((hits :desc :nulls-last)) :limit-by (3 user) :limit 100 :offset 10 :settings ((max_threads 4)) :format "JSONEachRow")
(union-all q1 q2) and (union-distinct ...) combine queries. Strings are
escaped, identifiers are validated or backtick-quoted, function and format
names must be plain identifiers, and every infix expression is
parenthesized, so precedence bugs are structurally impossible. Verbatim
SQL only goes into a query where a form takes a string for that purpose:
(raw "..."), (fn "name" ...), a type written as a string such as
"Array(Point)", and the names registered with define-sql-function and
define-sql-type.
Unknown functions map kebab→camelCase automatically; teach the compiler
irregular ClickHouse spellings with
(define-sql-function my-fn "MyClickHouseName") and types with
(define-sql-type geo-point "Point"). A function name has to come out as
a plain identifier; (fn "quantile(0.9)" price) is for the ones that do
not.
The sql macro compiles at macroexpansion time — a rule library costs
nothing at runtime:
(sql (select (1))) ; => the literal string "SELECT 1"
;; rules.lisp (defrule ssh-bruteforce (:description "Hosts with > 20 failed SSH logins in 10 minutes" :severity :high :tags (:t1110 :ssh)) (select ((as source-ip ip) (as (count) attempts)) :from auth-events :where (and (= event-type "ssh_login_failed") (> event-time (- (now) (interval 10 :minutes)))) :group-by (source-ip) :having (> attempts 20)))
Rules are validated (compiled) at load time and rendered on demand:
clicklisp rules --load rules.lisp # list clicklisp rules sql --load rules.lisp --all # emit SQL for all clicklisp rules sql --load rules.lisp ssh-bruteforce clicklisp rules json --load rules.lisp --all # machine-readable JSON
rules json dumps each rule as JSON — name, description, severity,
tags, the extracted {name:Type} params, compact sql, multiline
sql_pretty, and the reconstructed form — for anything downstream that
wants rules as data; it is what feeds the
live demos at build time.
See examples/rules.lisp.
Exit codes: 0 success, 1 bad input (a query that does not compile),
2 bad invocation.
defquery is defrule minus the detection metadata: named queries for
any domain, served by the same rules commands.
examples/analytics/ has four libraries built on
the playground's example datasets:
(fn "quantile(0.9)" price), a (raw ...) window function, and a
macro that stamps out one price-league query per property type.clickhouse git-import tables; the whole library is one macro
parameterized by table names, so it runs against your repo locally,
and repo-health-playground.lisp
re-invokes it against the playground's pre-imported ClickHouse mirror
(load both files; the second registration wins).defrule threat pack for the playground's table of 11 billion public
GitHub events (github.events): branch-reset storms, mass ref
deletion, tag retargeting, star storms, bulk collaborator adds,
commit-hour anomalies, each tagged with MITRE ATT&CK technique IDs.
Every rule prunes to less than the playground's billion-row limit; the
two over the largest event types do it with an org parameter. Those
two also want a window before October 2025, when the dataset's loader
stopped populating push_size and tag CreateEvents, and the star
and deletion thresholds are parameters because the feed is thinner
now. The file header has the details.Rule files are full Common Lisp, and queries are data — so a macro can generate a family of queries with no copy-paste and no templating language:
(defmacro def-price-league (ptype) `(defquery ,(intern (format nil "TOP-~A-DISTRICTS" ptype)) (:description ,(format nil "Priciest districts for ~(~A~) sales" ptype)) (select (district (as (round (avg price)) avg-price)) :from uk.uk-price-paid :where (= type ,(string-downcase (string ptype))) :group-by (district) :order-by ((avg-price :desc)) :limit 10))) (def-price-league flat) ; => top-flat-districts (def-price-league detached) ; => top-detached-districts, ...
Parameterized queries bind server-side through play.sh arguments:
$ bin/clicklisp rules sql --load examples/analytics/uk-price-paid.lisp price-trend \
| scripts/play.sh town=LONDON
┌─year─┬─avg_price─┬─trend──────────┐
1. │ 1995 │ 109154 │ █▍ │
2. │ 1996 │ 118686 │ █▌ │
...
29. │ 2023 │ 1026396 │ █████████████▋ │
└──────┴───────────┴────────────────┘clicklisp repl --load examples/analytics/uk-price-paid.lisp gives you
the same registry interactively: evaluate another (def-price-league ...)
form, then (princ (rule-sql (find-rule "top-flat-districts"))).
Two authoring notes: a one-element :order-by ((year)) is read as the
call year() — write :order-by (year) or ((year :desc)) — and
executable-UDF calls must be written snake_case ((clicklisp_shout x)),
since kebab-case would camelCase to clicklispShout(x).
Define a function, and the binary serves it over ClickHouse's executable UDF protocol:
(defudf entropy (s)
"Shannon entropy in bits/char - flags DGA-style domains."
...)printf 'clickhouse\nq7x9z2j4k8w1.evil.example\n' | clicklisp udf --fn entropy 3.121928094887362 4.213660689688184
UDFs load from plain Lisp files too —
examples/udfs/text.lisp defines shout, a
clickbait score (fraction of letters that are uppercase):
$ printf 'HELLO World\n' | clicklisp udf --fn shout --load examples/udfs/text.lisp 0.6
Deploy on a self-managed server: copy bin/clicklisp into
user_scripts_path (default /var/lib/clickhouse/user_scripts/), and a
config like examples/clicklisp_function.xml
next to the server config (the filename must match the
user_defined_executable_functions_config glob, default *_function.*ml):
SELECT domain, clicklisp_entropy(domain) AS h FROM dns_events WHERE h > 3.8
Protocol notes (all handled by clicklisp udf):
TabSeparated: one row per line, fields tab-separated, \t \n \\
escaped, \N for NULL. Output rows must match input rows one-to-one.type=executable spawns a process per block and closes stdin;
type=executable_pool keeps long-lived processes that must loop forever
and flush per chunk. clicklisp udf flushes per row, or per chunk with
--chunked when the config sets <send_chunk_header>1</send_chunk_header>
(ClickHouse then precedes each block with its row count).\N instead of killing the pool.Run a ClickHouse server in Docker with the static Linux binary (from
scripts/build-static.sh or a release) mounted into user_scripts_path
— on Apple Silicon the aarch64 build runs natively in the container:
# clicklisp-linux-aarch64 on Apple Silicon, clicklisp-linux-x86_64 on Intel docker run -d --name clicklisp-ch \ -v "$PWD/dist/clicklisp-linux-aarch64:/var/lib/clickhouse/user_scripts/clicklisp" \ -v "$PWD/examples/udfs/text.lisp:/var/lib/clickhouse/user_scripts/text.lisp" \ -v "$PWD/examples/clicklisp_function.xml:/etc/clickhouse-server/clicklisp_function.xml" \ clickhouse/clickhouse-server:latest docker exec clicklisp-ch clickhouse-client \ -q "SELECT clicklisp_entropy('q7x9z2j4k8w1'), clicklisp_shout('READ THIS now')"
Load the Hacker News dataset
into the container and the shouting-titles query in
examples/analytics/hackernews.lisp
ranks real stories with it — SQL compiled from Lisp calling back into
Lisp per row.
Detection rules compile on the host and pipe straight into the server, calling back into the Lisp UDF:
bin/clicklisp rules sql --load examples/rules.lisp dga-domains \ | grep -v '^--' \ | docker exec -i clicklisp-ch clickhouse-client --format PrettyCompact
UDF stderr (failed rows, hot-reload notices) lands in the server log:
docker logs clicklisp-ch. Tear down with docker rm -f clicklisp-ch.
--watch re-loads a Lisp file whenever it changes, while the pool process
keeps serving queries — using ECL's in-image bytecode compiler, so it
works inside the UDF sandbox where no C toolchain exists. The
clicklisp_shout entry in
examples/clicklisp_function.xml is
wired this way:
<function> <type>executable_pool</type> <name>clicklisp_shout</name> <command>clicklisp udf --fn shout --load user_scripts/text.lisp --watch user_scripts/text.lisp --chunked</command> <format>TabSeparated</format> <argument><type>String</type></argument> <return_type>Float64</return_type> <send_chunk_header>1</send_chunk_header> </function>
Edit text.lisp in user_scripts_path, save, and the next block is
served by the new definition. clicklisp repl gives you the same
live-coding loop locally.
ClickHouse Cloud note: executable UDFs on Cloud are in public beta but
Python-shaped (ZIP with main.py uploaded via the console) — the native
binary path here targets self-managed ClickHouse; the experimental
driver-based UDF mechanism in ClickHouse master is the future hook for
compiling Lisp at CREATE FUNCTION time.
ubuntu-24.04 and ubuntu-24.04-arm.vX.Y.Z (which must match version.sexp)
builds both static binaries and publishes them with SHA256SUMS to a
GitHub release.make site-data), builds the site, and deploys it to
cl.clickhouse.com — so the demos are
compiled by the same commit they document.Supply-chain posture: every workflow action is pinned to a commit SHA
(dependabot keeps the pins fresh), workflows run with least-privilege
permissions, the ECL source tarball is SHA256-verified before it is
built, and the Alpine build image is pinned by digest.
src/names.lisp identifier/function/type naming, quoting, literals
src/compiler.lisp the s-expression -> SQL compiler
src/rules.lisp defrule/defquery registry
src/udf.lisp TabSeparated UDF loop, defudf, hot reload
src/main.lisp CLI entry point
build/build.lisp ECL AOT build (c:build-program; works on static ECL)
scripts/build-static.sh static musl build, run inside alpine:3.22
scripts/play.sh pipe SQL from stdin to the public ClickHouse playground
scripts/gen-site-data.sh compile the example libraries to JSON for the site demos
examples/ detection rules, analytics libraries, UDFs, server config
site/ cl.clickhouse.com — docs plus live demos of the libraries
tests/ zero-dependency harness + suite
ClickHouse/clicklisp is a public repo on GitHub by ClickHouse. A Common Lisp UDF runtime and query compiler for ClickHouse, shipped as a single binary.
It declares the Apache-2.0 license on GitHub.
Its maker has not tagged a system, so it is unknown here. Its README says what it needs.
Open "Take it" on its page: Take My Repo writes an install plan for your machine and hands it to your AI agent (Claude Code, Codex or Cursor), which asks you before each step. Take My Repo has not tested it on any machine.
Its last change on GitHub was on 2026-11-21. Take My Repo does not judge from that whether it is maintained.
Nobody has reviewed it on Take My Repo yet, which says nothing about its quality.
The Postgres development platform. Supabase gives you a dedicated Postgres database to build your web, mobile, and AI applications.
Stop renting your intelligence. Own it with AnythingLLM. Everything you need for a powerful local-first agent experience
25 MB lightweight cross-platform database client for 100+ databases, including MySQL, PostgreSQL, SQLite, Redis, MongoDB, DuckDB, SQL Server, and Dameng. Built-in AI, MCP Server, CLI, desktop and Docker. | 轻量级跨平台数据库管理工具,支持 MySQL、PostgreSQL、SQLite、Redis、MongoDB、达梦等 100+ 数据库,提供桌面端、Docker、CLI、内置 AI 助手和 MCP。
ClickHouse® is a real-time analytics database management system

Resolve production issues, fast. An open source observability platform unifying session replays, logs, metrics, traces and errors powered by ClickHouse and OpenTelemetry.
Presentations, meetups and talks about ClickHouse
This page is also available as structured data, with the same sources and dates.
/api/v1/resources/gh/ClickHouse/clicklispTake My Repo has not tested this on your machine. Requirements come from the project's own documentation.