Partitions are still showing in hive even though they are dropped for an external table

0
votes

I have an external table in hive partitioned by year, month, day. So I dropped one partition but I still see it in show partitions.

>use test_raw_tables;
>show partitions test1_raw;
[year=2016/month=01/day=01]
[year=2017/month=03/day=24]

> alter table test1_raw drop partition (year=2016, month=01, day=01);
> refresh test1_raw;
> show partitions test1_raw;
[year=2016/month=01/day=01]
[year=2017/month=03/day=24]     ---Still see the dropped partition here----

> msck repair table test1_raw;
> show partitions test1_raw;
[year=2016/month=01/day=01]
[year=2017/month=03/day=24]    ---Still see the dropped partition here----

Running from impala with hive as engine.

describe test1_raw col_name,data_type,comment ('amount_hold', 'int', '') ('id', 'int', '') ('transaction_id', 'string', '') ('recipient_id', 'string', '') ('year', 'string', '') ('month', 'string', '') ('day', 'string', '') ('', None, None) ('# Partition Information', None, None) ('# col_name ', 'data_type ', 'comment ') ('', None, None) ('year', 'string', '') ('month', 'string', '') ('day', 'string', '') location 'hdfs://localhost/sys/datalake/testing/test1_raw'

What is the problem here? The data in hdfs is deleted for that partition after dropping it. Cannot figure out the issue.

1
hadoophivehiveqlimpala
(1) please add the table definition (2) from where are you running your code? impala-shell? hive? beeline? – David דודו Markovitz
@DuduMarkovitz Table is an external table. Running the query from hive shell. – Andy Reddy
refresh is as Impala command, not hive. I need to know what are you executing from where as well as the table definition, including the relevant partitions definition. Please also show me how their paths looks like in the file system. – David דודו Markovitz
Please Add this to the post as well as the paths of the relevant partitions – David דודו Markovitz

1 Answers

1
votes

In your Table defination Column Year, Month and Day are in String format. Pls try with '2016', '01' and '01'. I used below code and it works.

alter table test1_raw drop partition (year='2016', month='01', day='01');