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 '' 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 fails saying so. Their source and setup are nace-ai/drex-clickhouse on GitHub, under the MIT license.

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 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

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.

UsageCopy Anchor Link

Ask one question per rowCopy Anchor Link

Each non-NULL row is one Drex request. A NULL input returns NULL and isn't sent or billed.

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 plain labels; for labels with descriptions, use drex_decide.

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.

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');

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.06 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 the model's limits. A query that fails a check returns an error.

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:

On a paid account, or a cluster, change pool_size and --concurrency in drex_function.xml: their product, times the servers, 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 and 422 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, at most 5 attempts within 2 minutes. A row whose retries run out returns NULL.
• A row Drex refuses on its own account, such as one over the token limit, returns 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 retried or failed row costs nothing. 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 that creates the functions and attaches them to a service.

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.