CODEWITHSUNDEEP
hadoopviewhivebigdatahiveql
Ganesh Dogiparthi
Ganesh Dogiparthi

Reputation: 385

How to create static views in Hive?

How to create a static view in Hive? Like I am creating below view, 

CREATE OR REPLACE VIEW V_TMP AS 
SELECT 1 AS LEVEL_ID,'URBAN' AS ITEM_CODE
UNION ALL
SELECT 1 AS LEVEL_ID,'RURAL' AS ITEM_CODE
UNION ALL
SELECT 9999 AS LEVEL_ID,'ALL' AS ITEM_CODE;

it is throwing below error:

Error while compiling statement: FAILED: SemanticException No valid privileges Required privilege( Table) not available in input privileges The required privileges:

Can someone help?

Upvotes: 0

Views: 709

Answers (2)

salmanbw
salmanbw

Reputation: 1311

Given query won't work as it is not fetching data from any table. In hive, while creating a static view, you still need to fetch data from a table. If it would have been oracle, you could have fetch from dummy table directly.

So there are 2 approaches that you can do to create a static view.

  1. Point it to any existing table and use where 1=1 condition.

  2. Create a dummy table and select it using from dummy table like below.

    create table dummy (id int)

    insert into dummy(1)

    CREATE OR REPLACE VIEW V_TMP1 (LEVEL_ID , ITEM_CODE ) AS SELECT 1 AS LEVEL_ID,'URBAN' AS ITEM_CODE from dummy UNION ALL SELECT 1 AS LEVEL_ID,'RURAL' AS ITEM_CODE from dummy UNION ALL SELECT 9999 AS LEVEL_ID,'ALL' AS ITEM_CODE from dummy;

Upvotes: 1

Deepan Ram
Deepan Ram

Reputation: 850

Do check if you have the necessary permissions in the hive warehouse and try to use any other database to create your view.

Sometimes, the default database is not given permission for all the users.

If you are using Kerberos cluster, you need to check whether the Hive user has the necessary sentry roles or not.

Or else :-

CREATE OR REPLACE VIEW V_TMP1 (LEVEL_ID , ITEM_CODE  ) AS 
SELECT 1 AS LEVEL_ID,'URBAN' AS ITEM_CODE
UNION ALL
SELECT 1 AS LEVEL_ID,'RURAL' AS ITEM_CODE
UNION ALL
SELECT 9999 AS LEVEL_ID,'ALL' AS ITEM_CODE;

Upvotes: 0

Related Questions