ClickHouse

Installation

1. Install the package:

   If you are developing with binary, the package is already bundled in the binary. You can skip this step.

   npm i @vulcan-sql/extension-driver-clickhouse

2. Update your vulcan.yaml file to enable the extension:

   extensions:
     ...
     ch: '@vulcan-sql/extension-driver-clickhouse' # Add this line

3. Create a new profile in your profiles.yaml file or in the designated profile paths. For example:

   - name: ch # profile name
     type: clickhouse
     connection:
       host: www.example.com:8123
       request_timeout: 60000
       compression:
         request: true
       max_open_connections: 10
       username: user
       password: pass
       database: hello-clickhouse
     allow: '*'

Configuration

For more information, please refer to the ClickHouse Client documentation to learn about the available arguments for the ClickHouse Client.

Name	Required	Default	Description
host	N	http://localhost:8123	ClickHouse instance URL.
request_timeout	N	30000	Request timeout in milliseconds.
max_open_connections	N	Infinity	Maximum number of sockets to allow per host.
compression	N		Compression settings for data transfer. Currently, only GZIP compression using zlib is supported. See Compression docs for details.
username	N	default	The name of the user on whose behalf requests are made.
password	N		The user's password.
application	N	VulcanSQL	The name of the application using the Node.js client.
database	N	default	Database name to use.
clickhouse_settings	N		ClickHouse settings to apply to all requests. For all available settings, see Advance Settings, and For the definition, see Definition
tls	N		Configure TLS certificates. See TLS docs.
session_id	N		ClickHouse Session ID to send with every request.
keep_alive	N		HTTP Keep-Alive related settings. See Keep Alive docs

The log option is not included above because it requires defining a Logger class and assigning it. Therefore, it cannot be set through profiles.yaml.

Note

ClickHouse supports parameterized queries to prevent SQL injection using prepared statements. Named placeholders are defined using the {name:type} syntax. For more information, refer to the Query with Parameters section in the ClickHouse documentation.

However, the VulcanSQL API supports JSON format for API query parameters and does not support the full range of types available in ClickHouse. VulcanSQL only supports the conversion of the following types:

• boolean to ClickHouse type Bool
• number to ClickHouse types Int or Float
• string to ClickHouse type String

Therefore, if you need to query data with special types in ClickHouse, such as Array(Unit8), Record<K, V>, Date, DateTime, and so on, you can use ClickHouse Regular Functions or Type Conversion Functions to handle them.

Example:

-- If the `val` from the API query parameter is '1990-11-01' and the `born_date` column is of type `Date32`,
-- you can use the toDate function to convert the value. See https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions#todate
SELECT * FROM users WHERE born_date = toDate({val:String});

⚠️ Caution

The ClickHouse driver currently does not support caching datasets. If you use the ClickHouse driver with caching dataset features, it will result in failure.