[
https://issues.apache.org/jira/browse/HIVE-25605?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Chiran Ravani reassigned HIVE-25605:
------------------------------------
Assignee: Chiran Ravani
> 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: 10m
> 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)
