postgresql

postgresql

원격 PostgreSQL 서버에 저장된 데이터에 SELECT와 INSERT 쿼리를 수행할 수 있게 해주는 테이블 함수예요.

출처: 문서

본문

원격 PostgreSQL 서버에 저장된 데이터에 SELECT와 INSERT 쿼리를 수행할 수 있게 해줘요.

문법 (Syntax)

postgresql({host:port, database, table, user, password[, schema, [, on_conflict]] | named_collection[, option=value [,..]]} [, SETTINGS name=value, ...])

인자 (Arguments)

인자	설명
host:port	PostgreSQL 서버 주소예요.
database	원격 데이터베이스 이름이에요.
table	원격 테이블 이름 또는 PostgreSQL에 그대로 전달되는 쿼리예요.
user	PostgreSQL 사용자예요.
password	사용자 비밀번호예요.
schema	기본이 아닌 테이블 스키마예요. 선택 사항이에요.
on_conflict	충돌 해결 전략이에요. 예: ON CONFLICT DO NOTHING. 선택 사항이에요.

인자는 네임드 컬렉션으로도 전달할 수 있어요. 이 경우 host와 port를 따로 지정해야 해요. 프로덕션 환경에서는 이 방식을 권장해요.

TLS/SSL 파라미터는 libpq로 전달되며, 네임드 컬렉션 키 또는 뒤에 붙는 키-값 인자로 제공될 수 있어요: sslmode(disable, allow, prefer, require, verify-ca 또는 verify-full; 지정하지 않으면 libpq 기본값인 prefer가 적용돼요)와 두 형태 중 하나의 인증서와 키. sslrootcert(CA 인증서 또는 특수 값 system), sslcert(클라이언트 인증서), sslkey(클라이언트 개인 키)는 서버-로컬 파일 경로이며 서버 구성 파일에 정의된 네임드 컬렉션에서만 지정할 수 있어요. sslrootcert_pem, sslcert_pem, sslkey_pem은 대신 해당 파일의 리터럴 내용을 받아요 — 예를 들어 postgresql('host:port', 'database', 'table', 'user', 'password', sslmode = 'verify-full', sslrootcert_pem = '...')처럼요 — 그리고 비밀번호처럼 로그와 SHOW 쿼리에서 마스킹돼요.

반환값 (Returned value)

원래 PostgreSQL 테이블과 같은 컬럼을 가진 테이블 객체예요.

INSERT 쿼리에서 테이블 함수 postgresql(...)를 컬럼 이름 목록이 있는 테이블 이름과 구분하려면 FUNCTION 또는 TABLE FUNCTION 키워드를 사용해야 해요. 아래 예시를 참고하세요.

설정 (Settings)

postgresql 테이블 함수(및 PostgreSQL 테이블 엔진)가 사용하는 연결 풀은 뒤에 붙는 SETTINGS 절로 구성할 수 있어요. 설정을 지정하지 않으면 해당 쿼리 수준 postgresql_* 설정의 값이 기본값으로 적용돼요. postgresql_connection_pool_* 및 postgresql_connection_attempt_timeout 설정의 전체 목록과 기본값은 테이블 엔진의 Settings 섹션을 참고하세요.

예시:

SELECT * FROM postgresql('localhost:5432', 'test', 'test', 'postgresql_user', 'password', SETTINGS postgresql_connection_pool_size = 32);

구현 세부사항 (Implementation Details)

PostgreSQL 쪽의 SELECT 쿼리는 각 SELECT 쿼리 후 커밋하는 읽기 전용 PostgreSQL 트랜잭션 안에서 COPY (SELECT ...) TO STDOUT로 실행돼요.

=, !=, >, >=, <, <=, IN 같은 간단한 WHERE 절은 PostgreSQL 서버에서 실행돼요.

모든 조인, 집계, 정렬, IN [ array ] 조건과 LIMIT 샘플링 제약은 PostgreSQL 쿼리가 끝난 뒤에만 ClickHouse에서 실행돼요.

테이블 이름 대신 쿼리 전달 (Passing a query instead of a table name)

테이블 이름 대신, 세 번째 인자는 PostgreSQL에 그대로 전달되는 SELECT 쿼리일 수 있어요. 결과 테이블의 구조는 쿼리 결과에서 유추돼요. 쿼리는 서브쿼리로 쓰거나 query 함수로 감쌀 수 있어요:

SELECT * FROM postgresql('localhost:5432', 'test', (SELECT a, b FROM t1 JOIN t2 USING (id) WHERE a > 0), 'user', 'password');
SELECT * FROM postgresql('localhost:5432', 'test', query('SELECT a, b FROM t1 JOIN t2 USING (id) WHERE a > 0'), 'user', 'password');

이것은 조인, 집계 또는 기타 처리를 PostgreSQL로 푸시다운하는 데 유용해요. 이런 테이블은 읽기 전용이에요: INSERT할 수 없어요. 동일한 문법은 PostgreSQL 테이블 엔진에서도 지원돼요.

서브쿼리 형태 (SELECT ...)는 ClickHouse가 파싱하고 PostgreSQL 방언(PostgreSQL 식별자 따옴표 및 문자열 리터럴 이스케이프)으로 재직렬화한 뒤 서버에 보내요. 따라서 유효한 ClickHouse SQL이어야 해요. ClickHouse가 파싱하지 못하는 PostgreSQL 전용 문법을 전달하려면 텍스트가 PostgreSQL에 그대로 전송되는 query('...') 형태를 사용하세요.

주변 ClickHouse 쿼리의 어떤 외부 WHERE, LIMIT, 집계 등도 전달된 쿼리에는 푸시다운되지 않아요 — 전체 쿼리 결과를 가져온 뒤 ClickHouse에서 적용돼요. PostgreSQL에서 읽는 데이터를 제한하려면 전달된 쿼리 안에 필터를 넣으세요. external_table_strict_query = 1로 설정하면 테이블 함수 컬럼에 대한 외부 필터는 로컬로 적용되는 대신 예외로 거부돼요. 전달된 쿼리에 푸시다운할 수 없기 때문이에요. 이 검사는 최상위 WHERE 조건자와 최상위 AND의 각 연결부를 다뤄요. 이 테이블 컬럼에 대한 PREWHERE는 이 설정의 대상이 아니에요: 이 테이블 엔진은 PREWHERE를 지원하지 않아서, 설정과 무관하게 그런 쿼리는 ILLEGAL_PREWHERE로 거부돼요. 검사는 필터를 푸시다운할 수 있는 경우에만 실행돼요: 이 테이블이 쿼리의 유일한 테이블이거나, INNER JOIN의 양쪽, 또는 외부 조인의 보존측(LEFT JOIN의 왼쪽, RIGHT JOIN의 오른쪽)일 때요. LEFT/RIGHT JOIN의 비보존측과 FULL JOIN의 양쪽에서는 아무것도 푸시다운되지도, 검사되기도 하지 않아요. 따라서 이 테이블 컬럼에 대한 필터는 엄격 모드에서도 조인 후 로컬로 적용돼요. 검사가 실행되는 곳에서 주변 쿼리에 조인된 다른 테이블을 참조하는 조건자는 푸시다운되지 않고 검사에서 제외돼요. 조인된 쪽만 참조하든, 하나의 AND가 아닌 표현식(예: OR) 안에서 이 테이블과 섞든 관계없이요. 그런 조건자는 평소 ClickHouse 평가 지점(조인 후의 WHERE, 그 앞의 PREWHERE)을 유지하고 거부되지 않아요.

PostgreSQL 쪽의 INSERT 쿼리는 각 INSERT 문 후 자동 커밋하는 PostgreSQL 트랜잭션 안에서 COPY "table_name" (field1, field2, ... fieldN) FROM STDIN으로 실행돼요.

PostgreSQL Array 타입은 ClickHouse 배열로 변환돼요.

주의하세요: PostgreSQL에서 Integer[] 같은 배열 데이터 타입 컬럼은 행마다 다른 차원의 배열을 포함할 수 있지만, ClickHouse에서는 모든 행에서 같은 차원의 다차원 배열만 허용돼요.

|로 나열해야 하는 여러 복제본을 지원해요. 예를 들어:

SELECT name FROM postgresql(`postgres{1|2|3}:5432`, 'postgres_database', 'postgres_table', 'user', 'password');

또는

SELECT name FROM postgresql(`postgres1:5431|postgres2:5432`, 'postgres_database', 'postgres_table', 'user', 'password');

PostgreSQL 사전 소스에 대한 복제본 우선순위를 지원해요. 맵 안의 숫자가 클수록 우선순위가 낮아요. 가장 높은 우선순위는 0이에요.

예시 (Examples)

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에서 데이터 선택:

SELECT * FROM postgresql('localhost:5432', 'test', 'test', 'postgresql_user', 'password') WHERE str IN ('test');

또는 네임드 컬렉션 사용:

CREATE NAMED COLLECTION mypg AS
 host = 'localhost',
 port = 5432,
 database = 'test',
 user = 'postgresql_user',
 password = 'password';
SELECT * FROM postgresql(mypg, table='test') WHERE str IN ('test');

┌─int_id─┬─int_nullable─┬─float─┬─str──┬─float_nullable─┐
│ 1 │ ᴺᵁᴸᴸ │ 2 │ test │ ᴺᵁᴸᴸ │
└────────┴──────────────┴───────┴──────┴────────────────┘

삽입:

INSERT INTO TABLE FUNCTION postgresql('localhost:5432', 'test', 'test', 'postgrsql_user', 'password') (int_id, float) VALUES (2, 3);
SELECT * FROM postgresql('localhost:5432', 'test', 'test', 'postgresql_user', 'password');

┌─int_id─┬─int_nullable─┬─float─┬─str──┬─float_nullable─┐
│ 1 │ ᴺᵁᴸᴸ │ 2 │ test │ ᴺᵁᴸᴸ │
│ 2 │ ᴺᵁᴸᴸ │ 3 │ │ ᴺᵁᴸᴸ │
└────────┴──────────────┴───────┴──────┴────────────────┘

기본이 아닌 스키마 사용:

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

PeerDB로 Postgres 데이터 복제 또는 마이그레이션

테이블 함수 외에도 ClickHouse의 PeerDB를 사용해 Postgres에서 ClickHouse로의 연속 데이터 파이프라인을 구축할 수 있어요. PeerDB는 변경 데이터 캡처(CDC)를 사용해 Postgres에서 ClickHouse로 데이터를 복제하도록 특별히 설계된 도구예요.

더 알아보기 (Learn more)

• PostgreSQL 테이블 엔진
• 사전 소스로 PostgreSQL 사용
• PeerDB