PostgreSQL テーブルエンジン - ClickHouse Documentation

PostgreSQL エンジンでは、リモートの PostgreSQL サーバーに保存されているデータに対して SELECT および INSERT クエリを実行できます。

現在、テーブルエンジンでサポートされているのは PostgreSQL バージョン 12 以降のみです。

Managed Postgres サービスもご利用いただけます。コンピュートと物理的に同一配置された NVMe ストレージを基盤としており、EBS のようなネットワーク接続型ストレージを使用する代替手段と比べて、ディスク I/O がボトルネックになるワークロードで最大 10 倍高速なパフォーマンスを実現します。さらに、ClickPipes の Postgres CDC (変更データキャプチャ) コネクタを使用して、Postgres データを ClickHouse にレプリケートできます。

テーブルの作成

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 のデータ型にキャストしようとします。
external_table_functions_use_nulls 設定は、Nullable カラムの扱いを定義します。デフォルト値は 1 です。0 の場合、テーブル関数は Nullable カラムを作成せず、null の代わりにデフォルト値を挿入します。これは配列内の NULL 値にも適用されます。

エンジンパラメータ

host:port — PostgreSQL サーバーのアドレス。
database — リモートデータベース名。
table — リモートテーブル名、または PostgreSQL にそのまま渡されるクエリです (テーブル名の代わりにクエリを渡すを参照) 。
user — PostgreSQL ユーザー。
password — ユーザーのパスワード。
schema — デフォルト以外のテーブルスキーマ。省略可能です。
on_conflict — 競合解決戦略。例: ON CONFLICT DO NOTHING。省略可能です。注意: このオプションを追加すると、挿入効率が低下します。

本番環境では、名前付きコレクション (バージョン 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');

設定

PostgreSQL テーブルエンジン (および postgresql テーブル関数) で使用される接続プールは、SETTINGS 句を使ってテーブルごとに設定できます。設定を指定しない場合は、対応するクエリレベルの postgresql_* 設定の値がデフォルトで使用されます。

`postgresql_connection_pool_size`

接続プールのサイズ (すべての接続が使用中の場合、クエリは接続が解放されるまで待機します) 。0以外の値である必要があります。デフォルト値: 16。

`postgresql_connection_pool_wait_timeout`

空の接続プールに対する push/pop のタイムアウト時間 (ミリ秒単位) です。0 は、プールが空のときにブロックすることを意味します。デフォルト値: 5000。

`postgresql_connection_pool_retries`

接続プールでの push/pop の再試行回数。デフォルト値: 2。

`postgresql_connection_pool_auto_close_connection`

プールに返却する前に接続を閉じます。デフォルト値: false。

`postgresql_connection_attempt_timeout`

PostgreSQL エンドポイントへの1回の接続試行における接続タイムアウト時間 (秒) です。この値は、接続 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クエリは、読み取り専用の PostgreSQL トランザクション内で COPY (SELECT ...) TO STDOUT として実行され、各SELECTクエリの後にコミットされます。 =, !=, >, >=, <, <=, IN などの単純なWHERE句は、PostgreSQL サーバー上で実行されます。すべての JOIN、集計、ソート、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');

これは、JOIN、集計、その他のあらゆる処理を PostgreSQL にプッシュダウンするのに役立ちます。このようなテーブルは読み取り専用であり、これに対するINSERTは許可されていません。同じ構文は postgresql テーブル関数でもサポートされています。

サブクエリ形式 (SELECT ...) は ClickHouse によって解析され、サーバーに送信される前に PostgreSQL 方言 (PostgreSQL の識別子の引用符付けと文字列リテラルのエスケープ) で再シリアライズされます。したがって、有効な ClickHouse SQL である必要があります。ClickHouse が解析しない PostgreSQL 固有の構文を渡すには、query('...') 形式を使用してください。この形式のテキストは、そのまま PostgreSQL に送信されます。渡されたクエリに対して、外側の ClickHouse クエリのWHERE、LIMIT、集計などがプッシュダウンされることはなく、完全なクエリ結果が取得された後に ClickHouse で適用されます。PostgreSQL から読み取るデータを制限するには、渡されたクエリの内部にフィルターを置いてください。external_table_strict_query = 1 を使用すると、プッシュダウンできない外側のフィルターは、ローカルで適用される代わりに例外で拒否されます。

PostgreSQL 側のINSERTクエリは、PostgreSQL トランザクション内で COPY "table_name" (field1, field2, ... fieldN) FROM STDIN として実行され、各INSERTステートメントの後に自動コミットされます。 PostgreSQL のArray型は ClickHouse の Array に変換されます。

注意してください。PostgreSQL では、type_name[] のように作成された配列データには、同じカラム内でも行ごとに次元数が異なる多次元配列を含めることができます。一方 ClickHouse では、同じカラム内のすべての行で次元数が同じ多次元配列しか許可されません。

複数のレプリカをサポートしており、|で列挙する必要があります。例えば:

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

PostgreSQL の Dictionary ソースでは、レプリカの優先度をサポートしています。マップ内の数値が大きいほど、優先度は低くなります。最も高い優先度は 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 の両方のステートメントを実行します。

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

関連項目

​テーブルの作成

​設定

​postgresql_connection_pool_size

​postgresql_connection_pool_wait_timeout

​postgresql_connection_pool_retries

​postgresql_connection_pool_auto_close_connection

​postgresql_connection_attempt_timeout

​実装の詳細

​テーブル名の代わりにクエリを渡す

​使用例

​PostgreSQL内のテーブル

​ClickHouse でテーブルを作成し、上で作成した PostgreSQL テーブルに接続する

​SELECTクエリを使用して、PostgreSQLテーブルからClickHouseテーブルに初期データを挿入する

​PostgreSQLテーブルからClickHouseテーブルにインクリメンタルデータを挿入する

​作成された ClickHouse テーブルからデータを取得する

​デフォルト以外のスキーマを使用する

​関連コンテンツ

テーブルの作成

設定

`postgresql_connection_pool_size`

`postgresql_connection_pool_wait_timeout`

`postgresql_connection_pool_retries`

`postgresql_connection_pool_auto_close_connection`

`postgresql_connection_attempt_timeout`

実装の詳細

テーブル名の代わりにクエリを渡す

使用例

PostgreSQL内のテーブル

ClickHouse でテーブルを作成し、上で作成した PostgreSQL テーブルに接続する

SELECTクエリを使用して、PostgreSQLテーブルからClickHouseテーブルに初期データを挿入する

PostgreSQLテーブルからClickHouseテーブルにインクリメンタルデータを挿入する

作成された ClickHouse テーブルからデータを取得する

デフォルト以外のスキーマを使用する

関連コンテンツ