CODEWITHSUNDEEP
hivehiveqldatabase-metadata
Vasanth Subramanian
Vasanth Subramanian

Reputation: 1351

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

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

Upvotes: 0

Views: 1427

Answers (1)

mck
mck

Reputation: 42392

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_*'

Upvotes: 1

Related Questions