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 in your OVHcloud account.
• A ClickHouse cluster running on OVHcloud Public Cloud and accepting incoming connections. See How to create a ClickHouse cluster and How to connect to a ClickHouse cluster.
• 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 for PostgreSQL or MySQL service, and a user allowed to create users on it.

Instructions

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 or MySQL).

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

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:

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

• on MySQL:

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:

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:

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:

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:

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:

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:

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

Go further

• ClickHouse documentation - postgresql table function
• ClickHouse documentation - mysql table function
• ClickHouse documentation - PostgreSQL table engine
• ClickHouse - Query files stored in OVHcloud Object Storage

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 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 and interact directly with the team that builds our Analytics service!

Join our community of users.