Connect Superset to ClickHouse

How to 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.

    Select Data -> Databases
  3. Select + Database.

    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 the Network Hardening section in the ClickHouse Operations Guide.
  5. Select Test Connection to verify the connection.

    Select Test Connection
  6. If the connection works, then click Add.

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

    Database connection added.

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 the Network Hardening section in the ClickHouse Operations Guide.

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.