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

["Chiran Ravani (Jira)" <ji...@apache.org>]

Chiran Ravani created HIVE-25605:
------------------------------------

             Summary: JdbcStorageHandler Create table fails when hive.sql.schema is specified and is not the default one
                 Key: HIVE-25605
                 URL: https://issues.apache.org/jira/browse/HIVE-25605
             Project: Hive
          Issue Type: Bug
          Components: JDBC storage handler
    Affects Versions: 4.0.0
            Reporter: Chiran Ravani


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
{code}
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
{code}

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.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)