how to drop partition table using date_add function

yangdkny — Tue, 21 Apr 2026 06:23:48 GMT

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'

Re: how to drop partition table using date_add function

VidyaSargur — Wed, 13 Nov 2024 06:17:06 GMT

@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.

Re: how to drop partition table using date_add function

udeshmukh — Wed, 13 Nov 2024 13:40:08 GMT

@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:

Re: how to drop partition table using date_add function

ggangadharan — Wed, 13 Nov 2024 15:12:10 GMT

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

Re: how to drop partition table using date_add function

VidyaSargur — Thu, 21 Nov 2024 07:29:43 GMT

@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.

question Re: how to drop partition table using date_add function in Support Questions

how to drop partition table using date_add function

Re: how to drop partition table using date_add function

Re: how to drop partition table using date_add function

Re: how to drop partition table using date_add function

Re: how to drop partition table using date_add function