- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Float this Question for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
Delete partition Hive error
- Labels:
-
Apache Hive
Created 02-20-2022 11:48 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi guys, i am trying some hive partitions but i am getting the following errors. kindly help figure out the problem.
0: jdbc:hive2://svdt5hadoopms02.bigdatadev.sa> ALTER TABLE customer_transaction_extract DROP IF EXISTS PARTITION (date='date=2019-11-13') PURGE;
Error: Error while compiling statement: FAILED: ParseException line 1:67 cannot recognize input near 'date' '=' ''date=2019-11-13'' in drop partition statement (state=42000,code=40000)
0: jdbc:hive2://svdt5hadoopms02.bigdatadev.sa> ALTER TABLE customer_transaction_extract DROP IF EXISTS PARTITION (date='date="2019-11-13"') PURGE;
Error: Error while compiling statement: FAILED: ParseException line 1:67 cannot recognize input near 'date' '=' ''date="2019-11-13"'' in drop partition statement (state=42000,code=40000)
0: jdbc:hive2://svdt5hadoopms02.bigdatadev.sa> ALTER TABLE customer_transaction_extract DROP IF EXISTS PARTITION (date=`2019-11-13`) PURGE;
Error: Error while compiling statement: FAILED: ParseException line 1:67 cannot recognize input near 'date' '=' '2019-11-13' in drop partition statement (state=42000,code=40000)
0: jdbc:hive2://svdt5hadoopms02.bigdatadev.sa> ALTER TABLE customer_transaction_extract DROP IF EXISTS PARTITION (date >='1648-10-03', date <='2022-02-15') ;
Error: Error while compiling statement: FAILED: ParseException line 1:67 cannot recognize input near 'date' '>=' ''1648-10-03'' in drop partition statement (state=42000,code=40000)
0: jdbc:hive2://svdt5hadoopms02.bigdatadev.sa> ALTER TABLE customer_transaction_extract DROP IF EXISTS PARTITION (date >='1648-10-03');
Error: Error while compiling statement: FAILED: ParseException line 1:67 cannot recognize input near 'date' '>=' ''1648-10-03'' in drop partition statement (state=42000,code=40000)
0: jdbc:hive2://svdt5hadoopms02.bigdatadev.sa> ALTER TABLE customer_transaction_extract DROP PARTITION (date >='1644948900', date <='1644949001');
Error: Error while compiling statement: FAILED: ParseException line 1:57 cannot recognize input near 'date' '>=' ''1644948900'' in drop partition statement (state=42000,code=40000)
0: jdbc:hive2://svdt5hadoopms02.bigdatadev.sa> ALTER TABLE customer_transaction_extract DROP PARTITION (date >='1644948900', date <='1644949001');
Error: Error while compiling statement: FAILED: ParseException line 1:57 cannot recognize input near 'date' '>=' ''164494
Created 02-22-2022 04:57 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Jmeks ,Please check again how the table has been created or how those partitions were created ("describe formatted <table> partition <partspec>"), as the same is still working for me even on HDP 3.1.0:
create table mdatetest (col1 string) partitioned by (`date` date) location '/tmp/md';
alter table mdatetest add partition (`date`="2022-02-22");
show partitions mdatetest;
+------------------+
| partition |
+------------------+
| date=2022-02-22 |
+------------------+
alter table mdatetest drop partition (`date`="2022-02-22");
Created 02-21-2022 02:38 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Jmeks ,
I assume the partitioning column for this table is literally named as "date". Please note that "date" is a reserved word in Hive (see the docs for reference), so it is not recommended to use it in table names and column identifiers. If you really need to use it as an identifier, then encapsulate it within backticks. For example:
ALTER TABLE customer_transaction_extract DROP IF EXISTS PARTITION (`date`="2019-11-13");
Hope this helps.
Best regards, Miklos
Created 02-21-2022 04:17 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Miklos,
I appreciate your help but using your code still throws the following error:
| date=2019-11-13 |
| date=2022-02-15 |
| date=2022-02-16 |
| date=2022-02-17 |
| date=2022-02-19 |
| date=2022-02-20 |
| date=__HIVE_DEFAULT_PARTITION__ |
+----------------------------------+
790 rows selected (0.284 seconds)
0: jdbc:hive2://svdt5hadoopms02.bigdatadev.sa> ALTER TABLE customer_transaction_extract DROP IF EXISTS PARTITION (`date`="2019-11-13");
Error: Error while compiling statement: FAILED: SemanticException Unexpected unknown partitions for (date = DATE'2019-11-13') (state=42000,code=40000)
Created 02-21-2022 07:50 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Jmeks ,
Can you clarify which CDH/HDP/CDP version do you have?
What is the datatype of that "date" partitioning column?
The mentioned syntax works for both string and date datatypes in CDH 6.x.
Created 02-22-2022 03:17 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi mszurap,
i use HDP-3.1.0.0. the data type for the `date` partitioning column is date
Created 02-22-2022 04:57 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Jmeks ,Please check again how the table has been created or how those partitions were created ("describe formatted <table> partition <partspec>"), as the same is still working for me even on HDP 3.1.0:
create table mdatetest (col1 string) partitioned by (`date` date) location '/tmp/md';
alter table mdatetest add partition (`date`="2022-02-22");
show partitions mdatetest;
+------------------+
| partition |
+------------------+
| date=2022-02-22 |
+------------------+
alter table mdatetest drop partition (`date`="2022-02-22");
Created 02-24-2022 02:17 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Many Thanks. I finally managed to delete the partitions using your code
