Dropping multiple tables with same prefix in Hive

My solution has been to use bash script with the following cmd:

hive -e "SHOW TABLES IN db LIKE 'schema*';" | grep "schema" | sed -e 's/^/hive -e \"DROP TABLE db\./1' | sed -e 's/$/\"/1' > script.sh
chmod +x script.sh
./script.sh

Above solutions are good. But if you have more tables to delete, then running 'hive -e drop table' is slow. So, I used this:

hive -e 'use db;show tables' | grep pattern > file.hql

use vim editor to open file.hql and run below commands

:%s!^!drop table  
:%s!$!;

then run

hive -f file.hql

This approach will be much faster.

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;