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

PostgreSQL

PostgreSQLからClickHouseへの完全な移行ガイド、データモデリングや同等の概念に関するアドバイスを含む内容は、こちらにあります。以下はClickHouseとPostgreSQLを接続する方法について説明します。

ClickHouseとPostgreSQLの接続

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

  • PostgreSQLテーブルエンジンを使用して、PostgreSQLテーブルから読み取る
  • 実験的なMaterializedPostgreSQLデータベースエンジンを使用して、PostgreSQLのデータベースをClickHouseのデータベースと同期する
ヒント

ClickPipesを使用することをお勧めします。これは、PeerDBによって支援されたClickHouse Cloud向けの管理統合サービスです。別の選択肢として、PostgreSQLデータベースのレプリケーションに特化したオープンソースのCDCツールであるPeerDBも利用可能です。

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にインストールされています。以下は新しいテストインストールの最小設定です

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コマンドはサポートされていませんが、エンジンを変更を検出し、構造的な変更が行われた場合にテーブルを再読み込みするように設定できます。

参考

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