Integrating Tableau with ClickHouse

• 1: Connect Tableau Desktop to ClickHouse

1 - Connect Tableau Desktop to ClickHouse

Tableau Desktop allows uses to draw data from multiple sources, including spreadsheets and databases. The following steps detail how to add the ClickHouse Connector to Tableau Desktop. Examples are provided for using specific tables and SQL based queries with a Tableau workbook.

Installation

Prerequisites

Before installing the ClickHouse connector with Tableau Desktop, the following prerequisites must be installed:

• The Clickhouse ODBC Driver. Note that an ODBC DSN is not required to be generated, just the ClickHouse ODBC Driver installed.
• Tableau Desktop installed for Microsoft Windows or Apple OS X. Version 2020.2 or above is required.
• A Tableau account to access their Extension gallery.

ClickHouse Connector Installation

The following steps uses the Windows version of Tableau Desktop as a basis for the examples. For more information on installing a Tableau Connector, see the Tableau documentation article Run Your Connector.

The following process will install the ClickHouse Connector for Tableau Desktop for Microsoft Windows.

There are three methods of installing the ClickHouse Connector for Tableau Desktop:

• Install the ODBC Driver Version
• Download from Tableau Coming Soon
• Install from Tableau Desktop Coming Soon

Install the ODBC Driver Version

The clickhouse-tableau-connector-odbc allows users to manually install an ODBC compliant driver that connect Tableau Desktop or Tableau Server to a ClickHouse server.

The installation steps below are geared towards a Windows 10 based installation of Tableau Desktop. For full details of installing a ODBC compliant Tableau connector, see the Tableau Run Your Connector site.

The instructions assume that Tableau Desktop is installed in C:\Program Files\Tableau\Tableau 2021.1. Please adjust these instructions based on your installation of Tableau Desktop.

To manually install the ODBC version of the ClickHouse connector:

1. Install the ClickHouse ODBC driver. See the Clickhouse ODBC Driver page for more details.

2. Download the clickhouse-tableau-connector-odbc by one of the following methods:

   1. From the GitHub repository, select Code -> Download Zip and extract the ZIP file to a folder.

   2. Using the following command:

      git clone https://github.com/Altinity/clickhouse-tableau-connector-odbc
      

3. Create a directory for Tableau connectors. If these are only to be used by the logged in user, then the directory should be in your local user directory [username]\Documents\TableauConnectors directory. For example, if your username is jhummel, then this would be located under c:\Users\jhummel\Documents\TableauConnectors directory.

4. From the clickhouse-tableau-connector-odbc folder, copy the folder tableau_odbc_connector to the Tableau connectors folder created in the previous step.

   1. For example: If the username is jhummel, and the Tableau connectors directory is in c:\Users\jhummel\Documents\TableauConnectors, then the clickhouse-tableau-connector-odbc would be placed in C:\Users\jhummel\Documents\TableauConnectors\tableau_odbc_connector.

5. Run the Tableau Desktop with either of following options:

   1. From the Command Line: Run tableau.exe with the following options, replacing {Your Connector Directory} with the directory for your custom Tableau Connectors as created in the steps above:

      tableau.exe -DConnectPluginsPath={Your Connector Directory}
      

      For example:

      tableau.exe -DConnectPluginsPath=C:\Users\jhummel\Documents\TableauConnectors
      

      

   2. From a Windows Shortcut: To create a customized Windows shortcut, copy the Tableau Desktop shortcut, and add the custom Tableau Connectors directory option after the tableau.exe path. For example:

      "C:\Program Files\Tableau\Tableau 2021.1\bin\tableau.exe" -DConnectPluginsPath=C:\Users\jhummel\Documents\TableauConnectors
      

      

Connect to ClickHouse

After the ClickHouse connector has been installed, Tableau Desktop can be connected to a ClickHouse server.

To connect Tableau Desktop to a ClickHouse server:

1. Launch Tableau Desktop.

2. From the Connect menu, select To a Server -> More.

3. Select ClickHouse by Altinity Inc.

4. Enter the following:

   

   1. Server: The name of the ClickHouse database server, either by hostname or IP address.
   2. Port: The HTTP (8123) or HTTPS port (8443).
   3. Database: The name of the ClickHouse database to use.
   4. Username: The ClickHouse user used to authenticate to the database.
   5. Password (Optional): The ClickHouse user password.
   6. Require SSL (Optional): Select if the connection uses SSL.

Tableau with ClickHouse Connection Examples

Tableau supports multiple methods of data extraction, from single tables, to selecting multiple tables and connecting fields, to creating a virtual table from a SQL query.

For full details on Tableau sheets, dashboards, charts and other features, see the Tableau Desktop documentation site.

The following examples demonstrate using a ClickHouse database connected to Tableau. The first uses a single ClickHouse table, while the second uses a SQL query.

For this example, we will be using data from a publicly available ClickHouse database server hosted on Altinity.Cloud. This allows new ClickHouse users to test queries and connection methods. Use the following settings for this sample ClickHouse database:

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

Single ClickHouse Table Example

To create a sheet populated with data from a ClickHouse database table:

1. Verify that the ClickHouse Connector for Tableau is installed and the connection is working. For more information, see ClickHouse Connector Installation.

2. Select File -> New.

3. Select Connect to Data.

4. Verify the connection information, then select Sign In.

5. Drag the table that will be used for the Tableau sheet. Verify that Live is selected - this determines that Tableau will connect to the ClickHouse server upon request, rather than trying to download the data locally.

   

6. Select the sheet at the bottom - typically this will be Sheet 1 for a new workbook.

   1. For this example, we will be using the table ontime, which stores United States flight data.

7. Select the rows and columns to be used by dragging and dropping them into the Columns and Rows fields. Mouse hover over the chart that you want to use to determine what parameters it requires.

   1. For example, for the Line Chart, 1 data type must be Date, with one or more Dimensions and one or more Measures.
   2. For this example:

      1. Select Flight Date as the Column. Click the arrow and select Year, then the + icon and set the next Flight Date column to Month.

      2. Select Carrier as the Row. Click the arrow and select Measure -> Count to convert this value to a Measure.

         

      3. Select Line Chart as the type of chart to use.

         

8. When finished, select the save icon and name the workbook.

SQL Based Query Example

Tableau can use a SQL query generate the data for its tables and charts. To use a SQL query from Tableau Desktop connected to a ClickHouse server:

1. Verify that the ClickHouse Connector for Tableau is installed and the connection is working. For more information, see ClickHouse Connector Installation.

2. Select File -> New.

3. Select Connect to Data.

4. Verify the connection information, then select Sign In.

5. Select “New Custom SQL”.

6. Enter the new ClickHouse SQL Query in the text field, then select OK.

   1. For this example, the following query is used:

   SELECT FlightDate, Origin, Dest, 
       oa.Latitude as Origin_Latitude, oa.Longitude AS Origin_Longitude, 
       od.Latitude as Dest_Latitude, od.Longitude AS Dest_Longitude, 
       Flights, Cancelled, DepDelayed, ArrDelayed
   FROM (
   SELECT FlightDate, Origin, Dest, count() Flights,
       sum(Cancelled) Cancelled, 
       sum(DepDel15) DepDelayed,
       sum(ArrDel15) ArrDelayed
   FROM ontime
   GROUP BY FlightDate, Origin, Dest ORDER BY FlightDate, Origin, Dest
   ) AS o 
   INNER JOIN airports AS oa ON toString(o.Origin) = oa.IATA
   INNER JOIN airports AS od ON toString(o.Dest) = od.IATA
   

   

7. Select the sheet at the bottom - typically this will be Sheet 1 for a new workbook.

8. Select the rows and columns to be used by dragging and dropping them into the Columns and Rows fields. Check on the chart that is to be used to determine how many values and what values will be applied.

   1. For example, set Origin_Longitude as a Column, and Origin_Latitude as the Row. Select the arrow and verify they are Dimensions. Select the Symbol Map chart. This will display a map with all of the originating flight locations pinned.

   

9. When finished, select the save icon and name the workbook.