Hi Vidya, I believe you need to remove Location '/user/hive/warehouse/default.db/jobs_ex2' line since it stores the table automatically in warehouse once you create the table.
On Sat, Jul 19, 2014 at 1:46 AM, Andre Araujo <ara...@pythian.com> wrote: > Vidya, > > I'm not sure I've understood your problem correctly. But if you want to > create a table in the default database you can do either: > > use default; > create table jobs_ex2 as select ... > > OR > > create table default.jobs_ex2 as select ... > > With that you don't need to specify the LOCATION clause. > However, the ROW FORMAT clause cannot be specified after the query. It > must come at the beginning of your statement: > > Try this: > > > *create table default.jobs_ex2 * > > *row format delimited fields terminated by ',' > * > > > *as select year, capitalregion, universe from jobs;* > > > > On 19 July 2014 17:23, Vidya Sujeet <sjayatheer...@gmail.com> wrote: > >> Hello, >>> >>> I am trying to create a new table using an existing table's schema >>> (existing table name in hive: jobs). However, when I do that it doesn't put >>> the new table (new table name in hive: jobs_ex2) in the same location as >>> the existing table. When I specify the location explicitly, it errors out. >>> >>> >>> Query which has the problem is pasted below: >>> >>> >>> >>> >>> >>> *create table jobs_ex2as select year, capitalregion, universe from >>> jobsrow format delimited fields terminated by >>> ',' Location >>> '/user/hive/warehouse/default.db/jobs_ex2'* >>> >>> The file that is being used to create a table is in the following >>> location: */user/hive/warehouse/default.db/jobs/universe=1/Jobs.csv* >>> where universe=1 is the partition. The new table jobs_ex_2 needs to be >>> created inside default.db folder. >>> >>> thanks, >>> Vidya >>> >> >> > > > -- > André Araújo > Big Data Consultant/Solutions Architect > The Pythian Group - Australia - www.pythian.com > > Office (calls from within Australia): 1300 366 021 x1270 > Office (international): +61 2 8016 7000 x270 *OR* +1 613 565 8696 x1270 > Mobile: +61 410 323 559 > Fax: +61 2 9805 0544 > IM: pythianaraujo @ AIM/MSN/Y! or ara...@pythian.com @ GTalk > > “Success is not about standing at the top, it's the steps you leave behind.” > — Iker Pou (rock climber) > > -- > > > >
