You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Vidya Sujeet <sj...@gmail.com> on 2014/07/18 08:41:41 UTC
Errors while creating a new table using existing table schema
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
Re: Errors while creating a new table using existing table schema
Posted by Vidya Sujeet <sj...@gmail.com>.
thanks all. I created a new database and it works fine there..
On Sat, Jul 19, 2014 at 1:37 PM, Lefty Leverenz <le...@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 <le...@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 <
>> tirumerla@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 <ar...@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 <sj...@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 araujo@pythian.com @ GTalk
>>>>
>>>> “Success is not about standing at the top, it's the steps you leave
>>>> behind.” — Iker Pou (rock climber)
>>>>
>>>> --
>>>>
>>>>
>>>>
>>>>
>>>
>>
>
Re: Errors while creating a new table using existing table schema
Posted by Lefty Leverenz <le...@gmail.com>.
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 <le...@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 <
> tirumerla@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 <ar...@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 <sj...@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 araujo@pythian.com @ GTalk
>>>
>>> “Success is not about standing at the top, it's the steps you leave
>>> behind.” — Iker Pou (rock climber)
>>>
>>> --
>>>
>>>
>>>
>>>
>>
>
Re: Errors while creating a new table using existing table schema
Posted by Lefty Leverenz <le...@gmail.com>.
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 <
tirumerla@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 <ar...@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 <sj...@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 araujo@pythian.com @ GTalk
>>
>> “Success is not about standing at the top, it's the steps you leave
>> behind.” — Iker Pou (rock climber)
>>
>> --
>>
>>
>>
>>
>
Re: Errors while creating a new table using existing table schema
Posted by sai chaitanya tirumerla <ti...@gmail.com>.
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 <ar...@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 <sj...@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 araujo@pythian.com @ GTalk
>
> “Success is not about standing at the top, it's the steps you leave behind.”
> — Iker Pou (rock climber)
>
> --
>
>
>
>
Re: Errors while creating a new table using existing table schema
Posted by Andre Araujo <ar...@pythian.com>.
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 <sj...@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 araujo@pythian.com @ GTalk
“Success is not about standing at the top, it's the steps you leave behind.”
— Iker Pou (rock climber)
--
--
Re: Errors while creating a new table using existing table schema
Posted by Vidya Sujeet <sj...@gmail.com>.
>
> 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
>