André, thanks for the reminder about USE <database>, which hadn't been documented in the wiki. I've added a note to the Select Syntax <https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select#LanguageManualSelect-SelectSyntax> section (for starters).
-- Lefty On Sat, Jul 19, 2014 at 4:52 AM, sai chaitanya tirumerla < tirume...@gmail.com> wrote: > 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) >> >> -- >> >> >> >> >
