ClickHouse

Installation

1. Install 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 vulcan.yaml, and enable the extension.

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

3. Create a new profile in profiles.yaml or in your profile files. 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

Please check arguments of ClickHouse Client for further information.

Name	Required	Default	Description
host	N	http://localhost:8123	ClickHouse instance URL.
request_timeout	N	30000	The request timeout in milliseconds.
max_open_connections	N	Infinity	Maximum number of sockets to allow per host.
compression	N		Data applications operating with large datasets over the wire can benefit from enabling compression. Currently, only GZIP is supported using zlib.Please see Compression docs.
username	N	default	The name of the user on whose behalf requests are made.
password	N		The user 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. please see the Advance Settings, and Definition
tls	N		Configure TLS certificates. Please see TLS docs.
session_id	N		ClickHouse Session ID to send with every request.
keep_alive	N		HTTP Keep-Alive related settings. Please see Keep Alive docs

At the above, it not contains log option, because the logs need to define a Logger class and assign to it, so it could not set by profiles.yaml.

Note

The ClickHouse support parameterize query to prevent SQL Injection by prepared statement. The named placeholder define by {name:type}, please see Query with Parameters.

However, The VulcanSQL API support API query parameter is JSON format, so it not support variety types like ClickHouse, The VulcanSQL will only support to convert below types:

• boolean to Boolean ClickHouse type
• number to Int or Float ClickHouse type
• string to String ClickHouse type

Therefore, if you would like to query the data is a special type from ClickHouse, e.g: Array(Unit8), Record<K, V>, Date, DateTime ...etc, you could use the ClickHouse Regular Function or Type Conversion Function to do it.

Example:

-- If the val from API query parameter is '1990-11-01', and the born_date columns type is Date32 type
-- What is the toDate function, please see https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions#todate
SELECT * FROM users WHERE born_date = toDate({val:String});

⚠️ Caution

ClickHouse driver currently not yet support for caching datasets feature. If you use the ClickHouse driver with caching dataset feature, it will be failed.