postgresql

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

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

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.

Les paramètres TLS/SSL sont transmis à libpq et peuvent être fournis sous forme de clés de collection nommée ou d’arguments clé-valeur ajoutés à la fin : sslmode (disable, allow, prefer, require, verify-ca ou verify-full ; s’il n’est pas défini, la valeur par défaut prefer de libpq s’applique), ainsi que les certificats et la clé, sous l’une de deux formes. sslrootcert (certificat d’AC ou valeur spéciale system), sslcert (certificat client) et sslkey (clé privée du client) sont des chemins vers des fichiers locaux au serveur et ne peuvent être spécifiés que dans une collection nommée définie dans le fichier de configuration du serveur. sslrootcert_pem, sslcert_pem et sslkey_pem acceptent à la place le contenu littéral du fichier correspondant — par exemple, postgresql('host:port', 'database', 'table', 'user', 'password', sslmode = 'verify-full', sslrootcert_pem = '...') — et sont masqués dans les journaux et les requêtes SHOW, comme un mot de passe.

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

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

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.

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.

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.

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

ou

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.

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

• Le moteur de table PostgreSQL
• Utiliser PostgreSQL comme source pour un dictionnaire

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