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.
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)))) ;
Execute the script:
$ hive -f view.hqlYou 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
Execute the script again:
$ hive -f view.hqlYou 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...