ClickHouse - Interroger des bases PostgreSQL et MySQL

Objectif

ClickHouse peut interroger les tables d'une base PostgreSQL ou MySQL sans les copier au préalable. Vous pouvez les lire, les joindre à vos tables ClickHouse ou entre elles dans une même requête, les copier dans ClickHouse, ou écrire des données dans PostgreSQL.

Ce guide explique comment interroger des bases PostgreSQL et MySQL, comme les services Public Cloud Databases pour PostgreSQL et MySQL, depuis un cluster Public Cloud Analytics avec ClickHouse.

Prérequis

• Disposer d'un projet Public Cloud dans votre compte OVHcloud.
• Disposer d'un cluster ClickHouse en cours d'exécution sur OVHcloud Public Cloud et acceptant les connexions entrantes. Consultez les guides « Comment créer un cluster ClickHouse » et « Comment se connecter à un cluster ClickHouse ».
• Disposer du mot de passe de l'utilisateur avnadmin du cluster ClickHouse.
• Disposer d'une base PostgreSQL ou MySQL joignable depuis le cluster ClickHouse, par exemple un service Public Cloud Databases pour PostgreSQL ou MySQL, et d'un utilisateur autorisé à y créer des utilisateurs.

En pratique

Étape 1 - Autoriser le cluster ClickHouse sur la base source

La base source doit accepter les connexions provenant de votre cluster ClickHouse. Pour un service Public Cloud Databases, ajoutez l'adresse IP sortante du cluster ClickHouse aux IP autorisées du service source (consultez la procédure de configuration des connexions entrantes pour PostgreSQL ou MySQL).

Pour connaître l'adresse IP sortante de votre cluster ClickHouse, exécutez cette requête sur celui-ci :

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

La requête renvoie l'adresse IPv4 publique utilisée par le cluster pour ses connexions sortantes. Ajoutez-la sous la forme d'un bloc /32, par exemple 203.0.113.25/32.

Étape 2 - Créer un utilisateur dédié sur la base source

Utilisez un utilisateur qui dispose uniquement des droits nécessaires à ClickHouse. Pour un accès en lecture seule :

• sur PostgreSQL :

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

• sur MySQL :

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

Vous trouverez l'hôte et le port de chaque service Public Cloud Databases dans l'onglet Dashboard du service, section Informations de connection.

Étape 3 - Interroger une table

Utilisez les fonctions de table postgresql et mysql. Elles prennent l'adresse du serveur (hôte:port), la base, la table, l'utilisateur et son mot de passe :

SELECT count(), uniqExact(country)
FROM postgresql('<hote_pg>:<port_pg>', 'defaultdb', 'customers', 'clickhouse_reader', '<mot_de_passe>');

SELECT count(), sum(amount)
FROM mysql('<hote_mysql>:<port_mysql>', 'defaultdb', 'orders', 'clickhouse_reader', '<mot_de_passe>');

Étape 4 - Enregistrer la connexion dans une named collection

Pour ne pas écrire le mot de passe dans chaque requête, enregistrez la connexion dans une named collection. ClickHouse la conserve chiffrée et n'en affiche jamais les valeurs :

CREATE NAMED COLLECTION pg_source AS
    host = '<hote_pg>',
    port = <port_pg>,
    database = 'defaultdb',
    user = 'clickhouse_reader',
    password = '<mot_de_passe>';

Vous pouvez ensuite créer une table qui pointe vers la table distante, et l'interroger comme une table ClickHouse :

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

SELECT count() FROM customers_pg;

Étape 5 - Joindre plusieurs sources dans une même requête

Vous pouvez joindre des tables PostgreSQL, MySQL et ClickHouse dans une même requête. Dans cet exemple, les commandes stockées dans MySQL sont jointes aux clients stockés dans PostgreSQL :

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

Étape 6 - Copier une table dans ClickHouse

Pour copier une table dans une table MergeTree de votre cluster :

CREATE TABLE orders_local
ENGINE = MergeTree
ORDER BY id
AS SELECT * FROM mysql('<hote_mysql>:<port_mysql>', 'defaultdb', 'orders', 'clickhouse_reader', '<mot_de_passe>');

Étape 7 - Écrire des données dans PostgreSQL

Vous pouvez aussi envoyer des résultats de ClickHouse vers une table PostgreSQL, avec un utilisateur autorisé à y écrire :

INSERT INTO FUNCTION postgresql('<hote_pg>:<port_pg>', 'defaultdb', 'customers', '<utilisateur_ecriture>', '<mot_de_passe>')
VALUES (99999, 'from-clickhouse', 'FR', now());

Limites

• L'accès se fait table par table : créer une base ClickHouse qui reflète toute une base PostgreSQL ou MySQL (ENGINE = PostgreSQL ou ENGINE = MySQL sur CREATE DATABASE) n'est pas disponible et renvoie Only Replicated database can be created through SQL.

Résolution des problèmes

Aller plus loin

• Documentation ClickHouse - fonction de table postgresql
• Documentation ClickHouse - fonction de table mysql
• Documentation ClickHouse - moteur de table PostgreSQL
• ClickHouse - Interroger des fichiers stockés dans OVHcloud Object Storage

Nous serions ravis de répondre à vos questions et apprécions tout retour que vous pourriez nous faire.

Pour une formation ou une assistance technique sur la mise en œuvre de nos solutions, contactez votre commercial ou consultez la page Professional Services pour obtenir un devis et faire analyser votre projet par nos experts.

Vous êtes sur Discord ? Rejoignez notre chaîne via https://discord.gg/ovhcloud et interagissez directement avec l'équipe qui développe notre service Analytics !

Rejoignez notre communauté d'utilisateurs.