Integrations

• 1: Integrating Superset with ClickHouse
• 1.1: Install Superset
• 1.2: Connect Superset to ClickHouse
• 1.3: Create Charts from ClickHouse Data
• 2: Integrating Tableau with ClickHouse
• 2.1: Connect Tableau Desktop to ClickHouse
• 3: ClickHouse ODBC Driver
• 3.1: ClickHouse ODBC Driver Installation for Windows
• 3.2: ClickHouse ODBC Connection for Microsoft Excel
• 4: Integrating Grafana with ClickHouse
• 4.1: How to Install Grafana and ClickHouse Plugins
• 4.2: How to Connect Grafana to ClickHouse
• 4.3: Create Grafana Dashboards from ClickHouse Data
• 4.4:

1 - Integrating Superset with ClickHouse

Apache Superset is a powerful and convenient way to create dashboards to display information for your organization. This presents information that allows for quick visual understand of trends, where events are happening, and keeps your team on top of the massive amounts of data coming into your organization.

When combined with ClickHouse and its capacity to analyze terabytes of information in milliseconds, the integration of Superset and ClickHouse can reduce the sea of seemingly disconnected data flowing through into an understandable whole.

Integrating Superset with ClickHouse is broken down into three major steps:

1. Install Superset.
2. Connect Superset to ClickHouse.
3. Connect Superset dashboard elements to ClickHouse queries.

1.1 - Install Superset

Three methods of installing Superset are provided here:

1. Direct installation: Directly download the Superset Python modules into an operating system.
2. Docker based installation: Use Docker containers to provide Superset, whether as stand alone containers or part of the Kubernetes environment.
3. Managed installation: Use managed services to provide Superset, and connect it to your ClickHouse.

Direct Installation

The following instructions are based an Ubuntu 20.04 environment. For information on how to install Superset for other operating systems, see Installing Superset from Scratch.

Direct Installation Prerequisites

Before installing Superset on Ubuntu 20.04, the following packages must be installed, and will require administrative access on the machine.

Note that Superset requires Python, specifically Python 3.7.9 and above as of this document. These instructions are modified from the original to include installing python3-venv as part of the other prerequisites.

sudo apt-get install build-essential libssl-dev libffi-dev python3-dev python3-pip libsasl2-dev libldap2-dev python3-venv

Direct Installation Instructions

To install Superset directly into the Ubuntu 20.04 operating system:

1. A virtual environment is highly recommended to keep all of the Superset python requirements contained. Use the following commands to create your Superset virtual environment, activate it, then upgrade pip:

   python3 -m venv clickhouse-sqlalchemy
   . clickhouse-sqlalchemy/bin/activate
   pip install --upgrade pip
   

2. Install Superset with the following in your virtual environment. The command superset fab create-admin sets the admin user password, which will be used to login and update administrative level Superset settings:

   export FLASK_APP=superset
   pip install apache-superset
   superset db upgrade
   superset fab create-admin
   superset load_examples
   superset init 
   

3. Install the ClickHouse SQLAlchemy - this allows Superset to communicate with ClickHouse. There are two drivers currently in development. As of this time, we recommend the clickhouse-sqlalchemy driver. To install clickhouse-sqlalchemy, use the pip command:

   pip install clickhouse-sqlalchemy
   

4. For those who want to enable TLS communications with services like Altinity.Cloud, verify the versions:

   1. The clickhouse-driver version should be greater than 0.2.0.

   2. The clickhouse-sqlalchemy driver should be 0.1.6 or greater.

      pip freeze | grep clickhouse
      clickhouse-driver==0.2.0
      clickhouse-sqlalchemy==0.1.6
      

Starting Superset From Direct Install

Once Superset is installed, start it with the following command:

superset run -p 8088 --with-threads --reload --debugger

Access the Superset web interface with a browser at the host name, port 8088. For example: http://localhost:8088. The default administrative account will be admin, with the password set from the command superset fab create-admin as listed above.

Installation References

• Installing Superset from Scratch

Docker Based Installation

For organizations that prefer Docker based installations, or want to add Superset to a Kubernetes environment along with ClickHouse on Kubernetes, Superset can be installed with a few Docker commands.

The following instructions use the existing Apache Superset docker-compose file from the Github repository.

Docker Prerequisites

docker 19 and above, with either docker-compose version 1.29 and above, or the built in docker compose command that’s part of Docker 20.10 and above.

Docker Based Installation Instructions

To install Superset with a Docker Container:

1. Download the Superset configuration files from the GitHub repository:

   git clone https://github.com/apache/superset
   

2. Enter the superset directory and set the clickhouse-driver and the clickhouse-sqlalchemy version requirements:

   cd superset
   touch ./docker/requirements-local.txt
   echo "clickhouse-driver>=0.2.0" >> ./docker/requirements-local.txt
   echo "clickhouse-sqlalchemy>=0.1.6" >> ./docker/requirements-local.txt
   

3. Run either docker-compose or docker compose to download the required images and start the Superset Docker containers:

   docker-compose -f docker-compose-non-dev.yml up
   

Starting Superset From Docker

Installing and starting Superset from Docker is the same docker-compose or docker compose installation command:

docker-compose -f docker-compose-non-dev.yml up

Access the Superset web interface with a browser at the server’s host name or IP address on port 8088. For example: http://localhost:8088. The default administrative account will be admin, with the password admin.

Preset Cloud

For organizations that prefer managed services, Preset Cloud offers Superset with with clickhouse-sqlalchemy. This can then be connected to an Altinity.Cloud account.

Production Installation Tips

The examples provided above are useful for development and testing environments. For full production environments installation and deployments, refer to the Superset Documentation site.

Some recommendations for organizations that want to install Superset in a production environment:

• Replace the default SQLite database used to store the Superset settings with something more robust like PostgreSQL or MySQL. For an example of the process, see the article Migrating Superset to Postgres.
• Superset can be run in Kubernetes, which allows it to be run in a defined cluster. For more information, see the Superset documentation site on Running on Kubernetes.

1.2 - Connect Superset to ClickHouse

After Superset has been setup and the ClickHouse SQLAlchemy drivers installed, the two systems can be connected together. The following details how to connect Superset to an existing ClickHouse database.

For details on how to install ClickHouse, see either Altinity ClickHouse Stable Release Quick Start Guide, or use Altinity.Cloud.

Superset to ClickHouse Connection Instructions

Superset and ClickHouse Connection PreRequisites

• Superset installed
• clickhouse-driver 0.2.0 or above installed
• clickhouse-sqlalchemy 0.1.6 or above installed

For more information, see the Install Superset page.

To connect Superset to ClickHouse once all prerequisites have been met:

1. Login as a Superset administrative user. The default is admin.

2. From the top menu, select Data->Databases to add a new database connection.

   

3. Select + Database.

   

4. Enter the following under the Connection tab:

   1. Database Name: The name your organization assigns the database. This does not have to match the actual name of the ClickHouse database.
   2. SQLAlchemy URI: The URI of the database connection in the SQLAlchemy format. The URI will be in the following format:

   {SQLAlchemy Connection Type}://{username}:{password}@{url}?{options}
   

   For example, to connect to a ClickHouse server, the following URI will be used:

   clickhouse+native://demo:demo@github.demo.trial.altinity.cloud/default?secure=true
   

   The SQLAlchemy format uses the following elements:

   1. SQLAlchemy Connection Type: Sets the driver to use to connect.
   2. username: The database account to use for authentication.
   3. password: The password for the database account being used.
   4. url: The full hostname to the database. This does not include tags such as HTTP or HTTPS - just the host name.
   5. options: Any additional options. For example, if the database connection is encrypted, then the option secure=true will be required. For more information on setting up ClickHouse network encryption, see Network Hardening.

5. Select Test Connection to verify the connection.

   

6. If the connection works, then click Add.

   

7. Once finished, the new database connection to ClickHouse will be available for use.

   

Connection Options

The clickhouse-driver supports different connection options. For a full list, see the clickhouse-driver Connection site. The following are recommended settings:

• secure: Required if the connection uses encryption.
• port: Used if the connection uses a non-standard port.
• alt_hosts: Specifies alternate connection host names.
• verify: Default to true. If true, then the certificate used for encryption must be validated. Set to false if using a self-signed certificate. For more information on ClickHouse certificates, see Network Hardening.

Connection FAQ

What ports are used for the Superset to ClickHouse Connection?

The Superset connection to ClickHouse uses the TCP connection over the following ports:

• Encrypted: 9440
• Unencrypted: 9000

If using the encrypted connection, then the option secure=yes is required.

1.3 - Create Charts from ClickHouse Data

Once Superset has been installed and connected to ClickHouse, new charts can be created that draw their visualizations from the ClickHouse data.

For complete details on Superset dashboards and charts, see the Superset Creating Your First Dashboard site.

The following procedures demonstrate two methods of adding charts connected to ClickHouse data:

1. From a physical dataset tied to a specific database table.
2. From a virtual dataset tied to a query saved in SQL Lab.

Create a Chart from a Physical Dataset

Physical Dataset Chart Prerequisites

Before adding a new chart in Superset from ClickHouse data, Superset must be connected to ClickHouse. See Connect Superset to ClickHouse for more information.

Add a Physical Dataset Steps

To add a new Physical Dataset:

1. From the top menu, select Data->Datasets.

   

2. Select + Dataset.

   

3. Set the following:

   

   1. Datasource: The connected ClickHouse database server to use.
   2. Schema: The database to use on the ClickHouse server.
   3. Table: The table to be queried.

Once complete, the physical dataset will be available for building new charts.

Chart from Standard Dataset Steps

To create a chart from a physical dataset:

1. Select Charts from the top menu.

   

2. Select + Chart.

   

3. Set the following:

   1. Dataset: Select the dataset to be used. In this case, a dataset tied to a specific table.
   2. Visualization Type: Select the chart type, such as line chart, time series, etc.

4. When ready, select Create New Chart.

   

5. Depending on the chart created, the following options may be set to improve the chart’s display:

   1. Group By: Select a column to group data by. This is highly useful when dealing with multiple rows of the same unique source.
   2. Time Grain: Set the interval of time to measure when dealing with time series or similar charts.
   3. Time Range: Select the period of time of interest for the chart to cover.

6. After each setting change, select Run to view the updated chart (A).

7. Set the name by selecting the section above the chart, titled “- untitled” by default (B).

   

8. When finished, select + Save to save the chart.

   

   1. Provide the chart name.
   2. Select a Dashboard to add the chart to, or enter a new Dashboard name and a title.
   3. Select Save when finished, or Save and Go To Dashboard to view the new chart.

Create a Chart from a Virtual Dataset

A Virtual Dataset is created from a query that is saved in the Superset SQL Labs. This allows for charts that are build from specific queries that include joins, specific filters, and other criteria.

Virtual Dataset Chart Prerequisites

Before adding a new chart in Superset from ClickHouse data, Superset must be connected to ClickHouse. See Connect Superset to ClickHouse for more information.

Create a Virtual Dataset Steps

To create a chart from a Virtual Dataset:

1. From the top menu, select + -> SQL Query.

   

2. Enter the following:

   1. Database: Select the ClickHouse database connected to Superset.
   2. Schema: Typically this will be default.
   3. Table: Select a table or table type. If building from just the query, this can be left blank.

3. In the Query field, enter the ClickHouse SQL query to use. Select Run to verify the results.

4. Select Save or Save As to save the query into SQL Lab.

5. To save the query as a Virtual Dataset, select Explore.

   

6. Set the name of the Virtual Dataset, then select Save & Explore.

   

At this point, a Chart can be created from the Virtual Dataset. See the section Create Chart from an Existing Virtual Dataset Steps for information on setting up the chart.

Create Chart from an Existing Virtual Dataset Steps

To create a chart from an existing Virtual Dataset:

1. Either create a new chart by either:

   1. Selecting Charts -> +Charts, then selecting the Physical Dataset tied to the Virtual Dataset, and Visualization Type to use.
   2. Or from creating a Virtual Dataset as detailed in the process Create a Virtual Dataset Steps.

2. To change the Virtual Dataset to use, select the … in the upper right under the Dataset heading, then select Change Dataset.

   

   1. Select the Virtual Dataset to use, then click Proceed to acknowledge that changing the chart’s dataset may break the chart.

3. Set the following required options:

   

   1. Visualization Type: How the data will be displayed in the chart (A).
   2. Name: This is above the chart. Select it once, then set the name (B).

4. Depending on the Visualization Type selected, complete the other options.

5. When finished, select + Save to save the chart.

   1. Provide the chart name.
   2. Select a Dashboard to add the chart to, or enter a new Dashboard name and a title.
   3. Select Save when finished, or Save and Go To Dashboard to view the new chart.

Adding Charts FAQ

When I Add a Deck.gl Chart, I Get the Message ‘No Token Warning’. How do I fix it?

The NO_TOKEN_WARNING when using charts such as deck.gl Arc occurs when there is no API token to a map service for generating the map used with the geographic data. In this example, the geographic information provides flight information, but there is no token for the mapbox service to retrieve the map data.

A token can be retrieved from mapbox after completing their registration process. After obtaining the token, place it in the file superset_config.py with the variable MAPBOX_API_KEY. For example:

MAPBOX_API_KEY = 'mytoken'

For Docker based installations, this is in the directory docker/pythonpath_dev. Once added, restart Superset:

2 - Integrating Tableau with ClickHouse

Tableau provides users across the world with the power to collect information from spreadsheets, databases and other sources to turn them into meaningful tables, charts, and dashboards.

Organizations can harness that power with their ClickHouse server. They can take the massive amounts of analytic data from ClickHouse and use it to provide dashboards to pinpoint exactly what their teams needs to know.

The following guides detail how to add the ClickHouse Connector to your Tableau Desktop and Tableau Server applications.

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

3 - ClickHouse ODBC Driver

The ClickHouse Open Database Connectivity(ODBC) driver allows users to connect different applications to ClickHouse, such as connecting Microsoft Excel, Tableau Desktop, and other platforms.

The official ODBC Driver for ClickHouse is available in binary versions for Microsoft Windows and Linux distributions. It can also be installed by compiling the source code for other operating systems such as Mac OS X.

The following guides details how to install the official ClickHouse ODBC driver for your operating systems. For the most up to date information, see the official ODBC Driver for ClickHouse site.

3.1 - ClickHouse ODBC Driver Installation for Windows

The ClickHouse ODBC Driver for Microsoft Windows allows users to connect different applications to a ClickHouse database. There are two versions available: 64-Bit and 32-Bit based on which version of Windows is being used, and the requirements of the applications connecting to ClickHouse.

Prerequisites

• The ClickHouse ODBC Driver relies on the Microsoft Visual C++ Redistributable for Visual Studio 2019. This is included with the ClickHouse ODBC Installer for Windows.
• A local Administrator account is required to install the ClickHouse ODBC driver on Windows 10.

Installation Steps

To install the ClickHouse ODBC Driver for Microsoft Windows:

1. With a browser, navigate to the clickhouse-odbc releases page.

2. Select the most recent release and select one of the following ClickHouse ODBC installer for Windows, replacing {version} with the version that will be downloaded:

   

   1. For 32-bit versions of Windows: clickhouse-odbc-{version}-win32.msi
   2. For 64-bit versions of Windows: clickhouse-odbc-{version}-win64.msi

3. Launch the downloaded ClickHouse ODBC installer.

   1. Note: There may be a warning from Microsoft Windows Defender that the installer is an unrecognized application. If this occurs, select More Info, then select Run Anyway.

   

4. Follow the ODBC installation process as detailed by the application. The default installation are typically sufficient, but refer to the clickhouse-odbc guide for full details.

5. Once finished, the ClickHouse ODBC Driver will be installed.

Verifying the ClickHouse ODBC Driver Installation

To verify the ClickHouse ODBC Driver has been installed:

1. Launch the Windows 10 application ODBC Data Source Administrator - there are two versions: 32 bit and 64 bit. Select the version that matches your operating system.

2. Select the System DSN tab. Under a standard ClickHouse ODBC installation, both the ClickHouse DSN (ANSI) and the ClickHouse DSN (Unicode) will be available.

   

Example Connecting to ClickHouse with ODBC

Once the ClickHouse ODBC driver has been installed, connections can be made to specific ClickHouse servers via the Data Source Name(DSN). Two connection types are recommended:

• User DSN: These are ODBC connections that are available for the Windows 10 user.
• System DSN: These are ODBC connections available to all users of the Windows 10 operating system.

The following example demonstrates how to create a User DSN connection to a ClickHouse server.

1. Launch the Windows 10 application ODBC Data Source Administrator - there are two versions: 32 bit and 64 bit. Select the version that matches your operating system and the applications that will be connecting to ClickHouse.

   1. For example: If running the 64 bit version of Windows 10, but the application is 32 bit, then select the 32 bit version of the ODBC driver.

2. Select the User DSN tab.

3. Select Add.

4. Select ClickHouse ODBC Driver (Unicode), then select Finish.

5. There are two methods of setting up the DSN connection: URL or Host Name. To set up the connection via URL:

   1. Name: The name you set for your connection.

   2. Description (Optional): A short description of the ODBC connection.

   3. URL: The URL for the ClickHouse server. This will be the HTTP or HTTPS connection based on the ClickHouse HTTP Interface.

      1. This will be in the format:

      {connection type}//{url}:{port}

      For example: https://github.demo.trial.altinity.cloud:8443

6. To set up the connection via Host Name, provide the following:

   

   1. Host: The hostname or IP address of the ClickHouse server.
   2. Port: The port to be used. This will be either the HTTP port, default 8123, or the HTTPS port default 8443.
   3. Database (Optional): The name of the database on the ClickHouse server.
   4. SSLMode (Optional)
      1. Set to require if SSL will be used and fail if it can not be verified.
      2. Set to allow if SSL will be used with self-signed certificates.
   5. User (Optional): Set to provide a specific username when connecting, leave blank to be prompted.
   6. Password (Optional): Set to provide a specific password when connecting, leave blank to be prompted.
   7. Timeout (Optional): Set a timeout period before giving up on the connection.

Test Connection

One method of testing the connection to ClickHouse through the ODBC driver is with Powershell. This script will make an ODBC connection to the specified database, then show all tables available to the authenticating ClickHouse user.

1. Launch Powershell.
   1. If using the 64 bit version of the ClickHouse ODBC Driver, then select Windows Powershell ISE.
   2. If using the 32 bit version of the ClickHouse ODBC Driver, select Windows Powershell ISE (x86).
2. Paste the following script, replacing the following:
   1. DSN: The DSN of your ClickHouse ODBC Connection.
   2. Uid: The ClickHouse user being used.
   3. Pwd: The password of the ClickHouse user being used.
3. Run the script.

$connectstring = "DSN=ClickHouseDemo;Uid=demo;Pwd=demo;"
$sql = @'
show tables;
'@
$connection = New-Object System.Data.Odbc.OdbcConnection($connectstring)
$connection.open()
$command = New-Object system.Data.Odbc.OdbcCommand($sql,$connection)
$data = New-Object system.Data.Odbc.OdbcDataAdapter($command)
$datatable = New-Object system.Data.datatable
$null = $data.fill($datatable)
$conn.close()
$datatable

3.2 - ClickHouse ODBC Connection for Microsoft Excel

Users who have completed the process in installing the official ClickHouse ODBC driver for Microsoft Windows can connect Microsoft Excel to a ClickHouse database.

Prerequisites

The official ClickHouse ODBC driver must be installed before making a connection from Excel. For more information, see Clickhouse ODBC Driver Installation for Windows

Connection Steps

To connect Microsoft Excel to a ClickHouse server through the ClickHouse ODBC driver, follow these steps:

1. Launch Microsoft Excel.

2. Open a new or existing Workbook.

3. Select Data -> Get Data -> From Other Sources -> From ODBC.

   

4. Select the ODBC connection created, then OK.

5. Provide the username and password for the connect, then select Connect.

6. If successful, the ClickHouse server with the databases and tables granted to the user will be displayed.

   

4 - Integrating Grafana with ClickHouse

Grafana dashboards provide dynamic, beautiful displays of account transactions, system performance, and other data that keep teams on top of what their data means.

When combined with the analytical power and speed of ClickHouse, organizations can monitor everything from financial transactions to network performance, security threats to shipping routes.

The following guides detail how to:

• Install Grafana and the plugins for ClickHouse
• Integrate your Grafana server with a ClickHouse cluster
• Create sample dashboards and panels in Grafana with your ClickHouse data.

4.1 - How to Install Grafana and ClickHouse Plugins

Grafana server supports multiple operating systems including:

• Linux (DEB and RPM based distributions)
• Microsoft Windows
• Mac OS
• Docker

The following instructions are based on installing Grafana for Ubuntu, or using the Docker image to provide services. For full details on installing Grafana, see the Grafana Labs Install Grafana site.

Install Grafana on Ubuntu

Prerequisites

Installing Grafana on Ubuntu has the following prerequisites:

• 255 MB or more storage space
• A database to store information. By default, Grafana installs and uses SQLite as its default database. For production systems, MySQL or PostgreSQL are recommended.

Grafana Install

Grafana comes in both an Enterprise and an OSS (Open Source Software) versions. The instructions below are for the OSS version of Grafana.

The following steps require an administrative account. To install Grafana on Ubuntu 20.04 or similar Linux distributions:

1. From a terminal, install Grafana through apt-get. This will install Grafana from the current stable repository that’s available for all Ubuntu 20.04 users:

   sudo apt-get clean  
   sudo apt-get update
   sudo apt-get install grafana
   

2. Start Grafana, and set it to start at system boot:

   sudo systemctl daemon-reload
   sudo systemctl start grafana-server
   sudo systemctl enable grafana-server.service
   sudo systemctl status grafana-server
   ● grafana-server.service - Grafana instance
       Loaded: loaded (/lib/systemd/system/grafana-server.service; enabled; vendo>
       Active: active (running) since Wed 2021-06-30 10:26:41 MDT; 11min ago
   

ClickHouse Plugin Install

Once Grafana has been installed, the plugins to access ClickHouse can be installed through the following process. For more information on the ClickHouse plugin for Grafana, see the ClickHouse datasource for Grafana page.

1. From a terminal, use an administrative account to install the ClickHouse plugins for Grafana with the following command:

   sudo grafana-cli plugins install vertamedia-clickhouse-datasource
   

2. Restart Grafana:

   sudo systemctl restart grafana-server
   

Deploy Grafana via Docker

For organizations that prefer to use Docker based services, the following process will set up a Grafana server with plugins for ClickHouse already installed.

To run the most recent version of Grafana with the ClickHouse plugins through Docker:

1. Start Docker and specify the ports, the container name, and the plugins to install with the following:

   docker run -d \
       -p 3000:3000 \
       --name=grafana \
       -e "GF_INSTALL_PLUGINS=vertamedia-clickhouse-datasource" \
       grafana/grafana
   

2. After running the first time, it can be restarted with:

   docker start grafana
   

Configure Grafana

Once Grafana has been installed from either of the directions listed above, erify it is running by launching a browser and pointing it at the host name or IP address port 3000. For example: http://localhost:3000. The default username is admin and the default password is admin. Once logged in you will be prompted to create a new password.

4.2 - How to Connect Grafana to ClickHouse

Once Grafana has been installed with the ClickHouse plugins, dashboards and charts can be created that draw off of data in the CLickHouse server.

Connecting Grafana to ClickHouse

Creating a Dashboard and Chart from ClickHouse data requires the creation of a Grafana Data Source. This is a configuration that specifies the ClickHouse server hostname and other settings Grafana will use to connect.

These instructions assume that you have installed Grafana with the ClickHouse plugins. For more information, see How to Install Grafana and ClickHouse Plugins.

Create Grafana Data Source with the ClickHouse Plugin

To create a new Grafana Data Source connected to a ClickHouse server:

1. Connect to your Grafana server from a browser.

2. From the left navigation panel, select Settings -> Data sources.

3. Select Add Data Source.

   

4. From the list of data sources, select ClickHouse.

   

Localhost Sample Data Source

For organizations who want to test their Grafana ClickHouse plugin with their own internal ClickHouse server, the following settings would be used to connect to a ClickHouse server at host name localhost, with no credentials and no encryption.

1. Enter the following:
   1. Name: The name for the connection. This does not need to be the name of the ClickHouse server or database.
   2. URL: For localhost, we need to specify both the protocol and the port. In this case, it will be http://localhost:8123.
   3. Auth: Only required if a ClickHouse username and password are specified. If there are no ClickHouse credentials to use, then leave this section disabled.
   4. Additional:
      1. Use Post method to send queries: True
      2. Default database: Select the database to be used without having to specify it in connections.
2. Select Save & test.

If successful, then the Grafana server can connect to the ClickHouse server on localhost.

Altinity.Cloud Sample Data Source

The following samples are based on a publicly available ClickHouse sample database provided by Altinity.Cloud. This database provides access to US airline flight data and sample logs, and can be access through the following credentials:

• Server: https://github.demo.trial.altinity.cloud
• Port: 8443
• Database: default
• Username: demo
• Password: demo

1. Enter the following:

   1. Name: The name for the connection. This does not need to be the name of the ClickHouse server or database.

   2. URL: The URL is in the format {HTTP/HTTPS}://{URL}:{port}. If using Altinity.Cloud, this is provided through the Cluster Access Point.

      

      For example:

      If the connection is on the specific IP 192.168.0.10 in your network on an unencrypted link then the URL would be http://192.168.0.10:8123.

      If using the Altinity.Cloud example above then the URL would be https://github.demo.trial.altinity.cloud:8443. The URL used for Grafana breaks down as follows:

      1. connectiontype: HTTP or HTTPS
      2. url: The fully qualified domain name or IP address of the ClickHouse server.
      3. port: The port being used. By default, HTTP is port 8123, HTTPS is port 8443.

   3. Auth: Only required if a ClickHouse username and password are specified.

      1. Basic auth: True
      2. With Credentials: True

   4. Basic Auth Details: Only required if Basic auth is set to True.

      1. User: The ClickHouse username.
      2. Password: The ClickHouse user password.

   5. Additional:

      1. Use Post method to send queries: True
      2. Default database: Select the database to be used without having to specify it in connections.
2. Select Save & test.

If the Save & test is successful, then the data source is available for use.

4.3 - Create Grafana Dashboards from ClickHouse Data

A Grafana server that has the ClickHouse plugins and connected a Data Source to a ClickHouse server can use that connection to populate charts.

In Grafana, the visualizations are organized into Dashboards containing one or more Panels. Each Panel is represented by a query to the ClickHouse server, the data used to create a visual representation.

The following examples will demonstrate how to create a Grafana dashboard, and populate it with the following types of visualizations:

• Stat: Used to display large statistical values.
• Time Series: Separates data into time segments, useful for tracking changes or patterns over time.
• World Map: Use latitude and longitude data to label information on a map.

Create a New Dashboard

To create a new Dashboard in Grafana:

1. From the left navigation panel, select + -> Dashboard.
2. Select the Save icon from the upper right menu.
3. Provide a name for the dashboard then select Save.

Once finished, new Grafana panels can be added by selecting Add Panel in the upper right hand corner.

Panel Examples

Sample Data

Sample data is provided by a ClickHouse database hosted by Altinity.Cloud. This database includes sample information for United States airline flights, http log file data, and other useful sets of data for testing ClickHouse and experimenting with querying data from it.

For more information on creating a Grafana Data Source with this sample Altinity.Cloud database, see How to Connect Grafana to ClickHouse

Create Big Stat Panel

Big stat panels are used to show single or prominent values. For examples, they can track the total number of servers that are down, total of shipments currently being managed, or other useful benchmarks.

The following example creates a Grafana Stat panel that displays the number of Carriers found on the table ontime, which tracks flight data across the United States. To create the Grafana Stat panel from the sample Altinity.Cloud data:

1. From Grafana, select the Dashboard to add the Stat panel to.
2. From the upper right hand menu select the Panel icon, then select Add Panel.
3. Select Add an Empty Panel.
4. Set the following:

   1. From the upper right hand corner, select the Visualization then select Stat.

   2. Set the Data source to your ClickHouse Data Source.

   3. In the Query field:

      1. Set the name by selecting the Name field and filling in whatever name you choose. In this case, it can be Total unique carriers.
      2. From
         1. Set –database– to default. If your data source connected to the Altinity.Cloud sample data, then this will be available as a drop down choice.
         2. Set –table– to ontime.

   4. Select Go to Query and enter the following SQL statement. Note that the ending ; is not included in this query, and that the query is using a variable marked with the $ symbol:

      select countDistinct(Carrier) FROM $table
      

   5. Set Format as Table.

5. Save the panel by selecting Save from the upper right corner. Provide a name for your panel such as Total Unique Carriers.

Depending on the connection, the values displayed will update within moments and display a single number as the total number of unique carriers.

Create Time Series Panel

A Time Series panel can display information over time segments, such as over a week, a month, or other sets of time. This provides a quick visual representation of how some measurement is comparing against similar sections of time. Such as comparing financial performance from one quarter to the next.

These steps will use the Altinity.Cloud demonstration database to show a count of flights grouped by carrier in monthly time segments.

To create a Time Series demonstration chart with the sample data:

1. From Grafana, select the Dashboard to add the Time Series panel to.

2. From the upper right hand menu select the Panel icon, then select Add Panel.

3. Select Add an Empty Panel.

4. Set the following:

   1. Set the Visualization in the upper right corner to Time Series.

   2. Set the Data source to your ClickHouse data source.

   3. In Query Options:

      1. Set Relative Time to 1M. This will accumulate one calendar month of data at a time.
      2. Time shift to 24M. This will have Grafana query 24 months of previous data from the current date.

   4. In the Query section:

      

      1. Set the name by selecting the Name field and filling in whatever name you choose. In this case, it can be Carrier Flights by Time Period.
      2. From:
         1. Set –database– to default. If your data source is connected to the Altinity.Cloud sample, then this will be available as a drop down choice.
         2. Set –table– to ontime.
      3. For the time series chart, set the following:
         1. Column:DateTime.
         2. In the –dateTime:col– field, set this to toDateTime(FlightDate). This informs Grafana of what field will be used for the time measurement.
         3. Set Column:Date to Column:Date.

   5. Select the query Edit icon and enter the following query:

      SELECT
          $timeSeries AS t,
          Carrier,
          count() Flights
      FROM $table
      
      WHERE $timeFilter
      
      GROUP BY
          t,
          Carrier
      ORDER BY
          t,
          Carrier
      

   Note that this query is using variables indicated by the $ symbol. This allows the table and time filter set in the previous steps to be automatically applied to the query without reconstructing the entire request.

5. Save the panel by selecting Save from the upper right corner. Provide a name for your panel such as Total Flights by Carriers Time Series.

For experimentation, change the Query Options to update the Time Shift and Relative Time to adjust the chart by different time metrics, such as 3M or 1Y for “three months” or “one year” respectively.

Create World Map Panel

One of the powerful features of Grafana is the ability to make use of Grafana plugins, some which are created by Grafana Labs, others by the community.

One custom panel is the Worldmap Panel, which allows latitude and longitude information to be charted on the image of a world map. Before beginning the steps below, complete the Worldmap Panel installation process for your Grafana server.

For this example, we will be combining the data from two tables from out Altinity.Cloud sample database:

• ontime: This tracks flight information for US flights across the United States.
• airports: This table stores information on airports, including their latitude and longitude.

These two tables will be combined through a JOIN query, and the information mapped onto the world panel map in Grafana showing the locations of airports on the map.

To create a world map panel with sample Altinity.Cloud airport data:

1. From Grafana, select the Dashboard to add the World Map panel to.

2. From the upper right hand menu select the Panel icon, then select Add Panel.

3. Select Add an Empty Panel.

4. Set the Visualization in the upper right corner to Worldmap Panel and set the following:

   

   1. Title: Create a title for the panel, such as Cancellations by Airport.

   2. Worldmap:

      1. Center: North America.
      2. Initial Zoom: 4
      3. Min Circle Size: 1
      4. Max Circle Size: 10

   3. Map Data Options:

      1. Location Data: table
      2. Aggregation: total

   4. Field Mapping:

      1. Table Query Format: coordinates
      2. Location Name Field: Origin
      3. Metric Field: Cancelled
      4. Latitude Field: Origin_Latitude
      5. Longitude Field: Origin_Longitude

   5. Threshold Options:

      

      1. Thresholds: 10000,50000
      2. Colors: Set the colors to Green, Orange and Red.

5. In the Data Source, select the ClickHouse data source created from How to Connect Grafana to ClickHouse.

6. In the Query section:

   1. Set the name by selecting the Name field and filling in whatever name you choose. In this case, it can be Flight Origins and Destinations.

   2. Select Edit icon and use the following query:

      SELECT Origin, 
          oa.Latitude as Origin_Latitude, oa.Longitude AS Origin_Longitude,  
          Flights, Cancelled, DepDelayed, ArrDelayed
      FROM (
      SELECT Origin, count() Flights,
          sum(Cancelled) Cancelled, 
          sum(DepDel15) DepDelayed,
          sum(ArrDel15) ArrDelayed
      FROM default.ontime
      GROUP BY Origin ORDER BY Cancelled DESC, Origin
      ) AS o 
      INNER JOIN default.airports AS oa ON toString(o.Origin) = oa.IATA
      LIMIT 100
      

   3. Set the Format as option as Table.

7. Save the panel by selecting Save from the upper right corner. Provide a name for your panel such as Cancelled Flights by Airport.

The panel will now display a world map, focused on the United States with circles at each airport. The colors will indicate how many late flights there were for each airport.

4.4 -

Setup the connection to ClickHouse demo site.

select toString(rand64()) as rnd_id, * from tripdata limit 100000000

ACOS( (SIN (RADIANS (LOOKUP (AVG ( [dropoff_latitude] ), [IsJFK] ) ) ) * SIN (RADIANS (AVG ( [dropoff_latitude] ) ) ) + COS (RADIANS (LOOKUP (AVG ( [dropoff_latitude] ) ), [IsJFK] ) ) ) * COS (RADIANS (AVG ( [dropoff_latitude] ) ) ) ) * COS (RADIANS (AVG ( [dropoff_longitude] ) ) - RADIANS (LOOKUP (AVG ( [dropoff_longitude] ) , [IsJFK] ) ) ) ) * 3959