Find list of databases/tables with specific pattern in Hive

Posted on by

Contents

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

SHOW (DATABASES|SCHEMAS) [LIKE identifier_with_wildcards];

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.

hive> show databases like 'product*';
OK
product_details
product_availability
product_comments
product_ratings
product_sales_history
Time taken: 0.016 seconds, Fetched: 5 row(s)

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

hive> show databases like '*payment';
OK
direct_payment
gift_card_payment
online_payment
Time taken: 0.016 seconds, Fetched: 3 row(s)

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

hive> show databases like 'product*|*payment';
OK
direct_payment
gift_card_payment
online_payment
product_details
product_availability
product_comments
product_ratings
product_sales_history
Time taken: 0.016 seconds, Fetched: 8 row(s)

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

hive> show databases like '*sellers*';
OK
Retail_sellers
sellers_details
premium_sellers
Inactive_sellers_history
Time taken: 0.016 seconds, Fetched: 4 row(s)

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

SHOW TABLES [LIKE identifier_with_wildcards];

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

hive> use KC_University;
OK
Time taken: 0.02 seconds
hive> show tables like '*student*';
OK
online_students
students_details
students_projects
UK_students_admission
university_students_marks
Time taken: 0.016 seconds, Fetched: 5 row(s)

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.

Recommended Articles