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

user_3583824 0
Viewed 7601

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.

hive hadoop
3 Answers

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.

user_1509920

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*
user_51523050

If the partition value is known, the output can be restricted using the below syntax.

show partitions <>.<<Hive_Table> partition (<<field_name>> = <<partition_value>>')

Ex: show partitions db.tablename partition (processed_dt ='202101')

user_80306300
Related