ClickHouse

View as Markdown

A RocketRide database node that answers natural-language questions against a ClickHouse database by translating them into SQL with an LLM.

What it does

Plays two roles: a pipeline node (natural-language questions arrive on a lane, results leave as a table, text, or structured answers) and a tool node (an agent calls get_data, get_schema, or get_sql directly).

Connects over ClickHouse's native TCP protocol (default port 9000) using clickhouse-sqlalchemy with the clickhouse-driver backend (clickhouse+native:// DSN). Generated SQL is validated with EXPLAIN against the live database before execution; if validation fails the error is fed back to the LLM for a corrected query, repeating up to max_attempts times.

The node is read-only by default: the natural-language path only ever runs SELECT. Raw SQL execution (QuestionType.EXECUTE) is gated behind the allow_execute toggle, which is off by default and intended only for trusted callers. This is also a query/read node: it does not expose a pipeline ingestion (insert) lane (see Ingestion).

---

Connections

Connection	Required	Description
llm	yes (min 1)	LLM used to generate SQL from natural language

---

Available tools

When connected to an agent, the node exposes three functions:

Tool	Description
get_data	Natural language to SQL, executes it, returns rows (default 250, max 25 000)
get_schema	Returns tables, columns, types, and primary keys
get_sql	Natural language to SQL only, no execution

Only SELECT is permitted for generated queries.

---

Configuration

Lanes

Lane in	Lane out	Description
questions	table	Translate question to SQL, execute, return as table
questions	text	Translate question to SQL, execute, return as text
questions	answers	Translate question to SQL, execute, return as answers

Fields

Field	Type	Description
host	string	Default "localhost". Host name or IP address of the ClickHouse server, optionally including a native-protocol port (e.g. localhost:9440). Defaults to port 9000 when none is given.
user	string	Default "default". User to connect to the ClickHouse server
password	string	Password to connect to the ClickHouse server
database	string	Default "default". Name of database
tls	boolean	Default false. Connect over TLS. Required for managed services such as ClickHouse Cloud (native TLS port 9440 is assumed when the host has no explicit port). Leave OFF for a plaintext local server on port 9000. ClickHouse-specific, MySQL/PostgreSQL nodes do not expose this.
table	string	Default "table". Name of table
db_description	string	Default empty. What is this database used for? Describe its content and purpose, this helps the LLM generate more accurate queries.
max_attempts	integer	Default 5. Maximum number of times to re-ask the LLM if EXPLAIN rejects the generated SQL
allow_execute	boolean	Default false. Permit QuestionType.EXECUTE callers to run raw SQL without LLM translation or safety checks. Leave OFF unless a trusted application explicitly needs to issue SQL directly.
profile	string	Default "default".

The node ships a single default profile that pre-sets database: default.

---

SQL validation

Generated SQL is validated by running EXPLAIN against the live database. If validation fails, the error is fed back to the LLM for a corrected query. This repeats up to max_attempts times before the node raises an error. The retry limit is clamped to 1-20 regardless of the value stored in config.

---

ClickHouse Cloud

To connect to a ClickHouse Cloud service:

1. In the Cloud console, open your service, choose Connect, and copy the native endpoint host (e.g. abc123.us-east-1.aws.clickhouse.cloud) and the default user password.
2. Configure the node with: host = that hostname (no port needed, TLS port 9440 is assumed), user = default, password = your service password, tls = ON.
3. Make sure your machine's IP is allowed under the service's IP Access List (or set it to "Anywhere" for testing).

---

Ingestion

Unlike the MySQL/PostgreSQL nodes, this node intentionally does not expose the ingestion/input answers lane (used for pipeline inserts). This removes only that input lane, not the questions to answers output lane used for querying, which still works. The shared auto-create-table helper builds tables with an auto-increment integer primary key and no table engine, neither of which exists in ClickHouse (tables require an explicit engine such as MergeTree), so the inherited insert/auto-create path cannot work here. Create your tables in ClickHouse directly, and use this node for querying. A ClickHouse-correct ingestion path can be added later as a separate feature.

---

Notes

• ClickHouse is column-oriented and has no foreign keys; the reflected schema therefore exposes columns and (best-effort) primary keys but no FK relationships.
• The tls config field is distinct from clickhouse-driver's ?secure=true DSN parameter (which the node sets on the wire when TLS is enabled) and from the field-level "secure": true attribute on the password field, which only marks the value as a masked secret.
• user, password, and database are URL-encoded when building the connection string, so reserved characters (@, /, #, :) in credentials are safe.
• Bracketed IPv6 literals (e.g. [::1]) are supported in host; a port is only detected when a : follows the closing ].

---

Schema

Field	Type	Description	Default
clickhouse.allow_execute	boolean	Allow direct query execution Permit QuestionType.EXECUTE callers to run raw SQL without LLM translation or safety checks. Leave OFF unless a trusted application explicitly needs to issue SQL directly.	false
clickhouse.database	string	Database name Name of database	"default"
clickhouse.db_description	string	Database description What is this database used for? Describe its content and purpose, this helps the LLM generate more accurate queries.	""
clickhouse.host	string	ClickHouse host Host name or IP address of the ClickHouse server, optionally including a native-protocol port (e.g. localhost:9440). Defaults to port 9000 when none is given.	"localhost"
clickhouse.max_attempts	integer	Max validation attempts Maximum number of times to re-ask the LLM if EXPLAIN rejects the generated SQL	5
clickhouse.password	string	Password Password to connect to the ClickHouse server
clickhouse.profile	string		"default"
clickhouse.table	string	Table name Name of table	"table"
clickhouse.tls	boolean	Use TLS Connect over TLS. Required for managed services such as ClickHouse Cloud (native TLS port 9440 is assumed when the host has no explicit port). Leave OFF for a plaintext local server on port 9000. ClickHouse-specific, MySQL/PostgreSQL nodes do not expose this.	false
clickhouse.user	string	User User to connect to the ClickHouse server	"default"

Dependencies

• clickhouse-sqlalchemy ==0.3.2
• clickhouse-driver ==0.2.9