You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hive.apache.org by "Chiran Ravani (Jira)" <ji...@apache.org> on 2021/10/08 20:13:00 UTC
[jira] [Updated] (HIVE-25605) JdbcStorageHandler Create table fails
when hive.sql.schema is specified and is not the default one
[ https://issues.apache.org/jira/browse/HIVE-25605?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Chiran Ravani updated HIVE-25605:
---------------------------------
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
{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.
was:
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.
> 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
> Priority: Major
>
> 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)