English

PXF Access to ClickHouse

1. Environment Configuration

Software	Version Information
YMatrix	MatrixDB 6.7.1+enterprise
ClickHouse	26.2.5.45
PXF	pxf-matrixdb4-6.5.0-1.el7.x86_64

2. Usage Examples

1. Prerequisites

YMatrix must have PXF configured and initialized, with the PXF service running on every segment host. If PXF is not yet configured, refer to PXF Installation for initialization instructions.

Download the ClickHouse driver corresponding to your version. You may download it via wget or upload it offline.
Initialize PXF:
```
pxf cluster init
```

Copy the JDBC driver to the PXF library directory on all nodes:

mv clickhouse-jdbc-0.4.6-shaded.jar /usr/local/pxf-matrixdb4/lib

2. Access Test

2.1 Create Database and Table in ClickHouse

Create a database:
```
CREATE DATABASE IF NOT EXISTS anadw;
```

Create a table:

CREATE TABLE IF NOT EXISTS anadw.test_table
(
  esn String,
  trace_datetime String,
  trace_timestamp Int64,
  latitude Float64,
  longitude Float64,
  dt String
)
ENGINE = MergeTree
ORDER BY (dt, esn, trace_timestamp);

2.2 Configure Environment in YMatrix

Create a server:

CREATE SERVER clickhouse_server
FOREIGN DATA WRAPPER jdbc_pxf_fdw
OPTIONS (
  db_url 'jdbc:clickhouse://172.16.100.29:8123/anadw?compress=0&decompress=0',
  jdbc_driver 'com.clickhouse.jdbc.ClickHouseDriver'
);

Create a user mapping:

CREATE USER MAPPING FOR mxadmin
SERVER clickhouse_server
OPTIONS (
  "user" 'pxf_user',
  password ''
);

Create a foreign table:

CREATE FOREIGN TABLE dm.test_table (
  esn text,
  trace_datetime text,
  trace_timestamp bigint,
  latitude double precision,
  longitude double precision,
  dt text
)
SERVER clickhouse_server
OPTIONS (
  resource 'anadw.test_table'
);