This article covers:

1. How to set up a connection to a Clickhouse database
2. How to add datasets
3. How to do connection overrides on a Clickhouse connection
4. The supported Clickhouse data types and the respective Luzmo data type they are mapped to

1. How to setup a Clickhouse connection

To make a connection to Clickhouse, navigate to the Connections page, select New Connection, then select the Clickhouse from the New Connection modal. You'll be asked to provide a host, key and token:

• host: this is the host where your Clickhouse database is exposed. Note that only https is allowed here in order to securely transfer data over the wire. The structure of the host url expects: https://url-to-clickhouse-db:port/database By default, the plugin will connect to the 'default' database and the 443 port. By providing a database after the '/' you can configure which database to connect to.
• key: the username that will be used to connect to your ClickHouse cluster.
• token: the password to connect to your ClickHouse cluster

Please refer to the examples in our developer documentation to find out how to create a connection to Clickhouse via our API.

Notes:

1. The Clickhouse connector uses the HTTP API interface (typically running on port 8123) to connect.
2. If you use tables with the Distributed table engine some Luzmo-charts might fail when distributed_product_mode is deny. This should only occur, however, if you link the table to another table and use that link in a chart. In that case make sure to set the distributed_product_mode to another option that makes sense for you within your Clickhouse cluster. If you are using Clickhouse Cloud you can safely ignore this setting.
3. To ensure that e.g. only the Luzmo application can access your Clickhouse instance, it is highly recommended to whitelist Luzmo's range of static IP addresses. You should whitelist Luzmo's range of IP addresses as described here.

2. How to add datasets

1. You can select one or multiple datasets as available in your Clickhouse and link them in Luzmo to ensure they can be used together in a dashboard.
2. You can also add SQL datasets by switching to the SQL tab in the dataset creation modal. While creating or editing a SQL dataset, you can parameterize anything within the query by specifying {{metadata.< parameter name >|< default value >}}. Find out more about parameterized SQL Datasets.

Also make sure to check out this article on Preparing your data for analytics.

To find out how to add datasets using our API, please refer to the examples in our developer documentation.

3. Clickhouse Connection Overrides

When generating an Authorization token to grant a user acces to your embedded dashboards it is possible to override the data source properties in the authorization request to dynamically use different properties. Find out more about connection overrides.

The fields available for overriding a Clickhouse connection are as follows:

• Connection ID: The ID of the Connection to be overridden. Retrieve the ID to specify as detailed here.
• host: The new database host to connect to, excluding any database.
• key: The new username that will be used to connect to your ClickHouse cluster.
• token: The new password to connect to your ClickHouse cluster.
• schema: The new database to connect to
• datasets: List of dataset-level overrides. Useful if you want to override only a single dataset in your dashboard or if you have a separate table per client.
  • schema: the new database to query
  • table: The new clickhouse table to query.

Our developer documentation has more info about connection overrides and an example of a Clickhouse connection override.

Example code demonstrating how to override a Clickhouse connection:

 import Luzmo from '@luzmo/nodejs-sdk';
const client = new Luzmo({
api_key: '<your Luzmo API key>',
api_token: '<your Luzmo API token>',
host: 'https://api.luzmo.com:443'
});


const response = await client.create('authorization',
{
  type: "embed",
  username: "< A unique and immutable identifier for your user >",
  name: "< user name >",
  email: "< user email >",
  suborganization: "< a suborganization name >",
  access: {
    collections: [
      
      {
        id: "<collection_id>",
        inheritRights: "use"
      }
    ]
  },
  account_overrides: {
    <your connection_id>: {
      host: "<The new database host URL to connect to>",
      key: "<username>",
      token: "<password>"
    }
  }
}
);

4. Supported Data Types