Dynamic partitioning in Hive

2
votes

I am new to hive. My input file is of the form (ID, Date(YYYY-MM-DD), hour(HH), key, value).Table is partitioned on (date, hour) the input file contains data for seven days(24 hours for each day). When i load this data into hive table, i need the data to be loaded in respective partitions of the table. Can some please help me out.

Thanks, Sudhakar.

1
mapreducehive
Have you tried a Map/Reduce job? - Olaf

1 Answers

4
votes

one way is to first load the data into an unpartitioned table (e.g. tmp_some_table in the example below). Then you can do something like:

  set hive.exec.dynamic.partition=true;
  set hive.exec.dynamic.partition.mode=nonstrict;
  from tmp_some_table tt
  insert overwrite table some_table partition(day, hour)
  select
    id,
    key,
    value, 
    day,
    hour

the partitions need to be the last columns in your select clause. The above works on hive 0.7.1. See the wiki for more info. Note that if you have too many partitions you'll get errors.