ClickHouse를 PostgreSQL에 연결하기

이 페이지에서는 PostgreSQL을 ClickHouse와 통합하는 다음 옵션을 설명합니다:

PostgreSQL 테이블에서 읽기 위해 PostgreSQL 테이블 엔진을 사용하는 방법
PostgreSQL의 데이터베이스를 ClickHouse의 데이터베이스와 동기화하기 위해 experimental MaterializedPostgreSQL 데이터베이스 엔진을 사용하는 방법

PostgreSQL 테이블 엔진 사용하기

PostgreSQL 테이블 엔진을 사용하면 ClickHouse에서 원격 PostgreSQL 서버에 저장된 데이터에 대해 SELECT 및 INSERT 작업을 수행할 수 있습니다. 이 문서에서는 하나의 테이블을 사용해 통합하는 기본 방법을 설명합니다.

PostgreSQL 설정하기

postgresql.conf에 PostgreSQL이 네트워크 인터페이스를 통해 수신 대기하도록 다음 항목을 추가합니다:

listen_addresses = '*'

ClickHouse에서 접속할 사용자를 생성합니다. 설명을 위해 이 예시에서는 전체 슈퍼유저 권한을 부여합니다.

CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';

PostgreSQL에 새 데이터베이스를 생성합니다:

CREATE DATABASE db_in_psg;

새 테이블을 생성합니다:

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

테스트를 위해 행 몇 개를 추가해 보겠습니다:

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

복제를 위해 새 사용자로 새 데이터베이스에 연결할 수 있도록 PostgreSQL을 구성하려면 pg_hba.conf 파일에 다음 항목을 추가하십시오. 주소 줄은 PostgreSQL 서버의 서브넷 또는 IP 주소에 맞게 업데이트하십시오:

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

pg_hba.conf 구성을 다시 로드합니다(사용 중인 버전에 따라 이 명령을 조정하세요):

/usr/pgsql-12/bin/pg_ctl reload

새 clickhouse_user로 로그인할 수 있는지 확인합니다:

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

ClickHouse에서 테이블 정의하기

clickhouse-client에 접속합니다:

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

새 데이터베이스를 생성해 보겠습니다:

CREATE DATABASE db_in_ch;

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

필요한 최소 매개변수는 다음과 같습니다:

parameter	Description	example
host:port	호스트명 또는 IP 주소와 포트	postgres-host.domain.com:5432
database	PostgreSQL 데이터베이스 이름	db_in_psg
user	PostgreSQL에 연결할 사용자 이름	clickhouse_user
password	PostgreSQL에 연결할 비밀번호	ClickHouse_123

통합 테스트

ClickHouse에서 초기 행을 조회합니다:

SELECT * FROM db_in_ch.table1

ClickHouse 테이블에는 PostgreSQL 테이블에 이미 존재하던 2개의 행이 자동으로 반영되어 있어야 합니다:

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

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

다시 PostgreSQL로 돌아가 테이블에 행 2개를 추가합니다:

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

새로 추가된 2개의 행이 ClickHouse 테이블에 표시되어야 합니다:

SELECT * FROM db_in_ch.table1

응답은 다음과 같아야 합니다:

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

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

ClickHouse 테이블에 행을 추가했을 때 어떤 일이 발생하는지 살펴보겠습니다:

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

ClickHouse에 추가된 행이 PostgreSQL 테이블에 표시됩니다:

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

이 예시에서는 PostrgeSQL 테이블 엔진을 사용해 PostgreSQL과 ClickHouse를 기본적으로 통합하는 방법을 설명했습니다. 스키마 지정, 일부 컬럼만 반환, 여러 레플리카 연결과 같은 추가 기능은 PostgreSQL 테이블 엔진 문서 페이지에서 확인하십시오. 또한 ClickHouse and PostgreSQL - a match made in data heaven - part 1 블로그도 참고하십시오.

MaterializedPostgreSQL 데이터베이스 엔진 사용

ClickHouse Cloud에서 지원되지 않음

실험 기능

PostgreSQL 데이터베이스 엔진은 PostgreSQL 복제 기능을 사용하여 전체 또는 일부 스키마와 테이블을 포함하는 데이터베이스 레플리카를 생성합니다. 이 문서에서는 하나의 데이터베이스, 하나의 스키마, 하나의 테이블을 사용하는 기본적인 통합 방법을 설명합니다.

다음 절차에서는 PostgreSQL CLI (psql)와 ClickHouse CLI (clickhouse-client)를 사용합니다. PostgreSQL 서버는 Linux에 설치되어 있습니다. 아래 내용은 PostgreSQL 데이터베이스를 새로 테스트 설치한 경우의 최소 설정입니다.

PostgreSQL에서

postgresql.conf에서 최소 리스닝 수준, 복제용 WAL 수준, 복제 슬롯을 설정합니다:

다음 항목을 추가합니다:

listen_addresses = '*'
max_replication_slots = 10
wal_level = logical

*ClickHouse를 사용하려면 최소 logical WAL 수준과 최소 2개의 replication slot이 필요합니다

관리자 계정을 사용해 ClickHouse에서 접속할 사용자를 생성합니다:

CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';

*시연을 위해 전체 슈퍼유저 권한이 부여되었습니다.

새 데이터베이스를 생성합니다:

CREATE DATABASE db1;

psql에서 새 데이터베이스에 접속하세요:

\connect db1

새 테이블을 만드세요:

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

초기 행 추가:

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

복제를 위해 새 사용자가 새 데이터베이스에 연결할 수 있도록 PostgreSQL을 구성합니다. 아래는 pg_hba.conf 파일에 추가할 최소 항목입니다:

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

*설명 편의를 위해 여기서는 평문 비밀번호 인증 메서드를 사용합니다. PostgreSQL 문서에 따라 address 항목을 서브넷 또는 서버 주소로 업데이트하십시오

다음과 같이 pg_hba.conf 구성을 다시 로드합니다(버전에 맞게 조정하십시오):

/usr/pgsql-12/bin/pg_ctl reload

새 clickhouse_user로 로그인이 되는지 확인합니다:

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

ClickHouse에서

ClickHouse CLI에 로그인하세요

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

데이터베이스 엔진용 PostgreSQL 실험 기능을 활성화합니다:

SET allow_experimental_database_materialized_postgresql=1

복제할 새 데이터베이스를 생성하고 초기 테이블을 정의합니다:

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

최소 옵션:

parameter	Description	example
host:port	호스트명 또는 IP와 포트	postgres-host.domain.com:5432
database	PostgreSQL 데이터베이스 이름	db1
user	Postgres에 연결할 username	clickhouse_user
password	Postgres에 연결할 password	ClickHouse_123
settings	엔진에 대한 추가 설정	materialized_postgresql_tables_list = 'table1'

초기 테이블에 데이터가 있는지 확인합니다:

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

기본 복제 확인

PostgreSQL에서 새 행 추가:

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

ClickHouse에서 새 행이 표시되는지 확인합니다:

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

요약

이 통합 가이드는 테이블이 있는 데이터베이스를 복제하는 간단한 예시에 초점을 맞추고 있지만, 전체 데이터베이스를 복제하거나 기존 복제에 새 테이블과 스키마를 추가하는 등 더 고급 옵션도 있습니다. 이 복제에서는 DDL 명령이 지원되지 않지만, 구조 변경이 발생하면 이를 감지해 테이블을 다시 로드하도록 엔진을 설정할 수 있습니다.