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 CE7F910936 for ; Mon, 10 Jun 2013 14:06:11 +0000 (UTC) Received: (qmail 88146 invoked by uid 500); 10 Jun 2013 14:06:10 -0000 Delivered-To: apmail-hive-user-archive@hive.apache.org Received: (qmail 87774 invoked by uid 500); 10 Jun 2013 14:06:07 -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 87757 invoked by uid 99); 10 Jun 2013 14:06:06 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 10 Jun 2013 14:06:06 +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.175 as permitted sender) Received: from [74.125.82.175] (HELO mail-we0-f175.google.com) (74.125.82.175) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 10 Jun 2013 14:06:01 +0000 Received: by mail-we0-f175.google.com with SMTP id t59so4774767wes.6 for ; Mon, 10 Jun 2013 07:05:40 -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=KPjRJAkFAVyz+GQvKRh8laLS68lbduoZusKlPCVuYUQ=; b=CF8+m64OeHrhAS/QkvjhoNHwWg9Xc40HBiFFFlbY9zZgh2Z0kGxyAbNzQtD/nV0Pk2 /Tu1lyroIHBi+gHvlt+islna66TzHVRZNyQoV6XAnsLie8Cwn0veqSdGxdD9r4XoFI+3 gu+BrRmv4yCASFE0D0UPCRCRk9yYE1zdxioNZCzwqbvHTBx72VzE6WJel1qKu4BFXbu1 /WFOz7D+wtpE9cRbDxVet8AtwcJ06vjIJj4GJuACluvvR/XAihC7Az5h/8EpdPdvfkdX tJQD12gCXtda+dxsRj4XGWwMo1/GvSMwTJn5tIQYSTMDXfNkxHkw4xy/Sj7+D7skIdVQ lAZw== MIME-Version: 1.0 X-Received: by 10.180.187.209 with SMTP id fu17mr4831803wic.52.1370873140717; Mon, 10 Jun 2013 07:05:40 -0700 (PDT) Received: by 10.194.172.201 with HTTP; Mon, 10 Jun 2013 07:05:40 -0700 (PDT) In-Reply-To: References: <458BA7AF19306B4FAA5FADDAD6D223353F02D507@BLUPRD0811MB401.namprd08.prod.outlook.com> Date: Mon, 10 Jun 2013 19:35:40 +0530 Message-ID: Subject: Re: Create table like with partitions From: Richa Sharma To: user@hive.apache.org Content-Type: multipart/alternative; boundary=001a11c269ac95ace404decd4638 X-Virus-Checked: Checked by ClamAV on apache.org --001a11c269ac95ace404decd4638 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable 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 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=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 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 =93CREATE TABLE copy L= IKE >> 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 partitione= d.* >> *** >> >> Sure I can add partitions if it=92s already partitioned but I don=92t se= em*** >> * >> >> to be able to make it partitioned if it=92s not already. I get errors li= ke >> this:**** >> >> ** ** >> >> hive> ALTER TABLE customerShortValues ADD PARTITION (aid=3D'1') LOCATION >> '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 al= l >> the**** >> >> column definitions. But is there an easier way?**** >> >> ** ** >> >> Z**** >> > > > > -- > Nitin Pawar > --001a11c269ac95ace404decd4638 Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: quoted-printable
Hi,
=A0
Can you please point to documentation on Dynamic partitioning.
=A0
I dont fully understand meaning of values for these parameters.
=A0
=A0
Regards
Richa
=A0
=A0
On Mon, Jun 10, 2013 at 7:08 PM, Nitin Pawar <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=20

Best approach would be, create a new table definition with the partiti= on 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: wait for others to add more suggestions. I may be very well wrong = in suggesting this=A0


On Mon, Jun 10, 2013 at 7:01 PM, Peter Marron <Peter.Marron@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



-= -
Nitin Pawar

--001a11c269ac95ace404decd4638--