[Clickhouse] Engine: PostgreSQL

clickhouse는 기본적으로 1차 저장소라기보다는, 기존 데이터를 부어서 빠르게 분석하기 위한 분석용 DB에 가깝다. 그래서 다른 DB와의 소스 통합을 꽤 잘 지원하는 편이다. PostgreSQL 소스를 엔진의 형태로 제공한다.

그냥 이런 식으로 만들면, clickhouse가 알아서 원본 DB에서 가져와서 쿼리를 쏘아준다.

CREATE TABLE default.postgresql_table
(
    `float_nullable` Nullable(Float32),
    `str` String,
    `int_id` Int32
)
ENGINE = PostgreSQL('localhost', 'db명', '원본테이블', 'username', 'password');

한계

이건 실제로 clickhouse에 데이터를 부어주는게 아니라, 대신 쏴주는 것에 불과하다.

그냥 실시간으로 PostgreSQL에 쿼리를 쏴서 clickhouse 내에서 연산을 하는 것이라서, PostgreSQL의 원본 데이터 크기가 크다면 따라서 느려진다. 내부적으로는 COPY(SELECT ...)의 쿼리를 날려서 가져온다.

PostgreSQL을 clickhouse로 가져와서 고성능 쿼리를 날리고 싶다면, 직접 CDC로 가져오거나 실험적 대안인 MaterializedPostgreSQL를 써야 한다.
https://blog.naver.com/sssang97/223918168727

기본 사용법

한번 써보자.
이런 단순한 key-value형 테이블이 있다고 가정해보겠다.

CREATE TABLE public.global_state (
	id bigserial NOT NULL,
	created_at timestamptz NULL,
	updated_at timestamptz NULL,
	deleted_at timestamptz NULL,
	"key" text NULL,
	value text NULL,
	CONSTRAINT global_state_pkey PRIMARY KEY (id)
);

정의 자체는 간단하다.

이걸 다시 clickhouse 형태에 맞춰서 재정의해주고

CREATE TABLE acloset_qa.global_state
(
    id bigserial NOT NULL,
	created_at timestamptz NULL,
	updated_at timestamptz NULL,
	deleted_at timestamptz NULL,
	"key" text NULL,
	value text NULL,
) 
ENGINE = PostgreSQL('엔드포인트', 'db명', '원본테이블', 'username', 'password');

엔진에다가 DB 접속정보만 넣어주면 된다.
그러면 clickhouse가 저 정보로 접속해서 알아서 가져오는 것이다.

그러고 들어가보면

데이터는 다 잘 들어가있고

데이터를 새로 넣으면

맞춰서 반영되고

수정해도

알아서 반영해줄 것이다.

그대로 잘 쓰면 된다.

named collection 기능

근데 위에서 사용한 생성 방법은 테이블을 만들때마다 접속정보를 계속 반복적으로, 중복되게 입력해야 한다는 단점이 있다.
named collection을 쓰면 접속정보를 미리 정의해두고 재사용할 수 있다.

/etc/clickhouse-server/config.d/named_collections.xml 같은 파일을 만들고

<clickhouse>

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

<clickhouse>

이런 식으로 입력해준 뒤에, clickhouse 서버를 재시작하면 된다.
mypg가 이후에 사용할 수 있는 접속정보의 이름이다.

그러면 그냥 바로 postgresql 함수로 호출해서 사용할 때도

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

이런 식으로 쓸 수 있고

테이블을 생성할 때도

CREATE TABLE mypgtable (
...
) 
ENGINE = PostgreSQL(mypg, table = 'test', schema = 'public');

이렇게 간단하게 처리할 수 있다.

데이터베이스 전체 미러링

테이블을 하나하나 정의해서 옮기기 귀찮다면, 데이터베이스 전체를 미러링하는 방법도 있다.
그냥 데이터베이스 만들때 엔진 옵션을 넣어주면 된다.

CREATE database DB명
ENGINE = PostgreSQL('엔드포인트', 'DBNAME', 'user', 'password');

그럼 해당 데이터베이스의 전체 테이블을 자동으로 연결해준다.

참조
https://clickhouse.com/docs/integrations/postgresql
https://clickhouse.com/docs/operations/named-collections#example-of-using-named-collections-with-the-postgresql-function
https://clickhouse.com/docs/engines/table-engines/integrations/postgresql
https://clickhouse.com/docs/engines/database-engines/postgresql