Introducing the Altinity Tableau Connector for ClickHouse

As readers of this blog know, we like ClickHouse. We also like showing new ways to present ClickHouse data. We are therefore happy to announce the Altinity ClickHouse Tableau Connector. It’s in beta and available for use. We plan to add it to the Tableau Extension Gallery once it is production ready. 

The new connector is the work of our colleague Dima Borovtsov. Tableau is Dima’s favorite BI tool, and it pained him to see it work poorly, so he did something about it. His project takes advantage of a new Tableau Connector SDK that enables development of connectors for new data sources.  Our ClickHouse connector project is on Github and licensed under Apache 2.0.

This blog article gives you all the information necessary to get started with the new connector using Tableau Desktop. We hope Tableau fans will try it out and provide feedback. Let’s dive in!

How the Tableau Connector Works

There are two ways to connect Tableau to ClickHouse. First is via the ClickHouse ODBC driver. This works but has significant limitations because Tableau does not understand the ClickHouse SQL dialect. 

The second is via the Altinity ClickHouse Tableau Connector. The connector provides metadata that enables Tableau to translate native queries properly to ClickHouse SQL. With the connector, Tableau visualization features can be correctly implemented in ClickHouse. 

The following diagram shows the two connectivity pathways.

Note that the ClickHouse Connector still depends on the ClickHouse ODBC driver. To use the new Connector we’ll need to install both of them. 

Beta Connector Installation

We have verified the connector using Tableau Desktop. The installation includes the following steps: 

  1. Ensure Tableau Desktop is updated to 2020.2 or later.
  2. Install the ClickHouse ODBC driver. 
  3. Download the ClickHouse Tableau Connector from Github and place it in a local directory like C:\Users\someuser\Documents\Tableauconnectors. 

The precise steps are documented on the Altinity documentation site. Refer to that procedure to complete the installation. 

Starting Tableau and connecting to ClickHouse

Once the installation is complete, start the Tableau Desktop from the DOS command line with a command like the following example on Windows 10. (You can also create a shortcut to make this command easier to run.)

“c:\Program Files\Tableau\Tableau 2021.2\bin\tableau.exe” -DConnectPluginsPath=C:\Users\someuser\Documents\TableauConnectors

You will see a display similar to this image once Tableau starts.

To access data, we’ll need to select the ClickHouse Connector and login. If you are just starting out you’ll need to click on More…, which will expand into a list of connectors.  Locate the ClickHouse by Altinity Inc entry and click on it. You’ll be invited to enter the location and login of your ClickHouse server. 

If you don’t have your own ClickHouse server, you can use our Altinity.Cloud endpoint, which has the following location and login credentials:

  • Server: github.demo.trial.altinity.cloud
  • Port: 8443
  • Database: default
  • Username: demo
  • Password: demo
  • Require SSL: Should be checked

Click Sign In to complete login. You’ll see a screen like the following.

We are now ready to start exploring data on ClickHouse.

Building your first display in Tableau

Let’s start with a simple display of airline ontime data that shows flights by year and by airline carrier. First we need to select a table for queries.  Drag the ontime data over to the Drag tables here panel.  The display looks like the following: 

Next, press Sheet1 at the bottom of your screen.  This will display a new sheet with the table columns on the left hand side, and the visualizations available on the right.  Follow the steps shown below to construct a chart. 

  1. Under Marks, select Line as the chart type. 
  2. Using the cursor, grab the Flight Date field from Tables on the left side and place it in the Columns field at the top of the screen. 
    1. It will default to YEAR for the date.  There’s a + symbol next to Year – select it, then set the Flight Date to Month.
  3. Place the cursor in the Rows field and double-click.  Once you have focus, type COUNT and select the COUNT(expression) function.  Fill in ontime as the expression.  
  4. Using the cursor, grab the Carrier field and drop it on the Color box in the Marks panel. 
  5. Double click on the title to edit and fill in Yearly Flights per Airline Carrier.

After completing these steps you will have a nice chart that looks like this. 

That’s it! You can now start to try out many other types of visualizations on ClickHouse data using Tableau.  

Next steps for the Altinity Tableau Connector

The Altinity Tableau Connector is still undergoing testing.  We welcome users to try it out and report any problems as Github issues. We plan to publish it as a Tableau Extension as soon as possible. Your feedback on problems will help. 

Speaking of problems…There are a couple that we know about already, so here they are in case you meet them. 

  • ClickHouse DateTime64 returns as a string.  This is a ClickHouse ODBC driver issue and will be fixed shortly. 
  • Int64 fields generate an error in Tableau. We’re working on this as well. 

You can work around these and any other issues with data type mappings using a custom query in Tableau instead of querying the table directly.  Here is an example. 

SELECT
  -- other fields
  some_field_1,
  -- Convert DateTime64 to DateTime
  toDateTime(my_datetime_64) as my_datetime_64,
  -- Convert Int64 to String for display
  toString(my_int_64) as my_int_64_string,
  -- Convert Int64 to Float for summarization.
  toFloat64(my_int_64) as my_int_64_float
FROM my_table;

Conclusion

The Altinity ClickHouse Tableau Connector is a big step forward to bring ClickHouse Tableau support up to the level of Grafana and Superset.  We’ll be working to polish the connector and extend its functionality, including adding instructions to run on Tableau Server. 

I would like to thank my Altinity colleagues, Dima Borovtsov and John Hummel, for their work to develop the connector and document it, respectively. All three of us are in turn grateful to our colleague Denis Glazachev for his excellent work on the ClickHouse ODBC driver. 

We welcome feedback on Tableau support or any other topic related to ClickHouse. You can connect with us directly by filing a Github issue, sending email to info@altinity.com, or filling out a Contact Us request. Now go have fun with Tableau!

2 Comments

  1. Thank you so much Robert, this is great!
    I’d like to thank Altinity for the hard work and publishing this article, and of course the connector for Tableau/Clickhouse.
    Once the connector is installed, do we always have to open Tableau on developer mode?
    On the other hand, once reports are built, is this connector supporting publishing with extracts and/or live connections?
    Your feedback is much appreciated.
    Thanks!
    JC

  2. Jorge, thank you for feedback!
    We continue to improve connector and it will be available in official Tableau Gallery soon.
    yes, you still need to run Tableau in developer mode. You could change Tableau shortcut to avoid running it from command line. Once connector will be available in Tableau Gallery – you could install it from there and dont use developer mode anymore.
    Connector works as regular connection and supports both modes (extracts and live connections).

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.