Join Error

["@Sanjiv Singh" <sa...@gmail.com>]

Hi All,

I have simple query of LEFT OUT JOIN which perform join of two table of
common column 'region_no' of type int.

(I am using Hive 1.0)

*Query :*

SELECT mem.member_no, mem.region_no,reg.region_name,reg.city
> FROM  member_nz AS mem  LEFT OUTER JOIN  region_nz   AS reg    ON
> mem.region_no = reg.region_no
> WHERE reg.region_no is not NULL
> limit 10;
>




*It is giving casting error. Few other observation :*

*If I Replace LEFT OUTER JOIN with JOIN, it works well.(obvious)*


*It tried to add CAST STRING explicit in query but doesn't work for me .*
Help me understand and resolve the issue.


*Error :*

Diagnostic Messages for this Task:
Error: java.lang.RuntimeException:
org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime Error while
processing row
{"member_no":9744,"lastname":"TOLSKY","firstname":"YONRLR","middleinitial":"
","street":"999 Ninth St.","city":" ","state_prov":"
","country":"Ke","mail_code":"209","phone_no":"8712346590","photograph":null,"issue_dt":"1999-08-17
10:45:58.097","expr_dt":"1999-09-06
10:45:58.097","region_no":9,"corp_no":null,"prev_balance":20000.78,"curr_balance":12.55,"member_code":"
"}
    at org.apache.hadoop.hive.ql.exec.mr.ExecMapper.map(ExecMapper.java:185)
    at org.apache.hadoop.mapred.MapRunner.run(MapRunner.java:54)
    at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:450)
    at org.apache.hadoop.mapred.MapTask.run(MapTask.java:343)
    at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:163)
    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:1628)
    at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158)
Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime
Error while processing row
{"member_no":9744,"lastname":"TOLSKY","firstname":"YONRLR","middleinitial":"
","street":"999 Ninth St.","city":" ","state_prov":"
","country":"Ke","mail_code":"209","phone_no":"8712346590","photograph":null,"issue_dt":"1999-08-17
10:45:58.097","expr_dt":"1999-09-06
10:45:58.097","region_no":9,"corp_no":null,"prev_balance":20000.78,"curr_balance":12.55,"member_code":"
"}
    at
org.apache.hadoop.hive.ql.exec.MapOperator.process(MapOperator.java:503)
    at org.apache.hadoop.hive.ql.exec.mr.ExecMapper.map(ExecMapper.java:176)
    ... 8 more
Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: Unexpected
exception: org.apache.hadoop.io.IntWritable cannot be cast to
org.apache.hadoop.io.Text
    at
org.apache.hadoop.hive.ql.exec.MapJoinOperator.processOp(MapJoinOperator.java:311)
    at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:815)
    at
org.apache.hadoop.hive.ql.exec.TableScanOperator.processOp(TableScanOperator.java:95)
    at
org.apache.hadoop.hive.ql.exec.MapOperator$MapOpCtx.forward(MapOperator.java:157)
    at
org.apache.hadoop.hive.ql.exec.MapOperator.process(MapOperator.java:493)
    ... 9 more
Caused by: java.lang.ClassCastException: org.apache.hadoop.io.IntWritable
cannot be cast to org.apache.hadoop.io.Text
    at
org.apache.hadoop.hive.serde2.objectinspector.primitive.WritableStringObjectInspector.getPrimitiveWritableObject(WritableStringObjectInspector.java:41)
    at
org.apache.hadoop.hive.serde2.lazy.LazyUtils.writePrimitiveUTF8(LazyUtils.java:225)
    at
org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe.serialize(LazySimpleSerDe.java:485)
    at
org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe.serializeField(LazySimpleSerDe.java:438)
    at
org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe.doSerialize(LazySimpleSerDe.java:422)
    at
org.apache.hadoop.hive.serde2.AbstractEncodingAwareSerDe.serialize(AbstractEncodingAwareSerDe.java:50)
    at
org.apache.hadoop.hive.ql.exec.FileSinkOperator.processOp(FileSinkOperator.java:660)
    at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:815)
    at
org.apache.hadoop.hive.ql.exec.LimitOperator.processOp(LimitOperator.java:51)
    at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:815)
    at
org.apache.hadoop.hive.ql.exec.SelectOperator.processOp(SelectOperator.java:84)
    at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:815)
    at
org.apache.hadoop.hive.ql.exec.FilterOperator.processOp(FilterOperator.java:120)
    at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:815)
    at
org.apache.hadoop.hive.ql.exec.CommonJoinOperator.internalForward(CommonJoinOperator.java:638)
    at
org.apache.hadoop.hive.ql.exec.CommonJoinOperator.genAllOneUniqueJoinObject(CommonJoinOperator.java:670)
    at
org.apache.hadoop.hive.ql.exec.CommonJoinOperator.checkAndGenObject(CommonJoinOperator.java:748)
    at
org.apache.hadoop.hive.ql.exec.MapJoinOperator.processOp(MapJoinOperator.java:299)
    ... 13 more


FAILED: Execution Error, return code 2 from
org.apache.hadoop.hive.ql.exec.mr.MapRedTask
MapReduce Jobs Launched:
Stage-Stage-3: Map: 2   HDFS Read: 0 HDFS Write: 0 FAIL
Total MapReduce CPU Time Spent: 0 msec



Regards
Sanjiv Singh
Mob :  +091 9990-447-339