Welcome to Our House!
Come on in and stay a while!! We would love to introduce ourselves and say "Hello".
Connecting Tableau to ClickHouse
Duration: 10 minutes
Audience: Anyone interested in learning how to connect Tableau to ClickHouse
Issues/Questions: Contact us at [email protected]
Overview: In this lesson, you will learn how to configure ClickHouse as a data source in Tableau. We will use the JDBC connector along with a new connector from ANALYTIKA PLUS that extends the features of the standard Tableau functionality that you get from the generic JDBC/ODBC connector.
Let’s get started!
- We will assume you have Tableau already
- You will need Docker installed and running so that you can start up a Docker Compose file
1. Start ClickHouse
You are going to run a preconfigured ClickHouse server in a Docker container that has some data in it already.
Let’s start by creating a local folder to work in (feel free to name the folder anything you like):
mkdir ~/clickhouse-tableau cd ~/clickhouse-tableau
In the clickhouse-tableau folder, create a new file named docker-compose.yml and copy-and-paste the following into it:
version: '3.7' services: clickhouse-server: image: learnclickhouse/public-repo:clickhouse-tpch-21.10 container_name: clickhouse-server hostname: clickhouse-server ports: - "9000:9000" - "8123:8123" - "9009:9009" restart: always tty: true ulimits: memlock: soft: -1 hard: -1 nofile: soft: 262144 hard: 262144 deploy: resources: limits: memory: 2g cap_add: - IPC_LOCK
From a terminal, run the following command from the clickhouse-tableau folder:
The entrypoint script has a delay to ensure that ClickHouse starts up before the database is created and populated. After about 30 seconds, open the Play UI and run the following command:
SHOW TABLES IN TPCD
You should see 8 tables:
The TPC-H dataset is used for benchmarking purposes and is freely available at http://www.tpc.org/tpch/.
- The dataset consists or products for sale, suppliers, customers and orders. Feel free to browse the contents of the various tables. For example:
SELECT * FROM TPCD.ORDERS LIMIT 100
2. Download the JDBC Driver
The Tableau connector is an extension of the ClickHouse JDBC driver, so you need to download the JDBC driver and save it in the correct folder.
- Download the latest version of the ClickHouse JDBC driver at https://github.com/ClickHouse/clickhouse-jdbc/releases/. (We used this version of the driver for this tutorial.)
Make sure you download the clickhouse-jdbc-x.x.x-shaded.jar JAR file.
Store the JDBC driver in the following folder (based on your OS):
Operating System Destination folder MacOS ~/Library/Tableau/Drivers Windows C:\Program Files\Tableau\Drivers
That’s it. The driver will be found the next time you start Tableau.
3. Download the Connector
ANALYTIKA PLUS has built a handy connector for simplifying connections to ClickHouse from Tableau. You can view the details of the project in Github. Follow these steps to download the connector…
The connector is built in a taco file (short for Tableau Connector). Download the latest version at https://github.com/analytikaplus/clickhouse-tableau-connector-jdbc/releases/. (For this lesson, we downloaded v0.1.1 of clickhouse_jdbc.taco.)
Store clickhouse_jdbc.taco in the following folder (based on your OS):
Operating System Destination folder MacOS ~/Documents/My Tableau Repository/Connectors Windows C:\Users[Windows User]\Documents\My Tableau Repository\Connectors
The connector is now ready to go.
4. Configure a ClickHouse data source in Tableau
Now that you have the driver and connector in the approriate folders on your machine, let’s see how to define a data source in Tableau that connects to the TPCD database in ClickHouse.
Start Tableau. (If you already had it running, then restart it.)
From the left-side menu, click on More under the To a Server section. If everything worked properly, you should see ClickHouse (JDBC) by ANALYTIKA PLUS in the list of installed connectors:
- Click on ClickHouse (JDBC) by ANALYTIKA PLUS and a dialog window pops up. Enter the following details:
Setting Value Server localhost Port 8123 Database default Username default Password leave blank
Your settings should look like:
Our ClickHouse database is named TPCD, but you must set the Database to default in the dialog above, then select TPCD for the Schema in the next step. (This is likely due to a bug in the connector, so this behavior could change, but for now you must use default as the database.)
- Click the Sign In button and you should see a new Tableau workbook:
- Select TPCD from the Schema dropdown and you should see the list of tables in TPCD:
You are now ready to build some visualizations in Tableau!
5. Building Visualizations in Tableau
Now that have a ClickHouse data source configured in Tableau, let’s visualize the data…
- Drag the CUSTOMER table onto the workbook. Notice the columns appear, but the data table is empty:
Click the Update Now button and 100 rows from CUSTOMER will populate the table.
Drag the ORDERS table into the workbook, then set Custkey as the relationship field between the two tables:
- You now have the ORDERS and LINEITEM tables associated with each other as your data source, so you can use this relationship to answer questions about the data. Select the Sheet 1 tab at the bottom of the workbook.
- Suppose you want to know how many specific items were ordered each year. Drag Orderdate from ORDERS into the Columns section (the horizontal field), then drag Quantity from LINEITEM into the Rows. Tableau will generate the following line chart:
Not a very exciting line chart, but the dataset was generated by a script and built for testing query performance, so you will notice there is not a lot of variations in the simulated orders of the TCPD data.
Suppose you want to know the average order amount (in dollars) by quarter and also by shipping mode (air, mail, ship, truck, etc.):
- Click the New Worksheet tab create a new sheet
- Drag OrderDate from ORDERS into Columns and change it from Year to Quarter
- Drag Shipmode from LINEITEM into Rows
You should see the following:
- The Abc values are just filling in the space until you drag a metric onto the table. Drag Totalprice from ORDERS onto the table. Notice the default calculation is to SUM the Totalpricess:
- Click on SUM and change the Measure to Average. From the same dropdown menu, select Format change the Numbers to Currency (Standard):
Well done! You have successfully connected Tableau to ClickHouse, and you have opened up a whole world of possibilities for analyzing and visualizing your ClickHouse data.
Tableau is great, and we love that it connects so nicely to ClickHouse! If you are new to Tableau, check out their documentation for help on building dashboards and visualizations.
Summary: You can connect Tableau to ClickHouse using the generic ODBC/JDBC ClickHouse driver, but we really like how this tool from ANALYTIKA PLUS simplifies the process of setting up the connection. If you have any issues with the connector, feel free to reach out to ANALYTIKA PLUS on GitHub.
What’s next: Check out the following lessons to continue your journey:
- The Ingest Nginx Logs into ClickHouse using Vector lesson demonstrates how to stream a log file into ClickHouse
- Check out What’s New in ClickHouse 21.10
- View all of our lessons on the Learn ClickHouse home page