---
metadata:
  - name: generator
    content: Diplodoc Platform v5.63.0
alternate:
  - en/operations/connection/create-clickhouse
  - ru/operations/connection/create-clickhouse
  - href: en/operations/connection/create-clickhouse.md
    type: text/markdown
    title: Markdown version
csp:
  - script-src:
      - https://mc.yandex.ru
    img-src:
      - https://mc.yandex.ru
    connect-src:
      - https://mc.yandex.ru
      - wss://mc.yandex.ru
    child-src:
      - 'blob:'
      - https://mc.yandex.ru
    frame-src:
      - 'blob:'
      - https://mc.yandex.ru
    frame-ancestors:
      - 'blob:'
      - https://mc.yandex.ru
canonical: en/operations/connection/create-clickhouse.html
title: How to create a ClickHouse® connection in DataLens On-premises
description: >-
  Follow this guide to create a connection to ClickHouse® in DataLens
  On-premises.
vcsPath: en/operations/connection/create-clickhouse.md
---

# Creating a connection to ClickHouse® from DataLens On-premises

{% note info %}

All data queries must be made with the [join_use_nulls](https://clickhouse.com/docs/enen/operations/settings/settings#join_use_nulls) flag enabled. See [Specifics of using a connection to ClickHouse®](#ch-connection-specify) if you are using views or subqueries with the JOIN section in DataLens.

{% endnote %}



To create a ClickHouse® connection:

1. Go to the [workbook](../../workbooks-collections/index.md) page or create a new one.
1. In the top-right corner, click **Create** → **Connection**.
1. Select the **ClickHouse®** connection.
1. Specify the connection parameters for the external ClickHouse® database:

   {% include [datalens-db-connection-parameters](../../_includes/datalens/datalens-db-connection-parameters.md) %}

1. Optionally, test the connection by clicking **Check connection**.
1. Click **Create connection**.
1. Enter a name for the connection and click **Create**.


## Additional settings {#clickhouse-additional-settings}

You can specify additional connection settings under **Advanced connection settings**:

* **TLS**: If this option is enabled, the database is accessed over `HTTPS`; if not, over `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.

* <!-- source: en/_includes/datalens/operations/datalens-db-connection-export-settings-item.md -->
  **Disable data export**: When this option is on, the data export item will not be available in the <!-- diplodoc:svg ![icon](../../_assets/console-icons/ellipsis.svg) --><svg xmlns="http://www.w3.org/2000/svg" width="16" height="16" fill="none" viewBox="0 0 16 16"><path fill="currentColor" fill-rule="evenodd" d="M3 9.5a1.5 1.5 0 1 0 0-3 1.5 1.5 0 0 0 0 3M9.5 8a1.5 1.5 0 1 1-3 0 1.5 1.5 0 0 1 3 0m5 0a1.5 1.5 0 1 1-3 0 1.5 1.5 0 0 1 3 0" clip-rule="evenodd"/></svg> menu for the charts based on this connection. However, you will still be able to copy chart data and take screenshots.
  <!-- endsource: en/_includes/datalens/operations/datalens-db-connection-export-settings-item.md -->

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

  * `0`: Allows all queries.
  * `1`: Allows only data read queries.
  * `2`: Allows queries to read data and edit settings.


## Specifics of using a connection to ClickHouse® {#ch-connection-specify}

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:

```sql
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](https://clickhouse.com/docs/enen/sql-reference/data-types/nullable#data_type-nullable).

<!-- source: en/_includes/clickhouse-disclaimer.md -->
_ClickHouse® is a registered trademark of [ClickHouse, Inc](https://clickhouse.com)._
<!-- endsource: en/_includes/clickhouse-disclaimer.md -->


{% included (../../_includes/datalens/datalens-db-connection-parameters.md) %}
- **Host name**: 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 fail 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 a username for the ClickHouse® connection.

  <!-- source: en/_includes/datalens/datalens-db-note.md -->
  {% note warning %}
                
   The user must have the [readonly](https://clickhouse.com/docs/enen/operations/settings/permissions-for-queries#settings_readonly) parameter set to one of the following values:
           
    * `0`: Allows all queries.
    * `1`: Allows only data read queries. In this case, specify the following in the ClickHouse® [settings](https://clickhouse.com/docs/enen/operations/settings/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 queries to read data and edit settings.
            
  {% endnote %}
  <!-- endsource: en/_includes/datalens/datalens-db-note.md -->

- **Password**: Specify a password for the user.
- **Cache TTL in seconds**: Specify cache TTL or leave the default value. The recommended value is 300 seconds (5 minutes).

<!-- source: en/_includes/datalens/datalens-db-connection-sql-level-3.md -->
* **Raw SQL level**: Enables you to use an ad-hoc SQL query to [generate a dataset](../../dataset/settings.md#sql-request-in-datatset). This option is disabled by default. When enabling it, you will need to select the raw SQL level:

   * **Subqueries only**: Describe dataset sources using [SQL queries](../../dataset/settings.md#sql-request-in-datatset).
   * **Subqueries and parameters**: Describe dataset sources using SQL queries and use [source parameterization](../../dataset/settings.md#parametrization).
   * **SQL to read**: Describe dataset sources using SQL queries, use source parameterization, [create QL charts](../../concepts/chart/ql-charts.md), and execute read-only SQL queries.
<!-- endsource: en/_includes/datalens/datalens-db-connection-sql-level-3.md -->

{% endincluded %}
{% included (../../_includes/datalens/datalens-db-connection-parameters.md:datalens-db-note.md) %}
{% note warning %}
              
 The user must have the [readonly](https://clickhouse.com/docs/enen/operations/settings/permissions-for-queries#settings_readonly) parameter set to one of the following values:
         
  * `0`: Allows all queries.
  * `1`: Allows only data read queries. In this case, specify the following in the ClickHouse® [settings](https://clickhouse.com/docs/enen/operations/settings/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 queries to read data and edit settings.
          
{% endnote %}
{% endincluded %}
{% included (../../_includes/datalens/datalens-db-connection-parameters.md:./datalens-db-connection-sql-level-3.md) %}
* **Raw SQL level**: Enables you to use an ad-hoc SQL query to [generate a dataset](../../dataset/settings.md#sql-request-in-datatset). This option is disabled by default. When enabling it, you will need to select the raw SQL level:

   * **Subqueries only**: Describe dataset sources using [SQL queries](../../dataset/settings.md#sql-request-in-datatset).
   * **Subqueries and parameters**: Describe dataset sources using SQL queries and use [source parameterization](../../dataset/settings.md#parametrization).
   * **SQL to read**: Describe dataset sources using SQL queries, use source parameterization, [create QL charts](../../concepts/chart/ql-charts.md), and execute read-only SQL queries.

{% endincluded %}