You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by mo...@apache.org on 2022/12/01 07:00:16 UTC
[doris] 01/10: [feature](multi-catalog) support Jdbc catalog (#14527)
This is an automated email from the ASF dual-hosted git repository.
morningman pushed a commit to branch branch-1.2-unstable
in repository https://gitbox.apache.org/repos/asf/doris.git
commit 05b875f5218169022c35dda14366a2be8f8b9fe8
Author: Tiewei Fang <43...@users.noreply.github.com>
AuthorDate: Wed Nov 30 11:28:08 2022 +0800
[feature](multi-catalog) support Jdbc catalog (#14527)
Issue Number: close #xxx
I add jdbc catalog for doris multi-catalog feature.
Currently, the jdbc catalog only supports MYSQL DBMS.
TODO:
support for postgre DB
Support for other databases.
Problem summary
For jdbc catalog, we can create catalog like:
CREATE CATALOG jdbc4 PROPERTIES (
"type"="jdbc",
"jdbc.user"="root",
"jdbc.password"="123456",
"jdbc.jdbc_url" = "jdbc:mysql://127.0.0.1:13396/demo?yearIsDateType=false",
"jdbc.driver_url" = "file:/mnt/disk2/ftw/tools/jar/mysql-connector-java-5.1.47/mysql-connector-java-5.1.47.jar",
"jdbc.driver_class" = "com.mysql.jdbc.Driver"
);
Note:
yearIsDateType is a param of jdbc:
If yearIsDateType configuration property is set to false, then the returned object type is java.sql.Short. If set to true (the default), then the returned object is of type java.sql.Date with the date set to January 1st, at midnight.
To compat with mysql, we force the use of yearIsDateType=false in FE. if user sets yearIsDateType=true, doris FE will force to change yearIsDateType=false.
---
be/src/vec/exec/vjdbc_connector.cpp | 15 +-
.../docker-compose/mysql/init/03-create-table.sql | 30 ++
.../docker-compose/mysql/init/04-insert.sql | 7 +-
.../java/org/apache/doris/catalog/JdbcTable.java | 10 +-
.../java/org/apache/doris/catalog/OdbcTable.java | 12 +-
.../java/org/apache/doris/catalog/TableIf.java | 4 +-
.../doris/catalog/external/EsExternalDatabase.java | 10 +-
.../doris/catalog/external/EsExternalTable.java | 7 -
.../doris/catalog/external/HMSExternalTable.java | 7 -
...rnalDatabase.java => JdbcExternalDatabase.java} | 99 ++---
.../doris/catalog/external/JdbcExternalTable.java | 89 +++++
.../apache/doris/datasource/CatalogFactory.java | 3 +
.../org/apache/doris/datasource/CatalogIf.java | 4 +
.../org/apache/doris/datasource/CatalogMgr.java | 1 +
.../apache/doris/datasource/EsExternalCatalog.java | 36 +-
.../apache/doris/datasource/ExternalCatalog.java | 31 +-
.../doris/datasource/HMSExternalCatalog.java | 30 --
.../apache/doris/datasource/InitCatalogLog.java | 1 +
.../apache/doris/datasource/InitDatabaseLog.java | 1 +
.../doris/datasource/JdbcExternalCatalog.java | 175 ++++++++
.../org/apache/doris/external/jdbc/JdbcClient.java | 441 +++++++++++++++++++++
.../doris/external/jdbc/JdbcClientException.java | 28 ++
.../main/java/org/apache/doris/load/ExportJob.java | 3 +-
.../org/apache/doris/planner/JdbcScanNode.java | 14 +
.../apache/doris/planner/SingleNodePlanner.java | 6 +-
.../java/org/apache/doris/udf/JdbcExecutor.java | 3 -
.../jdbc_catalog_p0/test_mysql_jdbc_catalog.out | 154 +++++++
.../jdbc_catalog_p0/test_mysql_jdbc_catalog.groovy | 103 +++++
28 files changed, 1177 insertions(+), 147 deletions(-)
diff --git a/be/src/vec/exec/vjdbc_connector.cpp b/be/src/vec/exec/vjdbc_connector.cpp
index 8af1159361..f3a7688d38 100644
--- a/be/src/vec/exec/vjdbc_connector.cpp
+++ b/be/src/vec/exec/vjdbc_connector.cpp
@@ -113,9 +113,18 @@ Status JdbcConnector::open(RuntimeState* state, bool read) {
std::string local_location;
std::hash<std::string> hash_str;
auto function_cache = UserFunctionCache::instance();
- RETURN_IF_ERROR(function_cache->get_jarpath(
- std::abs((int64_t)hash_str(_conn_param.resource_name)), _conn_param.driver_path,
- _conn_param.driver_checksum, &local_location));
+ if (_conn_param.resource_name.empty()) {
+ // for jdbcExternalTable, _conn_param.resource_name == ""
+ // so, we use _conn_param.driver_path as key of jarpath
+ RETURN_IF_ERROR(function_cache->get_jarpath(
+ std::abs((int64_t)hash_str(_conn_param.driver_path)), _conn_param.driver_path,
+ _conn_param.driver_checksum, &local_location));
+ } else {
+ RETURN_IF_ERROR(function_cache->get_jarpath(
+ std::abs((int64_t)hash_str(_conn_param.resource_name)), _conn_param.driver_path,
+ _conn_param.driver_checksum, &local_location));
+ }
+
TJdbcExecutorCtorParams ctor_params;
ctor_params.__set_statement(_sql_str);
ctor_params.__set_jdbc_url(_conn_param.jdbc_url);
diff --git a/docker/thirdparties/docker-compose/mysql/init/03-create-table.sql b/docker/thirdparties/docker-compose/mysql/init/03-create-table.sql
index d09fc95f63..b0e4890013 100644
--- a/docker/thirdparties/docker-compose/mysql/init/03-create-table.sql
+++ b/docker/thirdparties/docker-compose/mysql/init/03-create-table.sql
@@ -184,4 +184,34 @@ CREATE TABLE doris_test.ex_tb17 (
`order_source` tinyint(4) NULL
);
+create table ex_tb18 (
+ num_tinyint tinyint,
+ num_tinyint2 tinyint unsigned,
+ num_smallint SMALLINT,
+ num_smallint2 SMALLINT unsigned,
+ num_mediumint MEDIUMINT,
+ num_mediumint2 MEDIUMINT unsigned,
+ num_bigint BIGINT,
+ num_int int(5),
+ num_int2 int(5) unsigned,
+ num_int3 int(5) unsigned zerofill,
+ num_float float(5, 2),
+ num_double double(10, 3),
+ num_decimal decimal(20, 2),
+ char_value1 char(5),
+ char_value2 char(100),
+ varchar_value1 varchar(5),
+ varchar_value2 varchar(10),
+ varchar_value3 varchar(100),
+ text_value TEXT(123)
+) engine=innodb charset=utf8;
+
+create table ex_tb19 (
+ date_value date,
+ time_value time,
+ year_value year,
+ datetime_value datetime,
+ timestamp_value timestamp
+) engine=innodb charset=utf8;
+
diff --git a/docker/thirdparties/docker-compose/mysql/init/04-insert.sql b/docker/thirdparties/docker-compose/mysql/init/04-insert.sql
index 5f6bc34c59..684a4137e2 100644
--- a/docker/thirdparties/docker-compose/mysql/init/04-insert.sql
+++ b/docker/thirdparties/docker-compose/mysql/init/04-insert.sql
@@ -1112,5 +1112,10 @@ INSERT INTO doris_test.ex_tb17 (id,media_order_id,supplier_id,agent_policy_type,
(7,3,2,8,5297.81,9,3,23753694.2,96930000.64,'c',7,2,0,'b','e',1,5), (9,3,9,1,4785.38,1,5,95199488.12,94869703.42,'a',4,4,0,'c','d',2,4),
(5,6,4,5,9137.82,2,7,26526675.7,90098303.36,'a',6,7,0,'d','e',4,1);
+INSERT INTO ex_tb18 VALUES
+(1,1,1,1,1,1,1,1,1,1,3.14,13.141,2342.23,'aa','asdawdasdaasdasd','aaa','bbbbbbbb','xaqwdqwdqwdqdwqwdqwdqd','asdadwqdqwddqwdsadqwdas'),
+(127,255,32767, 65535, 8388607, 16777215, 9223372036854775807, -2147483648, 2147483647, 4294967295,33.1415926,422113.1411231,2342.23123123,'aa','asdawdasdaasdasd','aaa','bbbbbbbb','xaqwdqwdqwdqd','asdadwqdqwdsadqwdas'),
+(-128,255,-32768, 65535, -8388608, 16777215, -9223372036854775808, -2147483648, 2147483647, 4294967295,33.1415926,422113.1411231,2342.23123123,'aa','asdawdasdaasdasd','aaa','bbbbbbbb','xaqwdqwdqwdqd','asdas');
-
+INSERT INTO ex_tb19 VALUES
+('2022-11-27', '07:09:51', '2022', '2022-11-27 07:09:51', '2022-11-27 07:09:51');
diff --git a/fe/fe-core/src/main/java/org/apache/doris/catalog/JdbcTable.java b/fe/fe-core/src/main/java/org/apache/doris/catalog/JdbcTable.java
index bba585904b..c2851bb03a 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/catalog/JdbcTable.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/catalog/JdbcTable.java
@@ -29,7 +29,9 @@ import org.apache.doris.thrift.TTableType;
import com.google.common.base.Strings;
import com.google.common.collect.Maps;
+import lombok.Setter;
import org.apache.commons.codec.digest.DigestUtils;
+import org.apache.commons.collections.map.CaseInsensitiveMap;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
@@ -37,10 +39,10 @@ import java.io.DataInput;
import java.io.DataOutput;
import java.io.IOException;
import java.util.Collections;
-import java.util.HashMap;
import java.util.List;
import java.util.Map;
+@Setter
public class JdbcTable extends Table {
private static final Logger LOG = LogManager.getLogger(JdbcTable.class);
@@ -66,7 +68,7 @@ public class JdbcTable extends Table {
private String checkSum;
static {
- Map<String, TOdbcTableType> tempMap = new HashMap<>();
+ Map<String, TOdbcTableType> tempMap = new CaseInsensitiveMap();
tempMap.put("mysql", TOdbcTableType.MYSQL);
tempMap.put("postgresql", TOdbcTableType.POSTGRESQL);
tempMap.put("sqlserver", TOdbcTableType.SQLSERVER);
@@ -85,6 +87,10 @@ public class JdbcTable extends Table {
validate(properties);
}
+ public JdbcTable(long id, String name, List<Column> schema, TableType type) {
+ super(id, name, type, schema);
+ }
+
public String getCheckSum() {
return checkSum;
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/catalog/OdbcTable.java b/fe/fe-core/src/main/java/org/apache/doris/catalog/OdbcTable.java
index 35e55bb9e4..b2464c257f 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/catalog/OdbcTable.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/catalog/OdbcTable.java
@@ -74,7 +74,17 @@ public class OdbcTable extends Table {
// For different databases, special characters need to be escaped
private static String mysqlProperName(String name) {
- return "`" + name + "`";
+ // In JdbcExternalTable, the name contains databaseName, like: db.table
+ // So, we should split db and table, then switch to `db`.`table`.
+ String[] fields = name.split("\\.");
+ String result = "";
+ for (int i = 0; i < fields.length; ++i) {
+ if (i != 0) {
+ result += ".";
+ }
+ result += ("`" + fields[i] + "`");
+ }
+ return result;
}
private static String mssqlProperName(String name) {
diff --git a/fe/fe-core/src/main/java/org/apache/doris/catalog/TableIf.java b/fe/fe-core/src/main/java/org/apache/doris/catalog/TableIf.java
index 9b831bb524..361ef44ba2 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/catalog/TableIf.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/catalog/TableIf.java
@@ -118,7 +118,7 @@ public interface TableIf {
*/
enum TableType {
MYSQL, ODBC, OLAP, SCHEMA, INLINE_VIEW, VIEW, BROKER, ELASTICSEARCH, HIVE, ICEBERG, HUDI, JDBC,
- TABLE_VALUED_FUNCTION, HMS_EXTERNAL_TABLE, ES_EXTERNAL_TABLE, MATERIALIZED_VIEW;
+ TABLE_VALUED_FUNCTION, HMS_EXTERNAL_TABLE, ES_EXTERNAL_TABLE, MATERIALIZED_VIEW, JDBC_EXTERNAL_TABLE;
public String toEngineName() {
switch (this) {
@@ -143,6 +143,7 @@ public interface TableIf {
case HUDI:
return "Hudi";
case JDBC:
+ case JDBC_EXTERNAL_TABLE:
return "jdbc";
case TABLE_VALUED_FUNCTION:
return "Table_Valued_Function";
@@ -171,6 +172,7 @@ public interface TableIf {
case HIVE:
case HUDI:
case JDBC:
+ case JDBC_EXTERNAL_TABLE:
case TABLE_VALUED_FUNCTION:
case HMS_EXTERNAL_TABLE:
case ES_EXTERNAL_TABLE:
diff --git a/fe/fe-core/src/main/java/org/apache/doris/catalog/external/EsExternalDatabase.java b/fe/fe-core/src/main/java/org/apache/doris/catalog/external/EsExternalDatabase.java
index e9b3ce354b..bd0e322c5f 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/catalog/external/EsExternalDatabase.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/catalog/external/EsExternalDatabase.java
@@ -23,14 +23,14 @@ import org.apache.doris.datasource.ExternalCatalog;
import org.apache.doris.datasource.InitDatabaseLog;
import org.apache.doris.persist.gson.GsonPostProcessable;
+import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
+import com.google.common.collect.Sets;
import com.google.gson.annotations.SerializedName;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import java.io.IOException;
-import java.util.ArrayList;
-import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
@@ -118,14 +118,14 @@ public class EsExternalDatabase extends ExternalDatabase<EsExternalTable> implem
@Override
public Set<String> getTableNamesWithLock() {
- // Doesn't need to lock because everytime we call the hive metastore api to get table names.
- return new HashSet<>(extCatalog.listTableNames(null, name));
+ makeSureInitialized();
+ return Sets.newHashSet(tableNameToId.keySet());
}
@Override
public List<EsExternalTable> getTables() {
makeSureInitialized();
- return new ArrayList<>(idToTbl.values());
+ return Lists.newArrayList(idToTbl.values());
}
@Override
diff --git a/fe/fe-core/src/main/java/org/apache/doris/catalog/external/EsExternalTable.java b/fe/fe-core/src/main/java/org/apache/doris/catalog/external/EsExternalTable.java
index e2efd6aae1..ca085d4720 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/catalog/external/EsExternalTable.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/catalog/external/EsExternalTable.java
@@ -66,13 +66,6 @@ public class EsExternalTable extends ExternalTable {
return type.name();
}
- /**
- * get database name of es table.
- */
- public String getDbName() {
- return dbName;
- }
-
@Override
public TTableDescriptor toThrift() {
List<Column> schema = getFullSchema();
diff --git a/fe/fe-core/src/main/java/org/apache/doris/catalog/external/HMSExternalTable.java b/fe/fe-core/src/main/java/org/apache/doris/catalog/external/HMSExternalTable.java
index 4b09ca70b5..bfefe1f829 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/catalog/external/HMSExternalTable.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/catalog/external/HMSExternalTable.java
@@ -250,13 +250,6 @@ public class HMSExternalTable extends ExternalTable {
return 0;
}
- /**
- * get database name of hms table.
- */
- public String getDbName() {
- return dbName;
- }
-
/**
* get the dla type for scan node to get right information.
*/
diff --git a/fe/fe-core/src/main/java/org/apache/doris/catalog/external/EsExternalDatabase.java b/fe/fe-core/src/main/java/org/apache/doris/catalog/external/JdbcExternalDatabase.java
similarity index 70%
copy from fe/fe-core/src/main/java/org/apache/doris/catalog/external/EsExternalDatabase.java
copy to fe/fe-core/src/main/java/org/apache/doris/catalog/external/JdbcExternalDatabase.java
index e9b3ce354b..45d6b1a647 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/catalog/external/EsExternalDatabase.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/catalog/external/JdbcExternalDatabase.java
@@ -18,93 +18,67 @@
package org.apache.doris.catalog.external;
import org.apache.doris.catalog.Env;
-import org.apache.doris.datasource.EsExternalCatalog;
import org.apache.doris.datasource.ExternalCatalog;
import org.apache.doris.datasource.InitDatabaseLog;
+import org.apache.doris.datasource.JdbcExternalCatalog;
import org.apache.doris.persist.gson.GsonPostProcessable;
+import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
+import com.google.common.collect.Sets;
import com.google.gson.annotations.SerializedName;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import java.io.IOException;
-import java.util.ArrayList;
-import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.concurrent.locks.ReentrantReadWriteLock;
-/**
- * Elasticsearch metastore external database.
- */
-public class EsExternalDatabase extends ExternalDatabase<EsExternalTable> implements GsonPostProcessable {
- private static final Logger LOG = LogManager.getLogger(EsExternalDatabase.class);
+public class JdbcExternalDatabase extends ExternalDatabase<JdbcExternalTable> implements GsonPostProcessable {
+ private static final Logger LOG = LogManager.getLogger(JdbcExternalDatabase.class);
// Cache of table name to table id.
private Map<String, Long> tableNameToId = Maps.newConcurrentMap();
@SerializedName(value = "idToTbl")
- private Map<Long, EsExternalTable> idToTbl = Maps.newConcurrentMap();
+ private Map<Long, JdbcExternalTable> idToTbl = Maps.newConcurrentMap();
/**
- * Create Elasticsearch external database.
+ * Create Jdbc external database.
*
* @param extCatalog External data source this database belongs to.
* @param id database id.
* @param name database name.
*/
- public EsExternalDatabase(ExternalCatalog extCatalog, long id, String name) {
+ public JdbcExternalDatabase(ExternalCatalog extCatalog, long id, String name) {
super(extCatalog, id, name);
}
- public void replayInitDb(InitDatabaseLog log, ExternalCatalog catalog) {
- Map<String, Long> tmpTableNameToId = Maps.newConcurrentMap();
- Map<Long, EsExternalTable> tmpIdToTbl = Maps.newConcurrentMap();
- for (int i = 0; i < log.getRefreshCount(); i++) {
- EsExternalTable table = getTableForReplay(log.getRefreshTableIds().get(i));
- tmpTableNameToId.put(table.getName(), table.getId());
- tmpIdToTbl.put(table.getId(), table);
- }
- for (int i = 0; i < log.getCreateCount(); i++) {
- EsExternalTable table = new EsExternalTable(log.getCreateTableIds().get(i),
- log.getCreateTableNames().get(i), name, (EsExternalCatalog) catalog);
- tmpTableNameToId.put(table.getName(), table.getId());
- tmpIdToTbl.put(table.getId(), table);
- }
- tableNameToId = tmpTableNameToId;
- idToTbl = tmpIdToTbl;
- initialized = true;
- }
-
- public void setTableExtCatalog(ExternalCatalog extCatalog) {
- for (EsExternalTable table : idToTbl.values()) {
- table.setCatalog(extCatalog);
- }
- }
-
+ // TODO(ftw): drew out the public multiple parts
@Override
protected void init() {
InitDatabaseLog initDatabaseLog = new InitDatabaseLog();
- initDatabaseLog.setType(InitDatabaseLog.Type.ES);
+ initDatabaseLog.setType(InitDatabaseLog.Type.JDBC);
initDatabaseLog.setCatalogId(extCatalog.getId());
initDatabaseLog.setDbId(id);
List<String> tableNames = extCatalog.listTableNames(null, name);
if (tableNames != null) {
Map<String, Long> tmpTableNameToId = Maps.newConcurrentMap();
- Map<Long, EsExternalTable> tmpIdToTbl = Maps.newHashMap();
+ Map<Long, JdbcExternalTable> tmpIdToTbl = Maps.newHashMap();
for (String tableName : tableNames) {
long tblId;
if (tableNameToId != null && tableNameToId.containsKey(tableName)) {
tblId = tableNameToId.get(tableName);
tmpTableNameToId.put(tableName, tblId);
- EsExternalTable table = idToTbl.get(tblId);
+ JdbcExternalTable table = idToTbl.get(tblId);
tmpIdToTbl.put(tblId, table);
initDatabaseLog.addRefreshTable(tblId);
} else {
tblId = Env.getCurrentEnv().getNextId();
tmpTableNameToId.put(tableName, tblId);
- EsExternalTable table = new EsExternalTable(tblId, tableName, name, (EsExternalCatalog) extCatalog);
+ JdbcExternalTable table = new JdbcExternalTable(tblId, tableName, name,
+ (JdbcExternalCatalog) extCatalog);
tmpIdToTbl.put(tblId, table);
initDatabaseLog.addCreateTable(tblId, tableName);
}
@@ -116,20 +90,47 @@ public class EsExternalDatabase extends ExternalDatabase<EsExternalTable> implem
Env.getCurrentEnv().getEditLog().logInitExternalDb(initDatabaseLog);
}
+ public void setTableExtCatalog(ExternalCatalog extCatalog) {
+ for (JdbcExternalTable table : idToTbl.values()) {
+ table.setCatalog(extCatalog);
+ }
+ }
+
+ public void replayInitDb(InitDatabaseLog log, ExternalCatalog catalog) {
+ Map<String, Long> tmpTableNameToId = Maps.newConcurrentMap();
+ Map<Long, JdbcExternalTable> tmpIdToTbl = Maps.newConcurrentMap();
+ for (int i = 0; i < log.getRefreshCount(); i++) {
+ JdbcExternalTable table = getTableForReplay(log.getRefreshTableIds().get(i));
+ tmpTableNameToId.put(table.getName(), table.getId());
+ tmpIdToTbl.put(table.getId(), table);
+ }
+ for (int i = 0; i < log.getCreateCount(); i++) {
+ JdbcExternalTable table = new JdbcExternalTable(log.getCreateTableIds().get(i),
+ log.getCreateTableNames().get(i), name, (JdbcExternalCatalog) catalog);
+ tmpTableNameToId.put(table.getName(), table.getId());
+ tmpIdToTbl.put(table.getId(), table);
+ }
+ tableNameToId = tmpTableNameToId;
+ idToTbl = tmpIdToTbl;
+ initialized = true;
+ }
+
+ // TODO(ftw): drew
@Override
public Set<String> getTableNamesWithLock() {
- // Doesn't need to lock because everytime we call the hive metastore api to get table names.
- return new HashSet<>(extCatalog.listTableNames(null, name));
+ makeSureInitialized();
+ return Sets.newHashSet(tableNameToId.keySet());
}
@Override
- public List<EsExternalTable> getTables() {
+ public List<JdbcExternalTable> getTables() {
makeSureInitialized();
- return new ArrayList<>(idToTbl.values());
+ return Lists.newArrayList(idToTbl.values());
}
+ // TODO(ftw): drew
@Override
- public EsExternalTable getTableNullable(String tableName) {
+ public JdbcExternalTable getTableNullable(String tableName) {
makeSureInitialized();
if (!tableNameToId.containsKey(tableName)) {
return null;
@@ -138,25 +139,25 @@ public class EsExternalDatabase extends ExternalDatabase<EsExternalTable> implem
}
@Override
- public EsExternalTable getTableNullable(long tableId) {
+ public JdbcExternalTable getTableNullable(long tableId) {
makeSureInitialized();
return idToTbl.get(tableId);
}
- public EsExternalTable getTableForReplay(long tableId) {
+ public JdbcExternalTable getTableForReplay(long tableId) {
return idToTbl.get(tableId);
}
@Override
public void gsonPostProcess() throws IOException {
tableNameToId = Maps.newConcurrentMap();
- for (EsExternalTable tbl : idToTbl.values()) {
+ for (JdbcExternalTable tbl : idToTbl.values()) {
tableNameToId.put(tbl.getName(), tbl.getId());
}
rwLock = new ReentrantReadWriteLock(true);
}
- public void addTableForTest(EsExternalTable tbl) {
+ public void addTableForTest(JdbcExternalTable tbl) {
idToTbl.put(tbl.getId(), tbl);
tableNameToId.put(tbl.getName(), tbl.getId());
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/catalog/external/JdbcExternalTable.java b/fe/fe-core/src/main/java/org/apache/doris/catalog/external/JdbcExternalTable.java
new file mode 100644
index 0000000000..b5ac609402
--- /dev/null
+++ b/fe/fe-core/src/main/java/org/apache/doris/catalog/external/JdbcExternalTable.java
@@ -0,0 +1,89 @@
+// 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.catalog.external;
+
+import org.apache.doris.catalog.Column;
+import org.apache.doris.catalog.JdbcTable;
+import org.apache.doris.datasource.JdbcExternalCatalog;
+import org.apache.doris.thrift.TTableDescriptor;
+
+import org.apache.logging.log4j.LogManager;
+import org.apache.logging.log4j.Logger;
+
+import java.util.List;
+
+/**
+ * Elasticsearch external table.
+ */
+public class JdbcExternalTable extends ExternalTable {
+ private static final Logger LOG = LogManager.getLogger(JdbcExternalTable.class);
+
+ private JdbcTable jdbcTable;
+
+ /**
+ * Create jdbc external table.
+ *
+ * @param id Table id.
+ * @param name Table name.
+ * @param dbName Database name.
+ * @param catalog HMSExternalDataSource.
+ */
+ public JdbcExternalTable(long id, String name, String dbName, JdbcExternalCatalog catalog) {
+ super(id, name, catalog, dbName, TableType.JDBC_EXTERNAL_TABLE);
+ }
+
+ protected synchronized void makeSureInitialized() {
+ if (!objectCreated) {
+ jdbcTable = toJdbcTable();
+ objectCreated = true;
+ }
+ }
+
+ public JdbcTable getJdbcTable() {
+ makeSureInitialized();
+ return jdbcTable;
+ }
+
+ @Override
+ public String getMysqlType() {
+ return type.name();
+ }
+
+ @Override
+ public TTableDescriptor toThrift() {
+ makeSureInitialized();
+ return jdbcTable.toThrift();
+ }
+
+ private JdbcTable toJdbcTable() {
+ List<Column> schema = getFullSchema();
+ JdbcExternalCatalog jdbcCatalog = (JdbcExternalCatalog) catalog;
+ String fullDbName = this.dbName + "." + this.name;
+ JdbcTable jdbcTable = new JdbcTable(this.id, fullDbName, schema, TableType.JDBC_EXTERNAL_TABLE);
+ jdbcTable.setExternalTableName(fullDbName);
+ jdbcTable.setJdbcTypeName(jdbcCatalog.getDatabaseTypeName());
+ jdbcTable.setJdbcUrl(jdbcCatalog.getJdbcUrl());
+ jdbcTable.setJdbcUser(jdbcCatalog.getJdbcUser());
+ jdbcTable.setJdbcPasswd(jdbcCatalog.getJdbcPasswd());
+ jdbcTable.setDriverClass(jdbcCatalog.getDriverClass());
+ jdbcTable.setDriverUrl(jdbcCatalog.getDriverUrl());
+ jdbcTable.setCheckSum(jdbcCatalog.getCheckSum());
+ return jdbcTable;
+ }
+
+}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/datasource/CatalogFactory.java b/fe/fe-core/src/main/java/org/apache/doris/datasource/CatalogFactory.java
index 25ff3ffa5f..da43241508 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/datasource/CatalogFactory.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/datasource/CatalogFactory.java
@@ -79,6 +79,9 @@ public class CatalogFactory {
validateEsCatalogProperties(props);
catalog = new EsExternalCatalog(catalogId, name, props);
break;
+ case "jdbc":
+ catalog = new JdbcExternalCatalog(catalogId, name, props);
+ break;
default:
throw new RuntimeException("Unknown catalog type: " + type);
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/datasource/CatalogIf.java b/fe/fe-core/src/main/java/org/apache/doris/datasource/CatalogIf.java
index 0a6aabc404..a22d72a361 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/datasource/CatalogIf.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/datasource/CatalogIf.java
@@ -111,4 +111,8 @@ public interface CatalogIf<T extends DatabaseIf> {
return getDbOrException(dbId,
s -> new AnalysisException(ErrorCode.ERR_BAD_DB_ERROR.formatErrorMsg(s), ErrorCode.ERR_BAD_DB_ERROR));
}
+
+ default void onClose() {
+
+ }
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/datasource/CatalogMgr.java b/fe/fe-core/src/main/java/org/apache/doris/datasource/CatalogMgr.java
index 018f8f9f0e..fbd6a75d2c 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/datasource/CatalogMgr.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/datasource/CatalogMgr.java
@@ -96,6 +96,7 @@ public class CatalogMgr implements Writable, GsonPostProcessable {
private CatalogIf removeCatalog(long catalogId) {
CatalogIf catalog = idToCatalog.remove(catalogId);
if (catalog != null) {
+ catalog.onClose();
nameToCatalog.remove(catalog.getName());
Env.getCurrentEnv().getExtMetaCacheMgr().removeCache(catalog.getName());
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/datasource/EsExternalCatalog.java b/fe/fe-core/src/main/java/org/apache/doris/datasource/EsExternalCatalog.java
index 1f8c79cc1d..496eabfc8c 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/datasource/EsExternalCatalog.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/datasource/EsExternalCatalog.java
@@ -21,8 +21,6 @@ package org.apache.doris.datasource;
import org.apache.doris.catalog.Column;
import org.apache.doris.catalog.Env;
import org.apache.doris.catalog.external.EsExternalDatabase;
-import org.apache.doris.catalog.external.ExternalDatabase;
-import org.apache.doris.cluster.ClusterNamespace;
import org.apache.doris.common.DdlException;
import org.apache.doris.external.elasticsearch.EsRestClient;
import org.apache.doris.external.elasticsearch.EsUtil;
@@ -33,7 +31,6 @@ import lombok.Getter;
import org.apache.commons.lang3.StringUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
-import org.jetbrains.annotations.Nullable;
import java.io.IOException;
import java.util.ArrayList;
@@ -159,25 +156,15 @@ public class EsExternalCatalog extends ExternalCatalog {
@Override
public List<String> listTableNames(SessionContext ctx, String dbName) {
- return esRestClient.listTable();
- }
-
- @Nullable
- @Override
- public ExternalDatabase getDbNullable(String dbName) {
makeSureInitialized();
- String realDbName = ClusterNamespace.getNameFromFullName(dbName);
- if (!dbNameToId.containsKey(realDbName)) {
- return null;
+ EsExternalDatabase db = (EsExternalDatabase) idToDb.get(dbNameToId.get(dbName));
+ if (db != null && db.isInitialized()) {
+ List<String> names = Lists.newArrayList();
+ db.getTables().stream().forEach(table -> names.add(table.getName()));
+ return names;
+ } else {
+ return esRestClient.listTable();
}
- return idToDb.get(dbNameToId.get(realDbName));
- }
-
- @Nullable
- @Override
- public ExternalDatabase getDbNullable(long dbId) {
- makeSureInitialized();
- return idToDb.get(dbId);
}
@Override
@@ -185,15 +172,6 @@ public class EsExternalCatalog extends ExternalCatalog {
return esRestClient.existIndex(this.esRestClient.getClient(), tblName);
}
- @Override
- public List<Long> getDbIds() {
- return Lists.newArrayList(dbNameToId.values());
- }
-
- public ExternalDatabase getDbForReplay(long dbId) {
- return idToDb.get(dbId);
- }
-
@Override
public void gsonPostProcess() throws IOException {
super.gsonPostProcess();
diff --git a/fe/fe-core/src/main/java/org/apache/doris/datasource/ExternalCatalog.java b/fe/fe-core/src/main/java/org/apache/doris/datasource/ExternalCatalog.java
index d50b4d39e5..0eebe17298 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/datasource/ExternalCatalog.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/datasource/ExternalCatalog.java
@@ -22,6 +22,7 @@ import org.apache.doris.catalog.Env;
import org.apache.doris.catalog.external.EsExternalDatabase;
import org.apache.doris.catalog.external.ExternalDatabase;
import org.apache.doris.catalog.external.HMSExternalDatabase;
+import org.apache.doris.catalog.external.JdbcExternalDatabase;
import org.apache.doris.cluster.ClusterNamespace;
import org.apache.doris.common.io.Text;
import org.apache.doris.common.io.Writable;
@@ -30,10 +31,10 @@ import org.apache.doris.persist.gson.GsonPostProcessable;
import org.apache.doris.persist.gson.GsonUtils;
import org.apache.doris.qe.MasterCatalogExecutor;
+import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.google.gson.annotations.SerializedName;
import lombok.Data;
-import org.apache.commons.lang.NotImplementedException;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.jetbrains.annotations.Nullable;
@@ -60,7 +61,7 @@ public abstract class ExternalCatalog implements CatalogIf<ExternalDatabase>, Wr
protected String type;
// save properties of this catalog, such as hive meta store url.
@SerializedName(value = "catalogProperty")
- protected CatalogProperty catalogProperty = new CatalogProperty();
+ protected CatalogProperty catalogProperty;
@SerializedName(value = "initialized")
private boolean initialized = false;
@SerializedName(value = "idToDb")
@@ -134,7 +135,7 @@ public abstract class ExternalCatalog implements CatalogIf<ExternalDatabase>, Wr
}
public ExternalDatabase getDbForReplay(long dbId) {
- throw new NotImplementedException();
+ return idToDb.get(dbId);
}
public abstract List<Column> getSchema(String dbName, String tblName);
@@ -162,13 +163,25 @@ public abstract class ExternalCatalog implements CatalogIf<ExternalDatabase>, Wr
@Nullable
@Override
public ExternalDatabase getDbNullable(String dbName) {
- throw new NotImplementedException();
+ makeSureInitialized();
+ String realDbName = ClusterNamespace.getNameFromFullName(dbName);
+ if (!dbNameToId.containsKey(realDbName)) {
+ return null;
+ }
+ return idToDb.get(dbNameToId.get(realDbName));
}
@Nullable
@Override
public ExternalDatabase getDbNullable(long dbId) {
- throw new NotImplementedException();
+ makeSureInitialized();
+ return idToDb.get(dbId);
+ }
+
+ @Override
+ public List<Long> getDbIds() {
+ makeSureInitialized();
+ return Lists.newArrayList(dbNameToId.values());
}
@Override
@@ -217,6 +230,14 @@ public abstract class ExternalCatalog implements CatalogIf<ExternalDatabase>, Wr
tmpIdToDb.put(db.getId(), db);
}
break;
+ case JDBC:
+ for (int i = 0; i < log.getCreateCount(); i++) {
+ JdbcExternalDatabase db = new JdbcExternalDatabase(
+ this, log.getCreateDbIds().get(i), log.getCreateDbNames().get(i));
+ tmpDbNameToId.put(db.getFullName(), db.getId());
+ tmpIdToDb.put(db.getId(), db);
+ }
+ break;
default:
break;
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/datasource/HMSExternalCatalog.java b/fe/fe-core/src/main/java/org/apache/doris/datasource/HMSExternalCatalog.java
index dd81e327cb..15cb40fe5c 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/datasource/HMSExternalCatalog.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/datasource/HMSExternalCatalog.java
@@ -22,7 +22,6 @@ import org.apache.doris.catalog.Env;
import org.apache.doris.catalog.HiveMetaStoreClientHelper;
import org.apache.doris.catalog.external.ExternalDatabase;
import org.apache.doris.catalog.external.HMSExternalDatabase;
-import org.apache.doris.cluster.ClusterNamespace;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
@@ -30,7 +29,6 @@ import org.apache.hadoop.hive.conf.HiveConf;
import org.apache.hadoop.hive.metastore.api.FieldSchema;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
-import org.jetbrains.annotations.Nullable;
import java.util.List;
import java.util.Map;
@@ -129,34 +127,6 @@ public class HMSExternalCatalog extends ExternalCatalog {
return client.tableExists(getRealTableName(dbName), tblName);
}
- @Nullable
- @Override
- public ExternalDatabase getDbNullable(String dbName) {
- makeSureInitialized();
- String realDbName = ClusterNamespace.getNameFromFullName(dbName);
- if (!dbNameToId.containsKey(realDbName)) {
- return null;
- }
- return idToDb.get(dbNameToId.get(realDbName));
- }
-
- @Nullable
- @Override
- public ExternalDatabase getDbNullable(long dbId) {
- makeSureInitialized();
- return idToDb.get(dbId);
- }
-
- @Override
- public List<Long> getDbIds() {
- makeSureInitialized();
- return Lists.newArrayList(dbNameToId.values());
- }
-
- public ExternalDatabase getDbForReplay(long dbId) {
- return idToDb.get(dbId);
- }
-
public PooledHiveMetaStoreClient getClient() {
makeSureInitialized();
return client;
diff --git a/fe/fe-core/src/main/java/org/apache/doris/datasource/InitCatalogLog.java b/fe/fe-core/src/main/java/org/apache/doris/datasource/InitCatalogLog.java
index 48deec84e6..c7ed1696b2 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/datasource/InitCatalogLog.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/datasource/InitCatalogLog.java
@@ -35,6 +35,7 @@ public class InitCatalogLog implements Writable {
enum Type {
HMS,
ES,
+ JDBC,
UNKNOWN;
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/datasource/InitDatabaseLog.java b/fe/fe-core/src/main/java/org/apache/doris/datasource/InitDatabaseLog.java
index eade3d12b8..2471593626 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/datasource/InitDatabaseLog.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/datasource/InitDatabaseLog.java
@@ -35,6 +35,7 @@ public class InitDatabaseLog implements Writable {
public enum Type {
HMS,
ES,
+ JDBC,
UNKNOWN;
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/datasource/JdbcExternalCatalog.java b/fe/fe-core/src/main/java/org/apache/doris/datasource/JdbcExternalCatalog.java
new file mode 100644
index 0000000000..c8b571e47f
--- /dev/null
+++ b/fe/fe-core/src/main/java/org/apache/doris/datasource/JdbcExternalCatalog.java
@@ -0,0 +1,175 @@
+// 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.datasource;
+
+import org.apache.doris.catalog.Column;
+import org.apache.doris.catalog.Env;
+import org.apache.doris.catalog.external.ExternalDatabase;
+import org.apache.doris.catalog.external.JdbcExternalDatabase;
+import org.apache.doris.external.jdbc.JdbcClient;
+
+import com.google.common.collect.Lists;
+import com.google.common.collect.Maps;
+import lombok.Getter;
+import org.apache.logging.log4j.LogManager;
+import org.apache.logging.log4j.Logger;
+
+import java.io.IOException;
+import java.util.List;
+import java.util.Map;
+
+@Getter
+public class JdbcExternalCatalog extends ExternalCatalog {
+ private static final Logger LOG = LogManager.getLogger(JdbcExternalCatalog.class);
+
+ public static final String PROP_USER = "jdbc.user";
+ public static final String PROP_PASSWORD = "jdbc.password";
+ public static final String PROP_JDBC_URL = "jdbc.jdbc_url";
+ public static final String PROP_DRIVER_URL = "jdbc.driver_url";
+ public static final String PROP_DRIVER_CLASS = "jdbc.driver_class";
+
+ private JdbcClient jdbcClient;
+ private String databaseTypeName;
+ private String jdbcUser;
+ private String jdbcPasswd;
+ private String jdbcUrl;
+ private String driverUrl;
+ private String driverClass;
+ private String checkSum;
+
+ public JdbcExternalCatalog(long catalogId, String name, Map<String, String> props) {
+ this.id = catalogId;
+ this.name = name;
+ this.type = "jdbc";
+ setProperties(props);
+ this.catalogProperty = new CatalogProperty();
+ this.catalogProperty.setProperties(props);
+ }
+
+ @Override
+ public void onClose() {
+ if (jdbcClient != null) {
+ jdbcClient.closeClient();
+ }
+ }
+
+ private void setProperties(Map<String, String> props) {
+ jdbcUser = props.getOrDefault(PROP_USER, "");
+ jdbcPasswd = props.getOrDefault(PROP_PASSWORD, "");
+ jdbcUrl = props.getOrDefault(PROP_JDBC_URL, "");
+ handleJdbcUrl();
+ driverUrl = props.getOrDefault(PROP_DRIVER_URL, "");
+ driverClass = props.getOrDefault(PROP_DRIVER_CLASS, "");
+ }
+
+ // `yearIsDateType` is a parameter of JDBC, and the default is `yearIsDateType=true`
+ // We force the use of `yearIsDateType=false`
+ private void handleJdbcUrl() {
+ // delete all space in jdbcUrl
+ jdbcUrl.replaceAll(" ", "");
+ if (jdbcUrl.contains("yearIsDateType=false")) {
+ return;
+ } else if (jdbcUrl.contains("yearIsDateType=true")) {
+ jdbcUrl.replaceAll("yearIsDateType=true", "yearIsDateType=false");
+ } else {
+ String yearIsDateType = "yearIsDateType=false";
+ if (jdbcUrl.contains("?")) {
+ if (jdbcUrl.charAt(jdbcUrl.length() - 1) != '?') {
+ jdbcUrl += "&";
+ }
+ } else {
+ jdbcUrl += "?";
+ }
+ jdbcUrl += yearIsDateType;
+ }
+ }
+
+ @Override
+ protected void initLocalObjectsImpl() {
+ jdbcClient = new JdbcClient(jdbcUser, jdbcPasswd, jdbcUrl, driverUrl, driverClass);
+ databaseTypeName = jdbcClient.getDbType();
+ checkSum = jdbcClient.getCheckSum();
+ }
+
+ @Override
+ protected void init() {
+ Map<String, Long> tmpDbNameToId = Maps.newConcurrentMap();
+ Map<Long, ExternalDatabase> tmpIdToDb = Maps.newConcurrentMap();
+ InitCatalogLog initCatalogLog = new InitCatalogLog();
+ initCatalogLog.setCatalogId(id);
+ initCatalogLog.setType(InitCatalogLog.Type.JDBC);
+ List<String> allDatabaseNames = jdbcClient.getDatabaseNameList();
+ for (String dbName : allDatabaseNames) {
+ long dbId;
+ if (dbNameToId != null && dbNameToId.containsKey(dbName)) {
+ dbId = dbNameToId.get(dbName);
+ tmpDbNameToId.put(dbName, dbId);
+ ExternalDatabase db = idToDb.get(dbId);
+ db.setUnInitialized();
+ tmpIdToDb.put(dbId, db);
+ initCatalogLog.addRefreshDb(dbId);
+ } else {
+ dbId = Env.getCurrentEnv().getNextId();
+ tmpDbNameToId.put(dbName, dbId);
+ JdbcExternalDatabase db = new JdbcExternalDatabase(this, dbId, dbName);
+ tmpIdToDb.put(dbId, db);
+ initCatalogLog.addCreateDb(dbId, dbName);
+ }
+ }
+ dbNameToId = tmpDbNameToId;
+ idToDb = tmpIdToDb;
+ Env.getCurrentEnv().getEditLog().logInitCatalog(initCatalogLog);
+ }
+
+ @Override
+ public List<String> listDatabaseNames(SessionContext ctx) {
+ makeSureInitialized();
+ return Lists.newArrayList(dbNameToId.keySet());
+ }
+
+ @Override
+ public List<String> listTableNames(SessionContext ctx, String dbName) {
+ makeSureInitialized();
+ JdbcExternalDatabase db = (JdbcExternalDatabase) idToDb.get(dbNameToId.get(dbName));
+ if (db != null && db.isInitialized()) {
+ List<String> names = Lists.newArrayList();
+ db.getTables().stream().forEach(table -> names.add(table.getName()));
+ return names;
+ } else {
+ return jdbcClient.getTablesNameList(dbName);
+ }
+ }
+
+ @Override
+ public boolean tableExist(SessionContext ctx, String dbName, String tblName) {
+ makeSureInitialized();
+ return jdbcClient.isTableExist(dbName, tblName);
+ }
+
+ @Override
+ public void gsonPostProcess() throws IOException {
+ super.gsonPostProcess();
+ setProperties(this.catalogProperty.getProperties());
+ }
+
+ @Override
+ public List<Column> getSchema(String dbName, String tblName) {
+ makeSureInitialized();
+ return jdbcClient.getColumnsFromJdbc(dbName, tblName);
+ }
+}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcClient.java b/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcClient.java
new file mode 100644
index 0000000000..9148629d46
--- /dev/null
+++ b/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcClient.java
@@ -0,0 +1,441 @@
+// 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.external.jdbc;
+
+import org.apache.doris.catalog.Column;
+import org.apache.doris.catalog.PrimitiveType;
+import org.apache.doris.catalog.ScalarType;
+import org.apache.doris.catalog.Type;
+import org.apache.doris.common.FeConstants;
+import org.apache.doris.common.util.Util;
+
+import com.google.common.collect.Lists;
+import com.zaxxer.hikari.HikariConfig;
+import com.zaxxer.hikari.HikariDataSource;
+import lombok.Data;
+import lombok.Getter;
+import org.apache.commons.codec.binary.Hex;
+import org.apache.logging.log4j.LogManager;
+import org.apache.logging.log4j.Logger;
+
+import java.io.IOException;
+import java.io.InputStream;
+import java.net.MalformedURLException;
+import java.net.URL;
+import java.net.URLClassLoader;
+import java.security.MessageDigest;
+import java.security.NoSuchAlgorithmException;
+import java.sql.Connection;
+import java.sql.DatabaseMetaData;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.List;
+
+@Getter
+public class JdbcClient {
+ private static final Logger LOG = LogManager.getLogger(JdbcClient.class);
+ private static final String MYSQL = "MYSQL";
+ // private static final String ORACLE = "ORACLE";
+ // private static final String SQLSERVER = "SQLSERVER";
+ // private static final String POSTGRESQL = "POSTGRESQL";
+ private static final int HTTP_TIMEOUT_MS = 10000;
+
+ private String dbType;
+ private String jdbcUser;
+ private String jdbcPasswd;
+ private String jdbcUrl;
+ private String driverUrl;
+ private String driverClass;
+ private String checkSum;
+
+ private URLClassLoader classLoader = null;
+
+ private HikariDataSource dataSource = null;
+
+
+ public JdbcClient(String user, String password, String jdbcUrl, String driverUrl, String driverClass) {
+ this.jdbcUser = user;
+ this.jdbcPasswd = password;
+ this.jdbcUrl = jdbcUrl;
+ this.driverUrl = driverUrl;
+ this.driverClass = driverClass;
+ this.dbType = parseDbType(jdbcUrl);
+ this.checkSum = computeObjectChecksum();
+
+ try {
+ // TODO(ftw): The problem here is that the jar package is handled by FE
+ // and URLClassLoader may load the jar package directly into memory
+ URL[] urls = {new URL(driverUrl)};
+ // set parent ClassLoader to null, we can achieve class loading isolation.
+ classLoader = URLClassLoader.newInstance(urls, null);
+ Thread.currentThread().setContextClassLoader(classLoader);
+ HikariConfig config = new HikariConfig();
+ config.setDriverClassName(driverClass);
+ config.setJdbcUrl(jdbcUrl);
+ config.setUsername(jdbcUser);
+ config.setPassword(jdbcPasswd);
+ config.setMaximumPoolSize(1);
+ dataSource = new HikariDataSource(config);
+ } catch (MalformedURLException e) {
+ throw new JdbcClientException("MalformedURLException to load class about " + driverUrl, e);
+ }
+ }
+
+ public void closeClient() {
+ dataSource.close();
+ }
+
+ public String parseDbType(String url) {
+ if (url.startsWith("jdbc:mysql") || url.startsWith("jdbc:mariadb")) {
+ return MYSQL;
+ }
+ // else if (url.startsWith("jdbc:oracle")) {
+ // return ORACLE;
+ // }
+ // else if (url.startsWith("jdbc:sqlserver")) {
+ // return SQLSERVER;
+ // } else if (url.startsWith("jdbc:postgresql")) {
+ // return POSTGRESQL;
+ // }
+ throw new JdbcClientException("Unsupported jdbc database type, please check jdbcUrl: " + jdbcUrl);
+ }
+
+ public Connection getConnection() throws JdbcClientException {
+ Connection conn = null;
+ try {
+ conn = dataSource.getConnection();
+ } catch (Exception e) {
+ throw new JdbcClientException("Can not connect to jdbc", e);
+ }
+ return conn;
+ }
+
+ // close connection
+ public void close(Object o) {
+ if (o == null) {
+ return;
+ }
+ if (o instanceof ResultSet) {
+ try {
+ ((ResultSet) o).close();
+ } catch (SQLException e) {
+ throw new JdbcClientException("Can not close ResultSet ", e);
+ }
+ } else if (o instanceof Statement) {
+ try {
+ ((Statement) o).close();
+ } catch (SQLException e) {
+ throw new JdbcClientException("Can not close Statement ", e);
+ }
+ } else if (o instanceof Connection) {
+ Connection c = (Connection) o;
+ try {
+ if (!c.isClosed()) {
+ c.close();
+ }
+ } catch (SQLException e) {
+ throw new JdbcClientException("Can not close Connection ", e);
+ }
+ }
+ }
+
+ public void close(ResultSet rs, Statement stmt, Connection conn) {
+ close(rs);
+ close(stmt);
+ close(conn);
+ }
+
+ public void close(ResultSet rs, Connection conn) {
+ close(rs);
+ close(conn);
+ }
+
+ /**
+ * get all database name through JDBC
+ * @return list of database names
+ */
+ public List<String> getDatabaseNameList() {
+ Connection conn = getConnection();
+ Statement stmt = null;
+ ResultSet rs = null;
+ List<String> databaseNames = Lists.newArrayList();
+ try {
+ stmt = conn.createStatement();
+ rs = stmt.executeQuery("SHOW DATABASES");
+ while (rs.next()) {
+ databaseNames.add(rs.getString(1));
+ }
+ } catch (SQLException e) {
+ throw new JdbcClientException("failed to get database name list from jdbc", e);
+ } finally {
+ close(rs, stmt, conn);
+ }
+ return databaseNames;
+ }
+
+ /**
+ * get all tables of one database
+ */
+ public List<String> getTablesNameList(String dbName) {
+ Connection conn = getConnection();
+ ResultSet rs = null;
+ List<String> tablesName = Lists.newArrayList();
+ String[] types = { "TABLE", "VIEW" };
+ try {
+ DatabaseMetaData databaseMetaData = conn.getMetaData();
+ switch (dbType) {
+ case MYSQL:
+ rs = databaseMetaData.getTables(dbName, null, null, types);
+ break;
+ default:
+ throw new JdbcClientException("Unknown database type");
+ }
+ while (rs.next()) {
+ tablesName.add(rs.getString("TABLE_NAME"));
+ }
+ } catch (SQLException e) {
+ throw new JdbcClientException("failed to get all tables for db %s", e, dbName);
+ } finally {
+ close(rs, conn);
+ }
+ return tablesName;
+ }
+
+ public boolean isTableExist(String dbName, String tableName) {
+ Connection conn = getConnection();
+ ResultSet rs = null;
+ String[] types = { "TABLE", "VIEW" };
+ try {
+ DatabaseMetaData databaseMetaData = conn.getMetaData();
+ switch (dbType) {
+ case MYSQL:
+ rs = databaseMetaData.getTables(dbName, null, tableName, types);
+ break;
+ default:
+ throw new JdbcClientException("Unknown database type");
+ }
+ if (rs.next()) {
+ return true;
+ } else {
+ return false;
+ }
+ } catch (SQLException e) {
+ throw new JdbcClientException("failed to judge if table exist for table %s in db %s", e, tableName, dbName);
+ } finally {
+ close(rs, conn);
+ }
+ }
+
+ @Data
+ private class JdbcFieldSchema {
+ private String columnName;
+ // The SQL type of the corresponding java.sql.types (Type ID)
+ private int dataType;
+ // The SQL type of the corresponding java.sql.types (Type Name)
+ private String dataTypeName;
+ // For CHAR/DATA, columnSize means the maximum number of chars.
+ // For NUMERIC/DECIMAL, columnSize means precision.
+ private int columnSize;
+ private int decimalDigits;
+ // Base number (usually 10 or 2)
+ private int numPrecRadix;
+ // column description
+ private String remarks;
+ // This length is the maximum number of bytes for CHAR type
+ // for utf8 encoding, if columnSize=10, then charOctetLength=30
+ // because for utf8 encoding, a Chinese character takes up 3 bytes
+ private int charOctetLength;
+ /**
+ * Whether it is allowed to be NULL
+ * 0 (columnNoNulls)
+ * 1 (columnNullable)
+ * 2 (columnNullableUnknown)
+ */
+ private int nullAble;
+ }
+
+ /**
+ * get all columns of one table
+ */
+ public List<JdbcFieldSchema> getJdbcColumnsInfo(String dbName, String tableName) {
+ Connection conn = getConnection();
+ ResultSet rs = null;
+ List<JdbcFieldSchema> tableSchema = Lists.newArrayList();
+ try {
+ DatabaseMetaData databaseMetaData = conn.getMetaData();
+ // getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern)
+ // catalog - the catalog of this table, `null` means all catalogs
+ // schema - The schema of the table; corresponding to tablespace in Oracle
+ // `null` means get all schema;
+ // Can contain single-character wildcards ("_"), or multi-character wildcards ("%")
+ // tableNamePattern - table name
+ // Can contain single-character wildcards ("_"), or multi-character wildcards ("%")
+ // columnNamePattern - column name, `null` means get all columns
+ // Can contain single-character wildcards ("_"), or multi-character wildcards ("%")
+ rs = databaseMetaData.getColumns(dbName, null, tableName, null);
+ while (rs.next()) {
+ JdbcFieldSchema field = new JdbcFieldSchema();
+ field.setColumnName(rs.getString("COLUMN_NAME"));
+ field.setDataType(rs.getInt("DATA_TYPE"));
+ field.setDataTypeName(rs.getString("TYPE_NAME"));
+ field.setColumnSize(rs.getInt("COLUMN_SIZE"));
+ field.setDecimalDigits(rs.getInt("DECIMAL_DIGITS"));
+ field.setNumPrecRadix(rs.getInt("NUM_PREC_RADIX"));
+ field.setNullAble(rs.getInt("NULLABLE"));
+ field.setRemarks(rs.getString("REMARKS"));
+ field.setCharOctetLength(rs.getInt("CHAR_OCTET_LENGTH"));
+ tableSchema.add(field);
+ }
+ } catch (SQLException e) {
+ throw new JdbcClientException("failed to get table name list from jdbc for table %s", e, tableName);
+ } finally {
+ close(rs, conn);
+ }
+ return tableSchema;
+ }
+
+ public Type jdbcTypeToDoris(JdbcFieldSchema fieldSchema) {
+ switch (dbType) {
+ case MYSQL:
+ return mysqlTypeToDoris(fieldSchema);
+ default:
+ throw new JdbcClientException("Unknown database type");
+ }
+ }
+
+ public Type mysqlTypeToDoris(JdbcFieldSchema fieldSchema) {
+ // For mysql type: "INT UNSIGNED":
+ // fieldSchema.getDataTypeName().split(" ")[0] == "INT"
+ // fieldSchema.getDataTypeName().split(" ")[1] == "UNSIGNED"
+ String[] typeFields = fieldSchema.getDataTypeName().split(" ");
+ String mysqlType = typeFields[0];
+ // For unsigned int, should extend the type.
+ if (typeFields.length > 1 && "UNSIGNED".equals(typeFields[1])) {
+ switch (mysqlType) {
+ case "TINYINT":
+ return Type.SMALLINT;
+ case "SMALLINT":
+ return Type.INT;
+ case "MEDIUMINT":
+ return Type.INT;
+ case "INT":
+ return Type.BIGINT;
+ case "BIGINT":
+ return ScalarType.createStringType();
+ default:
+ throw new JdbcClientException("Unknown UNSIGNED type of mysql, type: [" + mysqlType + "]");
+ }
+ }
+ switch (mysqlType) {
+ case "BOOLEAN":
+ return Type.BOOLEAN;
+ case "TINYINT":
+ return Type.TINYINT;
+ case "SMALLINT":
+ case "YEAR":
+ return Type.SMALLINT;
+ case "MEDIUMINT":
+ case "INT":
+ return Type.INT;
+ case "BIGINT":
+ return Type.BIGINT;
+ case "DATE":
+ return ScalarType.getDefaultDateType(Type.DATE);
+ case "TIMESTAMP":
+ return ScalarType.getDefaultDateType(Type.DATETIME);
+ case "DATETIME":
+ return ScalarType.getDefaultDateType(Type.DATETIME);
+ case "FLOAT":
+ return Type.FLOAT;
+ case "DOUBLE":
+ return Type.DOUBLE;
+ case "DECIMAL":
+ int precision = fieldSchema.getColumnSize();
+ int scale = fieldSchema.getDecimalDigits();
+ return ScalarType.createDecimalType(precision, scale);
+ case "CHAR":
+ ScalarType charType = ScalarType.createType(PrimitiveType.CHAR);
+ charType.setLength(fieldSchema.columnSize);
+ return charType;
+ case "TIME":
+ case "VARCHAR":
+ case "TINYTEXT":
+ case "TEXT":
+ case "MEDIUMTEXT":
+ case "LONGTEXT":
+ case "TINYBLOB":
+ case "BLOB":
+ case "MEDIUMBLOB":
+ case "LONGBLOB":
+ case "TINYSTRING":
+ case "STRING":
+ case "MEDIUMSTRING":
+ case "LONGSTRING":
+ case "JSON":
+ case "SET":
+ case "BIT":
+ case "BINARY":
+ case "VARBINARY":
+ return ScalarType.createStringType();
+ default:
+ throw new JdbcClientException("Can not convert mysql data type to doris data type for type ["
+ + mysqlType + "]");
+ }
+ }
+
+ public List<Column> getColumnsFromJdbc(String dbName, String tableName) {
+ List<JdbcFieldSchema> jdbcTableSchema = getJdbcColumnsInfo(dbName, tableName);
+ List<Column> dorisTableSchema = Lists.newArrayListWithCapacity(jdbcTableSchema.size());
+ for (JdbcFieldSchema field : jdbcTableSchema) {
+ dorisTableSchema.add(new Column(field.getColumnName(),
+ jdbcTypeToDoris(field), true, null,
+ true, null, field.getRemarks(),
+ true, null, -1));
+ }
+ return dorisTableSchema;
+ }
+
+ private String computeObjectChecksum() {
+ if (FeConstants.runningUnitTest) {
+ // skip checking checksum when running ut
+ return "";
+ }
+
+ InputStream inputStream = null;
+ try {
+ inputStream = Util.getInputStreamFromUrl(driverUrl, null, HTTP_TIMEOUT_MS, HTTP_TIMEOUT_MS);
+ MessageDigest digest = MessageDigest.getInstance("MD5");
+ byte[] buf = new byte[4096];
+ int bytesRead = 0;
+ do {
+ bytesRead = inputStream.read(buf);
+ if (bytesRead < 0) {
+ break;
+ }
+ digest.update(buf, 0, bytesRead);
+ } while (true);
+ return Hex.encodeHexString(digest.digest());
+ } catch (IOException e) {
+ throw new JdbcClientException("compute driver checksum from url: " + driverUrl + " meet an IOException.");
+ } catch (NoSuchAlgorithmException e) {
+ throw new JdbcClientException(
+ "compute driver checksum from url: " + driverUrl + " could not find algorithm.");
+ }
+ }
+}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcClientException.java b/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcClientException.java
new file mode 100644
index 0000000000..1298a0df8f
--- /dev/null
+++ b/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcClientException.java
@@ -0,0 +1,28 @@
+// 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.external.jdbc;
+
+public class JdbcClientException extends RuntimeException {
+ public JdbcClientException(String format, Throwable cause, Object... msg) {
+ super(String.format(format, msg), cause);
+ }
+
+ public JdbcClientException(String format, Object... msg) {
+ super(String.format(format, msg));
+ }
+}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/load/ExportJob.java b/fe/fe-core/src/main/java/org/apache/doris/load/ExportJob.java
index 31c3ac918c..00057dfd67 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/load/ExportJob.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/load/ExportJob.java
@@ -38,7 +38,6 @@ import org.apache.doris.analysis.TupleDescriptor;
import org.apache.doris.catalog.Column;
import org.apache.doris.catalog.Database;
import org.apache.doris.catalog.Env;
-import org.apache.doris.catalog.JdbcTable;
import org.apache.doris.catalog.MysqlTable;
import org.apache.doris.catalog.OdbcTable;
import org.apache.doris.catalog.PrimitiveType;
@@ -420,7 +419,7 @@ public class ExportJob implements Writable {
scanNode = new MysqlScanNode(new PlanNodeId(0), exportTupleDesc, (MysqlTable) this.exportTable);
break;
case JDBC:
- scanNode = new JdbcScanNode(new PlanNodeId(0), exportTupleDesc, (JdbcTable) this.exportTable);
+ scanNode = new JdbcScanNode(new PlanNodeId(0), exportTupleDesc, false);
break;
default:
break;
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/JdbcScanNode.java
index 551135e730..b06956fda3 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/JdbcScanNode.java
@@ -26,6 +26,7 @@ import org.apache.doris.analysis.TupleDescriptor;
import org.apache.doris.catalog.Column;
import org.apache.doris.catalog.JdbcTable;
import org.apache.doris.catalog.OdbcTable;
+import org.apache.doris.catalog.external.JdbcExternalTable;
import org.apache.doris.common.UserException;
import org.apache.doris.statistics.StatisticalType;
import org.apache.doris.statistics.StatsRecursiveDerive;
@@ -59,6 +60,19 @@ public class JdbcScanNode extends ScanNode {
tableName = OdbcTable.databaseProperName(jdbcType, tbl.getJdbcTable());
}
+ public JdbcScanNode(PlanNodeId id, TupleDescriptor desc, boolean isJdbcExternalTable) {
+ super(id, desc, "JdbcScanNode", StatisticalType.JDBC_SCAN_NODE);
+ JdbcTable tbl = null;
+ if (isJdbcExternalTable) {
+ JdbcExternalTable jdbcExternalTable = (JdbcExternalTable) (desc.getTable());
+ tbl = jdbcExternalTable.getJdbcTable();
+ } else {
+ tbl = (JdbcTable) (desc.getTable());
+ }
+ jdbcType = tbl.getJdbcTableType();
+ tableName = OdbcTable.databaseProperName(jdbcType, tbl.getJdbcTable());
+ }
+
@Override
public void init(Analyzer analyzer) throws UserException {
super.init(analyzer);
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 e9b50549c8..5e6b779514 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
@@ -55,7 +55,6 @@ import org.apache.doris.catalog.AggregateFunction;
import org.apache.doris.catalog.AggregateType;
import org.apache.doris.catalog.Column;
import org.apache.doris.catalog.FunctionSet;
-import org.apache.doris.catalog.JdbcTable;
import org.apache.doris.catalog.KeysType;
import org.apache.doris.catalog.MysqlTable;
import org.apache.doris.catalog.OdbcTable;
@@ -1942,7 +1941,7 @@ public class SingleNodePlanner {
null, -1);
break;
case JDBC:
- scanNode = new JdbcScanNode(ctx.getNextNodeId(), tblRef.getDesc(), (JdbcTable) tblRef.getTable());
+ scanNode = new JdbcScanNode(ctx.getNextNodeId(), tblRef.getDesc(), false);
break;
case TABLE_VALUED_FUNCTION:
scanNode = ((TableValuedFunctionRef) tblRef).getScanNode(ctx.getNextNodeId());
@@ -1953,6 +1952,9 @@ public class SingleNodePlanner {
case ES_EXTERNAL_TABLE:
scanNode = new EsScanNode(ctx.getNextNodeId(), tblRef.getDesc(), "EsScanNode", true);
break;
+ case JDBC_EXTERNAL_TABLE:
+ scanNode = new JdbcScanNode(ctx.getNextNodeId(), tblRef.getDesc(), true);
+ break;
default:
break;
}
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 ae8b910f91..0a267bd9a6 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
@@ -204,7 +204,6 @@ public class JdbcExecutor {
try {
ClassLoader parent = getClass().getClassLoader();
ClassLoader classLoader = UdfUtils.getClassLoader(driverUrl, parent);
- Class.forName(driverClass, true, classLoader);
Thread.currentThread().setContextClassLoader(classLoader);
HikariConfig config = new HikariConfig();
config.setDriverClassName(driverClass);
@@ -226,8 +225,6 @@ public class JdbcExecutor {
throw new UdfRuntimeException("Can not find driver file: " + driverUrl, e);
} catch (MalformedURLException e) {
throw new UdfRuntimeException("MalformedURLException to load class about " + driverUrl, e);
- } catch (ClassNotFoundException e) {
- throw new UdfRuntimeException("Loading JDBC class error ClassNotFoundException about " + driverClass, e);
} catch (SQLException e) {
throw new UdfRuntimeException("Initialize datasource failed: ", e);
}
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
new file mode 100644
index 0000000000..9c4a43e959
--- /dev/null
+++ b/regression-test/data/jdbc_catalog_p0/test_mysql_jdbc_catalog.out
@@ -0,0 +1,154 @@
+-- This file is automatically generated. You should know what you did if you want to edit this
+-- !db_amount --
+doris_test
+information_schema
+init_db
+mysql
+performance_schema
+sys
+
+-- !ex_tb0 --
+111 abc
+112 abd
+113 abe
+114 abf
+115 abg
+
+-- !in_tb --
+111 abc
+112 abd
+113 abe
+114 abf
+115 abg
+
+-- !ex_tb1 --
+{"k1":"v1", "k2":"v2"}
+
+-- !ex_tb2 --
+123 10
+123 15
+123 20
+
+-- !ex_tb3 --
+mus plat_code 1001169339 1590381433914 1590420872639 11 1006061 beijing
+mus plat_code 1001169339 1590402594411 1590420872639 11 1006061 beijing
+mus plat_code 1001169339 1590406790026 1590420872639 11 1006061 beijing
+mus plat_code 1001169339 1590420482288 1590420872639 11 1006061 beijing
+mus plat_code 1001169339 1590420872639 1590420872639 11 1006061 beijing
+
+-- !ex_tb4 --
+1 111 2021-09-01T07:01:01 2021-09-01T08:01:01 1
+2 112 2021-09-02T07:01:01 2021-09-02T08:01:01 1
+3 113 0000-01-01T00:00 2021-12-01T08:01:01 2
+5 115 2021-09-01T07:02:01 2021-09-01T08:01:04 4
+6 116 2021-10-01T07:03:01 2022-09-01T08:02:05 5
+
+-- !ex_tb5 --
+1 test_apply_id 123321 zhangsan zhangsan ready ok 2 2022-01-01T02:03:04
+
+-- !ex_tb6 --
+639215401565159424 1143681147589283841 test
+639237839376089088 1143681147589283841 test123
+
+-- !ex_tb7 --
+2 sim 1.000
+2 sim 1.001
+2 sim 1.002
+
+-- !ex_tb8 --
+2022-07-15 2222 1 \N
+2022-07-15 ddddd 2 0.5
+
+-- !ex_tb9 --
+\N
+2022-01-01
+
+-- !ex_tb10 --
+a 1 2
+b 1 2
+c 1 2
+d 3 2
+
+-- !ex_tb11 --
+a 1
+b 1
+c 1
+
+-- !ex_tb12 --
+a 1
+b 1
+c 1
+
+-- !ex_tb13 --
+
+-- !ex_tb14 --
+123 2022-11-02 2022-11-02 8011 oppo
+abc 2022-11-02 2022-11-02 8011 agdtb
+bca 2022-11-02 2022-11-02 8012 vivo
+
+-- !ex_tb15 --
+2022-11-04 2022-10-31 2022-11-04 62 5.4103451446E9 7.211386993606482E10 21 10 16 - - 2022-11-04T17:40:19
+
+-- !ex_tb16 --
+1 a 0 4 3 6 8
+1 b 0 4 4 8 8
+1 c 0 9 9 5 4
+1 d 0 7 6 1 7
+1 e 0 7 5 6 3
+2 a 0 3 4 1 6
+2 b 0 1 5 4 5
+2 c 0 5 7 9 1
+2 d 0 4 4 8 4
+2 e 0 6 4 7 8
+3 a 0 7 9 4 8
+3 b 0 4 9 8 1
+3 d 0 2 7 1 5
+3 e 0 2 4 3 4
+4 a 0 5 7 4 1
+4 b 0 3 4 2 7
+4 c 0 3 9 3 7
+4 d 0 1 5 6 4
+5 a 0 1 2 2 1
+5 b 0 6 6 2 9
+5 c 0 8 5 7 6
+5 d 0 6 2 7 7
+5 e 0 5 7 9 2
+6 a 0 1 1 8 8
+6 b 0 3 9 1 6
+6 c 0 3 1 3 8
+6 d 0 1 2 4 7
+6 e 0 1 9 7 6
+7 a 0 1 1 3 8
+7 b 0 3 2 8 1
+7 c 0 3 7 7 1
+7 d 0 6 1 5 6
+7 e 0 6 1 3 7
+8 a 0 3 2 8 2
+8 b 0 4 9 4 9
+8 c 0 1 7 1 5
+8 e 0 4 4 5 4
+9 a 0 8 3 9 1
+9 b 0 2 1 4 2
+9 c 0 8 3 9 8
+9 d 0 6 6 5 3
+9 e 0 9 1 9 7
+
+-- !ex_tb17 --
+1 6 1 1 2099.18 3 8 1554296.82 68781940.49 d 8 5 0 d a 7 9
+2 8 9 8 2900.42 1 6 97486621.73 59634489.39 c 3 2 0 a e 7 4
+3 5 7 3 6276.86 8 9 32758730.38 10260499.72 c 8 1 0 d c 9 2
+4 3 7 5 2449.00 6 3 91359059.28 64743145.92 e 7 8 0 b d 8 4
+5 6 4 5 9137.82 2 7 26526675.70 90098303.36 a 6 7 0 d e 4 1
+6 3 6 8 7601.25 4 9 49117098.47 46499188.80 c 3 3 0 c d 4 8
+7 3 2 8 5297.81 9 3 23753694.20 96930000.64 c 7 2 0 b e 1 5
+8 3 6 7 3683.85 5 7 26056250.91 1127755.43 b 7 6 0 d b 4 7
+9 3 9 1 4785.38 1 5 95199488.12 94869703.42 a 4 4 0 c d 2 4
+
+-- !ex_tb18 --
+-128 255 -32768 65535 -8388608 16777215 -9223372036854775808 -2147483648 2147483647 4294967295 33.14 422113.141 2342.23 aa asdawdasdaasdasd aaa bbbbbbbb xaqwdqwdqwdqd asdas
+1 1 1 1 1 1 1 1 1 1 3.14 13.141 2342.23 aa asdawdasdaasdasd aaa bbbbbbbb xaqwdqwdqwdqdwqwdqwdqd asdadwqdqwddqwdsadqwdas
+127 255 32767 65535 8388607 16777215 9223372036854775807 -2147483648 2147483647 4294967295 33.14 422113.141 2342.23 aa asdawdasdaasdasd aaa bbbbbbbb xaqwdqwdqwdqd asdadwqdqwdsadqwdas
+
+-- !ex_tb19 --
+2022-11-27 07:09:51 2022 2022-11-27T07:09:51 2022-11-27T07:09:51
+
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
new file mode 100644
index 0000000000..514815755c
--- /dev/null
+++ b/regression-test/suites/jdbc_catalog_p0/test_mysql_jdbc_catalog.groovy
@@ -0,0 +1,103 @@
+// 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_mysql_jdbc_catalog", "p0") {
+ String enabled = context.config.otherConfigs.get("enableJdbcTest")
+ if (enabled != null && enabled.equalsIgnoreCase("true")) {
+ sql """admin set frontend config ("enable_multi_catalog" = "true")"""
+
+ String catalog_name = "mysql_jdbc_catalog";
+ String internal_db_name = "regression_test_jdbc_catalog_p0";
+ String ex_db_name = "doris_test";
+ String mysql_port = context.config.otherConfigs.get("mysql_57_port");
+ String inDorisTable = "doris_in_tb";
+ String ex_tb0 = "ex_tb0";
+ String ex_tb1 = "ex_tb1";
+ String ex_tb2 = "ex_tb2";
+ String ex_tb3 = "ex_tb3";
+ String ex_tb4 = "ex_tb4";
+ String ex_tb5 = "ex_tb5";
+ String ex_tb6 = "ex_tb6";
+ String ex_tb7 = "ex_tb7";
+ String ex_tb8 = "ex_tb8";
+ String ex_tb9 = "ex_tb9";
+ String ex_tb10 = "ex_tb10";
+ String ex_tb11 = "ex_tb11";
+ String ex_tb12 = "ex_tb12";
+ String ex_tb13 = "ex_tb13";
+ String ex_tb14 = "ex_tb14";
+ String ex_tb15 = "ex_tb15";
+ String ex_tb16 = "ex_tb16";
+ String ex_tb17 = "ex_tb17";
+ String ex_tb18 = "ex_tb18";
+ String ex_tb19 = "ex_tb19";
+
+
+ sql """drop catalog if exists ${catalog_name} """
+
+ // if use 'com.mysql.cj.jdbc.Driver' here, it will report: ClassNotFound
+ sql """ CREATE CATALOG ${catalog_name} PROPERTIES (
+ "type"="jdbc",
+ "jdbc.user"="root",
+ "jdbc.password"="123456",
+ "jdbc.jdbc_url" = "jdbc:mysql://127.0.0.1:${mysql_port}/doris_test?useSSL=false",
+ "jdbc.driver_url" = "https://doris-community-test-1308700295.cos.ap-hongkong.myqcloud.com/jdbc_driver/mysql-connector-java-8.0.25.jar",
+ "jdbc.driver_class" = "com.mysql.cj.jdbc.Driver");
+ """
+
+ sql """ drop table if exists ${inDorisTable} """
+ sql """
+ CREATE TABLE ${inDorisTable} (
+ `id` INT NULL COMMENT "主键id",
+ `name` string NULL COMMENT "名字"
+ ) DISTRIBUTED BY HASH(id) BUCKETS 10
+ PROPERTIES("replication_num" = "1");
+ """
+
+ sql """switch ${catalog_name}"""
+ qt_db_amount """ show databases; """
+
+ sql """ use ${ex_db_name}"""
+
+ order_qt_ex_tb0 """ select id, name from ${ex_tb0} order by id; """
+ sql """ insert into internal.${internal_db_name}.${inDorisTable} select id, name from ${ex_tb0}; """
+ order_qt_in_tb """ select id, name from internal.${internal_db_name}.${inDorisTable} order by id; """
+
+ order_qt_ex_tb1 """ select * from ${ex_tb1} order by id; """
+ order_qt_ex_tb2 """ select * from ${ex_tb2} order by id; """
+ order_qt_ex_tb3 """ select * from ${ex_tb3} order by game_code; """
+ order_qt_ex_tb4 """ select * from ${ex_tb4} order by products_id; """
+ order_qt_ex_tb5 """ select * from ${ex_tb5} order by id; """
+ order_qt_ex_tb6 """ select * from ${ex_tb6} order by id; """
+ order_qt_ex_tb7 """ select * from ${ex_tb7} order by id; """
+ order_qt_ex_tb8 """ select * from ${ex_tb8} order by uid; """
+ order_qt_ex_tb9 """ select * from ${ex_tb9} order by c_date; """
+ order_qt_ex_tb10 """ select * from ${ex_tb10} order by aa; """
+ order_qt_ex_tb11 """ select * from ${ex_tb11} order by aa; """
+ order_qt_ex_tb12 """ select * from ${ex_tb12} order by cc; """
+ order_qt_ex_tb13 """ select * from ${ex_tb13} order by name; """
+ order_qt_ex_tb14 """ select * from ${ex_tb14} order by tid; """
+ order_qt_ex_tb15 """ select * from ${ex_tb15} order by col1; """
+ order_qt_ex_tb16 """ select * from ${ex_tb16} order by id; """
+ order_qt_ex_tb17 """ select * from ${ex_tb17} order by id; """
+ order_qt_ex_tb18 """ select * from ${ex_tb18} order by num_tinyint; """
+ order_qt_ex_tb19 """ select * from ${ex_tb19} order by date_value; """
+
+
+ sql """admin set frontend config ("enable_multi_catalog" = "false")"""
+ }
+}
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org