CODEWITHSUNDEEP
hivehive-partitions
Surender Raja
Surender Raja

Reputation: 3599

How do I drop all partitions at once in hive?

Hive version 1.1

I have a hive external table as below:

 CREATE EXTERNAL TABLE `schedule_events`(
  `schedule_id` string COMMENT 'from deserializer',
  `service_key` string COMMENT 'from deserializer',
  `event_start_date_time` string COMMENT 'from deserializer',
  `event_id` string COMMENT 'from deserializer',
  `event_type` string COMMENT 'from deserializer',
  `transitional_key` string COMMENT 'from deserializer',
  `created_date_time` string COMMENT 'from deserializer',
  `bus_date` string COMMENT 'from deserializer')
    PARTITIONED BY (
                    `year` string,
                    `month` string,
                    `day` string)
   ROW FORMAT SERDE
   'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
   STORED AS INPUTFORMAT
   'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
   OUTPUTFORMAT
   'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'
   LOCATION
   'hdfs://nameservice1/hadoop/raw/omega/scheduled_events'
  TBLPROPERTIES (
    'avro.schema.url'='hdfs:////hadoop/raw/omega/schema/schedule_events.avsc',
   'transient_lastDdlTime'='1505742141')

Now to drop a particular partition I can run a ALTER command as below

 ALTER TABLE schedule_events DROP IF EXISTS PARTITION  (year='2016',month='06',day='01')
 Dropped the partition year=2016/month=06/day=01

 hive> show partitions schedule_events;
 OK
 year=2017/month=09/day=01
 year=2017/month=09/day=02
 year=2017/month=09/day=03
 year=2017/month=09/day=04
 year=2017/month=09/day=05

But this table is having many partitions.

How do I drop all existing partitions at once? I would like to delete all existing partitions at once? Is that possible?

Upvotes: 15

Views: 52983

Answers (5)

Hari_pb
Hari_pb

Reputation: 7416

For example : suppose partitions are on date and the name is partition_column:-

alter table database.table_name drop if exists partition (partition_column>'2023-01-01');

Upvotes: 0

Venkatesh Surya Goli
Venkatesh Surya Goli

Reputation: 89

Using spark sql:

val paritions_values = spark.sql("show partitions "+databasename+'.'+tablename)
.collect().map(f=>f(0).toString)
.toArray.mkString("partition(", "," , "\")")
.replace("," , "\") ,partition(")
.replace("=", "=\"")

spark.sql("alter table "+databasename+'.'+tablename+" drop "+paritions_values)

Upvotes: 1

J_V
J_V

Reputation: 397

alter table schema_name.table_name drop partition (partition_column != '');

Upvotes: 1

Ani Menon
Ani Menon

Reputation: 28219

You may use something similar to this:

ALTER TABLE schedule_events drop if exists partition (year>'0');

Upvotes: 13

David דודו Markovitz
David דודו Markovitz

Reputation: 44951

There are multiple options, here is one:

alter table schedule_events drop if exists partition (year<>'');

Hive: Extend ALTER TABLE DROP PARTITION syntax to use all comparators

"... To drop a partition from a Hive table, this works:
ALTER TABLE foo DROP PARTITION(ds = 'date')
...but it should also work to drop all partitions prior to date.
ALTER TABLE foo DROP PARTITION(ds < 'date') This task is to implement ALTER TABLE DROP PARTITION for all of the comparators, < > <= >= <> = != instead of just for ="

https://issues.apache.org/jira/browse/HIVE-2908

Upvotes: 28

Related Questions