Time for action – making a partitioned UFO sighting table

We will create a new table for the UFO data to demonstrate the usefulness of partitioning.

1. Save the following query as createpartition.hql:

   CREATE TABLE partufo(sighted string, reported string, sighting_location string,shape string, duration string, description string) 
   PARTITIONED BY (year string)
   ROW FORMAT DELIMITED
   FIELDS TERMINATED BY '\t' ;

2. Save the following query as insertpartition.hql:

   SET hive.exec.dynamic.partition=true ;
   SET hive.exec.dynamic.partition.mode=nonstrict ;
   
   INSERT OVERWRITE TABLE partufo partition (year)
   SELECT sighted, reported, sighting_location, shape, duration, description,
   SUBSTR(TRIM(sighted), 1,4)  FROM ufodata ;

3. Create the partitioned table:

   $ hive -f createpartition.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_201203101838_17331656.txt
   OK...