HIVE nested ARRAY in MAP data type

Question

I have HIVE table structured like this:-

Create table test_stg(employee_id INT, name STRING, abu ARRAY <String>, sabu MAP <String, ARRAY<INT>)
row format delimited fields terminated by '|'                                                              
collection items terminated by '/'                                                                         
map keys terminated by ':'; 

I will import the data from local file system using LOAD DATA LOCAL....

The question is how should I frame the contents of my local file so that Map datatype field sabu can have nested array.

Thanks in advance.

Answer 1

Hive's default delimiters are:

• Row Delimiter => Control-A ('\001')
• Collection Item Delimiter => Control-B ('\002')
• Map Key Delimiter => Control-C ('\003')

If you override these delimiters then overridden delimiters are used during parsing. The preceding description of delimiters is correct for the usual case of flat data structures, where the complex types only contain primitive types. For nested types the level of the nesting determines the delimiter.

For an array of arrays, for example, the delimiters for the outer array are Control-B ('\002') characters, as expected, but for the inner array they are Control-C ('\003') characters, the next delimiter in the list.

Hive actually supports eight levels of delimiters, corresponding to ASCII codes 1, 2, ... 8, but you can only override the first three.

For your case delimiter for items in nested Array of Map datatype field sabu will be '\004' as Map Key Delimiter is '\003' (Overridden as ':').

So you can write your input file as following format:

1|JOHN|abu1/abu2|key1:1'\004'2'\004'3/key2:6'\004'7'\004'8

Output of SELECT * FROM test_stg; will be:

1       JOHN     ["abu1","abu2"]     {"key1":[1,2,3],"key2":[6,7,8]}

Reference: Hadoop The Definitive Guide - Chapter 12: Hive, Page No: 433, 434

Answer 2

I find a easy way to do so in 3 steps. Also reference from "Hadoop The Definitive Guide - Chapter 12: Hive"

hive> create table test_stg2 as select 1, 'name', array('str1','str2'), map('key',array(1,2)) from dummy;

$ hdfs dfs -copyToLocal /user/hive/warehouse/test_stg2/000000_0 test_stg2_dump

$ vi test_stg2_dump

1^Aname^Astr1^Bstr2^Akey^C1^D2

That's what you need. And the nest level decides what your delimiter will be.