Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

PostgreSQL 테이블 엔진

View as MarkdownEdit this pageReport an issueSet up docs MCP

PostgreSQL 엔진을 사용하면 원격 PostgreSQL 서버에 저장된 데이터에 대해 SELECT 및 INSERT 쿼리를 실행할 수 있습니다.

테이블 생성

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

CREATE TABLE 쿼리에 대한 자세한 설명을 참조하십시오.

테이블 구조는 원본 PostgreSQL 테이블 구조와 다를 수 있습니다.

  • 컬럼 이름은 원본 PostgreSQL 테이블의 컬럼 이름과 같아야 하지만, 그중 일부만 사용하거나 순서를 임의로 지정할 수 있습니다.
  • 컬럼 타입은 원본 PostgreSQL 테이블의 타입과 달라도 됩니다. ClickHouse는 값을 ClickHouse 데이터 타입으로 cast하려고 시도합니다.
  • external_table_functions_use_nulls 설정은 널 허용 컬럼을 처리하는 방식을 정의합니다. 기본값은 1입니다. 값이 0이면 테이블 함수는 널 허용 컬럼을 생성하지 않고 null 대신 기본값을 삽입합니다. 이는 배열 내부의 NULL 값에도 적용됩니다.

엔진 매개변수

  • host:port — PostgreSQL 서버 주소입니다.
  • database — 원격 데이터베이스 이름입니다.
  • table — 원격 테이블 이름 또는 PostgreSQL에 그대로 전달되는 쿼리입니다(테이블 이름 대신 쿼리 전달 참조).
  • user — PostgreSQL 사용자입니다.
  • password — 사용자 비밀번호입니다.
  • schema — 기본값이 아닌 테이블 스키마입니다. 선택 사항입니다.
  • on_conflict — 충돌 해결 전략입니다. 예시: ON CONFLICT DO NOTHING. 선택 사항입니다. 참고: 이 옵션을 추가하면 삽입 성능이 저하됩니다.

이름이 지정된 컬렉션은 운영 환경(production)에서 사용하는 것을 권장합니다(버전 21.11부터 사용 가능). 다음은 예시입니다.

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

일부 매개변수는 키-값 인수로 재정의할 수 있습니다:

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

TLS/SSL

TLS/SSL 매개변수는 libpq로 전달되며, 명명된 컬렉션 키 또는 후행 키-값 인수로 설정할 수 있습니다. sslmode(disable, allow, prefer, require, verify-ca 또는 verify-full)와 인증서 및 키는 다음 두 가지 형식 중 하나로 지정할 수 있습니다. 설정하지 않으면 libpq 기본값(sslmode=prefer)이 적용됩니다.

  • sslrootcert(CA 인증서 또는 특수 값 system), sslcert(클라이언트 인증서), sslkey(클라이언트 private key)는 서버 로컬 파일의 경로입니다. 서버 구성 파일에 정의된 명명된 컬렉션에서만 지정할 수 있으며, 쿼리에서 재정의할 수 없습니다. 서버가 자체 권한으로 파일을 열기 때문입니다.
  • sslrootcert_pem, sslcert_pem, sslkey_pem은 경로 대신 해당 파일의 리터럴 내용을 받습니다. 쿼리, SQL로 생성한 명명된 컬렉션 또는 명명된 컬렉션 재정의 등 어디에서나 지정할 수 있으며, 비밀번호와 마찬가지로 logs 및 SHOW 쿼리에서 마스킹됩니다.

예를 들어, 암호화된 연결을 요구하고 server certificate를 확인하려면 다음과 같이 합니다.

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

설정 파일 없이 인증서 내용을 쿼리에 전달하는 방법은 다음과 같습니다:

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

설정

PostgreSQL 테이블 엔진(및 postgresql 테이블 함수)에서 사용하는 연결 풀은 SETTINGS 절을 사용해 테이블별로 구성할 수 있습니다. 설정을 지정하지 않으면 해당 쿼리 수준 postgresql_* 설정 값이 기본값으로 적용됩니다.

postgresql_connection_pool_size

연결 풀 크기입니다(모든 연결이 사용 중이면 일부 연결이 해제될 때까지 쿼리가 대기합니다). 0이 아닌 값이어야 합니다.

기본값: 16.

postgresql_connection_pool_wait_timeout

풀이 비어 있을 때 연결 풀의 push/pop timeout을 밀리초 단위로 지정합니다. 0은 풀이 비어 있으면 계속 대기함을 의미합니다.

기본값: 5000.

postgresql_connection_pool_retries

연결 풀의 push/pop 작업 재시도 횟수입니다.

기본값: 2.

postgresql_connection_pool_auto_close_connection

풀에 반환하기 전에 연결을 종료합니다.

기본값: false.

postgresql_connection_attempt_timeout

PostgreSQL 엔드포인트에 한 번 연결을 시도할 때 적용되는 연결 타임아웃 시간(초)입니다. 이 값은 연결 URL의 connect_timeout 매개변수로 전달됩니다.

기본값: 2.

예시:

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;

구현 세부 사항

PostgreSQL 측의 SELECT 쿼리는 각 SELECT 쿼리 후 커밋되는 읽기 전용 PostgreSQL 트랜잭션 내에서 COPY (SELECT ...) TO STDOUT 형태로 실행됩니다.

=, !=, >, >=, <, <=, IN과 같은 단순한 WHERE 절은 PostgreSQL 서버에서 실행됩니다.

모든 조인, 집계, 정렬, IN [ array ] 조건, 그리고 LIMIT 샘플링 제약은 PostgreSQL에 대한 쿼리가 완료된 후에만 ClickHouse에서 실행됩니다.

테이블 이름 대신 쿌리 전달하기

테이블 이름 대신 table 인수로 PostgreSQL에 있는 그대로 전달되는 SELECT 쿼리를 사용할 수 있습니다. 테이블의 구조는 쿼리 결과에서 자동으로 추론됩니다. 쿼리는 서브쿼리로 작성하거나 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');

조인, 집계 또는 기타 처리를 PostgreSQL로 푸시다운하는 데 유용합니다. 이러한 테이블은 읽기 전용이므로 여기에 INSERT할 수 없습니다. 동일한 구문이 postgresql 테이블 함수에서도 지원됩니다.

PostgreSQL 측의 INSERT 쿼리는 각 INSERT 문 후 자동 커밋되는 PostgreSQL 트랜잭션 내에서 COPY \"table_name\" (field1, field2, ... fieldN) FROM STDIN 형태로 실행됩니다.

PostgreSQL Array 타입은 ClickHouse 배열로 변환됩니다.

|로 나열해야 하는 여러 레플리카를 지원합니다. 예를 들면 다음과 같습니다:

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

PostgreSQL 딕셔너리 소스에서는 레플리카 우선순위를 지원합니다. 맵의 숫자가 클수록 우선순위는 낮아집니다. 가장 높은 우선순위는 0입니다.

아래 예시에서는 레플리카 example01-1의 우선순위가 가장 높습니다:

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

사용 예시

PostgreSQL의 테이블

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)

ClickHouse에서 테이블 생성 및 위에서 생성한 PostgreSQL 테이블에 연결하기

이 예시에서는 PostgreSQL 테이블 엔진을 사용해 ClickHouse 테이블을 PostgreSQL 테이블에 연결하고, PostgreSQL 데이터베이스에 대해 SELECT와 INSERT SQL 문을 모두 사용할 수 있습니다:

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

SELECT 쿼리를 사용해 PostgreSQL 테이블의 초기 데이터를 ClickHouse 테이블에 삽입하기

postgresql 테이블 함수는 PostgreSQL의 데이터를 ClickHouse로 복사합니다. 이는 PostgreSQL 대신 ClickHouse에서 데이터를 쿼리하거나 분석하여 쿼리 성능을 높일 때 자주 사용되며, PostgreSQL에서 ClickHouse로 데이터를 마이그레이션하는 데에도 사용할 수 있습니다. PostgreSQL의 데이터를 ClickHouse로 복사할 것이므로, ClickHouse에서는 MergeTree 테이블 엔진을 사용하는 테이블을 만들고 이름을 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');

PostgreSQL 테이블에서 ClickHouse 테이블로 증분 데이터 삽입하기

초기 삽입 후 PostgreSQL 테이블과 ClickHouse 테이블 간 동기화를 계속 수행하려면, ClickHouse에서 WHERE 절을 사용해 타임스탬프 또는 고유한 시퀀스 ID를 기준으로 PostgreSQL에 새로 추가된 데이터만 삽입할 수 있습니다.

이렇게 하려면 다음과 같이 이전에 삽입한 최대 ID 또는 타임스탬프를 추적해야 합니다:

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

그런 다음 PostgreSQL 테이블에서 최대값을 초과하는 값을 삽입합니다

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

생성된 ClickHouse 테이블에서 데이터 조회하기

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

관련 항목

관련 콘텐츠

Navigation