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. 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 [,..]]})
- 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>
SELECT * FROM postgresql(postgres_creds, table='table1');
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.
|. Par exemple :
CREATE TABLE test_replicas (id UInt32, name String) ENGINE = PostgreSQL(`postgres{2|3|4}:5432`, 'clickhouse', 'test_replicas', 'postgres', 'mysecretpassword');
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>
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
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
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
SELECT max(`int_id`) AS maxIntID FROM default.postgresql_copy;
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');
