Views in Hive

Views

           Views are a logical construct that can be used to simplify queries by either abstracting away complexities such as joins or subqueries or by pre-filtered or restricting data. The usage of view in Hive is same as that of the view in SQL.

            A view is capable of using any valid select statement & can be used to filter the projection (columns) or it can contain a predicate (where clause) to filter the results.

            Syntax:-

                             CREATE VIEW <ViewName> AS

                              SELECT <col1,col2,col3....>

                              FROM <table>;

                          

Creation of View:- 

hive> create view ny_death_view as
    > select year,leading_cause,sex,death from ny_cause_of_death_new limit 20;
OK
Time taken: 2.161 seconds

hive> select * from ny_death_view;
OK
2014    Diabetes Mellitus (E10-E14)    F    11
2011    Cerebrovascular Disease (Stroke: I60-I69)    M    290
2008    Malignant Neoplasms (Cancer: C00-C97)    M    60
2010    Malignant Neoplasms (Cancer: C00-C97)    F    1045
2012    Cerebrovascular Disease (Stroke: I60-I69)    M    170
2007    Mental and Behavioral Disorders due to Use of Alcohol (F10)    M    .
2011    All Other Causes    F    14
2007    Chronic Lower Respiratory Diseases (J40-J47)    F    163
2012    "Essential Hypertension and Renal Diseases (I10     I12)"    Hispanic
2009    "Diseases of Heart (I00-I09     I11     I20-I51)"
2011    Chronic Lower Respiratory Diseases (J40-J47)    M    .
2008    Cerebrovascular Disease (Stroke: I60-I69)    F    13
2013    "Diseases of Heart (I00-I09     I11     I20-I51)"
2010    Cerebrovascular Disease (Stroke: I60-I69)    F    .
2012    "Diseases of Heart (I00-I09     I11     I20-I51)"
2012    Malignant Neoplasms (Cancer: C00-C97)    M    1563
2011    Cerebrovascular Disease (Stroke: I60-I69)    F    98
2014    "Congenital Malformations     Deformations    F
2009    All Other Causes    F    220
Time taken: 3.869 seconds, Fetched: 20 row(s)

Dropping the View:-
           Syntax:-  DROP VIEW ViewName;

hive (hivedb)> show tables;
OK
employee
ny_cause_of_death_new
ny_death_view
reuse_extrnl_tab
Time taken: 0.769 seconds, Fetched: 4 row(s)

hive (hivedb)> drop view ny_death_view;
OK
Time taken: 28.846 seconds

hive (hivedb)> show tables;
OK
employee
ny_cause_of_death_new
reuse_extrnl_tab
Time taken: 0.161 seconds, Fetched: 3 row(s)