pg_clickhouse リファレンス - ClickHouse Documentation

説明

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

はじめに

pg_clickhouse を試す最も簡単な方法は Docker image を使うことです。これには、 pg_clickhouse と re2 拡張機能を含む標準の PostgreSQL Docker イメージが含まれています:

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 の変更時にはマイナーバージョンが増加します
binary のみの変更時にはパッチバージョンが増加します

インストールされると、PostgreSQL は 2 種類のバージョンを管理します。

ライブラリバージョン (PostgreSQL 18 以降では PG_MODULE_MAGIC によって定義) には完全なセマンティックバージョンが含まれており、 pgch_version() 関数の出力または Postgres の pg_get_loaded_modules() 関数で確認できます。
拡張機能バージョン (control file で定義) にはメジャーバージョンとマイナーバージョンのみが含まれており、pg_catalog.pg_extension テーブル、 pg_available_extension_versions() 関数の出力、および \dx pg_clickhouse で確認できます。

実際には、これはたとえば v0.1.0 から v0.1.1 へのようにパッチバージョンが増加する release では、v0.1 を読み込んでいるすべての database がその恩恵を受けられ、アップグレードを反映するために ALTER EXTENSION を実行する必要がないことを意味します。一方、マイナーバージョンまたはメジャーバージョンが増加する release には SQL アップグレードスクリプトが付属し、拡張機能を含む既存のすべての database では、アップグレードを反映するために ALTER EXTENSION pg_clickhouse UPDATE を実行する必要があります。

DDL SQL リファレンス

以下の SQL DDL 文では、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

pg_clickhouse を変更するには、ALTER EXTENSION を使用します。例:

pg_clickhouse の新しい release をインストールした後は、UPDATE 句を使用します。
ALTER EXTENSION pg_clickhouse UPDATE;
拡張機能を新しいスキーマに移動するには、SET SCHEMA を使用します。
CREATE SCHEMA ch; ALTER EXTENSION pg_clickhouse SET SCHEMA ch;

DROP EXTENSION

データベースから pg_clickhouse を削除するには、DROP EXTENSION を使用します。

DROP EXTENSION pg_clickhouse;

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

DROP EXTENSION pg_clickhouse CASCADE;

CREATE SERVER

ClickHouseサーバーに接続する外部サーバーを作成するには、CREATE SERVER を使用します。例:

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

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

driver: 使用する ClickHouse 接続ドライバーです。“binary” または “http” のいずれかを指定します。必須です。
compression: バイナリドライバー用のネイティブプロトコル圧縮です。“none”、 “lz4”、“zstd” のいずれかです。デフォルトは “lz4” です。“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
min_tls_version: TLS を使用する接続でネゴシエートする最小 TLS プロトコルバージョンです。 TLSv1、TLSv1.1、TLSv1.2、TLSv1.3 のいずれかです。デフォルトは TLS ライブラリ自体の最小バージョンです。両方のドライバーに適用されます。
secure: 接続で使用する TLS を制御します。次のいずれかです。
- auto (デフォルト): host が ClickHouse Cloud ホストであるか、 port がセキュアポートの場合は TLS を使用し、それ以外では平文を使用します。
- on (または true/yes/1): 常に TLS を使用します。デフォルトの port は 8443 (“http”) または 9440 (“binary”) です。
- off (または false/no/0): TLS を使用しません。デフォルトの port は 8123 (“http”) または 9000 (“binary”) です.

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 でサポートされているオプションも使用されます。

インポートされた識別子の大文字・小文字の保持IMPORT FOREIGN SCHEMA は、インポートするテーブル名とカラム名に対して quote_identifier() を実行します。これにより、大文字を含む識別子または空白を含む識別子は二重引用符で囲まれます。そのため、そのようなテーブル名とカラム名は PostgreSQL クエリ内でも二重引用符で囲む必要があります。すべて小文字で空白文字を含まない名前は、引用符で囲む必要はありません。たとえば、次の ClickHouse テーブルがあるとします。

CREATE OR REPLACE TABLE test
(
    id UInt64,
    Name TEXT,
    updatedAt DateTime DEFAULT now()
)
ENGINE = MergeTree
ORDER BY id;

IMPORT FOREIGN SCHEMA は、次の外部テーブルを作成します。

CREATE TABLE test
(
    id          BIGINT      NOT NULL,
    "Name"      TEXT        NOT NULL,
    "updatedAt" TIMESTAMPTZ NOT NULL
);

したがって、クエリでは適切に引用する必要があります。たとえば、

SELECT id, "Name", "updatedAt" FROM test;

異なる名前のオブジェクトや、すべて小文字の (つまり大文字・小文字を区別しない) 名前でオブジェクトを作成するには、CREATE FOREIGN TABLE を使用します。

CREATE FOREIGN TABLE

ClickHouseデータベースのデータをクエリできる外部テーブルを作成するには、CREATE FOREIGN TABLE を使用します。

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

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

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

各カラムには、リモートの ClickHouse のデータ型に対応するものを使用してください。サポートされているカラムオプションは次のとおりです。

column_name: ClickHouse 側のカラム名です。クエリや INSERT のデパース時には、PostgreSQL の属性名よりもこちらが優先して使用されます。これは、引用符なしの小文字の PostgreSQL カラム名を大文字と小文字を区別する ClickHouse カラムにマッピングする場合に便利です。例:
CREATE FOREIGN TABLE hits ( watchid bigint OPTIONS(column_name 'WatchID'), javaenable smallint OPTIONS(column_name 'JavaEnable'), title text OPTIONS(column_name 'Title') ) SERVER taxi_srv OPTIONS(table_name 'hits');
AggregateFunction: [AggregateFunction 型] カラムに適用される集約関数の名前です。データ型を、その関数に渡される ClickHouse の型にマッピングし、適切なカラムオプションで集約関数名を指定すると、pg_clickhouse が自動的にカラムを評価する集約関数に Merge を付加します。
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;
SimpleAggregateFunction: [SimpleAggregateFunction 型] カラムに適用される集約関数の名前です。データ型を、その関数に渡される ClickHouse の型にマッピングし、適切なカラムオプションで集約関数名を指定してください。

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

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

DROP FOREIGN TABLE acts CASCADE;

DML SQL リファレンス

以下の SQL DML 式では、pg_clickhouse を使用することがあります。例は、以下の ClickHouse テーブルを前提としています:

CREATE TABLE logs (
    req_id    Int64 NOT NULL,
    start_at   DateTime64(6, 'UTC') NOT NULL,
    duration  Int32 NOT NULL,
    resource  Text  NOT NULL,
    method    Enum8('GET' = 1, 'HEAD', 'POST', 'PUT', 'DELETE', 'CONNECT', 'OPTIONS', 'TRACE', 'PATCH', 'QUERY') NOT NULL,
    node_id   Int64 NOT NULL,
    response  Int32 NOT NULL
) ENGINE = MergeTree
  ORDER BY start_at;

CREATE TABLE nodes (
    node_id Int64 NOT NULL,
    name    Text  NOT NULL,
    region  Text  NOT NULL,
    arch    Text  NOT NULL,
    os      Text  NOT NULL
) ENGINE = MergeTree
  PRIMARY KEY node_id;

EXPLAIN

EXPLAIN コマンドは想定どおりに動作しますが、VERBOSE オプションを指定すると ClickHouse の “Remote SQL” クエリが出力されます:

try=# EXPLAIN (VERBOSE)
       SELECT resource, avg(duration) AS average_duration
         FROM logs
        GROUP BY resource;
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Foreign Scan  (cost=1.00..5.10 rows=1000 width=64)
   Output: resource, (avg(duration))
   Relations: Aggregate on (logs)
   Remote SQL: SELECT resource, avg(duration) FROM "default".logs GROUP BY resource
(4 rows)

このクエリでは、“Foreign Scan” プランノードを介して、リモート SQL が ClickHouse にプッシュダウンされます。

SELECT

SELECTステートメントを使うと、他のテーブルと同様に pg_clickhouse テーブルに対してクエリを実行できます。

try=# SELECT start_at, duration, resource FROM logs WHERE req_id = 4117909262;
          start_at          | duration |    resource
----------------------------+----------+----------------
 2025-12-05 15:07:32.944188 |      175 | /widgets/totem
(1 row)

pg_clickhouse は、集約関数を含め、可能な限りクエリ実行を ClickHouse にプッシュダウンします。EXPLAIN を使用して、どこまでプッシュダウンされているかを確認してください。たとえば、上記のクエリでは、実行はすべて ClickHouse にプッシュダウンされます

try=# EXPLAIN (VERBOSE, COSTS OFF)
       SELECT start_at, duration, resource FROM logs WHERE req_id = 4117909262;
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Foreign Scan on public.logs
   Output: start_at, duration, resource
   Remote SQL: SELECT start_at, duration, resource FROM "default".logs WHERE ((req_id = 4117909262))
(3 rows)

pg_clickhouse は、同じリモートサーバー上のテーブルに対する JOIN もプッシュダウンします。

try=# EXPLAIN (ANALYZE, VERBOSE)
       SELECT name, count(*), round(avg(duration))
         FROM logs
         LEFT JOIN nodes on logs.node_id = nodes.node_id
        GROUP BY name;
                                                                                  QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan  (cost=1.00..5.10 rows=1000 width=72) (actual time=3.201..3.221 rows=8.00 loops=1)
   Output: nodes.name, (count(*)), (round(avg(logs.duration), 0))
   Relations: Aggregate on ((logs) LEFT JOIN (nodes))
   Remote SQL: SELECT r2.name, count(*), round(avg(r1.duration), 0) FROM  "default".logs r1 ALL LEFT JOIN "default".nodes r2 ON (((r1.node_id = r2.node_id))) GROUP BY r2.name
   FDW Time: 0.086 ms
 Planning Time: 0.335 ms
 Execution Time: 3.261 ms
(7 rows)

ローカルテーブルとの JOIN は、注意深くチューニングしないと、非効率なクエリを生成します。この例では、 nodes テーブルのローカルコピーを作成し、リモートテーブルの代わりにそれと JOIN します。

try=# CREATE TABLE local_nodes AS SELECT * FROM nodes;
SELECT 8

try=# EXPLAIN (ANALYZE, VERBOSE)
       SELECT name, count(*), round(avg(duration))
         FROM logs
         LEFT JOIN local_nodes on logs.node_id = local_nodes.node_id
        GROUP BY name;
                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=147.65..150.65 rows=200 width=72) (actual time=6.215..6.235 rows=8.00 loops=1)
   Output: local_nodes.name, count(*), round(avg(logs.duration), 0)
   Group Key: local_nodes.name
   Batches: 1  Memory Usage: 32kB
   Buffers: shared hit=1
   ->  Hash Left Join  (cost=31.02..129.28 rows=2450 width=36) (actual time=2.202..5.125 rows=1000.00 loops=1)
         Output: local_nodes.name, logs.duration
         Hash Cond: (logs.node_id = local_nodes.node_id)
         Buffers: shared hit=1
         ->  Foreign Scan on public.logs  (cost=10.00..20.00 rows=1000 width=12) (actual time=2.089..3.779 rows=1000.00 loops=1)
               Output: logs.req_id, logs.start_at, logs.duration, logs.resource, logs.method, logs.node_id, logs.response
               Remote SQL: SELECT duration, node_id FROM "default".logs
               FDW Time: 1.447 ms
         ->  Hash  (cost=14.90..14.90 rows=490 width=40) (actual time=0.090..0.091 rows=8.00 loops=1)
               Output: local_nodes.name, local_nodes.node_id
               Buckets: 1024  Batches: 1  Memory Usage: 9kB
               Buffers: shared hit=1
               ->  Seq Scan on public.local_nodes  (cost=0.00..14.90 rows=490 width=40) (actual time=0.069..0.073 rows=8.00 loops=1)
                     Output: local_nodes.name, local_nodes.node_id
                     Buffers: shared hit=1
 Planning:
   Buffers: shared hit=14
 Planning Time: 0.551 ms
 Execution Time: 6.589 ms

この場合、ローカルのカラムではなく node_id でグループ化することで、集約処理のより多くを ClickHouse 側に任せられ、その後でルックアップテーブルと join できます。

try=# EXPLAIN (ANALYZE, VERBOSE)
       WITH remote AS (
           SELECT node_id, count(*), round(avg(duration))
             FROM logs
            GROUP BY node_id
       )
       SELECT name, remote.count, remote.round
         FROM remote
         JOIN local_nodes
           ON remote.node_id = local_nodes.node_id
        ORDER BY name;
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=65.68..66.91 rows=490 width=72) (actual time=4.480..4.484 rows=8.00 loops=1)
   Output: local_nodes.name, remote.count, remote.round
   Sort Key: local_nodes.name
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=4
   ->  Hash Join  (cost=27.60..43.79 rows=490 width=72) (actual time=4.406..4.422 rows=8.00 loops=1)
         Output: local_nodes.name, remote.count, remote.round
         Inner Unique: true
         Hash Cond: (local_nodes.node_id = remote.node_id)
         Buffers: shared hit=1
         ->  Seq Scan on public.local_nodes  (cost=0.00..14.90 rows=490 width=40) (actual time=0.010..0.016 rows=8.00 loops=1)
               Output: local_nodes.node_id, local_nodes.name, local_nodes.region, local_nodes.arch, local_nodes.os
               Buffers: shared hit=1
         ->  Hash  (cost=15.10..15.10 rows=1000 width=48) (actual time=4.379..4.381 rows=8.00 loops=1)
               Output: remote.count, remote.round, remote.node_id
               Buckets: 1024  Batches: 1  Memory Usage: 9kB
               ->  Subquery Scan on remote  (cost=1.00..15.10 rows=1000 width=48) (actual time=4.337..4.360 rows=8.00 loops=1)
                     Output: remote.count, remote.round, remote.node_id
                     ->  Foreign Scan  (cost=1.00..5.10 rows=1000 width=48) (actual time=4.330..4.349 rows=8.00 loops=1)
                           Output: logs.node_id, (count(*)), (round(avg(logs.duration), 0))
                           Relations: Aggregate on (logs)
                           Remote SQL: SELECT node_id, count(*), round(avg(duration), 0) FROM "default".logs GROUP BY node_id
                           FDW Time: 0.055 ms
 Planning:
   Buffers: shared hit=5
 Planning Time: 0.319 ms
 Execution Time: 4.562 ms

この “Foreign Scan” ノードでは、node_id ごとの集約がプッシュダウンされるようになり、 Postgres に引き戻す必要のある行数が 1000 行 (全件) から各ノード 1 行ずつのわずか 8 行まで削減されます。

パーティション化テーブル

PostgreSQL の[パーティション化テーブル]では、ローカルパーティションと ClickHouse をバックエンドとする外部パーティションを混在させることができます。一般的な構成では、古いデータを ClickHouse にオフロードし、最新のデータは PostgreSQL に保持します。

CREATE TABLE events (id int, ts date, val int, amt float8)
    PARTITION BY RANGE (ts);

-- 2023 data lives on ClickHouse
CREATE FOREIGN TABLE events_2023 PARTITION OF events
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01')
    SERVER ch_svr OPTIONS (table_name 'events');

-- 2024 data stays local
CREATE TABLE events_2024 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

ローカルパーティションから外部パーティションへデータを移動する例については、 offload-partition.sqlを参照してください。ローカルパーティションと外部パーティションの両方にまたがる集計には、PostgreSQL でデフォルトでは無効になっている[パーティション単位の集計]が必要です。

SET enable_partitionwise_aggregate = on;

enable_partitionwise_aggregate を有効にすると、PostgreSQL は Append の下で部分集約を実行し、その後、上位の最終集約でそれらを結合して結果を生成します。pg_clickhouse は外部パーティションの部分集約を ClickHouse にプッシュダウンします。

try=# EXPLAIN (VERBOSE, COSTS OFF)
       SELECT count(*), sum(val), min(ts), max(ts) FROM events;
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate
   Output: count(*), sum(events.val), min(events.ts), max(events.ts)
   ->  Append
         ->  Foreign Scan
               Output: (PARTIAL count(*)), (PARTIAL sum(events.val)), (PARTIAL min(events.ts)), (PARTIAL max(events.ts))
               Relations: Aggregate on (events_2023 events)
               Remote SQL: SELECT count(*), sum(val), min(ts), max(ts) FROM "default".events
         ->  Partial Aggregate
               Output: PARTIAL count(*), PARTIAL sum(events_1.val), PARTIAL min(events_1.ts), PARTIAL max(events_1.ts)
               ->  Seq Scan on public.events_2024 events_1
                     Output: events_1.val, events_1.ts

部分集約がプッシュダウンされる条件

PostgreSQL では、部分集約は遷移状態として表され、ファイナライズ処理でパーティション間の遷移状態が結合されます。pg_clickhouse がパーティションの部分集約をプッシュダウンできるのは、それを ClickHouse の値として表現できる場合に限られます。

遷移状態がすでに最終値となっている分解可能な集約関数は、直接プッシュダウンされます。対象は count、sum、min、max、bool_and/every、 bool_or、bit_and、bit_or、bit_xor です。
**整数に対する avg**は、{count, sum} の状態を配列としてプッシュダウンします。
**浮動小数点に対する avg、var_pop、var_samp、stddev_pop、stddev_samp**は、 {N, sum, sum of squared deviations} の状態を配列としてプッシュダウンします。

FILTER (WHERE …) も、これらの集約関数とともにプッシュダウンされます。

フォールバックする場合

遷移状態が PostgreSQL の不透明な internal 型である集約関数には、ポータブルな表現がないため、外部パーティションは行を取得してローカルで集約します。これには numeric に対するすべての集約関数のほか、avg(bigint) と avg(interval) が含まれます。 DISTINCT、順序付き集合集約関数、可変長引数集約関数もフォールバックします。

PREPARE, EXECUTE, DEALLOCATE

v0.1.2以降、pg_clickhouse は主に PREPARE コマンドで作成されるパラメータ付きクエリをサポートしています:

try=# PREPARE avg_durations_between_dates(date, date) AS
       SELECT date(start_at), round(avg(duration)) AS average_duration
         FROM logs
        WHERE date(start_at) BETWEEN $1 AND $2
        GROUP BY date(start_at)
        ORDER BY date(start_at);
PREPARE

プリペアドステートメントを実行するには、通常どおり EXECUTE を使用します。

try=# EXECUTE avg_durations_between_dates('2025-12-09', '2025-12-13');
    date    | average_duration
------------+------------------
 2025-12-09 |              190
 2025-12-10 |              194
 2025-12-11 |              197
 2025-12-12 |              190
 2025-12-13 |              195
(5 行)

パラメータ化された実行では、[根本原因となっていたバグ] が [修正] される ClickHouse 25.8 より前のバージョンで、http ドライバーが DateTime のタイムゾーンを正しく変換できません。PostgreSQL では PREPARE を使っていなくても、パラメータ化されたクエリプランが使用されることがある点に注意してください。タイムゾーンの正確な変換が必要なクエリで、25.8 以降にアップグレードできない場合は、代わりにバイナリドライバーを使用してください。

通常どおり、pg_clickhouse は集計をプッシュダウンします。これは EXPLAIN の詳細出力で確認できます：

try=# EXPLAIN (VERBOSE) EXECUTE avg_durations_between_dates('2025-12-09', '2025-12-13');
                                                                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan  (cost=1.00..5.10 rows=1000 width=36)
   Output: (date(start_at)), (round(avg(duration), 0))
   Relations: Aggregate on (logs)
   Remote SQL: SELECT date(start_at), round(avg(duration), 0) FROM "default".logs WHERE ((date(start_at) >= '2025-12-09')) AND ((date(start_at) <= '2025-12-13')) GROUP BY (date(start_at)) ORDER BY date(start_at) ASC NULLS LAST
(4 rows)

完全な日付の値が送信されており、パラメータのプレースホルダーではないことに注意してください。これは、PostgreSQL の [PREPARE に関する注意事項]で説明されているとおり、最初の 5 回のリクエストに当てはまります。6 回目の実行では、ClickHouse の {param:type} 形式の[クエリパラメータ]が送信されます。パラメータ:

                                                                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan  (cost=1.00..5.10 rows=1000 width=36)
   Output: (date(start_at)), (round(avg(duration), 0))
   Relations: Aggregate on (logs)
   Remote SQL: SELECT date(start_at), round(avg(duration), 0) FROM "default".logs WHERE ((date(start_at) >= {p1:Date})) AND ((date(start_at) <= {p2:Date})) GROUP BY (date(start_at)) ORDER BY date(start_at) ASC NULLS LAST
(4 rows)

プリペアドステートメントを解放するには、DEALLOCATE を使用します:

try=# DEALLOCATE avg_durations_between_dates;
DEALLOCATE

INSERT

リモートのClickHouseテーブルに値を挿入するには、INSERT コマンドを使用します。

try=# INSERT INTO nodes(node_id, name, region, arch, os)
VALUES (9,  'Augustin Gamarra', 'us-west-2', 'amd64', 'Linux')
     , (10, 'Cerisier', 'us-east-2', 'amd64', 'Linux')
     , (11, 'Dewalt', 'use-central-1', 'arm64', 'macOS')
;
INSERT 0 3

COPY

リモートの ClickHouse テーブルに複数の行をまとめて挿入するには、COPY コマンドを使用します。

try=# COPY logs FROM stdin CSV;
4285871863,2025-12-05 11:13:58.360760,206,/widgets,POST,8,401
4020882978,2025-12-05 11:33:48.248450,199,/users/1321945,HEAD,3,200
3231273177,2025-12-05 12:20:42.158575,220,/search,GET,2,201
\.
>> COPY 3

⚠️ Batch API の制限 pg_clickhouse は、PostgreSQL FDW のバッチ insert API のサポートをまだ実装していません。そのため、現在 COPY はレコードを挿入するために INSERT ステートメントを使用しています。これは今後のリリースで改善される予定です。

LOAD

pg_clickhouse 共有ライブラリを読み込むには、LOAD を使用します:

try=# LOAD 'pg_clickhouse';
LOAD

通常、LOAD を使う必要はありません。Postgres は、その機能 (関数、外部テーブルなど) のいずれかが初めて使用されたときに、自動的に pg_clickhouse を読み込みます。 LOAD pg_clickhouse が役立つ可能性があるのは、SET で pg_clickhouse のパラメータを設定してから、それらに依存するクエリを実行したい場合に限られます。

SET

SET を使用して、pg_clickhouse のカスタム設定パラメーターを設定します。

`pg_clickhouse.session_settings`

pg_clickhouse.session_settings パラメーターは、後続のクエリに適用する [ClickHouse 設定] を指定します。例:

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

デフォルトは

join_use_nulls 1, group_by_use_nulls 1, final 1, transform_null_in 0

ClickHouse サーバーの設定に戻すには、これを空文字列に設定します — ただし、プッシュダウンの正しさは、これらのデフォルト設定の一部に依存します: 外部結合には join_use_nulls、IN ファミリーには transform_null_in (IN と NULL のセマンティクスを参照) 。

SET pg_clickhouse.session_settings = '';

構文は、1 つ以上のスペースで区切られたキー/値ペアのカンマ区切りリストです。キーは [ClickHouse 設定] に対応している必要があります。値に含まれるスペース、カンマ、バックスラッシュは、バックスラッシュでエスケープします:

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

または、スペースやカンマをエスケープせずに済むよう、単一引用符で囲んだ値を使用します。二重引用符を使う必要がなくなるため、[ダラークォート] の使用も検討してください:

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 自体の動作に支障をきたす場合、無視されます。これには次のものが含まれます。

date_time_output_format: http ドライバーではこれが “iso” である必要があります
format_tsv_null_representation: http ドライバーではデフォルト値が必要です
output_format_tsv_crlf_end_of_line http ドライバーではデフォルト値が必要です

それ以外については、pg_clickhouse は設定を検証せず、すべてのクエリごとに ClickHouse にそのまま渡します。したがって、各 ClickHouse バージョンですべての設定をサポートします。 pg_clickhouse.session_settings を設定する前に、 pg_clickhouse を読み込んでおく必要がある点に注意してください。共有ライブラリのプリロードを使用するか、または拡張機能内のいずれかのオブジェクトを使って、確実に読み込まれるようにしてください。

`pg_clickhouse.pushdown_regex`

pg_clickhouse.pushdown_regex パラメータは、pg_clickhouse が正規表現関数および演算子をプッシュダウンするかどうかを制御します。既定ではプッシュダウンが有効です。プッシュダウンを無効にするには、このパラメータを false に設定します:

SET pg_clickhouse.pushdown_regex = 'false';

詳細については、正規表現を参照してください。

ALTER ROLE

ALTER ROLE’s SET コマンドを使用すると、pg_clickhouse をプリロードしたり、特定のロールに対してそのパラメータをSETしたりできます。

try=# ALTER ROLE CURRENT_USER SET session_preload_libraries = pg_clickhouse;
ALTER ROLE

try=# ALTER ROLE CURRENT_USER SET pg_clickhouse.session_settings = 'final 1';
ALTER ROLE

ALTER ROLE の RESET コマンドを使用して、pg_clickhouse のプリロード設定やパラメータをリセットします:

try=# ALTER ROLE CURRENT_USER RESET session_preload_libraries;
ALTER ROLE

try=# ALTER ROLE CURRENT_USER RESET pg_clickhouse.session_settings;
ALTER ROLE

プリロード

すべて、またはほぼすべてのPostgres接続で pg_clickhouse を使用する必要がある場合は、これを自動的に読み込むために、共有ライブラリのプリロードの使用を検討してください:

`session_preload_libraries`

PostgreSQL への新しい接続のたびに共有ライブラリを読み込みます:

session_preload_libraries = pg_clickhouse

サーバーを再起動せずに更新を反映するのに便利で、再接続するだけで済みます。ALTER ROLE を使って、特定のユーザーやロールに対して設定することもできます。

`shared_preload_libraries`

起動時に、共有ライブラリを PostgreSQL の親プロセスに読み込みます:

shared_preload_libraries = pg_clickhouse

各セッションごとのメモリ使用量と読み込み時のオーバーヘッドを抑えられますが、ライブラリの更新時にはクラスターの再起動が必要です。

データ型

pg_clickhouse は、以下の ClickHouse データ型を PostgreSQL のデータ型にマッピングします。IMPORT FOREIGN SCHEMA では、カラムのインポート時に PostgreSQL カラム型として先頭の型が使用されます。追加の型は、CREATE FOREIGN TABLE ステートメントで使用できます。

ClickHouse	PostgreSQL	注記
Bool	boolean
Date	date
Date32	date
DateTime	timestamptz
Decimal	numeric
Float32	real
Float64	double precision
IPv4	inet
IPv6	inet
Int16	smallint
Int32	integer
Int64	bigint
Int8	smallint
JSON	jsonb, json
String	text, bytea
UInt16	integer
UInt32	bigint
UInt64	bigint	値が BIGINT の最大値を超えるとエラー
UInt8	smallint
UUID	uuid

すべてのカラムは、text、varchar、またはその他の文字列型としても読み取れます。値は上記の PostgreSQL 型に変換された後、その型の出力関数を通じてレンダリングされます。bigint の最大値を超える UInt64 値は引き続きエラーになるため、これらは ClickHouse の toString() 関数でレンダリングしてください。追加の注記と詳細を以下に示します。

BYTEA

ClickHouse は PostgreSQL の BYTEA 型に相当する型を提供していませんが、String 型に任意のバイト列を格納できます。通常、ClickHouse の文字列は PostgreSQL の TEXT にマッピングしますが、バイナリデータを扱う場合は BYTEA にマッピングしてください。例：

-- Create ClickHouse table with String columns.
CALL clickhouse_perform('ch_srv', $$
    CREATE TABLE bytes (
        c1 Int8, c2 String, c3 String
    ) ENGINE = MergeTree ORDER BY (c1);
$$);

-- Create foreign table with BYTEA columns.
CREATE FOREIGN TABLE bytes (
    c1 int,
    c2 BYTEA,
    c3 BYTEA
) SERVER ch_srv OPTIONS( table_name 'bytes' );

-- Insert binary data into the foreign table.
INSERT INTO bytes
SELECT n, sha224(bytea('val'||n)), decode(md5('int'||n), 'hex')
  FROM generate_series(1, 4) n;

-- View the results.
SELECT * FROM bytes;

最後のSELECTクエリの出力は以下のとおりです：

 c1 |                             c2                             |                 c3
----+------------------------------------------------------------+------------------------------------
  1 | \x1bf7f0cc821d31178616a55a8e0c52677735397cdde6f4153a9fd3d7 | \xae3b28cde02542f81acce8783245430d
  2 | \x5f6e9e12cd8592712e638016f4b1a2e73230ee40db498c0f0b1dc841 | \x23e7c6cacb8383f878ad093b0027d72b
  3 | \x53ac2c1fa83c8f64603fe9568d883331007d6281de330a4b5e728f9e | \x7e969132fc656148b97b6a2ee8bc83c1
  4 | \x4e3c2e4cb7542a45173a8dac939ddc4bc75202e342ebc769b0f5da2f | \x8ef30f44c65480d12b650ab6b2b04245
(4 rows)

ClickHouseのカラムにnulバイトが含まれている場合、TEXTカラムを使用する外部テーブルは正しい値を出力しないことに注意してください：

-- Create foreign table with TEXT columns.
CREATE FOREIGN TABLE texts (
    c1 int,
    c2 TEXT,
    c3 TEXT
) SERVER ch_srv OPTIONS( table_name 'bytes' );

-- Encode binary data as hex.
SELECT c1, encode(c2::bytea, 'hex'), encode(c3::bytea, 'hex') FROM texts ORDER BY c1;

出力結果:

 c1 |                          encode                          |              encode
----+----------------------------------------------------------+----------------------------------
  1 | 1bf7f0cc821d31178616a55a8e0c52677735397cdde6f4153a9fd3d7 | ae3b28cde02542f81acce8783245430d
  2 | 5f6e9e12cd8592712e638016f4b1a2e73230ee40db498c0f0b1dc841 | 23e7c6cacb8383f878ad093b
  3 | 53ac2c1fa83c8f64603fe9568d883331                         | 7e969132fc656148b97b6a2ee8bc83c1
  4 | 4e3c2e4cb7542a45173a8dac939ddc4bc75202e342ebc769b0f5da2f | 8ef30f44c65480d12b650ab6b2b04245
(4 rows)

2行目と3行目の値が切り捨てられていることに注意してください。これは、PostgreSQLがnul終端文字列に依存しており、文字列内のnulをサポートしていないためです。バイナリ値を TEXT カラムに挿入しようとすると、成功し、期待どおりに動作します：

-- Insert via text columns:
TRUNCATE texts;
INSERT INTO texts
SELECT n, sha224(bytea('val'||n)), decode(md5('int'||n), 'hex')
  FROM generate_series(1, 4) n;

-- View the data.
SELECT c1, encode(c2::bytea, 'hex'), encode(c3::bytea, 'hex') FROM texts ORDER BY c1;

テキストカラムは正しく表示されます:

 c1 |                          encode                          |              encode
----+----------------------------------------------------------+----------------------------------
  1 | 1bf7f0cc821d31178616a55a8e0c52677735397cdde6f4153a9fd3d7 | ae3b28cde02542f81acce8783245430d
  2 | 5f6e9e12cd8592712e638016f4b1a2e73230ee40db498c0f0b1dc841 | 23e7c6cacb8383f878ad093b0027d72b
  3 | 53ac2c1fa83c8f64603fe9568d883331007d6281de330a4b5e728f9e | 7e969132fc656148b97b6a2ee8bc83c1
  4 | 4e3c2e4cb7542a45173a8dac939ddc4bc75202e342ebc769b0f5da2f | 8ef30f44c65480d12b650ab6b2b04245
(4 rows)

しかし、BYTEAとして読み取ることはできません:

# SELECT * FROM bytes;
 c1 |                                                           c2                                                           |                                   c3
----+------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------
  1 | \x5c783162663766306363383231643331313738363136613535613865306335323637373733353339376364646536663431353361396664336437 | \x5c786165336232386364653032353432663831616363653837383332343534333064
  2 | \x5c783566366539653132636438353932373132653633383031366634623161326537333233306565343064623439386330663062316463383431 | \x5c783233653763366361636238333833663837386164303933623030323764373262
  3 | \x5c783533616332633166613833633866363436303366653935363864383833333331303037643632383164653333306134623565373238663965 | \x5c783765393639313332666336353631343862393762366132656538626338336331
  4 | \x5c783465336332653463623735343261343531373361386461633933396464633462633735323032653334326562633736396230663564613266 | \x5c783865663330663434633635343830643132623635306162366232623034323435
(4 rows)

原則として、エンコードされた文字列には TEXT カラムのみを、バイナリデータには BYTEA カラムのみを使用し、両者を混在させないでください。

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

関数

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

`clickhouse_raw_query`

非推奨: clickhouse_raw_query() は非推奨警告を出力し、次のリリースで削除されます。行の読み取りには clickhouse_query を使用し、結果を返さないステートメントの実行には clickhouse_perform を使用してください。どちらもアドホックな接続文字列ではなく、設定済み server のドライバー、credentials、 database、および connection cache を再利用します。

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

ClickHouse service に接続し、単一のクエリを実行してから切断します。省略可能な 2 番目の引数では connection string を指定します。デフォルトは host=localhost port=8123 です。サポートされる connection parameter は次のとおりです。

driver: 使用する connection driver。“http” または “binary” のいずれかです。デフォルトは “http” です
host: 接続先の host。必須です。
port: 接続先の port。“http” driver の場合はデフォルトで 8123、“binary” driver の場合は 9000 です。host が ClickHouse Cloud host の場合は、それぞれ 8443 または 9440 になります
dbname: 接続先の database 名。
username: 接続に使用する username。デフォルトは default です
password: 認証に使用する password。デフォルトでは password はありません

どちらの driver もタブ区切りの行 (null は \N) を返しますが、値ごとの表現は異なります。“http” driver は ClickHouse 独自の TSV フォーマットをそのまま返すのに対し、“binary” driver は各値を PostgreSQL の出力関数に渡します。デフォルトでは、どのロールにもこの関数に対する EXECUTE 権限はありません。GRANTで、アドホックな ClickHouse クエリを正当に実行する必要があるロールにのみアクセスを付与することを検討してください。たとえば、専用の ClickHouse 管理者ロールです。結果を返さないクエリに便利ですが、値を返すクエリの場合は単一のテキスト値として返されます。

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)

`clickhouse_server_version`

SELECT clickhouse_server_version('taxi_srv');

指定したforeign serverのClickHouse server バージョンをmajor.minor.patch形式で返します。必要に応じて、server’s optionsおよびcurrent user’s user mappingを使用して接続します。

 clickhouse_server_version
---------------------------
 25.8.1
(1 row)

ネイティブプロトコル接続のハンドシェイク、または HTTP 経由で実行する単一の SELECT version() クエリからバージョンを取得し、接続の存続期間中キャッシュします。

`clickhouse_query`

SELECT * FROM clickhouse_query(
    'server',
    'SELECT id, name, salary FROM remote_table WHERE salary > 50000'
) AS ch(id int, name text, salary numeric);

構成済みのforeign serverに対してクエリを実行し、その行をrelationとして返します。ClickHouseの各結果カラムは、カラム定義リストで指定したPostgreSQL型にマッピングされます。serverのdriver、 credentials、database、およびconnection cacheが再利用されます。最初のargumentには、CREATE SERVERで作成したserverの名前を指定します。カラム定義リスト (AS name(col type, ...)) が必要です。PostgreSQLでは行を取得する前に結果のshapeを把握する必要があり、クエリが返すカラムと一致している必要があります。値は、外部テーブルのカラムと同様に、ClickHouseから宣言された型に変換されます。DDLなど、結果を返さないステートメントでは宣言するものがありません。そのようなステートメントは代わりにclickhouse_performで実行してください。デフォルトでは、どのロールにもEXECUTEアクセス権はありません。関数の使用を許可するには、ロールにGRANTしてください。

GRANT EXECUTE ON FUNCTION clickhouse_query(text, text) TO ch_admin;

`clickhouse_perform`

CALL clickhouse_perform(
    'server',
    'CREATE TABLE remote_table (id Int32) ENGINE = MergeTree ORDER BY id'
);

設定済みの foreign server に対してステートメントを実行し、結果を破棄します。DDL のように行を返さず、clickhouse_query で結果の shape を宣言できないステートメントに使用します。server は clickhouse_query と同じ方法で解決され、その driver、credentials、database、および connection cache が再利用されます。プロシージャとして、SELECT ではなく CALL で呼び出す必要があり、行は返しません。デフォルトでは、どのロールにも EXECUTE アクセスは付与されていません。プロシージャの使用を許可するには、ロールに GRANT します。

GRANT EXECUTE ON PROCEDURE clickhouse_perform(text, text) TO ch_admin;

プッシュダウン関数

pg_clickhouse は、条件式 (HAVING 句および WHERE 句) で使用される PostgreSQL の組み込み関数の一部をプッシュダウンします。対応する関数は、ClickHouse では以下のとおりです。

abs: abs
factorial: factorial
mod (int2/int4/int8/numeric): 剰余
pow & power (float8/numeric): pow
round: round
sin, cos, tan, atan, atan2, sinh, cosh, tanh, asinh, degrees, radians, pi: ClickHouse の数学関数と同名です。asin, acos, atanh, acosh はプッシュダウンされません。PG では範囲外の入力でエラーになりますが、CH では NaN が返されます。
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
extract(field FROM source): date_part と同じ対応
date(timestamp) & date(timestamptz): toDate (CH alias date としてデパースされます)
array_position: indexOf に nullIf を使用して 0 を NULL に変換し、検索開始インデックスを指定する第 3 引数がある場合は arraySlice を使用します。なお、現在 nan は一致しません
array_cat: arrayConcat
array_append: arrayPushBack
array_prepend: arrayPushFront
array_remove: arrayRemove
cardinality: length
array_length(array, 1): nullIf(length(array), 0)
array_length & cardinality: length
array_to_string: arrayStringConcat
string_to_array: splitByString
split_part: splitByString + 配列の添字指定
trim_array: arrayResize
array_fill: arrayWithConstant
array_reverse: arrayReverse
array_shuffle: arrayShuffle
array_sample: arrayRandomSample
array_sort: arraySort / arrayReverseSort
btrim: trimBoth
ltrim: trimLeft
rtrim: trimRight
concat_ws: concatWithSeparator
lower(text): lowerUTF8
upper(text): upperUTF8
substring(text, ...) & substr(text, ...): substringUTF8
substring(bytea, ...) & substr(bytea, ...): substring
length(text): lengthUTF8
length(bytea) & octet_length: length
reverse(text): reverseUTF8
reverse(bytea): reverse
strpos: positionUTF8
regexp_like: match
regexp_match: 正規表現に丸括弧で囲まれた部分式が含まれている場合は extractGroups、そうでない場合は extractAll を arraySlice でスライスしたものです。
regexp_replace: g フラグがある場合は replaceRegexpOne、または replaceRegexpOne
regexp_split_to_array: splitByRegexp
md5: MD5
fmt が文字列定数 (大文字と小文字を区別しない) の場合の encode(bytea, fmt):
- encode(bytea, 'hex'): hex を lower でラップします。 PostgreSQL は小文字の hex を出力するためです。
- encode(bytea, 'base64'): base64Encode を replaceRegexpAll でラップし、 PostgreSQL の MIME (RFC 2045) 形式の 76 文字ごとの改行を再現します。
- encode(bytea, 'base64url') (PostgreSQL 19 以降) : base64URLEncode。これは、padding なしの PostgreSQL の RFC 4648 URL アルファベットに一致します。
json_extract_path_text: サブカラムの構文
json_extract_path: toJSONString + サブカラムの構文
jsonb_extract_path_text: サブカラム構文
jsonb_extract_path: toJSONString + サブカラムの構文
bit_count(bytea): bitCount
to_timestamp(float8): toDateTime64
to_char(timestamp[tz], fmt): fmt が、含まれるすべてのキーワードに ClickHouse で忠実に対応するものがある文字列定数である場合は、formatDateTime です。対応しているキーワードについては、Compatibility Notes の to_char() を参照してください。それ以外の場合、この関数は PostgreSQL 側でローカルに評価されます。
statement_timestamp, transaction_timestamp, & clock_timestamp: nowInBlock64 (nowInBlock64(9, $session_timezone))
CURRENT_DATE: now および toDate (toDate(now($session_timezone)))
now, CURRENT_TIMESTAMP, & LOCALTIMESTAMP: now64 (now64(9, $session_timezone))
CURRENT_TIMESTAMP(n) & LOCALTIMESTAMP(n): now64 (now64(n, $session_timezone))
CURRENT_DATABASE: PostgreSQL 関数から渡される値です。
CURRENT_SCHEMA: PostgreSQL関数から渡される値。
CURRENT_CATALOG: PostgreSQL 関数から渡される値です。
CURRENT_USER: PostgreSQL 関数の値として渡されます。
USER: PostgreSQL の関数から値として渡されます。
CURRENT_ROLE: PostgreSQL の関数から値として渡されます。
SESSION_USER: PostgreSQL 関数から値として渡されるものです。

プッシュダウン演算子

Array スライス (arr[L:U]): arraySlice
@> (配列が含む) : hasAll
<@ (配列に含まれる) : hasAll
&& (配列の重複) : hasAny
~ (正規表現に一致) : match
!~ (正規表現に一致しない) : match
~* (大文字と小文字を区別しない正規表現に一致しない) : match
!~* (大文字と小文字を区別しない正規表現に一致しない) : match
->> (JSON/JSONB の要素をテキストとして抽出) : sub-column syntax
-> (JSON/JSONB を抽出) : toJSONString + sub-column syntax

IN と NULL のセマンティクス

ClickHouse は IN を二値論理で評価します。プローブで一致が見つからない場合、NULL が含まれていても 0 を返しますが、PostgreSQL では NULL になります。PostgreSQL のセマンティクスを維持するため、pg_clickhouse は定数リストまたは配列に対する IN ファミリー (IN、NOT IN、= ANY、= ALL、<> ANY、<> ALL) を無条件にプッシュダウンします。プローブと配列要素のいずれも NULL になり得ないことを証明できる場合はネイティブ形式または低コストな形式を使用し、それ以外の場合は実行時に NULL 値を確認するガード付き CASE 形式を使用します。これにより、SELECT リストや GROUP BY などの値の位置を含むあらゆるコンテキストで、PostgreSQL と完全に同じ三値の結果 (TRUE、FALSE、NULL) を計算します。 NULL 許容カラムに対する NOT IN (SELECT ...) フィルタもプッシュダウンされ、PostgreSQL の動作を維持するための補償ガードを付加して逆変換されます。NULL を含む集合はすべての行を除外し、NULL のプローブが通過するのは集合が空の場合のみです。NOT NULL 宣言によって不要であることが証明できる場合は、各ガードを省略します。上記の配列形式とは異なり、このガードは通常のフィルタ条件内 (または NOT 配下) でのみ適用されます。値の位置にある IN (SELECT ...) や、グループ化または集計されたサブクエリ本体は、引き続きプッシュダウンされません。カラムを NOT NULL と宣言すると、より低コストなガードなし形式を送信できるため、プッシュダウンを最大化できます。IMPORT FOREIGN SCHEMA は、Nullable ではない ClickHouse カラムに対してこれを自動的に行います。この証明では、非 NULL 定数、NOT NULL カラム、およびそれらに対する基本的な算術演算 (+、-、*、単項 -) を追跡します。これらの規則は、ClickHouse のデフォルト設定である transform_null_in = 0 を前提としています。pg_clickhouse は pg_clickhouse.session_settings パラメータのデフォルト値を通じて、すべてのクエリでこれを設定します。これにより、ClickHouse サーバープロファイルがこの設定を暗黙的に変更することはありません。transform_null_in = 1 を設定すると、プッシュダウンされたすべての IN のセマンティクスが破綻します。

カスタム関数

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

dictGet

拡張機能のプッシュダウン

pg_clickhouse は、一部のコア拡張機能やサードパーティ拡張機能の関数を認識し、それらを ClickHouse の対応する関数にプッシュダウンします。

re2

すべての [re2 拡張機能] 演算子および関数は、ClickHouse に 1:1 でプッシュダウンされます。

@~ → match
re2match → match
re2extract → extract
re2extractall → extractAll
re2regexpextract → regexpExtract
re2extractgroups → extractGroups
re2replaceregexpone → replaceRegexpOne
re2replaceregexpall → replaceRegexpAll
re2countmatches → countMatches
re2countmatchescaseinsensitive → countMatchesCaseInsensitive
re2multimatchany → multiMatchAny
re2multimatchanyindex → multiMatchAnyIndex
re2multimatchallindices → multiMatchAllIndices

intarray

intarray 関数のうち、ClickHouse にプッシュダウンされるものは 1 つあります。

idx → indexOf

fuzzystrmatch

2つの fuzzystrmatch 関数が ClickHouse にプッシュダウンされます。

soundex: soundex
levenshtein (2引数) : editDistanceUTF8

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

pg_clickhouse は、互換性のあるデータ型に対して CAST(x AS bigint) のようなキャストをプッシュダウンします。互換性のない型ではプッシュダウンに失敗します。たとえば、この例で x が ClickHouse の UInt64 である場合、ClickHouse はその値をキャストしません。互換性のないデータ型へのキャストをプッシュダウンするために、pg_clickhouse は次の関数を提供しています。これらの関数は、プッシュダウンされなかった場合に PostgreSQL で例外を発生させます。

集計のプッシュダウン

以下の PostgreSQL 集約関数は ClickHouse にプッシュダウンされます。

カスタム集約関数

pg_clickhouse が作成するこれらのカスタム集約関数は、PostgreSQL に同等の機能がない一部の ClickHouse 集約関数について、外部クエリのプッシュダウンを提供します。これらの関数のいずれかをプッシュダウンできない場合は、例外をスローします。

ordered-set 集約関数のプッシュダウン

これらのordered-set 集約関数は、それぞれの direct argument をパラメータとして、ORDER BY 式を引数として渡すことで、ClickHouse のparametric 集約関数に対応します。たとえば、次の 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 はサポートされておらず、error が発生します。

percentile_cont(double): quantile
percentile_cont(double[]): quantiles
percentile_disc(double): quantileExactLow
percentile_disc(double[]): quantilesExactLow

カスタム ordered-set 集約関数

pg_clickhouse によって作成されたこれらのカスタムordered-set 集約関数は、一部の ClickHouse parametric 集約関数に対する外部クエリのプッシュダウンを提供します。これらの関数のいずれかをプッシュダウンできない場合は、例外が発生します。

quantile(double): quantile
quantileExact(double): quantileExact

カスタム ordered-set 集約関数

pg_clickhouse が提供するこれらのカスタムordered-set 集約関数では、特定の ClickHouse parametric 集約関数に対して外部クエリのプッシュダウンが可能です。これらの関数のいずれかをプッシュダウンできない場合は、例外が発生します。

プッシュダウンされるウィンドウ関数

以下の PostgreSQL [ウィンドウ関数]は、該当する場合はフレーム指定を含む OVER (PARTITION BY ... ORDER BY ...) 句とともに ClickHouse にプッシュダウンされます。

ランキング関数 (row_number、rank、dense_rank、ntile、cume_dist、 percent_rank) では、ClickHouse がこれらの関数のフレーム指定を受け付けないため、プッシュダウン時にフレーム句が省略されます。

互換性に関する注意

正規表現

pg_clickhouse.pushdown_regex が true の場合 (デフォルト) 、pg_clickhouse は正規表現を ClickHouse の同等表現にプッシュダウンし、基本的な互換性を確保するよう努めます。ただし、両者の違いと、それらを pg_clickhouse がどのように扱うかは把握しておいてください。

PostgreSQL は [POSIX 正規表現] をサポートし、ClickHouse は RE2 正規表現をサポートしています。動作の違いに注意してください。正規表現が ClickHouse によって評価される場合 (例: WHERE 句内) は RE2 を、Postgres によって評価される場合 (例: SELECT 句内) は POSIX を使用してください。
pg_clickhouse は、[Postgres フラグ]を ClickHouse の正規表現の (?) 内の先頭に追加することで、プッシュダウンします。たとえば:
regexp_like(val, '^VAL\d', 'i')
になります
match(val, concat('(?i)', '^VAL\\d'))

両方でサポートされており、そのため ClickHouse で評価される際に使用できるフラグは、次のとおりです:

フラグ	相当	注記
`i`	`i`	大文字と小文字を区別しないマッチング
`m`	`m-s`	`^` と `$` は、テキストの先頭/末尾に加えて行頭/行末にもマッチします
`n`	`m-s`	Postgres での `m` の別名
`p`	`-s`	`.` と `[^x]` が `\n` にマッチしないようにします
`s`	`s`	`.` と `[^x]` が `\n` にマッチするようにします
`t`		厳密構文、無視されます
`w`	`m`	部分的な改行感知マッチングの逆

RE2 がサポートしているのはこれらのフラグだけです。それ以外の [Postgres のフラグ] は使用しないでください。

この表は、改行および行末のマッチングにおける各種フラグ (およびフラグなし。これは s と同じです) の効果を要約したものです。Postgres では、 m と p を指定すると否定文字クラス ([^xyz]) が改行にマッチしなくなる点に注意してください。一方、ClickHouse の同等の機能ではそうなりません。それ以外の動作は、ClickHouse でも Postgres と同じです:

`a\nb` に適用した pattern	Postgres	ClickHouse	一致?
`a.b`	true	true	✔︎
`a[^x]b`	true	true	✔︎
`a$`	false	false	✔︎
`s` フラグ
`(?s)a.b`	true	true	✔︎
`(?s)a[^x]b`	true	true	✔︎
`(?s)a$`	false	false	✔︎
`m` フラグ
`(?m)a.b`	false	false	✔︎
`(?m)a[^x]b`	true	false	✘
`(?m)a$`	true	true	✔︎
`p` フラグ
`(?p)a.b`	false	false	✔︎
`(?p)a[^x]b`	true	false	✘
`(?p)a$`	false	false	✔︎
`w` フラグ
`(?w)a.b`	true	true	✔
`(?w)a[^x]b`	true	true	✔
`(?w)a$`	true	true	✔

正規表現関数に渡すその他のフラグがあると、その関数はプッシュダウンされません。
例外は regexp_replace() で、これも g フラグをサポートします。 g が設定されている場合、pg_clickhouse は replaceRegexpOne() ではなく replaceRegexpAll() を使用し、他のフラグを先頭に追加する前に g フラグを削除します。
Postgres の regexp_replace() の置換引数では、一致全体を参照するために \& を使用できますが、ClickHouse では一致全体に \0 を使用します。関数が ClickHouse にプッシュダウンされる場合は、必ず \0 を使用してください。
Postgres の regexp_match は一致がない場合に NULL を返しますが、 push down される式は空の配列を返します。戻り値を互換性のある形で比較するには、COALESCE() を使って NULL ではなく空の配列を返すようにします。例えば:
SELECT * FROM events WHERE COALESCE(regexp_match(msg, '^ERR'), '{}');

曖昧さを完全に避けるには、 Postgres の正規表現が ClickHouse にプッシュダウンされないよう pg_clickhouse.pushdown_regex を設定し、 pg_clickhouse が ClickHouse 互換の RE2 正規表現の direct プッシュダウンをサポートしている [re2 拡張機能] の使用を検討してください。

`to_char()`

timestamp および timestamp with time zone に対する PostgreSQL の to_char() は、フォーマット引数が非 NULL の文字列定数であり、なおかつ含まれる PostgreSQL のキーワードがすべて ClickHouse にバイト単位で完全一致する対応を持つ場合にのみ、ClickHouse の formatDateTime にプッシュダウンされます。フォーマットが動的な場合 (Const ではない場合) 、または未サポートのキーワードや modifier を含む場合、この呼び出しは PostgreSQL でローカルに評価されます。部分的な変換でプッシュダウンを試みることはないため、出力は PG 互換のまま保たれます。 numeric、interval、およびその他の timestamp 以外の型に対する 2 引数の to_char() 形式は、プッシュダウンされません。ClickHouse の formatDateTime は日付時刻値のみをフォーマットします。

変換されるキーワード

PostgreSQL	ClickHouse	意味
`YYYY`, `yyyy`	`%Y`	4桁の年
`YY`, `yy`	`%y`	2桁の年
`MM`, `mm`	`%m`	ゼロ埋めされた月 (01–12)
`DD`, `dd`	`%d`	ゼロ埋めされた日 (01–31)
`DDD`, `ddd`	`%j`	ゼロ埋めされた年内通算日 (001–366)
`HH24`, `hh24`	`%H`	ゼロ埋めされた24時間表記の時 (00–23)
`HH`, `hh`, `HH12`, `hh12`	`%I`	ゼロ埋めされた12時間表記の時 (01–12)
`MI`, `mi`	`%i`	ゼロ埋めされた分 (00–59)
`SS`, `ss`	`%S`	ゼロ埋めされた秒 (00–59)
`Q`, `q`	`%Q`	四半期 (1–4)
`Mon`	`%b`	省略した月名 (例: `Oct`)
`Dy`	`%a`	省略した曜日名 (例: `Mon`)
`AM`, `PM`	`%p`	午前・午後の指定子 (常に大文字)

引用符付きテキストとリテラル

"..." で囲まれたテキストは、そのまま渡されます。リテラルの % は、 ClickHouse の指定子プレフィックスをエスケープするため、%% に二重化されます。引用符の外側にある \" も、リテラルの " としてそのまま渡されます。"..." の内側では、バックスラッシュでエスケープされるのは " のみで、それ以外のバックスラッシュシーケンスはリテラルテキストとして扱われます。

著者

David E. Wheeler

​説明

​はじめに

​使い方

​バージョニングポリシー

​DDL SQL リファレンス

​CREATE EXTENSION

​ALTER EXTENSION

​DROP EXTENSION

​CREATE SERVER

​ALTER SERVER

​DROP SERVER

​CREATE USER MAPPING

​ALTER USER MAPPING

​DROP USER MAPPING

​IMPORT FOREIGN SCHEMA

​CREATE FOREIGN TABLE

​ALTER FOREIGN TABLE

​DROP FOREIGN TABLE

​DML SQL リファレンス

​EXPLAIN

​SELECT

​パーティション化テーブル

​部分集約がプッシュダウンされる条件

​フォールバックする場合

​PREPARE, EXECUTE, DEALLOCATE

​INSERT

​COPY

​LOAD

​SET

​pg_clickhouse.session_settings

​pg_clickhouse.pushdown_regex

​ALTER ROLE

​プリロード

​session_preload_libraries

​shared_preload_libraries

​データ型

​BYTEA

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

​関数

​clickhouse_raw_query

​clickhouse_server_version

​clickhouse_query

​clickhouse_perform

​プッシュダウン関数

​プッシュダウン演算子

​IN と NULL のセマンティクス

​カスタム関数

​拡張機能のプッシュダウン

​re2

​intarray

​fuzzystrmatch

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

​集計のプッシュダウン

​カスタム集約関数

​ordered-set 集約関数 の プッシュダウン

​カスタム ordered-set 集約関数

​カスタム ordered-set 集約関数

​プッシュダウンされるウィンドウ関数

​互換性に関する注意

​正規表現

​to_char()

​変換されるキーワード

​引用符付きテキストとリテラル

​著者

​著作権

説明

はじめに

使い方

バージョニングポリシー

DDL SQL リファレンス

CREATE EXTENSION

ALTER EXTENSION

DROP EXTENSION

CREATE SERVER

ALTER SERVER

DROP SERVER

CREATE USER MAPPING

ALTER USER MAPPING

DROP USER MAPPING

IMPORT FOREIGN SCHEMA

CREATE FOREIGN TABLE

ALTER FOREIGN TABLE

DROP FOREIGN TABLE

DML SQL リファレンス

EXPLAIN

SELECT

パーティション化テーブル

部分集約がプッシュダウンされる条件

フォールバックする場合

PREPARE, EXECUTE, DEALLOCATE

INSERT

COPY

LOAD

SET

`pg_clickhouse.session_settings`

`pg_clickhouse.pushdown_regex`

ALTER ROLE

プリロード

`session_preload_libraries`

`shared_preload_libraries`

データ型

BYTEA

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

関数

`clickhouse_raw_query`

`clickhouse_server_version`

`clickhouse_query`

`clickhouse_perform`

プッシュダウン関数

プッシュダウン演算子

IN と NULL のセマンティクス

カスタム関数

拡張機能のプッシュダウン

re2

intarray

fuzzystrmatch

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

集計のプッシュダウン

カスタム集約関数

ordered-set 集約関数のプッシュダウン

カスタム ordered-set 集約関数

カスタム ordered-set 集約関数

プッシュダウンされるウィンドウ関数

互換性に関する注意

正規表現

`to_char()`

変換されるキーワード

引用符付きテキストとリテラル

著者

著作権