Hadoop: Data Processing and Modelling

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