Impala SHOW Statement – Listing the Tables using Hue

In our last Impala tutorial, we learned to create table statements, drop table statements in Impala. in this tutorial, we will discuss Impala Show Statements, is used to get information about different types of Impala objects.

Moreover, we will discuss, how to get information with Impala SHOW Statement. Also, we will cover its syntax, usage as well as an example to understand it well.

So, let’s start Impala Show Statements.

How to use Impala SHOW Statement?

Basically, to get information about different types of Impala objects we use the Impala SHOW statement. In addition, it is a flexible way to do it.

In other words, we can say to get the list of all the existing tables in the current database we use the show tables statement in Impala.

a. Syntax of Show Statements in Impala

So, the syntax for using Impala SHOW Statement is-

SHOW DATABASES [[LIKE] 'pattern']
SHOW SCHEMAS [[LIKE] 'pattern'] - an alias for SHOW DATABASES
SHOW TABLES [IN database_name] [[LIKE] 'pattern']
SHOW [AGGREGATE | ANALYTIC] FUNCTIONS [IN database_name] [[LIKE] 'pattern']
SHOW CREATE TABLE [database_name].table_name
SHOW TABLE STATS [database_name.]table_name
SHOW COLUMN STATS [database_name.]table_name
SHOW PARTITIONS [database_name.]table_name
SHOW FILES IN [database_name.]table_name [PARTITION (key_col=value [, key_col=value]]
SHOW ROLES
SHOW CURRENT ROLES
SHOW ROLE GRANT GROUP group_name
SHOW GRANT ROLE role_name

Also, to see the appropriate objects in the current database, issue a SHOW object_type statement, or to see objects in a specific database SHOW object_type IN database_name.

Moreover, using Unix-style * wildcards and allowing | for alternation, the optional pattern argument is a quoted string literal. Also, the preceding LIKE keyword is optional. Although, make sure use all lowercase letters in the pattern string because all object names are stored in lowercase.

For example:

show databases 'a*';
show databases like 'a*';
show tables in some_db like '*fact*';
use some_db;
show tables '*dim*|*fact*';

b. Cancellation

It is not possible to cancel it. That implies it Cannot be canceled.

Example of SHOW Statements in Impala

Let us understand it with an example of the show tables statement. So, begin with changing the context to the required database if we want to get the list of tables in a particular database. Then using show tables statement we will get the list of tables in it.

For Example,

[quickstart.cloudera:21000] > use my_db;
Query: use my_db
[quickstart.cloudera:21000] > show tables;

Moreover, Impala fetches the list of all the tables in the specified database, on executing the above query.

Query: show tables
+-----------+
| name      |
+-----------+
| customers |
| employee  |
+-----------+
Fetched 2 row(s) in 0.10s

Listing the Tables using Hue

There are several steps you have to follow while Listing the Tables using Hue, such as:

• At first, select the context as my_db. Further, type the show tables statement in Impala Query editor then click on the execute button.
• Afterward, we can see the list of the tables if we scroll down and select the results tab just after executing the query.

So, this was all in the Impala SHOW Statements. Hope you like our explanation.

Conclusion – Impala SHOW Statements

As a result, we have seen the whole concept of Impala SHOW Statement. Still, if any doubt occurs, feel free to ask in the comment section.