import json data into hive table,store JSON data in hive

[#1]

i want to transfer json data in hive table.

,

[#2]

@Sunit Gupta Here are some good resources:

http://thornydev.blogspot.com/2013/07/querying-json-records-via-hive.html

http://engineering.skybettingandgaming.com/2015/01/20/parsing-json-in-hive/

[#3]

i have this type of json files please tell me how to store in hive

{
"md5": "bcgjcjdshhdfhkfd",
"dat": "15/08/2000",
"data": '[{"job_id":"589d0147aac2ed8665ce94d6","environment_id":100,"environment_description":"Windows732bit","size":109056,"type":"PE32executable(GUI)Intel80386, forMSWindows","type_short":["peexe"],"target_url":null,"state":"SUCCESS","error_type":null,"error_origin":null,"submit_name":"svchost.exe","md5":"7657fcb7d772448a6d8504e4b20168b8","sha1":"84c7201f7e59cb416280fd69a2e7f2e349ec8242","sha256":"54bc950d46a0d1aa72048a17c8275743209e6c17bdacfc4cb9601c9ce3ec9a71","sha512":"786addd2a793bd4123625b22dc717d193246442ac97f1c3f4a763ec794b48e68051cd41097c0e9f7367e6914534f36eafccb109ab03dc793d68bf1522e7884e2","ssdeep":"1536: xfZZnAEjEIZvumULmj4wrraK5dZ4Ltta9Km\/ec3DtAL6bmZ4bXSjrAE+fySPoqRr: xnnAQVG\/LytaKItS\/fiLKS+f5Aq7is","imphash":"093a51e0b7dcb2466b7edfd78d191aa0","av_detect":91,"vx_family":"Gen: Variant.Kazy","url_analysis":false,"analysis_start_time":"2017-02-10T00: 54: 57-06: 00","threat_score":100,"interesting":false,"threat_level":2,"verdict":"malicious","certificates":[],"domains":["stromoliks.com","promoliks.com"],"classification_tags":[],"compromised_hosts":[],"hosts":[],"total_network_connections":0,"total_processes":2,"total_signatures":37,"extracted_files":[],"processes":[],"file_metadata":null,"tags":[],"mitre_attcks":[]}]'
}

[#4]

Download JSON serde Jar file. move this file into your HDFS

you can use below link for that.

https://docs.aws.amazon.com/athena/latest/ug/json.html

Move your .Json data into HDFS, create table in hive add required columns which you want to load.

Use below command before creating table in hive to add jar file.

add jar hdfs:///<path-to-serde>/<name-of-serde>.jar;

CREATE EXTERNAL TABLE IF NOT EXISTS <your-table-name>(columns)

ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' STORED AS TEXTFILE;

[#5]

i already done this but the problem is ,i am not able to query the data ,it giving me error.

'org.apache.hive.service.cli.operation.SQLOperation:getNextRowSet:SQLOperation.java:361', "*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')\n at [Source: java.io.StringReader@7d14527f; line: 1, column: 2]:30:1", 'com.cloudera.hive.serde.JSONSerDe:deserialize:JSONSerDe.java:128', 'org.apache.hadoop.hive.ql.exec.FetchOperator:getNextRow:FetchOperator.java:488', "*org.codehaus.jackson.JsonParseException:Unexpected character ('O' (code 79)): expected a valid value (number, String, array, object, 'true', 'false' or 'null')\n at [Source: java.io.StringReader@7d14527f; line: 1, column: 2]:38:8", 'org.codehaus.jackson.JsonParser:_constructError:JsonParser.java:1291',