postgresql

Permite ejecutar consultas SELECT e INSERT sobre datos almacenados en un servidor PostgreSQL remoto.

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

Los argumentos también pueden pasarse mediante colecciones con nombre. En este caso, host y port deben especificarse por separado. Este enfoque se recomienda para entornos de producción.

Los parámetros TLS/SSL se reenvían a libpq y pueden proporcionarse como claves de colecciones con nombre o como argumentos de clave-valor al final: sslmode (disable, allow, prefer, require, verify-ca o verify-full; si no se especifica, se aplica el valor predeterminado prefer de libpq), así como los certificados y la clave, de una de estas dos formas. sslrootcert (certificado de CA o el valor especial system), sslcert (certificado de cliente) y sslkey (clave privada de cliente) son rutas a archivos locales del servidor y solo pueden especificarse en una colección con nombre definida en el archivo de configuración del servidor. Como alternativa, sslrootcert_pem, sslcert_pem y sslkey_pem aceptan el contenido literal del archivo correspondiente —por ejemplo, postgresql('host:port', 'database', 'table', 'user', 'password', sslmode = 'verify-full', sslrootcert_pem = '...')— y se enmascaran en los registros y las consultas SHOW, al igual que una contraseña.

Un objeto de tipo tabla con las mismas columnas que la tabla original de PostgreSQL.

El pool de conexiones que utiliza la función de tabla postgresql (y el motor de tabla PostgreSQL) puede configurarse con una cláusula SETTINGS al final. Si no se especifica una configuración, se usa de forma predeterminada el valor de la configuración postgresql_* correspondiente a nivel de consulta. Consulte la sección Configuración del motor de tabla para ver la lista completa de las configuraciones postgresql_connection_pool_* y postgresql_connection_attempt_timeout, así como sus valores predeterminados.

Ejemplo:

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

Las consultas SELECT del lado de PostgreSQL se ejecutan como COPY (SELECT ...) TO STDOUT dentro de una transacción de PostgreSQL de solo lectura, con commit después de cada consulta SELECT.

Las cláusulas WHERE simples, como =, !=, >, >=, <, <= e IN, se ejecutan en el servidor PostgreSQL.

Todos los joins, las agregaciones, la ordenación, las condiciones IN [ array ] y la restricción de muestreo LIMIT se ejecutan en ClickHouse solo después de que finaliza la consulta a PostgreSQL.

En lugar del nombre de una tabla, el tercer argumento puede ser una consulta SELECT que se pasa a PostgreSQL tal cual. La estructura de la tabla resultante se infiere a partir del resultado de la consulta. La consulta puede escribirse como una subconsulta o envolverse en la función 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');

Esto es útil para hacer pushdown de joins, agregaciones o cualquier otro procesamiento a PostgreSQL. Dicha tabla es de solo lectura: no se permite hacer INSERT en ella. La misma sintaxis es compatible con el motor de tabla PostgreSQL.

Las consultas INSERT del lado de PostgreSQL se ejecutan como COPY "table_name" (field1, field2, ... fieldN) FROM STDIN dentro de una transacción de PostgreSQL, con autocommit después de cada sentencia INSERT.

Los tipos Array de PostgreSQL se convierten en arrays de ClickHouse.

Admite varias réplicas, que deben listarse con |. Por ejemplo:

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

o

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

Admite prioridades de réplicas para la fuente para diccionario de PostgreSQL. Cuanto mayor sea el número en el mapa, menor será la prioridad. La prioridad más alta es 0.

Tabla en 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)

Seleccionar datos de ClickHouse con argumentos simples:

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

O bien usando colecciones con nombre:

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 │           ᴺᵁᴸᴸ │
└────────┴──────────────┴───────┴──────┴────────────────┘

Inserción:

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 │      │           ᴺᵁᴸᴸ │
└────────┴──────────────┴───────┴──────┴────────────────┘

Uso de un esquema distinto del predeterminado:

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

• El motor de tabla de PostgreSQL
• Uso de PostgreSQL como fuente para diccionarios

Además de las funciones de tabla, también puedes usar PeerDB de ClickHouse para configurar un pipeline de datos continuo de Postgres a ClickHouse. PeerDB es una herramienta diseñada específicamente para replicar datos de Postgres a ClickHouse mediante captura de cambios de datos (CDC).