Moteur de table PostgreSQL - ClickHouse Documentation

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 [,..]]})
SETTINGS
    [ postgresql_connection_pool_size=16, ]
    [ postgresql_connection_pool_wait_timeout=5000, ]
    [ postgresql_connection_pool_retries=2, ]
    [ postgresql_connection_pool_auto_close_connection=false, ]
    [ postgresql_connection_attempt_timeout=2 ]
;

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, ou une requête transmise telle quelle à PostgreSQL (voir Utilisation d’une requête à la place d’un nom de table).
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');

Paramètres

Le pool de connexions utilisé par le moteur de table PostgreSQL (ainsi que par la fonction de table postgresql) peut être configuré pour chaque table au moyen d’une clause SETTINGS. Si un paramètre n’est pas indiqué, la valeur du paramètre postgresql_* correspondant au niveau de la requête est utilisée par défaut.

`postgresql_connection_pool_size`

Taille du pool de connexions (si toutes les connexions sont utilisées, la requête attend qu’une connexion se libère). Doit être non nulle. Valeur par défaut : 16.

`postgresql_connection_pool_wait_timeout`

Délai d’attente, en millisecondes, pour les opérations push/pop du pool de connexions lorsque celui-ci est vide. 0 signifie que l’opération bloque lorsque le pool est vide. Valeur par défaut : 5000.

`postgresql_connection_pool_retries`

Nombre de tentatives de push/pop du pool de connexions. Valeur par défaut : 2.

`postgresql_connection_pool_auto_close_connection`

Fermer la connexion avant de la remettre dans le pool. Valeur par défaut : false.

`postgresql_connection_attempt_timeout`

Délai d’expiration, en secondes, pour une tentative unique de connexion au point de terminaison PostgreSQL. La valeur est transmise en tant que paramètre connect_timeout dans l’URL de connexion. Valeur par défaut : 2. Exemple :

CREATE TABLE pg_table
(
    `float_nullable` Nullable(Float32),
    `str` String,
    `int_id` Int32
)
ENGINE = PostgreSQL('localhost:5432', 'public', 'test', 'postgres_user', 'postgres_password')
SETTINGS postgresql_connection_pool_size = 32, postgresql_connection_pool_auto_close_connection = 1;

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.

Passer une requête au lieu d’un nom de table

Au lieu d’un nom de table, l’argument table peut être une requête SELECT transmise telle quelle à PostgreSQL. La structure de la table est déduite du résultat de la requête. La requête peut être écrite soit sous forme de sous-requête, soit encapsulée dans la fonction query :

CREATE TABLE pg_table ENGINE = PostgreSQL('localhost:5432', 'test', (SELECT a, b FROM t1 JOIN t2 USING (id) WHERE a > 0), 'user', 'password');
CREATE TABLE pg_table ENGINE = PostgreSQL('localhost:5432', 'test', query('SELECT a, b FROM t1 JOIN t2 USING (id) WHERE a > 0'), 'user', 'password');

Cela est utile pour déporter les jointures, les agrégations ou tout autre traitement vers PostgreSQL. Une telle table est en lecture seule : les requêtes INSERT n’y sont pas autorisées. La même syntaxe est prise en charge par la fonction de table postgresql.

La forme de sous-requête (SELECT ...) est analysée par ClickHouse puis re-sérialisée dans le dialecte PostgreSQL (guillemets d’identifiants PostgreSQL et échappement des littéraux de chaîne) avant d’être envoyée au serveur. Elle doit donc être valide en SQL ClickHouse. Pour transmettre une syntaxe spécifique à PostgreSQL que ClickHouse n’analyse pas, utilisez la forme query('...'), dont le texte est envoyé à PostgreSQL tel quel.Toute clause externe WHERE, LIMIT, agrégation, etc. de la requête ClickHouse englobante n’est pas déportée dans la requête transmise — elle est appliquée dans ClickHouse après récupération du résultat complet de la requête. Pour limiter les données lues depuis PostgreSQL, placez le filtre dans la requête transmise. Avec external_table_strict_query = 1, un filtre externe qui ne peut pas être déporté est rejeté avec une exception au lieu d’être appliqué localement.

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

​Création d’une table

​Paramètres

​postgresql_connection_pool_size

​postgresql_connection_pool_wait_timeout

​postgresql_connection_pool_retries

​postgresql_connection_pool_auto_close_connection

​postgresql_connection_attempt_timeout

​Détails d’implémentation

​Passer une requête au lieu d’un nom de table

​Exemple d’utilisation

​Table PostgreSQL

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

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

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

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

​Utiliser un schéma non par défaut

​Articles connexes

Création d’une table

Paramètres

`postgresql_connection_pool_size`

`postgresql_connection_pool_wait_timeout`

`postgresql_connection_pool_retries`

`postgresql_connection_pool_auto_close_connection`

`postgresql_connection_attempt_timeout`

Détails d’implémentation

Passer une requête au lieu d’un nom de table

Exemple d’utilisation

Table PostgreSQL

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

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

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

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

Utiliser un schéma non par défaut

Articles connexes