Connecting a ClickHouse Database
Connect to ClickHouse
Here is what you need to connect:
- Read-only user (Username field)
- Whitelist the bipp IP address
- Database connection information
- Credentials
Create a ClickHouse read-only User
bipp recommends using a read-only user for connecting to your ClickHouse databases. You can create the read-only user with a settings profile, included with the server. A settings profile is a collection of settings grouped under the same name. The profile readonly allows read queries. The profile is located in /etc/clickhouse-server/users.xml:
<yandex>
<!-- Profiles of settings. -->
<profiles>
...
<!-- Profile that allows only read queries. -->
<readonly>
<readonly>1</readonly>
</readonly>
</profiles>
...
To create a read-only user, enter your information using the ClickHouse Client command line:
CREATE USER bipp_db_user IDENTIFIED WITH plaintext_password BY 'Secret@123' SETTINGS PROFILE 'readonly'
GRANT SHOW TABLES, SELECT ON your_database.* TO bipp_db_user
Assign your own user name and strong password for bipp_db_user and Secret@123.
Whitelist the bipp IP address
If your ClickHouse instance is behind the firewall, you need to whitelist the bipp IP Address to allow bipp to connect to your database. Use Direct Connection as the Connection type when you create the datasource.
app.bipp.io: 34.121.142.220
asia.bipp.io: 34.93.212.91
eu.bipp.io: 34.147.50.209
Create a ClickHouse Datasource in bipp
-
Select Datasources from the Home Navigation pane to open the Datasources page.
-
Click New Datasource on the Datasources page.
-
Enter a Name. For example, TestClickHouse.
-
Select ClickHouse for SQL Dialect for your database. bipp creates and enforces the rules for the selected dialect.
-
Enter the information for your ClickHouse database instance:
- Host: name or IP address of the database host.
- Port: port on the host for the connection.
- AltHosts: comma separated list of hosts for load-balancing. If your ClickHouse instance is not configured with any alternate hosts, enter the database host : port.
- Username: user name for the connection. bipp recommends using a read-only user account.
- Password: password for the user.
- DbName: name of the database for the connection.
- Connection: connection type
- Direct Connection: database directly available over internet or your bipp platform is deployed on-premise in a private subnet. If your database is behind a firewall. Whitelist the bipp IP address.
- SSH Tunnel: encrypted connection using Datasource ClickHouse SSH Port Forwarding. bipp supports both forward and reverse SSH tunnels. You can use the SshTunnelAuth to specify Key or Password for the authorization type. Here are the additional SSH fields:
- SshTunnelHost: name or IP address of the SSH tunnel authentication host.
- SshPort: port on the SSH tunnel authentication host for the connection.
- SshTunnelUser: user name for SSH tunnel authentication.
- Ssh TunnelAuth: select Key to use an SSH Private Key and optional SSH Passphrase, or select Password to use your SSH Password.
-
Click Test to verify the datasource connectivity. If you have an error, fix the connection or credentials problem and click Test again.
-
Click Create to add the datasource.
Congratulations! You have successfully created a ClickHouse datasource in bipp.
