This is an automated email from the ASF dual-hosted git repository.
zykkk pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 2daad2151d [enhancement](jdbc catalog) Add mysql jdbc catalog function
to filter push-down identification (#21745)
2daad2151d is described below
commit 2daad2151d021d1754be031f0b23b38631d89644
Author: zy-kkk <zhongy...@gmail.com>
AuthorDate: Wed Jul 19 23:48:23 2023 +0800
[enhancement](jdbc catalog) Add mysql jdbc catalog function to filter
push-down identification (#21745)
---
docs/en/docs/lakehouse/multi-catalog/jdbc.md | 9 +++-
docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md | 9 +++-
.../apache/doris/analysis/NativeInsertStmt.java | 2 +-
.../glue/translator/PhysicalPlanTranslator.java | 2 +-
.../java/org/apache/doris/planner/DataSink.java | 1 +
.../apache/doris/planner/DistributedPlanner.java | 2 +
.../apache/doris/planner/SingleNodePlanner.java | 2 +
.../external/jdbc/JdbcFunctionPushDownRule.java | 49 ++++++++++++++++++++++
.../planner/{ => external/jdbc}/JdbcScanNode.java | 6 ++-
.../planner/{ => external/jdbc}/JdbcTableSink.java | 5 ++-
.../planner/{ => external/odbc}/OdbcScanNode.java | 4 +-
.../planner/{ => external/odbc}/OdbcTableSink.java | 5 ++-
.../jdbc_catalog_p0/test_mysql_jdbc_catalog.out | 6 +++
.../jdbc_catalog_p0/test_mysql_jdbc_catalog.groovy | 8 ++--
14 files changed, 99 insertions(+), 11 deletions(-)
diff --git a/docs/en/docs/lakehouse/multi-catalog/jdbc.md
b/docs/en/docs/lakehouse/multi-catalog/jdbc.md
index 70bd3b471f..7a1a73f9d7 100644
--- a/docs/en/docs/lakehouse/multi-catalog/jdbc.md
+++ b/docs/en/docs/lakehouse/multi-catalog/jdbc.md
@@ -89,7 +89,14 @@ In some cases, the keywords in the database might be used as
the field names. Fo
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.
+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.
+
+Functions that are currently not pushed down include:
+
+| MYSQL |
+|:------------:|
+| DATE_TRUNC |
+| MONEY_FORMAT |
### Line Limit
diff --git a/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md
b/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md
index 3a92ad5677..7f87668431 100644
--- a/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md
+++ b/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md
@@ -89,7 +89,14 @@ 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。
+2. 当 `enable_func_pushdown` 设置为true，会将 where 之后的函数条件也下推到外部数据源，目前仅支持 MySQL，如遇到
MySQL 不支持的函数，可以将此参数设置为 false，目前 Doris 会自动识别部分 MySQL 不支持的函数进行下推条件过滤，可通过 explain
sql 查看。
+
+目前不会下推的函数有：
+
+| MYSQL |
+|:------------:|
+| DATE_TRUNC |
+| MONEY_FORMAT |
### 行数限制
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/analysis/NativeInsertStmt.java
b/fe/fe-core/src/main/java/org/apache/doris/analysis/NativeInsertStmt.java
index 2a05ee9e01..8323ff5cf7 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/NativeInsertStmt.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/NativeInsertStmt.java
@@ -47,8 +47,8 @@ import org.apache.doris.mysql.privilege.PrivPredicate;
import org.apache.doris.planner.DataPartition;
import org.apache.doris.planner.DataSink;
import org.apache.doris.planner.ExportSink;
-import org.apache.doris.planner.JdbcTableSink;
import org.apache.doris.planner.OlapTableSink;
+import org.apache.doris.planner.external.jdbc.JdbcTableSink;
import org.apache.doris.qe.ConnectContext;
import org.apache.doris.rewrite.ExprRewriter;
import org.apache.doris.service.FrontendOptions;
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/PhysicalPlanTranslator.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/PhysicalPlanTranslator.java
index 1f5f90efc1..f1dacc32e6 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/PhysicalPlanTranslator.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/PhysicalPlanTranslator.java
@@ -137,7 +137,6 @@ import org.apache.doris.planner.ExchangeNode;
import org.apache.doris.planner.HashJoinNode;
import org.apache.doris.planner.HashJoinNode.DistributionMode;
import org.apache.doris.planner.IntersectNode;
-import org.apache.doris.planner.JdbcScanNode;
import org.apache.doris.planner.JoinNodeBase;
import org.apache.doris.planner.MultiCastDataSink;
import org.apache.doris.planner.MultiCastPlanFragment;
@@ -160,6 +159,7 @@ import org.apache.doris.planner.UnionNode;
import org.apache.doris.planner.external.HiveScanNode;
import org.apache.doris.planner.external.hudi.HudiScanNode;
import org.apache.doris.planner.external.iceberg.IcebergScanNode;
+import org.apache.doris.planner.external.jdbc.JdbcScanNode;
import org.apache.doris.planner.external.paimon.PaimonScanNode;
import org.apache.doris.qe.ConnectContext;
import org.apache.doris.system.SystemInfoService;
diff --git a/fe/fe-core/src/main/java/org/apache/doris/planner/DataSink.java
b/fe/fe-core/src/main/java/org/apache/doris/planner/DataSink.java
index 3e0ff32d06..839301e509 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/planner/DataSink.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/planner/DataSink.java
@@ -24,6 +24,7 @@ import org.apache.doris.catalog.MysqlTable;
import org.apache.doris.catalog.OdbcTable;
import org.apache.doris.catalog.Table;
import org.apache.doris.common.AnalysisException;
+import org.apache.doris.planner.external.odbc.OdbcTableSink;
import org.apache.doris.thrift.TDataSink;
import org.apache.doris.thrift.TExplainLevel;
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/planner/DistributedPlanner.java
b/fe/fe-core/src/main/java/org/apache/doris/planner/DistributedPlanner.java
index 6a73952e20..2c236f5f72 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/planner/DistributedPlanner.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/planner/DistributedPlanner.java
@@ -38,6 +38,8 @@ import org.apache.doris.catalog.Table;
import org.apache.doris.common.AnalysisException;
import org.apache.doris.common.Pair;
import org.apache.doris.common.UserException;
+import org.apache.doris.planner.external.jdbc.JdbcScanNode;
+import org.apache.doris.planner.external.odbc.OdbcScanNode;
import org.apache.doris.qe.ConnectContext;
import org.apache.doris.qe.SessionVariable;
import org.apache.doris.thrift.TPartitionType;
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/planner/SingleNodePlanner.java
b/fe/fe-core/src/main/java/org/apache/doris/planner/SingleNodePlanner.java
index eda6c04795..9e041302c2 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/planner/SingleNodePlanner.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/planner/SingleNodePlanner.java
@@ -73,6 +73,8 @@ import org.apache.doris.planner.external.HiveScanNode;
import org.apache.doris.planner.external.MaxComputeScanNode;
import org.apache.doris.planner.external.hudi.HudiScanNode;
import org.apache.doris.planner.external.iceberg.IcebergScanNode;
+import org.apache.doris.planner.external.jdbc.JdbcScanNode;
+import org.apache.doris.planner.external.odbc.OdbcScanNode;
import org.apache.doris.planner.external.paimon.PaimonScanNode;
import org.apache.doris.qe.ConnectContext;
import org.apache.doris.rewrite.mvrewrite.MVSelectFailedException;
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
new file mode 100644
index 0000000000..bac9d62dbe
--- /dev/null
+++
b/fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcFunctionPushDownRule.java
@@ -0,0 +1,49 @@
+// 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.
+
+package org.apache.doris.planner.external.jdbc;
+
+import org.apache.doris.thrift.TOdbcTableType;
+
+import java.util.TreeSet;
+
+public class JdbcFunctionPushDownRule {
+ private static final TreeSet<String> UNSUPPORTED_MYSQL_FUNCTIONS = new
TreeSet<>(String.CASE_INSENSITIVE_ORDER);
+
+ static {
+ UNSUPPORTED_MYSQL_FUNCTIONS.add("date_trunc");
+ UNSUPPORTED_MYSQL_FUNCTIONS.add("money_format");
+ }
+
+ public static boolean isUnsupportedFunctions(TOdbcTableType tableType,
String filter) {
+ if (tableType.equals(TOdbcTableType.MYSQL)) {
+ return isMySQLUnsupportedFunctions(filter);
+ } else {
+ return false;
+ }
+ }
+
+ private static boolean isMySQLUnsupportedFunctions(String filter) {
+ for (String func : UNSUPPORTED_MYSQL_FUNCTIONS) {
+ if (filter.contains(func)) {
+ return true;
+ }
+ }
+ return false;
+ }
+}
+
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/planner/JdbcScanNode.java
b/fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcScanNode.java
similarity index 98%
rename from fe/fe-core/src/main/java/org/apache/doris/planner/JdbcScanNode.java
rename to
fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcScanNode.java
index 86cadc847d..3bcbfb8b4d 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/planner/JdbcScanNode.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcScanNode.java
@@ -15,7 +15,7 @@
// specific language governing permissions and limitations
// under the License.
-package org.apache.doris.planner;
+package org.apache.doris.planner.external.jdbc;
import org.apache.doris.analysis.Analyzer;
import org.apache.doris.analysis.BinaryPredicate;
@@ -36,6 +36,7 @@ import org.apache.doris.common.AnalysisException;
import org.apache.doris.common.Config;
import org.apache.doris.common.UserException;
import org.apache.doris.nereids.glue.translator.PlanTranslatorContext;
+import org.apache.doris.planner.PlanNodeId;
import org.apache.doris.planner.external.ExternalScanNode;
import org.apache.doris.statistics.StatisticalType;
import org.apache.doris.statistics.StatsRecursiveDerive;
@@ -137,6 +138,9 @@ public class JdbcScanNode extends ExternalScanNode {
if (filter.equals("TRUE")) {
filter = "1 = 1";
}
+ if (JdbcFunctionPushDownRule.isUnsupportedFunctions(jdbcType,
filter)) {
+ continue;
+ }
filters.add(filter);
conjuncts.remove(p);
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/planner/JdbcTableSink.java
b/fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcTableSink.java
similarity index 96%
rename from fe/fe-core/src/main/java/org/apache/doris/planner/JdbcTableSink.java
rename to
fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcTableSink.java
index ae99a53997..263860f1e7 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/planner/JdbcTableSink.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcTableSink.java
@@ -15,9 +15,12 @@
// specific language governing permissions and limitations
// under the License.
-package org.apache.doris.planner;
+package org.apache.doris.planner.external.jdbc;
import org.apache.doris.catalog.JdbcTable;
+import org.apache.doris.planner.DataPartition;
+import org.apache.doris.planner.DataSink;
+import org.apache.doris.planner.PlanNodeId;
import org.apache.doris.qe.ConnectContext;
import org.apache.doris.thrift.TDataSink;
import org.apache.doris.thrift.TDataSinkType;
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/planner/OdbcScanNode.java
b/fe/fe-core/src/main/java/org/apache/doris/planner/external/odbc/OdbcScanNode.java
similarity index 98%
rename from fe/fe-core/src/main/java/org/apache/doris/planner/OdbcScanNode.java
rename to
fe/fe-core/src/main/java/org/apache/doris/planner/external/odbc/OdbcScanNode.java
index 07b2d3775b..2bac81218f 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/planner/OdbcScanNode.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/planner/external/odbc/OdbcScanNode.java
@@ -15,7 +15,7 @@
// specific language governing permissions and limitations
// under the License.
-package org.apache.doris.planner;
+package org.apache.doris.planner.external.odbc;
import org.apache.doris.analysis.Analyzer;
import org.apache.doris.analysis.Expr;
@@ -29,7 +29,9 @@ import org.apache.doris.catalog.JdbcTable;
import org.apache.doris.catalog.OdbcTable;
import org.apache.doris.common.AnalysisException;
import org.apache.doris.common.UserException;
+import org.apache.doris.planner.PlanNodeId;
import org.apache.doris.planner.external.ExternalScanNode;
+import org.apache.doris.planner.external.jdbc.JdbcScanNode;
import org.apache.doris.statistics.StatisticalType;
import org.apache.doris.statistics.StatsRecursiveDerive;
import org.apache.doris.statistics.query.StatsDelta;
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/planner/OdbcTableSink.java
b/fe/fe-core/src/main/java/org/apache/doris/planner/external/odbc/OdbcTableSink.java
similarity index 94%
rename from fe/fe-core/src/main/java/org/apache/doris/planner/OdbcTableSink.java
rename to
fe/fe-core/src/main/java/org/apache/doris/planner/external/odbc/OdbcTableSink.java
index 0fbe85ea32..8778f06026 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/planner/OdbcTableSink.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/planner/external/odbc/OdbcTableSink.java
@@ -15,10 +15,13 @@
// specific language governing permissions and limitations
// under the License.
-package org.apache.doris.planner;
+package org.apache.doris.planner.external.odbc;
import org.apache.doris.catalog.JdbcTable;
import org.apache.doris.catalog.OdbcTable;
+import org.apache.doris.planner.DataPartition;
+import org.apache.doris.planner.DataSink;
+import org.apache.doris.planner.PlanNodeId;
import org.apache.doris.qe.ConnectContext;
import org.apache.doris.thrift.TDataSink;
import org.apache.doris.thrift.TDataSinkType;
diff --git a/regression-test/data/jdbc_catalog_p0/test_mysql_jdbc_catalog.out
b/regression-test/data/jdbc_catalog_p0/test_mysql_jdbc_catalog.out
index f04589e6a2..7177d429e8 100644
--- a/regression-test/data/jdbc_catalog_p0/test_mysql_jdbc_catalog.out
+++ b/regression-test/data/jdbc_catalog_p0/test_mysql_jdbc_catalog.out
@@ -255,6 +255,12 @@ VIEWS
-- !filter3 --
1 6 1 1 2099.18 3 8 1554296.82
68781940.49 d 8 5 0 d a 7 9
+-- !date_trunc --
+2023-06-17T10:00
+
+-- !money_format --
+1
+
-- !test_insert1 --
doris1 18
diff --git
a/regression-test/suites/jdbc_catalog_p0/test_mysql_jdbc_catalog.groovy
b/regression-test/suites/jdbc_catalog_p0/test_mysql_jdbc_catalog.groovy
index 28f8bfa4a7..2e2da519c6 100644
--- a/regression-test/suites/jdbc_catalog_p0/test_mysql_jdbc_catalog.groovy
+++ b/regression-test/suites/jdbc_catalog_p0/test_mysql_jdbc_catalog.groovy
@@ -62,7 +62,7 @@ suite("test_mysql_jdbc_catalog", "p0") {
"driver_url" =
"https://doris-community-test-1308700295.cos.ap-hongkong.myqcloud.com/jdbc_driver/mysql-connector-java-8.0.25.jar";,
"driver_class" = "com.mysql.cj.jdbc.Driver"
);"""
-
+
sql """ drop table if exists ${inDorisTable} """
sql """
CREATE TABLE ${inDorisTable} (
@@ -108,6 +108,8 @@ suite("test_mysql_jdbc_catalog", "p0") {
order_qt_filter1 """select * from ${ex_tb17} where id = 1; """
order_qt_filter2 """select * from ${ex_tb17} where 1=1 order by 1; """
order_qt_filter3 """select * from ${ex_tb17} where id = 1 and 1 = 1;
"""
+ order_qt_date_trunc """ SELECT timestamp0 from dt where
DATE_TRUNC(date_sub(timestamp0,INTERVAL 9 HOUR),'hour') > '2011-03-03
17:39:05'; """
+ order_qt_money_format """ select k8 from test1 where money_format(k8)
= '1.00'; """
// test insert
String uuid1 = UUID.randomUUID().toString();
@@ -138,7 +140,7 @@ suite("test_mysql_jdbc_catalog", "p0") {
"driver_class" = "com.mysql.cj.jdbc.Driver",
"only_specified_database" = "true"
);"""
-
+
sql """switch ${catalog_name}"""
qt_specified_database_1 """ show databases; """
@@ -156,7 +158,7 @@ suite("test_mysql_jdbc_catalog", "p0") {
"only_specified_database" = "true",
"include_database_list" = "doris_test"
);"""
-
+
sql """switch ${catalog_name}"""
qt_specified_database_2 """ show databases; """
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
