PostgreSQL

データモデリングや同等の概念に関するアドバイスを含む、PostgreSQL から ClickHouse への包括的な移行ガイドはこちらで確認できます。以下では、ClickHouse と PostgreSQL を接続する方法を説明します。

ClickHouse と PostgreSQL の接続

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

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

ヒント

当社の Managed Postgres サービスをご検討ください。コンピュートと物理的に同一場所にある NVMe ストレージを採用しており、EBS のようなネットワーク接続ストレージを使用する代替手段と比べて、ディスクボトルネックとなるワークロードで最大 10 倍の高いパフォーマンスを実現します。また、ClickPipes の Postgres CDC（変更データキャプチャ）コネクタを使用して、Postgres データを ClickHouse にレプリケートできます。

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 <your_postgresql_host>

注記

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 行が自動的に反映されているはずです。

  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 データベースエンジンの使用

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 <your_postgresql_host>

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	hostname or IP and port	postgres-host.domain.com:5432
database	PostgreSQL database name	db1
user	username to connect to postgres	clickhouse_user
password	password to connect to postgres	ClickHouse_123
settings	additional settings for the engine	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. まとめ​