pg_clickhouse リファレンス ドキュメント

説明

pg_clickhouse は、foreign data wrapper を含め、ClickHouse データベースに対するリモートでのクエリ実行を可能にする PostgreSQL 拡張機能です。PostgreSQL 13 以降および ClickHouse 23 以降をサポートしています。

はじめに

pg_clickhouse を試す最も簡単な方法は、pg_clickhouse 拡張機能を組み込んだ標準 PostgreSQL の Docker image を使うことです。

docker run --name pg_clickhouse -e POSTGRES_PASSWORD=my_pass \
       -d ghcr.io/clickhouse/pg_clickhouse:18
docker exec -it pg_clickhouse psql -U postgres

ClickHouse テーブルのインポートやクエリのプッシュダウンを始めるには、チュートリアル を参照してください。

使用方法

CREATE EXTENSION pg_clickhouse;
CREATE SERVER taxi_srv FOREIGN DATA WRAPPER clickhouse_fdw
       OPTIONS(driver 'binary', host 'localhost', dbname 'taxi');
CREATE USER MAPPING FOR CURRENT_USER SERVER taxi_srv
       OPTIONS (user 'default');
CREATE SCHEMA taxi;
IMPORT FOREIGN SCHEMA taxi FROM SERVER taxi_srv INTO taxi;

バージョニングポリシー

pg_clickhouse は公開リリースに対して Semantic Versioning に従います。

• メジャーバージョンは API の変更時にインクリメントされます
• マイナーバージョンは後方互換性のある SQL の変更時にインクリメントされます
• パッチバージョンはバイナリのみの変更時にインクリメントされます

インストール後は、PostgreSQL は 2 種類のバージョンを追跡します。

• ライブラリバージョン（PostgreSQL 18 以上では PG_MODULE_MAGIC によって定義）は完全なセマンティックバージョンを含み、pg_get_loaded_modules() 関数の出力で確認できます。
• 拡張機能バージョン（control ファイルで定義）はメジャーおよびマイナーバージョンのみを含み、pg_catalog.pg_extension テーブル、pg_available_extension_versions() 関数の出力、そして \dx pg_clickhouse で確認できます。

実際には、パッチバージョンのみがインクリメントされるリリース、例えば v0.1.0 から v0.1.1 への変更は、v0.1 をロードしているすべてのデータベースに適用され、アップグレードのために ALTER EXTENSION を実行する必要はありません。

一方で、マイナーまたはメジャーバージョンがインクリメントされるリリースには SQL アップグレードスクリプトが伴い、その拡張機能を含む既存のすべてのデータベースは、アップグレードの恩恵を受けるために ALTER EXTENSION pg_clickhouse UPDATE を実行する必要があります。

SQL リファレンス

以下の SQL 文は pg_clickhouse を利用します。

CREATE EXTENSION

CREATE EXTENSION ステートメントを使用して、pg_clickhouse 拡張機能をデータベースに追加します。

CREATE EXTENSION pg_clickhouse;

特定のスキーマにインストールする場合は（推奨）、WITH SCHEMA を使用します。

CREATE SCHEMA ch;
CREATE EXTENSION pg_clickhouse WITH SCHEMA ch;

ALTER EXTENSION

ALTER EXTENSION を使用して pg_clickhouse を変更します。例：

• 新しいリリースの pg_clickhouse をインストールした後は、UPDATE 句を使用します：

  ALTER EXTENSION pg_clickhouse UPDATE;
  

• SET SCHEMA を使用して、拡張機能を新しいスキーマに移動します：

  CREATE SCHEMA ch;
  ALTER EXTENSION pg_clickhouse SET SCHEMA ch;
  

DROP EXTENSION

DROP EXTENSION を使用して、データベースから pg_clickhouse 拡張機能を削除します。

DROP EXTENSION pg_clickhouse;

このコマンドは、pg_clickhouse に依存するオブジェクトが存在する場合、失敗します。 それらもまとめて削除するには、CASCADE 句を使用してください。

DROP EXTENSION pg_clickhouse CASCADE;

CREATE SERVER

CREATE SERVER を使用して、ClickHouse サーバーへの接続を定義する foreign server（外部サーバー）を作成します。例:

CREATE SERVER taxi_srv FOREIGN DATA WRAPPER clickhouse_fdw
       OPTIONS(driver 'binary', host 'localhost', dbname 'taxi');

サポートされているオプションは次のとおりです:

• driver: 使用する ClickHouse 接続ドライバ。"binary" または "http" のいずれか。必須。
• dbname: 接続時に使用する ClickHouse データベース。デフォルトでは "default"。
• host: ClickHouse サーバーのホスト名。デフォルトでは "localhost"。
• port: ClickHouse サーバーに接続するポート。デフォルトは次のとおり:
  • driver が "binary" で、host が ClickHouse Cloud のホストの場合は 9440
  • driver が "binary" で、host が ClickHouse Cloud のホストではない場合は 9004
  • driver が "http" で、host が ClickHouse Cloud のホストの場合は 8443
  • driver が "http" で、host が ClickHouse Cloud のホストではない場合は 8123

ALTER SERVER

ALTER SERVER を使用して、外部サーバーの定義を変更します。例:

ALTER SERVER taxi_srv OPTIONS (SET driver 'http');

オプションは CREATE SERVER の場合と同じです。

DROP SERVER

DROP SERVER を使用して、外部サーバー定義を削除します。

DROP SERVER taxi_srv;

サーバーに他のオブジェクトが依存している場合、このコマンドは失敗します。それらの依存関係も削除するには、CASCADE 句を使用してください。

DROP SERVER taxi_srv CASCADE;

CREATE USER MAPPING

CREATE USER MAPPING を使用して、PostgreSQL ユーザーを ClickHouse ユーザーにマッピングします。 たとえば、taxi_srv フォーリンサーバーに接続する際に、現在の PostgreSQL ユーザーをリモートの ClickHouse ユーザーにマッピングするには、次のようにします。

CREATE USER MAPPING FOR CURRENT_USER SERVER taxi_srv
       OPTIONS (user 'demo');

サポートされているオプションは次のとおりです。

• user: ClickHouse のユーザー名。既定値は「default」です。
• password: ClickHouse のユーザーのパスワード。

ALTER USER MAPPING

ユーザー マッピングの定義を変更するには、ALTER USER MAPPING を使用します。

ALTER USER MAPPING FOR CURRENT_USER SERVER taxi_srv
       OPTIONS (SET user 'default');

オプションは CREATE USER MAPPING の場合と同じです。

DROP USER MAPPING

ユーザーマッピングを削除するには、DROP USER MAPPING を使用します。

DROP USER MAPPING FOR CURRENT_USER SERVER taxi_srv;

IMPORT FOREIGN SCHEMA

IMPORT FOREIGN SCHEMA を使用して、ClickHouse データベース内で定義されているすべてのテーブルを、外部テーブルとして PostgreSQL のスキーマ内にインポートします。

CREATE SCHEMA taxi;
IMPORT FOREIGN SCHEMA demo FROM SERVER taxi_srv INTO taxi;

LIMIT TO を使用して、インポートするテーブルを特定のものに絞り込みます。

IMPORT FOREIGN SCHEMA demo LIMIT TO (trips) FROM SERVER taxi_srv INTO taxi;

EXCEPT を使用してテーブルを除外します:

IMPORT FOREIGN SCHEMA demo EXCEPT (users) FROM SERVER taxi_srv INTO taxi;

pg_clickhouse は、指定された ClickHouse データベース（上記の例では "demo"）内のすべてのテーブルの一覧を取得し、各テーブルのカラム定義を取得したうえで、外部テーブルを作成するために CREATE FOREIGN TABLE コマンドを実行します。カラムは、サポートされているデータ型 と、検出可能な場合には CREATE FOREIGN TABLE でサポートされているオプションを使用して定義されます。

CREATE FOREIGN TABLE

IMPORT FOREIGN SCHEMA を使用して、ClickHouse データベース内のデータを参照する外部テーブルを作成します。

CREATE FOREIGN TABLE uact (
    user_id    bigint NOT NULL,
    page_views int,
    duration   smallint,
    sign       smallint
) SERVER taxi_srv OPTIONS(
    table_name 'uact'
    engine 'CollapsingMergeTree'
);

サポートされているテーブルオプションは次のとおりです。

• database: リモートデータベース名。指定がない場合は、外部サーバーに対して定義されたデータベースが使用されます。
• table_name: リモートテーブル名。指定がない場合は、外部テーブルに指定された名前が使用されます。
• engine: ClickHouse テーブルで使用される[テーブルエンジン]。CollapsingMergeTree() および AggregatingMergeTree() の場合、pg_clickhouse はテーブル上で実行される関数式にパラメータを自動的に適用します。

各カラムのリモート ClickHouse 側のデータ型に適した data type を使用します。AggregateFunction Type および SimpleAggregateFunction Type カラムについては、データ型を関数に渡される ClickHouse の型にマッピングし、適切なカラムオプションを使用して集約関数名を指定します。

• AggregateFunction: AggregateFunction Type カラムに適用される集約関数の名前
• SimpleAggregateFunction: SimpleAggregateFunction Type カラムに適用される集約関数の名前

例:

(aggregatefunction 'sum')

CREATE FOREIGN TABLE test (
    column1 bigint  OPTIONS(AggregateFunction 'uniq'),
    column2 integer OPTIONS(AggregateFunction 'anyIf'),
    column3 bigint  OPTIONS(AggregateFunction 'quantiles(0.5, 0.9)')
) SERVER clickhouse_srv;

AggregateFunction 関数を持つカラムに対しては、pg_clickhouse がそのカラムを評価する集約関数に自動的に Merge を付与します。

ALTER FOREIGN TABLE

[ALTER FOREIGN TABLE] を使用すると、外部テーブルの定義を変更できます。

ALTER TABLE table ALTER COLUMN b OPTIONS (SET AggregateFunction 'count');

サポートされるテーブルおよびカラムのオプションは、[CREATE FOREIGN TABLE] の場合と同様です。

DROP FOREIGN TABLE

[DROP FOREIGN TABLE] を使用して、外部テーブルを削除します。

DROP FOREIGN TABLE uact;

このコマンドは、外部テーブルに依存するオブジェクトが存在する場合は失敗します。 それらも合わせて削除するには、CASCADE 句を使用します。

DROP FOREIGN TABLE uact CASCADE;

関数と演算子のリファレンス

データ型

pg_clickhouse は、次の ClickHouse データ型を PostgreSQL データ型にマッピングします。

ClickHouse	PostgreSQL	備考
Bool	boolean
Date	date
DateTime	timestamp
Decimal	numeric
Float32	real
Float64	double precision
IPv4	inet
IPv6	inet
Int16	smallint
Int32	integer
Int64	bigint
Int8	smallint
JSON	jsonb	HTTP エンジンのみ
String	text
UInt16	integer
UInt32	bigint
UInt64	bigint	値が BIGINT の最大値を超えるとエラー
UInt8	smallint
UUID	uuid

関数

これらの関数は、ClickHouse データベースに対してクエリを実行するためのインターフェースを提供します。

clickhouse_raw_query

SELECT clickhouse_raw_query(
    'CREATE TABLE t1 (x String) ENGINE = Memory',
    'host=localhost port=8123'
);

ClickHouse サービスに HTTP インターフェイス経由で接続し、単一のクエリを実行してから切断します。省略可能な 2 番目の引数には接続文字列を指定でき、指定しない場合のデフォルトは host=localhost port=8123 です。サポートされている接続パラメータは次のとおりです。

• host: 接続先のホスト。必須。
• port: 接続先の HTTP ポート。host が ClickHouse Cloud のホストでない場合のデフォルトは 8123、ClickHouse Cloud のホストである場合のデフォルトは 8443
• dbname: 接続先のデータベース名。
• username: 接続時に使用するユーザー名。デフォルトは default
• password: 認証に使用するパスワード。デフォルトはパスワードなし

レコードを返さないクエリに便利ですが、値を返すクエリの場合は、単一のテキスト値として返されます。

SELECT clickhouse_raw_query(
    'SELECT schema_name, schema_owner from information_schema.schemata',
    'host=localhost port=8123'
);

      clickhouse_raw_query       
---------------------------------
 INFORMATION_SCHEMA      default+
 default default                +
 git     default                +
 information_schema      default+
 system  default                +
 
(1 row)

プッシュダウン関数

条件式（HAVING および WHERE 句）で使用される PostgreSQL のすべての組み込み関数は、ClickHouse 外部テーブルに対してクエリを実行する際、同じ名前とシグネチャのまま自動的に ClickHouse 側へプッシュダウンされます。ただし、一部の関数は名前やシグネチャが異なるため、同等の関数にマッピングする必要があります。pg_clickhouse は次の関数をマッピングします:

• date_part:
  • date_part('day'): toDayOfMonth
  • date_part('doy'): toDayOfYear
  • date_part('dow'): toDayOfWeek
  • date_part('year'): toYear
  • date_part('month'): toMonth
  • date_part('hour'): toHour
  • date_part('minute'): toMinute
  • date_part('second'): toSecond
  • date_part('quarter'): toQuarter
  • date_part('isoyear'): toISOYear
  • date_part('week'): toISOYear
  • date_part('epoch'): toISOYear
• date_trunc:
  • date_trunc('week'): toMonday
  • date_trunc('second'): toStartOfSecond
  • date_trunc('minute'): toStartOfMinute
  • date_trunc('hour'): toStartOfHour
  • date_trunc('day'): toStartOfDay
  • date_trunc('month'): toStartOfMonth
  • date_trunc('quarter'): toStartOfQuarter
  • date_trunc('year'): toStartOfYear
• array_position: indexOf
• btrim: trimBoth
• strpos: position
• regexp_like: match

カスタム関数

pg_clickhouse によって作成されるこれらのカスタム関数は、PostgreSQL に同等の機能が存在しない一部の ClickHouse 関数に対して、外部クエリのプッシュダウンを可能にします。これらの関数のいずれかがプッシュダウンできない場合は、例外を発生させます。

• dictGet

キャストのプッシュダウン

pg_clickhouse は、互換性のあるデータ型に対して CAST(x AS bigint) のようなキャストをプッシュダウンします。互換性のない型の場合はプッシュダウンが失敗します。この例で x が ClickHouse の UInt64 である場合、ClickHouse はその値のキャストを拒否します。

互換性のないデータ型へのキャストをプッシュダウンするために、pg_clickhouse は次の関数を提供します。これらの関数がプッシュダウンされなかった場合、PostgreSQL で例外をスローします。

• toUInt8
• toUInt16
• toUInt32
• toUInt64
• toUInt128

プッシュダウンされる集約関数

これらの PostgreSQL 集約関数は ClickHouse へプッシュダウンされます。

• count

カスタム集約関数

pg_clickhouse によって定義されるこれらのカスタム集約関数は、PostgreSQL に同等の機能が存在しない一部の ClickHouse 集約関数に対して、外部クエリのプッシュダウンを可能にします。これらの関数のいずれかをプッシュダウンできない場合は、例外を発生させます。

• argMax
• argMin
• uniq
• uniqCombined
• uniqCombined64
• uniqExact
• uniqHLL12
• uniqTheta
• quantile
• quantileExact

プッシュダウンされる順序付き集合集約

これらの ordered-set aggregate functions は、direct argument をパラメータとして渡し、ORDER BY の式を引数として渡すことで、ClickHouse の Parametric aggregate functions に対応します。例えば、次の PostgreSQL クエリでは：

SELECT percentile_cont(0.25) WITHIN GROUP (ORDER BY a) FROM t1;

これは次の ClickHouse クエリに対応しています：

SELECT quantile(0.25)(a) FROM t1;

デフォルト以外の ORDER BY 句の接尾辞である DESC および NULLS FIRST はサポートされておらず、指定するとエラーになります。

• percentile_cont(double): quantile
• quantile(double): quantile
• quantileExact(double): quantileExact

セッション設定

pg_clickhouse.session_settings ランタイムパラメーターを設定して、 以降のクエリに適用される ClickHouse settings を構成します。例:

SET pg_clickhouse.session_settings = 'join_use_nulls 1, final 1';

デフォルトは join_use_nulls 1 です。空文字列に設定すると、 ClickHouse サーバー側の設定が使用されます。

SET pg_clickhouse.session_settings = '';

この構文は、カンマ区切りのキーと値のペアのリストで、1つ以上のスペースで区切られます。キーは ClickHouse settings に対応している必要があります。値中の空白、カンマ、およびバックスラッシュは、バックスラッシュでエスケープします:

SET pg_clickhouse.session_settings = 'join_algorithm grace_hash\,hash';

スペースやカンマをエスケープせずに済むように値をシングルクォートで囲むか、二重引用符を重ねて書く必要がないように dollar quoting の利用を検討してください：

SET pg_clickhouse.session_settings = $$join_algorithm 'grace_hash,hash'$$;

可読性を重視し、設定項目が多い場合は、たとえば次のように複数行に分けて記述してください。

SET pg_clickhouse.session_settings TO $$
    connect_timeout 2,
    count_distinct_implementation uniq,
    final 1,
    group_by_use_nulls 1,
    join_algorithm 'prefer_partial_merge',
    join_use_nulls 1,
    log_queries_min_type QUERY_FINISH,
    max_block_size 32768,
    max_execution_time 45,
    max_result_rows 1024,
    metrics_perf_events_list 'this,that',
    network_compression_method ZSTD,
    poll_interval 5,
    totals_mode after_having_auto
$$;

pg_clickhouse は設定を検証せず、すべてのクエリについて設定をそのまま ClickHouse に渡します。そのため、各 ClickHouse バージョンのすべての設定をサポートします。

なお、pg_clickhouse.session_settings を設定する前に pg_clickhouse をロードしておく必要があります。library preloading を使用するか、拡張機能内のいずれかのオブジェクトを利用してロードされるようにしてください。

著者

• David E. Wheeler
• Ildus Kurbangaliev
• Ibrar Ahmed

著作権

• Copyright (c) 2025, ClickHouse

• Portions Copyright (c) 2023-2025, Ildus Kurbangaliev

• Portions Copyright (c) 2019-2023, Adjust GmbH

• Portions Copyright (c) 2012-2019, PostgreSQL Global Development Group