ClickHouse を PostgreSQL に接続する

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

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

Managed Postgres サービスもご覧ください。コンピュートと物理的に同一配置された NVMe storage を採用しており、EBS のようなネットワーク接続型ストレージを使用する代替手段と比べて、ディスク I/O がボトルネックになるワークロードで最大 10 倍高速なパフォーマンスを実現します。さらに、ClickPipes の Postgres CDC (変更データキャプチャ) コネクタを使用して、Postgres のデータを ClickHouse にレプリケートできます。

PostgreSQL テーブルエンジンを使用する

PostgreSQL table engineを使用すると、ClickHouse からリモートの PostgreSQL server に保存されているデータに対して、SELECT および INSERT 操作を実行できます。この記事では、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 ファイルに追加します。アドレス行は、お使いの PostgreSQL server のサブネットまたは 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 <your_postgresql_host>

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

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	ホスト名または IP アドレスとポート	postgres-host.domain.com:5432
database	PostgreSQL のデータベース名	db_in_psg
user	postgres に接続するためのユーザー名	clickhouse_user
password	postgres に接続するためのパスワード	ClickHouse_123

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

インテグレーションをテストする

ClickHouse で初期状態の行を確認します:

SELECT * FROM db_in_ch.table1

ClickHouse のテーブルには、PostgreSQL のテーブルにすでに存在していた 2 行が自動的に取り込まれるはずです:

Query 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 データベースエンジンを使用する

PostgreSQL データベースエンジンは、PostgreSQL のレプリケーション機能を使用して、すべてまたは一部のスキーマとテーブルを含むデータベースのレプリカを作成します。この記事では、1 つのデータベース、1 つのスキーマ、1 つのテーブルを使用したインテグレーションの基本的な方法を説明します。以下の手順では、PostgreSQL CLI (psql) と ClickHouse CLI (clickhouse-client) を使用します。PostgreSQL サーバーは Linux にインストールされているものとします。以下は、PostgreSQL データベースが新規のテストインストールである場合の最小構成です

PostgreSQL で

postgresql.conf で、最小の listen レベル、レプリケーション用の wal level、および replication slots を設定します。

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

listen_addresses = '*'
max_replication_slots = 10
wal_level = logical

*ClickHouse では、wal level が少なくとも 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 のドキュメントに従って、address 行をサブネットまたはサーバーのアドレスに更新してください

次のように pg_hba.conf の設定を再読み込みします (使用しているバージョンに合わせて調整してください) :

/usr/pgsql-12/bin/pg_ctl reload

新しいclickhouse_userでのログインをテストします:

 psql -U clickhouse_user -W -d db1 -h <your_postgresql_host>

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

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

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

まとめ

このインテグレーションガイドでは、テーブルを含むデータベースをレプリケートする方法のシンプルな例に焦点を当てましたが、データベース全体をレプリケートしたり、既存のレプリケーションに新しいテーブルやスキーマを追加したりする、より高度なオプションもあります。このレプリケーションでは DDL コマンドはサポートされていませんが、構造変更が行われた際に変更を検出してテーブルを再読み込みするようエンジンを設定できます。

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

BigQuery 向け ClickPipes

プログラムによるアクセス

その他

ClickHouse を PostgreSQL に接続する

PostgreSQL テーブルエンジンを使用する

MaterializedPostgreSQL データベースエンジンを使用する

​PostgreSQL テーブルエンジンを使用する

​MaterializedPostgreSQL データベースエンジンを使用する

PostgreSQL テーブルエンジンを使用する

MaterializedPostgreSQL データベースエンジンを使用する