EDB Docs - EDB Postgres AI for ClickHouse v26.3 - Connecting ClickHouse and WarehousePG

Connect ClickHouse and WarehousePG in either direction, without ETL pipelines or data duplication:

Connecting ClickHouse to WarehousePG — query and write WarehousePG data from ClickHouse using the PostgreSQL table function, table engine, or database engine.
Connecting WarehousePG to ClickHouse — read ClickHouse data from WarehousePG using PXF.

Connecting ClickHouse to WarehousePG

ClickHouse connects to WarehousePG over the standard Postgres wire protocol through three built-in mechanisms:

Table function — ad-hoc, one-off queries with no permanent object in ClickHouse.
Table engine — repeated access, with the connection defined once as a named table.
Database engine — exposing an entire WarehousePG database, with all tables becoming queryable automatically.

Note

All three mechanisms connect to the WarehousePG coordinator only and don't engage WarehousePG's massively parallel processing (MPP) architecture. Queries run through the coordinator as single-node Postgres queries. SELECT and INSERT are supported across all three mechanisms. UPDATE, DELETE, TRUNCATE, and DDL operations aren't supported. Make those changes directly in WarehousePG.

Configuring WarehousePG to accept connections from ClickHouse

On the coordinator host, confirm $COORDINATOR_DATA_DIRECTORY/postgresql.conf has listen_addresses set to '*' or your network range. If not, update it:
```
listen_addresses = '*'
```
Add an entry to pg_hba.conf that allows connections from your ClickHouse host or hosts. Each node in a ClickHouse cluster connects to the coordinator independently, so the entry must cover all node addresses. A CIDR range works if all nodes are on the same subnet:
```
echo "host    <database>    <user>    <clickhouse-host-network>/24    md5" >> $COORDINATOR_DATA_DIRECTORY/pg_hba.conf
```
Replace <clickhouse-host-network> with the network address of your ClickHouse host or subnet.
Reload the coordinator configuration:
```
gpstop -u
```
From any ClickHouse node, open a clickhouse-client session and verify the WarehousePG coordinator is reachable:
```
-- in ClickHouse
SELECT nspname
FROM postgresql('<coordinator-ip>:<port>', '<database>', 'pg_namespace', '<user>', '<password>', 'pg_catalog')
LIMIT 1;
```
A successful connection returns a row. An authentication error confirms the network path is open but credentials or pg_hba.conf need attention.

Using the table function

Use the postgresql table function to query a WarehousePG table inline, without creating any object in ClickHouse. Each call opens a connection, runs the statement, and closes it.

The function takes five positional arguments: host:port, database, table, user, and password. An optional sixth argument specifies the schema name and defaults to public. All arguments are positional, so you can't omit the password even if WarehousePG uses trust authentication. Pass an empty string '' in that case.

Open a clickhouse-client session on any node, then pass the connection details inline with each query:

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

Use INSERT INTO TABLE FUNCTION to write rows to WarehousePG:

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

Using the table engine

Use the PostgreSQL table engine to register a WarehousePG table as a named object in ClickHouse and query it by name. Queries go directly to WarehousePG in real time, with no data copied or cached locally.

Creating the linked table

In WarehousePG, create the table to expose to 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);

From any ClickHouse node, open a clickhouse-client session and create a table mapped to the WarehousePG table using ENGINE = PostgreSQL. On a cluster, use ON CLUSTER <cluster-name> to create the table on all nodes, where <cluster-name> is your ClickHouse cluster name (check with 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>'
);
```
The ENGINE = PostgreSQL syntax takes five positional arguments: host:port, database, table, user, and password. An optional sixth argument specifies the schema (defaults to public). If WarehousePG is configured with trust authentication, pass an empty string '' for the password.
In clickhouse-client, verify the table was created with the expected column types:
```
-- in ClickHouse
DESCRIBE TABLE whpg_analytics;
```
Or view the full CREATE TABLE statement:
```
-- in ClickHouse
SHOW CREATE TABLE whpg_analytics;
```
The password appears as [HIDDEN] in the output.

Reading data

Query the ClickHouse table as you would any local table:

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

Data inserted into WarehousePG after the ClickHouse table is created is immediately visible on the next SELECT. ClickHouse doesn't cache WarehousePG data locally when using the PostgreSQL engine.

Writing data

Use INSERT on the ClickHouse table to write data to WarehousePG. ClickHouse translates the insert into a COPY ... FROM STDIN statement on the WarehousePG side:

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

Confirm the row is visible by querying the table in WarehousePG:

-- in WarehousePG
SELECT * FROM analytics_data;

Using the database engine

Use the PostgreSQL database engine to expose an entire WarehousePG database in ClickHouse. Every table becomes queryable automatically without individual table definitions.

Create the database:

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

List the available tables:

-- in ClickHouse
SHOW TABLES FROM whpg_db;

Query any table directly by its WarehousePG name:

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

Storing credentials in a named collection

Store connection credentials once in a named collection and reference it by name across queries and CREATE TABLE statements. Named collections work with all three mechanisms.

Enable named collection management for your ClickHouse user by creating /etc/clickhouse-server/users.d/named_collection_control.xml on each server node with the following content:

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

From clickhouse-client on each server node, reload the configuration (server nodes only, not Keeper nodes):
```
-- in ClickHouse
SYSTEM RELOAD CONFIG;
SYSTEM RELOAD USERS;
```

Create the named collection on all server nodes using 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>';

Reference it by name in any of the three mechanisms:

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

Understanding the query flow

Use EXPLAIN to see how data moves between the systems:

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

The ReadFromPostgreSQL step establishes a TCP connection to the coordinator, sends the SQL query, and converts the row-oriented stream into ClickHouse's columnar format. Simple filters and column projections are pushed down to WarehousePG, so only the matching rows and columns travel over the network. Each query opens a fresh connection and closes it on completion.

Connecting WarehousePG to ClickHouse

Read ClickHouse data from WarehousePG using the WarehousePG Platform Extension Framework (PXF), reading in parallel across every WarehousePG segment. Query it either through a pxf:// external table or, on WarehousePG 7 and later, through a standard foreign table using pxf_fdw. Both options read through the same PXF server and offer identical parallelism, so the choice comes down to whether external tables or foreign tables fit your existing workflow better.

Preparing ClickHouse for incoming connections

Confirm ClickHouse listens on an address reachable from your WarehousePG hosts. By default, it listens only on 127.0.0.1. Add an override file at /etc/clickhouse-server/config.d/listen.xml on the ClickHouse node your PXF server connects to (just that one node, not the whole cluster):
```

<clickhouse>
    <listen_host>127.0.0.1</listen_host>
    <listen_host><clickhouse-private-ip></listen_host>
</clickhouse>
```
Keep 127.0.0.1 in the list to preserve local clickhouse-client access.
Restart ClickHouse to apply it:
```
sudo systemctl restart clickhouse-server
```
From a clickhouse-client session on that node, create a dedicated ClickHouse user for WarehousePG to connect with, and grant it read access to the database you want to expose. PXF's JDBC profile accepts only plaintext passwords, so create the user accordingly:
```
-- in ClickHouse
CREATE USER whpg_reader IDENTIFIED WITH plaintext_password BY '<password>';
GRANT SELECT, INSERT ON <database>.* TO whpg_reader;
```
Replace <database> with the ClickHouse database you want WarehousePG to read from and write to. Drop INSERT from the grant if you only need to read.
Make sure every WarehousePG host can resolve the ClickHouse hostname.

Configuring PXF's JDBC connection to ClickHouse

Use the WarehousePG Platform Extension Framework (PXF) with the Jdbc profile to read ClickHouse tables in parallel across all WarehousePG segments, through ClickHouse's HTTP interface.

Install and start PXF first if you haven't already. See Installing PXF for WarehousePG and Configuring and starting PXF for WarehousePG, including creating the pxf extension in the database you want to query from.

Run the following steps on the WHPG coordinator, as the gpadmin user. pxf cluster sync preserves file ownership when it copies the jar and server directory to every segment, so files owned by another user (for example, if you downloaded the jar as root) stay wrong on every segment too.

Download the ClickHouse JDBC driver's shaded jar and add it to PXF's library directory:

curl -fsSL \
  "https://repo1.maven.org/maven2/com/clickhouse/clickhouse-jdbc/0.9.0/clickhouse-jdbc-0.9.0-all.jar" \
  -o "$PXF_BASE/lib/clickhouse-jdbc-0.9.0-all.jar"

Create a directory for the new PXF server:
```
mkdir -p "$PXF_BASE/servers/clickhouse"
```

Add a jdbc-site.xml file pointing at ClickHouse's HTTP interface. Credentials go in separate properties, not the JDBC URL. A ?user= parameter in jdbc.url overrides jdbc.user, so leave the URL clean:

<!-- $PXF_BASE/servers/clickhouse/jdbc-site.xml -->
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <property>
        <name>jdbc.driver</name>
        <value>com.clickhouse.jdbc.ClickHouseDriver</value>
    </property>
    <property>
        <name>jdbc.url</name>
        <!-- Replace <clickhouse-host> and <database> -->
        <value>jdbc:clickhouse://<clickhouse-host>:8123/<database></value>
    </property>
    <property>
        <name>jdbc.user</name>
        <value>whpg_reader</value>
    </property>
    <property>
        <name>jdbc.password</name>
        <!-- Replace <password> -->
        <value><password></value>
    </property>
</configuration>

Sync the new jar and server configuration to every segment, then restart PXF to pick them up:
```
pxf cluster sync
pxf cluster restart
```

Querying ClickHouse

Once the server is configured, choose one of the following two options to query it:

Querying ClickHouse with a pxf:// external table works on any supported WarehousePG version.
Querying ClickHouse with pxf_fdw is available on WarehousePG 7 and later and queries through standard foreign table syntax instead.

Querying ClickHouse with a pxf:// external table

Create an external table over the ClickHouse table, then query it like any other WarehousePG table:

-- in WarehousePG
CREATE EXTERNAL TABLE ch_events_ext (
    id         text,
    event_type text,
    event_time timestamp,
    value      float8
)
LOCATION ('pxf://<database>.<table>?PROFILE=Jdbc&SERVER=clickhouse')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');

SELECT * FROM ch_events_ext;

Note

Leave out UNIQUE and PRIMARY KEY constraints. WarehousePG doesn't support them on external tables and rejects the CREATE EXTERNAL TABLE statement if they're present.

Write data back to ClickHouse with a separate writable external table at the same LOCATION, using FORMATTER='pxfwritable_export':

-- in WarehousePG
CREATE WRITABLE EXTERNAL TABLE ch_events_write_ext (
    id         text,
    event_type text,
    event_time timestamp,
    value      float8
)
LOCATION ('pxf://<database>.<table>?PROFILE=Jdbc&SERVER=clickhouse')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_export');

INSERT INTO ch_events_write_ext VALUES ('evt-1', 'purchase', now(), 99.99);

For general PXF and JDBC server configuration, including connection pooling and other JDBC settings, see Connecting to a SQL database over JDBC. For settings specific to the ClickHouse driver itself, see ClickHouse's JDBC driver documentation.

Querying ClickHouse with pxf_fdw

Query the same server through standard CREATE FOREIGN TABLE syntax instead, using the jdbc_pxf_fdw foreign data wrapper:

-- in WarehousePG
CREATE EXTENSION pxf_fdw;

CREATE SERVER clickhouse_svr
    FOREIGN DATA WRAPPER jdbc_pxf_fdw
    OPTIONS (config 'clickhouse');

CREATE USER MAPPING FOR CURRENT_USER SERVER clickhouse_svr;

CREATE FOREIGN TABLE ch_events_ft (
    id         text,
    event_type text,
    event_time timestamp,
    value      float8
)
SERVER clickhouse_svr
OPTIONS (resource '<database>.<table>');

SELECT * FROM ch_events_ft;

A pxf_fdw foreign table supports both reading and writing, unlike a pxf:// external table, which needs a separate READABLE/WRITABLE definition for each direction. Insert directly into the same foreign table:

-- in WarehousePG
INSERT INTO ch_events_ft VALUES ('evt-1', 'purchase', now(), 99.99);

See Querying data with the PXF foreign data wrapper for the full syntax, including per-user credentials in the user mapping and standard FDW grants.

Confirming parallel execution

Confirm parallel execution with EXPLAIN. Both interfaces read through the same PXF service, with each segment's PXF instance querying ClickHouse independently:

EXPLAIN SELECT * FROM ch_events_ext;

Output

                        QUERY PLAN
-----------------------------------------------------------
 Gather Motion 4:1  (slice1; segments: 4)
   ->  Foreign Scan on ch_events_ext
 Optimizer: Postgres query optimizer

A Foreign Scan under a Gather Motion with segments: <N> matching your WHPG segment count confirms every segment is reading its own share of the ClickHouse table in parallel, rather than one segment pulling all the data.

Troubleshooting

Connection refused from a segment host: confirm listen_host in ClickHouse's configuration covers the interface that segment reaches it on, and that the segment can resolve and reach the ClickHouse host over the network.
Authentication errors even with correct credentials: check jdbc.url for a ?user= or ?password= parameter. Either one overrides jdbc.user/jdbc.password from jdbc-site.xml and is a common source of confusing auth failures.
Write fails with a ClickHouse access-denied error: the ClickHouse user needs INSERT granted, not just SELECT. See Preparing ClickHouse for incoming connections.
pxf_fdw writes fail with a NullPointerException: upgrade the driver jar to 0.10.0 or later.
PXF stops responding after a large read: the PXF JVM can run out of heap and terminate itself rather than throttling. Run pxf restart on the affected host, or tune the JVM heap as described in Managing the PXF cluster if your ClickHouse tables are large.

Connecting ClickHouse and WarehousePG v26.3

Connecting ClickHouse to WarehousePG

Note

Configuring WarehousePG to accept connections from ClickHouse

Using the table function

Using the table engine

Creating the linked table

Reading data

Writing data

Using the database engine

Storing credentials in a named collection

Understanding the query flow

Connecting WarehousePG to ClickHouse

Preparing ClickHouse for incoming connections

Configuring PXF's JDBC connection to ClickHouse

Querying ClickHouse

Querying ClickHouse with a pxf:// external table

Note

Querying ClickHouse with pxf_fdw

Confirming parallel execution

Troubleshooting

← Prev

↑ Up

Next →