Welcome to Our House!

Come on in and stay a while!! We would love to introduce ourselves and say "Hello".

No thanks

You must have cookies enabled to view this lesson.

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!


Prerequisites

  • 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.

Show instructions
  1. 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
    
  2. 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
    
  3. From a terminal, run the following command from the clickhouse-tableau folder:

    docker-compose up
    
  4. 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/.

  1. 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.

Show instructions
  1. 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.

  1. Store the JDBC driver in the following folder (based on your OS):

    Operating SystemDestination folder
    MacOS~/Library/Tableau/Drivers
    WindowsC:\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…

Show instructions
  1. 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.)

  2. Store clickhouse_jdbc.taco in the following folder (based on your OS):

    Operating SystemDestination folder
    MacOS~/Documents/My Tableau Repository/Connectors
    WindowsC:\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.

Show instructions
  1. Start Tableau. (If you already had it running, then restart it.)

  2. 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:

ClickHouse (JDBC) by ANALYTIKA PLUS

  1. Click on ClickHouse (JDBC) by ANALYTIKA PLUS and a dialog window pops up. Enter the following details:
    SettingValue
    Serverlocalhost
    Port8123
    Databasedefault
    Usernamedefault
    Passwordleave blank

Your settings should look like:

ClickHouse Settings

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.)

  1. Click the Sign In button and you should see a new Tableau workbook:

New Workbook

  1. Select TPCD from the Schema dropdown and you should see the list of tables in TPCD:

Select TPCD for the Schema

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…

Show instructions
  1. Drag the CUSTOMER table onto the workbook. Notice the columns appear, but the data table is empty:

  1. Click the Update Now button and 100 rows from CUSTOMER will populate the table.

  2. Drag the ORDERS table into the workbook, then set Custkey as the relationship field between the two tables:

  1. 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.

  1. 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.

  1. 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:

  1. 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:

  1. 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:


We hope you found this learning module helpful and informative! Please feel free to contact us at [email protected] with any issues, questions, feedback, or ideas for future learning modules.