question Re: Querying JSON data using Hive in Support Questions

Querying JSON data using Hive

Jagatheeshr — Fri, 20 Nov 2015 04:19:21 GMT

I am using https://github.com/rcongiu/Hive-JSON-Serde to query the JSON data via hive.

As part of testing, I am using an external table to query the JSON plain text file in HDFS.

i am able to query the data from hive using select, However when i do select * from JSON_EXTERNAL_TABLE limit 1, the output is an Invalid JSON though the message in HDFS is a valid JSON. Is this an expected one ?

Re: Querying JSON data using Hive

gopalv — Fri, 20 Nov 2015 04:20:38 GMT

JSON Serde works on 1 line at a time, with each line being parsed independently. Is your JSON encoded to fit into 1 line for each record in your stream?

Re: Querying JSON data using Hive

amcbarnett — Fri, 20 Nov 2015 04:27:56 GMT

Maybe use a different SERDE?

ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' or
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' from https://github.com/sheetaldolas/Hive-JSON-Serde/tree/master

Re: Querying JSON data using Hive

aervits — Fri, 20 Nov 2015 04:55:57 GMT

load the JSON as string with column name "json"

SELECT get_json_object(json, '$.id') AS ID,get_json_object(json, '$.person.last_name') AS LastName,get_json_object(json, '$.person.first_name') AS FirstName,get_json_object(json, '$.person.email') AS email,get_json_object(json, '$.person.location.address') AS Address,get_json_object(json, '$.person.location.city') AS City,get_json_object(json, '$.person.location.state') AS State,get_json_object(json, '$.person.location.zipcode') AS Zip,get_json_object(json, '$.person.text') AS Text,get_json_object(json, '$.person.url') AS URLFROM HBASE_JSON_TABLE;ORSELECT id, lastName, firstName, email, city, state, text, url FROM hbase_json_table A LATERAL VIEW json_tuple(A.json, 'id', 'person') B AS id, personLATERAL VIEW json_tuple(person, 'last_name', 'first_name', 'email', 'text', 'url', 'location') C as lastName, firstName, email, text, url, locLATERAL VIEW json_tuple(loc, 'city', 'state') D AS city, state;

CREATE EXTERNAL TABLE json_serde_table (
  id string,
  person struct<email:string, first_name:string, last_name:string, location:struct<address:string, city:string, state:string, zipcode:string>, text:string, url:string>)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
LOCATION '/tmp/json/';
SELECT id, person.first_name, person.last_name, person.email, 
person.location.address, person.location.city, person.location.state, 
person.location.zipcode, person.text, person.url
FROM json_serde_table LIMIT 5;

Re: Querying JSON data using Hive

Jagatheeshr — Fri, 20 Nov 2015 19:58:53 GMT

@Artem Ervits. Have used the last create external statement . If i do select * from json_serde_table ; i get a JSON output.but not in a valid format.

Re: Querying JSON data using Hive

Jagatheeshr — Fri, 20 Nov 2015 19:59:30 GMT

@Ancil McBarnett, i am using the 2nd one here. Which is quite popular and well tested as it appers

Re: Querying JSON data using Hive

Jagatheeshr — Fri, 20 Nov 2015 20:06:07 GMT

@gopal, Yes. The input JSON is all in single line.

Enabled debugging, to see if the select * on hive console is using a different Serde than the one defined in the Table. however it looks thats not the case,

Enabled the debug option in hive cli

serialization.lib=org.openx.data.jsonserde.JsonSerDe,

Re: Querying JSON data using Hive

aervits — Fri, 20 Nov 2015 21:39:15 GMT

can you paste the output

Re: Querying JSON data using Hive

sreecharan_akir — Mon, 22 Aug 2016 12:16:06 GMT

What if my Json record is spread across multiple lines?

Re: Querying JSON data using Hive

aliyesami — Tue, 25 Oct 2016 00:05:20 GMT

hi Artem

I used your method but I am getting error can you help please?

CREATE EXTERNAL TABLE tweetdata3(created_at STRING,
text STRING,
  person STRUCT< 
     screen_name:STRING,
     name:STRING,
     locations:STRING,
     description:STRING,
     created_at:STRING,
     followers_count:INT,
     url:STRING>
) ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'  location '/user/flume/tweets';

hive>
    >
    > select person.name,person.locations, person.created_at, text from tweetdata3;
OK
Failed with exception java.io.IOException:org.apache.hadoop.hive.serde2.SerDeException: org.codehaus.jackson.JsonParseException: Unexpected character ('O' (code 79)): expected a valid value (number, String, array, object, 'true', 'false' or 'null')
 at [Source: java.io.ByteArrayInputStream@2bc779ed; line: 1, column: 2]
Time taken: 0.274 seconds
hive>

Re: Querying JSON data using Hive

aervits — Tue, 25 Oct 2016 00:09:27 GMT

@Sami Ahmad

what is your sample data?

Re: Querying JSON data using Hive

aliyesami — Tue, 25 Oct 2016 00:16:21 GMT

events1476284674520.zip

iam attaching the tweeter file that was created using flume . can you please see if its of valid structure as I am unable to read/view this file .

Re: Querying JSON data using Hive

aliyesami — Tue, 25 Oct 2016 00:19:00 GMT

and this is how I generate these twitter files (based on internet demos)

 flume-ng agent --conf-file twitter-to-hdfs.properties --name agent1  -Dflume.root.logger=WARN,console -Dtwitter4j.http.proxyHost=dotatofwproxy.tolls.dot.state.fl.us -Dtwitter4j.http.proxyPort=8080
[root@hadoop1 ~]# more twitter-to-hdfs.properties
agent1.sources =source1
agent1.sinks = sink1
agent1.channels = channel1

agent1.sources.source1.channels = channel1
agent1.sinks.sink1.channel = channel1
agent1.sources.source1.type = org.apache.flume.source.twitter.TwitterSource
agent1.sources.source1.consumerKey = xxxxxxxxxxxxxxxxxxxxxxxxxTaz
agent1.sources.source1.consumerSecret = xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxCI9
agent1.sources.source1.accessToken = xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxwov
agent1.sources.source1.accessTokenSecret = xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxY5H3
agent1.sources.source1.keywords = Clinton Trump
agent1.sinks.sink1.type = hdfs
agent1.sinks.sink1.hdfs.path = /user/flume/tweets
agent1.sinks.sink1.hdfs.filePrefix = events
agent1.sinks.sink1.hdfs.fileSuffix = .log
agent1.sinks.sink1.hdfs.inUsePrefix = _
agent1.sinks.sink1.hdfs.fileType = DataStream
agent1.channels.channel1.type = file

Re: Querying JSON data using Hive

aliyesami — Tue, 25 Oct 2016 20:45:55 GMT

Artem any advise? I am anxiously waiting for yours or anyone feedback

Re: Querying JSON data using Hive

aervits — Tue, 25 Oct 2016 23:01:12 GMT

@Sami Ahmad if you can't view or read the file means this is not a valid text file, hence the problems you're facing. From your output, looks like you have some binary structures in your file and therefore you're having difficulty setting Hive schema on top of it. Please review your HDFS sink properties. Also, I highly recommend investing in Apache Nifi, all these problems would go away quickly and since this is a closed thread, please open a new question instead of here. This makes it difficult to understand the context of this thread. Again, once you can view your resultant twitter file output, you should be able to apply Hive schema on it.

Re: Querying JSON data using Hive

aliyesami — Wed, 26 Oct 2016 00:49:29 GMT

I already have another thread open but not getting much responses there . can you please follow up on that thread ?

also I attached my output file and how I generate it in this thread and the other , can you help me identify why the file is bad ?

https://community.hortonworks.com/questions/61181/reading-json-files.html#comment-63383

Re: Querying JSON data using Hive

aliyesami — Wed, 26 Oct 2016 01:19:59 GMT

also created a new one just for finding out how to create the twitter json file from flume

https://community.hortonworks.com/questions/63419/bad-hdfs-sink-property.html