Connect ClickHouse

​

Before you start

• A ClickHouse user that can create users, roles and databases and grant privileges.
• The tables or views Kubit should query.
• A password for the Kubit user. The Kubit team generates a strong one and shares it with you.

​

Steps

1

Create a user

In the SQL Console, create a user named KUBIT. The Kubit team generates and shares a strong password to use here.

CREATE USER KUBIT IDENTIFIED WITH sha256_password BY '*****';

2

Create roles

Create a read role and a write role.

CREATE ROLE OR REPLACE KUBIT_R;
CREATE ROLE OR REPLACE KUBIT_W;
GRANT KUBIT_R TO KUBIT;
GRANT KUBIT_W TO KUBIT;

3

Create a database

Create a database named KUBIT_DB. Kubit uses it for small auxiliary tables and views.

CREATE DATABASE KUBIT_DB;

4

Grant permissions

Grant KUBIT_W read and write access to KUBIT_DB so Kubit can create its auxiliary objects.

GRANT SELECT,INSERT,ALTER,CREATE,DROP,TRUNCATE,SHOW ON KUBIT_DB.* TO KUBIT_W;

Grant KUBIT_R read-only (SELECT) access to KUBIT_DB and the tables or views Kubit should query.

GRANT SELECT ON KUBIT_DB TO KUBIT_R;
GRANT SELECT ON INFORMATION_SCHEMA.* TO KUBIT_R;
GRANT SELECT ON system.parts TO KUBIT_R;
GRANT SELECT ON <your_table/or_view> TO KUBIT_R;

5

Allow Kubit's IP ranges

Add Kubit’s IP ranges to your ClickHouse IP filters.

6

Split test and production workloads (optional)

As a best practice, create a second user, roles and service for development. Isolating development from production avoids accidental impact on live workloads.

7

Setting up the connection in Kubit

When configuring the JDBC URL in Kubit you will need to pass on the &compress=0 parameter.

​

Connection parameters

​

If it fails

​

Next steps

• Bring your own warehouse: how Kubit queries your warehouse in place.
• Allow Kubit’s IP ranges: the current ranges and how to keep firewall rules up to date.
• Setup Kubit in the Modeler: map your tables to fact tables, dimensions and subjects once the connection works.