Cloudera Community
sri1417
user rank
Contributor

Hive query to get data from json records

by Contributor in Support Questions
‎06-26-2019 01:29 PM
‎06-26-2019 01:29 PM
Hive query giving null values when trying to pull the data from json which has @date column.below is the sample data and the query iam using. {"name":"jai","@date":"2015-06-15"} {"name":"pri","@date":"2017-08-25"} CREATE TABLE json_obj( json string) LOCATION '/user/*************/' select get_json_object(json_obj.json,'$.name') as name, get_json_object(json_obj.json,'$.@date') as date from json_obj; ouput: name date jai null pri null expected output: name date jai 2015-06-15 pri 2017-08-25 ... View more
Labels:

Re: Split json array in hive

by Contributor in Support Questions
‎06-18-2018 07:35 AM
‎06-18-2018 07:35 AM
Hi Snadeep, Did you find solution for this,if so can you please share the query here ... View more

Re: getting NULL value while using get_json_object...

by Contributor in Support Questions
‎06-16-2018 06:37 AM
‎06-16-2018 06:37 AM
Hi @Rajkumar Singh Are you to to fix the problem,even i have same requirement , for me getting null values can you share the ddl and select query for this ... View more

How can we check the count if a column family ge...

by Contributor in Support Questions
‎06-13-2018 03:20 PM
‎06-13-2018 03:20 PM
Is there any possibility where we can check the count of vesions of a column family in hbase ... View more
Labels:

Re: Error while creating hive table on top of json...

by Contributor in Support Questions
‎05-07-2018 03:47 PM
‎05-07-2018 03:47 PM
Hi @Shu Thanks for the help. i have json file as below format , i am creating ddl which works well,but when i do a select query it throwing error. json file::: {"purchaseid": {"ticid": "1496","ticlocation": "vizag","custnum": "222","Travleinfo": {"Trav": {"fname": "ramu","mname": "g","freq": {"fre": {"frequencynumber": "9","frequnecystatus": "na"}},"food": {"foodpref": [{"foodcode": "9","foodcodeSegment": "chic"},{"foodcode": "22","foodcodeSegment": "veg"},{"foodcode": "36","foodcodeSegment": "idl"}] },"Seats": { "Seat": [{"seatberth": "upper","loc": "s15"},{"seatberth": "lower","loc": "s215"},{"seatberth": "upper","loc": "s3"}] },"stations": { "station": [{"sationname": "vizag","trainnum": "c197"},{"sationname": "hyd","trainnum": "hyd187"},{"sationname": "wrgl","trainnum": "wr1822"}]}}},"Comments": {"comment": [{"commentno": "1","desc": "journey","passengerseat": { "intele": "09" },"passengerloc": { "intele": "s15" }},{"commentno": "5","desc": " food","passengerseat": { "intele": "09" },"passengerloc": { "intele": "s15" }},{"commentno": "12","desc": " service","passengerseat": { "intele": "09" },"passengerloc": { "intele": "s15" } }]}}} DDL::: Create EXTERNAL TABLE SRGMSBI1417.json14_07_05_01( purchaseid struct<ticid:string,ticlocation:string,custnum :string, Travleinfo:struct< trav:struct<fname:string,lname:string,mname:string, freq :struct<fre:array<struct<frequencynumber:string,frequnecystatus:string>>>, food :struct<foodpref:array<struct<foodcode:string,foodcodeSegment:string>>>, Seats :struct<Seat:array<struct<seatberth:string,loc:string>>>, stations :struct<station:array<struct<sationname:string,trainnum:string>>> >>, Comments :struct<Comment:array<struct<commentno:string,desc:string,passengerseat :struct<intele :string>,passengerloc :struct<intele :string> >>> > ) ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' location '/user/srgmsbi1417/json14_07_05_01'; error::: Failed with exception java.io.IOException:org.apache.hadoop.hive.serde2.SerDeException: java.io.IOException: Start of Array ... View more

Re: Error while creating hive table on top of json...

by Contributor in Support Questions
‎05-07-2018 11:32 AM
‎05-07-2018 11:32 AM
@Shu Thanks for the help. i have json file as below format , i am creating ddl which works well,but when i do a select query it throwing error. json file::: {"purchaseid": {"ticid": "1496","ticlocation": "vizag","custnum": "222","Travleinfo": {"Trav": {"fname": "ramu","mname": "g","freq": {"fre": {"frequencynumber": "9","frequnecystatus": "na"}},"food": {"foodpref": [{"foodcode": "9","foodcodeSegment": "chic"},{"foodcode": "22","foodcodeSegment": "veg"},{"foodcode": "36","foodcodeSegment": "idl"}] },"Seats": { "Seat": [{"seatberth": "upper","loc": "s15"},{"seatberth": "lower","loc": "s215"},{"seatberth": "upper","loc": "s3"}] },"stations": { "station": [{"sationname": "vizag","trainnum": "c197"},{"sationname": "hyd","trainnum": "hyd187"},{"sationname": "wrgl","trainnum": "wr1822"}]}}},"Comments": {"comment": [{"commentno": "1","desc": "journey","passengerseat": { "intele": "09" },"passengerloc": { "intele": "s15" }},{"commentno": "5","desc": " food","passengerseat": { "intele": "09" },"passengerloc": { "intele": "s15" }},{"commentno": "12","desc": " service","passengerseat": { "intele": "09" },"passengerloc": { "intele": "s15" } }]}}} DDL::: Create EXTERNAL TABLE SRGMSBI1417.json14_07_05_01( purchaseid struct<ticid:string,ticlocation:string,custnum :string, Travleinfo:struct< trav:struct<fname:string,lname:string,mname:string, freq :struct<fre:array<struct<frequencynumber:string,frequnecystatus:string>>>, food :struct<foodpref:array<struct<foodcode:string,foodcodeSegment:string>>>, Seats :struct<Seat:array<struct<seatberth:string,loc:string>>>, stations :struct<station:array<struct<sationname:string,trainnum:string>>> >>, Comments :struct<Comment:array<struct<commentno:string,desc:string,passengerseat :struct<intele :string>,passengerloc :struct<intele :string> >>> > ) ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' location '/user/srgmsbi1417/json14_07_05_01'; error::: Failed with exception java.io.IOException:org.apache.hadoop.hive.serde2.SerDeException: java.io.IOException: Start of Array ... View more

Re: Error while creating hive table on top of json...

by Contributor in Support Questions
‎05-01-2018 05:23 AM
‎05-01-2018 05:23 AM
hi @Shu still i am getting the same error,please find the attachment of errorerror.png ... View more

Error while creating hive table on top of json dat...

by Contributor in Support Questions
‎05-01-2018 04:18 AM
‎05-01-2018 04:18 AM
Hi , I am trying to create a hive table on top of son data file but getting the below error.Below are my json file,hive ddl and error. Json file:::::::: { "purchaseid": { "ticketnumber": "23546852222", "location": "vizag", "Travelerhistory": { "trav": { "fname": "ramu", "lname": "gogi", "travelingarea": { "destination": { "stationid": "KAJKL", "stationname": "hyd" } }, "food": { "foodpref": [{ "foodcode": "CK567", "foodcodeSegment": "NOVEG" }, { "foodcode": "MM98", "foodcodeSegment": "VEG" } ] } } } } } Hive DDL:: add jar /home/**********/json-serde-1.3.7-jar-with-dependencies.jar; CREATE external TABLE ds1414( ticketnumber string, location string, Travelerhistory ARRAY<struct<trav :struct<fname:string,lname:string, travelingarea :ARRAY<struct<destination :struct<stationid:string,stationname:string>>>, food :ARRAY<struct<foodpref :struct<foodcode:string,foodcodeSegment:string>>> >>> ) ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' location '/user/***********/json2/' ; Error::: select * from ds1414; OK Failed with exception java.io.IOException:org.apache.hadoop.hive.serde2.SerDeException: org.codehaus.jackson.JsonParseException: Unexpected end-of-input: expected close marker for OBJECT (from [Source: java.io.ByteArrayInputStream@205df5dc; line: 1, column: 0]) at [Source: java.io.ByteArrayInputStream@205df5dc; line: 1, column: 3] Time taken: 0.212 seconds ... View more
Labels:

Hbase row filter from hbase shell

by Contributor in Support Questions
‎04-12-2018 06:05 PM
‎04-12-2018 06:05 PM
Hi , I am trying do filter on rowkey which is combination of 3 columns in my case which are separated by a space.i want to query on filter conditions on 2 columns..below is how my data looks in hbase and hbase queries i am using. i want to query on rowkey which contains values row1 and rad.how can i do that hbase(main):003:0> scan 'testspaces' ROW COLUMN+CELL row1 gud column=cf:a2, timestamp=1523548834897, value=value1e row1 rad column=cf:a2, timestamp=1523548716606, value=value1e row1 sec column=cf:a2, timestamp=1523548822010, value=value1e row2 rad exam column=cf:a3, timestamp=1523548741273, value=vale1e 4 row(s) in 0.0150 seconds scan 'testspaces',{FILTER =>"(PrefixFilter('row1') OR PrefixFilter('sec')"} scan 'testspaces', { LIMIT => 3, FILTER => "org.apache.hadoop.hbase.filter.RowFilter( =, 'row1') AND ValueFilter( =, 'sec'} scan 'testspaces', {FILTER => org.apache.hadoop.hbase.filter.RowFilter.new(CompareFilter::CompareOp.valueOf('EQUAL'),SubstringComparator.new("sec")) AND org.apache.hadoop.hbase.filter.RowFilter.new(CompareFilter::CompareOp.valueOf('EQUAL'),SubstringComparator.new("row1"))} ... View more
Labels:

Re: How to create hive ddl on top xml using the xm...

by Contributor in Support Questions
‎03-12-2018 05:50 PM
‎03-12-2018 05:50 PM
Hi Trivedi ,Thanks for your reply.Can you please answer this in details because i was able to create ddl on other part of xml which consists of array and structs,but if you see the xml i attached segments is a tag which contains segment within it and xse:type is manager,vp,svp. Can you please help in creating the exact schema for this ... View more
Contact Me
Online
Offline
Last Visited
‎06-18-2018 11:09 AM
Community Statistics
Member Since ‎05-24-2018 08:59 AM
Last Visited ‎06-18-2018 11:09 AM
Posts 25
Kudos received 1