Usage of with clause in hive

[#1]

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]

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.

[#3]

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