thanks all. I created a new database and it works fine there..
On Sat, Jul 19, 2014 at 1:37 PM, Lefty Leverenz <leftylever...@gmail.com> wrote: > And now it's documented in the DDL wiki: > > - Use Database > > <https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-UseDatabase> > - Create Table > > <https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-CreateTable> > (notes after syntax) > > > -- Lefty > > > On Sat, Jul 19, 2014 at 3:59 PM, Lefty Leverenz <leftylever...@gmail.com> > wrote: > >> 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) >>>> >>>> -- >>>> >>>> >>>> >>>> >>> >> >
