Skip to main content

Looker Studio

Looker Studio can connect to ClickHouse via the MySQL interface using the official Google MySQL data source.

ClickHouse Cloud Setup

  1. After creating your ClickHouse Cloud Service, on the credentials screen, select the MySQL tab Credentials screen - Prompt
  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. Credentials screen - Enabled MySQL 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 Connection screen - Prompt MySQL
  4. Toggle the switch to enable the MySQL interface for this specific service. This will prompt you to enter the default password. Connection screen - Prompt MySQL
  5. After entering the password, you will get prompted the MySQL connection string for this service Connection screen -  MySQL Enabled

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 Looker Studio to ClickHouse

First, login to https://lookerstudio.google.com using your Google account and create a new Data Source:

Creating a new data source

Search for the official MySQL connector provided by Google (named just MySQL):

MySQL connector search

Specify your connection details. Please note that MySQL interface port is 9004 by default, and it might be different depending on your server configuration.

Specifying the connection details

Now, you have two options on how to fetch the data from ClickHouse. First, you could use the Table Browser feature:

Using the Table Browser

Alternatively, you could specify a custom query to fetch your data:

Using a custom query to fetch the data

Finally, you should be able to see the introspected table structure and adjust the data types if necessary.

Viewing the introspected table structure

Now you can proceed with exploring your data or creating a new report!

Using Looker Studio with ClickHouse Cloud

When using ClickHouse Cloud, you need to enable MySQL interface first. You can do that in connection dialog, "MySQL" tab.

Looker Studio Require MySQL enabled first

In the Looker Studio UI, choose the "Enable SSL" option. ClickHouse Cloud's SSL certificate is signed by LetsEncrypt. You can download this root cert here.

Looker Studio with ClickHouse Cloud SSL Config

The rest of the steps are the same as listed above in the previous section.

Create Multiple MySQL Users in Cloud

Besides the built-in mysql4<subdomain> user, you can also create multiple users for the MySQL interface.

  1. Create a user using the following format: mysql4<subdomain>_<suffix>. The password must be in double SHA1 format. For example:

    CREATE USER mysql4abcdefg123_team1 IDENTIFIED WITH double_sha1_password BY 'YourPassword42$';
  2. Grant the new user the necessary permission to interact with the database:

    GRANT SELECT ON system.query_log TO mysql4abcdefg123_team1;
  3. Use the created user to connect to the MySQL interface.