Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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

Highlighted

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

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

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

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

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

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;

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

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