changeprasant
commented
8 years ago Please find here the below attached data.zip to load into the table for testing. data.zip
Open changeprasant opened 8 years ago
changeprasant
commented
8 years ago Please find here the below attached data.zip to load into the table for testing. data.zip
CREATE TABLE Patient ( active boolean, address array<struct<city:string, line:array, postalcode:string, state:string>>,
birthdate string,
extension array<struct<url:string, valuecodeableconcept:struct<coding:array<struct<code:string, display:string, system:string>>>>>,
gender string,
id string,
identifier array<struct<system:string, type:struct<coding:array<struct<code:string, system:string>>, text:string>, use:string, value:string>>,
meta struct<lastupdated:string, versionid:string>,
name array<struct<family:array, given:array>>,
resourcetype string,
telecom array<struct<system:string, use:string, value:string>>,
text struct<division:string, status:string>)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe';
CREATE TABLE CarePlan ( activity array<struct<detail:struct<category:struct<coding:array<struct<code:string, system:string>>>, goal:array<struct>, performer:array<struct>, scheduledtiming:struct<code:struct<coding:array<struct<code:string, system:string>>>, repeat:structcount:int>>, progress:array<struct>>>,
addresses array<struct>,
author array<struct>,
contained array<struct<active:boolean, address:array<struct<city:string, country:string, line:array, postalcode:string, state:string, text:string>>, birthdate:string, gender:string, id:string, identifier:array<struct<system:string, value:string>>, name:array<struct<family:array, given:array>>, resourcetype:string, telecom:array<struct<system:string, use:string, value:string>>>>,
description string,
goal array<struct>,
id string,
identifier array<struct<system:string, value:string>>,
meta struct<lastupdated:string, versionid:string>,
period struct<period_end:string, period_start:string>,
resourcetype string,
subject structreference:string)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe';
I want to JOIN above two table;
hive> SELECT c.contained[1].identifier[0].value as Careplan_P_Id FROM CarePlan c; hive> SELECT p.ID as Patient_Id, p.identifier[0].value as identifier_value FROM Patient p;
hive> SELECT c.contained[1].identifier[0].value as Careplan_P_Id, p.ID as Patient_Id, p.identifier[0].value as identifier_value FROM CarePlan c JOIN Patient p ON (p.identifier[0].value = c.contained[1].identifier[0].value);
However getting the below error
Caused by: java.lang.RuntimeException: org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime Error while processing row [Error getting row data with exception java.lang.ClassCastException: org.openx.data.jsonserde.json.JSONObject cannot be cast to org.openx.data.jsonserde.json.JSONArray at org.openx.data.jsonserde.objectinspector.JsonListObjectInspector.getList(JsonListObjectInspector.java:38) at org.apache.hadoop.hive.serde2.SerDeUtils.buildJSONString(SerDeUtils.java:298) at org.apache.hadoop.hive.serde2.SerDeUtils.buildJSONString(SerDeUtils.java:354) at org.apache.hadoop.hive.serde2.SerDeUtils.buildJSONString(SerDeUtils.java:307) at org.apache.hadoop.hive.serde2.SerDeUtils.buildJSONString(SerDeUtils.java:354) at org.apache.hadoop.hive.serde2.SerDeUtils.getJSONString(SerDeUtils.java:198) at org.apache.hadoop.hive.serde2.SerDeUtils.getJSONString(SerDeUtils.java:184) at org.apache.hadoop.hive.ql.exec.MapOperator.toErrorMessage(MapOperator.java:588) at org.apache.hadoop.hive.ql.exec.MapOperator.process(MapOperator.java:557) at org.apache.hadoop.hive.ql.exec.tez.MapRecordSource.processRow(MapRecordSource.java:83) at org.apache.hadoop.hive.ql.exec.tez.MapRecordSource.pushRecord(MapRecordSource.java:68) at org.apache.hadoop.hive.ql.exec.tez.MapRecordProcessor.run(MapRecordProcessor.java:326) at org.apache.hadoop.hive.ql.exec.tez.TezProcessor.initializeAndRunProcessor(TezProcessor.java:150) at org.apache.hadoop.hive.ql.exec.tez.TezProcessor.run(TezProcessor.java:139) at org.apache.tez.runtime.LogicalIOProcessorRuntimeTask.run(LogicalIOProcessorRuntimeTask.java:344) at org.apache.tez.runtime.task.TezTaskRunner$TaskRunnerCallable$1.run(TezTaskRunner.java:181) at org.apache.tez.runtime.task.TezTaskRunner$TaskRunnerCallable$1.run(TezTaskRunner.java:172) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:415) at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1657) at org.apache.tez.runtime.task.TezTaskRunner$TaskRunnerCallable.callInternal(TezTaskRunner.java:172) at org.apache.tez.runtime.task.TezTaskRunner$TaskRunnerCallable.callInternal(TezTaskRunner.java:168) at org.apache.tez.common.CallableWithNdc.call(CallableWithNdc.java:36) at java.util.concurrent.FutureTask.run(FutureTask.java:262) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:745) ] at org.apache.hadoop.hive.ql.exec.tez.MapRecordSource.processRow(MapRecordSource.java:91) at org.apache.hadoop.hive.ql.exec.tez.MapRecordSource.pushRecord(MapRecordSource.java:68) at org.apache.hadoop.hive.ql.exec.tez.MapRecordProcessor.run(MapRecordProcessor.java:326) at org.apache.hadoop.hive.ql.exec.tez.TezProcessor.initializeAndRunProcessor(TezProcessor.java:150) ... 14 more