postgresql - ClickHouse Documentation

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

Sintaxis

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

Argumentos

Argumento	Descripción
`host:port`	Dirección del servidor de PostgreSQL.
`database`	Nombre de la base de datos remota.
`table`	Nombre de la tabla remota o una consulta pasada a PostgreSQL tal cual (consulte Pasar una consulta en lugar de un nombre de tabla).
`user`	Usuario de PostgreSQL.
`password`	Contraseña del usuario.
`schema`	Esquema de tabla distinto del predeterminado. Opcional.
`on_conflict`	Estrategia de resolución de conflictos. Ejemplo: `ON CONFLICT DO NOTHING`. Opcional.

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.

Valor devuelto

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

En la consulta INSERT, para distinguir la función de tabla postgresql(...) del nombre de la tabla con una lista de nombres de columnas, debe usar las palabras clave FUNCTION o TABLE FUNCTION. Vea los ejemplos a continuación.

Configuración

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

Detalles de implementación

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.

Pasar una consulta en lugar del nombre de una tabla

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.

La forma de subconsulta (SELECT ...) es analizada por ClickHouse y se vuelve a serializar en el dialecto de PostgreSQL (entrecomillado de identificadores de PostgreSQL y escaping de literales de cadena) antes de enviarse al servidor. Por lo tanto, debe ser una consulta ClickHouse SQL válida. Para pasar sintaxis específica de PostgreSQL que ClickHouse no analiza, usa la forma query('...'), cuyo texto se envía a PostgreSQL literalmente.Cualquier WHERE, LIMIT, agregación, etc., externos de la consulta de ClickHouse circundante no se hacen pushdown en la consulta pasada; se aplican en ClickHouse después de recuperar el resultado completo de la consulta. Para restringir los datos leídos desde PostgreSQL, coloca el filtro dentro de la consulta pasada. Con external_table_strict_query = 1, un filtro externo que no puede hacerse pushdown se rechaza con una excepción en lugar de aplicarse localmente.

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.

Ten cuidado: en PostgreSQL, una columna de tipo array como Integer[] puede contener arrays de distintas dimensiones en diferentes filas, pero en ClickHouse solo se permite tener arrays multidimensionales de la misma dimensión en todas las filas.

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

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

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

Ejemplos

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

Replicar o migrar datos de Postgres con PeerDB

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

​Sintaxis

​Argumentos

​Valor devuelto

​Configuración

​Detalles de implementación

​Pasar una consulta en lugar del nombre de una tabla

​Ejemplos

​Relacionado

​Replicar o migrar datos de Postgres con PeerDB

Sintaxis

Argumentos

Valor devuelto

Configuración

Detalles de implementación

Pasar una consulta en lugar del nombre de una tabla

Ejemplos

Relacionado

Replicar o migrar datos de Postgres con PeerDB