Hi Folks, I am running query on hive ....seems from logs , it is failing because of erroneous record in table [*date_dim*] causing casting exception ....when I looked for other records in [*date_dim*], nothing different in particular record(see sample records at last)
Can you please help me understand why error for this particular record ? how it can be resolved ? Any help is highly appreciated !!! *Hive query : * *SELECT 'store' AS channel, 'ss_cdemo_sk' AS col_name, d_year, d_qoy, i_category, ss_ext_sales_price AS ext_sales_price FROM store_sales , item , date_dim WHERE ss_cdemo_sk IS NULL AND ss_sold_date_sk = d_date_sk AND ss_item_sk = i_item_sk ;* *Hive logs : * Query ID = root_20150922151717_a94d4679-224b-41f3-8336-a799d4ebedab Total jobs = 4 .... Launching Job 3 out of 4 Number of reduce tasks is set to 0 since there's no reduce operator Starting Job = job_1441794795162_13426, Tracking URL = http://IMPETUS-DSRV03.impetus.co.in:8035/proxy/application_1441794795162_13426/ Kill Command = /opt/hes/hadoop/hadoop-2.6.0//bin/hadoop job -kill job_1441794795162_13426 Hadoop job information for Stage-6: number of mappers: 1; number of reducers: 0 2015-09-22 15:18:34,291 Stage-6 map = 0%, reduce = 0% 2015-09-22 15:18:54,541 Stage-6 map = 100%, reduce = 0% Ended Job = job_1441794795162_13426 with errors Error during job, obtaining debugging information... Examining task ID: task_1441794795162_13426_m_000000 (and more) from job job_1441794795162_13426 Task with the most failures(4): ----- Task ID: task_1441794795162_13426_m_000000 URL: http://IMPETUS-DSRV03.impetus.co.in:8088/taskdetails.jsp?jobid=job_1441794795162_13426&tipid=task_1441794795162_13426_m_000000 ----- Diagnostic Messages for this Task: Error: java.lang.RuntimeException: org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime Error while processing row {"d_date_sk":2452538,"d_date_id":"AAAAAAAAKDMGFCAA","d_date":"2002-09-20","d_month_seq":1232,"d_week_seq":5360,"d_quarter_seq":412,"d_year":2002,"d_dow":5,"d_moy":9,"d_dom":20,"d_qoy":3,"d_fy_year":2002,"d_fy_quarter_seq":412,"d_fy_week_seq":5360,"d_day_name":"Friday ","d_quarter_name":"N","d_holiday":"2002Q3","d_weekend":"N","d_following_holiday":"Y","d_first_dom":2452761,"d_last_dom":2452519,"d_same_day_ly":2452447,"d_same_day_lq":2452173,"d_current_day":"N","d_current_week":"N","d_current_month":"N","d_current_quarter":"N","d_current_year":"N"} 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 {"d_date_sk":2452538,"d_date_id":"AAAAAAAAKDMGFCAA","d_date":"2002-09-20","d_month_seq":1232,"d_week_seq":5360,"d_quarter_seq":412,"d_year":2002,"d_dow":5,"d_moy":9,"d_dom":20,"d_qoy":3,"d_fy_year":2002,"d_fy_quarter_seq":412,"d_fy_week_seq":5360,"d_day_name":"Friday ","d_quarter_name":"N","d_holiday":"2002Q3","d_weekend":"N","d_following_holiday":"Y","d_first_dom":2452761,"d_last_dom":2452519,"d_same_day_ly":2452447,"d_same_day_lq":2452173,"d_current_day":"N","d_current_week":"N","d_current_month":"N","d_current_quarter":"N","d_current_year":"N"} 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.hive.serde2.io.HiveDecimalWritable cannot be cast to org.apache.hadoop.io.IntWritable 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.FilterOperator.processOp(FilterOperator.java:120) 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.hive.serde2.io.HiveDecimalWritable cannot be cast to org.apache.hadoop.io.IntWritable at org.apache.hadoop.hive.serde2.objectinspector.primitive.WritableIntObjectInspector.get(WritableIntObjectInspector.java:36) at org.apache.hadoop.hive.ql.udf.generic.GenericUDFOPEqual.evaluate(GenericUDFOPEqual.java:84) at org.apache.hadoop.hive.ql.exec.ExprNodeGenericFuncEvaluator._evaluate(ExprNodeGenericFuncEvaluator.java:185) at org.apache.hadoop.hive.ql.exec.ExprNodeEvaluator.evaluate(ExprNodeEvaluator.java:77) at org.apache.hadoop.hive.ql.exec.ExprNodeGenericFuncEvaluator$DeferredExprObject.get(ExprNodeGenericFuncEvaluator.java:86) at org.apache.hadoop.hive.ql.udf.generic.GenericUDFOPAnd.evaluate(GenericUDFOPAnd.java:68) at org.apache.hadoop.hive.ql.exec.ExprNodeGenericFuncEvaluator._evaluate(ExprNodeGenericFuncEvaluator.java:185) at org.apache.hadoop.hive.ql.exec.ExprNodeEvaluator.evaluate(ExprNodeEvaluator.java:77) at org.apache.hadoop.hive.ql.exec.ExprNodeEvaluator.evaluate(ExprNodeEvaluator.java:65) at org.apache.hadoop.hive.ql.exec.FilterOperator.processOp(FilterOperator.java:106) 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.genUniqueJoinObject(CommonJoinOperator.java:651) at org.apache.hadoop.hive.ql.exec.CommonJoinOperator.genUniqueJoinObject(CommonJoinOperator.java:654) at org.apache.hadoop.hive.ql.exec.CommonJoinOperator.checkAndGenObject(CommonJoinOperator.java:750) at org.apache.hadoop.hive.ql.exec.MapJoinOperator.processOp(MapJoinOperator.java:299) ... 15 more FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask ATTEMPT: Execute BackupTask: org.apache.hadoop.hive.ql.exec.mr.MapRedTask ....... Stage-Stage-8: Map: 2 Cumulative CPU: 42.59 sec HDFS Read: 122983367 HDFS Write: 5267230 SUCCESS Stage-Stage-6: Map: 1 HDFS Read: 0 HDFS Write: 0 FAIL Stage-Stage-2: Map: 2 Reduce: 1 Cumulative CPU: 8.78 sec HDFS Read: 15648221 HDFS Write: 8106 SUCCESS Total MapReduce CPU Time Spent: 51 seconds 370 msec OK channel col_name d_year d_qoy i_category ext_sales_price store ss_cdemo_sk 1998 1 Sports NULL ..... . . . .. *Sample data of table from date_dim ( including erroneous record )* hive > select * from date_dim ; date_dim.d_date_sk date_dim.d_date_id date_dim.d_date date_dim.d_month_seq date_dim.d_week_seq date_dim.d_quarter_seq date_dim.d_year date_dim.d_dow date_dim.d_moy date_dim.d_dom date_dim.d_qoy date_dim.d_fy_year date_dim.d_fy_quarter_seq date_dim.d_fy_week_seq date_dim.d_day_name date_dim.d_quarter_name date_dim.d_holiday date_dim.d_weekend date_dim.d_following_holiday date_dim.d_first_dom date_dim.d_last_dom date_dim.d_same_day_ly date_dim.d_same_day_lq date_dim.d_current_day date_dim.d_current_week date_dim.d_current_month date_dim.d_current_quarter date_dim.d_current_year *2452538 AAAAAAAAKDMGFCAA 2002-09-20 1232 5360 412 2002 5 9 20 3 2002 412 5360 Friday N 2002Q3 N Y 2452761 2452519 2452447 2452173 N N N NN* 2431208 AAAAAAAAIOIBFCAA 1944-04-27 531 2313 178 1944 4 4 27 2 1944 178 2313 Thursday N 1944Q2 N N 2431272 2431182 2431117 2430842 N N N NN 2456494 AAAAAAAAOKLHFCAA 2013-07-20 1362 5925 455 2013 6 7 20 3 2013 455 5925 Saturday N 2013Q3 N Y 2456655 2456475 2456403 2456129 N N N NN 2481780 AAAAAAAAEHONFCAA 2082-10-12 2193 9537 732 2082 1 10 12 4 2082 732 9537 Monday N 2082Q4 N N 2482041 2481769 2481688 2481415 N N N NN Regards Sanjiv Singh Mob : +091 9990-447-339
