postgresql

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

構文

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

引数

引数	説明
`host:port`	PostgreSQL サーバーのアドレス。
`database`	リモートデータベースの名前。
`table`	リモートテーブルの名前、または PostgreSQL にそのまま渡されるクエリ (テーブル名の代わりにクエリを渡すを参照) 。
`user`	PostgreSQL ユーザー。
`password`	ユーザーのパスワード。
`schema`	デフォルト以外のテーブルスキーマ。省略可能です。
`on_conflict`	競合解決の戦略。例: `ON CONFLICT DO NOTHING`。省略可能です。

引数は named collections を使用して渡すこともできます。この場合、host と port は別々に指定する必要があります。この方法は本番環境に推奨されます。 TLS/SSL パラメータは libpq に転送され、named collection のキーまたは末尾のキー・バリュー引数として指定できます。指定できるのは sslmode (disable、allow、prefer、require、verify-ca、verify-full。未設定の場合は libpq のデフォルトである prefer が適用されます) と、2 つの形式のいずれかで指定する証明書および秘密鍵です。sslrootcert (CA 証明書、または特別な値 system) 、sslcert (クライアント証明書) 、sslkey (クライアント秘密鍵) はサーバーローカルファイルへのパスであり、サーバー設定ファイルで定義された named collection でのみ指定できます。代わりに、sslrootcert_pem、sslcert_pem、sslkey_pem には対応するファイルの内容を直接指定できます。たとえば、postgresql('host:port', 'database', 'table', 'user', 'password', sslmode = 'verify-full', sslrootcert_pem = '...') のように指定します。これらはパスワードと同様に、ログおよび SHOW クエリではマスクされます。

戻り値

元の PostgreSQL テーブルと同じカラムを持つテーブルオブジェクト。

テーブル関数 postgresql(...) を、カラム名リスト付きのテーブル名と区別するには、INSERT クエリでキーワード FUNCTION または TABLE FUNCTION を使用する必要があります。以下の例を参照してください。

設定

postgresql テーブル関数 (およびPostgreSQLテーブルエンジン) で使用される接続プールは、末尾に SETTINGS 句を付けて設定できます。設定が指定されていない場合は、対応するクエリレベルの postgresql_* 設定の値がデフォルトで使用されます。postgresql_connection_pool_* および postgresql_connection_attempt_timeout の各設定とそのデフォルト値の一覧については、テーブルエンジンの設定セクションを参照してください。例:

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

実装の詳細

PostgreSQL 側の SELECT クエリは、読み取り専用の PostgreSQL トランザクション内で COPY (SELECT ...) TO STDOUT として実行され、各 SELECT クエリの後にコミットされます。 =, !=, >, >=, <, <=, IN などの単純な WHERE 句は、PostgreSQL サーバー上で実行されます。すべての JOIN、集計、ソート、IN [ array ] 条件、および LIMIT サンプリング制約は、PostgreSQL へのクエリが完了した後にのみ ClickHouse で実行されます。

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

テーブル名の代わりに、第 3 引数には、そのまま 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');

これは、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 クエリは、各 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 の Dictionary ソースで、レプリカの優先度をサポートしています。map 内の数値が大きいほど優先度は低くなります。最も高い優先度は 0 です。

例

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

または、named collectionsを使用する方法もあります：

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 にデータをレプリケートするために専用設計されたツールです。

SQLリファレンス

データ型

エンジン

関数

フォーマット

インターフェイス

設定

システムテーブル

データレイク

構文

引数

戻り値

設定

実装の詳細

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

例

PeerDB を使用した Postgres データのレプリケーションまたは移行

​構文

​引数

​戻り値

​設定

​実装の詳細

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

​例

​関連

​PeerDB を使用した Postgres データのレプリケーションまたは移行

構文

引数

戻り値

設定

実装の詳細

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

例

関連

PeerDB を使用した Postgres データのレプリケーションまたは移行