Mailing-List: contact hive-user-help@hadoop.apache.org; run by ezmlm
Precedence: bulk
Reply-To: hive-user@hadoop.apache.org
Received-SPF: pass (nike.apache.org: domain of nzhang@facebook.com designates
 69.63.178.184 as permitted sender)
From: Ning Zhang <nzhang@facebook.com>
To: "hive-user@hadoop.apache.org" <hive-user@hadoop.apache.org>
Date: Wed, 16 Jun 2010 23:49:33 -0700
Subject: Re: Creating partitions causes Error in semantic analysis
Thread-Topic: Creating partitions causes Error in semantic analysis
Thread-Index: AcsN6RvEBZKr2TutQZuVMYchSY+cJA==
Message-ID: <266EA6AB-BF84-495F-990E-27A1BBF89B39@facebook.com>
References: 
 <F632207F06AD2A4CA6CCEE0E1ACBE97F0613B70B@SNV-EXVS06.ds.corp.yahoo.com>
 <AANLkTinRFagjwOIQUA3AFGo5jbISJOo9TKbXaocfxj1V@mail.gmail.com>
 <F632207F06AD2A4CA6CCEE0E1ACBE97F0613B757@SNV-EXVS06.ds.corp.yahoo.com>
In-Reply-To: 
 <F632207F06AD2A4CA6CCEE0E1ACBE97F0613B757@SNV-EXVS06.ds.corp.yahoo.com>
Accept-Language: en-US
Content-Language: en-US
acceptlanguage: en-US
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0

The __HIVE_DEFAULT_PARTITION__ is created by default if the partitioning co=
lumn value (newdatestamp etc.) is NULL or empty string. Below is the wiki p=
age that describes the syntax and semantics of dynamic partitioning, includ=
ing some best practices.=20

http://wiki.apache.org/hadoop/Hive/Tutorial#Dynamic-partition_Insert

Ning

On Jun 16, 2010, at 11:11 PM, Viraj Bhat wrote:

> Hi Yongqiang,
> I am using the trunk code. I figured out what the problem was=20
> INSERT OVERWRITE TABLE newtable
> PARTITION (newdatestamp, myregion, myproperty)
> SELECT
> name,
> age,
> datestamp as newdatestamp,
> region as myregion,
> property as myproperty,
> from oldtable where datestamp=3D'20100525';
>=20
> I need to specify the last 3 columns in the order of partitions, which I =
did not.=20
>=20
> Meanwhile the dynamic partitioning produced a partition which was named "=
__HIVE_DEFAULT_PARTITION__". Is this created by default?=20
>=20
> Thanks again for your help.
> Viraj
>=20
> -----Original Message-----
> From: yongqiang he [mailto:heyongqiangict@gmail.com]=20
> Sent: Wednesday, June 16, 2010 5:46 PM
> To: hive-user@hadoop.apache.org
> Subject: Re: Creating partitions causes Error in semantic analysis
>=20
> Hive supports dynamic partition ( i think you need to use trunk code
> for this feature.?).
>=20
> here is an example:
>=20
> set hive.exec.dynamic.partition.mode=3Dnonstrict;
> set hive.exec.dynamic.partition=3Dtrue;
>=20
> create table if not exists nzhang_part1 like srcpart;
> create table if not exists nzhang_part2 like srcpart;
> describe extended nzhang_part1;
>=20
> from srcpart
> insert overwrite table nzhang_part1 partition (ds, hr) select key,
> value, ds, hr where ds <=3D '2008-04-08'
> insert overwrite table nzhang_part2 partition(ds=3D'2008-12-31', hr)
> select key, value, hr where ds > '2008-04-08';
>=20
> On Wed, Jun 16, 2010 at 4:07 PM, Viraj Bhat <viraj@yahoo-inc.com> wrote:
>> Hi all,
>>=20
>>   I have a table known as "oldtable" which is partitioned by datestamp.
>>=20
>>=20
>>=20
>> The schema of the "oldtable" is:
>>=20
>>=20
>>=20
>> name string
>>=20
>> age bigint
>>=20
>> property string
>>=20
>> region string
>>=20
>> datestamp string
>>=20
>>=20
>>=20
>>=20
>>=20
>> I now need to create a new table which is based of this old table and
>> partitioned by (datestamp, region, property)
>>=20
>>=20
>>=20
>> The DDL for the new table looks like:
>>=20
>>=20
>>=20
>> CREATE EXTERNAL TABLE newtable
>>=20
>> (
>>=20
>> newname string,
>>=20
>> newage bigint,
>>=20
>> )
>>=20
>>=20
>>=20
>> PARTITIONED BY (newdatestamp STRING, myregion STRING, myproperty STRING)
>>=20
>>=20
>>=20
>> STORED AS RCFILE
>>=20
>> LOCATION '/user/viraj/rcfile;
>>=20
>>=20
>>=20
>>=20
>>=20
>> When I try to populate this new table from my old table, I try to use
>> partitioning which uses values of old columns.
>>=20
>>=20
>>=20
>> INSERT OVERWRITE TABLE newtable
>>=20
>> PARTITION (newdatestamp=3D'20100525', region, property)
>>=20
>> SELECT
>>=20
>>   name,
>>=20
>>   age
>>=20
>> from oldtable where datestamp=3D'20100525';
>>=20
>>=20
>>=20
>> The above statement causes an error and expects hardcoded values for reg=
ion
>> and property.
>>=20
>>=20
>>=20
>> FAILED: Error in semantic analysis: Partition column in the partition
>> specification does not exist.
>>=20
>>=20
>>=20
>> How do I specify the partition information such that the new tables, tak=
es
>> values from "property" and "region" from the old table and uses it as
>> partitions.
>>=20
>>=20
>>=20
>> Is there a better way to achieve the above instead of hard coding values=
 for
>> each and every partition?
>>=20
>>=20
>>=20
>> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D
>>=20
>> Addendum: If the above is possible, how can I define some conditions whe=
re I
>> need to say, If region is not "us" or "asia", put it in another partitio=
n
>> known as misc?
>>=20
>> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D
>>=20
>>=20
>>=20
>>=20
>>=20
>> Thanks Viraj