メインコンテンツまでスキップ
メインコンテンツまでスキップ

PostgreSQL

PostgreSQL から ClickHouse への完全な移行ガイド、データモデリングおよび同等の概念に関するアドバイスは、こちらで確認できます。次に、ClickHouse と PostgreSQL を接続する方法について説明します。

ClickHouseとPostgreSQLの接続

このページでは、PostgreSQLをClickHouseと統合するための以下のオプションを説明します。

  • ClickPipesを使用する。PeerDBによって提供されるClickHouse Cloud用のマネージド統合サービスで、現在はパブリックベータ版です。
  • PeerDBを使用する。PostgreSQLデータベースのレプリケーションに特化したオープンソースのCDCツールで、セルフホストされるClickHouseおよびClickHouse Cloudの両方に対応しています。
  • PostgreSQLテーブルエンジンを使用し、PostgreSQLテーブルからの読み込みを行う。
  • 実験的なMaterializedPostgreSQLデータベースエンジンを使用し、PostgreSQLのデータベースとClickHouseのデータベースを同期する。

PostgreSQLテーブルエンジンの使用​

PostgreSQLテーブルエンジンは、ClickHouseからリモートのPostgreSQLサーバー上に保存されたデータに対してSELECTおよびINSERT操作を許可します。このドキュメントでは、1つのテーブルを使用した基本的な統合方法を説明します。

1. PostgreSQLの設定​

  1. postgresql.confに、PostgreSQLがネットワークインターフェースでリスンするように以下のエントリを追加します。
listen_addresses = '*'
  1. ClickHouseから接続するためのユーザーを作成します。デモンストレーションの目的で、この例では完全なスーパーユーザー権限を付与します。
CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';
  1. PostgreSQLに新しいデータベースを作成します。
CREATE DATABASE db_in_psg;
  1. 新しいテーブルを作成します。
CREATE TABLE table1 (
    id         integer primary key,
    column1    varchar(10)
);
  1. テスト用にいくつかの行を追加します。
INSERT INTO table1
  (id, column1)
VALUES
  (1, 'abc'),
  (2, 'def');
  1. 新しいユーザーで新しいデータベースへの接続を許可するようにPostgreSQLを構成するには、pg_hba.confファイルに以下のエントリを追加します。アドレス行は、PostgreSQLサーバーのサブネットまたはIPアドレスで更新してください。
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    db_in_psg             clickhouse_user 192.168.1.0/24          password
  1. pg_hba.conf設定をリロードします（このコマンドはバージョンによって調整してください）。
/usr/pgsql-12/bin/pg_ctl reload
  1. 新しいclickhouse_userがログインできるか確認します。
psql -U clickhouse_user -W -d db_in_psg -h <your_postgresql_host>
注記

ClickHouse Cloudでこの機能を使用している場合は、ClickHouse CloudのIPアドレスがPostgreSQLインスタンスにアクセスできるようにする必要があるかもしれません。 ClickHouseのCloud Endpoints APIで、送信トラフィックの詳細を確認してください。

2. ClickHouseでテーブルを定義​

  1. clickhouse-clientにログインします。
clickhouse-client --user default --password ClickHouse123!
  1. 新しいデータベースを作成します。
CREATE DATABASE db_in_ch;
  1. PostgreSQLを使用するテーブルを作成します。
CREATE TABLE db_in_ch.table1
(
    id UInt64,
    column1 String
)
ENGINE = PostgreSQL('postgres-host.domain.com:5432', 'db_in_psg', 'table1', 'clickhouse_user', 'ClickHouse_123');

必要な最小パラメーターは以下の通りです：

parameterDescriptionexample
host:portホスト名またはIPとポートpostgres-host.domain.com:5432
databasePostgreSQLデータベース名db_in_psg
userPostgreSQLへの接続ユーザー名clickhouse_user
passwordPostgreSQLへの接続パスワードClickHouse_123
注記

完全なパラメーターリストについては、PostgreSQLテーブルエンジンのドキュメントページをご覧ください。

3 統合のテスト​

  1. ClickHouseで初期行を表示します：
SELECT * FROM db_in_ch.table1

ClickHouseのテーブルには、PostgreSQLのテーブルに既に存在している2行が自動的に反映されているはずです：

Query id: 34193d31-fe21-44ac-a182-36aaefbd78bf

┌─id─┬─column1─┐
│  1 │ abc     │
│  2 │ def     │
└────┴─────────┘
  1. PostgreSQLに戻り、テーブルに行をいくつか追加します：
INSERT INTO table1
  (id, column1)
VALUES
  (3, 'ghi'),
  (4, 'jkl');
  1. 新しく追加された2行がClickHouseテーブルに表示されるはずです：
SELECT * FROM db_in_ch.table1

応答は次のようになります：

Query id: 86fa2c62-d320-4e47-b564-47ebf3d5d27b

┌─id─┬─column1─┐
│  1 │ abc     │
│  2 │ def     │
│  3 │ ghi     │
│  4 │ jkl     │
└────┴─────────┘
  1. ClickHouseテーブルに行を追加すると何が起こるか見てみましょう：
INSERT INTO db_in_ch.table1
  (id, column1)
VALUES
  (5, 'mno'),
  (6, 'pqr');
  1. ClickHouseで追加された行はPostgreSQLのテーブルに表示されるはずです：
db_in_psg=# SELECT * FROM table1;
id | column1
----+---------
  1 | abc
  2 | def
  3 | ghi
  4 | jkl
  5 | mno
  6 | pqr
(6 rows)

この例では、PostgreSQLテーブルエンジンを使用してPostgreSQLとClickHouseの基本的な統合を示しました。 他の機能としてスキーマの指定、カラムのサブセットのみを返すこと、複数のレプリカへの接続については、PostgreSQLテーブルエンジンのドキュメントページをチェックしてください。また、ClickHouseとPostgreSQL - データの天国における出会い - パート1ブログもチェックしてください。

MaterializedPostgreSQLデータベースエンジンの使用​

Not supported in ClickHouse Cloud
Experimental feature. Learn more.

PostgreSQLデータベースエンジンは、PostgreSQLのレプリケーション機能を利用して、すべてのスキーマとテーブルまたはそのサブセットのデータベースのレプリカを作成します。このドキュメントでは、1つのデータベース、1つのスキーマ、および1つのテーブルを使用した基本的な統合方法を示します。

以下の手順では、PostgreSQL CLI (psql) と ClickHouse CLI (clickhouse-client) を使用します。PostgreSQLサーバーはLinuxにインストールされています。PostgreSQLデータベースが新しいテストインストールの場合は、以下の最小設定が必要です

1. PostgreSQLでの設定​

  1. postgresql.confで、最小のリスニングレベル、レプリケーションWALレベル、レプリケーションスロットを設定します。

次のエントリを追加します：

listen_addresses = '*'
max_replication_slots = 10
wal_level = logical

*ClickHouseは最低logical WALレベルおよび2つのレプリケーションスロットが必要です。

  1. 管理者アカウントを使用し、ClickHouseから接続するためのユーザーを作成します：
CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';

*デモ目的で、完全なスーパーユーザー権限が付与されました。

  1. 新しいデータベースを作成します：
CREATE DATABASE db1;
  1. psqlで新しいデータベースに接続します：
\connect db1
  1. 新しいテーブルを作成します：
CREATE TABLE table1 (
    id         integer primary key,
    column1    varchar(10)
);
  1. 初期行を追加します：
INSERT INTO table1
(id, column1)
VALUES
(1, 'abc'),
(2, 'def');
  1. PostgreSQLが新しいユーザーでレプリケーション用の新しいデータベースへの接続を許可するように構成します。以下はpg_hba.confファイルに追加する最小エントリです：

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    db1             clickhouse_user 192.168.1.0/24          password

*デモ目的で、クリアテキストパスワード認証メソッドを使用しています。アドレス行はPostgreSQLドキュメントに従って、サーバーのサブネットまたはアドレスで更新してください。

  1. 次のようにしてpg_hba.conf設定をリロードします（バージョンに応じて調整してください）：
/usr/pgsql-12/bin/pg_ctl reload
  1. 新しいclickhouse_userでログインをテストします：
 psql -U clickhouse_user -W -d db1 -h <your_postgresql_host>

2. ClickHouseでの設定​

  1. ClickHouse CLIにログインします：
clickhouse-client --user default --password ClickHouse123!
  1. データベースエンジンのPostgreSQL実験的機能を有効にします：
SET allow_experimental_database_materialized_postgresql=1
  1. レプリケーションされる新しいデータベースと初期テーブルを定義します：
CREATE DATABASE db1_postgres
ENGINE = MaterializedPostgreSQL('postgres-host.domain.com:5432', 'db1', 'clickhouse_user', 'ClickHouse_123')
SETTINGS materialized_postgresql_tables_list = 'table1';

最小オプションは以下の通りです：

parameterDescriptionexample
host:portホスト名またはIPとポートpostgres-host.domain.com:5432
databasePostgreSQLデータベース名db1
userPostgreSQLへの接続ユーザー名clickhouse_user
passwordPostgreSQLへの接続パスワードClickHouse_123
settingsエンジンに関する追加設定materialized_postgresql_tables_list = 'table1'
参考

PostgreSQLデータベースエンジンの完全なガイドについては、https://clickhouse.com/docs/engines/database-engines/materialized-postgresql/#settingsを参照してください

  1. 初期テーブルにデータがあるか確認します：
ch_env_2 :) select * from db1_postgres.table1;

SELECT *
FROM db1_postgres.table1

Query id: df2381ac-4e30-4535-b22e-8be3894aaafc

┌─id─┬─column1─┐
│  1 │ abc     │
└────┴─────────┘
┌─id─┬─column1─┐
│  2 │ def     │
└────┴─────────┘

3. 基本的なレプリケーションのテスト​

  1. PostgreSQLで新しい行を追加します：
INSERT INTO table1
(id, column1)
VALUES
(3, 'ghi'),
(4, 'jkl');
  1. ClickHouseで新しい行が見えることを確認します：
ch_env_2 :) select * from db1_postgres.table1;

SELECT *
FROM db1_postgres.table1

Query id: b0729816-3917-44d3-8d1a-fed912fb59ce

┌─id─┬─column1─┐
│  1 │ abc     │
└────┴─────────┘
┌─id─┬─column1─┐
│  4 │ jkl     │
└────┴─────────┘
┌─id─┬─column1─┐
│  3 │ ghi     │
└────┴─────────┘
┌─id─┬─column1─┐
│  2 │ def     │
└────┴─────────┘

4. まとめ​

この統合ガイドでは、テーブルを持つデータベースのレプリケーション方法に関する単純な例に焦点を当てましたが、データベース全体をレプリケートするか、新しいテーブルやスキーマを既存のレプリケーションに追加するなど、より高度なオプションも存在します。DDLコマンドはこのレプリケーションにはサポートされていませんが、エンジンは変更を検出し、構造変更が行われるとテーブルをリロードするように設定できます。

参考

高度なオプションに使用可能な機能については、リファレンスドキュメントを参照してください。

関連コンテンツ​