---
title: "ClickHouse - Query PostgreSQL and MySQL databases"
description: "Find out how to query, join and copy tables of PostgreSQL and MySQL databases, such as OVHcloud Public Cloud Databases services, from a ClickHouse cluster"
url: https://docs.ovhcloud.com/pl/guides/public-cloud/data-analytics/analytics/clickhouse-query-postgresql-mysql
lang: en
lastUpdated: 2026-10-13
---
> For AI agents: the complete documentation index is available at https://docs.ovhcloud.com/pl/llms.txt, the full documentation bundle is available at https://docs.ovhcloud.com/pl/llms-full.txt.

# ClickHouse - Query PostgreSQL and MySQL databases

## Objective

ClickHouse can query tables of a PostgreSQL or MySQL database without copying them first. You can read them, join them with your ClickHouse tables or with each other in the same query, copy them into ClickHouse, or write data back to PostgreSQL.

**This guide explains how to query PostgreSQL and MySQL databases, such as Public Cloud Databases for PostgreSQL and MySQL services, from a Public Cloud Analytics with ClickHouse cluster.**

## Requirements

- A [Public Cloud project](https://www.ovhcloud.com/pl/public-cloud/) in your OVHcloud account.
- A ClickHouse cluster running on OVHcloud Public Cloud and accepting incoming connections. See [How to create a ClickHouse cluster](https://docs.ovhcloud.com/pl/guides/public-cloud/data-analytics/analytics/clickhouse-create-cluster.md) and [How to connect to a ClickHouse cluster](https://docs.ovhcloud.com/pl/guides/public-cloud/data-analytics/analytics/clickhouse-connect-cluster-cli.md).
- The password of the `avnadmin` user of the ClickHouse cluster.
- A PostgreSQL or MySQL database reachable from the ClickHouse cluster, for example a [Public Cloud Databases](https://docs.ovhcloud.com/pl/guides/public-cloud/databases/getting-started.md) for PostgreSQL or MySQL service, and a user allowed to create users on it.

## Instructions

:::info
The ClickHouse examples in this guide are SQL queries. You can run them with `clickhouse-client` or over HTTPS, as described in [How to connect to a ClickHouse cluster](https://docs.ovhcloud.com/pl/guides/public-cloud/data-analytics/analytics/clickhouse-connect-cluster-cli.md).
:::

### Step 1 - Allowing the ClickHouse cluster on the source database

The source database must accept connections coming from your ClickHouse cluster. For a Public Cloud Databases service, add the outgoing IP address of the ClickHouse cluster to the authorised IPs of the source service (see how to configure incoming connections for [PostgreSQL](https://docs.ovhcloud.com/pl/guides/public-cloud/databases/postgresql-prepare-for-incoming-connections.md) or [MySQL](https://docs.ovhcloud.com/pl/guides/public-cloud/databases/mysql-prepare-for-incoming-connections.md)).

To find the outgoing IP address of your ClickHouse cluster, run this query on it:

```sql
SELECT * FROM url('https://checkip.amazonaws.com', 'RawBLOB');
```

The query returns the public IPv4 address the cluster uses for outgoing connections. Add it as a `/32` block, for example `203.0.113.25/32`.

### Step 2 - Creating a dedicated user on the source database

Use a user that has only the rights ClickHouse needs. For read-only access:

- on PostgreSQL:

```sql
CREATE ROLE clickhouse_reader LOGIN PASSWORD '<password>';
GRANT SELECT ON customers TO clickhouse_reader;
```

- on MySQL:

```sql
CREATE USER 'clickhouse_reader'@'%' IDENTIFIED BY '<password>';
GRANT SELECT ON defaultdb.orders TO 'clickhouse_reader'@'%';
```

You can find the host and port of each Public Cloud Databases service in the `Dashboard
` tab of the service, in the **Connection information**
 section.
### Step 3 - Querying a table

Use the `postgresql` and `mysql` table functions. They take the address of the server (`host:port`), the database, the table, the user and its password:

```sql
SELECT count(), uniqExact(country)
FROM postgresql('<pg_host>:<pg_port>', 'defaultdb', 'customers', 'clickhouse_reader', '<password>');

SELECT count(), sum(amount)
FROM mysql('<mysql_host>:<mysql_port>', 'defaultdb', 'orders', 'clickhouse_reader', '<password>');
```

### Step 4 - Storing the connection in a named collection

To avoid writing the password in each query, store the connection in a **named collection**. ClickHouse keeps it encrypted and never displays its values:

```sql
CREATE NAMED COLLECTION pg_source AS
    host = '<pg_host>',
    port = <pg_port>,
    database = 'defaultdb',
    user = 'clickhouse_reader',
    password = '<password>';
```

You can then create a table that points to the remote table, and query it like a ClickHouse table:

```sql
CREATE TABLE customers_pg (id Int32, name String, country String)
ENGINE = PostgreSQL(pg_source, table = 'customers');

SELECT count() FROM customers_pg;
```

### Step 5 - Joining several sources in one query

You can join PostgreSQL tables, MySQL tables and ClickHouse tables in the same query. In this example, orders stored in MySQL are joined with customers stored in PostgreSQL:

```sql
SELECT c.country, count() AS orders, round(sum(o.amount), 2) AS revenue
FROM mysql('<mysql_host>:<mysql_port>', 'defaultdb', 'orders', 'clickhouse_reader', '<password>') AS o
INNER JOIN customers_pg AS c ON o.customer_id = c.id
GROUP BY c.country
ORDER BY c.country;
```

### Step 6 - Copying a table into ClickHouse

To copy a table into a `MergeTree` table of your cluster:

```sql
CREATE TABLE orders_local
ENGINE = MergeTree
ORDER BY id
AS SELECT * FROM mysql('<mysql_host>:<mysql_port>', 'defaultdb', 'orders', 'clickhouse_reader', '<password>');
```

### Step 7 - Writing data to PostgreSQL

You can also send results from ClickHouse to a PostgreSQL table, with a user allowed to write to it:

```sql
INSERT INTO FUNCTION postgresql('<pg_host>:<pg_port>', 'defaultdb', 'customers', '<writer_user>', '<password>')
VALUES (99999, 'from-clickhouse', 'FR', now());
```

### Limitations

- Access is at table level only: creating a ClickHouse database that mirrors a whole PostgreSQL or MySQL database (`ENGINE = PostgreSQL` or `ENGINE = MySQL` on `CREATE DATABASE`) is not available and returns `Only Replicated database can be created through SQL`.

### Troubleshooting

| Error                                                       | Cause and solution                                                                                                                                                      |
| ----------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `Connection to … failed with error: … timeout expired`      | The source database does not accept connections from the ClickHouse cluster. Check that the outgoing IP of the cluster is in the authorised IPs of the source (step 1). |
| `std::exception` on an `INSERT INTO FUNCTION postgresql(…)` | The user is not allowed to write to the PostgreSQL table. Use a user with `INSERT` rights on it.                                                                        |
| `Only Replicated database can be created through SQL`       | Database engines are not available. Use the table functions or table engines in this guide.                                                                             |

## Go further

- [ClickHouse documentation - postgresql table function](https://clickhouse.com/docs/reference/functions/table-functions/postgresql)
- [ClickHouse documentation - mysql table function](https://clickhouse.com/docs/reference/functions/table-functions/mysql)
- [ClickHouse documentation - PostgreSQL table engine](https://clickhouse.com/docs/reference/engines/table-engines/integrations/postgresql)
- [ClickHouse - Query files stored in OVHcloud Object Storage](https://docs.ovhcloud.com/pl/guides/public-cloud/data-analytics/analytics/clickhouse-query-object-storage.md)

We would love to help answer questions and appreciate any feedback you may have.

For training or technical assistance implementing our solutions, contact your sales representative or visit our [Professional Services](https://www.ovhcloud.com/pl/professional-services/) page to request a quote and have your project analysed by our experts.

Are you on Discord? Connect to our channel at [https://discord.gg/ovhcloud](https://discord.gg/ovhcloud) and interact directly with the team that builds our Analytics service!

Join our [community of users](https://community.ovhcloud.com/).