Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Usage of with clause in hive

Highlighted

Usage of with clause in hive

I am trying to run a query in hive which is like shown below

select * from

(with View as (select * from table) select * from view) t1

I am able to execute the subquery but unable to run the full query .Hive throws an error saying

NoViableAltException(26@[146:1: fromSource :

Does hive not allow to use select from a view created with 'with clause'

2 REPLIES 2

Re: Usage of with clause in hive

Hi @viswanath kammula. The Hive With clause/CTE (Common Table Expression) does not allow that type of nesting in its' syntax. In the documentation:

https://cwiki.apache.org/confluence/display/Hive/Common+Table+Expression

It states "The WITH clause is not supported within SubQuery Blocks." What you are trying to do could be accomplished (with a little different syntax) if you use a permanent view, or a temporary table.

Also note: that same link, look at the bottom section titled "CTE in Views, CTAS, and Insert Statements." It shows you how to use CTEs (with clause) along with Views. The "View example" is similar to what yo want to do.

Here is a code example of a simple CTE/With statement (for the benefit of other people who will read this post):

with date_table as (select distinct year, quarter, month, dayofmonth 
                   from  flight_performance_orc_part
                   where yearmonth = '2016-12' limit 4) 
select * from date_table d ;  -- Or any other complex query, joins to other tables, etc.

Results:

year, quarter, month, dayofmonth
2016   4   12  1
2016   4   12 2
2016   4   12 3
2016   4   12 4

I hope this helps.

Highlighted

Re: Usage of with clause in hive

New Contributor

What if i want to use date_table within a subquery? can you provide the syntax?

Don't have an account?
Coming from Hortonworks? Activate your account here