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] [TTL expr1],
name2 type2 [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
...
) ENGINE = PostgreSQL({host:port, database, table, user, password[, schema, [, on_conflict]] | named_collection[, option=value [,..]]})
- カラム名は元の PostgreSQL テーブルと同じである必要がありますが、それらのうち一部のカラムのみを、任意の順序で使用できます。
- カラム型は元の PostgreSQL テーブルのものと異なっていてもかまいません。ClickHouse は値を ClickHouse のデータ型にキャストしようとします。
- external_table_functions_use_nulls 設定は、Nullable カラムの扱いを定義します。デフォルト値は 1 です。0 の場合、テーブル関数は Nullable カラムを作成せず、null の代わりにデフォルト値を挿入します。これは配列内の NULL 値にも適用されます。
エンジンパラメータ
host:port — PostgreSQL サーバーのアドレス。database — リモートデータベース名。table — リモートテーブル名。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');
SELECTクエリは、読み取り専用の PostgreSQL トランザクション内で COPY (SELECT ...) TO STDOUT として実行され、各SELECTクエリの後にコミットされます。
=, !=, >, >=, <, <=, IN などの単純なWHERE句は、PostgreSQL サーバー上で実行されます。
すべての JOIN、集計、ソート、IN [ array ] 条件、およびLIMITによるサンプリング制約は、PostgreSQL へのクエリが完了した後にのみ ClickHouse で実行されます。
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');
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>
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 テーブルに接続する
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テーブルに初期データを挿入する
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テーブルにインクリメンタルデータを挿入する
SELECT max(`int_id`) AS maxIntID FROM default.postgresql_copy;
INSERT INTO default.postgresql_copy
SELECT * FROM postgresql('localhost:5432', 'public', 'test', 'postges_user', 'postgres_password');
WHERE int_id > maxIntID;
作成された 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');
