You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Peter Marron <Pe...@trilliumsoftware.com> on 2013/06/10 15:31:26 UTC
Create table like with partitions
Hi,
Using hive 0.10.0 over hadoop 1.0.4
I have a (non-partitioned) table with loads of columns.
I would like to create a partitioned table with the same set of columns.
So the approach that I have been taking is to use "CREATE TABLE copy LIKE original;"
then I can use ALTER TABLE to change the location and the INPUTFORMAT
and the OUTPUTFORMAT and the SERDE and properties and pretty much
everything else. However I don't seem to be able to make it partitioned.
Sure I can add partitions if it's already partitioned but I don't seem
to be able to make it partitioned if it's not already. I get errors like this:
hive> ALTER TABLE customerShortValues ADD PARTITION (aid='1') LOCATION 'E7/phase2/values/aid=1';
FAILED: Error in metadata: table is not partitioned but partition spec exists: {aid=1}
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask
So, I guess that I could create the table I want by hand copying over all the
column definitions. But is there an easier way?
Z
Re: Create table like with partitions
Posted by Richa Sharma <ma...@gmail.com>.
Yes ... wise advice ... thanks :-)
On Wed, Jun 12, 2013 at 7:41 AM, Sanjay Subramanian <
Sanjay.Subramanian@wizecommerce.com> wrote:
> +1 Its one of the very very usable hands on book published in this
> technical domain. Same here I bought both versions :-)
>
> From: Lefty Leverenz <le...@hortonworks.com>
> Reply-To: "user@hive.apache.org" <us...@hive.apache.org>
> Date: Tuesday, June 11, 2013 5:39 PM
> To: "user@hive.apache.org" <us...@hive.apache.org>
>
> Subject: Re: Create table like with partitions
>
> It's an excellent book, and not too expensive so I recommend buying your
> own copy from O'Reilly or Amazon. (Not only will you have a legal copy,
> but you'll encourage authors to write more books for the benefit of all.)
> I've got it in hardcover and digital format.
>
> – Lefty
>
>
>
> On Tue, Jun 11, 2013 at 6:46 PM, Sanjay Subramanian <
> Sanjay.Subramanian@wizecommerce.com> wrote:
>
>> From Russia with Love...
>>
>> Domain ID:D39022749-LRMS
>> Domain Name:IT-EBOOKS.INFO
>> Created On:27-Jul-2011 12:24:45 UTC
>> Last Updated On:29-Apr-2013 11:00:03 UTC
>> Expiration Date:27-Jul-2015 12:24:45 UTC
>> Sponsoring Registrar:GoDaddy.com LLC (R171-LRMS)
>> Status:CLIENT DELETE PROHIBITED
>> Status:CLIENT RENEW PROHIBITED
>> Status:CLIENT TRANSFER PROHIBITED
>> Status:CLIENT UPDATE PROHIBITED
>> Registrant ID:CR89106899
>> Registrant Name:Artur Denisov
>> Registrant Organization:
>> Registrant Street1:Akademika Koroleva 21/5
>> Registrant Street2:
>> Registrant Street3:
>> Registrant City:Moscow
>> Registrant State/Province:Moscow
>> Registrant Postal Code:127427
>> Registrant Country:RU
>> Registrant Phone:+7.4957930823
>> Registrant Phone Ext.:
>> Registrant FAX:
>> Registrant FAX Ext.:
>> Registrant Email:support@it-ebooks.info
>> Admin ID:CR89106901
>> Admin Name:Artur Denisov
>> Admin Organization:
>> Admin Street1:Akademika Koroleva 21/5
>> Admin Street2:
>> Admin Street3:
>> Admin City:Moscow
>> Admin State/Province:Moscow
>> Admin Postal Code:127427
>> Admin Country:RU
>> Admin Phone:+7.4957930823
>> Admin Phone Ext.:
>> Admin FAX:
>> Admin FAX Ext.:
>> Admin Email:support@it-ebooks.info
>> Billing ID:CR89106902
>> Billing Name:Artur Denisov
>> Billing Organization:
>> Billing Street1:Akademika Koroleva 21/5
>> Billing Street2:
>> Billing Street3:
>> Billing City:Moscow
>> Billing State/Province:Moscow
>> Billing Postal Code:127427
>> Billing Country:RU
>> Billing Phone:+7.4957930823
>> Billing Phone Ext.:
>> Billing FAX:
>> Billing FAX Ext.:
>> Billing Email:support@it-ebooks.info
>> Tech ID:CR89106900
>> Tech Name:Artur Denisov
>> Tech Organization:
>> Tech Street1:Akademika Koroleva 21/5
>> Tech Street2:
>> Tech Street3:
>> Tech City:Moscow
>> Tech State/Province:Moscow
>> Tech Postal Code:127427
>> Tech Country:RU
>> Tech Phone:+7.4957930823
>> Tech Phone Ext.:
>> Tech FAX:
>> Tech FAX Ext.:
>> Tech Email:support@it-ebooks.info
>> Name Server:NS1.FASTVPS.RU
>> Name Server:NS2.FASTVPS.RU
>> Name Server:NS4.FASTVPS.RU
>> Name Server:NS3.FASTVPS.RU
>> Name Server:
>> Name Server:
>> Name Server:
>> Name Server:
>> Name Server:
>> Name Server:
>> Name Server:
>> Name Server:
>> Name Server:
>>
>>
>> From: Dean Wampler <de...@gmail.com>
>> Reply-To: "user@hive.apache.org" <us...@hive.apache.org>
>> Date: Tuesday, June 11, 2013 11:04 AM
>> To: "user@hive.apache.org" <us...@hive.apache.org>
>> Cc: "user@hive.apache.org" <us...@hive.apache.org>
>> Subject: Re: Create table like with partitions
>>
>> I confirmed it is a pirate site.
>>
>> Sent from my rotary phone.
>>
>> On Jun 11, 2013, at 10:33 AM, Edward Capriolo <ed...@gmail.com>
>> wrote:
>>
>> For reference, any that puts the entire book online like this is
>> likely pirated.
>>
>>
>>
>>
>> On Tue, Jun 11, 2013 at 8:34 AM, Richa Sharma <
>> mailtorichasharma@gmail.com> wrote:
>>
>>> Hi all,
>>>
>>> Found a very useful guide online. Link ->
>>> http://it-ebooks.info/book/941/
>>>
>>> Richa
>>>
>>>
>>> On Tue, Jun 11, 2013 at 1:53 PM, Richa Sharma <
>>> mailtorichasharma@gmail.com> wrote:
>>>
>>>> Thanks for sharing!
>>>>
>>>> I looked at these links .. Is there any documentation with more
>>>> examples with both static and dynamic partitions covered together.
>>>>
>>>> Richa
>>>>
>>>>
>>>> On Tue, Jun 11, 2013 at 12:33 PM, Lefty Leverenz <lefty@hortonworks.com
>>>> > wrote:
>>>>
>>>>> Dynamic partitions are described in the Hive design docs here:
>>>>> https://cwiki.apache.org/confluence/display/Hive/DynamicPartitions.
>>>>>
>>>>> For the configuration parameters, though, you need to look in the
>>>>> language manual here:
>>>>> https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties (search
>>>>> for "dynamic" to find various parameters related to dynamic partitions).
>>>>>
>>>>> – Lefty
>>>>>
>>>>>
>>>>>
>>>>> On Mon, Jun 10, 2013 at 7:06 AM, Owen O'Malley <om...@apache.org>wrote:
>>>>>
>>>>>> You need to create the partitioned table and then copy the rows into
>>>>>> it.
>>>>>>
>>>>>> create table foo_staging (int x, int y);
>>>>>>
>>>>>> create table foo(int x) partitioned by (int y) clustered by (x) into
>>>>>> 16 buckets;
>>>>>>
>>>>>> set hive.exec.dynamic.partition=true;
>>>>>> set hive.exec.dynamic.partition.mode=nonstrict;
>>>>>> set hive.enforce.bucketing = true;
>>>>>>
>>>>>> insert overwrite table partition (y) select * from foo_staging;
>>>>>>
>>>>>>
>>>>>> On Mon, Jun 10, 2013 at 6:38 AM, Nitin Pawar <nitinpawar432@gmail.com
>>>>>> > wrote:
>>>>>>
>>>>>>> If a table is not partitioned and then you want to partition the
>>>>>>> table on the data already written but data is not in partition format, that
>>>>>>> is not doable.
>>>>>>>
>>>>>>> Best approach would be, create a new table definition with the
>>>>>>> partition columns you want.
>>>>>>> turn on the dynamic partitioning system before you load data into
>>>>>>> new table
>>>>>>>
>>>>>>> set hive.exec.dynamic.partition=true;
>>>>>>> set hive.exec.dynamic.partition.mode=nonstrict;
>>>>>>>
>>>>>>> insert overwrite table partitioned(columns) select * from oldtable
>>>>>>>
>>>>>>>
>>>>>>> remove old table
>>>>>>>
>>>>>>> PS: wait for others to add more suggestions. I may be very well
>>>>>>> wrong in suggesting this
>>>>>>>
>>>>>>>
>>>>>>> On Mon, Jun 10, 2013 at 7:01 PM, Peter Marron <
>>>>>>> Peter.Marron@trilliumsoftware.com> wrote:
>>>>>>>
>>>>>>>> Hi,****
>>>>>>>>
>>>>>>>> ** **
>>>>>>>>
>>>>>>>> Using hive 0.10.0 over hadoop 1.0.4****
>>>>>>>>
>>>>>>>> ** **
>>>>>>>>
>>>>>>>> I have a (non-partitioned) table with loads of columns.****
>>>>>>>>
>>>>>>>> I would like to create a partitioned table with the same set of
>>>>>>>> columns.****
>>>>>>>>
>>>>>>>> So the approach that I have been taking is to use “CREATE TABLE
>>>>>>>> copy LIKE original;”****
>>>>>>>>
>>>>>>>> then I can use ALTER TABLE to change the location and the
>>>>>>>> INPUTFORMAT****
>>>>>>>>
>>>>>>>> and the OUTPUTFORMAT and the SERDE and properties and pretty much**
>>>>>>>> **
>>>>>>>>
>>>>>>>> everything else. However I don’t seem to be able to make it
>>>>>>>> partitioned.****
>>>>>>>>
>>>>>>>> Sure I can add partitions if it’s already partitioned but I don’t
>>>>>>>> seem****
>>>>>>>>
>>>>>>>> to be able to make it partitioned if it’s not already. I get errors
>>>>>>>> like this:****
>>>>>>>>
>>>>>>>> ** **
>>>>>>>>
>>>>>>>> hive> ALTER TABLE customerShortValues ADD PARTITION (aid='1')
>>>>>>>> LOCATION 'E7/phase2/values/aid=1';****
>>>>>>>>
>>>>>>>> FAILED: Error in metadata: table is not partitioned but partition
>>>>>>>> spec exists: {aid=1}****
>>>>>>>>
>>>>>>>> FAILED: Execution Error, return code 1 from
>>>>>>>> org.apache.hadoop.hive.ql.exec.DDLTask****
>>>>>>>>
>>>>>>>> ** **
>>>>>>>>
>>>>>>>> So, I guess that I could create the table I want by hand copying
>>>>>>>> over all the****
>>>>>>>>
>>>>>>>> column definitions. But is there an easier way?****
>>>>>>>>
>>>>>>>> ** **
>>>>>>>>
>>>>>>>> Z****
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> Nitin Pawar
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>> CONFIDENTIALITY NOTICE
>> ======================
>> This email message and any attachments are for the exclusive use of the
>> intended recipient(s) and may contain confidential and privileged
>> information. Any unauthorized review, use, disclosure or distribution is
>> prohibited. If you are not the intended recipient, please contact the
>> sender by reply email and destroy all copies of the original message along
>> with any attachments, from your computer system. If you are the intended
>> recipient, please be advised that the content of this message is subject to
>> access, review and disclosure by the sender's Email System Administrator.
>>
>
>
> CONFIDENTIALITY NOTICE
> ======================
> This email message and any attachments are for the exclusive use of the
> intended recipient(s) and may contain confidential and privileged
> information. Any unauthorized review, use, disclosure or distribution is
> prohibited. If you are not the intended recipient, please contact the
> sender by reply email and destroy all copies of the original message along
> with any attachments, from your computer system. If you are the intended
> recipient, please be advised that the content of this message is subject to
> access, review and disclosure by the sender's Email System Administrator.
>
RE: Create table like with partitions
Posted by Peter Marron <Pe...@trilliumsoftware.com>.
Given that I started the original thread it seems appropriate that I should point out that I also have a bought and paid for (personal) digital copy.
It's a good book.
Peter Marron
Trillium Software UK Limited
Tel : +44 (0) 118 940 7609
Fax : +44 (0) 118 940 7699
E: Peter.Marron@TrilliumSoftware.com<ma...@TrilliumSoftware.com>
From: Lefty Leverenz [mailto:lefty@hortonworks.com]
Sent: 12 June 2013 01:39
To: user@hive.apache.org
Subject: Re: Create table like with partitions
It's an excellent book, and not too expensive so I recommend buying your own copy from O'Reilly or Amazon. (Not only will you have a legal copy, but you'll encourage authors to write more books for the benefit of all.) I've got it in hardcover and digital format.
- Lefty
On Tue, Jun 11, 2013 at 6:46 PM, Sanjay Subramanian <Sa...@wizecommerce.com>> wrote:
>From Russia with Love...
Domain ID:D39022749-LRMS
Domain Name:IT-EBOOKS.INFO<http://IT-EBOOKS.INFO>
Created On:27-Jul-2011 12:24:45 UTC
Last Updated On:29-Apr-2013 11:00:03 UTC
Expiration Date:27-Jul-2015 12:24:45 UTC
Sponsoring Registrar:GoDaddy.com LLC (R171-LRMS)
Status:CLIENT DELETE PROHIBITED
Status:CLIENT RENEW PROHIBITED
Status:CLIENT TRANSFER PROHIBITED
Status:CLIENT UPDATE PROHIBITED
Registrant ID:CR89106899
Registrant Name:Artur Denisov
Registrant Organization:
Registrant Street1:Akademika Koroleva 21/5
Registrant Street2:
Registrant Street3:
Registrant City:Moscow
Registrant State/Province:Moscow
Registrant Postal Code:127427
Registrant Country:RU
Registrant Phone:+7.4957930823
Registrant Phone Ext.:
Registrant FAX:
Registrant FAX Ext.:
Registrant Email:support@it-ebooks.info<ma...@it-ebooks.info>
Admin ID:CR89106901
Admin Name:Artur Denisov
Admin Organization:
Admin Street1:Akademika Koroleva 21/5
Admin Street2:
Admin Street3:
Admin City:Moscow
Admin State/Province:Moscow
Admin Postal Code:127427
Admin Country:RU
Admin Phone:+7.4957930823
Admin Phone Ext.:
Admin FAX:
Admin FAX Ext.:
Admin Email:support@it-ebooks.info<ma...@it-ebooks.info>
Billing ID:CR89106902
Billing Name:Artur Denisov
Billing Organization:
Billing Street1:Akademika Koroleva 21/5
Billing Street2:
Billing Street3:
Billing City:Moscow
Billing State/Province:Moscow
Billing Postal Code:127427
Billing Country:RU
Billing Phone:+7.4957930823
Billing Phone Ext.:
Billing FAX:
Billing FAX Ext.:
Billing Email:support@it-ebooks.info<ma...@it-ebooks.info>
Tech ID:CR89106900
Tech Name:Artur Denisov
Tech Organization:
Tech Street1:Akademika Koroleva 21/5
Tech Street2:
Tech Street3:
Tech City:Moscow
Tech State/Province:Moscow
Tech Postal Code:127427
Tech Country:RU
Tech Phone:+7.4957930823
Tech Phone Ext.:
Tech FAX:
Tech FAX Ext.:
Tech Email:support@it-ebooks.info<ma...@it-ebooks.info>
Name Server:NS1.FASTVPS.RU<http://NS1.FASTVPS.RU>
Name Server:NS2.FASTVPS.RU<http://NS2.FASTVPS.RU>
Name Server:NS4.FASTVPS.RU<http://NS4.FASTVPS.RU>
Name Server:NS3.FASTVPS.RU<http://NS3.FASTVPS.RU>
Name Server:
Name Server:
Name Server:
Name Server:
Name Server:
Name Server:
Name Server:
Name Server:
Name Server:
From: Dean Wampler <de...@gmail.com>>
Reply-To: "user@hive.apache.org<ma...@hive.apache.org>" <us...@hive.apache.org>>
Date: Tuesday, June 11, 2013 11:04 AM
To: "user@hive.apache.org<ma...@hive.apache.org>" <us...@hive.apache.org>>
Cc: "user@hive.apache.org<ma...@hive.apache.org>" <us...@hive.apache.org>>
Subject: Re: Create table like with partitions
I confirmed it is a pirate site.
Sent from my rotary phone.
On Jun 11, 2013, at 10:33 AM, Edward Capriolo <ed...@gmail.com>> wrote:
For reference, any that puts the entire book online like this is likely pirated.
On Tue, Jun 11, 2013 at 8:34 AM, Richa Sharma <ma...@gmail.com>> wrote:
Hi all,
Found a very useful guide online. Link -> http://it-ebooks.info/book/941/
Richa
On Tue, Jun 11, 2013 at 1:53 PM, Richa Sharma <ma...@gmail.com>> wrote:
Thanks for sharing!
I looked at these links .. Is there any documentation with more examples with both static and dynamic partitions covered together.
Richa
On Tue, Jun 11, 2013 at 12:33 PM, Lefty Leverenz <le...@hortonworks.com>> wrote:
Dynamic partitions are described in the Hive design docs here: https://cwiki.apache.org/confluence/display/Hive/DynamicPartitions.
For the configuration parameters, though, you need to look in the language manual here: https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties (search for "dynamic" to find various parameters related to dynamic partitions).
- Lefty
On Mon, Jun 10, 2013 at 7:06 AM, Owen O'Malley <om...@apache.org>> wrote:
You need to create the partitioned table and then copy the rows into it.
create table foo_staging (int x, int y);
create table foo(int x) partitioned by (int y) clustered by (x) into 16 buckets;
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.enforce.bucketing = true;
insert overwrite table partition (y) select * from foo_staging;
On Mon, Jun 10, 2013 at 6:38 AM, Nitin Pawar <ni...@gmail.com>> wrote:
If a table is not partitioned and then you want to partition the table on the data already written but data is not in partition format, that is not doable.
Best approach would be, create a new table definition with the partition columns you want.
turn on the dynamic partitioning system before you load data into new table
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table partitioned(columns) select * from oldtable
remove old table
PS: wait for others to add more suggestions. I may be very well wrong in suggesting this
On Mon, Jun 10, 2013 at 7:01 PM, Peter Marron <Pe...@trilliumsoftware.com>> wrote:
Hi,
Using hive 0.10.0 over hadoop 1.0.4
I have a (non-partitioned) table with loads of columns.
I would like to create a partitioned table with the same set of columns.
So the approach that I have been taking is to use "CREATE TABLE copy LIKE original;"
then I can use ALTER TABLE to change the location and the INPUTFORMAT
and the OUTPUTFORMAT and the SERDE and properties and pretty much
everything else. However I don't seem to be able to make it partitioned.
Sure I can add partitions if it's already partitioned but I don't seem
to be able to make it partitioned if it's not already. I get errors like this:
hive> ALTER TABLE customerShortValues ADD PARTITION (aid='1') LOCATION 'E7/phase2/values/aid=1';
FAILED: Error in metadata: table is not partitioned but partition spec exists: {aid=1}
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask
So, I guess that I could create the table I want by hand copying over all the
column definitions. But is there an easier way?
Z
--
Nitin Pawar
CONFIDENTIALITY NOTICE
======================
This email message and any attachments are for the exclusive use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message along with any attachments, from your computer system. If you are the intended recipient, please be advised that the content of this message is subject to access, review and disclosure by the sender's Email System Administrator.
Re: Create table like with partitions
Posted by Sanjay Subramanian <Sa...@wizecommerce.com>.
+1 Its one of the very very usable hands on book published in this technical domain. Same here I bought both versions :-)
From: Lefty Leverenz <le...@hortonworks.com>>
Reply-To: "user@hive.apache.org<ma...@hive.apache.org>" <us...@hive.apache.org>>
Date: Tuesday, June 11, 2013 5:39 PM
To: "user@hive.apache.org<ma...@hive.apache.org>" <us...@hive.apache.org>>
Subject: Re: Create table like with partitions
It's an excellent book, and not too expensive so I recommend buying your own copy from O'Reilly or Amazon. (Not only will you have a legal copy, but you'll encourage authors to write more books for the benefit of all.) I've got it in hardcover and digital format.
– Lefty
On Tue, Jun 11, 2013 at 6:46 PM, Sanjay Subramanian <Sa...@wizecommerce.com>> wrote:
>From Russia with Love...
Domain ID:D39022749-LRMS
Domain Name:IT-EBOOKS.INFO<http://IT-EBOOKS.INFO>
Created On:27-Jul-2011 12:24:45 UTC
Last Updated On:29-Apr-2013 11:00:03 UTC
Expiration Date:27-Jul-2015 12:24:45 UTC
Sponsoring Registrar:GoDaddy.com LLC (R171-LRMS)
Status:CLIENT DELETE PROHIBITED
Status:CLIENT RENEW PROHIBITED
Status:CLIENT TRANSFER PROHIBITED
Status:CLIENT UPDATE PROHIBITED
Registrant ID:CR89106899
Registrant Name:Artur Denisov
Registrant Organization:
Registrant Street1:Akademika Koroleva 21/5
Registrant Street2:
Registrant Street3:
Registrant City:Moscow
Registrant State/Province:Moscow
Registrant Postal Code:127427
Registrant Country:RU
Registrant Phone:+7.4957930823
Registrant Phone Ext.:
Registrant FAX:
Registrant FAX Ext.:
Registrant Email:support@it-ebooks.info<ma...@it-ebooks.info>
Admin ID:CR89106901
Admin Name:Artur Denisov
Admin Organization:
Admin Street1:Akademika Koroleva 21/5
Admin Street2:
Admin Street3:
Admin City:Moscow
Admin State/Province:Moscow
Admin Postal Code:127427
Admin Country:RU
Admin Phone:+7.4957930823
Admin Phone Ext.:
Admin FAX:
Admin FAX Ext.:
Admin Email:support@it-ebooks.info<ma...@it-ebooks.info>
Billing ID:CR89106902
Billing Name:Artur Denisov
Billing Organization:
Billing Street1:Akademika Koroleva 21/5
Billing Street2:
Billing Street3:
Billing City:Moscow
Billing State/Province:Moscow
Billing Postal Code:127427
Billing Country:RU
Billing Phone:+7.4957930823
Billing Phone Ext.:
Billing FAX:
Billing FAX Ext.:
Billing Email:support@it-ebooks.info<ma...@it-ebooks.info>
Tech ID:CR89106900
Tech Name:Artur Denisov
Tech Organization:
Tech Street1:Akademika Koroleva 21/5
Tech Street2:
Tech Street3:
Tech City:Moscow
Tech State/Province:Moscow
Tech Postal Code:127427
Tech Country:RU
Tech Phone:+7.4957930823
Tech Phone Ext.:
Tech FAX:
Tech FAX Ext.:
Tech Email:support@it-ebooks.info<ma...@it-ebooks.info>
Name Server:NS1.FASTVPS.RU<http://NS1.FASTVPS.RU>
Name Server:NS2.FASTVPS.RU<http://NS2.FASTVPS.RU>
Name Server:NS4.FASTVPS.RU<http://NS4.FASTVPS.RU>
Name Server:NS3.FASTVPS.RU<http://NS3.FASTVPS.RU>
Name Server:
Name Server:
Name Server:
Name Server:
Name Server:
Name Server:
Name Server:
Name Server:
Name Server:
From: Dean Wampler <de...@gmail.com>>
Reply-To: "user@hive.apache.org<ma...@hive.apache.org>" <us...@hive.apache.org>>
Date: Tuesday, June 11, 2013 11:04 AM
To: "user@hive.apache.org<ma...@hive.apache.org>" <us...@hive.apache.org>>
Cc: "user@hive.apache.org<ma...@hive.apache.org>" <us...@hive.apache.org>>
Subject: Re: Create table like with partitions
I confirmed it is a pirate site.
Sent from my rotary phone.
On Jun 11, 2013, at 10:33 AM, Edward Capriolo <ed...@gmail.com>> wrote:
For reference, any that puts the entire book online like this is likely pirated.
On Tue, Jun 11, 2013 at 8:34 AM, Richa Sharma <ma...@gmail.com>> wrote:
Hi all,
Found a very useful guide online. Link -> http://it-ebooks.info/book/941/
Richa
On Tue, Jun 11, 2013 at 1:53 PM, Richa Sharma <ma...@gmail.com>> wrote:
Thanks for sharing!
I looked at these links .. Is there any documentation with more examples with both static and dynamic partitions covered together.
Richa
On Tue, Jun 11, 2013 at 12:33 PM, Lefty Leverenz <le...@hortonworks.com>> wrote:
Dynamic partitions are described in the Hive design docs here: https://cwiki.apache.org/confluence/display/Hive/DynamicPartitions.
For the configuration parameters, though, you need to look in the language manual here: https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties (search for "dynamic" to find various parameters related to dynamic partitions).
– Lefty
On Mon, Jun 10, 2013 at 7:06 AM, Owen O'Malley <om...@apache.org>> wrote:
You need to create the partitioned table and then copy the rows into it.
create table foo_staging (int x, int y);
create table foo(int x) partitioned by (int y) clustered by (x) into 16 buckets;
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.enforce.bucketing = true;
insert overwrite table partition (y) select * from foo_staging;
On Mon, Jun 10, 2013 at 6:38 AM, Nitin Pawar <ni...@gmail.com>> wrote:
If a table is not partitioned and then you want to partition the table on the data already written but data is not in partition format, that is not doable.
Best approach would be, create a new table definition with the partition columns you want.
turn on the dynamic partitioning system before you load data into new table
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table partitioned(columns) select * from oldtable
remove old table
PS: wait for others to add more suggestions. I may be very well wrong in suggesting this
On Mon, Jun 10, 2013 at 7:01 PM, Peter Marron <Pe...@trilliumsoftware.com>> wrote:
Hi,
Using hive 0.10.0 over hadoop 1.0.4
I have a (non-partitioned) table with loads of columns.
I would like to create a partitioned table with the same set of columns.
So the approach that I have been taking is to use “CREATE TABLE copy LIKE original;”
then I can use ALTER TABLE to change the location and the INPUTFORMAT
and the OUTPUTFORMAT and the SERDE and properties and pretty much
everything else. However I don’t seem to be able to make it partitioned.
Sure I can add partitions if it’s already partitioned but I don’t seem
to be able to make it partitioned if it’s not already. I get errors like this:
hive> ALTER TABLE customerShortValues ADD PARTITION (aid='1') LOCATION 'E7/phase2/values/aid=1';
FAILED: Error in metadata: table is not partitioned but partition spec exists: {aid=1}
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask
So, I guess that I could create the table I want by hand copying over all the
column definitions. But is there an easier way?
Z
--
Nitin Pawar
CONFIDENTIALITY NOTICE
======================
This email message and any attachments are for the exclusive use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message along with any attachments, from your computer system. If you are the intended recipient, please be advised that the content of this message is subject to access, review and disclosure by the sender's Email System Administrator.
CONFIDENTIALITY NOTICE
======================
This email message and any attachments are for the exclusive use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message along with any attachments, from your computer system. If you are the intended recipient, please be advised that the content of this message is subject to access, review and disclosure by the sender's Email System Administrator.
Re: Create table like with partitions
Posted by Lefty Leverenz <le...@hortonworks.com>.
It's an excellent book, and not too expensive so I recommend buying your
own copy from O'Reilly or Amazon. (Not only will you have a legal copy,
but you'll encourage authors to write more books for the benefit of all.)
I've got it in hardcover and digital format.
– Lefty
On Tue, Jun 11, 2013 at 6:46 PM, Sanjay Subramanian <
Sanjay.Subramanian@wizecommerce.com> wrote:
> From Russia with Love...
>
> Domain ID:D39022749-LRMS
> Domain Name:IT-EBOOKS.INFO
> Created On:27-Jul-2011 12:24:45 UTC
> Last Updated On:29-Apr-2013 11:00:03 UTC
> Expiration Date:27-Jul-2015 12:24:45 UTC
> Sponsoring Registrar:GoDaddy.com LLC (R171-LRMS)
> Status:CLIENT DELETE PROHIBITED
> Status:CLIENT RENEW PROHIBITED
> Status:CLIENT TRANSFER PROHIBITED
> Status:CLIENT UPDATE PROHIBITED
> Registrant ID:CR89106899
> Registrant Name:Artur Denisov
> Registrant Organization:
> Registrant Street1:Akademika Koroleva 21/5
> Registrant Street2:
> Registrant Street3:
> Registrant City:Moscow
> Registrant State/Province:Moscow
> Registrant Postal Code:127427
> Registrant Country:RU
> Registrant Phone:+7.4957930823
> Registrant Phone Ext.:
> Registrant FAX:
> Registrant FAX Ext.:
> Registrant Email:support@it-ebooks.info
> Admin ID:CR89106901
> Admin Name:Artur Denisov
> Admin Organization:
> Admin Street1:Akademika Koroleva 21/5
> Admin Street2:
> Admin Street3:
> Admin City:Moscow
> Admin State/Province:Moscow
> Admin Postal Code:127427
> Admin Country:RU
> Admin Phone:+7.4957930823
> Admin Phone Ext.:
> Admin FAX:
> Admin FAX Ext.:
> Admin Email:support@it-ebooks.info
> Billing ID:CR89106902
> Billing Name:Artur Denisov
> Billing Organization:
> Billing Street1:Akademika Koroleva 21/5
> Billing Street2:
> Billing Street3:
> Billing City:Moscow
> Billing State/Province:Moscow
> Billing Postal Code:127427
> Billing Country:RU
> Billing Phone:+7.4957930823
> Billing Phone Ext.:
> Billing FAX:
> Billing FAX Ext.:
> Billing Email:support@it-ebooks.info
> Tech ID:CR89106900
> Tech Name:Artur Denisov
> Tech Organization:
> Tech Street1:Akademika Koroleva 21/5
> Tech Street2:
> Tech Street3:
> Tech City:Moscow
> Tech State/Province:Moscow
> Tech Postal Code:127427
> Tech Country:RU
> Tech Phone:+7.4957930823
> Tech Phone Ext.:
> Tech FAX:
> Tech FAX Ext.:
> Tech Email:support@it-ebooks.info
> Name Server:NS1.FASTVPS.RU
> Name Server:NS2.FASTVPS.RU
> Name Server:NS4.FASTVPS.RU
> Name Server:NS3.FASTVPS.RU
> Name Server:
> Name Server:
> Name Server:
> Name Server:
> Name Server:
> Name Server:
> Name Server:
> Name Server:
> Name Server:
>
>
> From: Dean Wampler <de...@gmail.com>
> Reply-To: "user@hive.apache.org" <us...@hive.apache.org>
> Date: Tuesday, June 11, 2013 11:04 AM
> To: "user@hive.apache.org" <us...@hive.apache.org>
> Cc: "user@hive.apache.org" <us...@hive.apache.org>
> Subject: Re: Create table like with partitions
>
> I confirmed it is a pirate site.
>
> Sent from my rotary phone.
>
> On Jun 11, 2013, at 10:33 AM, Edward Capriolo <ed...@gmail.com>
> wrote:
>
> For reference, any that puts the entire book online like this is likely
> pirated.
>
>
>
>
> On Tue, Jun 11, 2013 at 8:34 AM, Richa Sharma <mailtorichasharma@gmail.com
> > wrote:
>
>> Hi all,
>>
>> Found a very useful guide online. Link ->
>> http://it-ebooks.info/book/941/
>>
>> Richa
>>
>>
>> On Tue, Jun 11, 2013 at 1:53 PM, Richa Sharma <
>> mailtorichasharma@gmail.com> wrote:
>>
>>> Thanks for sharing!
>>>
>>> I looked at these links .. Is there any documentation with more
>>> examples with both static and dynamic partitions covered together.
>>>
>>> Richa
>>>
>>>
>>> On Tue, Jun 11, 2013 at 12:33 PM, Lefty Leverenz <le...@hortonworks.com>wrote:
>>>
>>>> Dynamic partitions are described in the Hive design docs here:
>>>> https://cwiki.apache.org/confluence/display/Hive/DynamicPartitions.
>>>>
>>>> For the configuration parameters, though, you need to look in the
>>>> language manual here:
>>>> https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties (search
>>>> for "dynamic" to find various parameters related to dynamic partitions).
>>>>
>>>> – Lefty
>>>>
>>>>
>>>>
>>>> On Mon, Jun 10, 2013 at 7:06 AM, Owen O'Malley <om...@apache.org>wrote:
>>>>
>>>>> You need to create the partitioned table and then copy the rows into
>>>>> it.
>>>>>
>>>>> create table foo_staging (int x, int y);
>>>>>
>>>>> create table foo(int x) partitioned by (int y) clustered by (x) into
>>>>> 16 buckets;
>>>>>
>>>>> set hive.exec.dynamic.partition=true;
>>>>> set hive.exec.dynamic.partition.mode=nonstrict;
>>>>> set hive.enforce.bucketing = true;
>>>>>
>>>>> insert overwrite table partition (y) select * from foo_staging;
>>>>>
>>>>>
>>>>> On Mon, Jun 10, 2013 at 6:38 AM, Nitin Pawar <ni...@gmail.com>wrote:
>>>>>
>>>>>> If a table is not partitioned and then you want to partition the
>>>>>> table on the data already written but data is not in partition format, that
>>>>>> is not doable.
>>>>>>
>>>>>> Best approach would be, create a new table definition with the
>>>>>> partition columns you want.
>>>>>> turn on the dynamic partitioning system before you load data into new
>>>>>> table
>>>>>>
>>>>>> set hive.exec.dynamic.partition=true;
>>>>>> set hive.exec.dynamic.partition.mode=nonstrict;
>>>>>>
>>>>>> insert overwrite table partitioned(columns) select * from oldtable
>>>>>>
>>>>>>
>>>>>> remove old table
>>>>>>
>>>>>> PS: wait for others to add more suggestions. I may be very well
>>>>>> wrong in suggesting this
>>>>>>
>>>>>>
>>>>>> On Mon, Jun 10, 2013 at 7:01 PM, Peter Marron <
>>>>>> Peter.Marron@trilliumsoftware.com> wrote:
>>>>>>
>>>>>>> Hi,****
>>>>>>>
>>>>>>> ** **
>>>>>>>
>>>>>>> Using hive 0.10.0 over hadoop 1.0.4****
>>>>>>>
>>>>>>> ** **
>>>>>>>
>>>>>>> I have a (non-partitioned) table with loads of columns.****
>>>>>>>
>>>>>>> I would like to create a partitioned table with the same set of
>>>>>>> columns.****
>>>>>>>
>>>>>>> So the approach that I have been taking is to use “CREATE TABLE copy
>>>>>>> LIKE original;”****
>>>>>>>
>>>>>>> then I can use ALTER TABLE to change the location and the INPUTFORMAT
>>>>>>> ****
>>>>>>>
>>>>>>> and the OUTPUTFORMAT and the SERDE and properties and pretty much***
>>>>>>> *
>>>>>>>
>>>>>>> everything else. However I don’t seem to be able to make it
>>>>>>> partitioned.****
>>>>>>>
>>>>>>> Sure I can add partitions if it’s already partitioned but I don’t
>>>>>>> seem****
>>>>>>>
>>>>>>> to be able to make it partitioned if it’s not already. I get errors
>>>>>>> like this:****
>>>>>>>
>>>>>>> ** **
>>>>>>>
>>>>>>> hive> ALTER TABLE customerShortValues ADD PARTITION (aid='1')
>>>>>>> LOCATION 'E7/phase2/values/aid=1';****
>>>>>>>
>>>>>>> FAILED: Error in metadata: table is not partitioned but partition
>>>>>>> spec exists: {aid=1}****
>>>>>>>
>>>>>>> FAILED: Execution Error, return code 1 from
>>>>>>> org.apache.hadoop.hive.ql.exec.DDLTask****
>>>>>>>
>>>>>>> ** **
>>>>>>>
>>>>>>> So, I guess that I could create the table I want by hand copying
>>>>>>> over all the****
>>>>>>>
>>>>>>> column definitions. But is there an easier way?****
>>>>>>>
>>>>>>> ** **
>>>>>>>
>>>>>>> Z****
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Nitin Pawar
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>
>
> CONFIDENTIALITY NOTICE
> ======================
> This email message and any attachments are for the exclusive use of the
> intended recipient(s) and may contain confidential and privileged
> information. Any unauthorized review, use, disclosure or distribution is
> prohibited. If you are not the intended recipient, please contact the
> sender by reply email and destroy all copies of the original message along
> with any attachments, from your computer system. If you are the intended
> recipient, please be advised that the content of this message is subject to
> access, review and disclosure by the sender's Email System Administrator.
>
Re: Renaming partition columnname only (locations remain unchanged)
Posted by Sanjay Subramanian <Sa...@wizecommerce.com>.
Alter table rename partition column does not recognize the column name. It's ok I dropped the table and created a new one and executed ADD PARTITION. and provided LOCATION
Sanjay
Sent from my iPhone
On Jun 12, 2013, at 9:53 AM, "Stephen Sprague" <sp...@gmail.com>> wrote:
all you have to do is create a partitioned test table and run an alter table command to rename the partition column(s) - and see what happens.
That's about as simple as it gets. It either works or it doesn't. :)
On Tue, Jun 11, 2013 at 9:50 PM, Nitin Pawar <ni...@gmail.com>> wrote:
currently hive partitions on hdfs stored as col1=val1/col2=val2
looking at this if you change the column name in the metadata, I am not sure you will be able to locate the data with old column name. other way would be alter table and and run hdfs move to reflect your new column name in the partition directories on hdfs.
It looks much simpler to just create new table and dump the old one via hive though
On Wed, Jun 12, 2013 at 3:46 AM, Sanjay Subramanian <Sa...@wizecommerce.com>> wrote:
Hi
I have external tables where I want to change the name of the partition column, locations remaining constant
Is there a way to do this…Else I will drop and create the table with new partition column names and run my scripts to ADD PARTITION, with LOCATION specified
Thanks
sanjay
CONFIDENTIALITY NOTICE
======================
This email message and any attachments are for the exclusive use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message along with any attachments, from your computer system. If you are the intended recipient, please be advised that the content of this message is subject to access, review and disclosure by the sender's Email System Administrator.
--
Nitin Pawar
CONFIDENTIALITY NOTICE
======================
This email message and any attachments are for the exclusive use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message along with any attachments, from your computer system. If you are the intended recipient, please be advised that the content of this message is subject to access, review and disclosure by the sender's Email System Administrator.
Re: Renaming partition columnname only (locations remain unchanged)
Posted by Stephen Sprague <sp...@gmail.com>.
all you have to do is create a partitioned test table and run an alter
table command to rename the partition column(s) - and see what happens.
That's about as simple as it gets. It either works or it doesn't. :)
On Tue, Jun 11, 2013 at 9:50 PM, Nitin Pawar <ni...@gmail.com>wrote:
> currently hive partitions on hdfs stored as col1=val1/col2=val2
>
> looking at this if you change the column name in the metadata, I am not
> sure you will be able to locate the data with old column name. other way
> would be alter table and and run hdfs move to reflect your new column name
> in the partition directories on hdfs.
>
> It looks much simpler to just create new table and dump the old one via
> hive though
>
>
> On Wed, Jun 12, 2013 at 3:46 AM, Sanjay Subramanian <
> Sanjay.Subramanian@wizecommerce.com> wrote:
>
>> Hi
>> I have external tables where I want to change the name of the
>> partition column, locations remaining constant
>> Is there a way to do this…Else I will drop and create the table with new
>> partition column names and run my scripts to ADD PARTITION, with LOCATION
>> specified
>> Thanks
>> sanjay
>>
>> CONFIDENTIALITY NOTICE
>> ======================
>> This email message and any attachments are for the exclusive use of the
>> intended recipient(s) and may contain confidential and privileged
>> information. Any unauthorized review, use, disclosure or distribution is
>> prohibited. If you are not the intended recipient, please contact the
>> sender by reply email and destroy all copies of the original message along
>> with any attachments, from your computer system. If you are the intended
>> recipient, please be advised that the content of this message is subject to
>> access, review and disclosure by the sender's Email System Administrator.
>>
>
>
>
> --
> Nitin Pawar
>
Re: Renaming partition columnname only (locations remain unchanged)
Posted by Nitin Pawar <ni...@gmail.com>.
currently hive partitions on hdfs stored as col1=val1/col2=val2
looking at this if you change the column name in the metadata, I am not
sure you will be able to locate the data with old column name. other way
would be alter table and and run hdfs move to reflect your new column name
in the partition directories on hdfs.
It looks much simpler to just create new table and dump the old one via
hive though
On Wed, Jun 12, 2013 at 3:46 AM, Sanjay Subramanian <
Sanjay.Subramanian@wizecommerce.com> wrote:
> Hi
> I have external tables where I want to change the name of the partition
> column, locations remaining constant
> Is there a way to do this…Else I will drop and create the table with new
> partition column names and run my scripts to ADD PARTITION, with LOCATION
> specified
> Thanks
> sanjay
>
> CONFIDENTIALITY NOTICE
> ======================
> This email message and any attachments are for the exclusive use of the
> intended recipient(s) and may contain confidential and privileged
> information. Any unauthorized review, use, disclosure or distribution is
> prohibited. If you are not the intended recipient, please contact the
> sender by reply email and destroy all copies of the original message along
> with any attachments, from your computer system. If you are the intended
> recipient, please be advised that the content of this message is subject to
> access, review and disclosure by the sender's Email System Administrator.
>
--
Nitin Pawar
Renaming partition columnname only (locations remain unchanged)
Posted by Sanjay Subramanian <Sa...@wizecommerce.com>.
Hi
I have external tables where I want to change the name of the partition column, locations remaining constant
Is there a way to do this…Else I will drop and create the table with new partition column names and run my scripts to ADD PARTITION, with LOCATION specified
Thanks
sanjay
CONFIDENTIALITY NOTICE
======================
This email message and any attachments are for the exclusive use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message along with any attachments, from your computer system. If you are the intended recipient, please be advised that the content of this message is subject to access, review and disclosure by the sender's Email System Administrator.
Re: Create table like with partitions
Posted by Sanjay Subramanian <Sa...@wizecommerce.com>.
>From Russia with Love...
Domain ID:D39022749-LRMS
Domain Name:IT-EBOOKS.INFO
Created On:27-Jul-2011 12:24:45 UTC
Last Updated On:29-Apr-2013 11:00:03 UTC
Expiration Date:27-Jul-2015 12:24:45 UTC
Sponsoring Registrar:GoDaddy.com LLC (R171-LRMS)
Status:CLIENT DELETE PROHIBITED
Status:CLIENT RENEW PROHIBITED
Status:CLIENT TRANSFER PROHIBITED
Status:CLIENT UPDATE PROHIBITED
Registrant ID:CR89106899
Registrant Name:Artur Denisov
Registrant Organization:
Registrant Street1:Akademika Koroleva 21/5
Registrant Street2:
Registrant Street3:
Registrant City:Moscow
Registrant State/Province:Moscow
Registrant Postal Code:127427
Registrant Country:RU
Registrant Phone:+7.4957930823
Registrant Phone Ext.:
Registrant FAX:
Registrant FAX Ext.:
Registrant Email:support@it-ebooks.info
Admin ID:CR89106901
Admin Name:Artur Denisov
Admin Organization:
Admin Street1:Akademika Koroleva 21/5
Admin Street2:
Admin Street3:
Admin City:Moscow
Admin State/Province:Moscow
Admin Postal Code:127427
Admin Country:RU
Admin Phone:+7.4957930823
Admin Phone Ext.:
Admin FAX:
Admin FAX Ext.:
Admin Email:support@it-ebooks.info
Billing ID:CR89106902
Billing Name:Artur Denisov
Billing Organization:
Billing Street1:Akademika Koroleva 21/5
Billing Street2:
Billing Street3:
Billing City:Moscow
Billing State/Province:Moscow
Billing Postal Code:127427
Billing Country:RU
Billing Phone:+7.4957930823
Billing Phone Ext.:
Billing FAX:
Billing FAX Ext.:
Billing Email:support@it-ebooks.info
Tech ID:CR89106900
Tech Name:Artur Denisov
Tech Organization:
Tech Street1:Akademika Koroleva 21/5
Tech Street2:
Tech Street3:
Tech City:Moscow
Tech State/Province:Moscow
Tech Postal Code:127427
Tech Country:RU
Tech Phone:+7.4957930823
Tech Phone Ext.:
Tech FAX:
Tech FAX Ext.:
Tech Email:support@it-ebooks.info
Name Server:NS1.FASTVPS.RU
Name Server:NS2.FASTVPS.RU
Name Server:NS4.FASTVPS.RU
Name Server:NS3.FASTVPS.RU
Name Server:
Name Server:
Name Server:
Name Server:
Name Server:
Name Server:
Name Server:
Name Server:
Name Server:
From: Dean Wampler <de...@gmail.com>>
Reply-To: "user@hive.apache.org<ma...@hive.apache.org>" <us...@hive.apache.org>>
Date: Tuesday, June 11, 2013 11:04 AM
To: "user@hive.apache.org<ma...@hive.apache.org>" <us...@hive.apache.org>>
Cc: "user@hive.apache.org<ma...@hive.apache.org>" <us...@hive.apache.org>>
Subject: Re: Create table like with partitions
I confirmed it is a pirate site.
Sent from my rotary phone.
On Jun 11, 2013, at 10:33 AM, Edward Capriolo <ed...@gmail.com>> wrote:
For reference, any that puts the entire book online like this is likely pirated.
On Tue, Jun 11, 2013 at 8:34 AM, Richa Sharma <ma...@gmail.com>> wrote:
Hi all,
Found a very useful guide online. Link -> http://it-ebooks.info/book/941/
Richa
On Tue, Jun 11, 2013 at 1:53 PM, Richa Sharma <ma...@gmail.com>> wrote:
Thanks for sharing!
I looked at these links .. Is there any documentation with more examples with both static and dynamic partitions covered together.
Richa
On Tue, Jun 11, 2013 at 12:33 PM, Lefty Leverenz <le...@hortonworks.com>> wrote:
Dynamic partitions are described in the Hive design docs here: https://cwiki.apache.org/confluence/display/Hive/DynamicPartitions.
For the configuration parameters, though, you need to look in the language manual here: https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties (search for "dynamic" to find various parameters related to dynamic partitions).
– Lefty
On Mon, Jun 10, 2013 at 7:06 AM, Owen O'Malley <om...@apache.org>> wrote:
You need to create the partitioned table and then copy the rows into it.
create table foo_staging (int x, int y);
create table foo(int x) partitioned by (int y) clustered by (x) into 16 buckets;
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.enforce.bucketing = true;
insert overwrite table partition (y) select * from foo_staging;
On Mon, Jun 10, 2013 at 6:38 AM, Nitin Pawar <ni...@gmail.com>> wrote:
If a table is not partitioned and then you want to partition the table on the data already written but data is not in partition format, that is not doable.
Best approach would be, create a new table definition with the partition columns you want.
turn on the dynamic partitioning system before you load data into new table
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table partitioned(columns) select * from oldtable
remove old table
PS: wait for others to add more suggestions. I may be very well wrong in suggesting this
On Mon, Jun 10, 2013 at 7:01 PM, Peter Marron <Pe...@trilliumsoftware.com>> wrote:
Hi,
Using hive 0.10.0 over hadoop 1.0.4
I have a (non-partitioned) table with loads of columns.
I would like to create a partitioned table with the same set of columns.
So the approach that I have been taking is to use “CREATE TABLE copy LIKE original;”
then I can use ALTER TABLE to change the location and the INPUTFORMAT
and the OUTPUTFORMAT and the SERDE and properties and pretty much
everything else. However I don’t seem to be able to make it partitioned.
Sure I can add partitions if it’s already partitioned but I don’t seem
to be able to make it partitioned if it’s not already. I get errors like this:
hive> ALTER TABLE customerShortValues ADD PARTITION (aid='1') LOCATION 'E7/phase2/values/aid=1';
FAILED: Error in metadata: table is not partitioned but partition spec exists: {aid=1}
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask
So, I guess that I could create the table I want by hand copying over all the
column definitions. But is there an easier way?
Z
--
Nitin Pawar
CONFIDENTIALITY NOTICE
======================
This email message and any attachments are for the exclusive use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message along with any attachments, from your computer system. If you are the intended recipient, please be advised that the content of this message is subject to access, review and disclosure by the sender's Email System Administrator.
Re: Create table like with partitions
Posted by Dean Wampler <de...@gmail.com>.
I confirmed it is a pirate site.
Sent from my rotary phone.
On Jun 11, 2013, at 10:33 AM, Edward Capriolo <ed...@gmail.com> wrote:
> For reference, any that puts the entire book online like this is likely pirated.
>
>
>
>
> On Tue, Jun 11, 2013 at 8:34 AM, Richa Sharma <ma...@gmail.com> wrote:
>> Hi all,
>>
>> Found a very useful guide online. Link -> http://it-ebooks.info/book/941/
>>
>> Richa
>>
>>
>> On Tue, Jun 11, 2013 at 1:53 PM, Richa Sharma <ma...@gmail.com> wrote:
>>> Thanks for sharing!
>>>
>>> I looked at these links .. Is there any documentation with more examples with both static and dynamic partitions covered together.
>>>
>>> Richa
>>>
>>>
>>> On Tue, Jun 11, 2013 at 12:33 PM, Lefty Leverenz <le...@hortonworks.com> wrote:
>>>> Dynamic partitions are described in the Hive design docs here: https://cwiki.apache.org/confluence/display/Hive/DynamicPartitions.
>>>>
>>>> For the configuration parameters, though, you need to look in the language manual here: https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties (search for "dynamic" to find various parameters related to dynamic partitions).
>>>>
>>>> – Lefty
>>>>
>>>>
>>>>
>>>> On Mon, Jun 10, 2013 at 7:06 AM, Owen O'Malley <om...@apache.org> wrote:
>>>>> You need to create the partitioned table and then copy the rows into it.
>>>>>
>>>>> create table foo_staging (int x, int y);
>>>>>
>>>>> create table foo(int x) partitioned by (int y) clustered by (x) into 16 buckets;
>>>>>
>>>>> set hive.exec.dynamic.partition=true;
>>>>> set hive.exec.dynamic.partition.mode=nonstrict;
>>>>> set hive.enforce.bucketing = true;
>>>>>
>>>>> insert overwrite table partition (y) select * from foo_staging;
>>>>>
>>>>>
>>>>> On Mon, Jun 10, 2013 at 6:38 AM, Nitin Pawar <ni...@gmail.com> wrote:
>>>>>> If a table is not partitioned and then you want to partition the table on the data already written but data is not in partition format, that is not doable.
>>>>>>
>>>>>> Best approach would be, create a new table definition with the partition columns you want.
>>>>>> turn on the dynamic partitioning system before you load data into new table
>>>>>> set hive.exec.dynamic.partition=true;
>>>>>> set hive.exec.dynamic.partition.mode=nonstrict;
>>>>>> insert overwrite table partitioned(columns) select * from oldtable
>>>>>>
>>>>>> remove old table
>>>>>>
>>>>>> PS: wait for others to add more suggestions. I may be very well wrong in suggesting this
>>>>>>
>>>>>>
>>>>>> On Mon, Jun 10, 2013 at 7:01 PM, Peter Marron <Pe...@trilliumsoftware.com> wrote:
>>>>>>> Hi,
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Using hive 0.10.0 over hadoop 1.0.4
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> I have a (non-partitioned) table with loads of columns.
>>>>>>>
>>>>>>> I would like to create a partitioned table with the same set of columns.
>>>>>>>
>>>>>>> So the approach that I have been taking is to use “CREATE TABLE copy LIKE original;”
>>>>>>>
>>>>>>> then I can use ALTER TABLE to change the location and the INPUTFORMAT
>>>>>>>
>>>>>>> and the OUTPUTFORMAT and the SERDE and properties and pretty much
>>>>>>>
>>>>>>> everything else. However I don’t seem to be able to make it partitioned.
>>>>>>>
>>>>>>> Sure I can add partitions if it’s already partitioned but I don’t seem
>>>>>>>
>>>>>>> to be able to make it partitioned if it’s not already. I get errors like this:
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> hive> ALTER TABLE customerShortValues ADD PARTITION (aid='1') LOCATION 'E7/phase2/values/aid=1';
>>>>>>>
>>>>>>> FAILED: Error in metadata: table is not partitioned but partition spec exists: {aid=1}
>>>>>>>
>>>>>>> FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> So, I guess that I could create the table I want by hand copying over all the
>>>>>>>
>>>>>>> column definitions. But is there an easier way?
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Z
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Nitin Pawar
>
Re: Create table like with partitions
Posted by Edward Capriolo <ed...@gmail.com>.
For reference, any that puts the entire book online like this is likely
pirated.
On Tue, Jun 11, 2013 at 8:34 AM, Richa Sharma
<ma...@gmail.com>wrote:
> Hi all,
>
> Found a very useful guide online. Link -> http://it-ebooks.info/book/941/
>
> Richa
>
>
> On Tue, Jun 11, 2013 at 1:53 PM, Richa Sharma <mailtorichasharma@gmail.com
> > wrote:
>
>> Thanks for sharing!
>>
>> I looked at these links .. Is there any documentation with more examples
>> with both static and dynamic partitions covered together.
>>
>> Richa
>>
>>
>> On Tue, Jun 11, 2013 at 12:33 PM, Lefty Leverenz <le...@hortonworks.com>wrote:
>>
>>> Dynamic partitions are described in the Hive design docs here:
>>> https://cwiki.apache.org/confluence/display/Hive/DynamicPartitions.
>>>
>>> For the configuration parameters, though, you need to look in the
>>> language manual here:
>>> https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties (search
>>> for "dynamic" to find various parameters related to dynamic partitions).
>>>
>>> – Lefty
>>>
>>>
>>>
>>> On Mon, Jun 10, 2013 at 7:06 AM, Owen O'Malley <om...@apache.org>wrote:
>>>
>>>> You need to create the partitioned table and then copy the rows into it.
>>>>
>>>> create table foo_staging (int x, int y);
>>>>
>>>> create table foo(int x) partitioned by (int y) clustered by (x) into 16
>>>> buckets;
>>>>
>>>> set hive.exec.dynamic.partition=true;
>>>> set hive.exec.dynamic.partition.mode=nonstrict;
>>>> set hive.enforce.bucketing = true;
>>>>
>>>> insert overwrite table partition (y) select * from foo_staging;
>>>>
>>>>
>>>> On Mon, Jun 10, 2013 at 6:38 AM, Nitin Pawar <ni...@gmail.com>wrote:
>>>>
>>>>> If a table is not partitioned and then you want to partition the table
>>>>> on the data already written but data is not in partition format, that is
>>>>> not doable.
>>>>>
>>>>> Best approach would be, create a new table definition with the
>>>>> partition columns you want.
>>>>> turn on the dynamic partitioning system before you load data into new
>>>>> table
>>>>>
>>>>> set hive.exec.dynamic.partition=true;
>>>>> set hive.exec.dynamic.partition.mode=nonstrict;
>>>>>
>>>>> insert overwrite table partitioned(columns) select * from oldtable
>>>>>
>>>>>
>>>>> remove old table
>>>>>
>>>>> PS: wait for others to add more suggestions. I may be very well wrong
>>>>> in suggesting this
>>>>>
>>>>>
>>>>> On Mon, Jun 10, 2013 at 7:01 PM, Peter Marron <
>>>>> Peter.Marron@trilliumsoftware.com> wrote:
>>>>>
>>>>>> Hi,****
>>>>>>
>>>>>> ** **
>>>>>>
>>>>>> Using hive 0.10.0 over hadoop 1.0.4****
>>>>>>
>>>>>> ** **
>>>>>>
>>>>>> I have a (non-partitioned) table with loads of columns.****
>>>>>>
>>>>>> I would like to create a partitioned table with the same set of
>>>>>> columns.****
>>>>>>
>>>>>> So the approach that I have been taking is to use “CREATE TABLE copy
>>>>>> LIKE original;”****
>>>>>>
>>>>>> then I can use ALTER TABLE to change the location and the INPUTFORMAT
>>>>>> ****
>>>>>>
>>>>>> and the OUTPUTFORMAT and the SERDE and properties and pretty much****
>>>>>>
>>>>>> everything else. However I don’t seem to be able to make it
>>>>>> partitioned.****
>>>>>>
>>>>>> Sure I can add partitions if it’s already partitioned but I don’t seem
>>>>>> ****
>>>>>>
>>>>>> to be able to make it partitioned if it’s not already. I get errors
>>>>>> like this:****
>>>>>>
>>>>>> ** **
>>>>>>
>>>>>> hive> ALTER TABLE customerShortValues ADD PARTITION (aid='1')
>>>>>> LOCATION 'E7/phase2/values/aid=1';****
>>>>>>
>>>>>> FAILED: Error in metadata: table is not partitioned but partition
>>>>>> spec exists: {aid=1}****
>>>>>>
>>>>>> FAILED: Execution Error, return code 1 from
>>>>>> org.apache.hadoop.hive.ql.exec.DDLTask****
>>>>>>
>>>>>> ** **
>>>>>>
>>>>>> So, I guess that I could create the table I want by hand copying over
>>>>>> all the****
>>>>>>
>>>>>> column definitions. But is there an easier way?****
>>>>>>
>>>>>> ** **
>>>>>>
>>>>>> Z****
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Nitin Pawar
>>>>>
>>>>
>>>>
>>>
>>
>
Re: Create table like with partitions
Posted by Richa Sharma <ma...@gmail.com>.
Hi all,
Found a very useful guide online. Link -> http://it-ebooks.info/book/941/
Richa
On Tue, Jun 11, 2013 at 1:53 PM, Richa Sharma
<ma...@gmail.com>wrote:
> Thanks for sharing!
>
> I looked at these links .. Is there any documentation with more examples
> with both static and dynamic partitions covered together.
>
> Richa
>
>
> On Tue, Jun 11, 2013 at 12:33 PM, Lefty Leverenz <le...@hortonworks.com>wrote:
>
>> Dynamic partitions are described in the Hive design docs here:
>> https://cwiki.apache.org/confluence/display/Hive/DynamicPartitions.
>>
>> For the configuration parameters, though, you need to look in the
>> language manual here:
>> https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties (search
>> for "dynamic" to find various parameters related to dynamic partitions).
>>
>> – Lefty
>>
>>
>>
>> On Mon, Jun 10, 2013 at 7:06 AM, Owen O'Malley <om...@apache.org>wrote:
>>
>>> You need to create the partitioned table and then copy the rows into it.
>>>
>>> create table foo_staging (int x, int y);
>>>
>>> create table foo(int x) partitioned by (int y) clustered by (x) into 16
>>> buckets;
>>>
>>> set hive.exec.dynamic.partition=true;
>>> set hive.exec.dynamic.partition.mode=nonstrict;
>>> set hive.enforce.bucketing = true;
>>>
>>> insert overwrite table partition (y) select * from foo_staging;
>>>
>>>
>>> On Mon, Jun 10, 2013 at 6:38 AM, Nitin Pawar <ni...@gmail.com>wrote:
>>>
>>>> If a table is not partitioned and then you want to partition the table
>>>> on the data already written but data is not in partition format, that is
>>>> not doable.
>>>>
>>>> Best approach would be, create a new table definition with the
>>>> partition columns you want.
>>>> turn on the dynamic partitioning system before you load data into new
>>>> table
>>>>
>>>> set hive.exec.dynamic.partition=true;
>>>> set hive.exec.dynamic.partition.mode=nonstrict;
>>>>
>>>> insert overwrite table partitioned(columns) select * from oldtable
>>>>
>>>>
>>>> remove old table
>>>>
>>>> PS: wait for others to add more suggestions. I may be very well wrong
>>>> in suggesting this
>>>>
>>>>
>>>> On Mon, Jun 10, 2013 at 7:01 PM, Peter Marron <
>>>> Peter.Marron@trilliumsoftware.com> wrote:
>>>>
>>>>> Hi,****
>>>>>
>>>>> ** **
>>>>>
>>>>> Using hive 0.10.0 over hadoop 1.0.4****
>>>>>
>>>>> ** **
>>>>>
>>>>> I have a (non-partitioned) table with loads of columns.****
>>>>>
>>>>> I would like to create a partitioned table with the same set of
>>>>> columns.****
>>>>>
>>>>> So the approach that I have been taking is to use “CREATE TABLE copy
>>>>> LIKE original;”****
>>>>>
>>>>> then I can use ALTER TABLE to change the location and the INPUTFORMAT*
>>>>> ***
>>>>>
>>>>> and the OUTPUTFORMAT and the SERDE and properties and pretty much****
>>>>>
>>>>> everything else. However I don’t seem to be able to make it
>>>>> partitioned.****
>>>>>
>>>>> Sure I can add partitions if it’s already partitioned but I don’t seem
>>>>> ****
>>>>>
>>>>> to be able to make it partitioned if it’s not already. I get errors
>>>>> like this:****
>>>>>
>>>>> ** **
>>>>>
>>>>> hive> ALTER TABLE customerShortValues ADD PARTITION (aid='1') LOCATION
>>>>> 'E7/phase2/values/aid=1';****
>>>>>
>>>>> FAILED: Error in metadata: table is not partitioned but partition spec
>>>>> exists: {aid=1}****
>>>>>
>>>>> FAILED: Execution Error, return code 1 from
>>>>> org.apache.hadoop.hive.ql.exec.DDLTask****
>>>>>
>>>>> ** **
>>>>>
>>>>> So, I guess that I could create the table I want by hand copying over
>>>>> all the****
>>>>>
>>>>> column definitions. But is there an easier way?****
>>>>>
>>>>> ** **
>>>>>
>>>>> Z****
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Nitin Pawar
>>>>
>>>
>>>
>>
>
Re: Create table like with partitions
Posted by Richa Sharma <ma...@gmail.com>.
Thanks for sharing!
I looked at these links .. Is there any documentation with more examples
with both static and dynamic partitions covered together.
Richa
On Tue, Jun 11, 2013 at 12:33 PM, Lefty Leverenz <le...@hortonworks.com>wrote:
> Dynamic partitions are described in the Hive design docs here:
> https://cwiki.apache.org/confluence/display/Hive/DynamicPartitions.
>
> For the configuration parameters, though, you need to look in the language
> manual here:
> https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties (search
> for "dynamic" to find various parameters related to dynamic partitions).
>
> – Lefty
>
>
>
> On Mon, Jun 10, 2013 at 7:06 AM, Owen O'Malley <om...@apache.org> wrote:
>
>> You need to create the partitioned table and then copy the rows into it.
>>
>> create table foo_staging (int x, int y);
>>
>> create table foo(int x) partitioned by (int y) clustered by (x) into 16
>> buckets;
>>
>> set hive.exec.dynamic.partition=true;
>> set hive.exec.dynamic.partition.mode=nonstrict;
>> set hive.enforce.bucketing = true;
>>
>> insert overwrite table partition (y) select * from foo_staging;
>>
>>
>> On Mon, Jun 10, 2013 at 6:38 AM, Nitin Pawar <ni...@gmail.com>wrote:
>>
>>> If a table is not partitioned and then you want to partition the table
>>> on the data already written but data is not in partition format, that is
>>> not doable.
>>>
>>> Best approach would be, create a new table definition with the partition
>>> columns you want.
>>> turn on the dynamic partitioning system before you load data into new
>>> table
>>>
>>> set hive.exec.dynamic.partition=true;
>>> set hive.exec.dynamic.partition.mode=nonstrict;
>>>
>>> insert overwrite table partitioned(columns) select * from oldtable
>>>
>>>
>>> remove old table
>>>
>>> PS: wait for others to add more suggestions. I may be very well wrong in
>>> suggesting this
>>>
>>>
>>> On Mon, Jun 10, 2013 at 7:01 PM, Peter Marron <
>>> Peter.Marron@trilliumsoftware.com> wrote:
>>>
>>>> Hi,****
>>>>
>>>> ** **
>>>>
>>>> Using hive 0.10.0 over hadoop 1.0.4****
>>>>
>>>> ** **
>>>>
>>>> I have a (non-partitioned) table with loads of columns.****
>>>>
>>>> I would like to create a partitioned table with the same set of columns.
>>>> ****
>>>>
>>>> So the approach that I have been taking is to use “CREATE TABLE copy
>>>> LIKE original;”****
>>>>
>>>> then I can use ALTER TABLE to change the location and the INPUTFORMAT**
>>>> **
>>>>
>>>> and the OUTPUTFORMAT and the SERDE and properties and pretty much****
>>>>
>>>> everything else. However I don’t seem to be able to make it partitioned.
>>>> ****
>>>>
>>>> Sure I can add partitions if it’s already partitioned but I don’t seem*
>>>> ***
>>>>
>>>> to be able to make it partitioned if it’s not already. I get errors
>>>> like this:****
>>>>
>>>> ** **
>>>>
>>>> hive> ALTER TABLE customerShortValues ADD PARTITION (aid='1') LOCATION
>>>> 'E7/phase2/values/aid=1';****
>>>>
>>>> FAILED: Error in metadata: table is not partitioned but partition spec
>>>> exists: {aid=1}****
>>>>
>>>> FAILED: Execution Error, return code 1 from
>>>> org.apache.hadoop.hive.ql.exec.DDLTask****
>>>>
>>>> ** **
>>>>
>>>> So, I guess that I could create the table I want by hand copying over
>>>> all the****
>>>>
>>>> column definitions. But is there an easier way?****
>>>>
>>>> ** **
>>>>
>>>> Z****
>>>>
>>>
>>>
>>>
>>> --
>>> Nitin Pawar
>>>
>>
>>
>
Re: Create table like with partitions
Posted by Lefty Leverenz <le...@hortonworks.com>.
Dynamic partitions are described in the Hive design docs here:
https://cwiki.apache.org/confluence/display/Hive/DynamicPartitions.
For the configuration parameters, though, you need to look in the language
manual here:
https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties
(search
for "dynamic" to find various parameters related to dynamic partitions).
– Lefty
On Mon, Jun 10, 2013 at 7:06 AM, Owen O'Malley <om...@apache.org> wrote:
> You need to create the partitioned table and then copy the rows into it.
>
> create table foo_staging (int x, int y);
>
> create table foo(int x) partitioned by (int y) clustered by (x) into 16
> buckets;
>
> set hive.exec.dynamic.partition=true;
> set hive.exec.dynamic.partition.mode=nonstrict;
> set hive.enforce.bucketing = true;
>
> insert overwrite table partition (y) select * from foo_staging;
>
>
> On Mon, Jun 10, 2013 at 6:38 AM, Nitin Pawar <ni...@gmail.com>wrote:
>
>> If a table is not partitioned and then you want to partition the table on
>> the data already written but data is not in partition format, that is not
>> doable.
>>
>> Best approach would be, create a new table definition with the partition
>> columns you want.
>> turn on the dynamic partitioning system before you load data into new
>> table
>>
>> set hive.exec.dynamic.partition=true;
>> set hive.exec.dynamic.partition.mode=nonstrict;
>>
>> insert overwrite table partitioned(columns) select * from oldtable
>>
>>
>> remove old table
>>
>> PS: wait for others to add more suggestions. I may be very well wrong in
>> suggesting this
>>
>>
>> On Mon, Jun 10, 2013 at 7:01 PM, Peter Marron <
>> Peter.Marron@trilliumsoftware.com> wrote:
>>
>>> Hi,****
>>>
>>> ** **
>>>
>>> Using hive 0.10.0 over hadoop 1.0.4****
>>>
>>> ** **
>>>
>>> I have a (non-partitioned) table with loads of columns.****
>>>
>>> I would like to create a partitioned table with the same set of columns.
>>> ****
>>>
>>> So the approach that I have been taking is to use “CREATE TABLE copy
>>> LIKE original;”****
>>>
>>> then I can use ALTER TABLE to change the location and the INPUTFORMAT***
>>> *
>>>
>>> and the OUTPUTFORMAT and the SERDE and properties and pretty much****
>>>
>>> everything else. However I don’t seem to be able to make it partitioned.
>>> ****
>>>
>>> Sure I can add partitions if it’s already partitioned but I don’t seem**
>>> **
>>>
>>> to be able to make it partitioned if it’s not already. I get errors like
>>> this:****
>>>
>>> ** **
>>>
>>> hive> ALTER TABLE customerShortValues ADD PARTITION (aid='1') LOCATION
>>> 'E7/phase2/values/aid=1';****
>>>
>>> FAILED: Error in metadata: table is not partitioned but partition spec
>>> exists: {aid=1}****
>>>
>>> FAILED: Execution Error, return code 1 from
>>> org.apache.hadoop.hive.ql.exec.DDLTask****
>>>
>>> ** **
>>>
>>> So, I guess that I could create the table I want by hand copying over
>>> all the****
>>>
>>> column definitions. But is there an easier way?****
>>>
>>> ** **
>>>
>>> Z****
>>>
>>
>>
>>
>> --
>> Nitin Pawar
>>
>
>
Re: Create table like with partitions
Posted by Owen O'Malley <om...@apache.org>.
You need to create the partitioned table and then copy the rows into it.
create table foo_staging (int x, int y);
create table foo(int x) partitioned by (int y) clustered by (x) into 16
buckets;
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.enforce.bucketing = true;
insert overwrite table partition (y) select * from foo_staging;
On Mon, Jun 10, 2013 at 6:38 AM, Nitin Pawar <ni...@gmail.com>wrote:
> If a table is not partitioned and then you want to partition the table on
> the data already written but data is not in partition format, that is not
> doable.
>
> Best approach would be, create a new table definition with the partition
> columns you want.
> turn on the dynamic partitioning system before you load data into new
> table
>
> set hive.exec.dynamic.partition=true;
> set hive.exec.dynamic.partition.mode=nonstrict;
>
> insert overwrite table partitioned(columns) select * from oldtable
>
>
> remove old table
>
> PS: wait for others to add more suggestions. I may be very well wrong in
> suggesting this
>
>
> On Mon, Jun 10, 2013 at 7:01 PM, Peter Marron <
> Peter.Marron@trilliumsoftware.com> wrote:
>
>> Hi,****
>>
>> ** **
>>
>> Using hive 0.10.0 over hadoop 1.0.4****
>>
>> ** **
>>
>> I have a (non-partitioned) table with loads of columns.****
>>
>> I would like to create a partitioned table with the same set of columns.*
>> ***
>>
>> So the approach that I have been taking is to use “CREATE TABLE copy LIKE
>> original;”****
>>
>> then I can use ALTER TABLE to change the location and the INPUTFORMAT****
>>
>> and the OUTPUTFORMAT and the SERDE and properties and pretty much****
>>
>> everything else. However I don’t seem to be able to make it partitioned.*
>> ***
>>
>> Sure I can add partitions if it’s already partitioned but I don’t seem***
>> *
>>
>> to be able to make it partitioned if it’s not already. I get errors like
>> this:****
>>
>> ** **
>>
>> hive> ALTER TABLE customerShortValues ADD PARTITION (aid='1') LOCATION
>> 'E7/phase2/values/aid=1';****
>>
>> FAILED: Error in metadata: table is not partitioned but partition spec
>> exists: {aid=1}****
>>
>> FAILED: Execution Error, return code 1 from
>> org.apache.hadoop.hive.ql.exec.DDLTask****
>>
>> ** **
>>
>> So, I guess that I could create the table I want by hand copying over all
>> the****
>>
>> column definitions. But is there an easier way?****
>>
>> ** **
>>
>> Z****
>>
>
>
>
> --
> Nitin Pawar
>
Re: Create table like with partitions
Posted by Richa Sharma <ma...@gmail.com>.
Hi,
Can you please point to documentation on Dynamic partitioning.
I dont fully understand meaning of values for these parameters.
Regards
Richa
On Mon, Jun 10, 2013 at 7:08 PM, Nitin Pawar <ni...@gmail.com>wrote:
> If a table is not partitioned and then you want to partition the table on
> the data already written but data is not in partition format, that is not
> doable.
>
> Best approach would be, create a new table definition with the partition
> columns you want.
> turn on the dynamic partitioning system before you load data into new
> table
>
> set hive.exec.dynamic.partition=true;
> set hive.exec.dynamic.partition.mode=nonstrict;
>
> insert overwrite table partitioned(columns) select * from oldtable
>
>
> remove old table
>
> PS: wait for others to add more suggestions. I may be very well wrong in
> suggesting this
>
>
> On Mon, Jun 10, 2013 at 7:01 PM, Peter Marron <
> Peter.Marron@trilliumsoftware.com> wrote:
>
>> Hi,****
>>
>> ** **
>>
>> Using hive 0.10.0 over hadoop 1.0.4****
>>
>> ** **
>>
>> I have a (non-partitioned) table with loads of columns.****
>>
>> I would like to create a partitioned table with the same set of columns.*
>> ***
>>
>> So the approach that I have been taking is to use “CREATE TABLE copy LIKE
>> original;”****
>>
>> then I can use ALTER TABLE to change the location and the INPUTFORMAT****
>>
>> and the OUTPUTFORMAT and the SERDE and properties and pretty much****
>>
>> everything else. However I don’t seem to be able to make it partitioned.*
>> ***
>>
>> Sure I can add partitions if it’s already partitioned but I don’t seem***
>> *
>>
>> to be able to make it partitioned if it’s not already. I get errors like
>> this:****
>>
>> ** **
>>
>> hive> ALTER TABLE customerShortValues ADD PARTITION (aid='1') LOCATION
>> 'E7/phase2/values/aid=1';****
>>
>> FAILED: Error in metadata: table is not partitioned but partition spec
>> exists: {aid=1}****
>>
>> FAILED: Execution Error, return code 1 from
>> org.apache.hadoop.hive.ql.exec.DDLTask****
>>
>> ** **
>>
>> So, I guess that I could create the table I want by hand copying over all
>> the****
>>
>> column definitions. But is there an easier way?****
>>
>> ** **
>>
>> Z****
>>
>
>
>
> --
> Nitin Pawar
>
Re: Create table like with partitions
Posted by Nitin Pawar <ni...@gmail.com>.
If a table is not partitioned and then you want to partition the table on
the data already written but data is not in partition format, that is not
doable.
Best approach would be, create a new table definition with the partition
columns you want.
turn on the dynamic partitioning system before you load data into new table
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table partitioned(columns) select * from oldtable
remove old table
PS: wait for others to add more suggestions. I may be very well wrong in
suggesting this
On Mon, Jun 10, 2013 at 7:01 PM, Peter Marron <
Peter.Marron@trilliumsoftware.com> wrote:
> Hi,****
>
> ** **
>
> Using hive 0.10.0 over hadoop 1.0.4****
>
> ** **
>
> I have a (non-partitioned) table with loads of columns.****
>
> I would like to create a partitioned table with the same set of columns.**
> **
>
> So the approach that I have been taking is to use “CREATE TABLE copy LIKE
> original;”****
>
> then I can use ALTER TABLE to change the location and the INPUTFORMAT****
>
> and the OUTPUTFORMAT and the SERDE and properties and pretty much****
>
> everything else. However I don’t seem to be able to make it partitioned.**
> **
>
> Sure I can add partitions if it’s already partitioned but I don’t seem****
>
> to be able to make it partitioned if it’s not already. I get errors like
> this:****
>
> ** **
>
> hive> ALTER TABLE customerShortValues ADD PARTITION (aid='1') LOCATION
> 'E7/phase2/values/aid=1';****
>
> FAILED: Error in metadata: table is not partitioned but partition spec
> exists: {aid=1}****
>
> FAILED: Execution Error, return code 1 from
> org.apache.hadoop.hive.ql.exec.DDLTask****
>
> ** **
>
> So, I guess that I could create the table I want by hand copying over all
> the****
>
> column definitions. But is there an easier way?****
>
> ** **
>
> Z****
>
--
Nitin Pawar