You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@spark.apache.org by "XiaoJing wang (JIRA)" <ji...@apache.org> on 2015/07/15 05:52:05 UTC
[jira] [Created] (SPARK-9058) if set `spark.sql.codegen` is
true,More than 100 aggregation operation, it exceeds JVM code size limits
XiaoJing wang created SPARK-9058:
------------------------------------
Summary: 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
if set “spark.sql.codegen”is true，there are more then 100 aggregation operation,it will wrong.
The following error arise:
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
The query is：
`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;`
The table :
`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';`
--
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