I think you should be using myregion and myproperty in the PARTITION clause (not region and property).
Also, regarding your question in the addendum, you could use a CASE expression in the source SELECT statement to transform the region. JVS ________________________________________ From: Viraj Bhat [[email protected]] Sent: Wednesday, June 16, 2010 4:07 PM To: [email protected] Subject: Creating partitions causes Error in semantic analysis Hi all, I have a table known as “oldtable” which is partitioned by datestamp. The schema of the “oldtable” is: name string age bigint property string region string datestamp string I now need to create a new table which is based of this old table and partitioned by (datestamp, region, property) The DDL for the new table looks like: CREATE EXTERNAL TABLE newtable ( newname string, newage bigint, ) PARTITIONED BY (newdatestamp STRING, myregion STRING, myproperty STRING) STORED AS RCFILE LOCATION '/user/viraj/rcfile; When I try to populate this new table from my old table, I try to use partitioning which uses values of old columns. INSERT OVERWRITE TABLE newtable PARTITION (newdatestamp='20100525', region, property) SELECT name, age from oldtable where datestamp='20100525'; The above statement causes an error and expects hardcoded values for region and property. FAILED: Error in semantic analysis: Partition column in the partition specification does not exist. How do I specify the partition information such that the new tables, takes values from “property” and “region” from the old table and uses it as partitions. Is there a better way to achieve the above instead of hard coding values for each and every partition? ======================================================= Addendum: If the above is possible, how can I define some conditions where I need to say, If region is not “us” or “asia”, put it in another partition known as misc? ======================================================= Thanks Viraj
