Views in Hive

Views in Hive

hive> CREATE VIEW raw_view AS SELECT r1.id, r2.name,r1.dt FROM raw1 r1 JOIN raw2 r2 ON (r1.id=r2.id) WHERE r1.name like "s%";
OK
id	name	dt
Time taken: 0.251 seconds

hive> select * from raw_view;
Query ID = ubuntu_20160128185605_ee25eca7-b580-42a5-89af-18d412352f08
Total jobs = 1
2016-01-28 18:56:09,141 WARN  [main] util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
Execution log at: /tmp/ubuntu/ubuntu_20160128185605_ee25eca7-b580-42a5-89af-18d412352f08.log
2016-01-28 18:56:09	Starting to launch local task to process map join;	maximum memory = 518979584
2016-01-28 18:56:11	Dump the side-table for tag: 0 with group count: 2 into file: file:/tmp/hive/54faa348-44ed-43ce-b53c-7088021d9239/hive_2016-01-28_18-56-05_237_68899345089162387-1/-local-10003/HashTable-Stage-3/MapJoin-mapfile00--.hashtable
2016-01-28 18:56:11	Uploaded 1 File to: file:/tmp/hive/54faa348-44ed-43ce-b53c-7088021d9239/hive_2016-01-28_18-56-05_237_68899345089162387-1/-local-10003/HashTable-Stage-3/MapJoin-mapfile00--.hashtable (320 bytes)
2016-01-28 18:56:11	End of local task; Time Taken: 1.241 sec.
Execution completed successfully
MapredLocal task succeeded
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1453963805097_0038, Tracking URL = http://ubuntu:8088/proxy/application_1453963805097_0038/
Kill Command = /usr/local/hadoop2/bin/hadoop job  -kill job_1453963805097_0038
Hadoop job information for Stage-3: number of mappers: 1; number of reducers: 0
2016-01-28 18:56:18,118 Stage-3 map = 0%,  reduce = 0%
2016-01-28 18:56:24,711 Stage-3 map = 100%,  reduce = 0%, Cumulative CPU 1.14 sec
MapReduce Total cumulative CPU time: 1 seconds 140 msec
Ended Job = job_1453963805097_0038
MapReduce Jobs Launched: 
Stage-Stage-3: Map: 1   Cumulative CPU: 1.14 sec   HDFS Read: 6335 HDFS Write: 42 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 140 msec
OK
raw_view.id	raw_view.name	raw_view.dt
1	sriram	12-10-2015
2	seetamma	12-09-2015
Time taken: 20.628 seconds, Fetched: 2 row(s)

hive> DESCRIBE raw_view;
OK
col_name	data_type	comment
id                  	int                 	                    
name                	string              	                    
dt                  	string              	                    
Time taken: 0.093 seconds, Fetched: 3 row(s)



hive> EXPLAIN EXTENDED CREATE VIEW raw_view AS SELECT r1.id, r2.name,r1.dt FROM raw1 r1 JOIN raw2 r2 ON (r1.id=r2.id) WHERE r1.name like "s%";
OK
Explain
ABSTRACT SYNTAX TREE:
  
TOK_CREATEVIEW
   TOK_TABNAME
      raw_view
   TOK_QUERY
      TOK_FROM
         TOK_JOIN
            TOK_TABREF
               TOK_TABNAME
                  raw1
               r1
            TOK_TABREF
               TOK_TABNAME
                  raw2
               r2
            =
               .
                  TOK_TABLE_OR_COL
                     r1
                  id
               .
                  TOK_TABLE_OR_COL
                     r2
                  id
      TOK_INSERT
         TOK_DESTINATION
            TOK_DIR
               TOK_TMP_FILE
         TOK_SELECT
            TOK_SELEXPR
               .
                  TOK_TABLE_OR_COL
                     r1
                  id
            TOK_SELEXPR
               .
                  TOK_TABLE_OR_COL
                     r2
                  name
            TOK_SELEXPR
               .
                  TOK_TABLE_OR_COL
                     r1
                  dt
         TOK_WHERE
            like
               .
                  TOK_TABLE_OR_COL
                     r1
                  name
               "s%"


STAGE DEPENDENCIES:
  Stage-0 is a root stage

STAGE PLANS:
  Stage: Stage-0
      Create View Operator:
        Create View
          or replace: false
          columns: id int, name string, dt string
          expanded text: SELECT `r1`.`id`, `r2`.`name`,`r1`.`dt` FROM `thanooj`.`raw1` `r1` JOIN `thanooj`.`raw2` `r2` ON (`r1`.`id`=`r2`.`id`) WHERE `r1`.`name` like "s%"
          name: thanooj.raw_view
          original text: SELECT r1.id, r2.name,r1.dt FROM raw1 r1 JOIN raw2 r2 ON (r1.id=r2.id) WHERE r1.name like "s%"

Time taken: 0.121 seconds, Fetched: 67 row(s)
hive>

A view allows a query to be saved and treated like a table. It is a logical construct, as it does not store data like a table.

hive> DESCRIBE raw_view;