question Re: HDP 3 Hive: create view if not exists fails in Archives of Support Questions (Read Only)

HDP 3 Hive: create view if not exists fails

flashacid — Fri, 24 Aug 2018 18:46:08 GMT

After upgrading to HDP 3.0, I can't run my queries anymore. It seems that "if not exists" is ignored in "create view".

Example:

create view if not exists test as select 1;
create view if not exists test as select 1;
The second call will fail with:
INFO  : Starting task [Stage-1:DDL] in serial mode
ERROR : FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Table already exists: default.test
INFO  : Completed executing command(queryId=hive_20180824114455_907fb7eb-d05a-4831-8482-4f0f3d761721); Time taken: 0.031 seconds
Restarting hiveserver2 and beeline does not help.
Does anybody else has this problem?

Re: HDP 3 Hive: create view if not exists fails

Shelton — Fri, 24 Aug 2018 19:01:12 GMT

@M Ax

It seems to me you are connected to the default hive database and trying to create a view which already exists in that database can you change the SQL to

create view if not exists test20 as select1;

Or just run the below to validate create a test database

Create database  max;
use max;
create view if not exists test20 as select1;

If the above runs fine then it will confirm your initial error was due to the existing object (view test) in the default database.

HTH

Re: HDP 3 Hive: create view if not exists fails

flashacid — Fri, 24 Aug 2018 22:01:38 GMT

Hi Geofffrey, yes the view already exists, but "if not exists" should suppress the error, no matter if which database I'm on. This works fine in HDP 2.6 (Hive 1.2) but does not work in HDP 3.0 (Hive 3.1.0.3.0.0.0-1634) anymore.

If I create a new database "max", your example will fail the second time I call "create view if not exists test20 as select 1;"

Re: HDP 3 Hive: create view if not exists fails

vgarg — Sat, 25 Aug 2018 05:53:26 GMT

This is a bug (confirmed that this exist on upstream/apache master as well). Please go ahead and open a bug on apache hive (Or let me know and I can do that)

Re: HDP 3 Hive: create view if not exists fails

flashacid — Sat, 25 Aug 2018 16:18:33 GMT

Thanks, issue created: https://issues.apache.org/jira/browse/HIVE-20462