Hi John, Yes the problem was that in the select clause I needed to define the partition columns in exactly the similar order. INSERT OVERWRITE TABLE newtable PARTITION (newdatestamp, myregion, myproperty) SELECT name, age, datestamp as newdatestamp, region as myregion, property as myproperty from oldtable where datestamp='20100525';
I will put in the CASE statement to obtain the necessary partition information. Is __HIVE_DEFAULT_PARTITION__ created by default? Thanks again for your help. Viraj -----Original Message----- From: John Sichi [mailto:[email protected]] Sent: Wednesday, June 16, 2010 8:03 PM To: [email protected] Subject: RE: Creating partitions causes Error in semantic analysis 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
