This is an automated email from the ASF dual-hosted git repository. yiguolei pushed a commit to branch branch-2.1 in repository https://gitbox.apache.org/repos/asf/doris.git
commit b3db6b2eae1cc5721b7e686d77af3bae404a02c8 Author: zy-kkk <[email protected]> AuthorDate: Wed Mar 6 22:42:15 2024 +0800 [improvement](jdbc catalog) opt get db2 schema list & xml type mapping (#31856) 1. Trim Schema Names: Adapted the system to remove trailing spaces from DB2 schema names, ensuring compatibility without affecting query operations. 2. XML Mapping: Implemented a feature to directly map XML types to String. --- docs/en/docs/lakehouse/multi-catalog/jdbc.md | 1 + docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md | 1 + .../datasource/jdbc/client/JdbcDB2Client.java | 36 ++++++++++++++++++++++ .../datasource/jdbc/client/JdbcOracleClient.java | 6 ++-- .../jdbc/test_db2_jdbc_catalog.out | 20 ++++++++++++ .../jdbc/test_db2_jdbc_catalog.groovy | 24 +++++++++++++++ 6 files changed, 85 insertions(+), 3 deletions(-) diff --git a/docs/en/docs/lakehouse/multi-catalog/jdbc.md b/docs/en/docs/lakehouse/multi-catalog/jdbc.md index 64f598aec19..e0585cd13e1 100644 --- a/docs/en/docs/lakehouse/multi-catalog/jdbc.md +++ b/docs/en/docs/lakehouse/multi-catalog/jdbc.md @@ -890,6 +890,7 @@ When mapping DB2, Doris's Database corresponds to a Schema under the specified D | LONG VARGRAPHIC | STRING | | | TIME | STRING | | | CLOB | STRING | | +| XML | STRING | | | OTHER | UNSUPPORTED | | ## JDBC Drivers diff --git a/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md b/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md index ec54699227c..ae28cc6ed59 100644 --- a/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md +++ b/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md @@ -894,6 +894,7 @@ CREATE CATALOG `jdbc_db2` PROPERTIES ( | LONG VARGRAPHIC | STRING | | | TIME | STRING | | | CLOB | STRING | | +| XML | STRING | | | OTHER | UNSUPPORTED | | ## JDBC Driver 列表 diff --git a/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcDB2Client.java b/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcDB2Client.java index 5cdcbf1ca4f..e53ff9165ec 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcDB2Client.java +++ b/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcDB2Client.java @@ -21,17 +21,52 @@ import org.apache.doris.catalog.PrimitiveType; import org.apache.doris.catalog.ScalarType; import org.apache.doris.catalog.Type; +import com.google.common.collect.Lists; + +import java.sql.Connection; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; +import java.util.List; + public class JdbcDB2Client extends JdbcClient { protected JdbcDB2Client(JdbcClientConfig jdbcClientConfig) { super(jdbcClientConfig); } + @Override + public List<String> getDatabaseNameList() { + Connection conn = getConnection(); + Statement stmt = null; + ResultSet rs = null; + if (isOnlySpecifiedDatabase && includeDatabaseMap.isEmpty() && excludeDatabaseMap.isEmpty()) { + return getSpecifiedDatabase(conn); + } + List<String> remoteDatabaseNames = Lists.newArrayList(); + try { + rs = conn.getMetaData().getSchemas(conn.getCatalog(), null); + while (rs.next()) { + remoteDatabaseNames.add(rs.getString("TABLE_SCHEM").trim()); + } + } catch (SQLException e) { + throw new JdbcClientException("failed to get database name list from jdbc", e); + } finally { + close(rs, stmt, conn); + } + return filterDatabaseNames(remoteDatabaseNames); + } + @Override protected String getDatabaseQuery() { return "SELECT schemaname FROM syscat.schemata WHERE DEFINER = CURRENT USER;"; } + @Override + protected String getCatalogName(Connection conn) throws SQLException { + return conn.getCatalog(); + } + @Override protected Type jdbcTypeToDoris(JdbcFieldSchema fieldSchema) { String db2Type = fieldSchema.getDataTypeName(); @@ -75,6 +110,7 @@ public class JdbcDB2Client extends JdbcClient { case "CLOB": case "VARGRAPHIC": case "LONG VARGRAPHIC": + case "XML": return ScalarType.createStringType(); default: return Type.UNSUPPORTED; diff --git a/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcOracleClient.java b/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcOracleClient.java index 514e7b81638..4d536a4ef3f 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcOracleClient.java +++ b/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcOracleClient.java @@ -52,18 +52,18 @@ public class JdbcOracleClient extends JdbcClient { if (isOnlySpecifiedDatabase && includeDatabaseMap.isEmpty() && excludeDatabaseMap.isEmpty()) { return getSpecifiedDatabase(conn); } - List<String> databaseNames = Lists.newArrayList(); + List<String> remoteDatabaseNames = Lists.newArrayList(); try { rs = conn.getMetaData().getSchemas(conn.getCatalog(), null); while (rs.next()) { - databaseNames.add(rs.getString("TABLE_SCHEM")); + remoteDatabaseNames.add(rs.getString("TABLE_SCHEM")); } } catch (SQLException e) { throw new JdbcClientException("failed to get database name list from jdbc", e); } finally { close(rs, conn); } - return filterDatabaseNames(databaseNames); + return filterDatabaseNames(remoteDatabaseNames); } @Override diff --git a/regression-test/data/external_table_p0/jdbc/test_db2_jdbc_catalog.out b/regression-test/data/external_table_p0/jdbc/test_db2_jdbc_catalog.out index b8dbcf1bfad..50cfbe42102 100644 --- a/regression-test/data/external_table_p0/jdbc/test_db2_jdbc_catalog.out +++ b/regression-test/data/external_table_p0/jdbc/test_db2_jdbc_catalog.out @@ -41,3 +41,23 @@ VARCHAR_COLUMN VARCHAR(255) Yes true \N 123 1234567890.1234567890 1 12345.6789 12345.679 1.2345678901234567E9 1.2345678901234567E9 123 12345 123456789012345 中文一 中文二 中文三 中文四 中文五 中文六 2024-01-24 2024-01-24T12:34:56.789 12:34:56 中文七 123 1234567890.1234567890 1 12345.6789 12345.679 1.2345678901234567E9 1.2345678901234567E9 123 12345 123456789012345 中文一 中文二 中文三 中文四 中文五 中文六 2024-01-24 2024-01-24T12:34:56.789 12:34:56 中文七 +-- !desc_db -- +DORIS_TEST +NULLID +SQLJ +SYSCAT +SYSFUN +SYSIBM +SYSIBMADM +SYSIBMINTERNAL +SYSIBMTS +SYSPROC +SYSPUBLIC +SYSSTAT +SYSTOOLS +TEST +information_schema + +-- !select_xml -- +1000 <catalog><book><author> Gambardella Matthew</author><title>XML Developers Guide</title><genre>Computer</genre><price>44.95</price><publish_date>2000-10-01</publish_date><description>An in-depth look at creating application\n with XML</description></book></catalog> + diff --git a/regression-test/suites/external_table_p0/jdbc/test_db2_jdbc_catalog.groovy b/regression-test/suites/external_table_p0/jdbc/test_db2_jdbc_catalog.groovy index 68d3066c471..ddd812c7955 100644 --- a/regression-test/suites/external_table_p0/jdbc/test_db2_jdbc_catalog.groovy +++ b/regression-test/suites/external_table_p0/jdbc/test_db2_jdbc_catalog.groovy @@ -37,6 +37,7 @@ suite("test_db2_jdbc_catalog", "p0,external,db2,external_docker,external_docker_ try { db2_docker "CREATE SCHEMA doris_test;" + db2_docker "CREATE SCHEMA test;" db2_docker """CREATE TABLE doris_test.sample_table ( id_column INT GENERATED ALWAYS AS IDENTITY, numeric_column NUMERIC, @@ -239,12 +240,35 @@ suite("test_db2_jdbc_catalog", "p0,external,db2,external_docker,external_docker_ select * except(ID_COLUMN) from ${sample_table}; """ + db2_docker """create table test.books(id bigint not null + primary key, book XML);""" + + db2_docker """insert into test.books values(1000, '<catalog> + <book> + + <author> Gambardella Matthew</author> + <title>XML Developers Guide</title> + <genre>Computer</genre> + <price>44.95</price> + <publish_date>2000-10-01</publish_date> + <description>An in-depth look at creating application + with XML</description> + </book> + + </catalog>');""" + order_qt_sample_table_insert """ select * except(ID_COLUMN) from ${sample_table} order by 1; """ + order_qt_desc_db "show databases from ${catalog_name};" + + order_qt_select_xml "select * from db2.TEST.BOOKS;" + sql """ drop catalog if exists ${catalog_name} """ db2_docker "DROP TABLE IF EXISTS doris_test.sample_table;" db2_docker "DROP SCHEMA doris_test restrict;" + db2_docker "DROP TABLE IF EXISTS test.books;" + db2_docker "DROP SCHEMA test restrict;" } catch (Exception e) { e.printStackTrace() --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
