motor de tabla PostgreSQL

El motor PostgreSQL permite consultas SELECT y INSERT sobre datos almacenados en un servidor PostgreSQL remoto.

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 [,..]]})
SETTINGS
    [ postgresql_connection_pool_size=16, ]
    [ postgresql_connection_pool_wait_timeout=5000, ]
    [ postgresql_connection_pool_retries=2, ]
    [ postgresql_connection_pool_auto_close_connection=false, ]
    [ postgresql_connection_attempt_timeout=2 ]
;

Consulte una descripción detallada de la consulta CREATE TABLE.

La estructura de la tabla puede diferir de la estructura de la tabla original de PostgreSQL:

• Los nombres de las columnas deben ser los mismos que en la tabla original de PostgreSQL, pero puede usar solo algunas de ellas y en cualquier orden.
• Los tipos de las columnas pueden diferir de los de la tabla original de PostgreSQL. ClickHouse intenta convertir los valores a los tipos de datos de ClickHouse.
• La configuración external_table_functions_use_nulls define cómo manejar las columnas Nullable. Valor predeterminado: 1. Si es 0, la función de tabla no crea columnas Nullable e inserta valores predeterminados en lugar de valores nulos. Esto también se aplica a los valores NULL dentro de arrays.

Parámetros del motor

• host:port — Dirección del servidor 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. Nota: añadir esta opción hará que la inserción sea menos eficiente.

Se recomienda usar colecciones con nombre (disponibles desde la versión 21.11) en entornos de producción. Aquí tiene un ejemplo:

<named_collections>
    <postgres_creds>
        <host>localhost</host>
        <port>5432</port>
        <user>postgres</user>
        <password>****</password>
        <schema>schema1</schema>
    </postgres_creds>
</named_collections>

Algunos parámetros pueden sobrescribirse mediante argumentos de clave-valor:

SELECT * FROM postgresql(postgres_creds, table='table1');

Los parámetros de TLS/SSL se reenvían a libpq y pueden establecerse como claves de una colección con nombre o como argumentos de clave-valor finales: sslmode (disable, allow, prefer, require, verify-ca o verify-full), así como los certificados y la clave, en una de dos formas. Si no se establecen, se aplican los valores predeterminados de libpq (sslmode=prefer).

• 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. Solo pueden especificarse en una colección con nombre definida en el archivo de configuración del servidor y no pueden sobrescribirse en una consulta: el servidor abre los archivos con sus propios privilegios.
• sslrootcert_pem, sslcert_pem y sslkey_pem aceptan el contenido literal del archivo correspondiente en lugar de una ruta. Pueden especificarse en cualquier lugar —en una consulta, en una colección con nombre creada con SQL o como sobrescritura de una colección con nombre— y se ocultan en los logs y las consultas SHOW, como las contraseñas.

Por ejemplo, para requerir una conexión cifrada y verificar el certificado del servidor:

<named_collections>
    <postgres_creds>
        <host>localhost</host>
        <port>5432</port>
        <user>postgres</user>
        <password>****</password>
        <sslmode>verify-full</sslmode>
        <sslrootcert>/etc/clickhouse-server/postgresql-ca.crt</sslrootcert>
    </postgres_creds>
</named_collections>

Lo mismo, pero sin un archivo de configuración, pasando el contenido del certificado en la consulta:

CREATE TABLE postgres_table (id UInt64, value String)
ENGINE = PostgreSQL('localhost:5432', 'database', 'table', 'user', 'password',
                    sslmode = 'verify-full', sslrootcert_pem = '-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----');

El grupo de conexiones utilizado por el motor de tabla PostgreSQL (y la función de tabla postgresql) puede configurarse para cada tabla mediante una cláusula SETTINGS. Cuando no se especifica ninguna configuración, se usa de forma predeterminada el valor de la configuración postgresql_* correspondiente a nivel de consulta.

Tamaño del grupo de conexiones (si todas las conexiones están en uso, la consulta espera hasta que se libere alguna). Debe ser distinto de cero.

Valor predeterminado: 16.

Tiempo de espera de push/pop del grupo de conexiones, en milisegundos, cuando el grupo está vacío. 0 significa que se bloquea si el grupo está vacío.

Valor predeterminado: 5000.

Número de reintentos al extraer o devolver conexiones del grupo de conexiones.

Valor predeterminado: 2.

Cierra la conexión antes de devolverla al grupo.

Valor predeterminado: false.

Tiempo de espera de la conexión, en segundos, para un único intento de conexión al endpoint de PostgreSQL. El valor se pasa como parámetro connect_timeout de la URL de conexión.

Valor predeterminado: 2.

Ejemplo:

CREATE TABLE pg_table
(
    `float_nullable` Nullable(Float32),
    `str` String,
    `int_id` Int32
)
ENGINE = PostgreSQL('localhost:5432', 'public', 'test', 'postgres_user', 'postgres_password')
SETTINGS postgresql_connection_pool_size = 32, postgresql_connection_pool_auto_close_connection = 1;

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 de un nombre de tabla, el argumento table puede ser una consulta SELECT que se pasa a PostgreSQL tal como está. La estructura de la tabla se infiere a partir del resultado de la consulta. La consulta puede escribirse como una subconsulta o envolverse en la función query:

CREATE TABLE pg_table ENGINE = PostgreSQL('localhost:5432', 'test', (SELECT a, b FROM t1 JOIN t2 USING (id) WHERE a > 0), 'user', 'password');
CREATE TABLE pg_table ENGINE = 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 la función 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 auto-commit después de cada sentencia INSERT.

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

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

CREATE TABLE test_replicas (id UInt32, name String) ENGINE = PostgreSQL(`postgres{2|3|4}:5432`, 'clickhouse', 'test_replicas', 'postgres', 'mysecretpassword');

Se admite la prioridad de las 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.

En el ejemplo siguiente, la réplica example01-1 tiene la prioridad más alta:

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

Este ejemplo utiliza el motor de tabla PostgreSQL para conectar la tabla de ClickHouse con la tabla de PostgreSQL y usar sentencias SELECT e INSERT en la base de datos PostgreSQL:

CREATE TABLE default.postgresql_table
(
    `float_nullable` Nullable(Float32),
    `str` String,
    `int_id` Int32
)
ENGINE = PostgreSQL('localhost:5432', 'public', 'test', 'postgres_user', 'postgres_password');

La función de tabla postgresql copia los datos de PostgreSQL a ClickHouse. Suele utilizarse para mejorar el rendimiento de las consultas al consultar los datos o realizar análisis en ClickHouse en lugar de PostgreSQL, y también puede usarse para migrar datos de PostgreSQL a ClickHouse. Como vamos a copiar los datos de PostgreSQL a ClickHouse, usaremos un motor de tabla MergeTree en ClickHouse y lo llamaremos postgresql_copy:

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

Si después va a realizar una sincronización continua entre la tabla de PostgreSQL y la tabla de ClickHouse tras la inserción inicial, puede usar una cláusula WHERE en ClickHouse para insertar solo los datos añadidos a PostgreSQL en función de un timestamp o de un ID de secuencia único.

Para ello, debe llevar un registro del ID máximo o del timestamp insertado previamente, como el siguiente:

SELECT max(`int_id`) AS maxIntID FROM default.postgresql_copy;

Luego, insertar los valores de la tabla de PostgreSQL que sean mayores que el máximo

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

SELECT * FROM postgresql_copy WHERE str IN ('test');

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

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

Véase también

• La función de tabla postgresql
• Uso de PostgreSQL como fuente de un diccionario

• Blog: ClickHouse y PostgreSQL: la combinación perfecta para los datos - parte 1
• Blog: ClickHouse y PostgreSQL: la combinación perfecta para los datos - parte 2