Connecting ClickHouse to WarehousePG

Connecting ClickHouse to WarehousePG#

ETLパイプラインやデータの複製を使用せずに、 ClickHouseからWarehousePGデータを照会し、WarehousePGにデータを書き込みます。 ClickHouseは、 PostgreSQL テーブルエンジンを使用してWarehousePGに接続します。これは、 WarehousePGテーブルをClickHouseテーブル定義にマップし、標準のPostgresワイヤプロトコルを介して読み取りおよび書き込みを処理します。

注釈

PostgreSQL エンジンはWarehousePGコーディネーターにのみ接続します。 WarehousePGの超並列処理MPPアーキテクチャを採用していないため、クエリはシングルノードのPostgresクエリとしてコーディネーターを介して実行されます。

ClickHouseからの接続を受け入れるようにWarehousePGを構成する#

  1. コーディネーターホストで、 $COORDINATOR_DATA_DIRECTORY/postgresql.conf のlisten_addresses が'*' またはネットワーク範囲に設定されていることを確認します。まだである場合は、更新します。

listen_addresses = *

  1. ClickHouseホストからの接続を許可するエントリーをpg_hba.conf に追加します。 <clickhouse-host-network> をClickHouseホストのネットワーク範囲に置き換えます。

echo "host    <database>    <user>    <clickhouse-host-network>/24    md5" >> $COORDINATOR_DATA_DIRECTORY/pg_hba.conf

  1. コーディネーター構成をリロードします。

gpstop -u

  1. ClickHouseホストから、WarehousePGコーディネーターに到達可能であることを確認します。

psql -h <coordinator-ip> -p <port> -d <database> -U <user>

ClickHouseでリンクテーブルを作成する#

PostgreSQL テーブルエンジンを使用して、WarehousePGテーブルをClickHouseテーブル定義にマップします。 ClickHouseテーブルに対するクエリーは、作成されると、リアルタイムでWarehousePGに直接送信され、ローカルにコピーまたはキャッシュされるデータはありません。

  1. WarehousePGで、ClickHouseに公開するテーブルを作成します。例

CREATE TABLE analytics_data (
    id         integer primary key,
    event_type varchar(50),
    event_time timestamp,
    value      numeric(12, 4)
);

INSERT INTO analytics_data (id, event_type, event_time, value)
VALUES
    (1, page_view, 2025-01-01 10:00:00, 1.0),
    (2, click,     2025-01-01 10:01:00, 2.5);

  1. ClickHouseサーバーから、clickhouse-client セッションを開きます。

clickhouse-client

  1. ENGINE = PostgreSQL を使用してWarehousePGテーブルにマッピングするClickHouseでデータベースとテーブルを作成します。

CREATE DATABASE whpg_connect;

CREATE TABLE whpg_connect.analytics_data
(
    id         UInt64,
    event_type String,
    event_time DateTime,
    value      Float64
)
ENGINE = PostgreSQL(
    <coordinator-ip>:<port>,  -- host:port of the WarehousePG coordinator
    <database>,               -- WarehousePG database name
    analytics_data,           -- table name in WarehousePG
    <user>,                   -- WarehousePG user
    <password>                -- password (stored encrypted in ClickHouse)
);

ENGINE = PostgreSQL 構文は、 host:port 、データベース、テーブル、ユーザー、パスワードの5つの位置パラメーターを受け入れます。オプションの6番目のパラメーターは、スキーマ名デフォルトpublic を指定します。 WarehousePGがtrust 認証で構成されている場合、パスワードに空の文字列'' を渡します。

  1. 定義を検査して、テーブルが正しくリンクされていることを確認します。

DESCRIBE TABLE whpg_connect.analytics_data;

または、 CREATE TABLE ステートメントを完全に表示します。

SHOW CREATE TABLE whpg_connect.analytics_data;

パスワードは出力に[HIDDEN] と表示されます。

ClickHouseからWarehousePGデータを読み取る#

ClickHouseテーブルを照会します。 ClickHouseはWarehousePGに接続し、 COPY ... TO STDOUT ステートメントを実行し、行指向ストリームを到着時に列指向形式に変換します。

SELECT * FROM whpg_connect.analytics_data;
__OUTPUT__
┌─id─┬─event_type─┬─────────event_time─┬─value─┐
│  1 │ page_view  │ 2025-01-01 10:00:00│   1   │
│  2 │ click      │ 2025-01-01 10:01:00│  2.5  │
└────┴────────────┴────────────────────┴───────┘

ClickHouseテーブルの作成後にWarehousePGに挿入されたデータは、次のSELECT にすぐに表示されます。 PostgreSQL エンジンを使用する場合、ClickHouseはWarehousePGデータをローカルにキャッシュしません。

ClickHouseからWarehousePGへのデータの書き込み#

ClickHouseテーブルでINSERT を使用して、WarehousePGにデータを書き込みます。 ClickHouseは、WarehousePG側で挿入をCOPY ... FROM STDIN ステートメントに変換します。

- - Run in ClickHouse
INSERT INTO whpg_connect.analytics_data (id, event_type, event_time, value)
VALUES (3, purchase, 2025-01-01 10:05:00, 99.99);

行がWarehousePGに表示されることを確認します。

- - Run in WarehousePG
SELECT * FROM analytics_data;

クエリフローの理解#

ClickHouseでEXPLAIN を使用して、システム間でデータがどのように移動するかを確認します。

EXPLAIN SELECT * FROM whpg_connect.analytics_data;
__OUTPUT__
┌─explain──────────────────────────────────────────────────────────────────────────────────────┐
│ Expression ((Project names + (Projection + Change column names to column identifiers)))       │
│   ReadFromPostgreSQL                                                                           │
└────────────────────────────────────────────────────────────────────────────────────────────────┘

ReadFromPostgreSQL ステップは、コーディネーターへのTCP接続を確立し、 SQLクエリーを送信し、受信行指向ストリームをClickHouseの列指向形式に変換します。データがExpression ステップに到達するまでに、データは既に列状形式になっています。

目次