Does hive table name support wildcard?

user_4428471 0
Viewed 118

there are some tables in hive, with the same pattern like this: product_info_{xxx}_manual I want to get min and max datetime of these tables, now I use sql like below

select 'foo', min(dt), max(dt) from product_info_foo_manual
union
select 'bar', min(dt), max(dt) from product_info_bar_manual
union
select 'foobar', min(dt), max(dt) from product_info_foobar_manual
...

Does hive support wildcard could implement above requirement more easyly, e.g

select substring(table_name, x,y),min(dt), max(dt) from product_info_*_manual
hive
1 Answers

No, wildcards in table name is not supported in SELECT statement. Wildcard is supported in the show tables, so you can execute SHOW TABLES, get result in a shell and loop through it and parametrize your select query.

For example like this:

#!/bin/bash
for table_name in $(hive -e "use mydb; show tables 'product_info_*_manual';") 
do
   hive -S -e "use $db; select substring(${table_name}, x,y),min(dt), max(dt) from ${table_name};"
done
user_27003440
Related