how to drop partition table using date_add function

[#1]

using command
ALTER TABLE test table DROP PARTITION (START_DATE = DATE_ADD('2023-09-13', -13) )

error: SQL Error [500051] [HY000]: [Cloudera][HiveJDBCDriver](500051) ERROR processing query/statement. Error Code: 40000, SQL state: TStatus(statusCode:ERROR_STATUS, info Messages:[*org.apache.hive.service.cli.HiveSQLException:Error while compiling statement: FAILED: ParseException line 1:78 cannot recognize input near 'DATE_ADD' '(' ''2023-09-13'' in constant:17:16

Need Result : '2023-08-31'

[#2]

@yangdkny Welcome to our community! To help you get the best possible answer, I have tagged in our Hive experts  @ggangadharan @udeshmukh who may be able to assist you further.

Please feel free to provide any additional information or details about your query, and we hope that you will find a satisfactory solution to your question.

[#3]

@yangdkny 

Hive's ALTER TABLE DROP PARTITION statement doesn't directly accept DATE_ADD or similar functions inside the partition specification. Hive expects a static date value (e.g., 'YYYY-MM-DD') in the DROP PARTITION statement, not a function call.

As an alternative, You can create an HQL script and in that

• Calculate the Date Using DATE_ADD and Store it in a Variable:
• Use the Variable in the ALTER TABLE Statement:

[#4 · Accepted solution]

As mentioned in the previous comment , it's advisable to use the static value. 

Example - 

CREATE TABLE test_table (
    id INT,
    name STRING
)
PARTITIONED BY (START_DATE STRING);

-- Add sample partitions for testing
ALTER TABLE test_table ADD PARTITION (START_DATE = '2023-09-01');
ALTER TABLE test_table ADD PARTITION (START_DATE = '2023-08-31'); -- This should be the target partition to drop
ALTER TABLE test_table ADD PARTITION (START_DATE = '2023-08-30');

compute the date in the shell and pass it to Hive.

# Set the base date and calculate the target date (13 days before the base date)
base_date="2023-09-13"
partition_date=$(date -d "$base_date - 13 days" +%Y-%m-%d)

# Log the computed date (optional)
echo "Dropping partition for date: $partition_date"

# Execute the Hive command to drop the partition
beeline -n hive -p hive -e "ALTER TABLE test_table DROP PARTITION (START_DATE = '$partition_date');show partitions test_table;"

Sample Run 

[root@ccycloud-2.blesc-15238.root.comops.site ~]# # Set the base date and calculate the target date (13 days before the base date)
[root@ccycloud-2.blesc-15238.root.comops.site ~]# base_date="2023-09-13"
[root@ccycloud-2.blesc-15238.root.comops.site ~]# partition_date=$(date -d "$base_date - 13 days" +%Y-%m-%d)
[root@ccycloud-2.blesc-15238.root.comops.site ~]#
[root@ccycloud-2.blesc-15238.root.comops.site ~]# # Log the computed date (optional)
[root@ccycloud-2.blesc-15238.root.comops.site ~]# echo "Dropping partition for date: $partition_date"
Dropping partition for date: 2023-08-31
[root@ccycloud-2.blesc-15238.root.comops.site ~]#
[root@ccycloud-2.blesc-15238.root.comops.site ~]# # Execute the Hive command to drop the partition
[root@ccycloud-2.blesc-15238.root.comops.site ~]# beeline -n hive -p hive -e "ALTER TABLE test_table DROP PARTITION (START_DATE = '$partition_date');show partitions test_table;"
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/cloudera/parcels/CDH-7.1.9-1.cdh7.1.9.p9.52289703/jars/log4j-slf4j-impl-2.18.0.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/cloudera/parcels/CDH-7.1.9-1.cdh7.1.9.p9.52289703/jars/slf4j-reload4j-1.7.36.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
WARNING: Use "yarn jar" to launch YARN applications.
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/cloudera/parcels/CDH-7.1.9-1.cdh7.1.9.p9.52289703/jars/log4j-slf4j-impl-2.18.0.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/cloudera/parcels/CDH-7.1.9-1.cdh7.1.9.p9.52289703/jars/slf4j-reload4j-1.7.36.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Connecting to jdbc:hive2://ccycloud-2.blesc-15238.root.comops.site:2181/default;password=hive;principal=hive/_HOST@ROOT.COMOPS.SITE;serviceDiscoveryMode=zooKeeper;user=hive;zooKeeperNamespace=hiveserver2
24/11/13 15:08:10 [main]: INFO jdbc.HiveConnection: Connected to ccycloud-2.blesc-15238.root.comops.site:10000
Connected to: Apache Hive (version 3.1.3000.7.1.9.9-1)
Driver: Hive JDBC (version 3.1.3000.7.1.9.9-1)
Transaction isolation: TRANSACTION_REPEATABLE_READ
INFO  : Compiling command(queryId=hive_20241113150811_5d177453-0f69-4fee-baa3-9d2a6e82c828): ALTER TABLE test_table DROP PARTITION (START_DATE = '2023-08-31')
INFO  : Semantic Analysis Completed (retrial = false)
INFO  : Created Hive schema: Schema(fieldSchemas:null, properties:null)
INFO  : Completed compiling command(queryId=hive_20241113150811_5d177453-0f69-4fee-baa3-9d2a6e82c828); Time taken: 1.076 seconds
INFO  : Executing command(queryId=hive_20241113150811_5d177453-0f69-4fee-baa3-9d2a6e82c828): ALTER TABLE test_table DROP PARTITION (START_DATE = '2023-08-31')
INFO  : Starting task [Stage-0:DDL] in serial mode
INFO  : Dropped the partition start_date=2023-08-31
INFO  : Completed executing command(queryId=hive_20241113150811_5d177453-0f69-4fee-baa3-9d2a6e82c828); Time taken: 0.251 seconds
INFO  : OK
No rows affected (1.417 seconds)
INFO  : Compiling command(queryId=hive_20241113150812_da9db0ea-d18d-4016-9a3f-3da3535caf13): show partitions test_table
INFO  : Semantic Analysis Completed (retrial = false)
INFO  : Created Hive schema: Schema(fieldSchemas:[FieldSchema(name:partition, type:string, comment:from deserializer)], properties:null)
INFO  : Completed compiling command(queryId=hive_20241113150812_da9db0ea-d18d-4016-9a3f-3da3535caf13); Time taken: 0.039 seconds
INFO  : Executing command(queryId=hive_20241113150812_da9db0ea-d18d-4016-9a3f-3da3535caf13): show partitions test_table
INFO  : Starting task [Stage-0:DDL] in serial mode
INFO  : Completed executing command(queryId=hive_20241113150812_da9db0ea-d18d-4016-9a3f-3da3535caf13); Time taken: 0.053 seconds
INFO  : OK
+------------------------+
|       partition        |
+------------------------+
| start_date=2023-08-30  |
| start_date=2023-09-01  |
+------------------------+
2 rows selected (0.185 seconds)
Beeline version 3.1.3000.7.1.9.9-1 by Apache Hive
Closing: 0: jdbc:hive2://ccycloud-2.blesc-15238.root.comops.site:2181/default;password=hive;principal=hive/_HOST@ROOT.COMOPS.SITE;serviceDiscoveryMode=zooKeeper;user=hive;zooKeeperNamespace=hiveserver2
[root@ccycloud-2.blesc-15238.root.comops.site ~]#

[#5]

@yangdkny, Did the response help resolve your query? If it did, kindly mark the relevant reply as the solution, as it will aid others in locating the answer more easily in the future.