This is an automated email from the ASF dual-hosted git repository. morningman pushed a commit to branch branch-1.2-lts in repository https://gitbox.apache.org/repos/asf/doris.git
commit bbf57a9dd06ad98a045e722728ce249fc98d66e8 Author: yongkang.zhong <[email protected]> AuthorDate: Mon Apr 3 20:18:36 2023 +0800 [test](jdbc catalog) add clickhouse jdbc catalog base type test (#18007) --- .../docker-compose/clickhouse/clickhouse.env | 19 ++++ .../docker-compose/clickhouse/clickhouse.yaml.tpl | 48 +++++++++++ .../docker-compose/clickhouse/init/01-drop-db.sql | 18 ++++ .../clickhouse/init/02-create-db.sql | 18 ++++ .../clickhouse/init/03-create-table.sql | 96 +++++++++++++++++++++ .../docker-compose/clickhouse/init/04-insert.sql | 26 ++++++ docker/thirdparties/run-thirdparties-docker.sh | 21 ++++- .../java/org/apache/doris/udf/JdbcExecutor.java | 1 + regression-test/conf/regression-conf.groovy | 1 + .../test_clickhouse_jdbc_catalog.out | Bin 0 -> 880 bytes .../test_clickhouse_jdbc_catalog.groovy | 64 ++++++++++++++ 11 files changed, 309 insertions(+), 3 deletions(-) diff --git a/docker/thirdparties/docker-compose/clickhouse/clickhouse.env b/docker/thirdparties/docker-compose/clickhouse/clickhouse.env new file mode 100644 index 0000000000..13355650a6 --- /dev/null +++ b/docker/thirdparties/docker-compose/clickhouse/clickhouse.env @@ -0,0 +1,19 @@ +#!/usr/bin/env bash +# 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. + +DOCKER_CLICKHOUSE_EXTERNAL_HTTP_PORT=8123 diff --git a/docker/thirdparties/docker-compose/clickhouse/clickhouse.yaml.tpl b/docker/thirdparties/docker-compose/clickhouse/clickhouse.yaml.tpl new file mode 100644 index 0000000000..b9e26d1c21 --- /dev/null +++ b/docker/thirdparties/docker-compose/clickhouse/clickhouse.yaml.tpl @@ -0,0 +1,48 @@ +# +# 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. +# + +version: "2.1" + +services: + doris--clickhouse: + image: "clickhouse/clickhouse-server:22.8.15.23-alpine" + restart: always + environment: + CLICKHOUSE_PASSWORD: 123456 + ulimits: + nofile: + soft: 262144 + hard: 262144 + ports: + - ${DOCKER_CLICKHOUSE_EXTERNAL_HTTP_PORT}:8123 + healthcheck: + test: ["CMD-SHELL", "clickhouse-client --password=123456 --query 'SELECT 1'"] + interval: 30s + timeout: 10s + retries: 5 + volumes: + - ./init:/docker-entrypoint-initdb.d + networks: + - doris--clickhouse + hello-world: + image: hello-world + depends_on: + doris--clickhouse: + condition: service_healthy + +networks: + doris--clickhouse: diff --git a/docker/thirdparties/docker-compose/clickhouse/init/01-drop-db.sql b/docker/thirdparties/docker-compose/clickhouse/init/01-drop-db.sql new file mode 100644 index 0000000000..469f104b98 --- /dev/null +++ b/docker/thirdparties/docker-compose/clickhouse/init/01-drop-db.sql @@ -0,0 +1,18 @@ +-- 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. + +DROP DATABASE IF EXISTS doris_test; diff --git a/docker/thirdparties/docker-compose/clickhouse/init/02-create-db.sql b/docker/thirdparties/docker-compose/clickhouse/init/02-create-db.sql new file mode 100644 index 0000000000..da5a4cf993 --- /dev/null +++ b/docker/thirdparties/docker-compose/clickhouse/init/02-create-db.sql @@ -0,0 +1,18 @@ +-- 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. + +CREATE DATABASE IF NOT EXISTS doris_test; diff --git a/docker/thirdparties/docker-compose/clickhouse/init/03-create-table.sql b/docker/thirdparties/docker-compose/clickhouse/init/03-create-table.sql new file mode 100644 index 0000000000..4a8fceee3d --- /dev/null +++ b/docker/thirdparties/docker-compose/clickhouse/init/03-create-table.sql @@ -0,0 +1,96 @@ +-- 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. + +CREATE TABLE doris_test.type +( + `k1` Bool, + `k2` Date, + `k3` Date32, + `k4` Datetime, + `k5` Datetime64, + `k6` Float32, + `k7` Float64, + `k8` Int8, + `k9` Int16, + `k10` Int32, + `k11` Int64, + `k12` Int128, + `k13` Int256, + `k14` UInt8, + `k15` UInt16, + `k16` UInt32, + `k17` UInt64, + `k18` UInt128, + `k19` UInt256, + `k20` Decimal(9,2), + `k21` Decimal(18,2), + `k22` Decimal(38,2), + `k23` Decimal(76,2), + `k24` Enum('hello' = 1, 'world' = 2), + `k25` IPv4, + `k26` IPv6, + `k27` UUID, + `k28` String, + `k29` FixedString(2) +) +ENGINE = MergeTree +ORDER BY k1; + + + +CREATE TABLE doris_test.student +( + id Int16, + name String, + age Int16 +) +ENGINE = MergeTree +ORDER BY id; + +CREATE TABLE doris_test.arr +( + `id` String, + `arr1` Array(Bool), + `arr2` Array(Date), + `arr3` Array(Date32), + `arr4` Array(Float32), + `arr5` Array(Float64), + `arr6` Array(Int8), + `arr7` Array(Int16), + `arr8` Array(Int32), + `arr9` Array(Int64), + `arr10` Array(Int128), + `arr11` Array(Int256), + `arr12` Array(UInt8), + `arr13` Array(UInt16), + `arr14` Array(UInt32), + `arr15` Array(UInt64), + `arr16` Array(UInt128), + `arr17` Array(UInt256), + `arr18` Array(Decimal(9,2)), + `arr19` Array(Enum('hello' = 1, 'world' = 2)), + `arr20` Array(IPv4), + `arr21` Array(IPv6), + `arr22` Array(UUID), + `arr23` Array(Nullable(Int8)), + `arr24` Array(String), + `arr25` Array(LowCardinality(String)), + `arr26` Array(Datetime), + `arr27` Array(Datetime64) +) +ENGINE = MergeTree +ORDER BY id \ No newline at end of file diff --git a/docker/thirdparties/docker-compose/clickhouse/init/04-insert.sql b/docker/thirdparties/docker-compose/clickhouse/init/04-insert.sql new file mode 100644 index 0000000000..d5df9baba1 --- /dev/null +++ b/docker/thirdparties/docker-compose/clickhouse/init/04-insert.sql @@ -0,0 +1,26 @@ +-- 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. + +INSERT INTO doris_test.type VALUES +(true, '2022-01-01','2022-01-01','2022-01-01 00:00:00','2022-01-01 00:00:00.000000000',1.1,1.1,1,1,1,1,1,1,1,1,1,1,1,1,1.1,1.1,1.1,1.1,1,'116.253.40.133','2a02:aa08:e000:3100::2','61f0c404-5cb3-11e7-907b-a6006ad3dba0','String','F'); +INSERT INTO doris_test.type VALUES +(false, '2022-01-02','2022-01-02','2022-01-02 00:00:00','2022-01-02 00:00:00.000000000',2.2,2.2,2,2,2,2,2,2,2,2,2,2,2,2,2.2,2.2,2.2,2.2,2,'116.253.40.133','2a02:aa08:e000:3100::2','61f0c404-5cb3-11e7-907b-a6006ad3dba0','String','T'); + +INSERT INTO doris_test.student values (1, 'doris', 18), (2, 'alice', 19), (3, 'bob', 20); + +INSERT INTO doris_test.arr values +('1',[true],['2022-01-01'],['2022-01-01'],[1.1],[1.1],[1],[1],[1],[1],[1],[1],[1],[1],[1],[1],[1],[1],[2.2],[1],['116.253.40.133'],['2a02:aa08:e000:3100::2'],['61f0c404-5cb3-11e7-907b-a6006ad3dba0'],[1],['string'],['string'],['2022-01-01 00:00:00'],['2022-01-01 00:00:00']) \ No newline at end of file diff --git a/docker/thirdparties/run-thirdparties-docker.sh b/docker/thirdparties/run-thirdparties-docker.sh index bb68a2fe37..aeaad67dff 100755 --- a/docker/thirdparties/run-thirdparties-docker.sh +++ b/docker/thirdparties/run-thirdparties-docker.sh @@ -37,7 +37,7 @@ Usage: $0 <options> --stop stop the specified components All valid components: - mysql,pg,oracle,sqlserver,es,hive,iceberg + mysql,pg,oracle,sqlserver,clickhouse,es,hive,iceberg " exit 1 } @@ -60,7 +60,7 @@ STOP=0 if [[ "$#" == 1 ]]; then # default - COMPONENTS="mysql,pg,oracle,sqlserver,hive,iceberg" + COMPONENTS="mysql,pg,oracle,sqlserver,clickhouse,hive,iceberg" else while true; do case "$1" in @@ -92,7 +92,7 @@ else done if [[ "${COMPONENTS}"x == ""x ]]; then if [[ "${STOP}" -eq 1 ]]; then - COMPONENTS="mysql,pg,oracle,sqlserver,hive,iceberg" + COMPONENTS="mysql,pg,oracle,sqlserver,clickhouse,hive,iceberg" fi fi fi @@ -126,6 +126,7 @@ RUN_MYSQL=0 RUN_PG=0 RUN_ORACLE=0 RUN_SQLSERVER=0 +RUN_CLICKHOUSE=0 RUN_HIVE=0 RUN_ES=0 RUN_ICEBERG=0 @@ -138,6 +139,8 @@ for element in "${COMPONENTS_ARR[@]}"; do RUN_ORACLE=1 elif [[ "${element}"x == "sqlserver"x ]]; then RUN_SQLSERVER=1 + elif [[ "${element}"x == "clickhouse"x ]]; then + RUN_CLICKHOUSE=1 elif [[ "${element}"x == "es"x ]]; then RUN_ES=1 elif [[ "${element}"x == "hive"x ]]; then @@ -216,6 +219,18 @@ if [[ "${RUN_SQLSERVER}" -eq 1 ]]; then fi fi +if [[ "${RUN_CLICKHOUSE}" -eq 1 ]]; then + # clickhouse + cp "${ROOT}"/docker-compose/clickhouse/clickhouse.yaml.tpl "${ROOT}"/docker-compose/clickhouse/clickhouse.yaml + sed -i "s/doris--/${CONTAINER_UID}/g" "${ROOT}"/docker-compose/clickhouse/clickhouse.yaml + sudo docker compose -f "${ROOT}"/docker-compose/clickhouse/clickhouse.yaml --env-file "${ROOT}"/docker-compose/clickhouse/clickhouse.env down + if [[ "${STOP}" -ne 1 ]]; then + sudo mkdir -p "${ROOT}"/docker-compose/clickhouse/data/ + sudo rm "${ROOT}"/docker-compose/clickhouse/data/* -rf + sudo docker compose -f "${ROOT}"/docker-compose/clickhouse/clickhouse.yaml --env-file "${ROOT}"/docker-compose/clickhouse/clickhouse.env up -d + fi +fi + if [[ "${RUN_HIVE}" -eq 1 ]]; then # hive # before start it, you need to download parquet file package, see "README" in "docker-compose/hive/scripts/" diff --git a/fe/java-udf/src/main/java/org/apache/doris/udf/JdbcExecutor.java b/fe/java-udf/src/main/java/org/apache/doris/udf/JdbcExecutor.java index 0d284265fb..b1922ac45e 100644 --- a/fe/java-udf/src/main/java/org/apache/doris/udf/JdbcExecutor.java +++ b/fe/java-udf/src/main/java/org/apache/doris/udf/JdbcExecutor.java @@ -220,6 +220,7 @@ public class JdbcExecutor { static { CK_ARRAY_CONVERTERS.put(String[].class, res -> Arrays.toString((String[]) res)); + CK_ARRAY_CONVERTERS.put(boolean[].class, res -> Arrays.toString((boolean[]) res)); CK_ARRAY_CONVERTERS.put(byte[].class, res -> Arrays.toString((byte[]) res)); CK_ARRAY_CONVERTERS.put(Byte[].class, res -> Arrays.toString((Byte[]) res)); CK_ARRAY_CONVERTERS.put(LocalDate[].class, res -> Arrays.toString((LocalDate[]) res)); diff --git a/regression-test/conf/regression-conf.groovy b/regression-test/conf/regression-conf.groovy index ebb52dbe28..fa2a4e3cc2 100644 --- a/regression-test/conf/regression-conf.groovy +++ b/regression-test/conf/regression-conf.groovy @@ -76,6 +76,7 @@ mysql_57_port=3316 pg_14_port=5442 oracle_11_port=1521 sqlserver_2022_port=1433 +clickhouse_22_port=8123 // hive catalog test config // To enable jdbc test, you need first start hive container. diff --git a/regression-test/data/jdbc_catalog_p0/test_clickhouse_jdbc_catalog.out b/regression-test/data/jdbc_catalog_p0/test_clickhouse_jdbc_catalog.out new file mode 100644 index 0000000000..8efde34a30 Binary files /dev/null and b/regression-test/data/jdbc_catalog_p0/test_clickhouse_jdbc_catalog.out differ diff --git a/regression-test/suites/jdbc_catalog_p0/test_clickhouse_jdbc_catalog.groovy b/regression-test/suites/jdbc_catalog_p0/test_clickhouse_jdbc_catalog.groovy new file mode 100644 index 0000000000..57d47c9469 --- /dev/null +++ b/regression-test/suites/jdbc_catalog_p0/test_clickhouse_jdbc_catalog.groovy @@ -0,0 +1,64 @@ +// 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. + +suite("test_clickhouse_jdbc_catalog", "p0") { + String enabled = context.config.otherConfigs.get("enableJdbcTest") + if (enabled != null && enabled.equalsIgnoreCase("true")) { + String resource_name = "clickhouse_catalog_resource"; + String catalog_name = "clickhouse_catalog"; + String internal_db_name = "regression_test_jdbc_catalog_p0"; + String ex_db_name = "doris_test"; + String clickhouse_port = context.config.otherConfigs.get("clickhouse_22_port"); + + String inDorisTable = "doris_in_tb"; + + sql """ drop catalog if exists ${catalog_name} """ + sql """ drop resource if exists ${resource_name} """ + + sql """ create resource if not exists ${resource_name} properties( + "type"="jdbc", + "user"="default", + "password"="123456", + "jdbc_url" = "jdbc:clickhouse://127.0.0.1:${clickhouse_port}/doris_test", + "driver_url" = "https://doris-community-test-1308700295.cos.ap-hongkong.myqcloud.com/jdbc_driver/clickhouse-jdbc-0.4.2-all.jar";, + "driver_class" = "com.clickhouse.jdbc.ClickHouseDriver" + );""" + + sql """ CREATE CATALOG ${catalog_name} WITH RESOURCE ${resource_name} """ + + sql """ drop table if exists ${inDorisTable} """ + sql """ + CREATE TABLE ${inDorisTable} ( + `id` INT NULL COMMENT "主键id", + `name` string NULL COMMENT "名字", + `age` INT NULL COMMENT "年龄" + ) DISTRIBUTED BY HASH(id) BUCKETS 10 + PROPERTIES("replication_num" = "1"); + """ + + sql """ switch ${catalog_name} """ + sql """ use ${ex_db_name} """ + + order_qt_type """ select * from type order by k1; """ + order_qt_arr """ select * from arr order by id; """ + sql """ insert into internal.${internal_db_name}.${inDorisTable} select * from student; """ + order_qt_in_tb """ select id, name, age from internal.${internal_db_name}.${inDorisTable} order by id; """ + + sql """ drop catalog if exists ${catalog_name} """ + sql """ drop resource if exists ${resource_name} """ + } +} \ No newline at end of file --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
