[HIVE-25591] CREATE EXTERNAL TABLE fails for JDBC tables stored in non-default schema

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 4.0.0-alpha-1
• Component/s: Query Planning
• Labels: pull-request-available
• Assignee: Stamatis Zampetakis
• Reporter: Stamatis Zampetakis
• Created: 05/Oct/21 11:15
• Updated: 17/Nov/22 08:48
• Resolved: 03/Nov/21 22:26

Description

Consider the following use case where tables reside in some user-defined schema in some JDBC compliant database:

Postgres

create schema world;

create table if not exists world.country (name varchar(80) not null);

insert into world.country (name) values ('India');
insert into world.country (name) values ('Russia');
insert into world.country (name) values ('USA');

The following DDL statement in Hive fails:

Hive

CREATE EXTERNAL TABLE country (name varchar(80))
STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
TBLPROPERTIES (
"hive.sql.database.type" = "POSTGRES",
"hive.sql.jdbc.driver" = "org.postgresql.Driver",
"hive.sql.jdbc.url" = "jdbc:postgresql://localhost:5432/test",
"hive.sql.dbcp.username" = "user",
"hive.sql.dbcp.password" = "pwd",
"hive.sql.schema" = "world",
"hive.sql.table" = "country");

The exception is the following:

org.postgresql.util.PSQLException: ERROR: relation "country" does not exist
  Position: 15
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2532) ~[postgresql-42.2.14.jar:42.2.14]
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2267) ~[postgresql-42.2.14.jar:42.2.14]
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:312) ~[postgresql-42.2.14.jar:42.2.14]
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448) ~[postgresql-42.2.14.jar:42.2.14]
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369) ~[postgresql-42.2.14.jar:42.2.14]
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:153) ~[postgresql-42.2.14.jar:42.2.14]
	at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:103) ~[postgresql-42.2.14.jar:42.2.14]
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122) ~[commons-dbcp2-2.7.0.jar:2.7.0]
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122) ~[commons-dbcp2-2.7.0.jar:2.7.0]
	at org.apache.hive.storage.jdbc.dao.GenericJdbcDatabaseAccessor.getColumnNames(GenericJdbcDatabaseAccessor.java:83) [hive-jdbc-handler-4.0.0-SNAPSHOT.jar:4.0.0-SNAPSHOT]
	at org.apache.hive.storage.jdbc.JdbcSerDe.initialize(JdbcSerDe.java:98) [hive-jdbc-handler-4.0.0-SNAPSHOT.jar:4.0.0-SNAPSHOT]
	at org.apache.hadoop.hive.metastore.HiveMetaStoreUtils.getDeserializer(HiveMetaStoreUtils.java:95) [hive-metastore-4.0.0-SNAPSHOT.jar:4.0.0-SNAPSHOT]
	at org.apache.hadoop.hive.metastore.HiveMetaStoreUtils.getDeserializer(HiveMetaStoreUtils.java:78) [hive-metastore-4.0.0-SNAPSHOT.jar:4.0.0-SNAPSHOT]
	at org.apache.hadoop.hive.ql.metadata.Table.getDeserializerFromMetaStore(Table.java:342) [hive-exec-4.0.0-SNAPSHOT.jar:4.0.0-SNAPSHOT]
	at org.apache.hadoop.hive.ql.metadata.Table.getDeserializer(Table.java:324) [hive-exec-4.0.0-SNAPSHOT.jar:4.0.0-SNAPSHOT]
	at org.apache.hadoop.hive.ql.metadata.Table.getColsInternal(Table.java:734) [hive-exec-4.0.0-SNAPSHOT.jar:4.0.0-SNAPSHOT]
	at org.apache.hadoop.hive.ql.metadata.Table.getCols(Table.java:717) [hive-exec-4.0.0-SNAPSHOT.jar:4.0.0-SNAPSHOT]
	at org.apache.hadoop.hive.ql.ddl.table.create.CreateTableDesc.toTable(CreateTableDesc.java:933) [hive-exec-4.0.0-SNAPSHOT.jar:4.0.0-SNAPSHOT]
	at org.apache.hadoop.hive.ql.ddl.table.create.CreateTableOperation.execute(CreateTableOperation.java:59) [hive-exec-4.0.0-SNAPSHOT.jar:4.0.0-SNAPSHOT]
	at org.apache.hadoop.hive.ql.ddl.DDLTask.execute(DDLTask.java:84) [hive-exec-4.0.0-SNAPSHOT.jar:4.0.0-SNAPSHOT]
	at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:212) [hive-exec-4.0.0-SNAPSHOT.jar:4.0.0-SNAPSHOT]
	at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:105) [hive-exec-4.0.0-SNAPSHOT.jar:4.0.0-SNAPSHOT]
	at org.apache.hadoop.hive.ql.Executor.launchTask(Executor.java:361) [hive-exec-4.0.0-SNAPSHOT.jar:4.0.0-SNAPSHOT]
	at org.apache.hadoop.hive.ql.Executor.launchTasks(Executor.java:334) [hive-exec-4.0.0-SNAPSHOT.jar:4.0.0-SNAPSHOT]
	at org.apache.hadoop.hive.ql.Executor.runTasks(Executor.java:245) [hive-exec-4.0.0-SNAPSHOT.jar:4.0.0-SNAPSHOT]
	at org.apache.hadoop.hive.ql.Executor.execute(Executor.java:108) [hive-exec-4.0.0-SNAPSHOT.jar:4.0.0-SNAPSHOT]
	at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:348) [hive-exec-4.0.0-SNAPSHOT.jar:4.0.0-SNAPSHOT]

Issue Links

• duplicates: HIVE-25605 JdbcStorageHandler Create table fails when hive.sql.schema is specified and is not the default one
• relates to: HIVE-21383 JDBC storage handler: Use catalog and schema to retrieve tables if specified
• links to: GitHub Pull Request #2759