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
    Time taken: 5.135 ...

Get Hadoop: Data Processing and Modelling now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.