Support Questions
Find answers, ask questions, and share your expertise
Announcements
Check out our newest addition to the community, the Cloudera Innovation Accelerator group hub.

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

New Contributor

i want to transfer json data in hive table.

,

4 REPLIES 4

New Contributor

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":[]}]'
}

New Contributor

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;

New Contributor

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',