Connecting Chartbrew to ClickHouse

Community Maintained

Chartbrew is a data visualization platform that allows users to create dashboards and monitor data in real time. It supports multiple data sources, including ClickHouse, and provides a no-code interface for building charts and reports.

Goal

In this guide, you will connect Chartbrew to ClickHouse, run a SQL query, and create a visualization. By the end, your dashboard may look something like this:

Add some data

If you do not have a dataset to work with, you can add one of the examples. This guide uses the UK Price Paid dataset.

1. Gather your connection details

To connect to ClickHouse with HTTP(S) you need this information:

• The HOST and PORT: typically, the port is 8443 when using TLS or 8123 when not using TLS.

• The DATABASE NAME: out of the box, there is a database named default, use the name of the database that you want to connect to.

• The USERNAME and PASSWORD: out of the box, the username is default. Use the username appropriate for your use case.

The details for your ClickHouse Cloud service are available in the ClickHouse Cloud console. Select the service that you will connect to and click Connect:

Choose HTTPS, and the details are available in an example curl command.

If you are using self-managed ClickHouse, the connection details are set by your ClickHouse administrator.

2. Connect Chartbrew to ClickHouse

1. Log in to Chartbrew and go to the Connections tab.

2. Click Create connection and select ClickHouse from the available database options.

   

3. Enter the connection details for your ClickHouse database:

   • Display Name: A name to identify the connection in Chartbrew.
   • Host: The hostname or IP address of your ClickHouse server.
   • Port: Typically 8443 for HTTPS connections.
   • Database Name: The database you want to connect to.
   • Username: Your ClickHouse username.
   • Password: Your ClickHouse password.
   

4. Click Test connection to verify that Chartbrew can connect to ClickHouse.

5. If the test is successful, click Save connection. Chartbrew will automatically retrieve the schema from ClickHouse.

   

3. Create a dataset and run a SQL query

1. Click on the Create dataset button or navigate to the Datasets tab to create one.
2. Select the ClickHouse connection you created earlier.

Write a SQL query to retrieve the data you want to visualize. For example, this query calculates the average price paid per year from the uk_price_paid dataset:

SELECT toYear(date) AS year, avg(price) AS avg_price
FROM uk_price_paid
GROUP BY year
ORDER BY year;

Click Run query to fetch the data.

If you're unsure how to write the query, you can use Chartbrew's AI assistant to generate SQL queries based on your database schema.

Once the data is retrieved, click Configure dataset to set up the visualization parameters.

4. Create a visualization

1. Define a metric (numerical value) and dimension (categorical value) for your visualization.
2. Preview the dataset to ensure the query results are structured correctly.
3. Choose a chart type (e.g., line chart, bar chart, pie chart) and add it to your dashboard.
4. Click Complete dataset to finalize the setup.

You can create as many datasets as you want to visualize different aspects of your data. Using these datasets, you can create multiple dashboards to keep track of different metrics.

5. Automate data updates

To keep your dashboard up-to-date, you can schedule automatic data updates:

1. Click the Calendar icon next to the dataset refresh button.
2. Configure the update interval (e.g., every hour, every day).
3. Save the settings to enable automatic refresh.

Learn more

For more details, check out the blog post about Chartbrew and ClickHouse.