Connecting ClickHouse to WarehousePG#

ETLパイプラインやデータの複製を使用せずに、 ClickHouseからWarehousePGデータを照会し、WarehousePGにデータを書き込みます。 ClickHouseは、3つのビルトインメカニズムを介して標準のPostgresワイヤプロトコルでWarehousePGに接続します。

テーブルファンクションを使用する — ClickHouseに永続オブジェクトを持たないアドホック、1回限りのクエリー。
テーブルエンジンを使用する — 名前付けテーブルとして一度定義された接続を使用した繰り返しアクセス。
データベースエンジンを使用する — WarehousePGデータベース全体を公開し、すべてのテーブルが自動的に照会可能になります。

注釈

3つのメカニズムはすべてWarehousePGコーディネーターにのみ接続し、WarehousePGの超並列処理MPPアーキテクチャには関与しません。クエリは、シングルノードのPostgresクエリとしてコーディネーターを介して実行されます。 SELECT および`INSERT` は、3つのメカニズムすべてでサポートされています。 UPDATE 、DELETE 、TRUNCATE 、およびDDL操作はサポートされていません。これらの変更はWarehousePGで直接行います。

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

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

listen_addresses = *

ClickHouseホストからの接続を許可するエントリーをpg_hba.conf に追加します。 ClickHouseクラスター内の各ノードは独立してコーディネーターに接続するため、エントリはすべてのノードアドレスをカバーする必要があります。 CIDRレンジは、すべてのノードが同じサブネット上にある場合に機能します。

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

<clickhouse-host-network> は、ClickHouseホストまたはサブネットのネットワークアドレスに置き換えます。

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

gpstop -u

ClickHouseノードからclickhouse-client セッションを開き、 WarehousePGコーディネーターに到達可能であることを確認します。

-- in ClickHouse
SELECT nspname
FROM postgresql(<coordinator-ip>:<port>, <database>, pg_namespace, <user>, <password>, pg_catalog)
LIMIT 1;

接続が成功すると、行が返されます。認証エラーは、ネットワークパスが開いていることを確認しますが、資格情報またはpg_hba.conf には注意が必要です。

テーブルファンクションを使用する#

postgresql テーブルファンクションを使用して、ClickHouseでオブジェクトを作成せずに、WarehousePGテーブルをインラインで照会します。各呼び出しは、接続を開き、ステートメントを実行し、それを閉じます。

ファンクションは、host:port 、データベース、テーブル、ユーザー、パスワードの5つの位置引数を取ります。オプションの6番目の引数はスキーマ名を指定し、デフォルトのpublic です。すべての引数は位置であるため、WarehousePGがtrust 認証を使用する場合でもパスワードを省略することはできません。その場合、空の文字列'' を渡します。

任意のノードでclickhouse-client セッションを開き、各クエリーにインラインで接続の詳細を渡します。

-- in ClickHouse
SELECT *
FROM postgresql(<coordinator-ip>:<port>, <database>, <table>, <user>, <password>)
ORDER BY id;

INSERT INTO TABLE FUNCTION を使用してWarehousePGに行を書き込みます。

-- in ClickHouse
INSERT INTO TABLE FUNCTION
  postgresql(<coordinator-ip>:<port>, <database>, <table>, <user>, <password>)
  (id, region, amount)
VALUES (100, new-row, 999);

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

PostgreSQL テーブルエンジンを使用して、WarehousePGテーブルをClickHouseの名前付けオブジェクトとして登録し、名前で照会します。クエリーはリアルタイムでWarehousePGに直接送信され、データはローカルにコピーまたはキャッシュされません。

リンクテーブルの作成#

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

-- in WarehousePG
CREATE TABLE analytics_data (
    id         integer,
    event_type varchar(50),
    event_time timestamp,
    value      numeric(12, 4)
) DISTRIBUTED BY (id);

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:05:00, 2.5);

ClickHouseノードからclickhouse-client セッションを開き、 ENGINE = PostgreSQL を使用してWarehousePGテーブルにマップされるテーブルを作成します。クラスターでは、ON CLUSTER <cluster-name> を使用してすべてのノードにテーブルを作成します。ここで、 <cluster-name> はクリックハウスクラスター名です SELECT DISTINCT cluster FROM system.clusters で確認してください。

-- in ClickHouse
CREATE TABLE whpg_analytics ON CLUSTER <cluster-name>
(
    id         Int32,
    event_type String,
    event_time DateTime,
    value      Decimal(12, 4)
)
ENGINE = PostgreSQL(
    <coordinator-ip>:<port>,
    <database>,
    analytics_data,
    <user>,
    <password>
);

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

clickhouse-client で、テーブルが予想される列タイプで作成されたことを確認します。

-- in ClickHouse
DESCRIBE TABLE whpg_analytics;

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

-- in ClickHouse
SHOW CREATE TABLE whpg_analytics;

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

データの読み取り#

ローカルテーブルと同様に、 ClickHouseテーブルを照会します。

- - in ClickHouse
SELECT * FROM whpg_analytics;
__OUTPUT__
┌─id─┬─event_type─┬─────────event_time─┬──────value─┐
│  1 │ page_view  │ 2025-01-01 10:00:00│     1.0000 │
│  2 │ click      │ 2025-01-01 10:05:00│     2.5000 │
└────┴────────────┴────────────────────┴────────────┘

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

データの書き込み#

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

- - in ClickHouse
INSERT INTO whpg_analytics (id, event_type, event_time, value)
VALUES (3, purchase, 2025-01-01 10:10:00, 99.99);

WarehousePGのテーブルを照会して、行が可視されていることを確認します。

- - in WarehousePG
SELECT * FROM analytics_data;

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

PostgreSQL データベースエンジンを使用して、ClickHouseでWarehousePGデータベース全体を公開します。個々のテーブル定義をせずに、すべてのテーブルが自動的にクエリ可能になります。

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

-- in ClickHouse
CREATE DATABASE whpg_db ON CLUSTER <cluster-name>  -- ClickHouse cluster name
  ENGINE = PostgreSQL(<coordinator-ip>:<port>, <database>, <user>, <password>);

使用可能なテーブルをリストします。

-- in ClickHouse
SHOW TABLES FROM whpg_db;

WarehousePG名でテーブルを直接照会します。

-- in ClickHouse
SELECT count() FROM whpg_db.analytics_data;

名前付けコレクションへの資格情報の保存#

接続資格情報を名前付けコレクションに保存し、クエリーおよびCREATE TABLE ステートメントにわたって名前で参照します。名前付けコレクションは、3つのメカニズムすべてで動作します。

次のコンテンツを使用して各サーバーノードに/etc/clickhouse-server/users.d/named_collection_control.xml を作成することにより、ClickHouseユーザーの名前付けコレクション管理を有効にします。

<clickhouse>
    <users>
        <default>
            <named_collection_control>1</named_collection_control>
        </default>
    </users>
</clickhouse>

各サーバーノードのclickhouse-client から、構成をリロードしますサーバーノードのみ、Keeperノードは含まれません。

-- in ClickHouse
SYSTEM RELOAD CONFIG;
SYSTEM RELOAD USERS;

ON CLUSTER <cluster-name> を使用して、すべてのサーバーノードで名前付けコレクションを作成します。

-- in ClickHouse
CREATE NAMED COLLECTION whpg ON CLUSTER <cluster-name> AS
  host=<coordinator-ip>, port=<port>, user=<user>,
  password=<password>, database=<database>;

3つのメカニズムのいずれかで名前で参照します。

-- in ClickHouse

-- Table function
SELECT * FROM postgresql(whpg, table = <table>);

-- Table engine
CREATE TABLE <table-name> (id Int32, event_type String, value Decimal(12, 4))
ENGINE = PostgreSQL(whpg, table = <table>);

-- Database engine
CREATE DATABASE whpg_db ON CLUSTER <cluster-name>
ENGINE = PostgreSQL(whpg);

クエリフローの理解#

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

- - in ClickHouse
EXPLAIN SELECT id, value FROM whpg_analytics WHERE id = 1;
__OUTPUT__
┌─explain──────────────────────────────────────────────────────────────────────────────────────┐
│ Expression ((Project names + (Projection + Change column names to column identifiers)))       │
│   ReadFromPostgreSQL                                                                           │
└────────────────────────────────────────────────────────────────────────────────────────────────┘

ReadFromPostgreSQL ステップは、コーディネーターへのTCP接続を確立し、 SQLクエリーを送信し、行指向ストリームをClickHouseの列指向形式に変換します。単純なフィルタと列の投影はWarehousePGにプッシュダウンされるため、一致する行と列のみがネットワークを介して移動します。各クエリーは新しい接続を開き、完了時にそれを閉じます。

Connecting ClickHouse to WarehousePG

目次