Tableau Online

Tableau Online can connect to ClickHouse Cloud or on-premise ClickHouse setup via MySQL interface using the official MySQL data source.

ClickHouse Cloud Setup

1. After creating your ClickHouse Cloud Service, on the credentials screen, select the MySQL tab
2. Toggle the switch to enable the MySQL interface for this specific service. This will expose port 3306 for this service and prompt you with your MySQL connection screen that include your unique MySQL username. The password will be the same as the service's default user password. Alternatively, in order to enable the MySQL interface for an existing service:
3. Ensure your service is in Running state then click on the "View connection string" button for the service you want to enable the MySQL interface for
4. Toggle the switch to enable the MySQL interface for this specific service. This will prompt you to enter the default password.
5. After entering the password, you will get prompted the MySQL connection string for this service

Creating multiple MySQL users in ClickHouse Cloud

By default, there is a built-in mysql4<subdomain> user, which uses the same password as the default one. The <subdomain> part is the first segment of your ClickHouse Cloud hostname. This format is necessary to work with the tools that implement secure connection, but don't provide SNI information in their TLS handshake, which makes it impossible to do the internal routing without an extra hint in the username (MySQL console client is one of such tools).

Because of this, we highly recommend following the mysql4<subdomain>_<username> format when creating a new user intended to be used with the MySQL interface, where <subdomain> is a hint to identify your Cloud service, and <username> is an arbitrary suffix of your choice.

Tip

For ClickHouse Cloud hostname like foobar.us-east1.aws.clickhouse.cloud, the <subdomain> part equals to foobar, and a custom MySQL username could look like mysql4foobar_team1.

You can create extra users to use with the MySQL interface if, for example, you need to apply extra settings.

1. Optional - create a settings profile to apply for your custom user. For example, my_custom_profile with an extra setting which will be applied by default when we connect with the user we create later:

   CREATE SETTINGS PROFILE my_custom_profile SETTINGS prefer_column_name_to_alias=1;

   prefer_column_name_to_alias is used just as an example, you can use other settings there.

2. Create a user using the following format: mysql4<subdomain>_<username> (see above). The password must be in double SHA1 format. For example:

   CREATE USER mysql4foobar_team1 IDENTIFIED WITH double_sha1_password BY 'YourPassword42$';

   or if you want to use a custom profile for this user:

   CREATE USER mysql4foobar_team1 IDENTIFIED WITH double_sha1_password BY 'YourPassword42$' SETTINGS PROFILE 'my_custom_profile';

   where my_custom_profile is the name of the profile you created earlier.

3. Grant the new user the necessary permissions to interact with the desired tables or databases. For example, if you want to grant access to system.query_log only:

   GRANT SELECT ON system.query_log TO mysql4foobar_team1;

4. Use the created user to connect to your ClickHouse Cloud service with the MySQL interface.

Troubleshooting multiple MySQL users in ClickHouse Cloud

If you created a new MySQL user, and you see the following error while connecting via MySQL CLI client:

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading authorization packet', system error: 54

In this case, ensure that the username follows the mysql4<subdomain>_<username> format, as described (above).

On-premise ClickHouse Server Setup

Please refer to the official documentation on how to set up a ClickHouse server with enabled MySQL interface.

Aside from adding an entry to the server's config.xml

<clickhouse>
    <mysql_port>9004</mysql_port>
</clickhouse>

it is also required to use Double SHA1 password encryption for the user that will be using MySQL interface.

Generating a random password encrypted with Double SHA1 from the shell:

PASSWORD=$(base64 < /dev/urandom | head -c16); echo "$PASSWORD"; echo -n "$PASSWORD" | sha1sum | tr -d '-' | xxd -r -p | sha1sum | tr -d '-'

The output should look like the following:

LZOQYnqQN4L/T6L0
fbc958cc745a82188a51f30de69eebfc67c40ee4

The first line is the generated password, and the second line is the hash we could use to configure ClickHouse.

Here is an example configuration for mysql_user that uses the generated hash:

/etc/clickhouse-server/users.d/mysql_user.xml

<users>
    <mysql_user>
        <password_double_sha1_hex>fbc958cc745a82188a51f30de69eebfc67c40ee4</password_double_sha1_hex>
        <networks>
            <ip>::/0</ip>
        </networks>
        <profile>default</profile>
        <quota>default</quota>
    </mysql_user>
</users>

Replace password_double_sha1_hex entry with your own generated Double SHA1 hash.

Additionally, it is recommended to use use_mysql_types_in_show_columns to show the native MySQL types instead of ClickHouse ones in SHOW [FULL] COLUMNS query results, which allows BI tools to properly introspect the database schema when using MySQL connectors.

For example:

/etc/clickhouse-server/users.d/mysql_user.xml

<profiles>
    <default>
        <use_mysql_types_in_show_columns>1</use_mysql_types_in_show_columns>
    </default>
</profiles>

or assign it to a different profile instead of the default one.

If you have the mysql binary available, you can test the connection from the commandline. Using the sample username (mysql_user) and password (LZOQYnqQN4L/T6L0) from above the command line would be:

mysql --protocol tcp -h localhost -u mysql_user -P 9004 --password=LZOQYnqQN4L/T6L0

mysql> show databases;
+--------------------+
| name               |
+--------------------+
| INFORMATION_SCHEMA |
| default            |
| information_schema |
| system             |
+--------------------+
4 rows in set (0.00 sec)
Read 4 rows, 603.00 B in 0.00156 sec., 2564 rows/sec., 377.48 KiB/sec.

Finally, configure the Clickhouse Server to listen on the desired IP address(es). For example, in config.xml, uncomment out the following to listen on all addresses:

<listen_host>::</listen_host> 

Connecting Tableau Online to ClickHouse (on-premise without SSL)

Login to your Tableau Cloud site and add a new Published Data Source.

Select "MySQL" from the list of available connectors.

Specify your connection details gathered during the ClickHouse setup.

Tableau Online will introspect the database and provide a list of available tables. Drag the desired table to the canvas on the right. Additionally, you can click "Update Now" to preview the data, as well as fine-tune the introspected field types or names.

After that, all that remains is to click "Publish As" in the top right corner, and you should be able to use a newly created dataset in Tableau Online as usual.

NB: if you want to use Tableau Online in combination with Tableau Desktop and share ClickHouse datasets between them, make sure you use Tableau Desktop with the default MySQL connector as well, following the setup guide that is displayed here if you select MySQL from the Data Source drop-down. If you have an M1 Mac, check this troubleshooting thread for a driver installation workaround.

Connecting Tableau Online to ClickHouse (Cloud or on-premise setup with SSL)

As it is not possible to provide the SSL certificates via the Tableau Online MySQL connection setup wizard, the only way is to use Tableau Desktop to set the connection up, and then export it to Tableau Online. This process is, however, pretty straightforward.

Run Tableau Desktop on a Windows or Mac machine, and select "Connect" -> "To a Server" -> "MySQL". Likely, it will be required to install the MySQL driver on your machine first. You can do that by following the setup guide that is displayed here if you select MySQL from the Data Source drop-down. If you have an M1 Mac, check this troubleshooting thread for a driver installation workaround.

Note

In the MySQL connection setup UI, make sure that the "SSL" option is enabled. ClickHouse Cloud's SSL certificate is signed by LetsEncrypt. You can download this root cert here.

Provide your ClickHouse Cloud instance MySQL user credentials and the path to the downloaded root certificate.

Choose the desired tables as usual (similarly to Tableau Online), and select "Server" -> "Publish Data Source" -> Tableau Cloud.

IMPORTANT: you need to select "Embedded password" in "Authentication" options.

Additionally, choose "Update workbook to use the published data source".

Finally, click "Publish", and your datasource with embedded credentials will be opened automatically in Tableau Online.

Known limitations (ClickHouse 23.11)

All the known limitations has been fixed in ClickHouse 23.11. If you encounter any other incompatibilities, please do not hesitate to contact us or create a new issue.