Re: Hive JSON Serde question
sure will try get_json_objectthank uregardssanjay From: 丁桂涛（桂花） To: user@hive.apache.org; Sanjay Subramanian 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 wrote: Thanks Ed. Let me try a few more iterations. Somehow I am not doing this correctly :-) regards sanjay From: Edward Capriolo To: "user@hive.apache.org" ; Sanjay Subramanian 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 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 ) 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 14748082019 1418608223 QUERY2==ADD JAR /home/sanjay/mycode/jar/jsonserde/json-serde-1.3.1-SNAPSHOT-jar-with-dependencies.jar; SELECT object, 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
Re: Hive JSON Serde question
Try get_json_object UDF. No iterations need. :) On Mon, Jan 26, 2015 at 12:25 AM, Sanjay Subramanian < sanjaysubraman...@yahoo.com> wrote: > Thanks Ed. Let me try a few more iterations. Somehow I am not doing this > correctly :-) > > regards > > sanjay > -- > *From:* Edward Capriolo > *To:* "user@hive.apache.org" ; Sanjay Subramanian < > sanjaysubraman...@yahoo.com> > *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 < > sanjaysubraman...@yahoo.com> 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 > time:BIGINT, > changes:array > value:struct > 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 14748082019 1418608223 > > > QUERY2 > == > ADD JAR > /home/sanjay/mycode/jar/jsonserde/json-serde-1.3.1-SNAPSHOT-jar-with-dependencies.jar > ; > SELECT > object, > 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 > > > > > >
Re: Hive JSON Serde question
Thanks Ed. Let me try a few more iterations. Somehow I am not doing this correctly :-) regards sanjay From: Edward Capriolo To: "user@hive.apache.org" ; Sanjay Subramanian 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 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 ) 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 14748082019 1418608223 QUERY2==ADD JAR /home/sanjay/mycode/jar/jsonserde/json-serde-1.3.1-SNAPSHOT-jar-with-dependencies.jar; SELECT object, 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
Re: Hive JSON Serde question
Nested lists require nested lateral views. On Sun, Jan 25, 2015 at 11:02 AM, Sanjay Subramanian < sanjaysubraman...@yahoo.com> 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 > time:BIGINT, > changes:array > value:struct > 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 14748082019 1418608223 > > > QUERY2 > == > ADD JAR > /home/sanjay/mycode/jar/jsonserde/json-serde-1.3.1-SNAPSHOT-jar-with-dependencies.jar > ; > SELECT > object, > 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 > > >
Hive JSON Serde question
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 ) 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 14748082019 1418608223 QUERY2==ADD JAR /home/sanjay/mycode/jar/jsonserde/json-serde-1.3.1-SNAPSHOT-jar-with-dependencies.jar; SELECT object, 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
