ClickHouse と PostgreSQL の接続

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

PostgreSQL のテーブルから読み取るための PostgreSQL テーブルエンジンの利用
PostgreSQL 内のデータベースと ClickHouse 内のデータベースを同期するための、実験的な MaterializedPostgreSQL データベースエンジンの利用

ヒント

ClickPipes は、PeerDB を基盤とした ClickHouse Cloud 向けのマネージド連携サービスであり、こちらの利用を推奨します。また、代替手段として PeerDB は、セルフホスト型の ClickHouse および ClickHouse Cloud 双方への PostgreSQL データベースレプリケーション向けに特化して設計された、オープンソースの CDC（変更データキャプチャ）ツールとして利用できます。

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

PostgreSQL テーブルエンジンを使用すると、リモートの PostgreSQL サーバー上に保存されているデータに対して、ClickHouse から SELECT および INSERT 操作を行うことができます。この記事では、1 つのテーブルを使った基本的な連携方法を説明します。

1. PostgreSQL のセットアップ

postgresql.conf で、PostgreSQL がネットワークインターフェイスで待ち受けできるようにするため、次の設定を追加します。

listen_addresses = '*'

ClickHouse から接続するためのユーザーを作成します。デモンストレーション目的のため、この例ではスーパーユーザー権限をすべて付与します。

CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';

PostgreSQL で新しいデータベースを作成する:

CREATE DATABASE db_in_psg;

新しいテーブルを作成します：

CREATE TABLE table1 (
    id         integer primary key,
    column1    varchar(10)
);

テスト用にいくつか行を追加しましょう。

INSERT INTO table1
  (id, column1)
VALUES
  (1, 'abc'),
  (2, 'def');

レプリケーション用の新しいユーザーが新しいデータベースに接続できるように PostgreSQL を構成するには、pg_hba.conf ファイルに次のエントリを追加します。address 行のアドレスを、PostgreSQL サーバーのサブネットまたは IP アドレスに更新してください。

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

pg_hba.conf 設定ファイルを再読み込みします（利用しているバージョンに応じてこのコマンドを調整してください）:

/usr/pgsql-12/bin/pg_ctl reload

新しい clickhouse_user がログインできることを確認します。

psql -U clickhouse_user -W -d db_in_psg -h <PostgreSQLホスト>

注記

ClickHouse Cloud 上でこの機能を利用している場合、ClickHouse Cloud の IP アドレスから PostgreSQL インスタンスへのアクセスを許可する必要がある場合があります。外向きトラフィックの詳細については、ClickHouse の Cloud Endpoints API を確認してください。

2. ClickHouse にテーブルを定義する

clickhouse-client にログインします:

clickhouse-client --user default --password ClickHouse123!

新しいデータベースを作成します。

CREATE DATABASE db_in_ch;

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

必要となる最小限のパラメータは次のとおりです:

parameter	Description	example
host:port	hostname or IP and port	postgres-host.domain.com:5432
database	PostgreSQL database name	db_in_psg
user	username to connect to postgres	clickhouse_user
password	password to connect to postgres	ClickHouse_123

注記

利用可能なパラメータの完全な一覧については、PostgreSQL table engine のドキュメントページを参照してください。

3 統合をテストする

ClickHouse で初期の行を表示します:

SELECT * FROM db_in_ch.table1

ClickHouse のテーブルには、PostgreSQL のテーブル内に既に存在していた 2 行が自動的に格納されているはずです。

クエリID: 34193d31-fe21-44ac-a182-36aaefbd78bf

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

PostgreSQL に戻り、テーブルにいくつか行を追加します：

INSERT INTO table1
  (id, column1)
VALUES
  (3, 'ghi'),
  (4, 'jkl');

その 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     │
└────┴─────────┘

ClickHouse テーブルに行を追加したときにどうなるか確認してみましょう。

INSERT INTO db_in_ch.table1
  (id, column1)
VALUES
  (5, 'mno'),
  (6, 'pqr');

ClickHouse に追加された行が PostgreSQL のテーブルに表示されているはずです。

db_in_psg=# SELECT * FROM table1;
id | column1
----+---------
  1 | abc
  2 | def
  3 | ghi
  4 | jkl
  5 | mno
  6 | pqr
(6 rows)

この例では、PostrgeSQL テーブルエンジンを使用して、PostgreSQL と ClickHouse の間の基本的な連携方法を示しました。スキーマの指定、特定のカラムのみを返す設定、複数レプリカへの接続など、さらに多くの機能については、PostgreSQL テーブルエンジンのドキュメントページを参照してください。また、ブログ記事 ClickHouse and PostgreSQL - a match made in data heaven - part 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 側の設定

postgresql.conf で、最低限の listen 設定、レプリケーション用の wal_level、レプリケーションスロットを設定します:

次の設定項目を追加します:

listen_addresses = '*'
max_replication_slots = 10
wal_level = logical

*ClickHouse には、WAL レベルとして logical 以上と、少なくとも 2 個のレプリケーションスロットが必要です

管理者アカウントで、ClickHouse から接続するためのユーザーを作成します。

CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';

*デモ目的のために、完全なスーパーユーザー権限が付与されています。

新しいデータベースを作成します:

CREATE DATABASE db1;

psql で新しいデータベースに接続します：

\connect db1

新しいテーブルを作成します：

CREATE TABLE table1 (
    id         integer primary key,
    column1    varchar(10)
);

初期データ行を追加します:

INSERT INTO table1
(id, column1)
VALUES
(1, 'abc'),
(2, 'def');

レプリケーション用に、新しいユーザーが新しいデータベースへ接続できるよう PostgreSQL を設定します。以下は pg_hba.conf ファイルに追加する最小限のエントリです。

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

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

pg_hba.conf 設定を次のようなコマンドで再読み込みします（お使いのバージョンに合わせて調整してください）:

/usr/pgsql-12/bin/pg_ctl reload

新しい clickhouse_user でログインできるかテストします。

 psql -U clickhouse_user -W -d db1 -h <PostgreSQLホスト>

2. ClickHouse で

ClickHouse CLI にログインする

clickhouse-client --user default --password ClickHouse123!

データベースエンジン用の PostgreSQL 実験的機能を有効にします：

SET allow_experimental_database_materialized_postgresql=1

レプリケーション用の新しいデータベースを作成し、初期テーブルを定義します：

CREATE DATABASE db1_postgres
ENGINE = MaterializedPostgreSQL('postgres-host.domain.com:5432', 'db1', 'clickhouse_user', 'ClickHouse_123')
SETTINGS materialized_postgresql_tables_list = 'table1';

最小限のオプション:

parameter	Description	example
host:port	ホスト名または IP アドレスとポート	postgres-host.domain.com:5432
database	PostgreSQL のデータベース名	db1
user	PostgreSQL に接続するためのユーザー名	clickhouse_user
password	PostgreSQL に接続するためのパスワード	ClickHouse_123
settings	エンジン向けの追加設定	materialized_postgresql_tables_list = 'table1'

参考文献

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

初期テーブルにデータが入っていることを確認します:

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. 基本的なレプリケーションをテストする

PostgreSQL に新しい行を追加します：

INSERT INTO table1
(id, column1)
VALUES
(3, 'ghi'),
(4, 'jkl');

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

参考文献

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

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

1. PostgreSQL のセットアップ​

2. ClickHouse にテーブルを定義する​

3 統合をテストする​

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

1. PostgreSQL 側の設定​

2. ClickHouse で​

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

4. まとめ​