Support Questions

Find answers, ask questions, and share your expertise
Celebrating as our community reaches 100,000 members! Thank you!

Querying JSON data using Hive


I am using 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 ?


Expert Contributor

This problem has been solved!

Want to get a detailed solution you have to login/registered on the community


Master Mentor
@Sami Ahmad

what is your sample data?

Super Collaborator

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 .

Super Collaborator

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

 flume-ng agent --conf-file --name agent1  -Dflume.root.logger=WARN,console -Dtwitter4j.http.proxyPort=8080
[root@hadoop1 ~]# more
agent1.sources =source1
agent1.sinks = sink1
agent1.channels = channel1

agent1.sources.source1.channels = channel1 = 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

Super Collaborator

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

Master Mentor

@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.

Super Collaborator

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 ?

Super Collaborator

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