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

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

     [ https://issues.apache.org/jira/browse/HIVE-25605?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Chiran Ravani resolved HIVE-25605.
----------------------------------
    Resolution: Duplicate

> 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
>            Assignee: Chiran Ravani
>            Priority: Major
>              Labels: pull-request-available
>          Time Spent: 50m
>  Remaining Estimate: 0h
>
> 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 Statement*:
> {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"
> );
> {code}
> 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)