2

I have been trying to run this piece of code to drop current day's partition from hive a table and for some reason it does not drop the partition from the hive table. Not sure what's worng.

Table Name : prod_db.products

desc:
+----------------------------+-----------------------+-----------------------+--+
|          col_name          |       data_type       |        comment        |
+----------------------------+-----------------------+-----------------------+--+
| name                       | string                |                       |
| cost                       | double                |                       |
| load_date                  | string                |                       |
|                            | NULL                  | NULL                  |
| # Partition Information    | NULL                  | NULL                  |
| # col_name                 | data_type             | comment               |
|                            | NULL                  | NULL                  |
| load_date                  | string                |                       |
+----------------------------+-----------------------+-----------------------+--+

## I am using the following code

SET hivevar:current_date=current_date();

ALTER TABLE prod_db.products DROP PARTITION(load_date='${current_date}');


Before and After picture of partitions:
+-----------------------+--+
|       partition       |
+-----------------------+--+
| load_date=2022-04-07  |
| load_date=2022-04-11  |
| load_date=2022-04-18  |
| load_date=2022-04-25  |
+-----------------------+--+

It runs without any error but doesn't work but won't drop the partition. Table is internal/managed. I tried different ways mentioned on stack but it is just not working for me. Help.

trougc
  • 329
  • 3
  • 14

1 Answers1

1

You dont need to set a variable. You can directly drop using direct sql.

Alter table prod_db.products
drop partition (load_date= current_date());
Koushik Roy
  • 6,868
  • 2
  • 12
  • 33
  • @kaushik I had tried it and It gives me: Error: Error while compiling statement: FAILED: ParseException line 1:113 extraneous input '(' expecting ) near '' (state=42000,code=40000) – trougc Apr 27 '22 at 18:39
  • i am seeing same issue in my system. i think you can follow this answer https://stackoverflow.com/questions/46004534/dynamically-drop-partitions-in-hive-before-the-current-date – Koushik Roy Apr 28 '22 at 09:17