Find list of databases/tables with specific pattern in Hive
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
|
1 |
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.
|
1 2 3 4 5 6 7 8 |
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.
|
1 2 3 4 5 6 |
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
|
1 2 3 4 5 6 7 8 9 10 11 |
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.
|
1 2 3 4 5 6 7 |
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
|
1 |
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’;
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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
- Show create table statement in Hive
- Describe command in Hive
- How to create a database at specific location in Hive?
