Moteur de table PostgreSQL

Le moteur PostgreSQL permet d’exécuter des requêtes SELECT et INSERT sur des données stockées sur un serveur PostgreSQL distant.

Le moteur PostgreSQL permet d’exécuter des requêtes SELECT et INSERT sur des données stockées sur un serveur PostgreSQL distant.

À l’heure actuelle, seules les versions 12 et ultérieures de PostgreSQL sont prises en charge par ce moteur de table.

Découvrez notre service Managed Postgres. Reposant sur un stockage NVMe physiquement colocalisé avec les ressources de calcul, il offre des performances jusqu’à 10x supérieures pour les charges de travail limitées par le disque par rapport aux alternatives utilisant un stockage en réseau comme EBS, et vous permet de répliquer vos données Postgres vers ClickHouse à l’aide du connecteur Postgres CDC dans ClickPipes.

​

Création d’une table

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 type1 [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 type2 [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = PostgreSQL({host:port, database, table, user, password[, schema, [, on_conflict]] | named_collection[, option=value [,..]]})

Voir une description détaillée de la requête CREATE TABLE.

La structure de la table peut différer de celle de la table PostgreSQL d’origine :

• Les noms de colonnes doivent être les mêmes que dans la table PostgreSQL d’origine, mais vous pouvez n’utiliser qu’une partie de ces colonnes, dans n’importe quel ordre.
• Les types de colonnes peuvent différer de ceux de la table PostgreSQL d’origine. ClickHouse essaie de convertir les valeurs vers les types de données ClickHouse.
• Le paramètre external_table_functions_use_nulls définit la manière de gérer les colonnes Nullable. Valeur par défaut : 1. Si la valeur est 0, la fonction de table ne crée pas de colonnes Nullable et insère des valeurs par défaut à la place des valeurs NULL. Cela s’applique également aux valeurs NULL dans les tableaux.

Paramètres du moteur

• host:port — Adresse du serveur PostgreSQL.
• database — Nom de la base de données distante.
• table — Nom de la table distante.
• user — Utilisateur PostgreSQL.
• password — Mot de passe de l’utilisateur.
• schema — Schéma de table autre que le schéma par défaut. Facultatif.
• on_conflict — Stratégie de résolution des conflits. Exemple : ON CONFLICT DO NOTHING. Facultatif. Remarque : l’ajout de cette option réduit l’efficacité de l’insertion.

Les collections nommées (disponibles depuis la version 21.11) sont recommandées en production. Voici un exemple :

<named_collections>
    <postgres_creds>
        <host>localhost</host>
        <port>5432</port>
        <user>postgres</user>
        <password>****</password>
        <schema>schema1</schema>
    </postgres_creds>
</named_collections>

Certains paramètres peuvent être redéfinis par des arguments clé-valeur :

SELECT * FROM postgresql(postgres_creds, table='table1');

​

Détails d’implémentation

Les requêtes SELECT côté PostgreSQL s’exécutent sous la forme COPY (SELECT ...) TO STDOUT dans une transaction PostgreSQL en lecture seule, avec validation après chaque requête SELECT.

Les clauses WHERE simples telles que =, !=, >, >=, <, <= et IN sont exécutées sur le serveur PostgreSQL.

Toutes les jointures, agrégations, opérations de tri, conditions IN [ array ] et la contrainte d’échantillonnage LIMIT sont exécutées dans ClickHouse uniquement une fois la requête vers PostgreSQL terminée.

Les requêtes INSERT côté PostgreSQL s’exécutent sous la forme COPY "table_name" (field1, field2, ... fieldN) FROM STDIN dans une transaction PostgreSQL, avec validation automatique après chaque instruction INSERT.

Les types PostgreSQL Array sont convertis en tableaux ClickHouse.

Attention : dans PostgreSQL, une donnée de type tableau, créée sous la forme type_name[], peut contenir des tableaux multidimensionnels ayant un nombre de dimensions différent selon les lignes d’une même colonne. En revanche, dans ClickHouse, seuls les tableaux multidimensionnels ayant le même nombre de dimensions dans toutes les lignes d’une même colonne sont autorisés.

Prend en charge plusieurs répliques, qui doivent être listées à l’aide de |. Par exemple :

CREATE TABLE test_replicas (id UInt32, name String) ENGINE = PostgreSQL(`postgres{2|3|4}:5432`, 'clickhouse', 'test_replicas', 'postgres', 'mysecretpassword');

La définition de priorités pour les répliques d’une source de dictionnaire PostgreSQL est prise en charge. Plus le nombre dans la map est élevé, plus la priorité est faible. La priorité la plus élevée est 0.

Dans l’exemple ci-dessous, la réplique example01-1 a la priorité la plus élevée :

<postgresql>
    <port>5432</port>
    <user>clickhouse</user>
    <password>qwerty</password>
    <replica>
        <host>example01-1</host>
        <priority>1</priority>
    </replica>
    <replica>
        <host>example01-2</host>
        <priority>2</priority>
    </replica>
    <db>db_name</db>
    <table>table_name</table>
    <where>id=10</where>
    <invalidate_query>SQL_QUERY</invalidate_query>
</postgresql>
</source>

​

Exemple d’utilisation

​

Table PostgreSQL

postgres=# CREATE TABLE "public"."test" (
"int_id" SERIAL,
"int_nullable" INT NULL DEFAULT NULL,
"float" FLOAT NOT NULL,
"str" VARCHAR(100) NOT NULL DEFAULT '',
"float_nullable" FLOAT NULL DEFAULT NULL,
PRIMARY KEY (int_id));

CREATE TABLE

postgres=# INSERT INTO test (int_id, str, "float") VALUES (1,'test',2);
INSERT 0 1

postgresql> SELECT * FROM test;
  int_id | int_nullable | float | str  | float_nullable
 --------+--------------+-------+------+----------------
       1 |              |     2 | test |
 (1 row)

​

Création d’une table dans ClickHouse et connexion à la table PostgreSQL créée ci-dessus

Cet exemple utilise le moteur de table PostgreSQL pour relier la table ClickHouse à la table PostgreSQL et exécuter des instructions SELECT et INSERT sur la base de données PostgreSQL :

CREATE TABLE default.postgresql_table
(
    `float_nullable` Nullable(Float32),
    `str` String,
    `int_id` Int32
)
ENGINE = PostgreSQL('localhost:5432', 'public', 'test', 'postgres_user', 'postgres_password');

​

Insertion des données initiales d’une table PostgreSQL dans une table ClickHouse à l’aide d’une requête SELECT

La fonction de table postgresql copie les données de PostgreSQL vers ClickHouse. Elle est souvent utilisée pour améliorer les performances des requêtes sur ces données en les interrogeant ou en effectuant des analyses dans ClickHouse plutôt que dans PostgreSQL, mais elle peut aussi servir à migrer des données de PostgreSQL vers ClickHouse. Comme nous allons copier les données de PostgreSQL vers ClickHouse, nous utiliserons dans ClickHouse un moteur de table MergeTree, que nous appellerons postgresql_copy:

CREATE TABLE default.postgresql_copy
(
    `float_nullable` Nullable(Float32),
    `str` String,
    `int_id` Int32
)
ENGINE = MergeTree
ORDER BY (int_id);

INSERT INTO default.postgresql_copy
SELECT * FROM postgresql('localhost:5432', 'public', 'test', 'postgres_user', 'postgres_password');

​

Insertion de données incrémentielles de la table PostgreSQL dans la table ClickHouse

Si vous mettez ensuite en place une synchronisation continue entre la table PostgreSQL et la table ClickHouse après l’insertion initiale, vous pouvez utiliser une clause WHERE dans ClickHouse pour n’insérer que les données ajoutées à PostgreSQL en fonction d’un timestamp ou d’un identifiant de séquence unique.

Cela implique de conserver la valeur maximale de l’ID ou du timestamp précédemment inséré, comme suit :

SELECT max(`int_id`) AS maxIntID FROM default.postgresql_copy;

Puis insertion des valeurs de la table PostgreSQL supérieures à la valeur maximale

INSERT INTO default.postgresql_copy
SELECT * FROM postgresql('localhost:5432', 'public', 'test', 'postgres_user', 'postgres_password')
WHERE int_id > (SELECT max(int_id) FROM default.postgresql_copy);

​

Sélection des données dans la table ClickHouse obtenue

SELECT * FROM postgresql_copy WHERE str IN ('test');

┌─float_nullable─┬─str──┬─int_id─┐
│           ᴺᵁᴸᴸ │ test │      1 │
└────────────────┴──────┴────────┘

​

Utiliser un schéma non par défaut

postgres=# CREATE SCHEMA "nice.schema";

postgres=# CREATE TABLE "nice.schema"."nice.table" (a integer);

postgres=# INSERT INTO "nice.schema"."nice.table" SELECT i FROM generate_series(0, 99) as t(i)

CREATE TABLE pg_table_schema_with_dots (a UInt32)
        ENGINE PostgreSQL('localhost:5432', 'clickhouse', 'nice.table', 'postgrsql_user', 'password', 'nice.schema');

Voir aussi

• La fonction de table postgresql
• Utiliser PostgreSQL comme source pour un dictionnaire

​

Articles connexes

• Blog : ClickHouse et PostgreSQL - une alliance parfaite au paradis des données - partie 1
• Blog : ClickHouse et PostgreSQL - une alliance parfaite au paradis des données - partie 2