Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

HDP 3 Hive: create view if not exists fails

avatar
Explorer

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

avatar
Contributor
hide-solution

This problem has been solved!

Want to get a detailed solution you have to login/registered on the community

Register/Login
4 REPLIES 4

avatar
Master 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

avatar
Explorer

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;"

avatar
Contributor
hide-solution

This problem has been solved!

Want to get a detailed solution you have to login/registered on the community

Register/Login

avatar
Explorer