This is an automated email from the ASF dual-hosted git repository. tuhaihe pushed a commit to branch REL_2_STABLE in repository https://gitbox.apache.org/repos/asf/cloudberry-pxf.git
commit 0b447bec084e336693d11af3416167af5cce8e7c Author: Nikolay Antonov <[email protected]> AuthorDate: Thu Apr 9 19:09:06 2026 +0500 Testcontainers: Add ClickHouse JDBC test (#97) ### Add ClickHouse JDBC tests Add new tests to cover Cloudberry -> PXF -> JDBC path. In this test we are verifying that main data Cloudberry and ClickHouse types can be converted back and forth. Test covers ClickHouse 24.x and 26.x versions. Open-source ClickHouse has short support lifetime. I have tested following clickhouse-jdbc drivers: * 0.6.x (`jdbc-v1`) - works well with old ClickHouse versions. In ClickHouse 25.10 `jdbc-v1` got broken https://github.com/ClickHouse/clickhouse-java/issues/2636 and all queries raised an "Magic is not correct" error message. * 0.9.4 - fixes "Magic" issues, however has issue with String <-> bytea conversion `ERROR: PXF server error : Method: getBytes("bin") encountered an exception.` * 0.9.7+ - works well ### Add jdbc-pxf-drivers project Add new `jdbc-pxf-drivers` project to server. It excluded from default DEB-package build. Explicit actions required to install jdbc-drivers. ### Side quest Cloudberry FDW serializes rows as Greenplum CSV => PXF `TextRecordReader` parses the CSV stream via univocity CSV parser => for `BYTEA` columns it calls `pgUtilities.parseByteaLiteral()` => which returns a `ByteBuffer`. --- automation/pom.xml | 7 + .../clickhouse/read_types/expected/query01.ans | 46 +++ .../jdbc/clickhouse/read_types/sql/query01.sql | 37 +++ .../clickhouse/write_types/expected/query01.ans | 73 +++++ .../jdbc/clickhouse/write_types/sql/query01.sql | 67 +++++ .../testcontainers/ClickHouseContainer.java | 83 ++++++ .../testcontainers/pxf-cbdb/script/build_pxf.sh | 3 + .../features/jdbc/JdbcClickhouseTest.java | 322 +++++++++++++++++++++ .../pxf/automation/features/jdbc/JdbcHiveTest.java | 3 - .../pxf/automation/features/jdbc/JdbcTest.java | 11 - docs/content/jdbc_pxf.html.md.erb | 1 + docs/content/jdbc_pxf_clickhouse.html.md.erb | 143 +++++++++ server/Makefile | 19 +- server/build.gradle | 11 +- .../build.gradle} | 17 +- .../cloudberry/pxf/plugins/jdbc/JdbcResolver.java | 20 +- server/settings.gradle | 1 + 17 files changed, 836 insertions(+), 28 deletions(-) diff --git a/automation/pom.xml b/automation/pom.xml index 0c11adb0..d9632ef7 100644 --- a/automation/pom.xml +++ b/automation/pom.xml @@ -251,6 +251,13 @@ <version>42.7.2</version> </dependency> + <dependency> + <groupId>com.clickhouse</groupId> + <artifactId>clickhouse-jdbc</artifactId> + <version>0.9.8</version> + <classifier>all</classifier> + </dependency> + <dependency> <groupId>org.springframework</groupId> <artifactId>spring</artifactId> diff --git a/automation/sqlrepo/features/jdbc/clickhouse/read_types/expected/query01.ans b/automation/sqlrepo/features/jdbc/clickhouse/read_types/expected/query01.ans new file mode 100644 index 00000000..2dcae5d4 --- /dev/null +++ b/automation/sqlrepo/features/jdbc/clickhouse/read_types/expected/query01.ans @@ -0,0 +1,46 @@ +-- start_ignore +-- end_ignore +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, +-- software distributed under the License is distributed on an +-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +-- KIND, either express or implied. See the License for the +-- specific language governing permissions and limitations +-- under the License. + +-- @description ClickHouse JDBC read: expect one row of primitive types from PXF (values compared by regress output) +SET timezone='utc'; +SET + +SET bytea_output='hex'; +SET + +SELECT + i_int, + s_small, + b_big, + f_float32, + d_float64, + b_bool, + dec, + t_text, + bin, + d_date, + d_ts, + d_tstz, + d_uuid +FROM pxf_ch_clickhouse_read_types +LIMIT 1; + i_int | s_small | b_big | f_float32 | d_float64 | b_bool | dec | t_text | bin | d_date | d_ts | d_tstz | d_uuid +-------+---------+-------+-----------+-----------+--------+-------------------+--------+------------+------------+-----------------------+-----------------------+-------------------------------------- + 1 | 2 | 3 | 1.25 | 3.1415926 | t | 12345.6789012345 | hello | \x41424344 | 2020-01-02 | 2020-01-02 03:04:05.006 | 2020-01-02 03:04:05.006+00 | 550e8400-e29b-41d4-a716-446655440000 +(1 row) diff --git a/automation/sqlrepo/features/jdbc/clickhouse/read_types/sql/query01.sql b/automation/sqlrepo/features/jdbc/clickhouse/read_types/sql/query01.sql new file mode 100644 index 00000000..863f1d6b --- /dev/null +++ b/automation/sqlrepo/features/jdbc/clickhouse/read_types/sql/query01.sql @@ -0,0 +1,37 @@ +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, +-- software distributed under the License is distributed on an +-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +-- KIND, either express or implied. See the License for the +-- specific language governing permissions and limitations +-- under the License. + +-- @description ClickHouse JDBC read: expect one row of primitive types from PXF (values compared by regress output) +SET timezone='utc'; +SET bytea_output='hex'; + +SELECT + i_int, + s_small, + b_big, + f_float32, + d_float64, + b_bool, + dec, + t_text, + bin, + d_date, + d_ts, + d_tstz, + d_uuid +FROM pxf_ch_clickhouse_read_types + LIMIT 1; \ No newline at end of file diff --git a/automation/sqlrepo/features/jdbc/clickhouse/write_types/expected/query01.ans b/automation/sqlrepo/features/jdbc/clickhouse/write_types/expected/query01.ans new file mode 100644 index 00000000..27664b27 --- /dev/null +++ b/automation/sqlrepo/features/jdbc/clickhouse/write_types/expected/query01.ans @@ -0,0 +1,73 @@ +-- start_ignore +-- end_ignore +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, +-- software distributed under the License is distributed on an +-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +-- KIND, either express or implied. See the License for the +-- specific language governing permissions and limitations +-- under the License. +-- @description ClickHouse JDBC write: insert full row then verify via readable external table +SET timezone='utc'; +SET +SET bytea_output='hex'; +SET +INSERT INTO pxf_ch_clickhouse_write_types ( + i_int, + s_small, + b_big, + f_float32, + d_float64, + b_bool, + dec, + t_text, + bin, + d_date, + d_ts, + d_tstz, + d_uuid +) VALUES ( + 1, + 2, + 3, + 1.25, + 3.1415926, + true, + CAST('12345.6789012345' AS numeric), + 'hello', + decode('41424344', 'hex'), + DATE '2020-01-02', + TIMESTAMP '2020-01-02 03:04:05.006', + TIMESTAMPTZ '2020-01-02 03:04:05.006+00', + '550e8400-e29b-41d4-a716-446655440000'::uuid +); +INSERT 0 1 +SELECT + i_int, + s_small, + b_big, + f_float32, + d_float64, + b_bool, + dec, + t_text, + bin, + d_date, + d_ts, + d_tstz, + d_uuid +FROM pxf_ch_clickhouse_write_verify +LIMIT 1; + i_int | s_small | b_big | f_float32 | d_float64 | b_bool | dec | t_text | bin | d_date | d_ts | d_tstz | d_uuid +-------+---------+-------+-----------+-----------+--------+-------------------+--------+------------+------------+-----------------------+-----------------------+-------------------------------------- + 1 | 2 | 3 | 1.25 | 3.1415926 | t | 12345.6789012345 | hello | \x41424344 | 2020-01-02 | 2020-01-02 03:04:05.006 | 2020-01-02 03:04:05.006+00 | 550e8400-e29b-41d4-a716-446655440000 +(1 row) diff --git a/automation/sqlrepo/features/jdbc/clickhouse/write_types/sql/query01.sql b/automation/sqlrepo/features/jdbc/clickhouse/write_types/sql/query01.sql new file mode 100644 index 00000000..4e3d146b --- /dev/null +++ b/automation/sqlrepo/features/jdbc/clickhouse/write_types/sql/query01.sql @@ -0,0 +1,67 @@ +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, +-- software distributed under the License is distributed on an +-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +-- KIND, either express or implied. See the License for the +-- specific language governing permissions and limitations +-- under the License. + +-- @description ClickHouse JDBC write: insert full row then verify via readable external table +SET timezone='utc'; +SET bytea_output='hex'; + +INSERT INTO pxf_ch_clickhouse_write_types ( + i_int, + s_small, + b_big, + f_float32, + d_float64, + b_bool, + dec, + t_text, + bin, + d_date, + d_ts, + d_tstz, + d_uuid +) VALUES ( + 1, + 2, + 3, + 1.25, + 3.1415926, + true, + CAST('12345.6789012345' AS numeric), + 'hello', + decode('41424344', 'hex'), + DATE '2020-01-02', + TIMESTAMP '2020-01-02 03:04:05.006', + TIMESTAMPTZ '2020-01-02 03:04:05.006+00', + '550e8400-e29b-41d4-a716-446655440000'::uuid + ); + +SELECT + i_int, + s_small, + b_big, + f_float32, + d_float64, + b_bool, + dec, + t_text, + bin, + d_date, + d_ts, + d_tstz, + d_uuid +FROM pxf_ch_clickhouse_write_verify + LIMIT 1; \ No newline at end of file diff --git a/automation/src/main/java/org/apache/cloudberry/pxf/automation/testcontainers/ClickHouseContainer.java b/automation/src/main/java/org/apache/cloudberry/pxf/automation/testcontainers/ClickHouseContainer.java new file mode 100644 index 00000000..ecccce84 --- /dev/null +++ b/automation/src/main/java/org/apache/cloudberry/pxf/automation/testcontainers/ClickHouseContainer.java @@ -0,0 +1,83 @@ +package org.apache.cloudberry.pxf.automation.testcontainers; + +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +import org.testcontainers.containers.GenericContainer; +import org.testcontainers.containers.Network; +import org.testcontainers.containers.wait.strategy.Wait; +import org.testcontainers.utility.DockerImageName; + +/** + * TestContainers wrapper around ClickHouse server. + * + * The container joins a shared Docker network with alias `clickhouse`, + * so PXF inside the Cloudberry container can reach it at `clickhouse:8123` (HTTP). + * + */ +public class ClickHouseContainer extends GenericContainer<ClickHouseContainer> { + + public static final int HTTP_PORT = 8123; + + private static final String DEFAULT_IMAGE = "clickhouse/clickhouse-server"; + private static final String NETWORK_ALIAS_PREFIX = "clickhouse-"; + + /** + * Credentials for the test container. ClickHouse 24+ restricts network access for `default` + * until a password is set; `CLICKHOUSE_PASSWORD` configures the server and JDBC must match. + */ + public static final String CLICKHOUSE_USER = "default"; + public static final String CLICKHOUSE_PASSWORD = "pxf-test"; + + private final String networkAlias; + + public ClickHouseContainer(String tag, Network network) { + super(DockerImageName.parse(DEFAULT_IMAGE + ":" + tag)); + + // generate unique DNS name for this Clickhouse container: + this.networkAlias = NETWORK_ALIAS_PREFIX + tag.replaceAll("[-.]", ""); + + super.withNetwork(network) + .withNetworkAliases(this.networkAlias) + .withExposedPorts(HTTP_PORT) + .withEnv("CLICKHOUSE_USER", CLICKHOUSE_USER) + .withEnv("CLICKHOUSE_PASSWORD", CLICKHOUSE_PASSWORD) + .waitingFor(Wait.forHttp("/ping").forPort(HTTP_PORT)); + } + + /** Embedded DNS name of this container on the Testcontainers network (for JDBC from other containers). */ + public String getNetworkAlias() { + return networkAlias; + } + + /** JDBC URL over HTTP, reachable from the host (mapped `HTTP_PORT`). */ + public String getJdbcUrl() { + return "jdbc:clickhouse://localhost:" + getMappedPort(HTTP_PORT) + "/default"; + } + + /** JDBC URL over HTTP for PXF / other containers on the same Docker network. */ + public String getInternalJdbcUrl() { + return "jdbc:clickhouse://" + networkAlias + ":" + HTTP_PORT + "/default"; + } + + public int getHttpMappedPort() { + return getMappedPort(HTTP_PORT); + } + +} \ No newline at end of file diff --git a/automation/src/main/resources/testcontainers/pxf-cbdb/script/build_pxf.sh b/automation/src/main/resources/testcontainers/pxf-cbdb/script/build_pxf.sh index 923775dd..bc7bbc22 100755 --- a/automation/src/main/resources/testcontainers/pxf-cbdb/script/build_pxf.sh +++ b/automation/src/main/resources/testcontainers/pxf-cbdb/script/build_pxf.sh @@ -56,6 +56,7 @@ cd /home/gpadmin/workspace/cloudberry-pxf make -C external-table install make -C fdw install make -C server install-server +make -C server install-jdbc-drivers # Set up PXF environment export PXF_BASE=$HOME/pxf-base @@ -65,6 +66,8 @@ mkdir -p "$PXF_BASE" # Initialize PXF pxf prepare +cp $PXF_HOME/lib/*.jar $PXF_BASE/lib/ +ls -l $PXF_BASE/lib/ pxf start # Verify PXF is running diff --git a/automation/src/test/java/org/apache/cloudberry/pxf/automation/features/jdbc/JdbcClickhouseTest.java b/automation/src/test/java/org/apache/cloudberry/pxf/automation/features/jdbc/JdbcClickhouseTest.java new file mode 100644 index 00000000..34302432 --- /dev/null +++ b/automation/src/test/java/org/apache/cloudberry/pxf/automation/features/jdbc/JdbcClickhouseTest.java @@ -0,0 +1,322 @@ +package org.apache.cloudberry.pxf.automation.features.jdbc; + +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +import annotations.WorksWithFDW; +import org.apache.cloudberry.pxf.automation.AbstractTestcontainersTest; +import org.apache.cloudberry.pxf.automation.structures.tables.pxf.ExternalTable; +import org.apache.cloudberry.pxf.automation.structures.tables.utils.TableFactory; +import org.apache.cloudberry.pxf.automation.testcontainers.ClickHouseContainer; +import org.testng.Assert; +import org.testng.annotations.DataProvider; +import org.testng.annotations.Factory; +import org.testng.annotations.Test; + +import java.math.BigDecimal; +import java.sql.Connection; +import java.sql.Date; +import java.sql.DriverManager; +import java.sql.PreparedStatement; +import java.sql.SQLException; +import java.sql.Statement; +import java.sql.Timestamp; +import java.util.Calendar; +import java.util.Properties; +import java.util.TimeZone; +import java.util.UUID; + +@WorksWithFDW +public class JdbcClickhouseTest extends AbstractTestcontainersTest { + + private static final String CLICKHOUSE_DRIVER = "com.clickhouse.jdbc.ClickHouseDriver"; + + private static final String CLICKHOUSE_TABLE_READ = "pxf_types_read"; + private static final String CLICKHOUSE_TABLE_WRITE = "pxf_types_write"; + + // PXF protocol compression options passed via LOCATION user parameters. + private static final String PROTOCOL_COMPRESS_ENABLED = "compress=true"; + private static final String PROTOCOL_COMPRESS_ALGORITHM_LZ4 = "compress_algorithm=lz4"; + private static final String HTTP_CONNECTION_PROVIDER_APACHE = "http_connection_provider=APACHE_HTTP_CLIENT"; + + /** PXF external/foreign table column definitions — same for read and write tests. */ + private static final String[] CLICKHOUSE_PXF_FIELDS = new String[] { + "i_int int", + "s_small smallint", + "b_big bigint", + "f_float32 real", + "d_float64 double precision", + "b_bool boolean", + "dec numeric", + "t_text text", + "bin bytea", + "d_date date", + "d_ts timestamp", + "d_tstz timestamp with time zone", + "d_uuid uuid" + }; + + private static final int V_I_INT = 1; + private static final short V_S_SMALL = 2; + private static final long V_B_BIG = 3L; + private static final float V_F_FLOAT32 = 1.25f; + private static final double V_D_FLOAT64 = 3.1415926d; + private static final boolean V_B_BOOL = true; + private static final String V_DEC_TEXT = "12345.6789012345"; + private static final String V_T_TEXT = "hello"; + private static final String V_D_DATE = "2020-01-02"; + private static final String V_D_TS = "2020-01-02 03:04:05.006"; + private static final String V_D_UUID = "550e8400-e29b-41d4-a716-446655440000"; + private final String dockerImageTag; + + private ClickHouseContainer clickhouseContainer; + + /** + * TestNG {@link Factory}: one test class instance per {@link #clickhouseVersions()} row (separate ClickHouse container). + */ + @Factory(dataProvider = "clickhouseVersions") + public static Object[] createInstances(String imageTag) { + return new Object[] { new JdbcClickhouseTest(imageTag) }; + } + + /** Docker image tags for `clickhouse/clickhouse-server` — same regress SQL for both. */ + @DataProvider(name = "clickhouseVersions") + public static Object[][] clickhouseVersions() { + return new Object[][] { + // Test PXF & ClickHouse with old database versions + { "24" }, + // ClickHouse 25.10+ doesn't work with old JDBC drivers + // https://github.com/ClickHouse/clickhouse-java/issues/2636 + { "26.2" }, + }; + } + + private JdbcClickhouseTest(String dockerImageTag) { + this.dockerImageTag = dockerImageTag; + } + + @Override + public void beforeClass() throws Exception { + clickhouseContainer = new ClickHouseContainer(dockerImageTag, container.getSharedNetwork()); + clickhouseContainer.start(); + + Assert.assertTrue(container.isRunning(), "PXFCloudberry container should be running"); + Assert.assertTrue(clickhouseContainer.isRunning(), "ClickHouse container should be running"); + } + + @Override + public void afterClass() throws Exception { + if (clickhouseContainer != null) { + clickhouseContainer.stop(); + } + } + + @Test(groups = {"testcontainers", "pxf-jdbc"}) + public void readSupportedTypes() throws Exception { + runReadSupportedTypes(clickhouseContainer.getInternalJdbcUrl(), clickhouseContainer.getJdbcUrl(), false); + } + + @Test(groups = {"testcontainers", "pxf-jdbc"}) + public void readSupportedTypesWithProtocolCompression() throws Exception { + runReadSupportedTypes(clickhouseContainer.getInternalJdbcUrl(), clickhouseContainer.getJdbcUrl(), true); + } + + @Test(groups = {"testcontainers", "pxf-jdbc"}) + public void readSupportedTypesWithHttpConnectionProvider() throws Exception { + runReadSupportedTypes(clickhouseContainer.getInternalJdbcUrl(), clickhouseContainer.getJdbcUrl(), false, true); + } + + @Test(groups = {"testcontainers", "pxf-jdbc"}) + public void writeSupportedTypes() throws Exception { + runWriteSupportedTypes(clickhouseContainer.getInternalJdbcUrl(), clickhouseContainer.getJdbcUrl(), false); + } + + @Test(groups = {"testcontainers", "pxf-jdbc"}) + public void writeSupportedTypesWithProtocolCompression() throws Exception { + runWriteSupportedTypes(clickhouseContainer.getInternalJdbcUrl(), clickhouseContainer.getJdbcUrl(), true); + } + + @Test(groups = {"testcontainers", "pxf-jdbc"}) + public void writeSupportedTypesWithHttpConnectionProvider() throws Exception { + runWriteSupportedTypes(clickhouseContainer.getInternalJdbcUrl(), clickhouseContainer.getJdbcUrl(), false, true); + } + + @Test(groups = {"testcontainers", "pxf-jdbc"}) + public void writeSupportedTypesWithHttpConnectionProviderAndCompression() throws Exception { + runWriteSupportedTypes(clickhouseContainer.getInternalJdbcUrl(), clickhouseContainer.getJdbcUrl(), true, true); + } + + private void runReadSupportedTypes(String internalJdbcUrl, String externalJdbcUrl, boolean enableProtocolCompression) throws Exception { + runReadSupportedTypes(internalJdbcUrl, externalJdbcUrl, enableProtocolCompression, false); + } + + private void runReadSupportedTypes(String internalJdbcUrl, String externalJdbcUrl, boolean enableProtocolCompression, boolean enableHttpConnectionProvider) throws Exception { + createAndSeedClickhouseReadTable(externalJdbcUrl); + + ExternalTable pxfRead = TableFactory.getPxfJdbcReadableTable( + "pxf_ch_clickhouse_read_types", + CLICKHOUSE_PXF_FIELDS, + CLICKHOUSE_TABLE_READ, + CLICKHOUSE_DRIVER, + internalJdbcUrl, + ClickHouseContainer.CLICKHOUSE_USER, + "PASS=" + ClickHouseContainer.CLICKHOUSE_PASSWORD); + pxfRead.setHost(pxfHost); + pxfRead.setPort(pxfPort); + if (enableProtocolCompression) { + pxfRead.addUserParameter(PROTOCOL_COMPRESS_ENABLED); + pxfRead.addUserParameter(PROTOCOL_COMPRESS_ALGORITHM_LZ4); + } + if (enableHttpConnectionProvider) { + pxfRead.addUserParameter(HTTP_CONNECTION_PROVIDER_APACHE); + } + cloudberry.createTableAndVerify(pxfRead); + + try { + regress.runSqlTest("features/jdbc/clickhouse/read_types"); + } finally { + cloudberry.dropTable(pxfRead, true); + } + } + + private void runWriteSupportedTypes(String internalJdbcUrl, String externalJdbcUrl, boolean enableProtocolCompression) throws Exception { + runWriteSupportedTypes(internalJdbcUrl, externalJdbcUrl, enableProtocolCompression, false); + } + + private void runWriteSupportedTypes(String internalJdbcUrl, String externalJdbcUrl, boolean enableProtocolCompression, boolean enableHttpConnectionProvider) throws Exception { + createClickhouseWriteTable(externalJdbcUrl); + + ExternalTable pxfWrite = TableFactory.getPxfJdbcWritableTable( + "pxf_ch_clickhouse_write_types", + CLICKHOUSE_PXF_FIELDS, + CLICKHOUSE_TABLE_WRITE, + CLICKHOUSE_DRIVER, + internalJdbcUrl, + ClickHouseContainer.CLICKHOUSE_USER, + "PASS=" + ClickHouseContainer.CLICKHOUSE_PASSWORD); + pxfWrite.setHost(pxfHost); + pxfWrite.setPort(pxfPort); + if (enableProtocolCompression) { + pxfWrite.addUserParameter(PROTOCOL_COMPRESS_ENABLED); + pxfWrite.addUserParameter(PROTOCOL_COMPRESS_ALGORITHM_LZ4); + } + if (enableHttpConnectionProvider) { + pxfWrite.addUserParameter(HTTP_CONNECTION_PROVIDER_APACHE); + } + cloudberry.createTableAndVerify(pxfWrite); + + ExternalTable pxfVerify = TableFactory.getPxfJdbcReadableTable( + "pxf_ch_clickhouse_write_verify", + CLICKHOUSE_PXF_FIELDS, + CLICKHOUSE_TABLE_WRITE, + CLICKHOUSE_DRIVER, + internalJdbcUrl, + ClickHouseContainer.CLICKHOUSE_USER, + "PASS=" + ClickHouseContainer.CLICKHOUSE_PASSWORD); + pxfVerify.setHost(pxfHost); + pxfVerify.setPort(pxfPort); + if (enableProtocolCompression) { + pxfVerify.addUserParameter(PROTOCOL_COMPRESS_ENABLED); + pxfVerify.addUserParameter(PROTOCOL_COMPRESS_ALGORITHM_LZ4); + } + if (enableHttpConnectionProvider) { + pxfVerify.addUserParameter(HTTP_CONNECTION_PROVIDER_APACHE); + } + cloudberry.createTableAndVerify(pxfVerify); + + try { + regress.runSqlTest("features/jdbc/clickhouse/write_types"); + } finally { + cloudberry.dropTable(pxfVerify, true); + cloudberry.dropTable(pxfWrite, true); + } + } + + private void createAndSeedClickhouseReadTable(String jdbcUrl) throws SQLException { + try (Connection chConn = openClickhouseConnection(jdbcUrl)) { + createClickhouseServerTable(chConn, CLICKHOUSE_TABLE_READ); + insertClickhouseReadFixture(chConn); + } + } + + private void createClickhouseWriteTable(String externalJdbcUrl) throws SQLException { + try (Connection chConn = openClickhouseConnection(externalJdbcUrl)) { + createClickhouseServerTable(chConn, CLICKHOUSE_TABLE_WRITE); + } + } + + /** Creates ClickHouse MergeTree table ({@code DROP IF EXISTS} + {@code CREATE}). */ + private void createClickhouseServerTable(Connection chConn, String tableName) throws SQLException { + try (Statement st = chConn.createStatement()) { + st.execute("DROP TABLE IF EXISTS " + tableName); + st.execute("CREATE TABLE " + tableName + " ( " + + "i_int Int32, " + + "s_small Int16, " + + "b_big Int64, " + + "f_float32 Float32, " + + "d_float64 Float64, " + + "b_bool Bool, " + + "dec Decimal(38,10), " + + "t_text String, " + + "bin String, " // binary data + + "d_date Date, " + + "d_ts DateTime64(3,'UTC'), " + + "d_tstz DateTime64(3,'UTC'), " + + "d_uuid UUID " + + ") ENGINE = MergeTree ORDER BY (i_int)"); + } + } + + /** Inserts fixture row into {@link #CLICKHOUSE_TABLE_READ} for the read test. */ + private void insertClickhouseReadFixture(Connection chConn) throws SQLException { + // TODO: setBytes() for ClickHouse String is driver-version sensitive and can serialize + // into an unexpected textual representation instead of raw bytes; use unhex() in SQL. + String insertSql = "INSERT INTO " + CLICKHOUSE_TABLE_READ + " (" + + "i_int, s_small, b_big, f_float32, d_float64, b_bool, dec, t_text, bin, d_date, d_ts, d_tstz, d_uuid" + + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, unhex('41424344'), ?, ?, ?, ?)"; + + try (PreparedStatement ps = chConn.prepareStatement(insertSql)) { + // JDBC setDate/setTimestamp without Calendar uses JVM default timezone during + // value conversion, which can shift wall-clock date/time on non-UTC hosts. + // Pass UTC explicitly to keep fixture values deterministic across environments. + Calendar utcCalendar = Calendar.getInstance(TimeZone.getTimeZone("UTC")); + ps.setInt(1, V_I_INT); + ps.setShort(2, V_S_SMALL); + ps.setLong(3, V_B_BIG); + ps.setFloat(4, V_F_FLOAT32); + ps.setDouble(5, V_D_FLOAT64); + ps.setBoolean(6, V_B_BOOL); + ps.setBigDecimal(7, new BigDecimal(V_DEC_TEXT)); + ps.setString(8, V_T_TEXT); + ps.setDate(9, Date.valueOf(V_D_DATE), utcCalendar); + ps.setTimestamp(10, Timestamp.valueOf(V_D_TS), utcCalendar); + ps.setTimestamp(11, Timestamp.valueOf(V_D_TS), utcCalendar); + ps.setObject(12, UUID.fromString(V_D_UUID)); + ps.executeUpdate(); + } + } + + private Connection openClickhouseConnection(String jdbcUrl) throws SQLException { + Properties props = new Properties(); + props.setProperty("user", ClickHouseContainer.CLICKHOUSE_USER); + props.setProperty("password", ClickHouseContainer.CLICKHOUSE_PASSWORD); + return DriverManager.getConnection(jdbcUrl, props); + } + +} \ No newline at end of file diff --git a/automation/src/test/java/org/apache/cloudberry/pxf/automation/features/jdbc/JdbcHiveTest.java b/automation/src/test/java/org/apache/cloudberry/pxf/automation/features/jdbc/JdbcHiveTest.java index 3090a099..3e902969 100644 --- a/automation/src/test/java/org/apache/cloudberry/pxf/automation/features/jdbc/JdbcHiveTest.java +++ b/automation/src/test/java/org/apache/cloudberry/pxf/automation/features/jdbc/JdbcHiveTest.java @@ -245,9 +245,6 @@ public class JdbcHiveTest extends BaseFeature { runSqlTest("features/jdbc/hive"); } - // Fails with the error: ERROR: PXF server error : java.io.DataInputStream cannot be cast to [B - // TODO: pxf_regress shows diff for this test. Should be fixed. - @FailsWithFDW @Test(enabled = false, groups = {"features", "gpdb", "security"}) public void jdbcHiveWrite() throws Exception { prepareDataForWriteTest(); diff --git a/automation/src/test/java/org/apache/cloudberry/pxf/automation/features/jdbc/JdbcTest.java b/automation/src/test/java/org/apache/cloudberry/pxf/automation/features/jdbc/JdbcTest.java index d52c850c..2263deb5 100755 --- a/automation/src/test/java/org/apache/cloudberry/pxf/automation/features/jdbc/JdbcTest.java +++ b/automation/src/test/java/org/apache/cloudberry/pxf/automation/features/jdbc/JdbcTest.java @@ -21,7 +21,6 @@ package org.apache.cloudberry.pxf.automation.features.jdbc; import java.io.File; -import annotations.FailsWithFDW; import annotations.WorksWithFDW; import org.apache.cloudberry.pxf.automation.AbstractTestcontainersTest; import org.apache.cloudberry.pxf.automation.applications.CloudberryApplication; @@ -508,31 +507,21 @@ public class JdbcTest extends AbstractTestcontainersTest { runSqlTest("features/jdbc/session_params"); } - @FailsWithFDW - // All the Writable Tests are failing with this Error: - // ERROR: PXF server error : class java.io.DataInputStream cannot be cast to class - // [B (java.io.DataInputStream and [B are in module java.base of loader 'bootstrap') @Test(groups = {"testcontainers", "pxf-jdbc"}) public void jdbcWritableTable() throws Exception { runSqlTest("features/jdbc/writable"); } - @FailsWithFDW - // All the Writable Tests are failing with this Error: - // ERROR: PXF server error : class java.io.DataInputStream cannot be cast to class - // [B (java.io.DataInputStream and [B are in module java.base of loader 'bootstrap') @Test(groups = {"testcontainers", "pxf-jdbc"}) public void jdbcWritableTableWithDateWideRange() throws Exception { runSqlTest("features/jdbc/writable_date_wide_range"); } - @FailsWithFDW @Test(groups = {"testcontainers", "pxf-jdbc"}) public void jdbcWritableTableNoBatch() throws Exception { runSqlTest("features/jdbc/writable_nobatch"); } - @FailsWithFDW @Test(groups = {"testcontainers", "pxf-jdbc"}) public void jdbcWritableTablePool() throws Exception { runSqlTest("features/jdbc/writable_pool"); diff --git a/docs/content/jdbc_pxf.html.md.erb b/docs/content/jdbc_pxf.html.md.erb index cef58764..e562a642 100644 --- a/docs/content/jdbc_pxf.html.md.erb +++ b/docs/content/jdbc_pxf.html.md.erb @@ -187,6 +187,7 @@ Refer to the following topics for examples on how to use PXF to read data from a - [Reading From and Writing to a PostgreSQL Table](jdbc_pxf_postgresql.html) - [Reading From and Writing to a MySQL Table](jdbc_pxf_mysql.html) - [Reading From and Writing to an Oracle Table](jdbc_pxf_oracle.html) +- [Reading From and Writing to a ClickHouse Table](jdbc_pxf_clickhouse.html) - [Reading From and Writing to a Trino Table](jdbc_pxf_trino.html) ## <a id="about_nq"></a>About Using Named Queries diff --git a/docs/content/jdbc_pxf_clickhouse.html.md.erb b/docs/content/jdbc_pxf_clickhouse.html.md.erb new file mode 100644 index 00000000..01b6a52f --- /dev/null +++ b/docs/content/jdbc_pxf_clickhouse.html.md.erb @@ -0,0 +1,143 @@ +--- +title: 'Example: Reading From and Writing to a ClickHouse Table' +--- + +<!-- +Licensed to the Apache Software Foundation (ASF) under one +or more contributor license agreements. See the NOTICE file +distributed with this work for additional information +regarding copyright ownership. The ASF licenses this file +to you under the Apache License, Version 2.0 (the +"License"); you may not use this file except in compliance +with the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + +Unless required by applicable law or agreed to in writing, +software distributed under the License is distributed on an +"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +KIND, either express or implied. See the License for the +specific language governing permissions and limitations +under the License. +--> + +In this example, you: + +- Install the ClickHouse JDBC driver on the Apache Cloudberry coordinator (see [JDBC driver installation](jdbc_pxf.html#cfg_jdbc_driver)) +- Create a ClickHouse table, and insert data into the table +- Create a PXF readable external table that references the ClickHouse table using `JDBC_DRIVER` and `DB_URL` in the `LOCATION` clause (no separate PXF server configuration directory is required) +- Read the data in the ClickHouse table using PXF +- Create a PXF writable external table that references the ClickHouse table +- Write data to the ClickHouse table using PXF +- Read the data in the ClickHouse table again + +## <a id="ex_create_ch_1"></a>Create a ClickHouse Table + +Perform the following steps to create a ClickHouse table named `pxf_ch_types` in the `default` database: + +```sql +CREATE TABLE default.pxf_ch_types ( + i_int Int32, + s_small Int16, + b_big Int64, + f_float32 Float32, + d_float64 Float64, + b_bool Bool, + dec Decimal(38,10), + t_text String, + bin String, + d_date Date, + d_ts DateTime64(3,'UTC'), + d_tstz DateTime64(3,'UTC'), + d_uuid UUID +) ENGINE = MergeTree ORDER BY (i_int); + +INSERT INTO default.pxf_ch_types + (i_int, s_small, b_big, f_float32, d_float64, b_bool, dec, t_text, bin, d_date, d_ts, d_tstz, d_uuid) +VALUES + (1, 2, 3, 1.25, 3.1415926, true, toDecimal64('12345.6789012345', 10), 'hello', 'ABCD', + toDate('2020-01-02'), toDateTime64('2020-01-02 03:04:05.006', 3, 'UTC'), + toDateTime64('2020-01-02 03:04:05.006', 3, 'UTC'), toUUID('550e8400-e29b-41d4-a716-446655440000')); +``` + +## <a id="ex_jdbconfig_1"></a>JDBC driver + +Place the ClickHouse JDBC driver JAR under `$PXF_BASE/lib` on every coordinator and segment host, synchronize PXF, and restart PXF. The Cloudberry build can bundle JDBC drivers from `server/pxf-jdbc-drivers`; follow your deployment’s procedure (for example, the automation `Makefile` target that stages PXF and links drivers). + +You do **not** need a `servers/clickhouse/jdbc-site.xml` if you pass connection options in the external table definition, as shown below. + +Automated tests exercise ClickHouse server versions **24** and **26** together with the ClickHouse JDBC drivers. + +For ClickHouse we recommend latest clickhouse-jdbc-all:0.9.7 or newer (fat jar version with shaded dependencies). In case you observe HTTP reliably issues you can use Apache HttpClient: add the JDBC URL parameter `http_connection_provider=APACHE_HTTP_CLIENT` (for example `jdbc:clickhouse://clickhousehost:8123/default?http_connection_provider=APACHE_HTTP_CLIENT`). + + +## <a id="ex_readjdbc_1"></a>Read from the ClickHouse Table + +Perform the following procedure to create a PXF external table that references the `pxf_ch_types` ClickHouse table, and reads the data in the table: + +1. Create a PXF readable external table using the `jdbc` profile. Substitute your ClickHouse host, port, and driver class as appropriate: + + ```sql + CREATE EXTERNAL TABLE pxf_ch_read( + i_int int, s_small smallint, b_big bigint, + f_float32 real, d_float64 double precision, b_bool boolean, + dec numeric, t_text text, bin bytea, d_date date, + d_ts timestamp, d_tstz timestamp with time zone, d_uuid uuid) + LOCATION ('pxf://default.pxf_ch_types?PROFILE=jdbc&JDBC_DRIVER=com.clickhouse.jdbc.ClickHouseDriver&DB_URL=jdbc:clickhouse://clickhousehost:8123/default') + FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import'); + ``` + +2. Read the data: + + ```sql + SELECT * FROM pxf_ch_read ORDER BY i_int; + ``` + +## <a id="ex_writejdbc_1"></a>Write to the ClickHouse Table + +Perform the following procedure to create a PXF writable external table that references the `pxf_ch_types` ClickHouse table, and writes data to the table: + +1. Create a PXF writable external table: + + ```sql + CREATE WRITABLE EXTERNAL TABLE pxf_ch_write( + i_int int, s_small smallint, b_big bigint, + f_float32 real, d_float64 double precision, b_bool boolean, + dec numeric, t_text text, bin bytea, d_date date, + d_ts timestamp, d_tstz timestamp with time zone, d_uuid uuid) + LOCATION ('pxf://default.pxf_ch_types?PROFILE=jdbc&JDBC_DRIVER=com.clickhouse.jdbc.ClickHouseDriver&DB_URL=jdbc:clickhouse://clickhousehost:8123/default') + FORMAT 'CUSTOM' (FORMATTER='pxfwritable_export'); + ``` + +2. Write data: + + ```sql + INSERT INTO pxf_ch_write VALUES + (2, 3, 4, 2.25, 4.1415926, true, CAST('12345.6789012345' AS numeric), + 'hello2', decode('41424344','hex'), DATE '2020-01-02', + TIMESTAMP '2020-01-02 03:04:05.006', + TIMESTAMPTZ '2020-01-02 03:04:05.006+00', + '550e8400-e29b-41d4-a716-446655440000'::uuid); + ``` + +3. Read the data back: + + ```sql + SELECT * FROM pxf_ch_read ORDER BY i_int; + ``` + +## Data Type Mapping and Limitations + +PXF supports the following JDBC data types (see [JDBC Data Types Supported](jdbc_pxf.html#datatypes)). +For ClickHouse, the typical JDBC-side mappings are: + +- `INTEGER`, `SMALLINT`, `BIGINT` → `Int32`, `Int16`, `Int64` +- `REAL`, `FLOAT8` → `Float32`, `Float64` +- `NUMERIC` → `Decimal(precision, scale)` +- `BOOLEAN` → `Bool` +- `VARCHAR`, `BPCHAR`, `TEXT` → `String` +- `DATE` → `Date` +- `TIMESTAMP` → `DateTime64(precision, 'UTC')` +- `TIMESTAMPTZ` → `DateTime64(precision, 'UTC')` +- `BYTEA` → `String` +- `UUID` → `UUID` diff --git a/server/Makefile b/server/Makefile index edd2cbb1..e6d05e61 100644 --- a/server/Makefile +++ b/server/Makefile @@ -40,7 +40,10 @@ help: @echo " - test - runs unit tests for all PXF modules" @echo " - coverage - runs unit tests for all PXF modules and gives a coverage report" @echo " - install - setup PXF in the configured deployPath" - @echo " - stage - build PXF server and stage resulting artifacts for packaging" + @echo " - stage - build PXF server and stage resulting artifacts for packaging (no JDBC drivers in build/stage/lib)" + @echo " - stage-notest - same as stage but skip tests (no JDBC drivers in build/stage/lib)" + @echo " - stage-jdbc-drivers - copy JDBC driver JARs to build/stage/lib" + @echo " - install-jdbc-drivers - setup PXF JDBC drivers in the configured deployPath" @echo " - doc - creates aggregate javadoc under docs" all: prepare-gradle-wrapper @@ -95,6 +98,20 @@ stage-notest: prepare-gradle-wrapper install -m 700 -d "build/stage/run" install -m 700 -d "build/stage/keytabs" +.PHONY: stage-jdbc-drivers +stage-jdbc-drivers: prepare-gradle-wrapper + rm -rf build/stage/lib + ./gradlew $(PXF_GRADLE_PROPERTIES) stageJdbcDrivers + +.PHONY: install-jdbc-drivers +install-jdbc-drivers: stage-jdbc-drivers + @if [ -z "$(PXF_HOME)" ]; then \ + echo "ERROR: PXF_HOME is not set"; exit 2; \ + fi + mkdir -p "$(PXF_HOME)"/lib + rm -f "$(PXF_HOME)"/lib/clickhouse-jdbc-*.jar + cp -R build/stage/lib/. "$(PXF_HOME)"/lib/ + clean: prepare-gradle-wrapper ./gradlew clean rm -rf build diff --git a/server/build.gradle b/server/build.gradle index e530d872..6a518c79 100644 --- a/server/build.gradle +++ b/server/build.gradle @@ -212,8 +212,9 @@ configure(javaProjects) { entry("avro") entry("avro-mapred") } - // Zstd support for Avro/Parquet + // Compression support for Avro/Parquet dependency("com.github.luben:zstd-jni:1.5.7-6") + dependency("at.yawk.lz4:lz4-java:1.10.3") // Jackson 1.x dependencies dependencySet(group:"org.codehaus.jackson", version:"1.9.13") { @@ -374,3 +375,11 @@ task stage(type: Copy) { doLast { new File("${buildDir}/stage/version").text = "${version}\n" } } + +task stageJdbcDrivers(type: Copy) { + description "Copy JDBC driver JARs from pxf-jdbc-drivers to build/stage/lib." + dependsOn ":pxf-jdbc-drivers:jar" + from(project(':pxf-jdbc-drivers').configurations.runtimeClasspath) + into("${buildDir}/stage/lib") + duplicatesStrategy = DuplicatesStrategy.FAIL +} diff --git a/server/settings.gradle b/server/pxf-jdbc-drivers/build.gradle similarity index 77% copy from server/settings.gradle copy to server/pxf-jdbc-drivers/build.gradle index dd419679..052eef02 100644 --- a/server/settings.gradle +++ b/server/pxf-jdbc-drivers/build.gradle @@ -17,14 +17,11 @@ * under the License. */ -rootProject.name = 'pxf' +dependencies { + // PostgreSQL driver is used by PXF itself. + // Its version is defined in server/build.gradle -include 'pxf-service', - 'pxf-api', - 'pxf-hbase', - 'pxf-hdfs', - 'pxf-hive', - 'pxf-jdbc', - 'pxf-json', - 'pxf-s3', - 'pxf-diagnostic' + // Clickhouse + implementation('com.clickhouse:clickhouse-jdbc:0.9.8:all') { transitive = false } + +} \ No newline at end of file diff --git a/server/pxf-jdbc/src/main/java/org/apache/cloudberry/pxf/plugins/jdbc/JdbcResolver.java b/server/pxf-jdbc/src/main/java/org/apache/cloudberry/pxf/plugins/jdbc/JdbcResolver.java index 99b1df73..485a0959 100644 --- a/server/pxf-jdbc/src/main/java/org/apache/cloudberry/pxf/plugins/jdbc/JdbcResolver.java +++ b/server/pxf-jdbc/src/main/java/org/apache/cloudberry/pxf/plugins/jdbc/JdbcResolver.java @@ -33,6 +33,7 @@ import org.slf4j.LoggerFactory; import java.io.IOException; import java.math.BigDecimal; +import java.nio.ByteBuffer; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; @@ -277,7 +278,7 @@ public class JdbcResolver extends JdbcBasePlugin implements Resolver { if (oneField.val == null) { valDebug = "null"; } else if (oneFieldType == DataType.BYTEA) { - valDebug = String.format("'{}'", new String((byte[]) oneField.val)); + valDebug = String.format("'{}'", new String(toByteArray(oneField.val))); } else { valDebug = String.format("'{}'", oneField.val.toString()); } @@ -421,7 +422,7 @@ public class JdbcResolver extends JdbcBasePlugin implements Resolver { if (field.val == null) { statement.setNull(i, Types.BINARY); } else { - statement.setBytes(i, (byte[]) field.val); + statement.setBytes(i, toByteArray(field.val)); } break; case DATE: @@ -506,4 +507,19 @@ public class JdbcResolver extends JdbcBasePlugin implements Resolver { } return value; } + + /** + * Converts a BYTEA field value to byte[]. The value can arrive + * as byte[] (OutputFormat.GPDBWritable from external-table) + * as ByteBuffer (OutputFormat.TEXT from FDW) + */ + private static byte[] toByteArray(Object val) { + if (val instanceof byte[]) { + return (byte[]) val; + } + ByteBuffer bb = (ByteBuffer) val; + byte[] bytes = new byte[bb.remaining()]; + bb.duplicate().get(bytes); + return bytes; + } } diff --git a/server/settings.gradle b/server/settings.gradle index dd419679..5df3ff21 100644 --- a/server/settings.gradle +++ b/server/settings.gradle @@ -25,6 +25,7 @@ include 'pxf-service', 'pxf-hdfs', 'pxf-hive', 'pxf-jdbc', + 'pxf-jdbc-drivers', 'pxf-json', 'pxf-s3', 'pxf-diagnostic' --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
