[Yandex Cloud documentation](../../../index.md) > [Yandex DataLens](../../index.md) > Connections > Databases > Creating a ClickHouse® connection # Creating a connection to ClickHouse® from Yandex DataLens {% 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 %} {% note warning %} Yandex Cloud holds no responsibility for configuring a remote connection on an external database server side. When connecting an external database that is not a Yandex Cloud resource: * When using your own SSL certificate, make sure it matches the one on the database side. * Grant database access to the following DataLens IP ranges (`/` is followed by the subnet mask length): {% list tabs group=ip_address %} - ipv4 {#ipv4} * `178.154.242.176/28` * `178.154.242.192/28` * `178.154.242.208/28` * `178.154.242.128/28` * `178.154.242.144/28` * `178.154.242.160/28` * `130.193.60.0/28` - ipv6 {#ipv6} * `2a02:6b8:c03:500:0:f83d:a987:0/112` * `2a02:6b8:c02:900:0:f83d:a987:0/112` * `2a02:6b8:c0e:500:0:f83d:a987:0/112` * `2a02:6b8:c41:1300:0:f83d:a987:0/112` {% endlist %} {% endnote %} To create a ClickHouse® connection: 1. Open the [new connection creation page](https://datalens.ru/connections/new). 1. Under **Databases**, select the **ClickHouse®** connection. 1. Select the connection type: {% list tabs group=connection %} - Select in organization {#organization} Select a managed database in the current Yandex Cloud organization and specify the internal network connection parameters for it: * **Cloud and folder**: Select the folder the cluster is located in. * **Cluster**: Specify the cluster from the list of available ClickHouse® clusters. Cluster settings must have the **DataLens access** option enabled. If you do not have an available cluster, click **Create new**. {% note info %} The list shows the following clusters: * With permissions for the user creating the connection. * Created in the same [organization](../../concepts/organizations.md) as the DataLens instance. {% endnote %} * **Host type**: Select the host type: * **Regular** (default): Allows you to select regular hosts to connect to. * **Special FQDNs**: Allows you to select a [special FQDN](../../../managed-clickhouse/operations/connect/fqdn.md#auto) to connect to an available ClickHouse® cluster host. * **Host name**: Select the host name from the list of hosts available in the ClickHouse® cluster. You can select multiple hosts. 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 the username for the ClickHouse® connection. {% 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 %} * **Password**: Enter the password for the user. * **Cache TTL in seconds**: Specify the cache TTL 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](../../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: * **Allow subqueries in datasets**: Describe dataset sources using [SQL queries](../../dataset/settings.md#sql-request-in-datatset). * **Allow subqueries in datasets and source parameterization**: Describe dataset sources using SQL queries and use [source parameterization](../../dataset/settings.md#parametrization). * **Allow subqueries in datasets, source parameterization, and QL charts**: Describe dataset sources using SQL queries, use source parameterization, and [create QL charts](../../concepts/chart/ql-charts.md). Click **Check connection** to make sure the parameters are correct. - Specify manually {#manual} Manually specify your external database details to connect to via a public network: - **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 the username for the ClickHouse® connection. {% 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 %} - **Password**: Enter the password for the user. - **Cache TTL in seconds**: Specify the cache TTL 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](../../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: * **Allow subqueries in datasets**: Describe dataset sources using [SQL queries](../../dataset/settings.md#sql-request-in-datatset). * **Allow subqueries in datasets and source parameterization**: Describe dataset sources using SQL queries and use [source parameterization](../../dataset/settings.md#parametrization). * **Allow subqueries in datasets, source parameterization, and QL charts**: Describe dataset sources using SQL queries, use source parameterization, and [create QL charts](../../concepts/chart/ql-charts.md). ![image](../../../_assets/datalens/operations/connection/connection-clickhouse.png) Click **Check connection** to make sure the parameters are correct. - Connection Manager {#conn-man} {% note info %} To use a Connection Manager connection in DataLens, the user must have the `connection-manager.user` [role](../../../metadata-hub/security/connection-manager-roles.md) for this connection. {% endnote %} Select the [connection](../../../metadata-hub/concepts/connection-manager.md) to a ClickHouse® managed database cluster created in Yandex Connection Manager: * **Cloud and folder**: Select the folder where you created the connection to the cluster. * **Connection ID**: Select an available connection in Connection Manager or [create a new one](../../../metadata-hub/operations/create-connection.md). * **Host**: Select the host from the list of available hosts in the ClickHouse® cluster. * **Port**: It is set automatically depending on the selected host. * **Username**: It is set automatically from the selected connection data. * **Cache TTL in seconds**: Specify the cache TTL or leave the default value. The recommended value is 300 seconds (5 minutes). - **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 the username for the ClickHouse® connection. {% 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 %} - **Password**: Enter the password for the user. - **Cache TTL in seconds**: Specify the cache TTL 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](../../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: * **Allow subqueries in datasets**: Describe dataset sources using [SQL queries](../../dataset/settings.md#sql-request-in-datatset). * **Allow subqueries in datasets and source parameterization**: Describe dataset sources using SQL queries and use [source parameterization](../../dataset/settings.md#parametrization). * **Allow subqueries in datasets, source parameterization, and QL charts**: Describe dataset sources using SQL queries, use source parameterization, and [create QL charts](../../concepts/chart/ql-charts.md). {% endlist %} 1. Click **Create connection**. 1. Select a [workbook](../../workbooks-collections/index.md) to save your connection to or create a new one. If using legacy folder navigation, select a folder to save the connection to. Click **Create**. 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. * **Disable data export**: When this option is on, the data export item will not be available in the ![icon](../../../_assets/console-icons/ellipsis.svg) menu for the charts based on this connection. However, you will still be able to copy chart data and take screenshots. * **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). _ClickHouse® is a registered trademark of [ClickHouse, Inc](https://clickhouse.com)._ ## Use cases {#examples} * [Migrating databases from Google BigQuery to Yandex Managed Service for ClickHouse®](../../tutorials/bigquery-to-clickhouse.md) * [Retail chain's dashboard based on a ClickHouse® database](../../tutorials/data-from-ch-visualization.md) * [Geocoding with the Yandex Maps API for data visualization in DataLens](../../tutorials/data-from-ch-geocoder.md) * [Examples of creating QL charts](../../tutorials/data-from-ch-to-sql-chart.md)