# Nested arrays in hive from MongoDB

**URL:** <https://discuss.elastic.co/t/nested-arrays-in-hive-from-mongodb/79353>\
**Category:** Elasticsearch\
**Tags:** es-hadoop\
**Created:** [March 21, 2017, 6:23am UTC](https://discuss.elastic.co/t/nested-arrays-in-hive-from-mongodb/79353 "2017-03-21T06:23:00Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![capcha](https://avatars.discourse-cdn.com/v4/letter/c/82dd89/32.png) [@capcha](https://discuss.elastic.co/u/capcha)\
**Post date:** [March 21, 2017, 6:23am UTC](https://discuss.elastic.co/t/nested-arrays-in-hive-from-mongodb/79353/1 "2017-03-21T06:23:00Z")

</div>

Hi,

I am really hoping there is a genius in here that can help me, I've been stuck on this for 3 days now.

I have a MongoDB collection, sample of a document is:

{  
"\_id" : "58c879787f4fd6a4982f3039",  
"Actions" : [  
{  
"What" : {  
"\_t" : [  
"TargetBase",  
"UserTarget",  
"CreatedTarget"  
],  
"Who" : {  
"TontoUserId" : 12345,  
"EmailAddress" : "someuser@test.com.au",  
"Name" : "some-user"  
}  
},  
"When" : ISODate("2017-03-14T23:49:06.585Z"),  
"Reason" : "some text",  
"Wrapup" : null  
}  
]  
}

I have created an external table in hive:

create external table if not exists x (my\_serial string, actions array\<struct\<what:struct\<`_t`:array, who:struct\<tontouserid:string, emailaddress:string, name:string\>\>, `when`:string, reason:string, wrapup:string\>\>)  
STORED BY 'com.mongodb.hadoop.hive.MongoStorageHandler'  
WITH SERDEPROPERTIES('mongo.columns.mapping'='{"my\_serial":"\_id", "actions":"Actions"}')  
TBLPROPERTIES('mongo.uri'='mongodb://testuser:testpwd@test-mongo.test.com.au:27017/somedb.somecollection?authSource=admin');

I don't get any errors when running the above, but when I query the table the only data that I get is the my\_serial column:

select \* from x limit 1;

58c879787f4fd6a4982f3039 [{"what":null,"when":null,"reason":null,"wrapup":null}]

Similar story when I try to explode the actions array:

select my\_serial, actionstuff.what.`_t`  
from x  
LATERAL VIEW explode(actions) actionstable as actionstuff  
limit 1;

If I try a different tack on creating the external table:

create external table if not exists x (my\_serial string, actions array)  
STORED BY 'com.mongodb.hadoop.hive.MongoStorageHandler'  
WITH SERDEPROPERTIES('mongo.columns.mapping'='{"my\_serial":"\_id", "actions":"Actions"}')  
TBLPROPERTIES('mongo.uri'='mongodb://testuser:testpwd@test-mongo.test.com.au:27017/somedb.somecollection?authSource=admin');

I get data, but I can't do anything with it:

58c879787f4fd6a4982f3039 ["{ "What" : { "\_t" : ["TargetBase" , "UserTarget" , "CreatedTarget"] , "Who" : { "TontoUserId" : 12345 , "EmailAddress" : "someuser@test.com.au" , "Name" : "some-user"}} , "When" : { "$date" : "2017-03-14T23:15:04.047Z"} , "Reason" : "some text" , "Wrapup" : null }"]

Anyone able to tell me what I'm doing wrong?

Much appreciated.

---

<div class="post-metadata">

**Author:** ![capcha](https://avatars.discourse-cdn.com/v4/letter/c/82dd89/32.png) [@capcha](https://discuss.elastic.co/u/capcha)\
**Post date:** [March 23, 2017, 10:30pm UTC](https://discuss.elastic.co/t/nested-arrays-in-hive-from-mongodb/79353/2 "2017-03-23T22:30:45Z")

</div>

Hi again,

I didn't get a reply on any of the sites that I posted my question on, but I have worked out a clunky solution that I wish to share in case  
anyone else is looking to do something similar.

-- create the external table to the mongo store  
create external table if not exists x (my\_serial string, actions array)  
STORED BY 'com.mongodb.hadoop.hive.MongoStorageHandler'  
WITH SERDEPROPERTIES('mongo.columns.mapping'='{"my\_serial":"\_id", "actions":"Actions"}')  
TBLPROPERTIES('mongo.uri'='mongodb://testuser:testpwd@test-mongo.test.com.au:27017/somedb.somecollection?authSource=admin');

-- create an intermediary table  
create table job\_actions\_exploded (my\_serial string, t string, reason string, when\_date timestamp, who\_tonto\_user\_id string);

-- insert into the intermediary table  
insert into job\_actions\_exploded  
(my\_serial, t, reason, when\_date, who\_tonto\_user\_id)  
select my\_serial  
, reverse(split(reverse(regexp\_replace(regexp\_replace(regexp\_replace(get\_json\_object(single\_json\_table.single\_json, '$.What.\_t'), '\["', ''), '","', ','), '"\]', '')), ',')[0]) as t  
, get\_json\_object(single\_json\_table.single\_json, '$.Reason') as reason  
, from\_utc\_timestamp(regexp\_replace(regexp\_replace(regexp\_replace(get\_json\_object(single\_json\_table.single\_json, '$.When'), '\{"\$date"\:"', ''), 'Z"\}', ''), 'T', ' '), 'Australia/Brisbane') as when\_date  
, get\_json\_object(single\_json\_table.single\_json, '$.What.Who.TontoUserId') as who\_tonto\_id  
from x  
lateral view explode(actions) single\_json\_table as single\_json;

The key to this is the lateral view explode to create single json strings which can then be inspected using the get\_json\_object function.  
The get\_json\_object is case sensitive when supplying the '$.Column' name.

It's worth noting that I only needed the last value out of the 'What.\_t' array, hence the reverse,split,reverse. This could also have been achieved  
using the regexp\_extract function.

I also needed the UTC date/time that is stored in mongo converted to Brisbane time (Australian Eastern Standard Time, AEST).

I hope this is useful for someone else out there.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [April 20, 2017, 10:30pm UTC](https://discuss.elastic.co/t/nested-arrays-in-hive-from-mongodb/79353/3 "2017-04-20T22:30:57Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.