Creating a ClickHouse® connection
Note
All data requests must be made with the join_use_nulls
Warning
When connecting an external database which 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 subnet mask length):ipv4ipv6178.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
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
To create a ClickHouse® connection:
-
Open the page for creating a new connection
. -
Under Databases, select the ClickHouse® connection.
-
Select the connection type:
Select in organizationSpecify manuallyConnection ManagerSelect a managed DB 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 a 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 who creates the connection.
- Created in the same organization as the DataLens instance.
-
Host type: Select a 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.
-
Hostname: Select the host name from the list of hosts available in the ClickHouse® cluster. You can select multiple hosts. 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 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 Check connection to make sure the parameters are correct.
Manually specify the parameters of an external database to connect to via a public network:
-
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 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 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 a connection to a ClickHouse® managed database cluster created in Yandex Connection Manager:
- Cloud and folder: Select the folder where the connection to the cluster was created.
- Connection ID: Select one of the available connections in Connection Manager or create a new one.
- Host: Select a 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 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.
-
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.
-
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, viaHTTP
. -
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.
Specifics for ClickHouse® connections
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