[HIVE-25605] JdbcStorageHandler Create table fails when hive.sql.schema is specified and is not the default one

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 4.0.0
• Component/s: JDBC storage handler
• Labels: pull-request-available
• Assignee: Chiran Ravani
• Reporter: Chiran Ravani
• Created: 08/Oct/21 20:11
• Updated: 09/Oct/21 17:13
• Resolved: 09/Oct/21 17:13

Description

We have observed create table statement failure for JdbcStorageHandler with Oracle when Schema name is specified in Table properties and that schema is not the default one for user.

eg:-
Consider Username: DI_METADATA with default schema DI_METADATA in Oracle, however this user has access to other schemas as well like schema name CHIRAN, when using below create statement in Hive it fails with error

org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.RuntimeException: MetaException(message:org.apache.hadoop.hive.serde2.SerDeException org.apache.hive.storage.jdbc.exception.HiveJdbcDatabaseAccessException: Error while trying to get column names: ORA-00942: table or view does not exist

Create Statement:

CREATE EXTERNAL TABLE if not exists query_fed_oracle.ABCD_TEST_pw_case_jceks_diff(
  YEAR INT,
  QUANTITY INT,
  NAME STRING
)
STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
TBLPROPERTIES (
  "bucketing_version"="2",
  "hive.sql.database.type" = "ORACLE",
  "hive.sql.jdbc.driver" = "oracle.jdbc.OracleDriver",
  "hive.sql.jdbc.url" = "jdbc:oracle:thin:@//obfuscated.compute-1.amazonaws.com",
  "hive.sql.dbcp.username" = "DI_METADATA",
  "hive.sql.dbcp.password.keystore" = "jceks://s3a@obfuscated-bucket/test.jceks",
  "hive.sql.dbcp.password.key" = "oracle.secret",
  "hive.sql.schema" = "CHIRAN",
  "hive.sql.table" = "ABCD_TEST_1",
  "hive.sql.dbcp.maxActive" = "1"
);

This can be fixed by using "hive.sql.table" = "CHIRAN.ABCD_TEST_1", but this will break CBO as pushdown wont happen. Possible fix would be to include schemaName check too after below call.
https://github.com/apache/hive/blob/master/jdbc-handler/src/main/java/org/apache/hive/storage/jdbc/conf/JdbcStorageConfigManager.java#L166

Attaching patch 1. Let me know if this looks good.

Issue Links

• is duplicated by: HIVE-25591 CREATE EXTERNAL TABLE fails for JDBC tables stored in non-default schema
• links to: GitHub Pull Request #2710