Creating a connection to ClickHouse® from Yandex DataLens

To create a ClickHouse® connection:

1. Open the new connection creation page.

2. Under Databases, select the ClickHouse® connection.

3. Select the connection type:

   Select in organization

   Specify manually

   Connection Manager

   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

     The list shows the following clusters:

     • With permissions for the user creating the connection.
     • Created in the same organization as the DataLens instance.

   • 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 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 a username for the ClickHouse® connection.

     Warning

     The user must have the 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:

       • 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.

   • 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).

   • Raw SQL level: Enables you to use an ad-hoc SQL query to generate a dataset. 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.
     • Subqueries and parameters: Describe dataset sources using SQL queries and use source parameterization.
     • SQL to read: Describe dataset sources using SQL queries, use source parameterization, and create QL charts.

   Click Check connection to make sure the parameters are correct.

   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 a username for the ClickHouse® connection.

     Warning

     The user must have the 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:

       • 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.

   • 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).

   • Raw SQL level: Enables you to use an ad-hoc SQL query to generate a dataset. 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.
     • Subqueries and parameters: Describe dataset sources using SQL queries and use source parameterization.
     • SQL to read: Describe dataset sources using SQL queries, use source parameterization, create QL charts, and execute read-only SQL queries.

   Click Check connection to make sure the parameters are correct.

   Note

   To use a Connection Manager connection in DataLens, the user must have the connection-manager.user role for this connection.

   Select the connection 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.
   • 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 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 a username for the ClickHouse® connection.

     Warning

     The user must have the 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:

       • 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.

   • 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).

   • Raw SQL level: Enables you to use an ad-hoc SQL query to generate a dataset. 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.
     • Subqueries and parameters: Describe dataset sources using SQL queries and use source parameterization.
     • SQL to read: Describe dataset sources using SQL queries, use source parameterization, create QL charts, and execute read-only SQL queries.

4. Click Create connection.

5. Select a workbook 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.

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

Additional settingsAdditional 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 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®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.

Use casesUse cases

• Migrating databases from Google BigQuery to Yandex Managed Service for ClickHouse®
• Retail chain's dashboard based on a ClickHouse® database
• Geocoding with the Yandex Maps API for data visualization in DataLens
• Examples of creating QL charts