CODEWITHSUNDEEP
hadoophivehqlhiveqlhadoop-partitioning
trougc
trougc

Reputation: 405

How to drop hive partitions with hivevar passed as partition variable?

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.

Upvotes: 2

Views: 382

Answers (1)

Koushik Roy
Koushik Roy

Reputation: 7387

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());

Upvotes: 1

Related Questions