Hadoop hive(beeline) drop multiple tables with same prefix

other key words: drop table with wildcard

Solution in one command:

beeline --outputformat=tsv2 --showHeader=false -u "<JDBC URL>" -e "SHOW TABLES 'hans*';" | xargs -I '{}' beeline -u "<JDBC URL>" -e 'DROP TABLE IF EXISTS {}'

Solution in script(Option):

> beeline --outputformat=tsv2 --showHeader=false -u "<JDBC URL>" -e "SHOW TABLES 'hans*';"> output
> sed -i -e 's/.*/DROP TABLE IF EXISTS&;/' output
> beeline -u "<JDBC URL>" -f output

Test:

[Create table]

beeline -u "<JDBC URL>" -e "CREATE TABLE IF NOT EXISTS hanstable1 (id bigint);"
beeline -u "<JDBC URL>" -e "CREATE TABLE IF NOT EXISTS hanstable2 (id bigint);"

[Show table]

> beeline -u "<JDBC URL>" -e "SHOW TABLES 'hans*';"
...
+-------------+--+
|  tab_name   |
+-------------+--+
| hanstable1  |
| hanstable2  |
+-------------+--+

However, above result can not pipe to next stage directly, because default output format includes tablebar and header. You can add following options:--outputformat=tsv2 --showHeader=false to get table name only.

> beeline --outputformat=tsv2 --showHeader=false -u "<JDBC URL>" -e "SHOW TABLES 'hans*';"
...
hanstable1
hanstable2

[Drop table]

> beeline --outputformat=tsv2 --showHeader=false -u "<JDBC URL>" -e "SHOW TABLES 'hans*';" | xargs -I '{}' beeline -u "<JDBC URL>" -e 'DROP TABLE IF EXISTS {}'
...
INFO  : OK

[Check it out]

> beeline --outputformat=tsv2 --showHeader=false -u "<JDBC URL>" -e "SHOW TABLES 'hans*';"
...
No rows selected (0.392 seconds)