We will create a new table for the UFO data to demonstrate the usefulness of partitioning.
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' ;
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 ;
Create the partitioned table:
$ hive -f createpartition.hqlYou 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...