Yes "date" is a reserved word. My recommendations
If your table is Hive managed (I.e u created the table without using EXTERNAL ) ======================= - Then copy the data for this hive table that is on HDFS to another location - Drop the table - CREATE a EXTERNAL TABLE with filename - replace date with some field name like "date_" - ALTER TABLE ADD IF NOT EXISTS PARTITION (date_='<your_partition>') LOCATION '/path/to/your/HDFS/where/you/copied/the/data' If your table is EXTERNAL ======================= - Drop the table - CREATE a EXTERNAL TABLE with filename - replace date with some field name like "date_" - ALTER TABLE ADD IF NOT EXISTS PARTITION (date_='<your_partition>') LOCATION '/path/to/your/HDFS/where/you/copied/the/data' Hope this helps Good luck sanjay From: Sonya Ling <[email protected]<mailto:[email protected]>> Reply-To: "[email protected]<mailto:[email protected]>" <[email protected]<mailto:[email protected]>>, Sonya Ling <[email protected]<mailto:[email protected]>> Date: Thursday, August 22, 2013 6:45 PM To: "[email protected]<mailto:[email protected]>" <[email protected]<mailto:[email protected]>> Subject: Alter or Query a table with field name 'date' always get error Hi: I have a table transaction3 with a field name 'date'. That table is the target of importing table from MYSQL using Sqoop. The table in MYSQL has a field with the field name 'date' and SQOOP does not allow column name mapping. Therefore, the field name 'date' is kept in the transaction3 table in Hive. 'date' field is the partition field name of transaction3. I tried to change column name (existing code is is using created_date field) ALTER TABLE transaction3 CHANGE date created_date STRING; I got error. FAILED: ParseException line 1:32 extraneous input 'date' expecting Identifier near '<EOF>' I cannot get around it. I query the count SELECT COUNT(*) FROM transaction3 WHERE date = '2013-08-15'; I got another error. FAILED: ParseException line 1:40 cannot recognize input near 'date' '=' ''2013-08-15'' in expression specification I query another table with field name 'created_date' instead and it works fine and I didn't get any error. I like to know if date is a reserved word. Is there any way to get around either change field name or query? Thanks. CONFIDENTIALITY NOTICE ====================== This email message and any attachments are for the exclusive use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message along with any attachments, from your computer system. If you are the intended recipient, please be advised that the content of this message is subject to access, review and disclosure by the sender's Email System Administrator.
