Conectar ClickHouse con PostgreSQL

Esta página cubre las siguientes opciones para integrar PostgreSQL con ClickHouse:

usar el motor de tabla PostgreSQL para leer desde una tabla de PostgreSQL
usar el motor de base de datos experimental MaterializedPostgreSQL para sincronizar una base de datos de PostgreSQL con una base de datos de ClickHouse

Consulta nuestro servicio Managed Postgres. Gracias a su almacenamiento NVMe, ubicado físicamente junto al cómputo, ofrece un rendimiento hasta 10 veces superior para cargas de trabajo limitadas por disco en comparación con alternativas que usan almacenamiento conectado por red como EBS, y te permite replicar tus datos de Postgres en ClickHouse mediante el conector Postgres CDC de ClickPipes.

Uso del motor de tabla PostgreSQL

El motor de tabla PostgreSQL permite realizar operaciones SELECT e INSERT sobre datos almacenados en un servidor PostgreSQL remoto desde ClickHouse. Este artículo ilustra métodos básicos de integración usando una sola tabla.

Configurar PostgreSQL

En postgresql.conf, añada la siguiente entrada para permitir que PostgreSQL escuche en las interfaces de red:

listen_addresses = '*'

Cree un usuario para la conexión desde ClickHouse. Con fines de demostración, este ejemplo concede privilegios completos de superusuario.

CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';

Cree una nueva base de datos en PostgreSQL:

CREATE DATABASE db_in_psg;

Cree una nueva tabla:

CREATE TABLE table1 (
    id         integer primary key,
    column1    varchar(10)
);

Añadamos unas cuantas filas para probar:

INSERT INTO table1
  (id, column1)
VALUES
  (1, 'abc'),
  (2, 'def');

Para configurar PostgreSQL de modo que permita conexiones de replicación a la nueva base de datos con el nuevo usuario, agregue la siguiente entrada al archivo pg_hba.conf. Actualice la línea de dirección con la subred o la dirección IP de su servidor PostgreSQL:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    db_in_psg             clickhouse_user 192.168.1.0/24          password

Recargue la configuración pg_hba.conf (ajuste este comando según su versión):

/usr/pgsql-12/bin/pg_ctl reload

Verifique que el nuevo clickhouse_user pueda iniciar sesión:

psql -U clickhouse_user -W -d db_in_psg -h <your_postgresql_host>

Si usa esta funcionalidad en ClickHouse Cloud, puede que necesite permitir que las direcciones IP de ClickHouse Cloud accedan a su instancia de PostgreSQL. Consulte la API de Cloud Endpoints de ClickHouse para obtener detalles sobre el tráfico de salida.

Definir una tabla en ClickHouse

Inicie sesión en clickhouse-client:

clickhouse-client --user default --password ClickHouse123!

Cree una nueva base de datos:

CREATE DATABASE db_in_ch;

Cree una tabla que use PostgreSQL:

CREATE TABLE db_in_ch.table1
(
    id UInt64,
    column1 String
)
ENGINE = PostgreSQL('postgres-host.domain.com:5432', 'db_in_psg', 'table1', 'clickhouse_user', 'ClickHouse_123');

Los parámetros mínimos necesarios son:

parámetro	Descripción	ejemplo
host:port	host o IP y puerto	postgres-host.domain.com:5432
database	nombre de la base de datos de PostgreSQL	db_in_psg
user	nombre de usuario para conectarse a Postgres	clickhouse_user
password	contraseña para conectarse a Postgres	ClickHouse_123

Consulte la página de documentación del motor de tabla PostgreSQL para obtener una lista completa de parámetros.

Probar la integración

En ClickHouse, consulte las filas iniciales:

SELECT * FROM db_in_ch.table1

La tabla de ClickHouse debería llenarse automáticamente con las dos filas que ya existían en la tabla de PostgreSQL:

Query id: 34193d31-fe21-44ac-a182-36aaefbd78bf

┌─id─┬─column1─┐
│  1 │ abc     │
│  2 │ def     │
└────┴─────────┘

De nuevo en PostgreSQL, añada un par de filas a la tabla:

INSERT INTO table1
  (id, column1)
VALUES
  (3, 'ghi'),
  (4, 'jkl');

Esas dos filas nuevas deberían aparecer en tu tabla de ClickHouse:

SELECT * FROM db_in_ch.table1

La respuesta debería ser:

Query id: 86fa2c62-d320-4e47-b564-47ebf3d5d27b

┌─id─┬─column1─┐
│  1 │ abc     │
│  2 │ def     │
│  3 │ ghi     │
│  4 │ jkl     │
└────┴─────────┘

Veamos qué ocurre cuando añades filas a la tabla de ClickHouse:

INSERT INTO db_in_ch.table1
  (id, column1)
VALUES
  (5, 'mno'),
  (6, 'pqr');

Las filas agregadas en ClickHouse deberían aparecer en la tabla de PostgreSQL:

db_in_psg=# SELECT * FROM table1;
id | column1
----+---------
  1 | abc
  2 | def
  3 | ghi
  4 | jkl
  5 | mno
  6 | pqr
(6 rows)

Este ejemplo mostró la integración básica entre PostgreSQL y ClickHouse mediante el motor de tabla PostrgeSQL. Consulta la página de documentación del motor de tabla PostgreSQL para ver más funcionalidades, como especificar esquemas, devolver solo un subconjunto de columnas y conectarse a varias réplicas. Consulta también el blog ClickHouse and PostgreSQL - a match made in data heaven - part 1.

Uso del motor de base de datos MaterializedPostgreSQL

El motor de base de datos PostgreSQL utiliza las funciones de replicación de PostgreSQL para crear una réplica de la base de datos con todos los esquemas y tablas, o con un subconjunto de ellos. Este artículo ilustra métodos básicos de integración con una base de datos, un esquema y una tabla. En los procedimientos siguientes se utilizan PostgreSQL CLI (psql) y ClickHouse CLI (clickhouse-client). El servidor PostgreSQL está instalado en Linux. A continuación se muestra la configuración mínima si la base de datos PostgreSQL es una instalación de prueba nueva

En PostgreSQL

En postgresql.conf, configure los niveles mínimos de escucha, el nivel WAL de replicación y los slots de replicación:

agregue las siguientes entradas:

listen_addresses = '*'
max_replication_slots = 10
wal_level = logical

*ClickHouse necesita como mínimo el nivel de wal logical y al menos 2 slots de replicación

Con una cuenta de administrador, cree un usuario para conectarse desde ClickHouse:

CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';

*con fines de demostración, se han concedido privilegios completos de superusuario.

cree una nueva base de datos:

CREATE DATABASE db1;

conéctese a la nueva base de datos desde psql:

\connect db1

cree una nueva tabla:

CREATE TABLE table1 (
    id         integer primary key,
    column1    varchar(10)
);

añade las filas iniciales:

INSERT INTO table1
(id, column1)
VALUES
(1, 'abc'),
(2, 'def');

Configure PostgreSQL para permitir conexiones a la nueva base de datos con el nuevo usuario de replicación. A continuación se muestra la entrada mínima que se debe añadir al archivo pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    db1             clickhouse_user 192.168.1.0/24          password

*para fines de demostración, aquí se utiliza el método de autenticación con contraseña en texto claro. actualice la línea address con la subred o la dirección del servidor, según la documentación de PostgreSQL

vuelva a cargar la configuración de pg_hba.conf con algo como esto (ajústelo según su versión):

/usr/pgsql-12/bin/pg_ctl reload

Pruebe a iniciar sesión con el nuevo clickhouse_user:

 psql -U clickhouse_user -W -d db1 -h <your_postgresql_host>

En ClickHouse

inicie sesión en la CLI de ClickHouse

clickhouse-client --user default --password ClickHouse123!

Habilite la funcionalidad experimental de PostgreSQL en el motor de base de datos:

SET allow_experimental_database_materialized_postgresql=1

Cree la nueva base de datos que se replicará y defina la tabla inicial:

CREATE DATABASE db1_postgres
ENGINE = MaterializedPostgreSQL('postgres-host.domain.com:5432', 'db1', 'clickhouse_user', 'ClickHouse_123')
SETTINGS materialized_postgresql_tables_list = 'table1';

opciones mínimas:

parámetro	Descripción	ejemplo
host:port	nombre de host o IP y puerto	postgres-host.domain.com:5432
database	nombre de la base de datos PostgreSQL	db1
user	nombre de usuario para conectarse a Postgres	clickhouse_user
password	contraseña para conectarse a Postgres	ClickHouse_123
settings	ajustes adicionales del motor	materialized_postgresql_tables_list = ‘table1’

Para obtener una guía completa sobre el motor de base de datos PostgreSQL, consulte https://clickhouse.com/docs/engines/database-engines/materialized-postgresql/#settings

Verifique que la tabla inicial tenga datos:

ch_env_2 :) select * from db1_postgres.table1;

SELECT *
FROM db1_postgres.table1

Query id: df2381ac-4e30-4535-b22e-8be3894aaafc

┌─id─┬─column1─┐
│  1 │ abc     │
└────┴─────────┘
┌─id─┬─column1─┐
│  2 │ def     │
└────┴─────────┘

Probar la replicación básica

En PostgreSQL, añada nuevas filas:

INSERT INTO table1
(id, column1)
VALUES
(3, 'ghi'),
(4, 'jkl');

En ClickHouse, verifique que las nuevas filas sean visibles:

ch_env_2 :) select * from db1_postgres.table1;

SELECT *
FROM db1_postgres.table1

Query id: b0729816-3917-44d3-8d1a-fed912fb59ce

┌─id─┬─column1─┐
│  1 │ abc     │
└────┴─────────┘
┌─id─┬─column1─┐
│  4 │ jkl     │
└────┴─────────┘
┌─id─┬─column1─┐
│  3 │ ghi     │
└────┴─────────┘
┌─id─┬─column1─┐
│  2 │ def     │
└────┴─────────┘

Resumen

Esta guía de integración se centra en un ejemplo sencillo de cómo replicar una base de datos con una tabla; sin embargo, existen opciones más avanzadas que incluyen replicar toda la base de datos o agregar nuevas tablas y esquemas a las replicaciones existentes. Aunque los comandos DDL no son compatibles con esta replicación, se puede configurar el motor para detectar cambios y volver a cargar las tablas cuando se realicen cambios estructurales.

Para conocer más funcionalidades disponibles en las opciones avanzadas, consulte la documentación de referencia.

ClickPipes para BigQuery

Acceso programático

Otros

Uso del motor de tabla PostgreSQL

Uso del motor de base de datos MaterializedPostgreSQL

​Uso del motor de tabla PostgreSQL

​Uso del motor de base de datos MaterializedPostgreSQL

Uso del motor de tabla PostgreSQL

Uso del motor de base de datos MaterializedPostgreSQL