Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

PostgreSQL テーブルエンジン

View as MarkdownEdit this pageReport an issueSet up docs MCP

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

テーブルの作成

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

TLS/SSL

TLS/SSL パラメータは libpq に渡され、名前付きコレクション のキーまたは末尾のキー・バリュー引数として設定できます。sslmode (disable、allow、prefer、require、verify-ca、verify-full) と、証明書および秘密鍵は、2 つの形式のいずれかで指定できます。未設定の場合は、libpq のデフォルト設定が適用されます (sslmode=prefer) 。

  • sslrootcert (CA 証明書、または特別な値 system) 、sslcert (クライアント証明書) 、sslkey (クライアント秘密鍵) は、サーバー上のローカルファイルへのパスです。これらはサーバー設定ファイルで定義された 名前付きコレクション でのみ指定でき、クエリでオーバーライドすることはできません。サーバー自身の権限でファイルを開くためです。
  • sslrootcert_pem、sslcert_pem、sslkey_pem には、パスの代わりに対応するファイルの内容をリテラルとして指定します。これらはクエリ、SQL で作成した 名前付きコレクション、または 名前付きコレクション のオーバーライドとしてなど、どこでも指定でき、パスワードと同様にログおよび SHOW クエリではマスクされます。

たとえば、暗号化された接続を必須とし、サーバー証明書を検証するには:

<named_collections>
    <postgres_creds>
        <host>localhost</host>
        <port>5432</port>
        <user>postgres</user>
        <password>****</password>
        <sslmode>verify-full</sslmode>
        <sslrootcert>/etc/clickhouse-server/postgresql-ca.crt</sslrootcert>
    </postgres_creds>
</named_collections>

設定ファイルを使用せず、証明書の内容をクエリで渡す場合は次のとおりです：

CREATE TABLE postgres_table (id UInt64, value String)
ENGINE = PostgreSQL('localhost:5432', 'database', 'table', 'user', 'password',
                    sslmode = 'verify-full', sslrootcert_pem = '-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----');

設定

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 テーブル関数でもサポートされています。

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

PostgreSQL のArray型は ClickHouse の Array に変換されます。

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

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

関連項目

関連コンテンツ

Navigation