Support Questions
Find answers, ask questions, and share your expertise

HDP 3 Hive: create view if not exists fails

New Contributor

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?

1 ACCEPTED SOLUTION

Accepted Solutions

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

Cloudera Employee

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)

View solution in original post

4 REPLIES 4

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

Mentor

@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

New Contributor

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

Cloudera Employee

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)

View solution in original post

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

New Contributor