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'
Hi @viswanath kammula. The Hive With clause/CTE (Common Table Expression) does not allow that type of nesting in its' syntax. In the documentation:
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.
year, quarter, month, dayofmonth 2016 4 12 1 2016 4 12 2 2016 4 12 3 2016 4 12 4
I hope this helps.