This is an automated email from the ASF dual-hosted git repository. morningman pushed a commit to branch branch-1.2-lts in repository https://gitbox.apache.org/repos/asf/doris.git
commit 98aeac63592aaa2a106887d62490669ed8c133fd Author: yongkang.zhong <[email protected]> AuthorDate: Wed Mar 15 20:37:36 2023 +0800 [feature](multi-catalog) support sap hana jdbc catalog and jdbc external table (#17780) --- .../Create/CREATE-CATALOG.md | 74 ++++++++++++++----- .../Create/CREATE-CATALOG.md | 84 ++++++++++++++++------ .../org/apache/doris/catalog/JdbcResource.java | 5 +- .../java/org/apache/doris/catalog/JdbcTable.java | 1 + .../java/org/apache/doris/catalog/OdbcTable.java | 7 ++ .../org/apache/doris/external/jdbc/JdbcClient.java | 74 +++++++++++++++++-- .../org/apache/doris/planner/JdbcScanNode.java | 3 +- gensrc/thrift/Types.thrift | 3 +- 8 files changed, 201 insertions(+), 50 deletions(-) diff --git a/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-CATALOG.md b/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-CATALOG.md index d530c2ced2..1f598bfd78 100644 --- a/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-CATALOG.md +++ b/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-CATALOG.md @@ -124,6 +124,7 @@ CREATE CATALOG catalog_name PROPERTIES ( ```sql -- 1.2.0+ Version + -- The first way CREATE RESOURCE mysql_resource PROPERTIES ( "type"="jdbc", "user"="root", @@ -134,8 +135,18 @@ CREATE CATALOG catalog_name PROPERTIES ( ); CREATE CATALOG jdbc WITH RESOURCE msyql_resource; - -- 1.2.0 Version + -- The second way CREATE CATALOG jdbc PROPERTIES ( + "type"="jdbc", + "user"="root", + "password"="123456", + "jdbc_url" = "jdbc:mysql://127.0.0.1:3316/doris_test?useSSL=false", + "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" + ); + + -- 1.2.0 Version + CREATE CATALOG jdbc PROPERTIES ( "type"="jdbc", "jdbc.user"="root", "jdbc.password"="123456", @@ -159,21 +170,21 @@ CREATE CATALOG catalog_name PROPERTIES ( ); CREATE CATALOG jdbc WITH RESOURCE pg_resource; - -- The second way, note: keys have 'jdbc' prefix in front. + -- The second way CREATE CATALOG jdbc PROPERTIES ( "type"="jdbc", - "jdbc.user"="postgres", - "jdbc.password"="123456", - "jdbc.jdbc_url" = "jdbc:postgresql://127.0.0.1:5432/demo", - "jdbc.driver_url" = "file:/path/to/postgresql-42.5.1.jar", - "jdbc.driver_class" = "org.postgresql.Driver" + "user"="postgres", + "password"="123456", + "jdbc_url" = "jdbc:postgresql://127.0.0.1:5432/demo", + "driver_url" = "file:///path/to/postgresql-42.5.1.jar", + "driver_class" = "org.postgresql.Driver" ); ``` **clickhouse** ```sql - -- 1.2.0+ Version + -- The first way CREATE RESOURCE clickhouse_resource PROPERTIES ( "type"="jdbc", "user"="default", @@ -184,11 +195,14 @@ CREATE CATALOG catalog_name PROPERTIES ( ) CREATE CATALOG jdbc WITH RESOURCE clickhouse_resource; - -- 1.2.0 Version + -- The second way CREATE CATALOG jdbc PROPERTIES ( "type"="jdbc", - "jdbc.jdbc_url" = "jdbc:clickhouse://127.0.0.1:8123/demo", - ... + "user"="default", + "password"="123456", + "jdbc_url" = "jdbc:clickhouse://127.0.0.1:8123/demo", + "driver_url" = "file:///path/to/clickhouse-jdbc-0.3.2-patch11-all.jar", + "driver_class" = "com.clickhouse.jdbc.ClickHouseDriver" ) ``` @@ -205,14 +219,14 @@ CREATE CATALOG catalog_name PROPERTIES ( ); CREATE CATALOG jdbc WITH RESOURCE oracle_resource; - -- The second way, note: keys have 'jdbc' prefix in front. + -- The second way CREATE CATALOG jdbc PROPERTIES ( "type"="jdbc", - "jdbc.user"="doris", - "jdbc.password"="123456", - "jdbc.jdbc_url" = "jdbc:oracle:thin:@127.0.0.1:1521:helowin", - "jdbc.driver_url" = "file:/path/to/ojdbc6.jar", - "jdbc.driver_class" = "oracle.jdbc.driver.OracleDriver" + "user"="doris", + "password"="123456", + "jdbc_url" = "jdbc:oracle:thin:@127.0.0.1:1521:helowin", + "driver_url" = "file:///path/to/ojdbc6.jar", + "driver_class" = "oracle.jdbc.driver.OracleDriver" ); ``` @@ -229,7 +243,7 @@ CREATE CATALOG catalog_name PROPERTIES ( ); CREATE CATALOG sqlserver_catlog WITH RESOURCE sqlserver_resource; - -- The second way, note: keys have 'jdbc' prefix in front. + -- The second way CREATE CATALOG sqlserver_catlog PROPERTIES ( "type"="jdbc", "jdbc.user"="SA", @@ -240,6 +254,30 @@ CREATE CATALOG catalog_name PROPERTIES ( ); ``` + **SAP_HANA** + ```sql + -- The first way + CREATE RESOURCE saphana_resource PROPERTIES ( + "type"="jdbc", + "user"="SYSTEM", + "password"="SAPHANA", + "jdbc_url" = "jdbc:sap://localhost:31515/TEST", + "driver_url" = "file:///path/to/ngdbc.jar", + "driver_class" = "com.sap.db.jdbc.Driver" + ); + CREATE CATALOG saphana_catlog WITH RESOURCE saphana_resource; + + -- The second way + CREATE CATALOG saphana_catalog PROPERTIES ( + "type"="jdbc", + "user"="SYSTEM", + "password"="SAPHANA", + "jdbc_url" = "jdbc:sap://localhost:31515/TEST", + "driver_url" = "file:///path/to/ngdbc.jar", + "driver_class" = "com.sap.db.jdbc.Driver" + ); + ``` + ### Keywords CREATE, CATALOG diff --git a/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-CATALOG.md b/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-CATALOG.md index 65b3417302..01ec62e60e 100644 --- a/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-CATALOG.md +++ b/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-CATALOG.md @@ -130,6 +130,7 @@ CREATE CATALOG catalog_name PROPERTIES ( ```sql -- 1.2.0+ 版本 + -- 方式一 CREATE RESOURCE mysql_resource PROPERTIES ( "type"="jdbc", "user"="root", @@ -140,8 +141,18 @@ CREATE CATALOG catalog_name PROPERTIES ( ); CREATE CATALOG jdbc WITH RESOURCE msyql_resource; - -- 1.2.0 版本 + -- 方式二 CREATE CATALOG jdbc PROPERTIES ( + "type"="jdbc", + "user"="root", + "password"="123456", + "jdbc_url" = "jdbc:mysql://127.0.0.1:3316/doris_test?useSSL=false", + "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" + ); + + -- 1.2.0 版本 + CREATE CATALOG jdbc PROPERTIES ( "type"="jdbc", "jdbc.user"="root", "jdbc.password"="123456", @@ -165,21 +176,21 @@ CREATE CATALOG catalog_name PROPERTIES ( ); CREATE CATALOG jdbc WITH RESOURCE pg_resource; - -- 方式二，注意有jdbc前缀 + -- 方式二 CREATE CATALOG jdbc PROPERTIES ( "type"="jdbc", - "jdbc.user"="postgres", - "jdbc.password"="123456", - "jdbc.jdbc_url" = "jdbc:postgresql://127.0.0.1:5432/demo", - "jdbc.driver_url" = "file:/path/to/postgresql-42.5.1.jar", - "jdbc.driver_class" = "org.postgresql.Driver" + "user"="postgres", + "password"="123456", + "jdbc_url" = "jdbc:postgresql://127.0.0.1:5432/demo", + "driver_url" = "file:///path/to/postgresql-42.5.1.jar", + "driver_class" = "org.postgresql.Driver" ); ``` **clickhouse** ```sql - -- 1.2.0+ Version + -- 方式一 CREATE RESOURCE clickhouse_resource PROPERTIES ( "type"="jdbc", "user"="default", @@ -190,11 +201,14 @@ CREATE CATALOG catalog_name PROPERTIES ( ) CREATE CATALOG jdbc WITH RESOURCE clickhouse_resource; - -- 1.2.0 Version + -- 方式一 CREATE CATALOG jdbc PROPERTIES ( "type"="jdbc", - "jdbc.jdbc_url" = "jdbc:clickhouse://127.0.0.1:8123/demo", - ... + "user"="default", + "password"="123456", + "jdbc_url" = "jdbc:clickhouse://127.0.0.1:8123/demo", + "driver_url" = "file:///path/to/clickhouse-jdbc-0.3.2-patch11-all.jar", + "driver_class" = "com.clickhouse.jdbc.ClickHouseDriver" ) ``` @@ -211,14 +225,14 @@ CREATE CATALOG catalog_name PROPERTIES ( ); CREATE CATALOG jdbc WITH RESOURCE oracle_resource; - -- 方式二，注意有jdbc前缀 + -- 方式二 CREATE CATALOG jdbc PROPERTIES ( "type"="jdbc", - "jdbc.user"="doris", - "jdbc.password"="123456", - "jdbc.jdbc_url" = "jdbc:oracle:thin:@127.0.0.1:1521:helowin", - "jdbc.driver_url" = "file:/path/to/ojdbc6.jar", - "jdbc.driver_class" = "oracle.jdbc.driver.OracleDriver" + "user"="doris", + "password"="123456", + "jdbc_url" = "jdbc:oracle:thin:@127.0.0.1:1521:helowin", + "driver_url" = "file:///path/to/ojdbc6.jar", + "driver_class" = "oracle.jdbc.driver.OracleDriver" ); ``` @@ -235,17 +249,41 @@ CREATE CATALOG catalog_name PROPERTIES ( ); CREATE CATALOG sqlserver_catlog WITH RESOURCE sqlserver_resource; - -- 方式二，注意有jdbc前缀 + -- 方式二 CREATE CATALOG sqlserver_catlog PROPERTIES ( "type"="jdbc", - "jdbc.user"="SA", - "jdbc.password"="Doris123456", - "jdbc.jdbc_url" = "jdbc:sqlserver://localhost:1433;DataBaseName=doris_test", - "jdbc.driver_url" = "file:/path/to/mssql-jdbc-11.2.3.jre8.jar", - "jdbc.driver_class" = "com.microsoft.sqlserver.jdbc.SQLServerDriver" + "user"="SA", + "password"="Doris123456", + "jdbc_url" = "jdbc:sqlserver://localhost:1433;DataBaseName=doris_test", + "driver_url" = "file:///path/to/mssql-jdbc-11.2.3.jre8.jar", + "driver_class" = "com.microsoft.sqlserver.jdbc.SQLServerDriver" ); ``` + **SAP_HANA** + ```sql + -- 方式一 + CREATE RESOURCE saphana_resource PROPERTIES ( + "type"="jdbc", + "user"="SYSTEM", + "password"="SAPHANA", + "jdbc_url" = "jdbc:sap://localhost:31515/TEST", + "driver_url" = "file:///path/to/ngdbc.jar", + "driver_class" = "com.sap.db.jdbc.Driver" + ); + CREATE CATALOG saphana_catlog WITH RESOURCE saphana_resource; + + -- 方式二 + CREATE CATALOG saphana_catalog PROPERTIES ( + "type"="jdbc", + "user"="SYSTEM", + "password"="SAPHANA", + "jdbc_url" = "jdbc:sap://localhost:31515/TEST", + "driver_url" = "file:///path/to/ngdbc.jar", + "driver_class" = "com.sap.db.jdbc.Driver" + ); + ``` + ### Keywords CREATE, CATALOG diff --git a/fe/fe-core/src/main/java/org/apache/doris/catalog/JdbcResource.java b/fe/fe-core/src/main/java/org/apache/doris/catalog/JdbcResource.java index 1cede92b9f..7cac35d627 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/catalog/JdbcResource.java +++ b/fe/fe-core/src/main/java/org/apache/doris/catalog/JdbcResource.java @@ -67,12 +67,14 @@ public class JdbcResource extends Resource { public static final String JDBC_ORACLE = "jdbc:oracle"; public static final String JDBC_SQLSERVER = "jdbc:sqlserver"; public static final String JDBC_CLICKHOUSE = "jdbc:clickhouse"; + public static final String JDBC_SAP_HANA = "jdbc:sap"; public static final String MYSQL = "MYSQL"; public static final String POSTGRESQL = "POSTGRESQL"; public static final String ORACLE = "ORACLE"; public static final String SQLSERVER = "SQLSERVER"; public static final String CLICKHOUSE = "CLICKHOUSE"; + public static final String SAP_HANA = "SAP_HANA"; public static final String JDBC_PROPERTIES_PREFIX = "jdbc."; public static final String JDBC_URL = "jdbc_url"; @@ -259,6 +261,8 @@ public class JdbcResource extends Resource { return SQLSERVER; } else if (url.startsWith(JDBC_CLICKHOUSE)) { return CLICKHOUSE; + } else if (url.startsWith(JDBC_SAP_HANA)) { + return SAP_HANA; } throw new DdlException("Unsupported jdbc database type, please check jdbcUrl: " + url); } @@ -323,7 +327,6 @@ public class JdbcResource extends Resource { * * @param jdbcUrl * @param params - * @param value * @return */ private static String checkAndSetJdbcParam(String jdbcUrl, String params, String expectedVal) { 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 21c7112f88..c9a36c0439 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 @@ -74,6 +74,7 @@ public class JdbcTable extends Table { tempMap.put("sqlserver", TOdbcTableType.SQLSERVER); tempMap.put("oracle", TOdbcTableType.ORACLE); tempMap.put("clickhouse", TOdbcTableType.CLICKHOUSE); + tempMap.put("sap_hana", TOdbcTableType.SAP_HANA); TABLE_TYPE_MAP = Collections.unmodifiableMap(tempMap); } 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 b02939119a..fb0cf1d3a3 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 @@ -97,6 +97,11 @@ public class OdbcTable extends Table { return list.stream().map(s -> "\"" + s.toUpperCase() + "\"").collect(Collectors.joining(".")); } + private static String saphanaProperName(String name) { + List<String> list = Arrays.asList(name.split("\\.")); + return list.stream().map(s -> "\"" + s.toUpperCase() + "\"").collect(Collectors.joining(".")); + } + public static String databaseProperName(TOdbcTableType tableType, String name) { switch (tableType) { case MYSQL: @@ -107,6 +112,8 @@ public class OdbcTable extends Table { return psqlProperName(name); case ORACLE: return oracleProperName(name); + case SAP_HANA: + return saphanaProperName(name); default: return name; } 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 index 1b4f5bd076..08d4122126 100644 --- 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 @@ -166,7 +166,7 @@ public class JdbcClient { * @return list of database names */ public List<String> getDatabaseNameList() { - Connection conn = getConnection(); + Connection conn = getConnection(); Statement stmt = null; ResultSet rs = null; if (isOnlySpecifiedDatabase) { @@ -190,6 +190,9 @@ public class JdbcClient { case JdbcResource.SQLSERVER: rs = stmt.executeQuery("SELECT name FROM sys.schemas"); break; + case JdbcResource.SAP_HANA: + rs = stmt.executeQuery("SELECT SCHEMA_NAME FROM SYS.SCHEMAS"); + break; default: throw new JdbcClientException("Not supported jdbc type"); } @@ -216,6 +219,7 @@ public class JdbcClient { case JdbcResource.POSTGRESQL: case JdbcResource.ORACLE: case JdbcResource.SQLSERVER: + case JdbcResource.SAP_HANA: databaseNames.add(conn.getSchema()); break; default: @@ -233,10 +237,10 @@ public class JdbcClient { * get all tables of one database */ public List<String> getTablesNameList(String dbName) { - Connection conn = getConnection(); + Connection conn = getConnection(); ResultSet rs = null; List<String> tablesName = Lists.newArrayList(); - String[] types = { "TABLE", "VIEW" }; + String[] types = {"TABLE", "VIEW"}; try { DatabaseMetaData databaseMetaData = conn.getMetaData(); switch (dbType) { @@ -247,6 +251,7 @@ public class JdbcClient { case JdbcResource.ORACLE: case JdbcResource.CLICKHOUSE: case JdbcResource.SQLSERVER: + case JdbcResource.SAP_HANA: rs = databaseMetaData.getTables(null, dbName, null, types); break; default: @@ -269,9 +274,9 @@ public class JdbcClient { } public boolean isTableExist(String dbName, String tableName) { - Connection conn = getConnection(); + Connection conn = getConnection(); ResultSet rs = null; - String[] types = { "TABLE", "VIEW" }; + String[] types = {"TABLE", "VIEW"}; try { DatabaseMetaData databaseMetaData = conn.getMetaData(); switch (dbType) { @@ -282,6 +287,7 @@ public class JdbcClient { case JdbcResource.ORACLE: case JdbcResource.CLICKHOUSE: case JdbcResource.SQLSERVER: + case JdbcResource.SAP_HANA: rs = databaseMetaData.getTables(null, dbName, null, types); break; default: @@ -325,7 +331,7 @@ public class JdbcClient { * get all columns of one table */ public List<JdbcFieldSchema> getJdbcColumnsInfo(String dbName, String tableName) { - Connection conn = getConnection(); + Connection conn = getConnection(); ResultSet rs = null; List<JdbcFieldSchema> tableSchema = Lists.newArrayList(); // if isLowerCaseTableNames == true, tableName is lower case @@ -352,6 +358,7 @@ public class JdbcClient { case JdbcResource.ORACLE: case JdbcResource.CLICKHOUSE: case JdbcResource.SQLSERVER: + case JdbcResource.SAP_HANA: rs = databaseMetaData.getColumns(null, dbName, tableName, null); break; default: @@ -397,6 +404,8 @@ public class JdbcClient { return oracleTypeToDoris(fieldSchema); case JdbcResource.SQLSERVER: return sqlserverTypeToDoris(fieldSchema); + case JdbcResource.SAP_HANA: + return saphanaTypeToDoris(fieldSchema); default: throw new JdbcClientException("Unknown database type"); } @@ -728,6 +737,59 @@ public class JdbcClient { } } + public Type saphanaTypeToDoris(JdbcFieldSchema fieldSchema) { + String hanaType = fieldSchema.getDataTypeName(); + switch (hanaType) { + case "TINYINT": + return Type.TINYINT; + case "SMALLINT": + return Type.SMALLINT; + case "INTEGER": + return Type.INT; + case "BIGINT": + return Type.BIGINT; + case "SMALLDECIMAL": + case "DECIMAL": { + int precision = fieldSchema.getColumnSize(); + int scale = fieldSchema.getDecimalDigits(); + return createDecimalOrStringType(precision, scale); + } + case "REAL": + return Type.FLOAT; + case "DOUBLE": + return Type.DOUBLE; + case "TIMESTAMP": + case "SECONDDATE": + return ScalarType.createDatetimeV2Type(6); + case "DATE": + return ScalarType.createDateV2Type(); + case "BOOLEAN": + return Type.BOOLEAN; + case "CHAR": + case "NCHAR": + ScalarType charType = ScalarType.createType(PrimitiveType.CHAR); + charType.setLength(fieldSchema.columnSize); + return charType; + case "TIME": + case "VARCHAR": + case "NVARCHAR": + case "ALPHANUM": + case "SHORTTEXT": + return ScalarType.createStringType(); + case "BINARY": + case "VARBINARY": + case "BLOB": + case "CLOB": + case "NCLOB": + case "TEXT": + case "BINTEXT": + case "ST_GEOMETRY": + case "ST_POINT": + default: + return Type.UNSUPPORTED; + } + } + private Type createDecimalOrStringType(int precision, int scale) { if (precision <= ScalarType.MAX_DECIMAL128_PRECISION) { if (!Config.enable_decimal_conversion && (precision > ScalarType.MAX_DECIMALV2_PRECISION 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 8f3c89ebd5..2749dcc213 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 @@ -147,7 +147,8 @@ public class JdbcScanNode extends ScanNode { && (jdbcType == TOdbcTableType.MYSQL || jdbcType == TOdbcTableType.POSTGRESQL || jdbcType == TOdbcTableType.MONGODB - || jdbcType == TOdbcTableType.CLICKHOUSE)) { + || jdbcType == TOdbcTableType.CLICKHOUSE + || jdbcType == TOdbcTableType.SAP_HANA)) { sql.append(" LIMIT ").append(limit); } diff --git a/gensrc/thrift/Types.thrift b/gensrc/thrift/Types.thrift index ba3d28e1a2..622822ec95 100644 --- a/gensrc/thrift/Types.thrift +++ b/gensrc/thrift/Types.thrift @@ -559,7 +559,8 @@ enum TOdbcTableType { SQLSERVER, REDIS, MONGODB, - CLICKHOUSE + CLICKHOUSE, + SAP_HANA } enum TKeysType { --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
