Hi Ari, Please send an email to [email protected]<mailto:[email protected]>
Thanks! Nick From: Ari Flink [mailto:[email protected]] Sent: Monday, January 26, 2015 1:30 PM To: [email protected] Subject: Re: Hive JSON Serde question unsubscribe On Sun, Jan 25, 2015 at 6:54 PM, Sanjay Subramanian <[email protected]<mailto:[email protected]>> wrote: sure will try get_json_object thank u regards sanjay ________________________________ From: 丁桂涛（桂花） <[email protected]<mailto:[email protected]>> To: [email protected]<mailto:[email protected]>; Sanjay Subramanian <[email protected]<mailto:[email protected]>> Sent: Sunday, January 25, 2015 4:45 PM Subject: Re: Hive JSON Serde question Try get_json_object UDF. No iterations need. :) On Mon, Jan 26, 2015 at 12:25 AM, Sanjay Subramanian <[email protected]<mailto:[email protected]>> wrote: Thanks Ed. Let me try a few more iterations. Somehow I am not doing this correctly :-) regards sanjay ________________________________ From: Edward Capriolo <[email protected]<mailto:[email protected]>> To: "[email protected]<mailto:[email protected]>" <[email protected]<mailto:[email protected]>>; Sanjay Subramanian <[email protected]<mailto:[email protected]>> Sent: Sunday, January 25, 2015 8:11 AM Subject: Re: Hive JSON Serde question Nested lists require nested lateral views. On Sun, Jan 25, 2015 at 11:02 AM, Sanjay Subramanian <[email protected]<mailto:[email protected]>> wrote: hey guys This is the Hive table definition I have created based on the JSON I am using this version of hive json serde https://github.com/rcongiu/Hive-JSON-Serde ADD JAR /home/sanjay/mycode/jar/jsonserde/json-serde-1.3.1-SNAPSHOT-jar-with-dependencies.jar ; DROP TABLE IF EXISTS datafeed_json ; CREATE EXTERNAL TABLE IF NOT EXISTS datafeed_json ( object STRING, entry array <struct <id:STRING, time:BIGINT, changes:array <struct <field:STRING, value:struct <item:STRING, verb:STRING, parent_id:STRING, sender_id:BIGINT, created_time:BIGINT>>>>> ) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' STORED AS TEXTFILE LOCATION '/data/sanjay/datafeed' ; QUERY 1 ======= ADD JAR /home/sanjay/mycode/jar/jsonserde/json-serde-1.3.1-SNAPSHOT-jar-with-dependencies.jar ; SELECT object, entry[0].id, entry[0].time, entry[0].changes[0].field, entry[0].changes[0].value.item, entry[0].changes[0].value.verb, entry[0].changes[0].value.parent_id, entry[0].changes[0].value.sender_id, entry[0].changes[0].value.created_time FROM datafeed_json ; RESULT1 ====== foo123 113621765320467 1418608223 leads song1 rock 113621765320467_1107142375968396 100004748082019 1418608223 QUERY2 ====== ADD JAR /home/sanjay/mycode/jar/jsonserde/json-serde-1.3.1-SNAPSHOT-jar-with-dependencies.jar ; SELECT object, entry.id<http://entry.id/>, entry.time, ntry FROM datafeed_json LATERAL VIEW EXPLODE (datafeed_json.entry.changes) oc1 AS ntry ; RESULT2 ======= This gives 4 rows but I was not able to iteratively do the LATERAL VIEW EXPLODE I tried various combinations of LATERAL VIEW , LATERAL VIEW EXPLODE, json_tuple to extract all fields in an exploded view from the JSON in tab separated format but no luck. Any thoughts ? Thanks sanjay
