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