Sometimes you would want to load maps which contain maps into Hive. I mean, this structure
Hive allows you this. In fact, it allows even deeper levels of mapping. However, the question is, how do you tell it where your inner maps end, since this is not one of the parameters in the LOAD DATA INPATH statement. Well, there is an undocumented default, and that is, '\004' and '\005' for inner maps.
Here is how your data has to be formatted (using an image, to show the non-ascii separators)
and this is how you define your table
CREATE EXTERNAL TABLE map_table
complex_map map <string, map<string,string>>
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\001'
COLLECTION ITEMS TERMINATED BY '\003'
MAP KEYS TERMINATED BY '\002'
STORED AS TextFile;
and load it as follows
LOAD DATA INPATH "/mydata" OVERWRITE INTO TABLE map_table;
and you can have the following queries:
hive> select * from map_table;
hive> select complex_map["key1"] from map_table;
hive> select complex_map["key1"]["innerkey11"] from map_table;
Now, it is true, this uses the default coding for inner-level maps for Hive load, and that's not documented, but it IS the coding which is unlikely to change.
Credits: this post talks about it, and it was brought to my attention by Steven Wong of Netflix.