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 ...

Get Hadoop Beginner's Guide now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.