Hive explode results in table

Question

Could someone tell me what's wrong with this code? If I run the select statement only, then its returning the results.

The code is working if I remove one of the line "location" or "stored as textfile". Also please let me know if I can specify 'delimiter' too.

create table exploderesults 
location '/user/cloudera/sometest'
stored as textfile
as
select id,ph as phone, ct as city from explodetest
lateral view explode(phone)p as ph
lateral view explode(city)c as ct;

Thanks

Answer 1

Swap the two rows stored as and location. They have to be in a particular order according to the manual

create table exploderesults 
stored as textfile
location '/user/cloudera/sometest'
as
select id,ph as phone, ct as city from explodetest
lateral view explode(phone)p as ph
lateral view explode(city)c as ct;

If you want to specify a delimiter,

create table exploderesults 
row format delimited fields terminated by ','
stored as textfile
location '/user/cloudera/sometest'
as
select id,ph as phone, ct as city from explodetest
lateral view explode(phone)p as ph
lateral view explode(city)c as ct;