You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by zy...@apache.org on 2024/01/30 15:24:43 UTC
(doris) branch branch-2.0 updated: [enhancement](jdbc catalog) Enhance function pushdown of Jdbc Oracle Catalog (#29972) (#30513)
This is an automated email from the ASF dual-hosted git repository.
zykkk pushed a commit to branch branch-2.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-2.0 by this push:
new b69344ffc01 [enhancement](jdbc catalog) Enhance function pushdown of Jdbc Oracle Catalog (#29972) (#30513)
b69344ffc01 is described below
commit b69344ffc01079447bf3e9468d104477119be6d9
Author: zy-kkk <zh...@gmail.com>
AuthorDate: Tue Jan 30 23:24:36 2024 +0800
[enhancement](jdbc catalog) Enhance function pushdown of Jdbc Oracle Catalog (#29972) (#30513)
---
docs/en/docs/lakehouse/multi-catalog/jdbc.md | 15 +++++++++++--
docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md | 15 +++++++++++--
.../external/jdbc/JdbcFunctionPushDownRule.java | 25 ++++++++++++++++++++++
.../doris/planner/external/jdbc/JdbcScanNode.java | 3 ++-
.../jdbc/test_oracle_jdbc_catalog.groovy | 6 ++++++
5 files changed, 59 insertions(+), 5 deletions(-)
diff --git a/docs/en/docs/lakehouse/multi-catalog/jdbc.md b/docs/en/docs/lakehouse/multi-catalog/jdbc.md
index fae92fdbbdb..1973994d9f4 100644
--- a/docs/en/docs/lakehouse/multi-catalog/jdbc.md
+++ b/docs/en/docs/lakehouse/multi-catalog/jdbc.md
@@ -113,8 +113,8 @@ In some cases, the keywords in the database might be used as the field names. Fo
### Predicate Pushdown
1. When executing a query like `where dt = '2022-01-01'`, Doris can push down these filtering conditions to the external data source, thereby directly excluding data that does not meet the conditions at the data source level, reducing the number of unqualified Necessary data acquisition and transfer. This greatly improves query performance while also reducing the load on external data sources.
-
-2. When `enable_func_pushdown` is set to true, the function condition after where will also be pushed down to the external data source. Currently, only MySQL is supported. If you encounter a function that MySQL does not support, you can set this parameter to false, at present, Doris will automatically identify some functions not supported by MySQL to filter the push-down conditions, which can be checked by explain sql.
+
+2. When `enable_func_pushdown` is set to true, the function conditions after where will also be pushed down to the external data source. Currently, only MySQL, ClickHouse, and Oracle are supported. If you encounter functions that are not supported by MySQL, ClickHouse, and Oracle, you can use this The parameter is set to false. At present, Doris will automatically identify some functions that are not supported by MySQL and functions supported by ClickHouse and Oracle for push-down condit [...]
Functions that are currently not pushed down include:
@@ -123,6 +123,17 @@ Functions that are currently not pushed down include:
| DATE_TRUNC |
| MONEY_FORMAT |
+Functions that are currently pushed down include:
+
+| ClickHouse |
+|:--------------:|
+| FROM_UNIXTIME |
+| UNIX_TIMESTAMP |
+
+| Oracle |
+|:------:|
+| NVL |
+
### Line Limit
If there is a limit keyword in the query, Doris will translate it into semantics suitable for different data sources.
diff --git a/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md b/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md
index eb8c03dd197..ec569d0017d 100644
--- a/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md
+++ b/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md
@@ -113,8 +113,8 @@ select * from mysql_catalog.mysql_database.mysql_table where k1 > 1000 and k3 ='
### 谓词下推
1. 当执行类似于 `where dt = '2022-01-01'` 这样的查询时，Doris 能够将这些过滤条件下推到外部数据源，从而直接在数据源层面排除不符合条件的数据，减少了不必要的数据获取和传输。这大大提高了查询性能，同时也降低了对外部数据源的负载。
-
-2. 当 `enable_func_pushdown` 设置为true，会将 where 之后的函数条件也下推到外部数据源，目前仅支持 MySQL，如遇到 MySQL 不支持的函数，可以将此参数设置为 false，目前 Doris 会自动识别部分 MySQL 不支持的函数进行下推条件过滤，可通过 explain sql 查看。
+
+2. 当 `enable_func_pushdown` 设置为true，会将 where 之后的函数条件也下推到外部数据源，目前仅支持 MySQL、ClickHouse、Oracle，如遇到 MySQL、ClickHouse、Oracle 不支持的函数，可以将此参数设置为 false，目前 Doris 会自动识别部分 MySQL 不支持的函数以及 ClickHouse、Oracle 支持的函数进行下推条件过滤，可通过 explain sql 查看。
目前不会下推的函数有：
@@ -123,6 +123,17 @@ select * from mysql_catalog.mysql_database.mysql_table where k1 > 1000 and k3 ='
| DATE_TRUNC |
| MONEY_FORMAT |
+目前会下推的函数有：
+
+| ClickHouse |
+|:--------------:|
+| FROM_UNIXTIME |
+| UNIX_TIMESTAMP |
+
+| Oracle |
+|:------:|
+| NVL |
+
### 行数限制
如果在查询中带有 limit 关键字，Doris 会将其转译成适合不同数据源的语义。
diff --git a/fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcFunctionPushDownRule.java b/fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcFunctionPushDownRule.java
index 27fd693ca4d..3678fa1a40c 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcFunctionPushDownRule.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcFunctionPushDownRule.java
@@ -49,6 +49,13 @@ public class JdbcFunctionPushDownRule {
CLICKHOUSE_SUPPORTED_FUNCTIONS.add("unix_timestamp");
}
+ private static final TreeSet<String> ORACLE_SUPPORTED_FUNCTIONS = new TreeSet<>(String.CASE_INSENSITIVE_ORDER);
+
+ static {
+ ORACLE_SUPPORTED_FUNCTIONS.add("nvl");
+ ORACLE_SUPPORTED_FUNCTIONS.add("ifnull");
+ }
+
private static boolean isMySQLFunctionUnsupported(String functionName) {
return MYSQL_UNSUPPORTED_FUNCTIONS.contains(functionName.toLowerCase());
}
@@ -57,6 +64,9 @@ public class JdbcFunctionPushDownRule {
return !CLICKHOUSE_SUPPORTED_FUNCTIONS.contains(functionName.toLowerCase());
}
+ private static boolean isOracleFunctionUnsupported(String functionName) {
+ return !ORACLE_SUPPORTED_FUNCTIONS.contains(functionName.toLowerCase());
+ }
private static final Map<String, String> REPLACE_MYSQL_FUNCTIONS = Maps.newHashMap();
@@ -79,6 +89,16 @@ public class JdbcFunctionPushDownRule {
return REPLACE_CLICKHOUSE_FUNCTIONS.containsKey(functionName.toLowerCase());
}
+ private static final Map<String, String> REPLACE_ORACLE_FUNCTIONS = Maps.newHashMap();
+
+ static {
+ REPLACE_ORACLE_FUNCTIONS.put("ifnull", "nvl");
+ }
+
+ private static boolean isReplaceOracleFunctions(String functionName) {
+ return REPLACE_ORACLE_FUNCTIONS.containsKey(functionName.toLowerCase());
+ }
+
public static Expr processFunctions(TOdbcTableType tableType, Expr expr, List<String> errors) {
if (tableType == null || expr == null) {
return expr;
@@ -93,6 +113,9 @@ public class JdbcFunctionPushDownRule {
} else if (TOdbcTableType.CLICKHOUSE.equals(tableType)) {
replaceFunction = JdbcFunctionPushDownRule::isReplaceClickHouseFunctions;
checkFunction = JdbcFunctionPushDownRule::isClickHouseFunctionUnsupported;
+ } else if (TOdbcTableType.ORACLE.equals(tableType)) {
+ replaceFunction = JdbcFunctionPushDownRule::isReplaceOracleFunctions;
+ checkFunction = JdbcFunctionPushDownRule::isOracleFunctionUnsupported;
} else {
return expr;
}
@@ -137,6 +160,8 @@ public class JdbcFunctionPushDownRule {
newFunc = REPLACE_MYSQL_FUNCTIONS.get(func.toLowerCase());
} else if (TOdbcTableType.CLICKHOUSE.equals(tableType)) {
newFunc = REPLACE_CLICKHOUSE_FUNCTIONS.get(func);
+ } else if (TOdbcTableType.ORACLE.equals(tableType)) {
+ newFunc = REPLACE_ORACLE_FUNCTIONS.get(func);
} else {
newFunc = null;
}
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 d2e81233fee..efe1daef4be 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
@@ -311,7 +311,8 @@ public class JdbcScanNode extends ExternalScanNode {
private static boolean shouldPushDownConjunct(TOdbcTableType tableType, Expr expr) {
if (containsFunctionCallExpr(expr)) {
- if (tableType.equals(TOdbcTableType.MYSQL) || tableType.equals(TOdbcTableType.CLICKHOUSE)) {
+ if (tableType.equals(TOdbcTableType.MYSQL) || tableType.equals(TOdbcTableType.CLICKHOUSE)
+ || tableType.equals(TOdbcTableType.ORACLE)) {
return Config.enable_func_pushdown;
} else {
return false;
diff --git a/regression-test/suites/external_table_p0/jdbc/test_oracle_jdbc_catalog.groovy b/regression-test/suites/external_table_p0/jdbc/test_oracle_jdbc_catalog.groovy
index 40487cccae5..a954d056eb3 100644
--- a/regression-test/suites/external_table_p0/jdbc/test_oracle_jdbc_catalog.groovy
+++ b/regression-test/suites/external_table_p0/jdbc/test_oracle_jdbc_catalog.groovy
@@ -134,6 +134,12 @@ suite("test_oracle_jdbc_catalog", "p0,external,oracle,external_docker,external_d
order_qt_date6 """ select * from TEST_DATE where (T1 < '2022-01-22 00:00:00' or T1 > '2022-01-20 00:00:00') and (T1 < '2022-01-23 00:00:00' or T1 > '2022-01-19 00:00:00'); """
order_qt_date7 """select * from TEST_TIMESTAMP where T2 < str_to_date('2020-12-21 12:34:56', '%Y-%m-%d %H:%i:%s');"""
+ // test nvl
+ explain {
+ sql("SELECT * FROM STUDENT WHERE nvl(score, 0) < 95;")
+ contains """SELECT "ID", "NAME", "AGE", "SCORE" FROM "DORIS_TEST"."STUDENT" WHERE (nvl("SCORE", 0.0) < 95.0)"""
+ }
+
// for old planner
order_qt_filter4_old_plan """ select /*+ SET_VAR(enable_nereids_planner=false) */ * from STUDENT where NAME NOT like '%bob%' order by ID; """
order_qt_filter5_old_plan """ select /*+ SET_VAR(enable_nereids_planner=false) */ * from STUDENT where NAME NOT like '%bob%' or NAME NOT LIKE '%jerry%' order by ID; """
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org