specs/081-clickhouse.md
Rama main · réplica de lectura del repositorio original.
# Spec: ClickHouse Action
## Status
Partially implemented.
Conformance covers action configuration without a ClickHouse server. Request
construction, streaming, error reporting, and cancellation belong in executor
unit tests and in the live integration tests that run against a server.
## Scope
This spec defines `clickhouse.query`, which runs SQL against the ClickHouse
HTTP interface. It does not define ClickHouse SQL, the native TCP protocol, or
bulk data import.
## Goal
Workflow authors can run queries, DDL, and data-modifying statements on
ClickHouse, with inline or file SQL and server-side parameters, and receive
results on step output.
## Behavior
- `with.url` is the `http://` or `https://` endpoint of the server and is
required. It may have a path but no credentials, query string, or fragment.
- `with.query` holds inline SQL. `with.file` names a `.sql` file instead; a
relative path is resolved against the step working directory. Exactly one is
required.
- `with.user` and `with.password` authenticate the request. They are sent only
in the `X-ClickHouse-User` and `X-ClickHouse-Key` request headers, never in
the URL, and the password is replaced with `***` in errors.
- `with.database` is the default database. A script of several statements
selects it once with `USE` at the start of its session, so a `USE` in the
script takes effect for later statements.
- The SQL may hold several statements separated by semicolons. A semicolon
inside a string, a quoted identifier, a heredoc, or a comment does not end a
statement; statements that are empty or hold only comments are skipped. The
statements run one after another and share one session, so `SET`, `USE`, and
temporary tables apply to the statements that follow. The step stops at the
first failing statement. A single statement runs without a session. The
executor does not close a session; the server expires it after its session
timeout.
- `with.params` maps names to values. Each is sent as a server-side query
parameter and bound to the `{name:Type}` placeholder of the same name; it is
never interpolated into the SQL. A string is passed as written, a number or
boolean as its literal, a list as an `Array` literal, and a map as a `Map`
literal.
- `with.settings` maps ClickHouse setting names to string, number, or boolean
values applied to every statement, for example `readonly: 1` or
`max_execution_time: 30`. Besides the listed settings the executor sets
only what it needs: `default_format` and, for `json`,
`output_format_json_array_of_rows` from `output_format`, a query id, a
session for scripts, and `http_write_exception_in_output_format=0`. The names
`query`, `database`, `user`, `password`, `default_format`, `query_id`,
`session_id`, `session_check`, `session_timeout`, `compress`, `decompress`,
and `http_write_exception_in_output_format`, and names starting with
`param_`, are rejected. Settings apply to every statement through the URL, so
a `SET x` in a script is overridden when `x` is also listed in
`with.settings`.
- `with.output_format` selects the result format of statements without their
own `FORMAT` clause; a `FORMAT` clause in the SQL takes precedence.
`jsonl` (default) writes one JSON object per row (`JSONEachRow`), `json`
writes a JSON array of row objects, and `csv` writes `CSV`, or
`CSVWithNames` when `with.headers` is true.
- The response of every statement is written to step stdout as it arrives,
whether or not the statement returns rows.
- `with.timeout` is the number of seconds the step may run, 60 by default and
0 for no limit. For an `https://` endpoint `with.tls_ca` names a PEM bundle of
additional root CAs, and `with.tls_skip_verify` disables certificate
verification. `with.tls_ca` is read when the step starts, not by
`dagu validate`. Redirects are not followed.
- Each statement writes one JSON line to stderr with its number (`statement`,
0 for the `USE` that selects `with.database`), `query_id`, `started_at`,
`finished_at`, `duration_ms`, `bytes_returned`, `status`, and `error` on
failure.
- Each statement has a unique query id. When the step is stopped, cancelled, or
times out, the request is closed and the executor asks the server to stop the
statement with `KILL QUERY` on a short separate deadline.
## Errors
- A missing `with.url`, a missing or conflicting `with.query` and `with.file`,
a `with.url` that holds credentials, an unknown option, an invalid
`with.output_format`, or a rejected setting or parameter name fails
validation with a diagnostic naming the problem. They are reported without
contacting a server.
- A response with a status other than 200 fails the step with the status and
the error text of the server. In a multi-statement script the diagnostic
names the failing statement. Error text kept from a response is limited to
about 8 KiB. The server may echo parameter values in its error text.
- An error that the server reports after it began streaming a 200 response
fails the step. When the server announces an error report with the
`X-ClickHouse-Exception-Tag` response header, the report is removed from the
output and its text becomes the step error. A response that ends before it is
complete fails the step as an interrupted response.
- When the timeout expires or the step is cancelled, the step fails. Stopping
the statement on the server is best effort; a refused or failed `KILL QUERY`
does not change the step outcome.
## Examples
```yaml
steps:
- action: clickhouse.query
with:
url: https://clickhouse.example.com:8443
user: etl
password: ${CLICKHOUSE_PASSWORD}
database: analytics
query: |
INSERT INTO daily SELECT * FROM raw WHERE day = {day:Date};
SELECT count() AS rows FROM daily WHERE day = {day:Date}
params:
day: "2024-01-02"
settings:
max_execution_time: 300
```
```yaml
steps:
- action: clickhouse.query
with:
url: http://localhost:8123
file: sql/rollup.sql
output_format: csv
headers: true
```