hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Martin, Nick" <NiMar...@pssd.com>
Subject RE: Hive JSON Serde question
Date Mon, 26 Jan 2015 18:31:57 GMT
Hi Ari,

Please send an email to user-unsubscribe@hive.apache.org<mailto:user-unsubscribe@hive.apache.org>

Thanks!
Nick

From: Ari Flink [mailto:flinkster@gmail.com]
Sent: Monday, January 26, 2015 1:30 PM
To: user@hive.apache.org
Subject: Re: Hive JSON Serde question

unsubscribe

On Sun, Jan 25, 2015 at 6:54 PM, Sanjay Subramanian <sanjaysubramanian@yahoo.com<mailto:sanjaysubramanian@yahoo.com>>
wrote:
sure will try get_json_object
thank u
regards
sanjay

________________________________
From: 丁桂涛(桂花) <dingguitao@baixing.com<mailto:dingguitao@baixing.com>>
To: user@hive.apache.org<mailto:user@hive.apache.org>; Sanjay Subramanian <sanjaysubramanian@yahoo.com<mailto:sanjaysubramanian@yahoo.com>>
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 <sanjaysubramanian@yahoo.com<mailto:sanjaysubramanian@yahoo.com>>
wrote:
Thanks Ed. Let me try a few more iterations. Somehow I am not doing this correctly :-)

regards

sanjay
________________________________
From: Edward Capriolo <edlinuxguru@gmail.com<mailto:edlinuxguru@gmail.com>>
To: "user@hive.apache.org<mailto:user@hive.apache.org>" <user@hive.apache.org<mailto:user@hive.apache.org>>;
Sanjay Subramanian <sanjaysubramanian@yahoo.com<mailto:sanjaysubramanian@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 <sanjaysubramanian@yahoo.com<mailto:sanjaysubramanian@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
          <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






Mime
View raw message