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?

score 22 · Accepted Answer · edited Mar 08 '18 at 20:42

22

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;
```

edited Mar 08 '18 at 20:42

Nicolás Ozimica

9,481
5
38
51

answered Mar 10 '16 at 16:54

Tremo

589
6
19

1

hive -e ‘show tables 'temp_*’ | xargs -I ‘{}’ hive -e ‘drop table {}’ is not working for me – MysticForce Jun 26 '16 at 17:02
2

The answer is perfect however there is a small change that is needed which actually worked for me. I am modifying @Louxou 's code a bit by adding double quotes. Here is the updated code. `hive -e "show tables 'temp_*'" | xargs -I '{}' hive -e 'drop table {}'` – Alex Raj Kaliamoorthy Sep 26 '17 at 06:34
i want to add something to this. The problem now is that hive ( or beeline ) tends to show the results in "graphical" format. Also if you have multiple DB in hive you need to specify the one you need `beeline --showHeader=false --outputformat=csv2 -e "use ; show tables 'temp_*'" | xargs -I '{}' beeline -e 'use; drop table {}'` – habarnam Jan 13 '22 at 07:00

score 9 · Answer 2 · edited Oct 23 '18 at 01:07

9

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.

edited Oct 23 '18 at 01:07

Community

1
1

answered Sep 01 '17 at 19:58

Chandra

526
2
9
26

Where do you mention the match table pattern here? – Alex Raj Kaliamoorthy Sep 25 '17 at 13:24
1

@Alex Raj Kaliamoorthy - grep 'pattern' – Chandra Sep 26 '17 at 20:43
Note that there is space after "drop table", like this: `drop table `. – Autonomous Oct 24 '18 at 21:26

score 5 · Answer 3 · answered Oct 18 '16 at 15:47

5

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

answered Oct 18 '16 at 15:47

HorusH

231
1
5
14

score 4 · Answer 4 · edited Mar 01 '19 at 12:28

4

I was able to delete all tables using following steps in Apache Spark with Scala:

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}"))

edited Mar 01 '19 at 12:28

Oleg Svechkarenko

2,508
25
30

answered Mar 13 '18 at 16:38

user1977823

39
7

dirceusemighini · Answer 5 · 2017-06-22T17:17:25.510

2

As I had a lot of tables do drop I used the following command, inspired in the @HorusH 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

edited Jun 22 '17 at 17:17

answered Jun 22 '17 at 15:22

dirceusemighini

1,344
2
16
35

score 0 · Answer 6 · answered Aug 13 '16 at 02:57

0

Try this:

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

answered Aug 13 '16 at 02:57

Ganesh B

1

score 0 · Answer 7 · answered Mar 30 '17 at 20:01

0

Below command will also work.

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

answered Mar 30 '17 at 20:01

kamoor

2,909
2
19
34

score 0 · Answer 8 · answered Jan 23 '18 at 23:53

0

fastest solution through one shell script:

drop_tables.sh pattern

Shell script content:

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

answered Jan 23 '18 at 23:53

user2359902

121
1
7

Dropping multiple tables with same prefix in Hive

8 Answers8