Hive query - Get list of all View Names for given Table Name

Question

Is there a way to query and fetch list of all View Names for a given Hive Table?

If we created three Views for the table. Given Table name as CRICKET_STATS, how to get output as,

CRICKET_AUS_VW 
CRICKET_SA_VW
CRICKET_IND_VW

HIVE DDL SCRIPT

CREATE TABLE CRICKET_STATS (COUNTRY_CODE STRING, NO_OF_MATCHES INT, MATCHES_WON INT, MATCHES_LOST INT) stored as orc
location '/tmp/hive-temp-tables/CRICKET_STATS'
tblproperties ('orc.compress.size'='8192');

CREATE VIEW IF NOT EXISTS CRICKET_AUS_VW AS SELECT COUNTRY_CODE, NO_OF_MATCHES, MATCHES_WON, MATCHES_LOST FROM CRICKET_STATS WHERE COUNTRY_CODE='AUS';
CREATE VIEW IF NOT EXISTS CRICKET_SA_VW AS SELECT COUNTRY_CODE, NO_OF_MATCHES, MATCHES_WON, MATCHES_LOST FROM CRICKET_STATS WHERE COUNTRY_CODE='ZAF';
CREATE VIEW IF NOT EXISTS CRICKET_IND_VW AS SELECT COUNTRY_CODE, NO_OF_MATCHES, MATCHES_WON, MATCHES_LOST FROM CRICKET_STATS WHERE COUNTRY_CODE='IND';

you can check in metastore if its accessable - https://stackoverflow.com/questions/16738516/hive-how-to-see-the-table-created-in-metastore — Koushik Roy, Dec 02 '20 at 15:25
Thanks @mck for the suggestion. SHOW VIEWS is listing all Views. How to list only those which is created based on a specific Table? — Vasanth Subramanian, Dec 02 '20 at 19:24
Thanks @KoushikRoy. Unfortunately we don't have access to Hive metastore — Vasanth Subramanian, Dec 02 '20 at 19:25

score 1 · Answer 1 · answered Dec 02 '20 at 19:38

Views are not based on tables, but based on databases. However, if you name your views according to the tables that they're created, you can selectively show those views.

For example, in the views you created, if you do SHOW VIEWS LIKE '*aus*', it will return cricket_aus_vw only.

If you name your views with a prefix of the table name, e.g. CRICKET_STATS_AUS_VW then you can do something like SHOW VIEWS LIKE 'CRICKET_STATS_*'

Hive query - Get list of all View Names for given Table Name

1 Answers1