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 3a45bf72cf7 [opt](oracle) support oracle synonym (#54483)
3a45bf72cf7 is described below
commit 3a45bf72cf7a09931fc81bc6a0efd7498878bcf5
Author: Mingyu Chen (Rayner) <[email protected]>
AuthorDate: Tue Aug 12 10:51:17 2025 -0700
[opt](oracle) support oracle synonym (#54483)
### What problem does this PR solve?
This PR adds support for Oracle synonyms in the JDBC catalog
integration. It enables Doris to recognize and query Oracle synonym
objects as if they were regular tables.
- Adds logic to detect when a table is actually a synonym and resolve it
to the underlying table
- Implements additional SQL queries to fetch synonym information from
Oracle's system views
- Extends the base JdbcClient to support custom table discovery queries
for database-specific features
---
.../docker-compose/oracle/init/02-create-user.sql | 3 ++
.../docker-compose/oracle/init/03-create-table.sql | 4 ++
.../docker-compose/oracle/oracle-11.yaml.tpl | 4 ++
.../doris/datasource/jdbc/client/JdbcClient.java | 38 +++++++++++++++++--
.../datasource/jdbc/client/JdbcOracleClient.java | 42 ++++++++++++++++++++-
.../jdbc/test_oracle_jdbc_catalog.out | Bin 10560 -> 10973 bytes
.../jdbc/test_oracle_jdbc_catalog.groovy | 35 +++++++++++++++++
7 files changed, 121 insertions(+), 5 deletions(-)
diff --git a/docker/thirdparties/docker-compose/oracle/init/02-create-user.sql
b/docker/thirdparties/docker-compose/oracle/init/02-create-user.sql
index dc59e57b78f..d1629b204cc 100644
--- a/docker/thirdparties/docker-compose/oracle/init/02-create-user.sql
+++ b/docker/thirdparties/docker-compose/oracle/init/02-create-user.sql
@@ -17,3 +17,6 @@
create user doris_test identified by 123456;
grant connect, resource to doris_test;
+
+create user synonym_test_user identified by 123456;
+grant connect, resource to synonym_test_user;
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 e3c5345e885..028d95930cf 100644
--- a/docker/thirdparties/docker-compose/oracle/init/03-create-table.sql
+++ b/docker/thirdparties/docker-compose/oracle/init/03-create-table.sql
@@ -22,6 +22,10 @@ age number(2),
score number(3,1)
);
+create SYNONYM DORIS_TEST.test_synonym_student for DORIS_TEST.STUDENT;
+create SYNONYM SYNONYM_TEST_USER.test_synonym_student for DORIS_TEST.STUDENT;
+create SYNONYM SYNONYM_TEST_USER.test_synonym_student2 for DORIS_TEST.STUDENT;
+
create table doris_test.test_num (
id int,
n1 number,
diff --git a/docker/thirdparties/docker-compose/oracle/oracle-11.yaml.tpl
b/docker/thirdparties/docker-compose/oracle/oracle-11.yaml.tpl
index d492762cd9e..09314c9dc23 100644
--- a/docker/thirdparties/docker-compose/oracle/oracle-11.yaml.tpl
+++ b/docker/thirdparties/docker-compose/oracle/oracle-11.yaml.tpl
@@ -46,3 +46,7 @@ networks:
driver: default
config:
- subnet: 168.40.0.0/24
+
+# login in container
+# sqlplus system/[email protected]:1521
+# sqlplus DORIS_TEST/[email protected]:1521
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 c7f6463b984..d723371c13e 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
@@ -416,17 +416,30 @@ public abstract class JdbcClient {
protected void processTable(String remoteDbName, String remoteTableName,
String[] tableTypes,
Consumer<ResultSet> resultSetConsumer) {
Connection conn = null;
- ResultSet rs = null;
+ ResultSet standardRs = null;
+ Statement stmt = null;
+ ResultSet customRs = null;
+
try {
conn = getConnection();
DatabaseMetaData databaseMetaData = conn.getMetaData();
String catalogName = getCatalogName(conn);
- rs = databaseMetaData.getTables(catalogName, remoteDbName,
remoteTableName, tableTypes);
- resultSetConsumer.accept(rs);
+
+ // 1. Process standard tables from getTables() method
+ standardRs = databaseMetaData.getTables(catalogName, remoteDbName,
remoteTableName, tableTypes);
+ resultSetConsumer.accept(standardRs);
+
+ // 2. Process additional tables from custom SQL query (if any)
+ String additionalQuery = getAdditionalTablesQuery(remoteDbName,
remoteTableName, tableTypes);
+ if (additionalQuery != null && !additionalQuery.trim().isEmpty()) {
+ stmt = conn.createStatement();
+ customRs = stmt.executeQuery(additionalQuery);
+ resultSetConsumer.accept(customRs);
+ }
} catch (SQLException e) {
throw new JdbcClientException("Failed to process table", e);
} finally {
- close(rs, conn);
+ close(customRs, stmt, standardRs, conn);
}
}
@@ -473,6 +486,23 @@ public abstract class JdbcClient {
protected abstract Type jdbcTypeToDoris(JdbcFieldSchema fieldSchema);
+ /**
+ * Get additional SQL query for tables that cannot be retrieved from
standard getTables() method.
+ * For example, Oracle SYNONYM tables need custom SQL query.
+ * <p>
+ * Default implementation returns null, meaning no additional query is
needed.
+ * Subclasses can override this method to provide custom SQL queries.
+ *
+ * @param remoteDbName database name
+ * @param remoteTableName table name (can be null for all tables)
+ * @param tableTypes table types array
+ * @return SQL query string, or null if no additional query needed
+ */
+ protected String getAdditionalTablesQuery(String remoteDbName, String
remoteTableName, String[] tableTypes) {
+ // Default implementation: most databases don't need additional queries
+ return null;
+ }
+
protected Type createDecimalOrStringType(int precision, int scale) {
if (precision <= ScalarType.MAX_DECIMAL128_PRECISION && precision > 0)
{
return ScalarType.createDecimalV3Type(precision, scale);
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 adffd06c244..82daa179f0f 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
@@ -22,6 +22,7 @@ import org.apache.doris.catalog.Type;
import org.apache.doris.common.util.Util;
import org.apache.doris.datasource.jdbc.util.JdbcFieldSchema;
+import com.google.common.base.Strings;
import com.google.common.collect.ImmutableSet;
import com.google.common.collect.Lists;
@@ -29,6 +30,7 @@ import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
+import java.sql.Statement;
import java.util.List;
import java.util.Set;
@@ -53,6 +55,9 @@ public class JdbcOracleClient extends JdbcClient {
Connection conn = null;
ResultSet rs = null;
List<JdbcFieldSchema> tableSchema = Lists.newArrayList();
+ Statement stmt = null;
+ ResultSet isSynonymRs = null;
+ ResultSet synonymInfoRs = null;
try {
conn = getConnection();
DatabaseMetaData databaseMetaData = conn.getMetaData();
@@ -76,11 +81,28 @@ public class JdbcOracleClient extends JdbcClient {
}
tableSchema.add(new JdbcFieldSchema(rs));
}
+ if (tableSchema.isEmpty()) {
+ // maybe the table is a synonym
+ stmt = conn.createStatement();
+ isSynonymRs = stmt.executeQuery(
+ "SELECT OBJECT_TYPE FROM ALL_OBJECTS WHERE OBJECT_NAME
= '" + remoteTableName
+ + "' AND OWNER = '" + remoteDbName + "'");
+ if (isSynonymRs.next() &&
"SYNONYM".equalsIgnoreCase(isSynonymRs.getString("OBJECT_TYPE"))) {
+ // if it is a synonym, get the actual table name and
owner(database)
+ String additionalTablesQuery =
getAdditionalTablesQuery(remoteDbName, remoteTableName, null);
+ synonymInfoRs = stmt.executeQuery(additionalTablesQuery);
+ while (synonymInfoRs.next()) {
+ String baseTableName =
synonymInfoRs.getString("BASE_TABLE_NAME");
+ String baseTableOwner =
synonymInfoRs.getString("BASE_TABLE_OWNER");
+ return getJdbcColumnsInfo(baseTableOwner,
baseTableName);
+ }
+ }
+ }
} catch (SQLException e) {
throw new JdbcClientException("failed to get table name list from
jdbc for table %s:%s", e, remoteTableName,
Util.getRootCauseMessage(e));
} finally {
- close(rs, conn);
+ close(rs, conn, stmt, isSynonymRs, synonymInfoRs);
}
return tableSchema;
}
@@ -109,6 +131,24 @@ public class JdbcOracleClient extends JdbcClient {
.build();
}
+ @Override
+ protected String getAdditionalTablesQuery(String remoteDbName, String
remoteTableName, String[] tableTypes) {
+ StringBuilder sb = new StringBuilder(
+ "SELECT SYNONYM_NAME as TABLE_NAME, TABLE_OWNER as
BASE_TABLE_OWNER, TABLE_NAME as BASE_TABLE_NAME "
+ + "FROM ALL_SYNONYMS");
+ List<String> conditions = Lists.newArrayList();
+ if (!Strings.isNullOrEmpty(remoteDbName)) {
+ conditions.add("OWNER = '" + remoteDbName + "'");
+ }
+ if (!Strings.isNullOrEmpty(remoteTableName)) {
+ conditions.add("SYNONYM_NAME = '" + remoteTableName + "'");
+ }
+ if (!conditions.isEmpty()) {
+ sb.append(" WHERE ").append(String.join(" AND ", conditions));
+ }
+ return sb.toString();
+ }
+
@Override
protected Type jdbcTypeToDoris(JdbcFieldSchema fieldSchema) {
String oracleType = fieldSchema.getDataTypeName().orElse("unknown");
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 d2806d74ab6..890f9e17b16 100644
Binary files
a/regression-test/data/external_table_p0/jdbc/test_oracle_jdbc_catalog.out and
b/regression-test/data/external_table_p0/jdbc/test_oracle_jdbc_catalog.out
differ
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 818de72bca7..27a9249d10e 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
@@ -457,6 +457,41 @@ suite("test_oracle_jdbc_catalog",
"p0,external,oracle,external_docker,external_d
exception """Failed to parse push down rules: invalid_json"""
}
+ // test synonym
+ sql """drop catalog if exists oracle_test_synonym"""
+ sql """create catalog oracle_test_synonym 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"
+ );"""
+
+ // TEST_SYNONYM_STUDENT is a synonym of "student" table in DORIS_TEST
+ order_qt_sql_syn01 """select * from
oracle_test_synonym.DORIS_TEST.TEST_SYNONYM_STUDENT"""
+ // should be empty, because user "doris_test" has no priv on
SYNONYM_TEST_USER
+ qt_sql_syn02 """show tables from
oracle_test_synonym.SYNONYM_TEST_USER;"""
+
+ // create catalog with admin priv
+ sql """drop catalog if exists oracle_test_synonym_sys"""
+ sql """create catalog oracle_test_synonym_sys properties(
+ "type"="jdbc",
+ "user"="system",
+ "password"="oracle",
+ "jdbc_url" =
"jdbc:oracle:thin:@${externalEnvIp}:${oracle_port}:${SID}",
+ "driver_url" = "${driver_url}",
+ "driver_class" = "oracle.jdbc.driver.OracleDriver"
+ );"""
+
+ // can still see synonym in DORIS_TEST
+ order_qt_sql_syn01 """select * from
oracle_test_synonym_sys.DORIS_TEST.TEST_SYNONYM_STUDENT"""
+ // should has priv to see 2 synonym in SYNONYM_TEST_USER
+ qt_sql_syn02 """show tables from
oracle_test_synonym_sys.SYNONYM_TEST_USER;"""
+ order_qt_sql_syn03 """select * from
oracle_test_synonym_sys.SYNONYM_TEST_USER.TEST_SYNONYM_STUDENT"""
+ order_qt_sql_syn04 """select * from
oracle_test_synonym_sys.SYNONYM_TEST_USER.TEST_SYNONYM_STUDENT2"""
+
+
// sql """ drop catalog if exists oracle_null_operator; """
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
