Skip to main content

Power BI

Power BI can load the data from ClickHouse Cloud or on-premise deployment using either the ODBC driver or ClickHouse Native connector. Both methods support Load mode, but the latter also supports Direct Query mode which eliminates the necessity to load the entire table.

This tutorial will guide you through the process of loading data using either of these methods.




ClickHouse Native Connector

1. Gather your connection details​

To connect to ClickHouse with HTTP(S) you need this information:

  • The HOST and PORT: typically, the port is 8443 when using TLS or 8123 when not using TLS.

  • The DATABASE NAME: out of the box, there is a database named default, use the name of the database that you want to connect to.

  • The USERNAME and PASSWORD: out of the box, the username is default. Use the username appropriate for your use case.

The details for your ClickHouse Cloud service are available in the ClickHouse Cloud console. Select the service that you will connect to and click Connect:

ClickHouse Cloud service connect button

Choose HTTPS, and the details are available in an example curl command.

ClickHouse Cloud HTTPS connection details

If you are using self-managed ClickHouse, the connection details are set by your ClickHouse administrator.

2. Install ClickHouse ODBC Client​

Download the most recent ClickHouse ODBC release from here. Execute the supplied .msi installer and follow the wizard. Optional "debug symbols" are not required, so you could keep everything default.

Installing the ODBC driver

When the driver installation is completed, you can verify the installation was successful: Search for ODBC in the Start menu and select "ODBC Data Sources (64-bit)".

Creating a new ODBC Data Source

Make sure ClickHouse Driver is listed.

Verify ODBC existence

In case you don't have Power BI installed yet, download and install Power BI Desktop.

3. Install ClickHouse Native Connector​

  • Create the following directory for the custom connector "[Documents]\Power BI Desktop\Custom Connectors" directory.
  • Download the latest release (.mez file) of the native connector from the Releases Section and place it in the directory you created in the previous step.
  • Open Power BI and enable unsigned connectors loading: File -> Options and settings -> Options -> Security -> Data Extensions -> Allow any extension to load without warning or validation
Enable unsigned connectors loading
  • Restart Power BI.

4. Get Data Into Power BI​

On the Power BI Desktop start screen, click "Get Data".

Getting started with Power BI Desktop

Search for "ClickHouseConnector (Beta)"

Choosing the data source

Select the connector, and fill the following boxes:

  • Server (required field) - Your instance domain/address. Make sure to add it with no prefixes/suffixes.
  • Port (required field) - Your instance port.
  • Database - Your database name.
  • Options - Any ODBC option as listed in ClickHouse ODBC GitHub Page
  • Data Connectivity mode - Choose DirectQuery for querying ClickHouse directly. In case you have a small load, you can choose import mode, and the entire data will be loaded to Power BI.
Filling ClickHouse instance information
  • Specify username and password
Username and password prompt

Finally, you should see the databases and tables in the Navigator view. Select the desired table and click "Load" to import the data from ClickHouse.

Navigator view

Once the import is complete, your ClickHouse Data should be accessible in Power BI as usual.


Power BI Service​

For cloud usage, please refer to Microsoft's documentation on how to Use custom data connectors with an on-premises data gateway.



ODBC Driver

Follow steps 1 and 2 from the Native Connector section above.

3. Create a new User DSN​

When the driver installation is complete, an ODBC data source can be created. Search for ODBC in the Start menu and select "ODBC Data Sources (64-bit)".

Creating a new ODBC Data Source

We need to add a new User DSN here. Click "Add" button on the left.

Adding a new User DSN

Choose the Unicode version of the ODBC driver.

Choosing Unicode Version

Fill in the connection details. The "Host" field should not include the protocol (i.e. omit http:// or https:// part). If you are using ClickHouse Cloud or your on-premise deployment has SSL enabled, type require in the "SSLMode" field. "Timeout" field value is set in seconds and, if omitted, the default value is 30 seconds.

Connection Details

4. Get Data Into Power BI​

In case you don't have Power BI installed yet, download and install Power BI Desktop.

On the Power BI Desktop start screen, click "Get Data".

Getting started with Power BI Desktop

Select "Other" -> "ODBC".

Data Sources menu

Select your previously created data source from the list.

Select ODBC Data Source

If you specified all the credentials during the data source creation, it should connect straight away. Otherwise, you will be prompted to specify username and password.

Navigator view

Finally, you should see the databases and tables in the Navigator view. Select the desired table and click "Load" to import the data from ClickHouse.

Navigator view

Once the import is complete, your ClickHouse Data should be accessible in Power BI as usual.



note

Unsigned integer types such as UInt64 or bigger won't be loaded into the dataset automatically, as Int64 is the maximum whole number type support by Power BI.
To import the data properly, before hitting the "Load" button in the Navigator, click "Transform Data" first.

In this example, pageviews table has a UInt64 column, which is recognized as "Binary" by default. "Transform Data" opens Power Query Editor, where we can reassign the type of the column, setting it as, for example, Text.

Navigator view

Once finished, click "Close & Apply" in the top left corner, and proceed with loading the data.