This is an automated email from the ASF dual-hosted git repository.
morningman pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new a7675243d9 [fix](jdbc catalog) fix adaptation to Oracle special
character `/` table names (#23080)
a7675243d9 is described below
commit a7675243d96c62d7e0f84aac5bc83fb105c45d5b
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 e6e969ddf3..b105c1e671 100644
--- a/docs/en/docs/lakehouse/multi-catalog/jdbc.md
+++ b/docs/en/docs/lakehouse/multi-catalog/jdbc.md
@@ -284,6 +284,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 75e81c2177..8e60826eb2 100644
--- a/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md
+++ b/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md
@@ -284,6 +284,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 ff6e88118a..e84622b4e7 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
@@ -156,5 +156,19 @@ suite("test_oracle_jdbc_catalog",
"p0,external,oracle,external_docker,external_d
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]
