Dropping multiple tables with same prefix in Hive

Question

I have few tables in hive that has same prefix like below..

temp_table_name
temp_table_add
temp_table_area

There are few hundreds of tables like this in my database along with many other tables. I want to delete tables that starts with "temp_table". Do any of you know any query that can do this work in Hive?

Answer 1

There is no such thing as regular expressions for drop query in hive (or i didn't find them). But there are multipe ways to do it, for example :

• With a shell script :

  hive -e "show tables 'temp_*'" | xargs -I '{}' hive -e 'drop table {}'
  

• Or by putting your tables in a specific database and dropping the whole database.

  Create table temp.table_name;
  
  Drop database temp cascade;