Looker Studio

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-visualization/looker-studio-and-clickhouse.md)

Partner Integration

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

## ClickHouse Cloud setup[​](#clickhouse-cloud-setup "Direct link to ClickHouse Cloud setup")

Select

#### Select `Connect your app`[​](#select-connect-your-app "Direct link to select-connect-your-app")

After creating your ClickHouse Cloud Service, on the `Connect your app` screen, select MySQL from the drop down.

Enable the MySQL interface

#### Enable the MySQL interface[​](#enable-mysql-interface "Direct link to Enable the MySQL interface")

Toggle the switch to enable the MySQL interface for this specific service.
This will expose port `3306` for this service and prompt you with a MySQL connection screen that includes your unique MySQL username.

Alternatively, in order to enable the MySQL interface for an existing service:

Select

#### Select `Connect`[​](#select-connect "Direct link to select-connect")

Ensure your service is in `Running` state then click on the service you want to enable the MySQL interface for.
Select "Connect" from the left menu:

Choose

#### Choose `MySQL`[​](#choose-mysql "Direct link to choose-mysql")

Select `MySQL` from the `Connect With` drop down.

Enable the MySQL interface

#### Enable the MySQL interface[​](#enable-mysql-interface "Direct link to Enable the MySQL interface")

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.

## Creating a readonly MySQL user in ClickHouse Cloud[​](#creating-multiple-mysql-users-in-clickhouse-cloud "Direct link to Creating a readonly MySQL user in ClickHouse Cloud")

ClickHouse Cloud automatically creates a `mysql4<subdomain>` user that shares the same password as the default user.
The `<subdomain>` portion corresponds to the first part of your ClickHouse Cloud hostname.

This username format is required for compatibility with tools that establish secure connections but don't include [SNI (Server Name Indication)](https://www.cloudflare.com/learning/ssl/what-is-sni) data in their TLS handshake.
Without SNI information, the system can't perform proper internal routing, so the subdomain hint embedded in the username provides the necessary routing information.
The MySQL console client is an example of a tool that requires this.

Tip

A recommended best practice is to create a new readonly MySQL user.

Note

For a 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`.

Create a readonly settings profile

#### Create a readonly settings profile[​](#create-a-custom-settings-user "Direct link to Create a readonly settings profile")

Create a [settings profile](/docs/sql-reference/statements/create/settings-profile) to apply to your readonly user,
setting the `readonly` setting to `1`:

```
CREATE SETTINGS PROFILE readonly_profile SETTINGS readonly = 1
```

Create a new readonly MySQL user

#### Create a new readonly MySQL user[​](#create-a-readonly-mysql-user "Direct link to Create a new readonly MySQL user")

[Create a user](/docs/sql-reference/statements/create/user) with a name following this format:

```
mysql4<subdomain>_<username>
```

Apply the `readonly_profile` to the new user and make sure that the password is in double SHA1 format. For example:

```
CREATE USER mysql4foobar_readonly
IDENTIFIED WITH double_sha1_password BY 'YourPassword42$'
SETTINGS PROFILE 'readonly_profile';
```

Grant the new user permissions to access the desired tables

#### Grant the new user permissions to access the desired tables[​](#grant-the-new-user-the-necessary-permissions "Direct link to Grant the new user permissions to access the desired tables")

[Grant](/docs/sql-reference/statements/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_readonly;
```

Note

For the readonly user, make sure to only grant `SELECT` permissions to the tables you want to access.

The newly created user can be used to connect to your ClickHouse Cloud service with the MySQL interface.

### Troubleshooting multiple MySQL users in ClickHouse Cloud[​](#troubleshooting-multiple-mysql-users-in-clickhouse-cloud "Direct link to 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](#creating-multiple-mysql-users-in-clickhouse-cloud)).

## On-premise ClickHouse server setup[​](#on-premise-clickhouse-server-setup "Direct link to On-premise ClickHouse server setup")

Please refer to [the official documentation](/docs/interfaces/mysql)
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](/docs/operations/settings/settings-users#user-namepassword)
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[​](#connecting-looker-studio-to-clickhouse "Direct link to Connecting Looker Studio to ClickHouse")