You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@spark.apache.org by "Apache Spark (JIRA)" <ji...@apache.org> on 2015/07/15 11:24:04 UTC
[jira] [Commented] (SPARK-9058) if set `spark.sql.codegen` is
true,More than 100 aggregation operation, it exceeds JVM code size limits
[ https://issues.apache.org/jira/browse/SPARK-9058?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14627774#comment-14627774 ]
Apache Spark commented on SPARK-9058:
-------------------------------------
User 'viirya' has created a pull request for this issue:
https://github.com/apache/spark/pull/7418
> if set `spark.sql.codegen` is true,More than 100 aggregation operation, it exceeds JVM code size limits
> -------------------------------------------------------------------------------------------------------
>
> Key: SPARK-9058
> URL: https://issues.apache.org/jira/browse/SPARK-9058
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 1.4.0
> Reporter: XiaoJing wang
> Fix For: 1.4.1
>
> Original Estimate: 0.2h
> Remaining Estimate: 0.2h
>
> if set “spark.sql.codegen”is true，there are more then 100 aggregation operation,it will wrong.
> The following error arise:
> {noformat}
> 15/07/15 09:34:51 ERROR executor.Executor: Exception in task 0.0 in stage 251.0 (TID 108305)
> java.util.concurrent.ExecutionException: scala.tools.reflect.ToolBoxError: reflective compilation has failed:
> Could not write class __wrapper$99$8a7b0d21732141bb96e5931945ce95f9/__wrapper$99$8a7b0d21732141bb96e5931945ce95f9$$anonfun$wrapper$1$$anon$1 because it exceeds JVM code size limits. Method apply's code too large!
> at org.spark-project.guava.util.concurrent.AbstractFuture$Sync.getValue(AbstractFuture.java:306)
> at org.spark-project.guava.util.concurrent.AbstractFuture$Sync.get(AbstractFuture.java:293)
> at org.spark-project.guava.util.concurrent.AbstractFuture.get(AbstractFuture.java:116)
> at org.spark-project.guava.util.concurrent.Uninterruptibles.getUninterruptibly(Uninterruptibles.java:135)
> at org.spark-project.guava.cache.LocalCache$Segment.getAndRecordStats(LocalCache.java:2410)
> at org.spark-project.guava.cache.LocalCache$Segment.loadSync(LocalCache.java:2380)
> at org.spark-project.guava.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2342)
> at org.spark-project.guava.cache.LocalCache$Segment.get(LocalCache.java:2257)
> at org.spark-project.guava.cache.LocalCache.get(LocalCache.java:4000)
> at org.spark-project.guava.cache.LocalCache.getOrLoad(LocalCache.java:4004)
> at org.spark-project.guava.cache.LocalCache$LocalLoadingCache.get(LocalCache.java:4874)
> at org.apache.spark.sql.catalyst.expressions.codegen.CodeGenerator.generate(CodeGenerator.scala:105)
> at org.apache.spark.sql.catalyst.expressions.codegen.CodeGenerator.generate(CodeGenerator.scala:102)
> at org.apache.spark.sql.execution.SparkPlan.newMutableProjection(SparkPlan.scala:170)
> at org.apache.spark.sql.execution.GeneratedAggregate$$anonfun$9.apply(GeneratedAggregate.scala:261)
> at org.apache.spark.sql.execution.GeneratedAggregate$$anonfun$9.apply(GeneratedAggregate.scala:246)
> at org.apache.spark.rdd.RDD$$anonfun$mapPartitions$1$$anonfun$apply$17.apply(RDD.scala:686)
> at org.apache.spark.rdd.RDD$$anonfun$mapPartitions$1$$anonfun$apply$17.apply(RDD.scala:686)
> at org.apache.spark.rdd.MapPartitionsRDD.compute(MapPartitionsRDD.scala:35)
> at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:277)
> at org.apache.spark.rdd.RDD.iterator(RDD.scala:244)
> at org.apache.spark.rdd.MapPartitionsRDD.compute(MapPartitionsRDD.scala:35)
> at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:277)
> at org.apache.spark.rdd.RDD.iterator(RDD.scala:244)
> at org.apache.spark.rdd.MapPartitionsRDD.compute(MapPartitionsRDD.scala:35)
> at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:277)
> at org.apache.spark.rdd.RDD.iterator(RDD.scala:244)
> at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:63)
> at org.apache.spark.scheduler.Task.run(Task.scala:70)
> at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:213)
> 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)
> Caused by: scala.tools.reflect.ToolBoxError: reflective compilation has failed:
> Could not write class __wrapper$99$8a7b0d21732141bb96e5931945ce95f9/__wrapper$99$8a7b0d21732141bb96e5931945ce95f9$$anonfun$wrapper$1$$anon$1 because it exceeds JVM code size limits. Method apply's code too large!
> at scala.tools.reflect.ToolBoxFactory$ToolBoxImpl$ToolBoxGlobal.throwIfErrors(ToolBoxFactory.scala:315)
> at scala.tools.reflect.ToolBoxFactory$ToolBoxImpl$ToolBoxGlobal.compile(ToolBoxFactory.scala:249)
> at scala.tools.reflect.ToolBoxFactory$ToolBoxImpl.compile(ToolBoxFactory.scala:416)
> at scala.tools.reflect.ToolBoxFactory$ToolBoxImpl.eval(ToolBoxFactory.scala:419)
> at org.apache.spark.sql.catalyst.expressions.codegen.GenerateMutableProjection$.create(GenerateMutableProjection.scala:74)
> at org.apache.spark.sql.catalyst.expressions.codegen.GenerateMutableProjection$.create(GenerateMutableProjection.scala:26)
> at org.apache.spark.sql.catalyst.expressions.codegen.CodeGenerator$$anon$1.load(CodeGenerator.scala:92)
> at org.spark-project.guava.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3599)
> at org.spark-project.guava.cache.LocalCache$Segment.loadSync(LocalCache.java:2379)
> ... 27 more
> {noformat}
> The query is:
> {code:sql}
> select
> a.user_id,
> a.phone_no,
> sum(case when a.total_fee>0 then 1 else 0 end) as bill_num,
> sum(1) as call_num,
> sum(a.call_dur_secnd_num) as CALL_DUR,
> sum(a.loc_call_fee) as BASIC_FEE,
> sum(a.roam_fee) as ROAM_CALL_FEE,
> sum(case when a.shot_no_flag='0' then a.call_dur_secnd_num else 0 end) as VPMN_IN_CALL_DUR,
> sum(case when a.shot_no_flag='0' then 1 else 0 end) as VPMN_IN_CALL_NUM,
> sum(case when a.shot_no_flag='0' then a.total_fee else 0 end) as VPMN_IN_CAL_FEE,
> sum(case when a.shot_no_flag='0' then 0 else a.call_dur_secnd_num end) as VPMN_OUT_CALL_DUR,
> sum(case when a.shot_no_flag='0' then 0 else 1 end) as VPMN_OUT_CALL_NUM,
> sum(case when a.shot_no_flag='0' then 0 else a.total_fee end) as VPMN_OUT_FEE,
> sum(a.toll_fee+a.toll_fee_2+a.toll_append_fee) as TOLL_CALL_FEE,
> sum(case when a.busy_idle_flag ='10' then a.call_dur_secnd_num else 0 end) as BUSY_CALL_DUR,
> sum(case when a.busy_idle_flag ='10' then 1 else 0 end)as BUSY_CALL_NUM,
> sum(case when a.busy_idle_flag ='10' then a.total_fee else 0 end)as BUSY_CALL_FEE,
> sum(case when a.busy_idle_flag ='11' then a.call_dur_secnd_num else 0 end) as IDLE_CALL_DUR,
> sum(case when a.busy_idle_flag ='11' then 1 else 0 end)as IDLE_CALL_NUM,
> sum(case when a.busy_idle_flag ='11' then a.total_fee else 0 end)as IDLE_CALL_FEE,
> sum(case when a.call_type ='10' then 1 else 0 end) as CALING_NUM,
> sum(case when a.call_type ='10' then a.call_dur_secnd_num else 0 end) as CALING_CALL_DUR,
> sum(case when a.call_type ='10' then a.bill_dur else 0 end) as CALING_BILL_DUR,
> sum(case when a.call_type ='10' then a.total_fee else 0 end) as CALING_FEE,
> sum(case when a.call_type ='11' then 1 else 0 end) as CALLED_NUM,
> sum(case when a.call_type ='11' then a.call_dur_secnd_num else 0 end) as CALLED_CALL_DUR,
> sum(case when a.call_type ='11' then a.bill_dur else 0 end) as CALLED_BILL_DUR,
> sum(case when a.call_type ='11' then a.total_fee else 0 end) as CALLED_FEE,
> sum(case when a.call_type ='12' then 1 else 0 end) as FORW_NUM,
> sum(case when a.call_type ='12' then a.call_dur_secnd_num else 0 end) as FORW_CALL_DUR,
> sum(case when a.call_type ='12' then a.bill_dur else 0 end) as FORW_BILL_DUR,
> sum(case when a.call_type ='12' then a.total_fee else 0 end) as FORW_FEE,
> sum(case when a.call_type ='12' and a.sort_name= 'OPP_CARR_CMC' then 1 else 0 end) as FORW_CMC_NUM,
> sum(case when a.call_type ='12' and a.sort_name= 'OPP_CARR_CMC' then a.call_dur_secnd_num else 0 end) as FORW_CMC_CALL_DUR,
> sum(case when a.call_type ='12' and a.sort_name= 'OPP_CARR_CMC' then a.bill_dur else 0 end) as FORW_CMC_BILL_DUR,
> sum(case when a.call_type ='12' and a.sort_name= 'OPP_CARR_CTC_CDMA' then 1 else 0 end) as FORW_CDMA_NUM,
> sum(case when a.call_type ='12' and a.sort_name= 'OPP_CARR_CTC_CDMA' then a.call_dur_secnd_num else 0 end) as FORW_CDMA_CALL_DUR,
> sum(case when a.call_type ='12' and a.sort_name= 'OPP_CARR_CTC_CDMA' then a.bill_dur else 0 end) as FORW_CDMA_BILL_DUR,
> sum(case when a.call_type ='12' and a.sort_name= 'OPP_CARR_CTC_PSTN' then 1 else 0 end) as FORW_CTC_FIX_NUM,
> sum(case when a.call_type ='12' and a.sort_name= 'OPP_CARR_CTC_PSTN' then a.call_dur_secnd_num else 0 end) as FORW_CTC_FIX_CALL_DUR,
> sum(case when a.call_type ='12' and a.sort_name= 'OPP_CARR_CTC_PSTN' then a.bill_dur else 0 end) as FORW_CTC_FIX_BILL_DUR,
> sum(case when a.call_type ='12' and a.sort_name= 'OPP_CARR_CUC' then 1 else 0 end) as FORW_CUC_NUM,
> sum(case when a.call_type ='12' and a.sort_name= 'OPP_CARR_CUC' then a.call_dur_secnd_num else 0 end) as FORW_CUC_CALL_DUR,
> sum(case when a.call_type ='12' and a.sort_name= 'OPP_CARR_CUC' then a.bill_dur else 0 end) as FORW_CUC_BILL_DUR,
> sum(case when a.sort_name= 'OPP_CARR_CMC' then 1 else 0 end) as INNET_CALL_NUM,
> sum(case when a.sort_name= 'OPP_CARR_CMC' then a.call_dur_secnd_num else 0 end) as INNET_CALL_DUR,
> sum(case when a.sort_name= 'OPP_CARR_CMC' then a.bill_dur else 0 end) as INNET_BILL_DUR,
> sum(case when a.sort_name= 'OPP_CARR_CMC' then 0 else 1 end) as NETO_CALL_NUM,
> sum(case when a.sort_name= 'OPP_CARR_CMC' then 0 else a.call_dur_secnd_num end) as NETO_CALL_DUR,
> sum(case when a.spec_type = 'CMC_SERVICE_NO' then 1 else 0 end) as CMC_CSR_NUM,
> sum(case when a.spec_type = 'CUC_SERVICE_NO' then 1 else 0 end) as CUC_CSR_NUM,
> sum(case when a.spec_type = 'CTC_SERVICE_NO' then 1 else 0 end) as CTC_CSR_NUM,
> sum(case when a.sort_name= 'OPP_CARR_CMC' then 0 else a.bill_dur end) as NETO_BILL_DUR,
> sum(case when a.call_type ='10' and a.sort_name in ('OPP_CARR_CTC_CDMA','OPP_CARR_CTC_PSTN') then 1 else 0 end) as CTC_CALING_NUM,
> sum(case when a.call_type ='10' and a.sort_name in ('OPP_CARR_CTC_CDMA','OPP_CARR_CTC_PSTN') then a.call_dur_secnd_num else 0 end) as CTC_CALING_CALL_DUR,
> sum(case when a.call_type ='10' and a.sort_name in ('OPP_CARR_CTC_CDMA','OPP_CARR_CTC_PSTN') then a.bill_dur else 0 end) as CTC_CALING_BILL_DUR,
> sum(case when a.call_type ='10' and a.sort_name= 'OPP_CARR_CUC' then 1 else 0 end) as CUC_CALING_NUM,
> sum(case when a.call_type ='10' and a.sort_name= 'OPP_CARR_CUC' then a.call_dur_secnd_num else 0 end) as CUC_CALING_CALL_DUR,
> sum(case when a.call_type ='10' and a.sort_name= 'OPP_CARR_CUC' then bill_dur else 0 end) as CUC_CALING_BILL_DUR,
> sum(case when a.call_type ='11' and a.sort_name in ('OPP_CARR_CTC_CDMA','OPP_CARR_CTC_PSTN')then 1 else 0 end) as CTC_CALLED_NUM,
> sum(case when a.call_type ='11' and a.sort_name in ('OPP_CARR_CTC_CDMA','OPP_CARR_CTC_PSTN') then a.call_dur_secnd_num else 0 end) as CTC_CALLED_CALL_DUR,
> sum(case when a.call_type ='11' and a.sort_name in ('OPP_CARR_CTC_CDMA','OPP_CARR_CTC_PSTN') then a.bill_dur else 0 end) as CTC_CALLED_BILL_DUR,
> sum(case when a.call_type ='11' and a.sort_name= 'OPP_CARR_CUC' then 1 else 0 end) as CUC_CALLED_NUM,
> sum(case when a.call_type ='11' and a.sort_name= 'OPP_CARR_CUC' then a.call_dur_secnd_num else 0 end) as CUC_CALLED_CALL_DUR,
> sum(case when a.call_type ='11' and a.sort_name= 'OPP_CARR_CUC' then a.bill_dur else 0 end) as CUC_CALLED_BILL_DUR,
> sum(case when a.call_type ='10' and a.TOLL_TYPE in ('10','11','12','13','14') then 1 else 0 end ) as LCALING_CALL_NUM,
> sum(case when a.call_type ='10' and a.TOLL_TYPE in ('10','11','12','13','14') then a.call_dur_secnd_num else 0 end ) as LCALING_CALL_DUR,
> sum(case when a.call_type ='10' and a.TOLL_TYPE in ('10','11','12','13','14') then a.bill_dur else 0 end ) as LCALING_BILL_DUR,
> sum(case when a.call_type ='11' and a.TOLL_TYPE in ('10','11','12','13','14') then 1 else 0 end ) as LCALLED_CALL_NUM,
> sum(case when a.call_type ='11' and a.TOLL_TYPE in ('10','11','12','13','14') then a.call_dur_secnd_num else 0 end ) as LCALLED_CALL_DUR,
> sum(case when a.call_type ='11' and a.TOLL_TYPE in ('10','11','12','13','14') then a.bill_dur else 0 end ) as LCALLED_BILL_DUR,
> sum(case when a.call_type ='10' and a.TOLL_TYPE not in ('10','11','12','13','14') then 1 else 0 end ) as TOLL_CALING_NUM,
> sum(case when a.call_type ='10' and a.TOLL_TYPE not in ('10','11','12','13','14') then a.call_dur_secnd_num else 0 end ) as TOLL_CALING_CALL_DUR,
> sum(case when a.call_type ='10' and a.TOLL_TYPE not in ('10','11','12','13','14') then a.bill_dur else 0 end ) as TOLL_CALING_BILL_DUR,
> sum(case when a.call_type ='11' and a.TOLL_TYPE not in ('10','11','12','13','14') then 1 else 0 end ) as TOLL_CALLED_NUM,
> sum(case when a.call_type ='11' and a.TOLL_TYPE not in ('10','11','12','13','14') then a.call_dur_secnd_num else 0 end ) as TOLL_CALLED_CALL_DUR,
> sum(case when a.call_type ='11' and a.TOLL_TYPE not in ('10','11','12','13','14') then a.bill_dur else 0 end ) as TOLL_CALLED_BILL_DUR,
> sum(case when a.dial_type='11' and a.TOLL_TYPE not in ('10','11','12','13','14') then 1 else 0 end ) as IP_TOLL_CALL_NUM,
> sum(case when a.dial_type='11' and a.TOLL_TYPE not in ('10','11','12','13','14') then a.call_dur_secnd_num else 0 end ) as IP_TOLL_CALL_DUR,
> sum(case when a.dial_type='11' and a.TOLL_TYPE not in ('10','11','12','13','14') then a.bill_dur else 0 end ) as IP_TOLL_BILL_DUR,
> sum(case when a.call_type ='10' and a.toll_type='15' then 1 else 0 end) as PROV_IN_CALING_NUM,
> sum(case when a.call_type ='10' and a.toll_type='15' then a.call_dur_secnd_num else 0 end) as PROV_IN_CALING_CALL_DUR,
> sum(case when a.call_type ='10' and a.toll_type='15' then a.bill_dur else 0 end) as PROV_IN_CALING_BILL_DUR,
> sum(case when a.call_type ='11' and a.toll_type='15' then 1 else 0 end) as PROV_IN_CALLED_NUM,
> sum(case when a.call_type ='11' and a.toll_type='15' then a.call_dur_secnd_num else 0 end) as PROV_IN_CALLED_CALL_DUR,
> sum(case when a.call_type ='11' and a.toll_type='15' then a.bill_dur else 0 end) as PROV_IN_CALLED_BILL_DUR,
> sum(case when a.call_type ='10' and a.toll_type='16' then 1 else 0 end) as LAND_CALING_NUM,
> sum(case when a.call_type ='10' and a.toll_type='16' then call_dur_secnd_num else 0 end) as LAND_CALING_CALL_DUR,
> sum(case when a.call_type ='10' and a.toll_type='16' then bill_dur else 0 end) as LAND_CALING_BILL_DUR,
> sum(case when a.call_type ='11' and a.toll_type='16' then 1 else 0 end) as LAND_CALLED_NUM,
> sum(case when a.call_type ='11' and a.toll_type='16' then call_dur_secnd_num else 0 end) as LAND_CALLED_CALL_DUR,
> sum(case when a.call_type ='11' and a.toll_type='16' then bill_dur else 0 end) as LAND_CALLED_BILL_DUR,
> sum(case when a.call_type ='10' and a.toll_type in ('17','21','22') then 1 else 0 end) as INTER_CALING_NUM,
> sum(case when a.call_type ='10' and a.toll_type in ('17','21','22') then a.call_dur_secnd_num else 0 end) as INTER_CALING_CALL_DUR,
> sum(case when a.call_type ='10' and a.toll_type in ('17','21','22') then a.bill_dur else 0 end) as INTER_CALING_BILL_DUR,
> sum(case when a.call_type ='11' and a.toll_type in ('17','21','22') then 1 else 0 end) as INTER_CALLED_NUM,
> sum(case when a.call_type ='11' and a.toll_type in ('17','21','22') then a.call_dur_secnd_num else 0 end) as INTER_CALLED_CALL_DUR,
> sum(case when a.call_type ='11' and a.toll_type in ('17','21','22') then a.bill_dur else 0 end) as INTER_CALLED_BILL_DUR,
> sum(case when a.call_type ='10' and a.toll_type in ('18','19','20') then 1 else 0 end) as HMT_CALING_NUM,
> sum(case when a.call_type ='10' and a.toll_type in ('18','19','20') then a.call_dur_secnd_num else 0 end) as HMT_CALING_CALL_DUR,
> sum(case when a.call_type ='10' and a.toll_type in ('18','19','20') then a.bill_dur else 0 end) as HMT_CALING_BILL_DUR,
> sum(case when a.call_type ='11' and a.toll_type in ('18','19','20') then 1 else 0 end) as HMT_CALLED_NUM,
> sum(case when a.call_type ='11' and a.toll_type in ('18','19','20') then a.call_dur_secnd_num else 0 end) as HMT_CALLED_CALL_DUR,
> sum(case when a.call_type ='11' and a.toll_type in ('18','19','20') then a.bill_dur else 0 end) as HMT_CALLED_BILL_DUR,
> sum(case when a.call_type ='10' and a.roam_type in ('10','11','13') then 1 else 0 end ) as NO_RCALING_NUM,
> sum(case when a.call_type ='10' and a.roam_type in ('10','11','13') then a.call_dur_secnd_num else 0 end ) as NO_RCALING_CALL_DUR,
> sum(case when a.call_type ='10' and a.roam_type in ('10','11','13') then a.bill_dur else 0 end ) as NO_RCALING_BILL_DUR,
> sum(case when a.call_type ='11' and a.roam_type in ('10','11','13') then 1 else 0 end ) as NO_RCALLED_NUM,
> sum(case when a.call_type ='11' and a.roam_type in ('10','11','13') then a.call_dur_secnd_num else 0 end ) as NO_RCALLED_CALL_DUR,
> sum(case when a.call_type ='11' and a.roam_type in ('10','11','13') then a.bill_dur else 0 end ) as NO_RCALLED_BILL_DUR,
> sum(case when a.call_type ='10' and a.roam_type not in ('10','11','13') then 1 else 0 end ) as RCALING_NUM,
> sum(case when a.call_type ='10' and a.roam_type not in ('10','11','13') then a.call_dur_secnd_num else 0 end ) as RCALING_CALL_DUR,
> sum(case when a.call_type ='10' and a.roam_type not in ('10','11','13') then a.bill_dur else 0 end ) as RCALING_BILL_DUR,
> sum(case when a.call_type ='11' and a.roam_type not in ('10','11','13') then 1 else 0 end ) as RCALLED_NUM,
> sum(case when a.call_type ='11' and a.roam_type not in ('10','11','13') then a.call_dur_secnd_num else 0 end ) as RCALLED_CALL_DUR,
> sum(case when a.call_type ='11' and a.roam_type not in ('10','11','13') then a.bill_dur else 0 end ) as RCALLED_BILL_DUR,
> sum(case when a.call_type ='10' and a.roam_type ='14' then 1 else 0 end ) as PROV_IN_RCALING_NUM,
> sum(case when a.call_type ='10' and a.roam_type ='14' then a.call_dur_secnd_num else 0 end ) as PROV_IN_RCALING_CALL_DUR,
> sum(case when a.call_type ='10' and a.roam_type ='14' then a.bill_dur else 0 end ) as PROV_IN_RCALING_BILL_DUR,
> sum(case when a.call_type ='11' and a.roam_type ='14' then 1 else 0 end ) as PROV_IN_RCALLED_NUM,
> sum(case when a.call_type ='11' and a.roam_type ='14' then a.call_dur_secnd_num else 0 end ) as PROV_IN_RCALLED_CALL_DUR,
> sum(case when a.call_type ='11' and a.roam_type ='14' then a.bill_dur else 0 end ) as PROV_IN_RCALLED_BILL_DUR,
> sum(case when a.call_type ='10' and a.roam_type ='06' then 1 else 0 end ) as LAND_RCALING_NUM,
> sum(case when a.call_type ='10' and a.roam_type ='06' then a.call_dur_secnd_num else 0 end ) as LAND_RCALING_CALL_DUR,
> sum(case when a.call_type ='10' and a.roam_type ='06' then a.bill_dur else 0 end ) as LAND_RCALING_BILL_DUR,
> sum(case when a.call_type ='11' and a.roam_type ='06' then 1 else 0 end ) as LAND_RCALLED_NUM,
> sum(case when a.call_type ='11' and a.roam_type ='06' then a.call_dur_secnd_num else 0 end ) as LAND_RCALLED_CALL_DUR,
> sum(case when a.call_type ='11' and a.roam_type ='06' then a.bill_dur else 0 end ) as LAND_RCALLED_BILL_DUR,
> sum(case when a.call_type ='10' and a.roam_type in ('18','22') then 1 else 0 end ) as INTER_ROAM_CALING_NUM,
> sum(case when a.call_type ='10' and a.roam_type in ('18','22') then a.call_dur_secnd_num else 0 end ) as INTER_RCALING_CALL_DUR,
> sum(case when a.call_type ='10' and a.roam_type in ('18','22') then a.bill_dur else 0 end ) as INTER_RCALING_BILL_DUR,
> sum(case when a.call_type ='11' and a.roam_type in ('18','22') then 1 else 0 end ) as INTER_RCALLED_NUM,
> sum(case when a.call_type ='11' and a.roam_type in ('18','22') then a.call_dur_secnd_num else 0 end ) as INTER_RCALLED_CALL_DUR,
> sum(case when a.call_type ='11' and a.roam_type in ('18','22') then a.bill_dur else 0 end ) as INTER_RCALLED_BILL_DUR,
> sum(case when a.call_type ='10' and a.roam_type in ('19','20','21') then 1 else 0 end ) as HMT_RCALING_CALL_NUM,
> sum(case when a.call_type ='10' and a.roam_type in ('19','20','21') then a.call_dur_secnd_num else 0 end ) as HMT_RCALING_CALL_DUR,
> sum(case when a.call_type ='10' and a.roam_type in ('19','20','21') then a.bill_dur else 0 end ) as HMT_RCALING_BILL_DUR,
> sum(case when a.call_type ='11' and a.roam_type in ('19','20','21') then 1 else 0 end ) as HMT_RCALLED_CALL_NUM,
> sum(case when a.call_type ='11' and a.roam_type in ('19','20','21') then a.call_dur_secnd_num else 0 end ) as HMT_RCALLED_CALL_DUR,
> sum(case when a.call_type ='11' and a.roam_type in ('19','20','21') then a.bill_dur else 0 end ) as HMT_RCALLED_BILL_DUR,
> sum(case when a.roam_type in ('10','11','13') then 1 else 0 end ) as LCALL_NUM,
> sum(case when a.roam_type in ('10','11','13') then a.call_dur_secnd_num else 0 end ) as LCALL_DUR,
> sum(case when a.roam_type in ('10','11','13') then a.bill_dur else 0 end ) as LBILL_DUR,
> sum(case when a.roam_type in ('10','11','13') and a.dial_type='11' then a.bill_dur else 0 end ) as NO_RIP_BILL_DUR,
> sum(case when a.roam_type not in ('10','11','13') and a.dial_type='11' then a.bill_dur else 0 end ) as ROAM_IP_BILL_DUR,
> sum(case when a.roam_type in ('10','11','13') and a.TOLL_TYPE in ('10','11','12','13','14') then a.bill_dur else 0 end ) as NO_RLBILL_DUR,
> sum(case when a.roam_type in ('10','11','13') and a.toll_type in ('17','21','22') then a.bill_dur else 0 end ) as NO_RINTER_BILL_DUR,
> sum(case when a.roam_type in ('10','11','13') and a.toll_type in ('18','19','20') then a.bill_dur else 0 end ) as NO_RHMT_BILL_DUR,
> sum(case when a.roam_type in ('10','11','13') and a.toll_type in ('16') then a.bill_dur else 0 end ) as NO_RLAND_BILL_DUR,
> sum(case when a.roam_type in ('10','11','13') and a.toll_type in ('15') then a.bill_dur else 0 end ) as NO_RPROV_IN_BILL_DUR,
> sum(case when a.dial_type='11' and a.toll_type in ('17','21','22') then a.bill_dur else 0 end ) as INTER_IP_BILL_DUR,
> sum(case when a.dial_type='11' and a.toll_type in ('18','19','20') then a.bill_dur else 0 end ) as HMT_IP_BILL_DUR,
> sum(case when a.roam_type not in ('10','11','13') and a.call_type ='10' and a.toll_type in ('17','21','22') then a.bill_dur else 0 end ) as RCALLING_INTER_BILL_DUR,
> sum(case when a.roam_type not in ('10','11','13') and a.call_type ='10' and a.toll_type in ('18','19','20') then a.bill_dur else 0 end ) as RCALLING_HMT_BILL_DUR,
> sum(case when a.roam_type not in ('10','11','13') and a.call_type ='10' and a.toll_type in ('16') then a.bill_dur else 0 end ) as RCALLING_LAND_BILL_DUR,
> sum(case when a.roam_type not in ('10','11','13') and a.call_type ='10' and a.toll_type in ('15') then a.bill_dur else 0 end ) as RCALLING_PROV_IN_BILL_DUR,
> sum(case when a.dial_type='11' and a.toll_type in ('16') then a.bill_dur else 0 end ) as LAND_IP_BILL_DUR,
> sum(case when a.dial_type='11' and a.toll_type in ('15') then a.bill_dur else 0 end ) as PROV_IN_IP_BILL_DUR,
> sum(case when a.call_type ='11' and a.sort_name= 'OPP_CARR_CMC' then a.bill_dur else 0 end ) as INNET_CALLED_BILL_DUR,
> sum(case when a.call_type ='10' and a.sort_name= 'OPP_CARR_CMC' then a.bill_dur else 0 end ) as INNET_CALLING_BILL_DUR,
> sum(case when a.call_type ='11' and a.sort_name= 'OPP_CARR_CTC_CDMA' then a.bill_dur else 0 end ) as CDMA_CALLED_BILL_DUR,
> sum(case when a.call_type ='12' and a.sort_name= 'OPP_CARR_CTC_CDMA' then a.bill_dur else 0 end ) as CDMA_FW_BILL_DUR,
> sum(case when a.call_type ='10' and a.sort_name= 'OPP_CARR_CTC_CDMA' then a.bill_dur else 0 end ) as CDMA_CALLING_BILL_DUR,
> sum(case when a.call_type ='11' and a.sort_name= 'OPP_CARR_CTC_PSTN' then a.bill_dur else 0 end ) as PSTN_CALLED_BILL_DUR,
> sum(case when a.call_type ='12' and a.sort_name= 'OPP_CARR_CTC_PSTN' then a.bill_dur else 0 end ) as PSTN_FW_BILL_DUR,
> sum(case when a.call_type ='10' and a.sort_name= 'OPP_CARR_CTC_PSTN' then a.bill_dur else 0 end ) as PSTN_CALLING_BILL_DUR,
> sum(case when a.call_type ='12' and a.opp_carr = '12' then a.bill_dur else 0 end ) as UNION_GSM_FW_BILL_DUR
> from (select * from sumtest limit 1) a
> group by a.user_id,a.phone_no;
> {code}
> The table:
> {code:sql}
> CREATE TABLE `sumtest`(
> `user_id` string,
> `call_type` string,
> `roam_type` string,
> `toll_type` string,
> `dial_type` string,
> `phone_no` string,
> `imei` string,
> `dynm_roam_no` string,
> `bts_id` string,
> `lac_id` string,
> `roam_region_no` string,
> `opp_no` string,
> `opp_carr` string,
> `opp_net_type` string,
> `opp_visit_region_no` string,
> `opp_bts_id` string,
> `opp_lac_code` string,
> `opp_city_id` string,
> `begin_time` string,
> `call_dur_secnd_num` bigint,
> `bill_dur` bigint,
> `net_type` string,
> `busy_idle_flag` smallint,
> `shot_no_flag` smallint,
> `total_fee` double,
> `recycle_flag` smallint,
> `cdr_busi_type` string,
> `imsi` string,
> `device_id` string,
> `serv_type` string,
> `serv_code` string,
> `carr_id` string,
> `trans_serv_suppr` string,
> `cdr_split_flag` smallint,
> `vpmn_call_type` string,
> `trd_no` string,
> `err_code` string,
> `device_type` string,
> `disc_track` string,
> `cdr_sn` string,
> `callreferenceno` string,
> `deal_time` string,
> `loc_call_fee` double,
> `std_loc_call_fee` double,
> `roam_fee` double,
> `std_roam_fee` double,
> `toll_fee` double,
> `std_toll_fee` double,
> `toll_append_fee` double,
> `toll_fee_2` double,
> `stat_month` string,
> `stat_date` string,
> `sort_name` string,
> `spec_type` string)
> ROW FORMAT SERDE
> 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
> STORED AS INPUTFORMAT
> 'org.apache.hadoop.mapred.TextInputFormat'
> OUTPUTFORMAT
> 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat';
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org