Skip to main content

How to connect your own ClickHouse to Propel

This guide explains how to connect your own ClickHouse to Propel. It works with self-hosted, managed, or ClickHouse Cloud servers.

It covers how to:

  1. Create a user for Propel in your ClickHouse server.
  2. Make sure your ClickHouse is accessible from Propel IPs.
  3. Create a ClickHouse Data Pool in Propel.
  4. Preview and query your data.

Requirements​

  • You have a Propel account.
  • You have a ClickHouse Cloud, self-managed, or managed ClickHouse server.
  • A user with SELECT permission on the tables you need to connect.

Step 1: Create a user for Propel in your ClickHouse server​

In this section, you'll set up a user for Propel. Propel will use this user to connect to your ClickHouse instance and query the tables.

1.1 Create the role "propel_role"​

To create a role for Propel in ClickHouse, execute the SQL statement below.

CREATE ROLE propel_role;

1.2 Grant the Propel role the necessary permissions​

Propel needs to be able to do a SELECT on the tables. You need to grant the “propel_role” role SELECT privileges. You can limit this grant to only the tables you are going to connect to Propel by specifying each table name instead of the “*”.

GRANT SELECT ON <DATABASE_NAME>.* TO propel_role;

Replace <DATABASE_NAME> with your database name.

1.3 Create the user "propel_user" and assign it to the role “propel_role”​

Next, you need to create the user "propel_user" and assign it the role "propel_role". You can do this with the following SQL statements:

CREATE USER propel_user IDENTIFIED WITH sha256_password BY '<REPLACE_PASSWORD>';

GRANT propel_role TO propel_user;

Make sure to replace <REPLACE_PASSWORD> with a secure password.

1.4 Test the user​

To ensure your user has the necessary permissions to see and query the tables you need, connect to your ClickHouse using the “propel_user” you just created and run the following statements:

USE DATABASE <DATABASE_NAME>;
SHOW TABLES;

Make sure to replace <DATABASE_NAME> with a secure password. You should see the tables you need to connect to Propel.

Make sure you can query the tables using a SELECT SQL statement.

SELECT * from <TABLE_NAME> LIMIT 10

If you see the records, you are good to go!

Step 2. Make sure your ClickHouse is accessible from Propel IPs​

To ensure that Propel can connect to your ClickHouse server, you need to authorize access from the following IP addresses:

3.17.239.162
3.15.73.135
18.219.73.236

2.1 Allowing Propel’s IP in ClickHouse Cloud​

If you are using ClickHouse Cloud, go to “Settings” then “IP access list” and click “Add IPs”.

Screen capture of the ClickHouse Cloud Settings page

Add the Propel IPs to the list.

Screen capture of the ClickHouse Cloud Add IPs page

Step 3: Create a ClickHouse Data Pool in Propel​

A Propel Data Pool will represent a table in your ClickHouse server.

To create a ClickHouse Data Pool, go to the “Data Pools” section of the Console and click “Create Data Pool”.

Propel Data Pool page

In the new Data Pool page, select “ClickHouse”.

Propel new Data Pool page

Then select “Add Credentials”.

Propel add credentials page

Enter your ClickHouse connection details and click “Test Credentials”.

Propel test credentials page

Wait until Propel connects and runs all the tests, then click “Next”.

Propel test credentials results page

Check that Propel has access to all the tables you need, and click “Continue” to finish setting up your first ClickHouse Data Pool.

Propel ClickHouse table list page

Select the table and columns you want to connect to Propel and click “Next”.

Propel new ClickHouse Data Pool table selection

Select the primary timestamp of the table and click “Next”.

Propel new ClickHouse Data Pool timestamp selection

Give your Data Pool a name and a description, and click “Create Data Pool”.

Propel new ClickHouse Data Pool name and description

You should see the setup tasks completed successfully, and then you're all done!

Propel new ClickHouse Data Pool setup tasks

Step 4: Preview and query your data​

To preview your data, click on the “Preview Data” tab.

Propel new ClickHouse Data Pool preview data

To query your data using SQL, click the "Playground" section and select "SQL" from the API dropdown.

Propel SQL Playground

To query your data using the GraphQL API, click the "Playground" section and select "GraphQL" from the API dropdown.

Propel GraphQL Playground

What's next?​

Now that you've connected your ClickHouse to Propel, you can begin using the APIs with your data in your Data Pool. Here are some suggestions for your next steps: