How to create a ClickHouse® connection

Note

All data requests must be made with the join_use_nulls flag enabled. See Specifics of using a connection to ClickHouse® if you are using views or subqueries with the JOIN section in DataLens.

To create a ClickHouse® connection:

  1. Go to the workbook page or create a new one.

  2. In the top-right corner, click Create → Connection.

  3. Select a ClickHouse® connection.

  4. Specify the connection parameters for the external ClickHouse® database:

    • Hostname: Specify the path to a master host or a ClickHouse® master host IP address. You can specify multiple hosts in a comma-separated list. If you are unable to connect to the first host, DataLens will select the next one from the list.

    • HTTP interface port: Specify the ClickHouse® connection port. The default port is 8443.

    • Username: Specify the username for the ClickHouse® connection.

      Warning

      The user must have the readonly parameter set to one of the following values:

      • 0: Allows all requests.

      • 1: Allows only data read requests. In this case, specify the following in the ClickHouse® settings:

        • join_use_nulls = 1
        • send_progress_in_http_headers = 0
        • output_format_json_quote_denormals = 1

        For DataLens, set the Readonly parameter to 1 in the connection's advanced settings.

      • 2: Allows requests to read data and edit settings.

    • Password: Enter the password for the user.

    • Cache TTL in seconds: Specify the cache time-to-live or leave the default value. The recommended value is 300 seconds (5 minutes).

    • Raw SQL level: Enables you to use an ad-hoc SQL query to generate a dataset.

    image

  5. (Optional) Test the connection. To do this, click Check connection.

  6. Click Create connection.

  7. Enter a name for the connection and click Create.

Additional settings

You can specify additional connection settings in the Advanced connection settings section:

  • TLS: If this option is enabled, the DB is accessed via HTTPS; if not, via HTTP.

  • CA Certificate: To upload a certificate, click Attach file and select the certificate file. When the certificate is uploaded, the field shows the file name.

  • Disable data export: When this option is enabled, the export data button will be hidden in the charts based on this connection. However, you will still be able to copy chart data and take screenshots.

  • Readonly: Select a permission for requests to read data, write data, and change parameters. This setting must not exceed the user's corresponding setting in ClickHouse®:

    • 0: Allows all requests.
    • 1: Allows only data read requests.
    • 2: Allows requests to read data and edit settings.

Specifics of using a connection to ClickHouse®

In ClickHouse®, you can create a dataset on top of a VIEW that contains the JOIN section. To do this, make sure a view is created with the join_use_nulls option enabled. We recommend setting join_use_nulls = 1 in the SETTINGS section:

CREATE VIEW ... (
    ...
) AS
    SELECT
        ...
    FROM
        ...
    SETTINGS join_use_nulls = 1

You should also enable this option for raw-sql subqueries that are used as a data source in your dataset.

To avoid errors when using views with the JOIN section in DataLens, re-create all views and set join_use_nulls = 1. This fills in empty cells with NULL values and converts the type of the relevant fields to Nullable.

ClickHouse® is a registered trademark of ClickHouse, Inc.