Find list of databases/tables with specific pattern in Hive

Show databases like query in Hive

Show databases or Show schemas statement is lists all the database names in Hive metastore. In that statement, the optional LIKE clause allows the list of databases to be filtered using a regular expression.

Syntax

The pattern should be mentioned after the LIKE clause. The following wild card characters can be used to define the pattern.

asterisk (*) – Matches any single character or multiple characters
OR symbol (|) – Matches either the part of the pattern on the left or the right side of the pipe.

Example : show databases like in hive

Lets consider that we have databases related to ecommerce industry in Hive. In that, we want to find the list of databases which starts with product.

Next we will find list of databases which ends with payment.

We can use OR (|) symbol in the pattern to fetch the databases which starts or ends with specific pattern. Lets find the databases which starts with product or ends with payment in Hive

The asterisk(*) symbol can be used before and after the pattern to fetch the databases which contains the given string/pattern.

Show tables like query in Hive

Show tables statement is lists all the tables which are created in the specific Hive database. Here also we can use the pattern using Like clause.

Syntax

Example : Show tables like query

Consider that we have a database called ‘KC_University’ in Hive. In that, we want to find list of tables which contains the string as ‘student’;

As mentioned above, first we used ” use <database_name>” to enter the specific database. Then we have used the show tables like query to find the table names which contains the string as student.