Create a ClickHouse Data Source
Follow these steps to connect to ClickHouse and create a ClickHouse data source:
Refer to Additional Information for additional performance tips and information about ClickHouse feature support.
Configure User Access Rights
We recommend that you create a dedicated user and user role for integration with the GoodData platform.
Steps:
Create a user role and grant access rights:
CREATE ROLE {role_name}; GRANT SELECT ON {database_name}.* TO {role_name};
Create a user and grant it the user role:
CREATE USER {user_name}; GRANT {role_name} TO {user_name};
Make the user role default for the user:
ALTER USER {user_name} DEFAULT ROLE {role_name};
Create a ClickHouse Data Source
Once you have configured your ClickHouse user’s access rights, you can proceed to create a ClickHouse data source that you can then connect to.
Steps:
On the home page switch to Data sources.
Click Connect data.
Select Clickhouse.
Name your data source and fill in your Clickhouse credentials and click Connect:
Click Save.
Your data source is created!
Steps:
Create a ClickHouse data source with the following API call:
curl $HOST_URL/api/v1/entities/dataSources \ -H "Content-Type: application/vnd.gooddata.api+json" \ -H "Accept: application/vnd.gooddata.api+json" \ -H "Authorization: Bearer $API_TOKEN" \ -X POST \ -d '{ "data": { "type": "dataSource", "id": "<unique_id_for_the_data_source>", "attributes": { "name": "<data_source_display_name>", "url": "jdbc:clickhouse:https://<CLICKHOUSE_HOST>:8443/<CLICKHOUSE_DBNAME>", "schema": "<CLICKHOUSE_DBNAME>", "type": "CLICKHOUSE", "username": "<CLICKHOUSE_USER>", "password": "<CLICKHOUSE_PASSWORD>" }}}' | jq .
To confirm that the data source has been created, ensure the server returns the following response:
{ "data": { "type": "dataSource", "id": "<unique_id_for_the_data_source>", "attributes": { "name": "<data_source_display_name>", "url": "jdbc:clickhouse:https://<CLICKHOUSE_HOST>:8443/<CLICKHOUSE_DBNAME>", "schema": "<CLICKHOUSE_DBNAME>", "type": "CLICKHOUSE", "username": "<CLICKHOUSE_USER>" } }, "links": { "self": "$HOST_URL/api/v1/entities/dataSources/<unique_id_for_the_data_source>" } }
Additional Information
Ensure you understand the following limitations and recommended practice.
Data Source Details
A typical JDBC URL may look like this:
jdbc:clickhouse:https://<host>:<port>/<databaseName>
For a secured connection using SSL, include
https
or?ssl=true
.Refer to the ClickHouse documentation to learn more about how the JDBC URL may be formatted.
Basic authentication is supported. Specify
user
andpassword
.GoodData uses up-to-date drivers.
Our Clickhouse dialect is tested against Clickhouse version 25.1.4.
Limitations
GoodData requires that all strings are encoded in UTF-8 or ASCII, which is a subset of UTF-8.
Clickhouse does not support foreign keys. An automatically generated LDM must rely on column naming conventions when it comes to generating relations among datasets.
A visualization may display
NO DATA FOR YOUR FILTER SELECTION
if one of the metrics returns no data, instead of showing data from other metrics that have valid results. This issue is due to a known bug in the ClickHouse database.For date datasets, we use the DateTime type with a supported range from
1970-01-01 00:00:00
to2106-02-07 06:28:15
.
Unsupported Features
- The following functions are not supported:
- PERCENT_RANK
- RSQ
- INTERCEPT
- boolean columns are not supported
- YEAR_FOR_WEEK Date dimension granularity in chronological queries is not supported
Supported URL Parameters
- ssl
- sslmode