Skip to main content

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.

NameRequiredDefaultDescription
hostNhttp://localhost:8123ClickHouse instance URL.
request_timeoutN30000The request timeout in milliseconds.
max_open_connectionsNInfinityMaximum number of sockets to allow per host.
compressionNData applications operating with large datasets over the wire can benefit from enabling compression. Currently, only GZIP is supported using zlib.Please see Compression docs.
usernameNdefaultThe name of the user on whose behalf requests are made.
passwordNThe user password.
applicationNVulcanSQLThe name of the application using the Node.js client.
databaseNdefaultDatabase name to use.
clickhouse_settingsNClickHouse settings to apply to all requests. please see the Advance Settings, and Definition
tlsNConfigure TLS certificates. Please see TLS docs.
session_idNClickHouse Session ID to send with every request.
keep_aliveNHTTP 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 parameterized 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.