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:
- Ensure Tableau Desktop is updated to 2020.2 or later.
- Install the ClickHouse ODBC driver.
- 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.
- Under Marks, select Line as the chart type.
- 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.
- It will default to YEAR for the date. There’s a + symbol next to Year – select it, then set the Flight Date to Month.
- 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.
- Using the cursor, grab the Carrier field and drop it on the Color box in the Marks panel.
- 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;
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 email@example.com, or filling out a Contact Us request. Now go have fun with Tableau!