Conectar ClickHouse con PostgreSQL

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

listen_addresses = '*'

2. 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';

3. Cree una nueva base de datos en PostgreSQL:

CREATE DATABASE db_in_psg;

4. Cree una nueva tabla:

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

5. Añadamos unas cuantas filas para probar:

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

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

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

/usr/pgsql-12/bin/pg_ctl reload

8. Verifique que el nuevo clickhouse_user pueda iniciar sesión:

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

1. Inicie sesión en clickhouse-client:

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

2. Cree una nueva base de datos:

CREATE DATABASE db_in_ch;

3. 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:

1. 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     │
└────┴─────────┘

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

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

4. 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     │
└────┴─────────┘

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

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

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

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

3. cree una nueva base de datos:

CREATE DATABASE db1;

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

\connect db1

5. cree una nueva tabla:

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

6. añade las filas iniciales:

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

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

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

9. Pruebe a iniciar sesión con el nuevo clickhouse_user:

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

1. inicie sesión en la CLI de ClickHouse

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

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

SET allow_experimental_database_materialized_postgresql=1

3. 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:

4. 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     │
└────┴─────────┘

1. En PostgreSQL, añada nuevas filas:

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

2. 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     │
└────┴─────────┘

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.