ClickHouse Integration

How to connect Locale with ClickHouse

ClickHouse is an open-source, column-oriented database management system that allows real-time analytical data processing. It's known for its lightning-fast queries, scalability, and flexibility in handling large datasets.

Here's how you can connect ClickHouse with Locale:

‍

Prerequisites:

• A running ClickHouse Server
• Your ClickHouse servers host and port addresses. If you do not have these details, you can obtain them from your database administrator.
• The permissions to create a new user from the ClickHouse server OR a the credentials of an existing user with permissions to read the required tables (more about this in step 2)

‍

Steps:

1. Whitelist Locale’s IP Addresses
2. Create a User and Grant Privileges
3. Retrieve the Hostname and Port Number (Optional)
4. Configure ClickHouse as a Data Connection on Locale

‍

Creating a Database User and Granting Privileges:

1. Create a database user: If you want to use an existing user with Locale, you can skip these steps. However, it is recommended to create a new user for Locale with only the necessary permissions.
   • Log in to your ClickHouse instance:
   • clickhouse-client --user default
   • Enter the following command to create a user:
   • CREATE USER localeuser IDENTIFIED WITH plaintext_password BY 'password';
   • Some ClickHouse servers don’t allow the default user to create and grant permissions to new users. If the above command returns an error for you, do the following:
   • • In the machine running your ClickHouse server, open the users.xml file
     • vim /etc/clickhouse-server/users.xml
     • Uncomment the line
     • <!-- <access_management>1</access_management> -->
     • Save and exit
2. Grant privileges to the user:
   • Use the following SQL query to grant privileges to the database user:
   • GRANT SELECT ON database.table TO 'localeuser';
   • Unfortunately you will have to run this for each table, if you want to give access to an entire database or multiple tables with a similar name you can use a wildcard
   • GRANT SELECT ON database.* TO 'localeuser'; GRANT SELECT ON database.data_* TO 'localeuser';

‍

Configure ClickHouse as a Data Connection on Locale:

1. Log in to your Locale command centre and click on the Data Sources option under the ⚙️ Org Settings, on the left-hand side menu.
2. Select ClickHouse Database from the available options.
3. Enter your database details:
   • Datasource Name: A unique name to identify the data source. You could have multiple ClickHouse databases connecting to Locale, so use this name to differentiate between them.
   • Host Address: The Domain address/IP Address of your database.
   • Port: The port on which the database accepts connections (The default value for ClickHouse is 8123).
   • Database Name: Enter the database name for the connection. (This won’t restrict access to other databases on this connection)
   • Username: Name of the user with the right privileges
   • Password: Password for the mentioned database user.
4. Optionally, if your ClickHouse database is behind a bastion host, you can enable SSH tunnelling through password or public key authentication [recommended].
5. Click on Test Connection and wait for the authentication to complete.
6. Once the connection is tested, click on Save Connection and start setting up your alerts.

Note: Locale will cancel queries that run for more than 5 minutes. This is to protect your database from running rogue queries and also to prevent a backlog of alerts in the Locale system. If you have a use case where this needs to be increased, please get in touch with our Support.

Happy Alerting! 🚀