postgresql - ClickHouse Documentation

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

Syntaxe

postgresql({host:port, database, table, user, password[, schema, [, on_conflict]] | named_collection[, option=value [,..]]} [, SETTINGS name=value, ...])

Arguments

Argument	Description
`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 à PostgreSQL telle quelle (voir passer une requête au lieu d’un nom de table).
`user`	Utilisateur PostgreSQL.
`password`	Mot de passe de l’utilisateur.
`schema`	Schéma de table autre que celui par défaut. Facultatif.
`on_conflict`	Stratégie de résolution des conflits. Exemple : `ON CONFLICT DO NOTHING`. Facultatif.

Les arguments peuvent également être transmis à l’aide de collections nommées. Dans ce cas, host et port doivent être indiqués séparément. Cette approche est recommandée en production.

Valeur renvoyée

Un objet de type table avec les mêmes colonnes que la table PostgreSQL d’origine.

Dans la requête INSERT, pour distinguer la fonction de table postgresql(...) d’un nom de table accompagné d’une liste de noms de colonnes, vous devez utiliser les mots-clés FUNCTION ou TABLE FUNCTION. Voir les exemples ci-dessous.

Paramètres

Le pool de connexions utilisé par la fonction de table postgresql (ainsi que par le moteur de table PostgreSQL) peut être configuré à l’aide d’une clause SETTINGS en fin d’instruction. Lorsqu’un paramètre n’est pas spécifié, il prend par défaut la valeur du paramètre postgresql_* correspondant au niveau de la requête. Consultez la section Paramètres du moteur de table pour obtenir la liste complète des paramètres postgresql_connection_pool_* et postgresql_connection_attempt_timeout, ainsi que leurs valeurs par défaut. Exemple :

SELECT * FROM postgresql('localhost:5432', 'test', 'test', 'postgresql_user', 'password', SETTINGS postgresql_connection_pool_size = 32);

Détails d’implémentation

Les requêtes SELECT côté PostgreSQL s’exécutent sous la forme de COPY (SELECT ...) TO STDOUT à l’intérieur d’une transaction PostgreSQL en lecture seule, avec un commit 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 PostgreSQL terminée.

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

Au lieu d’un nom de table, le troisième argument peut être une requête SELECT transmise telle quelle à PostgreSQL. La structure de la table résultante est inférée à partir du résultat de la requête. La requête peut être écrite soit comme une sous-requête, soit encapsulée dans la fonction query :

SELECT * FROM postgresql('localhost:5432', 'test', (SELECT a, b FROM t1 JOIN t2 USING (id) WHERE a > 0), 'user', 'password');
SELECT * FROM postgresql('localhost:5432', 'test', query('SELECT a, b FROM t1 JOIN t2 USING (id) WHERE a > 0'), 'user', 'password');

Cela est utile pour déléguer à PostgreSQL les jointures, les agrégations ou tout autre traitement. 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 le moteur de table PostgreSQL.

La forme de sous-requête (SELECT ...) est analysée par ClickHouse puis re-sérialisée dans le dialecte PostgreSQL (quotage des identifiants PostgreSQL et échappement des littéraux de chaîne) avant d’être envoyée au serveur. Elle doit donc être valide en ClickHouse SQL. Pour transmettre une syntaxe spécifique à PostgreSQL que ClickHouse n’analyse pas, utilisez la forme query('...'), dont le texte est envoyé tel quel à PostgreSQL.Toute clause externe WHERE, LIMIT, agrégation, etc. de la requête ClickHouse englobante n’est pas déléguée à la requête transmise — elle est appliquée dans ClickHouse après récupération du résultat complet de la requête. Pour restreindre 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élégué est rejeté avec une exception au lieu d’être appliqué localement.

Les requêtes INSERT côté PostgreSQL s’exécutent sous la forme de COPY "table_name" (field1, field2, ... fieldN) FROM STDIN à l’intérieur d’une transaction PostgreSQL avec commit automatique après chaque instruction INSERT. Les types Array de PostgreSQL sont convertis en tableaux ClickHouse.

Attention : dans PostgreSQL, une colonne de type tableau comme Integer[] peut contenir des tableaux de dimensions différentes selon les lignes, mais dans ClickHouse, seuls des tableaux multidimensionnels de même dimension sont autorisés dans toutes les lignes.

Prend en charge plusieurs répliques, qui doivent être séparées par |. Par exemple :

SELECT name FROM postgresql(`postgres{1|2|3}:5432`, 'postgres_database', 'postgres_table', 'user', 'password');

SELECT name FROM postgresql(`postgres1:5431|postgres2:5432`, 'postgres_database', 'postgres_table', 'user', 'password');

Prend en charge la priorité des répliques pour la source de dictionnaire PostgreSQL. Plus le nombre dans la map est élevé, plus la priorité est faible. La priorité la plus élevée est 0.

Exemples

Table dans 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)

Sélection de données dans ClickHouse à l’aide d’arguments simples :

SELECT * FROM postgresql('localhost:5432', 'test', 'test', 'postgresql_user', 'password') WHERE str IN ('test');

Ou en utilisant des collections nommées :

CREATE NAMED COLLECTION mypg AS
        host = 'localhost',
        port = 5432,
        database = 'test',
        user = 'postgresql_user',
        password = 'password';
SELECT * FROM postgresql(mypg, table='test') WHERE str IN ('test');

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

Insertion :

INSERT INTO TABLE FUNCTION postgresql('localhost:5432', 'test', 'test', 'postgrsql_user', 'password') (int_id, float) VALUES (2, 3);
SELECT * FROM postgresql('localhost:5432', 'test', 'test', 'postgresql_user', 'password');

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

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');

Répliquer ou migrer des données Postgres avec PeerDB

En plus des fonctions de table, vous pouvez également utiliser PeerDB de ClickHouse pour mettre en place un pipeline de données continu de Postgres vers ClickHouse. PeerDB est un outil spécialement conçu pour répliquer des données de Postgres vers ClickHouse à l’aide de la capture de données modifiées (CDC).

​Syntaxe

​Arguments

​Valeur renvoyée

​Paramètres

​Détails d’implémentation

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

​Exemples

​Voir aussi

​Répliquer ou migrer des données Postgres avec PeerDB

Syntaxe

Arguments

Valeur renvoyée

Paramètres

Détails d’implémentation

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

Exemples

Voir aussi

Répliquer ou migrer des données Postgres avec PeerDB