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

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

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