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]

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 ~]#