ClickHouse

Drex for ClickHouse is five SQL functions that call Drex, Nace's decision models, for each row and return calibrated probabilities as ClickHouse values. See Questions for how to write them.

ClickHouse functions take a fixed number of arguments, so every argument is required: pass '' (or NULL) for no criteria, no instructions, or the default model.

InstallCopy Anchor Link

The functions are a Python script, built on the Python SDK, that ClickHouse runs as an executable UDF. They need ClickHouse 26.7 or later: earlier versions pass arguments to executable UDFs in a form the script can't read, and a query on one usually fails saying so. Their source and setup are nace-ai/drex-clickhouse on GitHub, under the MIT license. The steps below install release v0.1.2.

The quickest start is the repository's Docker image: the official ClickHouse server with the script, the SDK and the functions' config. Create a key as shown in Authentication, then:

git clone --branch v0.1.2 https://github.com/nace-ai/drex-clickhouse.git
cd drex-clickhouse
mkdir -p secrets
(umask 077 && read -rs -p 'Nace API key: ' key && printf '%s' "$key" > secrets/nace_api_key); echo
sudo chown 101:101 secrets/nace_api_key  # Linux: the clickhouse user in the container
docker compose up -d --build --wait
docker compose exec clickhouse clickhouse-client

The server listens on 127.0.0.1 only, as the image's default user with no password. Add users and passwords before exposing it.

To add the functions to a server you already run, the repository's README lists the files to copy: a Python environment with the SDK, the script, drex_function.xml in ClickHouse's config directory, and the key in a file only the clickhouse user can read. The key is never a function argument, so it never appears in SQL or the query log. The functions read the key when their processes start, so after changing it run SYSTEM RELOAD FUNCTIONS. On a cluster, install them on every replica.

To upgrade, check out the new release's tag (git fetch --tags && git checkout vX.Y.Z), then run docker compose up -d --build --wait, or copy the script and config again and run SYSTEM RELOAD FUNCTIONS.

UsageCopy Anchor Link

Ask one question per rowCopy Anchor Link

Each non-NULL row is one Drex request. A NULL input isn't sent or billed: drex_noul, drex_decide and drex_decide_image return NULL, and drex_choice and drex_score return a tuple whose choice or score and confidence are NULL and whose probabilities is empty, since a ClickHouse tuple can't be NULL. A row without an answer, below, returns the same.

SELECT
  ticket_id,
  drex_choice(body, 'Which team should handle this support ticket?', ['billing', 'technical', 'sales'], '') AS team
FROM tickets;

team.choice is the chosen label, team.probabilities holds every label with its probability, sorted by label, and team.confidence is Drex's confidence. Reading answers explains each field.

drex_noul takes its criteria as JSON text, for example '{"true": "The customer wants money back", "false": null}', or '' for none. drex_choice and drex_score take 2 to 255 plain labels; for choice labels with descriptions, use drex_decide. A score's levels take no descriptions, so describe the scale in its instructions.

Ask several questions at onceCopy Anchor Link

drex_decide sends every question in one request per row, so the row is sent and billed once instead of once per question. The questions are JSON text, and the result is Drex's whole response:

SELECT
  ticket_id,
  JSONExtract(answers, 'answers', 'refund', 'noul', 'Float64') AS refund,
  JSONExtractString(answers, 'answers', 'team', 'choice') AS team
FROM (
  SELECT ticket_id, drex_decide(
    toJSONString(map('ticket', body)),
    '{"refund": {"type": "noul", "instructions": "Is the customer asking for a refund?"},
      "team": {"type": "choice", "instructions": "Which team should handle this ticket?",
               "criteria": {"billing": "Payments, invoices and refunds", "technical": "Bugs and errors", "sales": null}}}',
    ''
  ) AS answers
  FROM tickets
);

The state is JSON text, so a whole row works too, as structured state. It must be valid JSON, so wrap plain text with toJSONString; a state that isn't fails the query.

Read imagesCopy Anchor Link

Drex Max reads images. drex_decide_image takes 1 to 8 PNG, JPEG or WebP images as bytes, which ClickHouse reads from files or S3; prompts refer to them as "Image 1", "Image 2" and so on. It uses drex-max unless you name another model that reads images.

SELECT
  _file,
  JSONExtractString(
    drex_decide_image(
      '"A scanned document"',
      [_contents],
      '{"kind": {"type": "choice", "instructions": "What kind of document is Image 1?",
                 "criteria": {"invoice": "A bill or invoice", "receipt": "A proof of payment", "other": null}}}',
      ''
    ),
    'answers', 'kind', 'choice'
  ) AS kind
FROM file('scans/*.jpg', 'RawBLOB', '_contents String');

file() reads from the server's user_files directory; with the Docker setup, copy the files in first with docker compose cp ./scans clickhouse:/var/lib/clickhouse/user_files/scans. An empty array returns NULL and sends nothing. An image that isn't PNG, JPEG or WebP, or a row whose request would exceed the 4 MiB body limit, returns NULL without being sent, so resize large images first. Drex Max's image limits list the rest.

ModelsCopy Anchor Link

The default model is drex-v2.0, which reads text and JSON. Pass 'drex-max' as model for Drex Max. drex-v2.0 costs $0.03 and Drex Max $0.03 per million input tokens; see Pricing.

The functions check each block of rows before sending any of it: the model must be one that GET /v1/models lists for your key, images need a model that reads them, and the labels and questions must follow the rules in Questions and Drex's request limits. The functions are stricter than Drex: a choice or score takes 2 to 255 unique labels, drex_noul's criteria labels are exactly true and false, and drex_decide takes 1 to 512 questions of type noul, choice or score with ids unique ignoring case. A query that fails a check returns an error. A row over the model's token limit isn't checked in advance: Drex refuses it, and it returns NULL.

Concurrency and rate limitsCopy Anchor Link

Each function runs as a pool of processes on each server, and each process sends a few requests at once. The repository's config uses 2 processes with 4 requests each, so at most 8 requests per function and server are in flight, which fits a free account when one function runs on one server:

On a paid account, or a cluster, change pool_size and --concurrency in drex_function.xml: their product, times the servers and the functions running at once, is the most requests in flight. Every API key on an account shares its limits (see Scope). An account over its limit gets a 429 and the wait, and the functions retry after it.

ErrorsCopy Anchor Link

• 401, 402, a 422 about the questions or model, an unknown model, images with a model that doesn't read them, and a TLS failure fail the query. A 422 message lists each issue's path and message; a 402 links to Billing.
• No response, 408, 429, 500, 502, 503, 504 and 529 are retried after retry-after-ms (or retry-after, at most 30 seconds), at most 5 attempts within 2 minutes, each up to 60 seconds. A row whose retries run out returns NULL.
• A row Drex refuses on its own account, such as one over the token limit, and any other status, such as 403 or 404, return NULL with a warning in ClickHouse's server log and system.text_log. The warning never quotes the row.

Only a 200 is billed, so a failed attempt or a row that returns NULL costs nothing; a retry is billed twice only if a 200 was lost on its way back. See Errors and retries.

What gets sent, and billedCopy Anchor Link

ClickHouse calls the functions a block of rows at a time, and every request Drex answers is billed, whether or not the query returns that row.

• A WHERE on an answer, or ORDER BY ... LIMIT, sends every row it reads. Filter and limit the rows in a subquery, then call the function on its result.
• A cancelled query (KILL QUERY, or Ctrl-C) still sends the rest of the block it was answering. SETTINGS max_block_size = 1000 keeps blocks small.
• Running a query again, or reading a view again, sends its rows again. Keep the answers with CREATE TABLE ... AS SELECT or INSERT INTO ... SELECT and read the table.

CREATE TABLE ticket_teams ENGINE = MergeTree ORDER BY ticket_id AS
SELECT ticket_id, drex_choice(body, 'Which team should handle this support ticket?', ['billing', 'technical', 'sales'], '') AS team
FROM (SELECT * FROM tickets ORDER BY ticket_id DESC LIMIT 100);

CostCopy Anchor Link

Each row is billed for its input tokens at the model's price, and output tokens aren't billed. For 10,000,000 rows:

The same row counts a different number of tokens on each model, so read usage.input_tokens from drex_decide for a sample of rows on the model you plan to use before running a whole table.

ClickHouse CloudCopy Anchor Link

ClickHouse Cloud runs Python executable functions with network access. The repository builds one zip per function, with the script, its requirements and a config file, and includes a Terraform module (ClickHouse provider 3.24.0 or later, with an organization API key that has the Admin role) that creates the functions and attaches them to a service:

read -rs -p 'Nace API key: ' NACE_API_KEY && export NACE_API_KEY; echo
python3 cloud/build_bundles.py --pool-size 2   # writes cloud/dist/drex_<function>.zip
cd cloud/terraform
terraform init
terraform plan -var='service_id=YOUR_SERVICE_ID' -out=tfplan
terraform apply tfplan

The repository's README covers the Terraform credentials and the settings for creating the functions in the console instead. Attaching the first function to a service restarts its replicas one by one.

The service must run ClickHouse 26.7 or later (SELECT version()). A service on an older release gets one sooner on the fast release channel, in its settings. Each process on ClickHouse Cloud sends at most 4 requests at once, so the module runs 2 processes per function and replica: 8 requests in flight per replica.

Function names are unique within a ClickHouse Cloud organization, so one set of these functions, with one key, serves every service it is attached to.