Hive ParseException: cannot recognize input near 'create' 'table'

Question

I am trying to conduct a hive query of the form

with (
select a,b from some_db.some_table
) as my_subquery

create table some_other_db.new_table as select * from my_subquery

And I am getting the error

cannot recognize input near 'create' 'table' 'some_other_db' in statement

How do I resolve?

Answer 1

The issue is in hive you cannot include create statements after a with statement. They need to be before.

The following query worked:

create table some_other_db.new_table as 
with (
select a,b from some_db.some_table
) as my_subquery

select * from my_subquery