This is an automated email from the ASF dual-hosted git repository. kxiao pushed a commit to branch branch-2.0 in repository https://gitbox.apache.org/repos/asf/doris.git
commit 0342dc548e8f1b9eb31e1ce0e91824ccbd2a38c9 Author: zy-kkk <[email protected]> AuthorDate: Tue Aug 22 12:13:36 2023 +0800 [improve](jdbc catalog) Add a variable to accommodate the final keyword in ClickHouse Jdbc Catalog queries (#23282) --- .../clickhouse/init/03-create-table.sql | 10 +++++++++- .../docker-compose/clickhouse/init/04-insert.sql | 4 ++++ docs/en/docs/advanced/variables.md | 6 ++++++ docs/zh-CN/docs/advanced/variables.md | 6 ++++++ .../doris/planner/external/jdbc/JdbcScanNode.java | 4 ++++ .../java/org/apache/doris/qe/SessionVariable.java | 5 +++++ .../jdbc/test_clickhouse_jdbc_catalog.out | Bin 2710 -> 2766 bytes .../jdbc/test_clickhouse_jdbc_catalog.groovy | 14 +++++++++++--- 8 files changed, 45 insertions(+), 4 deletions(-) diff --git a/docker/thirdparties/docker-compose/clickhouse/init/03-create-table.sql b/docker/thirdparties/docker-compose/clickhouse/init/03-create-table.sql index dcc0637cbc..c13fac5cf9 100644 --- a/docker/thirdparties/docker-compose/clickhouse/init/03-create-table.sql +++ b/docker/thirdparties/docker-compose/clickhouse/init/03-create-table.sql @@ -122,4 +122,12 @@ CREATE TABLE doris_test.json `o` JSON ) ENGINE = MergeTree -ORDER BY id; \ No newline at end of file +ORDER BY id; + +CREATE TABLE doris_test.final_test +( + key Int64, + some String +) + ENGINE = ReplacingMergeTree +ORDER BY key; diff --git a/docker/thirdparties/docker-compose/clickhouse/init/04-insert.sql b/docker/thirdparties/docker-compose/clickhouse/init/04-insert.sql index 9c6785587a..69c2ebd25f 100644 --- a/docker/thirdparties/docker-compose/clickhouse/init/04-insert.sql +++ b/docker/thirdparties/docker-compose/clickhouse/init/04-insert.sql @@ -35,3 +35,7 @@ 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']); INSERT INTO doris_test.json VALUES ('1','{"a": 1, "b": { "c": 2, "d": [1, 2, 3] }}'); + +INSERT INTO doris_test.final_test Values (1, 'first'); +INSERT INTO doris_test.final_test Values (1, 'second'); + diff --git a/docs/en/docs/advanced/variables.md b/docs/en/docs/advanced/variables.md index df9983c52b..5829d63cf6 100644 --- a/docs/en/docs/advanced/variables.md +++ b/docs/en/docs/advanced/variables.md @@ -677,6 +677,12 @@ Translated with www.DeepL.com/Translator (free version) Because the maximum length of the char or varchar column in the schema of the table is inconsistent with the schema in the underlying parquet or orc file. At this time, if the option is turned on, it will be truncated according to the maximum length in the schema of the table. +* `jdbc_clickhouse_query_final` + + Whether to add the final keyword when using the JDBC Catalog function to query ClickHouse,default is false. + + It is used for the ReplacingMergeTree table engine of ClickHouse to deduplicate queries. + *** #### Supplementary instructions on statement execution timeout control diff --git a/docs/zh-CN/docs/advanced/variables.md b/docs/zh-CN/docs/advanced/variables.md index 4f545c35d5..315fc04355 100644 --- a/docs/zh-CN/docs/advanced/variables.md +++ b/docs/zh-CN/docs/advanced/variables.md @@ -664,6 +664,12 @@ try (Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:9030/ 因为外表会存在表的 schema 中 char 或者 varchar 列的最大长度和底层 parquet 或者 orc 文件中的 schema 不一致的情况。此时开启改选项，会按照表的 schema 中的最大长度进行截断。 +* `jdbc_clickhouse_query_final` + + 是否在使用 JDBC Catalog 功能查询 ClickHouse 时增加 final 关键字，默认为 false + + 用于 ClickHouse 的 ReplacingMergeTree 表引擎查询去重 + *** #### 关于语句执行超时控制的补充说明 diff --git a/fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcScanNode.java b/fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcScanNode.java index 4854ce5a00..87dca032c7 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcScanNode.java +++ b/fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcScanNode.java @@ -205,6 +205,10 @@ public class JdbcScanNode extends ExternalScanNode { sql.append(" LIMIT ").append(limit); } + if (jdbcType == TOdbcTableType.CLICKHOUSE + && ConnectContext.get().getSessionVariable().jdbcClickhouseQueryFinal) { + sql.append(" SETTINGS final = 1"); + } return sql.toString(); } diff --git a/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java b/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java index dd9af0d932..3e26c8fab3 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java +++ b/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java @@ -377,6 +377,8 @@ public class SessionVariable implements Serializable, Writable { public static final String ROUND_PRECISE_DECIMALV2_VALUE = "round_precise_decimalv2_value"; + public static final String JDBC_CLICKHOUSE_QUERY_FINAL = "jdbc_clickhouse_query_final"; + public static final List<String> DEBUG_VARIABLES = ImmutableList.of( SKIP_DELETE_PREDICATE, SKIP_DELETE_BITMAP, @@ -391,6 +393,9 @@ public class SessionVariable implements Serializable, Writable { // if it is setStmt, we needn't collect session origin value public boolean isSingleSetVar = false; + @VariableMgr.VarAttr(name = JDBC_CLICKHOUSE_QUERY_FINAL) + public boolean jdbcClickhouseQueryFinal = false; + @VariableMgr.VarAttr(name = ROUND_PRECISE_DECIMALV2_VALUE) public boolean roundPreciseDecimalV2Value = false; diff --git a/regression-test/data/external_table_p0/jdbc/test_clickhouse_jdbc_catalog.out b/regression-test/data/external_table_p0/jdbc/test_clickhouse_jdbc_catalog.out index a677bba662..0b847974ef 100644 Binary files a/regression-test/data/external_table_p0/jdbc/test_clickhouse_jdbc_catalog.out and b/regression-test/data/external_table_p0/jdbc/test_clickhouse_jdbc_catalog.out differ diff --git a/regression-test/suites/external_table_p0/jdbc/test_clickhouse_jdbc_catalog.groovy b/regression-test/suites/external_table_p0/jdbc/test_clickhouse_jdbc_catalog.groovy index 8675d04931..2bd1e7d00e 100644 --- a/regression-test/suites/external_table_p0/jdbc/test_clickhouse_jdbc_catalog.groovy +++ b/regression-test/suites/external_table_p0/jdbc/test_clickhouse_jdbc_catalog.groovy @@ -22,6 +22,10 @@ suite("test_clickhouse_jdbc_catalog", "p0") { 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 externalEnvIp = context.config.otherConfigs.get("externalEnvIp") + String s3_endpoint = getS3Endpoint() + String bucket = getS3BucketName() + String driver_url = "https://${bucket}.${s3_endpoint}/regression/jdbc_driver/clickhouse-jdbc-0.4.2-all.jar"; String inDorisTable = "doris_in_tb"; @@ -30,9 +34,9 @@ suite("test_clickhouse_jdbc_catalog", "p0") { sql """ create catalog if not exists ${catalog_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";, + "password"="", + "jdbc_url" = "jdbc:clickhouse://${externalEnvIp}:${clickhouse_port}/doris_test", + "driver_url" = "${driver_url}", "driver_class" = "com.clickhouse.jdbc.ClickHouseDriver" );""" @@ -58,6 +62,10 @@ suite("test_clickhouse_jdbc_catalog", "p0") { order_qt_filter """ select k1,k2 from type where 1 = 1 order by 1 ; """ order_qt_filter2 """ select k1,k2 from type where 1 = 1 and k1 = true order by 1 ; """ order_qt_filter3 """ select k1,k2 from type where k1 = true order by 1 ; """ + sql "set jdbc_clickhouse_query_final = true;" + order_qt_final1 """select * from final_test""" + sql "set jdbc_clickhouse_query_final = false;" + order_qt_final2 """select * from final_test""" sql """ drop catalog if exists ${catalog_name} """ } --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
