Hive loading in partitioned table
hive, loading
Solution
Ning Zhang has a great response on the topic at http://grokbase.com/t/hive/user/114frbfg0y/can-i-use-hive-dynamic-partition-while-loading-data-into-tables.
The quick context is that:
- Load data simply copies data, it doesn't read it so it cannot figure out what to partition
- Would suggest that you load data into an intermediate table first (or using an external table pointing to all the files) and then letting partition dynamic insert to kick in to load it into a partitioned table
Problem
I have a log file in HDFS, values are delimited by comma. For example: `2012-10-11 12:00,opened_browser,userid111,deviceid222` Now I want to load this file to Hive table which has columns "timestamp","action" and partitioned by "userid","deviceid". How can I ask Hive to take that last 2 columns in log file as partition for table? All examples `e.g. "hive> LOAD DATA INPATH '/user/myname/kv2.txt' OVERWRITE INTO TABLE invites PARTITION (ds='2008-08-15');"` require definition of partitions in the script, but I want partitions to set up automatically from HDFS file. The one solution is to create intermediate non-partitioned table with all that 4 columns, populate it from file and then make an `INSERT into first_table PARTITION (userid,deviceid) select from intermediate_table timestamp,action,userid,deviceid;` but that is and additional task and we will have 2 very similiar tables.. Or we should create external table as intermediate.