Deleting multiple tables with the same prefix in Hive

Question

I have several tables in the hive that have the same prefix as below ..

temp_table_name temp_table_add temp_table_area 

There are several hundred tables in my database, as well as many other tables. I want to delete tables starting with "temp_table". Do any of you know any query that can do this job in Hive?

Answer 1

There is no such thing as regular expressions for the drop request in the bush (or I did not find them). But there are several ways to do this, for example:

• With shell script:

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

• Or by placing your tables in a specific database and deleting the entire database.

   Create table temp.table_name; Drop database temp cascade; 

Answer 2

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

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

use the vim editor to open the .hql file and execute the commands below.

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

then run

hive -f file.hql

This approach will be much faster.

Answer 3

My solution was to use a 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 

Answer 4

Since I had many tables, I used the following command inspired by @HorusH's answer

 hive -e "show tables 'table_prefix*'" | sed -e 's/^/ \DROP TABLE db_name\./1' | sed -e 's/$/;/1' > script.sh hive -f script.sh 

Answer 5

I was able to delete all the tables by following these steps

 val df = sql("SHOW TABLES IN default LIke 'invoice*'").select("tableName") // to drop only selected column val df = sql("SHOW TABLES IN default").select("tableName") val tableNameList: List[String] = df.as[String].collect().toList val df2 = tableNameList.map(tableName => sql(s"drop table ${tableName}")) 

Answer 6

Try the following:

hive -e 'use sample_db; show tables' | xargs -I '{}' hive -e 'use sample_db; drop table {} '

Answer 7

The team below will also work.

  hive -e 'show tables' | grep table_prefix | while read line; do hive -e "drop table $line"; done 

Answer 8

fastest solution through one shell script:

drop_tables.sh pattern

The contents of the shell script:

 hive -e 'use db;show tables' | grep $1 | sed 's/^/drop table db./' | sed 's/$/;/' > temp.hql hive -f temp.hql rm temp.hql