How to use filter conditions on SHOW PARTITIONS clause on hive?

3
votes

I am having hive table which is partitioned by date, app_name, src (3 partitions)

I want to fire show partitions command in multiple ways like following

// works
show partitions mydb.tab_dt partition(date='2017-05-01');

// works
show partitions mydb.tab_dt partition(date='2017-05-01'/app_name='app_io'/src='mobile');

When I need to do some filtering in partition clause i am not able to make it work. examples of some of the things I am trying are below.

// doesn't work
show partitions mydb.tab_dt partition(date>='2017-05-01');

// doesn't work
show partitions mydb.tab_dt partition(date='2017-05-01'/app_name like '%app%');

// doesn't work
show partitions mydb.tab_dt partition(app_name like '%app%'/src='mobile');

I hope I have made my question clear.

2
hadoophive

2 Answers

1
votes

It appears that this is not currently possible, but will be after Hive 4.0.0.

The current possibilities are described in the documentation here.

However, what I ended up doing was running SHOW PARTITIONS with hive -e and using grep to filter the results afterwards.

0
votes

To find specific partition you can use hive shell to get the partitions and then extract the specific partition by using grep. This worked well for me.

 hive -e 'show partitions db.tablename;' | grep 202101*

 hive -e 'show partitions db.tablename partition (type='abc');' | grep 202101*