ClickHouse

View as Markdown

A RocketRide database node for asking natural-language questions of ClickHouse with an LLM, either on pipeline lanes or through agent tools. Choose it when your data is in ClickHouse and you need its native-protocol connection.

About ClickHouse

ClickHouse is the database product this node connects to. This implementation uses the clickhouse-sqlalchemy dialect with the clickhouse-driver backend over ClickHouse's native TCP protocol.

What it does

On the questions lane, the node gives its reflected database schema and optional database description to the connected LLM, asks it for a SELECT, validates that query with EXPLAIN, and sends the result to the requested table, text, or answers lane. It is also an agent tool node, so an agent can retrieve data, inspect the startup-reflected schema, generate SQL, or—when explicitly enabled—run raw SQL. Unlike a write-oriented relational database node, its declared lanes contain no data-ingestion input.

Connections

Connection	Required	Description
llm	yes	LLM used to craft SQL queries from questions

Lanes

Lane in	Lane out	Description
questions	table	Send query results as a Markdown table.
questions	text	Send the result as text.
questions	answers	Send the result as an answer.

As a tool

The registered tool names are bare method names; this node declares no configurable server-name prefix.

Function	Description
get_data	Generate a safe SELECT from a question and execute it.
get_schema	Return the schema reflected when the node started.
get_sql	Generate a safe SELECT without executing it.
execute	Run raw SQL, bypassing LLM translation and the safety check.
begin	Open a transaction and return its session ID.
commit	Commit and close a transaction session.
rollback	Roll back and close a transaction session.
dialect	Return { "dialect": "clickhouse" }.

get_data and get_sql require a non-empty question; get_data accepts an optional limit, defaulting to 250 and clamped to 1–25,000. get_schema accepts an optional table; an unknown table returns an error field, while omitting it returns all reflected tables. get_data returns {valid, rows, sql, row_limit} on success; a non-database question returns {valid: false, answer}, and a query execution failure returns {valid: false, error, sql, rows: []}.

execute requires non-empty sql and optionally accepts a transaction session_id plus positional values for $1, $2, and so on. It returns {rows, affected_rows}. begin takes no arguments and returns {session_id}; commit and rollback require that ID and return {ok: true}. These four write-capable operations fail when Allow direct query execution is off; unknown or expired session IDs also fail. Invalid tool input raises an error.

Configuration

Start with the default connection values, then set the database endpoint and target table. Most question-answering setups need the host, credentials, database, and a useful database description; the generated schema below is the complete field reference.

Database description

This text is added to every LLM SQL-generation request alongside the reflected schema. Describe the data's purpose, meanings, and conventions when names alone are ambiguous—for example, explain whether amount is a gross or net value. Change it when queries choose the wrong interpretation; it does not replace the actual table and column information collected at startup.

ClickHouse host and TLS

With TLS off, a host without an explicit port uses ClickHouse's native default of 9000. Turn TLS on for a TLS endpoint: the node adds secure=true to the native DSN and supplies port 9440 if no port is present. Keep TLS off for a plaintext local server. User, password, and database are URL-encoded when the connection string is built, so reserved characters in those values are safe.

Target database and table

The database selects the connection's database; the table identifies the target table whose schema is also cached at startup. Set both to the exact objects the LLM should use. The node reflects the full database for LLM context, but it warns if this particular target table does not exist. Since the declared lanes do not accept data for insertion, create and populate ClickHouse tables outside this node.

Max validation attempts

The default of five gives the LLM several chances to repair a query rejected by EXPLAIN. Lower it when fast failure matters more than recovery; raise it for complex schemas that need another correction cycle. The runtime clamps this setting to 1–20. After the final rejected attempt it returns the last generated result, so EXPLAIN retries improve generated SQL but do not guarantee that a subsequent execution will succeed.

Allow direct query execution

Leave this off by default. When on, the execute, begin, commit, and rollback tools can bypass LLM translation and the generated-query safety check, and raw statements use a transaction that commits on success. Enable it only for trusted callers that need writes or dialect-specific SQL; use get_data for ordinary read-only retrieval.

Limitations

This node declares the noremote capability, so it cannot run in a remote execution environment. It requires network reachability to the ClickHouse server from the environment where the pipeline runs.

Notes

Generated-query safety

For LLM-generated SQL, the node only accepts SELECT, optionally preceded by EXPLAIN. It strips comments, checks every semicolon-separated statement, and rejects CTEs plus SELECT ... INTO OUTFILE or INTO DUMPFILE. It validates each safe candidate with EXPLAIN and feeds database errors to the LLM for the next attempt.

ClickHouse schema details

The ClickHouse driver reports no foreign keys, so reflected schema output has columns and best-effort primary keys but no foreign-key relationships.

Upstream docs

• ClickHouse documentation

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