Creating a ClickHouse connection
To create a ClickHouse connection:
Go to the workbook page.
In the top-right corner, click Create → Connection.
Select a ClickHouse connection.
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.
The user should have one of the following values set for the parameter readonly:
0: All queries are allowed.
2: Queries to read data and edit settings are allowed.
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.
Click Create connection.
Enter a name for the connection and click Create.
You can check the host connection before creating it. To do this, click Check connection.
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
CA Certificate: To upload a certificate , click Attach file and specify 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.
Specifics for ClickHouse connections
In ClickHouse, you can create a dataset on top of a
VIEW that contains a
JOIN. To do this, make sure a view is created with the
join_use_nulls option enabled. We recommend that you set
join_use_nulls = 1 in the
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 a JOIN 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 corresponding fields to Nullable.