This is an automated email from the ASF dual-hosted git repository. kxiao pushed a commit to branch branch-2.0 in repository https://gitbox.apache.org/repos/asf/doris.git
commit 81585c0835272d251e00eda62cc4df8d8bb7542d Author: zy-kkk <[email protected]> AuthorDate: Tue Aug 22 15:25:42 2023 +0800 [fix](jdbc catalog) fix adaptation to Oracle special character `/` table names (#23080) The changes of this PR for JdbcOracleClient are as follows: #### bug fixes: 1. Fix the problem that if there is an approximate table name for Schema synchronization with a table name with `/` characters, the synchronization Column will be confused 2. Fix the NPE problem of metadata synchronization after enabling lower_case_table_names configuration #### improvement: 1. Modify the method of synchronizing Oracle User to Doris Database mapping, use `metadata.getSchemas` instead of `SELECT DISTINCT OWNER FROM all_tables` 2. When synchronizing metadata, change `null` at the catalog level to `conn.getcatalog` --- .../docker-compose/oracle/init/03-create-table.sql | 12 ++ .../docker-compose/oracle/init/04-insert.sql | 3 + docs/en/docs/lakehouse/multi-catalog/jdbc.md | 2 + docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md | 2 + .../doris/datasource/jdbc/client/JdbcClient.java | 55 ++++++--- .../datasource/jdbc/client/JdbcMySQLClient.java | 4 - .../datasource/jdbc/client/JdbcOracleClient.java | 128 +++++++++++++++++++++ .../jdbc/test_oracle_jdbc_catalog.out | 6 + .../jdbc/test_oracle_jdbc_catalog.groovy | 14 +++ 9 files changed, 206 insertions(+), 20 deletions(-) diff --git a/docker/thirdparties/docker-compose/oracle/init/03-create-table.sql b/docker/thirdparties/docker-compose/oracle/init/03-create-table.sql index c5f6b69121..d2c4d93ef0 100644 --- a/docker/thirdparties/docker-compose/oracle/init/03-create-table.sql +++ b/docker/thirdparties/docker-compose/oracle/init/03-create-table.sql @@ -112,3 +112,15 @@ create table doris_test.test_clob ( id number(11) not null primary key, num1 clob ); + +create table doris_test."AA/D" ( + id number(5), + name varchar2(20), + age number(2), + score number(3,1) +); + +create table doris_test.aaad ( + id number(5), + name varchar2(20) +); \ No newline at end of file diff --git a/docker/thirdparties/docker-compose/oracle/init/04-insert.sql b/docker/thirdparties/docker-compose/oracle/init/04-insert.sql index ff4a38afce..b0b5111217 100644 --- a/docker/thirdparties/docker-compose/oracle/init/04-insert.sql +++ b/docker/thirdparties/docker-compose/oracle/init/04-insert.sql @@ -77,4 +77,7 @@ insert into doris_test.test_number4 values (1, 12345678); insert into doris_test.test_number4 values (2, 123456789012); insert into doris_test.test_clob values (10086, 'yidong'); insert into doris_test.test_clob values (10010, 'liantong'); + +insert into doris_test."AA/D" values (1, 'alice', 20, 99.5); +insert into doris_test.aaad values (1, 'alice'); commit; diff --git a/docs/en/docs/lakehouse/multi-catalog/jdbc.md b/docs/en/docs/lakehouse/multi-catalog/jdbc.md index 3f8c627ef9..13afc47ac0 100644 --- a/docs/en/docs/lakehouse/multi-catalog/jdbc.md +++ b/docs/en/docs/lakehouse/multi-catalog/jdbc.md @@ -277,6 +277,8 @@ As for data mapping from Oracle to Doris, one Database in Doris corresponds to o | Database | User | | Table | Table | +**NOTE:** Synchronizing Oracle's SYNONYM TABLE is not currently supported. + #### Type Mapping | ORACLE Type | Doris Type | Comment | diff --git a/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md b/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md index 377a495fbe..270a2ac52b 100644 --- a/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md +++ b/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md @@ -277,6 +277,8 @@ CREATE CATALOG jdbc_oracle PROPERTIES ( | Database | User | | Table | Table | +**注意：** 当前不支持同步 Oracle 的 SYNONYM TABLE + #### 类型映射 | ORACLE Type | Doris Type | Comment | diff --git a/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcClient.java b/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcClient.java index 751965cc59..90070bdba7 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcClient.java +++ b/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcClient.java @@ -232,13 +232,18 @@ public abstract class JdbcClient { * get all tables of one database */ public List<String> getTablesNameList(String dbName) { + String currentDbName = dbName; List<String> tablesName = Lists.newArrayList(); String[] tableTypes = getTableTypes(); if (isLowerCaseTableNames) { - dbName = lowerDBToRealDB.get(dbName); + currentDbName = lowerDBToRealDB.get(dbName); + if (currentDbName == null) { + getDatabaseNameList(); + currentDbName = lowerDBToRealDB.get(dbName); + } } - String finalDbName = dbName; - processTable(dbName, null, tableTypes, (rs) -> { + String finalDbName = currentDbName; + processTable(finalDbName, null, tableTypes, (rs) -> { try { while (rs.next()) { String tableName = rs.getString("TABLE_NAME"); @@ -256,15 +261,25 @@ public abstract class JdbcClient { } public boolean isTableExist(String dbName, String tableName) { + String currentDbName = dbName; + String currentTableName = tableName; final boolean[] isExist = {false}; if (isLowerCaseTableNames) { - dbName = lowerDBToRealDB.get(dbName); - tableName = lowerTableToRealTable.get(tableName); + currentDbName = lowerDBToRealDB.get(dbName); + currentTableName = lowerTableToRealTable.get(tableName); + if (currentDbName == null) { + getDatabaseNameList(); + currentDbName = lowerDBToRealDB.get(dbName); + } + if (currentTableName == null) { + getTablesNameList(dbName); + currentTableName = lowerTableToRealTable.get(tableName); + } } String[] tableTypes = getTableTypes(); - String finalTableName = tableName; - String finalDbName = dbName; - processTable(dbName, tableName, tableTypes, (rs) -> { + String finalTableName = currentTableName; + String finalDbName = currentDbName; + processTable(finalDbName, finalTableName, tableTypes, (rs) -> { try { if (rs.next()) { isExist[0] = true; @@ -286,19 +301,27 @@ public abstract class JdbcClient { List<JdbcFieldSchema> tableSchema = Lists.newArrayList(); // if isLowerCaseTableNames == true, tableName is lower case // but databaseMetaData.getColumns() is case sensitive + String currentDbName = dbName; + String currentTableName = tableName; if (isLowerCaseTableNames) { - dbName = lowerDBToRealDB.get(dbName); - tableName = lowerTableToRealTable.get(tableName); + currentDbName = lowerDBToRealDB.get(dbName); + currentTableName = lowerTableToRealTable.get(tableName); + if (currentDbName == null) { + getDatabaseNameList(); + currentDbName = lowerDBToRealDB.get(dbName); + } + if (currentTableName == null) { + getTablesNameList(dbName); + currentTableName = lowerTableToRealTable.get(tableName); + } } + String finalDbName = currentDbName; + String finalTableName = currentTableName; try { DatabaseMetaData databaseMetaData = conn.getMetaData(); String catalogName = getCatalogName(conn); - tableName = modifyTableNameIfNecessary(tableName); - rs = getColumns(databaseMetaData, catalogName, dbName, tableName); + rs = getColumns(databaseMetaData, catalogName, finalDbName, finalTableName); while (rs.next()) { - if (isTableModified(tableName, rs.getString("TABLE_NAME"))) { - continue; - } JdbcFieldSchema field = new JdbcFieldSchema(); field.setColumnName(rs.getString("COLUMN_NAME")); field.setDataType(rs.getInt("DATA_TYPE")); @@ -323,7 +346,7 @@ public abstract class JdbcClient { tableSchema.add(field); } } catch (SQLException e) { - throw new JdbcClientException("failed to get table name list from jdbc for table %s:%s", e, tableName, + throw new JdbcClientException("failed to get table name list from jdbc for table %s:%s", e, finalTableName, Util.getRootCauseMessage(e)); } finally { close(rs, conn); diff --git a/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcMySQLClient.java b/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcMySQLClient.java index 7d4f342ac5..0f856ad3e9 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcMySQLClient.java +++ b/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcMySQLClient.java @@ -139,15 +139,11 @@ public class JdbcMySQLClient extends JdbcClient { try { DatabaseMetaData databaseMetaData = conn.getMetaData(); String catalogName = getCatalogName(conn); - tableName = modifyTableNameIfNecessary(tableName); rs = getColumns(databaseMetaData, catalogName, dbName, tableName); List<String> primaryKeys = getPrimaryKeys(databaseMetaData, catalogName, dbName, tableName); boolean needGetDorisColumns = true; Map<String, String> mapFieldtoType = null; while (rs.next()) { - if (isTableModified(tableName, rs.getString("TABLE_NAME"))) { - continue; - } JdbcFieldSchema field = new JdbcFieldSchema(); field.setColumnName(rs.getString("COLUMN_NAME")); field.setDataType(rs.getInt("DATA_TYPE")); 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 ae697b87be..410e10df20 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 @@ -19,6 +19,15 @@ package org.apache.doris.datasource.jdbc.client; import org.apache.doris.catalog.ScalarType; import org.apache.doris.catalog.Type; +import org.apache.doris.common.util.Util; + +import com.google.common.collect.Lists; + +import java.sql.Connection; +import java.sql.DatabaseMetaData; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.List; public class JdbcOracleClient extends JdbcClient { @@ -31,6 +40,125 @@ public class JdbcOracleClient extends JdbcClient { return "SELECT DISTINCT OWNER FROM all_tables"; } + @Override + protected String getCatalogName(Connection conn) throws SQLException { + return conn.getCatalog(); + } + + @Override + public List<String> getDatabaseNameList() { + Connection conn = getConnection(); + ResultSet rs = null; + if (isOnlySpecifiedDatabase && includeDatabaseMap.isEmpty() && excludeDatabaseMap.isEmpty()) { + return getSpecifiedDatabase(conn); + } + List<String> databaseNames = Lists.newArrayList(); + try { + rs = conn.getMetaData().getSchemas(conn.getCatalog(), null); + List<String> tempDatabaseNames = Lists.newArrayList(); + while (rs.next()) { + String databaseName = rs.getString("TABLE_SCHEM"); + if (isLowerCaseTableNames) { + lowerDBToRealDB.put(databaseName.toLowerCase(), databaseName); + databaseName = databaseName.toLowerCase(); + } + tempDatabaseNames.add(databaseName); + } + if (isOnlySpecifiedDatabase) { + for (String db : tempDatabaseNames) { + // Exclude database map take effect with higher priority over include database map + if (!excludeDatabaseMap.isEmpty() && excludeDatabaseMap.containsKey(db)) { + continue; + } + if (!includeDatabaseMap.isEmpty() && !includeDatabaseMap.containsKey(db)) { + continue; + } + databaseNames.add(db); + } + } else { + databaseNames = tempDatabaseNames; + } + } catch (SQLException e) { + throw new JdbcClientException("failed to get database name list from jdbc", e); + } finally { + close(rs, conn); + } + return databaseNames; + } + + @Override + public List<JdbcFieldSchema> getJdbcColumnsInfo(String dbName, String tableName) { + Connection conn = getConnection(); + ResultSet rs = null; + List<JdbcFieldSchema> tableSchema = Lists.newArrayList(); + String currentDbName = dbName; + String currentTableName = tableName; + if (isLowerCaseTableNames) { + currentDbName = lowerDBToRealDB.get(dbName); + currentTableName = lowerTableToRealTable.get(tableName); + if (currentDbName == null) { + getDatabaseNameList(); + currentDbName = lowerDBToRealDB.get(dbName); + } + if (currentTableName == null) { + getTablesNameList(dbName); + currentTableName = lowerTableToRealTable.get(tableName); + } + } + String finalDbName = currentDbName; + String finalTableName = currentTableName; + try { + DatabaseMetaData databaseMetaData = conn.getMetaData(); + String catalogName = getCatalogName(conn); + String modifiedTableName; + boolean isModify = false; + if (finalTableName.contains("/")) { + modifiedTableName = modifyTableNameIfNecessary(finalTableName); + isModify = !modifiedTableName.equals(finalTableName); + if (isModify) { + rs = getColumns(databaseMetaData, catalogName, finalDbName, modifiedTableName); + } else { + rs = getColumns(databaseMetaData, catalogName, finalDbName, finalTableName); + } + } else { + rs = getColumns(databaseMetaData, catalogName, finalDbName, finalTableName); + } + while (rs.next()) { + if (isModify && isTableModified(rs.getString("TABLE_NAME"), finalTableName)) { + continue; + } + JdbcFieldSchema field = new JdbcFieldSchema(); + field.setColumnName(rs.getString("COLUMN_NAME")); + field.setDataType(rs.getInt("DATA_TYPE")); + field.setDataTypeName(rs.getString("TYPE_NAME")); + /* + We used this method to retrieve the key column of the JDBC table, but since we only tested mysql, + we kept the default key behavior in the parent class and only overwrite it in the mysql subclass + */ + field.setKey(true); + field.setColumnSize(rs.getInt("COLUMN_SIZE")); + field.setDecimalDigits(rs.getInt("DECIMAL_DIGITS")); + field.setNumPrecRadix(rs.getInt("NUM_PREC_RADIX")); + /* + Whether it is allowed to be NULL + 0 (columnNoNulls) + 1 (columnNullable) + 2 (columnNullableUnknown) + */ + field.setAllowNull(rs.getInt("NULLABLE") != 0); + 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:%s", e, finalTableName, + Util.getRootCauseMessage(e)); + } finally { + close(rs, conn); + } + return tableSchema; + } + @Override protected String modifyTableNameIfNecessary(String tableName) { return tableName.replace("/", "%"); diff --git a/regression-test/data/external_table_p0/jdbc/test_oracle_jdbc_catalog.out b/regression-test/data/external_table_p0/jdbc/test_oracle_jdbc_catalog.out index a6733d6a3f..49630c56a0 100644 --- a/regression-test/data/external_table_p0/jdbc/test_oracle_jdbc_catalog.out +++ b/regression-test/data/external_table_p0/jdbc/test_oracle_jdbc_catalog.out @@ -114,3 +114,9 @@ DORIS_TEST 10010 liantong 10086 yidong +-- !query_ad1 -- +1 alice 20 99.5 + +-- !query_ad2 -- +1 alice + diff --git a/regression-test/suites/external_table_p0/jdbc/test_oracle_jdbc_catalog.groovy b/regression-test/suites/external_table_p0/jdbc/test_oracle_jdbc_catalog.groovy index ce85b618c0..8c6c3a6936 100644 --- a/regression-test/suites/external_table_p0/jdbc/test_oracle_jdbc_catalog.groovy +++ b/regression-test/suites/external_table_p0/jdbc/test_oracle_jdbc_catalog.groovy @@ -150,5 +150,19 @@ suite("test_oracle_jdbc_catalog", "p0") { sql """ switch ${catalog_name} """ qt_query_clob """ select * from doris_test.test_clob order by id; """ + // test for `AA/D` + sql """create catalog if not exists ${catalog_name} properties( + "type"="jdbc", + "user"="doris_test", + "password"="123456", + "jdbc_url" = "jdbc:oracle:thin:@${externalEnvIp}:${oracle_port}:${SID}", + "driver_url" = "${driver_url}", + "driver_class" = "oracle.jdbc.driver.OracleDriver", + "lower_case_table_names" = "true" + );""" + sql """ switch ${catalog_name} """ + qt_query_ad1 """ select * from doris_test.`aa/d` order by id; """ + qt_query_ad2 """ select * from doris_test.aaad order by id; """ + } } --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
