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)
