Search Databases and Tables in Apache Hive

In all SQL supported systems, the LIKE operator is used in the WHERE clause of a query statement to search for a specified pattern in a column.

But what if we need to find a certain table or database?

Users will mostly have an option to search for a specific table or database in most database graphical user interfaces (GUIs). For example, in SQL Server Management Studio, you can filter in the objects explorer for a table/ database based on keywords. But it’s more challenging when it comes to CLIs. Listing all of the tables and databases may not always be the best solution. Apache Hive includes a capability that can assist us in finding the table/database using wildcards with LIKE operator.

SHOW DATABASES command will list out all the databases and SHOW TABLES will list out all the tables in the database. However, in order to filter, use the following :

SHOW DATABASES LIKE ‘*sales*’;
The above command will return any databases with the word ‘sales’ in their name. Similarly,
SHOW TABLES LIKE ‘*test*’
will return all the tables that are having ‘test’ in their name.

ShowDatabaseLikeOperator

Hope you find this article helpful.

Please subscribe for more interesting updates.

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s