PostgreSQL

リモートのPostgreSQLサーバー上のデータベースに接続できます。ClickHouseとPostgreSQLの間でデータをやり取りするための読み取りおよび書き込み操作 (SELECT および INSERT クエリ) をサポートします。

SHOW TABLES および DESCRIBE TABLE クエリを使用して、リモートのPostgreSQLにあるテーブル一覧とテーブル構造にリアルタイムでアクセスできます。

テーブル構造の変更 (ALTER TABLE ... ADD|DROP COLUMN) をサポートします。use_table_cache パラメータ (下記のエンジンパラメータを参照) が 1 に設定されている場合、テーブル構造はキャッシュされ、変更されているかどうかは確認されませんが、DETACH および ATTACH クエリで更新できます。

CREATE DATABASE test_database
ENGINE = PostgreSQL('host:port', 'database', 'user', 'password'[, `schema`, `use_table_cache`]);

エンジンパラメータ

• host:port — PostgreSQL サーバーのアドレス。
• database — リモートデータベース名。
• user — PostgreSQL ユーザー。
• password — ユーザーのパスワード。
• schema — PostgreSQL スキーマ。
• use_table_cache — データベーステーブルのテーブル構造をキャッシュするかどうかを指定します。省略可。デフォルト値: 0。

TLS/SSL パラメータは libpq に渡され、名前付きコレクション を通じて、または末尾のキー・バリュー引数として指定できます。sslmode (disable、allow、prefer、require、verify-ca、または verify-full) と、証明書および秘密鍵は、2 つの形式のいずれかで指定できます。sslrootcert (CA 証明書) 、sslcert (クライアント証明書) 、sslkey (クライアント秘密鍵) はサーバーローカルのファイルへのパスであり、サーバー設定ファイルで定義された名前付きコレクションからのみ受け付けられます。代わりに sslrootcert_pem、sslcert_pem、sslkey_pem では対応するファイルの内容を直接指定でき、SQL からも指定できます (例: ENGINE = PostgreSQL('host:port', 'database', 'user', 'password', sslmode = 'verify-full', sslrootcert_pem = '...')) 。これらの値は、パスワードと同様にログおよび SHOW クエリでマスクされます。未設定の場合は、libpq のデフォルトが適用されます (sslmode=prefer) 。

PostgreSQL サーバーとデータをやり取りする ClickHouse のデータベース：

CREATE DATABASE test_database
ENGINE = PostgreSQL('postgres1:5432', 'test_database', 'postgres', 'mysecretpassword', 'schema_name',1);

SHOW DATABASES;

┌─name──────────┐
│ default       │
│ test_database │
│ system        │
└───────────────┘

SHOW TABLES FROM test_database;

┌─name───────┐
│ test_table │
└────────────┘

PostgreSQLのテーブルからデータを読み取る:

SELECT * FROM test_database.test_table;

┌─id─┬─value─┐
│  1 │     2 │
└────┴───────┘

PostgreSQLテーブルへのデータ書き込み:

INSERT INTO test_database.test_table VALUES (3,4);
SELECT * FROM test_database.test_table;

┌─int_id─┬─value─┐
│      1 │     2 │
│      3 │     4 │
└────────┴───────┘

PostgreSQL でテーブル構造が変更された場合を考えてみましょう：

postgre> ALTER TABLE test_table ADD COLUMN data Text

データベースの作成時に use_table_cache パラメータが 1 に設定されていたため、ClickHouse のテーブル構造はキャッシュされており、変更されませんでした。

DESCRIBE TABLE test_database.test_table;

┌─name───┬─type──────────────┐
│ id     │ Nullable(Integer) │
│ value  │ Nullable(Integer) │
└────────┴───────────────────┘

テーブルをデタッチして再度アタッチすると、構造が更新されました：

DETACH TABLE test_database.test_table;
ATTACH TABLE test_database.test_table;
DESCRIBE TABLE test_database.test_table;

┌─name───┬─type──────────────┐
│ id     │ Nullable(Integer) │
│ value  │ Nullable(Integer) │
│ data   │ Nullable(String)  │
└────────┴───────────────────┘

• ブログ: ClickHouse と PostgreSQL - データの世界で最高の組み合わせ - 第1部
• ブログ: ClickHouse と PostgreSQL - データの世界で最高の組み合わせ - 第2部