Time for action – using views

We can use views to hide the underlying query complexity such as the previous join example. Let us now create a view to do just that.

1. Create the following as view.hql:

   CREATE VIEW IF NOT EXISTS usa_sightings (sighted, reported, shape, state)
   AS select t1.sighted, t1.reported, t1.shape, t2.full_name
   FROM ufodata t1 JOIN states t2
   ON (LOWER(t2.abbreviation) = LOWER(substr( t1.sighting_location, (LENGTH(t1.sighting_location)-1)))) ;

2. Execute the script:

   $ hive -f view.hql
   

   You will receive the following response:

   Logging initialized using configuration in jar:file:/opt/hive-0.8.1/lib/hive-common-0.8.1.jar!/hive-log4j.properties
   Hive history file=/tmp/hadoop/hive_job_log_hadoop_201203040557_1017700649.txt
   OK
   Time taken: 5.135 seconds
   

3. Execute the script again:

   $ hive -f view.hql
   

   You will receive the following response:

   Logging initialized using configuration in jar:file:/opt/hive-0.8.1/lib/hive-common-0.8.1.jar!/hive-log4j.properties
   Hive history file=/tmp/hadoop/hive_job_log_hadoop_201203040557_851275946...