Return-Path: X-Original-To: apmail-hive-user-archive@www.apache.org Delivered-To: apmail-hive-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 2589610A56 for ; Tue, 11 Jun 2013 08:23:40 +0000 (UTC) Received: (qmail 41796 invoked by uid 500); 11 Jun 2013 08:23:37 -0000 Delivered-To: apmail-hive-user-archive@hive.apache.org Received: (qmail 41064 invoked by uid 500); 11 Jun 2013 08:23:34 -0000 Mailing-List: contact user-help@hive.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@hive.apache.org Delivered-To: mailing list user@hive.apache.org Received: (qmail 41056 invoked by uid 99); 11 Jun 2013 08:23:33 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 11 Jun 2013 08:23:33 +0000 X-ASF-Spam-Status: No, hits=1.5 required=5.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of mailtorichasharma@gmail.com designates 74.125.82.52 as permitted sender) Received: from [74.125.82.52] (HELO mail-wg0-f52.google.com) (74.125.82.52) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 11 Jun 2013 08:23:29 +0000 Received: by mail-wg0-f52.google.com with SMTP id z12so4953727wgg.31 for ; Tue, 11 Jun 2013 01:23:07 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; bh=Kyigs6P6QkhbO3lJE9Ljd23S1INZR78BvP25A/gN75o=; b=DIyF0lwLtG9Ugog/222r+FcMRlAlxXO7+LqHoDOoitVXGWZlkH5/Ezo2OLjk68k76O tZb6Dyl+AkUHBwlKU6MYe+0ncwYM72VJIa9tmfp4NW64iuhI6DW4gz2IFwOGNsu9Atdk KBOLUJnL1VdptdNFff1OMBRSPmMuw7hvOZY5Xd5Q51/EQMu8lJQCRCx/Qe1FzDVWFVKZ 4GrV4XObJ9UMdAw8Xo2aLqArR7RkorztaSkhACKDdW1qM9ITBSiXm6SNId2V1iuxITTm wHGw6YL20WBfrvj6HXfhAQFlUjF1bq4hNMrbDzC9ox/Adp8Wq950WBKUR3aBLd07srNC 5Lbg== MIME-Version: 1.0 X-Received: by 10.194.171.65 with SMTP id as1mr7726807wjc.40.1370938987756; Tue, 11 Jun 2013 01:23:07 -0700 (PDT) Received: by 10.194.172.201 with HTTP; Tue, 11 Jun 2013 01:23:07 -0700 (PDT) In-Reply-To: References: <458BA7AF19306B4FAA5FADDAD6D223353F02D507@BLUPRD0811MB401.namprd08.prod.outlook.com> Date: Tue, 11 Jun 2013 13:53:07 +0530 Message-ID: Subject: Re: Create table like with partitions From: Richa Sharma To: user@hive.apache.org Content-Type: multipart/alternative; boundary=089e0122f1a25fc0e304dedc9bad X-Virus-Checked: Checked by ClamAV on apache.org --089e0122f1a25fc0e304dedc9bad Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable 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 wro= te: > 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 languag= e > manual here: > https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties= (search > for "dynamic" to find various parameters related to dynamic partitions). > > =96 Lefty > > > > On Mon, Jun 10, 2013 at 7:06 AM, Owen O'Malley 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=3Dtrue; >> set hive.exec.dynamic.partition.mode=3Dnonstrict; >> set hive.enforce.bucketing =3D true; >> >> insert overwrite table partition (y) select * from foo_staging; >> >> >> On Mon, Jun 10, 2013 at 6:38 AM, Nitin Pawar wr= ote: >> >>> 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 i= s >>> not doable. >>> >>> Best approach would be, create a new table definition with the partitio= n >>> columns you want. >>> turn on the dynamic partitioning system before you load data into new >>> table >>> >>> set hive.exec.dynamic.partition=3Dtrue; >>> set hive.exec.dynamic.partition.mode=3Dnonstrict; >>> >>> 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 i= n >>> 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 column= s. >>>> **** >>>> >>>> So the approach that I have been taking is to use =93CREATE TABLE copy >>>> LIKE original;=94**** >>>> >>>> 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=92t seem to be able to make it partitio= ned. >>>> **** >>>> >>>> Sure I can add partitions if it=92s already partitioned but I don=92t = seem* >>>> *** >>>> >>>> to be able to make it partitioned if it=92s not already. I get errors >>>> like this:**** >>>> >>>> ** ** >>>> >>>> hive> ALTER TABLE customerShortValues ADD PARTITION (aid=3D'1') LOCATI= ON >>>> 'E7/phase2/values/aid=3D1';**** >>>> >>>> FAILED: Error in metadata: table is not partitioned but partition spec >>>> exists: {aid=3D1}**** >>>> >>>> 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 >>> >> >> > --089e0122f1a25fc0e304dedc9bad Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: quoted-printable
Thanks for sharing!

I looked at t= hese links .. Is there any documentation with more examples with both stati= c and dynamic partitions covered together.

Richa


On Tue, Jun 11, 2013 at 12:33 PM, Lefty Leverenz <= lefty@hortonwork= s.com> wrote:
Dynamic partitions are described in the Hive= design docs here: =A0https://cwiki.apache.org/conflu= ence/display/Hive/DynamicPartitions.=A0

For the configuration parameters, though, you need to look in th= e language manual here: =A0https://cwiki.apach= e.org/confluence/display/Hive/Configuration+Properties=A0(search for &q= uot;dynamic" to find various parameters related to dynamic partitions)= .=A0

=
=96 Lefty



On Mon, Jun 10, 2013 at 7:06 AM, Owen O&= #39;Malley <omalley@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 b= uckets;

set hive.exec.dynamic.partition=3Dtrue;
set hive.exec.dynamic.partit= ion.mode=3Dnonstrict;
set hive.enforce.bucketing =3D true;

inse= rt overwrite table partition (y) select * from foo_staging;=A0
<= div>


On Mon, Jun 10, 2013 at 6:38 AM, Nitin P= awar <nitinpawar432@gmail.com> wrote:
If a table is not partitioned and then you want to partiti= on the table on the data already written but data is not in partition forma= t, that is not doable.=A0

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=A0
set hive.exec.dynamic.partition=3Dtrue;
set hive.exec.dynamic.partition.mode=3Dnonstrict;
insert overwrite table partitioned(columns) select * from oldtable

remove old table=A0

PS: wai= t for others to add more suggestions. I may be very well wrong in suggestin= g this=A0


On Mon, Jun 10, 2013 at 7:01 PM, Peter Marron <Peter.Ma= rron@trilliumsoftware.com> wrote:

Hi,

=A0

Using hive 0.10.0 over hadoop 1.0.4

=A0

I have a (non-partitioned) table with loads of colum= ns.

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 = =93CREATE TABLE copy LIKE original;=94

then I can use ALTER TABLE to change the location an= d the INPUTFORMAT

and the OUTPUTFORMAT and the SERDE and properties an= d pretty much

everything else. However I don=92t seem to be able t= o make it partitioned.

Sure I can add partitions if it=92s already partitio= ned but I don=92t seem

to be able to make it partitioned if it=92s not alre= ady. I get errors like this:

=A0

hive> ALTER TABLE customerShortValues ADD PARTITI= ON (aid=3D'1') LOCATION 'E7/phase2/values/aid=3D1';<= u>

FAILED: Error in metadata: table is not partitioned = but partition spec exists: {aid=3D1}

FAILED: Execution Error, return code 1 from org.apac= he.hadoop.hive.ql.exec.DDLTask

=A0

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?

=A0

Z




<= font color=3D"#888888">--
Nitin Pawar



--089e0122f1a25fc0e304dedc9bad--