Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

postgresql

View as MarkdownEdit this pageReport an issueSet up docs MCP

リモートの 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 テーブル関数 (および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 テーブルエンジンでもサポートされています。

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

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

Navigation