package com.gmei import java.io.Serializable import org.apache.spark.sql.{SaveMode, TiContext} import org.apache.log4j.{Level, Logger} import scopt.OptionParser import com.gmei.lib.AbstractParams import org.apache.spark.sql.functions.lit import scala.util.Try object EsmmData { Logger.getLogger("org.apache.spark").setLevel(Level.WARN) Logger.getLogger("org.apache.eclipse.jetty.server").setLevel(Level.OFF) case class Params(env: String = "dev", date: String = GmeiConfig.getMinusNDate(1) ) extends AbstractParams[Params] with Serializable val defaultParams = Params() val parser = new OptionParser[Params]("Feed_EDA") { head("EsmmData") opt[String]("env") .text(s"the databases environment you used") .action((x, c) => c.copy(env = x)) opt[String]("date") .text(s"the date you used") .action((x,c) => c.copy(date = x)) note( """ |For example, the following command runs this app on a tidb dataset: | | spark-submit --class com.gmei.EsmmData ./target/scala-2.11/feededa-assembly-0.1.jar \ """.stripMargin + s"| --env ${defaultParams.env}" ) } def main(args: Array[String]): Unit = { parser.parse(args, defaultParams).map { param => GmeiConfig.setup(param.env) val spark_env = GmeiConfig.getSparkSession() val sc = spark_env._2 val ti = new TiContext(sc) ti.tidbMapTable(dbName = "eagle",tableName = "src_mimas_prod_api_diary_tags") ti.tidbMapTable(dbName = "eagle",tableName = "src_zhengxing_api_tag") ti.tidbMapTable(dbName = "jerry_prod",tableName = "data_feed_click") ti.tidbMapTable(dbName = "jerry_prod",tableName = "data_feed_exposure") ti.tidbMapTable(dbName = "jerry_test", tableName = "esmm_train_data") val max_stat_date = sc.sql( s""" |select max(stat_date) from esmm_train_data """.stripMargin ) val max_stat_date_str = max_stat_date.collect().map(s => s(0).toString).head println("max_stat_date_str",max_stat_date_str) println("param.date",param.date) if (max_stat_date_str != param.date){ val stat_date = param.date println(stat_date) // val imp_data = sc.sql( // s""" // |select distinct stat_date,device_id,city_id as ucity_id, // | cid_id,diary_service_id // |from data_feed_exposure // |where cid_type = 'diary' // |and stat_date ='${stat_date}' // """.stripMargin // ) val imp_data = sc.sql( s""" |select * from |(select stat_date,device_id,city_id as ucity_id,cid_id,diary_service_id |from data_feed_exposure |where cid_type = 'diary' |and stat_date ='${stat_date}' |group by stat_date,device_id,city_id,cid_id,diary_service_id having count(*) > 1) a """.stripMargin ) // imp_data.show() // println("imp_data.count()") // println(imp_data.count()) val clk_data = sc.sql( s""" |select distinct stat_date,device_id,city_id as ucity_id, | cid_id,diary_service_id |from data_feed_click |where cid_type = 'diary' |and stat_date ='${stat_date}' """.stripMargin ) // clk_data.show() // println("clk_data.count()") // println(clk_data.count()) val imp_data_filter = imp_data.except(clk_data).withColumn("y",lit(0)).withColumn("z",lit(0)) // imp_data_filter.createOrReplaceTempView("imp_data_filter") // imp_data_filter.show() // println("imp_data_filter.count()") // println(imp_data_filter.count()) val stat_date_not = stat_date.replace("-","") val cvr_data = sc.sql( s""" |select distinct | from_unixtime(unix_timestamp(partition_date ,'yyyyMMdd'), 'yyyy-MM-dd') as stat_date, | cl_id as device_id,city_id as ucity_id, | params["referrer_id"] as cid_id,params["business_id"] as diary_service_id |from online.tl_hdfs_maidian_view |where action='page_view' |and partition_date ='${stat_date_not}' |and params['page_name'] = 'welfare_detail' |and params['referrer'] = 'diary_detail' """.stripMargin ) val cvr_data_filter = cvr_data.withColumn("y",lit(1)).withColumn("z",lit(1)) // cvr_data_filter.createOrReplaceTempView("cvr_data_filter") // cvr_data_filter.show() // println("cvr_data_filter.count()") // println(cvr_data_filter.count()) val clk_data_filter =clk_data.except(cvr_data).withColumn("y",lit(1)).withColumn("z",lit(0)) // clk_data_filter.createOrReplaceTempView("clk_data_filter") // clk_data_filter.show() // println("clk_data_filter.count()") // println(clk_data_filter.count()) val union_data = imp_data_filter.union(clk_data_filter).union(cvr_data_filter) union_data.createOrReplaceTempView("union_data") // union_data.show() // println("union_data.count()") // println(union_data.count()) val union_data_clabel = sc.sql( s""" |select a.stat_date,a.device_id,a.ucity_id,a.cid_id,a.diary_service_id,a.y,a.z, | c.level1_id as clevel1_id |from union_data a |left join online.tl_hdfs_diary_tags_view b on a.cid_id=b.diary_id |left join online.bl_tag_hierarchy_detail c on b.tag_id=c.id |where b.partition_date='${stat_date_not}' |and c.partition_date='${stat_date_not}' """.stripMargin ) union_data_clabel.createOrReplaceTempView("union_data_clabel") // union_data_clabel.show() val union_data_slabel = sc.sql( s""" |select a.stat_date,a.device_id,a.ucity_id,a.cid_id,a.diary_service_id,a.y,a.z,a.clevel1_id, | c.level1_id as slevel1_id |from union_data_clabel a |left join online.tl_meigou_servicetag_view b on a.diary_service_id=b.service_id |left join online.bl_tag_hierarchy_detail c on b.tag_id=c.id |where b.partition_date='${stat_date_not}' |and c.partition_date='${stat_date_not}' """.stripMargin ) union_data_slabel.createOrReplaceTempView("union_data_slabel") // union_data_slabel.show() val union_data_ccity_name = sc.sql( s""" |select a.stat_date,a.device_id,a.ucity_id,a.cid_id,a.diary_service_id,a.y,a.z,a.clevel1_id,a.slevel1_id, | c.name as ccity_name |from union_data_slabel a |left join src_mimas_prod_api_diary_tags b on a.cid_id=b.diary_id |left join src_zhengxing_api_tag c on b.tag_id=c.id | where c.tag_type=4 """.stripMargin ) union_data_ccity_name.createOrReplaceTempView("union_data_ccity_name") // union_data_ccity_name.show() val union_data_scity_id = sc.sql( s""" |select a.stat_date,a.device_id,a.ucity_id,a.cid_id,a.diary_service_id,a.y,a.z,a.clevel1_id,a.slevel1_id,a.ccity_name, | d.city_id as scity_id |from union_data_ccity_name a |left join online.tl_meigou_service_view b on a.diary_service_id=b.id |left join online.tl_hdfs_doctor_view c on b.doctor_id=c.id |left join online.tl_hdfs_hospital_view d on c.hospital_id=d.id |where b.partition_date='${stat_date_not}' |and c.partition_date='${stat_date_not}' |and d.partition_date='${stat_date_not}' """.stripMargin ) // union_data_scity_id.createOrReplaceTempView("union_data_scity_id") union_data_scity_id.show() GmeiConfig.writeToJDBCTable("jdbc:mysql://10.66.157.22:4000/jerry_test?user=root&password=3SYz54LS9#^9sBvC&rewriteBatchedStatements=true",union_data_scity_id, table="esmm_train_data",SaveMode.Append) } else { println("esmm_train_data already have param.date data") } sc.stop() } } } object EsmmPredData { Logger.getLogger("org.apache.spark").setLevel(Level.WARN) Logger.getLogger("org.apache.eclipse.jetty.server").setLevel(Level.OFF) case class Params(env: String = "dev" ) extends AbstractParams[Params] with Serializable val defaultParams = Params() val parser = new OptionParser[Params]("Feed_EDA") { head("EsmmData") opt[String]("env") .text(s"the databases environment you used") .action((x, c) => c.copy(env = x)) note( """ |For example, the following command runs this app on a tidb dataset: | | spark-submit --class com.gmei.EsmmData ./target/scala-2.11/feededa-assembly-0.1.jar \ """.stripMargin + s"| --env ${defaultParams.env}" ) } def main(args: Array[String]): Unit = { parser.parse(args, defaultParams).map { param => GmeiConfig.setup(param.env) val spark_env = GmeiConfig.getSparkSession() val sc = spark_env._2 val ti = new TiContext(sc) ti.tidbMapTable(dbName = "eagle",tableName = "src_mimas_prod_api_diary_tags") ti.tidbMapTable(dbName = "eagle",tableName = "src_zhengxing_api_tag") ti.tidbMapTable(dbName = "jerry_prod",tableName = "data_feed_exposure") ti.tidbMapTable(dbName = "jerry_prod",tableName = "data_feed_click") ti.tidbMapTable("jerry_prod", "nd_device_cid_similarity_matrix") ti.tidbMapTable("eagle","ffm_diary_queue") ti.tidbMapTable("eagle","search_queue") ti.tidbMapTable(dbName = "jerry_test",tableName = "esmm_train_data") ti.tidbMapTable("eagle","biz_feed_diary_queue") import sc.implicits._ val yesteday_have_seq = GmeiConfig.getMinusNDate(1) //nearby_data val raw_data = sc.sql( s""" |select concat(tmp1.device_id,",",tmp1.city_id) as device_city, tmp1.merge_queue from |(select device_id,if(city_id='world','worldwide',city_id) city_id,similarity_cid as merge_queue from nd_device_cid_similarity_matrix |union |select device_id,if(city_id='world','worldwide',city_id) city_id,native_queue as merge_queue from ffm_diary_queue |union |select device_id,city_id,search_queue as merge_queue from search_queue) as tmp1 |where tmp1.device_id in (select distinct device_id from data_feed_click where stat_date='${yesteday_have_seq}') """.stripMargin ) raw_data.show() val raw_data1 = raw_data.rdd.groupBy(_.getAs[String]("device_city")).map { case (device_city, cid_data) => val device_id = Try(device_city.split(",")(0)).getOrElse("") val city_id = Try(device_city.split(",")(1)).getOrElse("") val cids = Try(cid_data.toSeq.map(_.getAs[String]("merge_queue").split(",")).flatMap(_.zipWithIndex).sortBy(_._2).map(_._1).distinct.take(500).mkString(",")).getOrElse("") (device_id,city_id ,s"$cids") }.filter(_._3!="").toDF("device_id","city_id","merge_queue") raw_data1.createOrReplaceTempView("raw_data1") println("nearby_device_count",raw_data1.count()) val raw_data2 = sc.sql( s""" |select device_id,city_id as ucity_id,explode(split(merge_queue, ',')) as cid_id from raw_data1 """.stripMargin ).withColumn("label",lit(1)) raw_data2.createOrReplaceTempView("raw_data2") println("nearby_explode_count",raw_data2.count()) // native_data val native_data = sc.sql( s""" |select distinct a.device_id,a.city_id,b.native_queue from data_feed_exposure a |left join (select if(city_id='world','worldwide',city_id) city_id,native_queue from biz_feed_diary_queue) b |on a.city_id = b.city_id |where a.stat_date='${yesteday_have_seq}' and b.native_queue != "" """.stripMargin ) native_data.createOrReplaceTempView("native_data") println("native_device_count",native_data.count()) val native_data1 = sc.sql( s""" |select device_id,city_id as ucity_id, |explode(split(split(native_queue, concat(',',split(native_queue,',')[500]))[0],',')) as cid_id |from native_data """.stripMargin ).withColumn("label",lit(0)) native_data1.createOrReplaceTempView("native_data1") println("native_explode_count",native_data1.count()) //union val union_data = sc.sql( s""" |select device_id,ucity_id,cid_id,label from native_data1 |union |select device_id,ucity_id,cid_id,label from raw_data2 """.stripMargin ) union_data.createOrReplaceTempView("raw_data") println("union_count",union_data.count()) //join feat val yesteday = GmeiConfig.getMinusNDate(1).replace("-","") val sid_data = sc.sql( s""" |select distinct | from_unixtime(unix_timestamp(partition_date ,'yyyyMMdd'), 'yyyy-MM-dd') as stat_date, | a.device_id,a.ucity_id,a.cid_id,a.label, b.service_id as diary_service_id |from raw_data a |left join online.ml_community_diary_updates b on a.cid_id = b.diary_id |where b.partition_date = '${yesteday}' """.stripMargin ) // sid_data.show() println(sid_data.count()) val sid_data_label = sid_data.withColumn("y",lit(0)).withColumn("z",lit(0)) sid_data_label.createOrReplaceTempView("union_data") val union_data_clabel = sc.sql( s""" |select a.stat_date,a.device_id,a.ucity_id,a.cid_id,a.label,a.diary_service_id,a.y,a.z, | c.level1_id as clevel1_id |from union_data a |left join online.tl_hdfs_diary_tags_view b on a.cid_id=b.diary_id |left join online.bl_tag_hierarchy_detail c on b.tag_id=c.id |where b.partition_date='${yesteday}' |and c.partition_date='${yesteday}' """.stripMargin ) union_data_clabel.createOrReplaceTempView("union_data_clabel") // union_data_clabel.show() val union_data_slabel = sc.sql( s""" |select a.stat_date,a.device_id,a.ucity_id,a.cid_id,a.label,a.diary_service_id,a.y,a.z,a.clevel1_id, | c.level1_id as slevel1_id |from union_data_clabel a |left join online.tl_meigou_servicetag_view b on a.diary_service_id=b.service_id |left join online.bl_tag_hierarchy_detail c on b.tag_id=c.id |where b.partition_date='${yesteday}' |and c.partition_date='${yesteday}' """.stripMargin ) union_data_slabel.createOrReplaceTempView("union_data_slabel") // union_data_slabel.show() val union_data_ccity_name = sc.sql( s""" |select a.stat_date,a.device_id,a.ucity_id,a.cid_id,a.label,a.diary_service_id,a.y,a.z,a.clevel1_id,a.slevel1_id, | c.name as ccity_name |from union_data_slabel a |left join src_mimas_prod_api_diary_tags b on a.cid_id=b.diary_id |left join src_zhengxing_api_tag c on b.tag_id=c.id | where c.tag_type=4 """.stripMargin ) union_data_ccity_name.createOrReplaceTempView("union_data_ccity_name") // union_data_ccity_name.show() val union_data_scity_id = sc.sql( s""" |select a.stat_date,a.device_id,a.ucity_id,a.cid_id,a.label,a.diary_service_id,a.y,a.z,a.clevel1_id,a.slevel1_id,a.ccity_name, | d.city_id as scity_id |from union_data_ccity_name a |left join online.tl_meigou_service_view b on a.diary_service_id=b.id |left join online.tl_hdfs_doctor_view c on b.doctor_id=c.id |left join online.tl_hdfs_hospital_view d on c.hospital_id=d.id |where b.partition_date='${yesteday}' |and c.partition_date='${yesteday}' |and d.partition_date='${yesteday}' """.stripMargin ) union_data_scity_id.createOrReplaceTempView("union_data_scity_id") val union_data_scity_id2 = sc.sql( s""" |select device_id,cid_id,first(stat_date) stat_date,first(ucity_id) ucity_id,first(label) label,first(diary_service_id)diary_service_id,first(y) y, |first(z) z,first(clevel1_id) clevel1_id,first(slevel1_id) slevel1_id,first(ccity_name) ccity_name,first(scity_id) scity_id |from union_data_scity_id |group by device_id,cid_id """.stripMargin ) // union_data_scity_id.createOrReplaceTempView("union_data_scity_id") println(union_data_scity_id2.count()) GmeiConfig.writeToJDBCTable("jdbc:mysql://10.66.157.22:4000/jerry_test?user=root&password=3SYz54LS9#^9sBvC&rewriteBatchedStatements=true",union_data_scity_id2, table="esmm_pre_data",SaveMode.Overwrite) sc.stop() } } } object GetDiaryPortrait { Logger.getLogger("org.apache.spark").setLevel(Level.WARN) Logger.getLogger("org.apache.eclipse.jetty.server").setLevel(Level.OFF) case class Params(env: String = "dev", date: String = GmeiConfig.getMinusNDate(1) ) extends AbstractParams[Params] with Serializable val defaultParams = Params() val parser = new OptionParser[Params]("Feed_EDA") { head("EsmmData") opt[String]("env") .text(s"the databases environment you used") .action((x, c) => c.copy(env = x)) opt[String]("date") .text(s"the date you used") .action((x,c) => c.copy(date = x)) note( """ |For example, the following command runs this app on a tidb dataset: | | spark-submit --class com.gmei.EsmmData ./target/scala-2.11/feededa-assembly-0.1.jar \ """.stripMargin + s"| --env ${defaultParams.env}" ) } def main(args: Array[String]): Unit = { parser.parse(args, defaultParams).map { param => GmeiConfig.setup(param.env) val spark_env = GmeiConfig.getSparkSession() val sc = spark_env._2 val ti = new TiContext(sc) ti.tidbMapTable(dbName = "jerry_prod",tableName = "data_feed_click") val stat_date = param.date.replace("-","") val diary_tag = sc.sql( s""" |select c.diary_id, | concat_ws(';',collect_set(cast(c.level1_id as string))) as level1_ids, | concat_ws(';',collect_set(cast(c.level2_id as string))) as level2_ids, | concat_ws(';',collect_set(cast(c.level3_id as string))) as level3_ids from | (select a.diary_id,b.level1_id,b.level2_id,b.level3_id | from online.tl_hdfs_diary_tags_view a | left join online.bl_tag_hierarchy_detail b | on a.tag_id = b.id | where a.partition_date = '${stat_date}' | and b.partition_date = '${stat_date}') c | group by c.diary_id """.stripMargin ) diary_tag.show() println(diary_tag.count()) GmeiConfig.writeToJDBCTable(diary_tag,"diary_feat",SaveMode.Overwrite) sc.stop() } } } object GetDevicePortrait { Logger.getLogger("org.apache.spark").setLevel(Level.WARN) Logger.getLogger("org.apache.eclipse.jetty.server").setLevel(Level.OFF) case class Params(env: String = "dev", date: String = GmeiConfig.getMinusNDate(1) ) extends AbstractParams[Params] with Serializable val defaultParams = Params() val parser = new OptionParser[Params]("Feed_EDA") { head("EsmmData") opt[String]("env") .text(s"the databases environment you used") .action((x, c) => c.copy(env = x)) opt[String]("date") .text(s"the date you used") .action((x,c) => c.copy(date = x)) note( """ |For example, the following command runs this app on a tidb dataset: | | spark-submit --class com.gmei.EsmmData ./target/scala-2.11/feededa-assembly-0.1.jar \ """.stripMargin + s"| --env ${defaultParams.env}" ) } def main(args: Array[String]): Unit = { parser.parse(args, defaultParams).map { param => GmeiConfig.setup(param.env) val spark_env = GmeiConfig.getSparkSession() val sc = spark_env._2 val ti = new TiContext(sc) ti.tidbMapTable(dbName = "jerry_prod",tableName = "data_feed_click") ti.tidbMapTable(dbName = "jerry_prod",tableName = "diary_feat") import sc.implicits._ val stat_date = param.date.replace("-","") val device_search_tag = sc.sql( s""" |select c.device_id,c.stat_date,c.level1_id,count(c.level1_id) as level1_count |from (select | a.cl_id as device_id,a.partition_date as stat_date, | COALESCE(a.params['diary_id'], a.params['business_id'], 0) as cid_id, | b.level1_ids as level1_id | from online.tl_hdfs_maidian_view a | left join diary_feat b | on COALESCE(a.params['diary_id'], a.params['business_id'], 0) = b.diary_id | where | b.level1_ids is not null and | a.partition_date = '${stat_date}' | and (a.action = 'on_click_diary_card' or (a.action="full_stack_click_video_card_full_screen_play" and a.params["card_type"]="diary"))) c |group by c.device_id,c.level1_id,c.stat_date """.stripMargin ) device_search_tag.show() println(device_search_tag.count()) device_search_tag.createOrReplaceTempView("tag_count") val max_count_tag = sc.sql( s""" |select a.device_id,a.stat_date,a.level1_id as max_level1_id,a.level1_count as max_level1_count |from tag_count a |inner join |(select device_id,max(level1_count) as max_count from tag_count group by device_id) b |on a.level1_count = b.max_count and a.device_id = b.device_id """.stripMargin ) // .rdd.map(x => (x(0).toString,x(1).toString,x(2).toString,x(3).toString)) // max_count_tag.foreachPartition(GmeiConfig.updateDeviceFeat) // // max_count_tag.take(10).foreach(println) // println(max_count_tag.count()) //drop duplicates val max_count_tag_rdd = max_count_tag.rdd.groupBy(_.getAs[String]("device_id")).map { case (device_id,data) => val stat_date = data.map(_.getAs[String]("stat_date")).head val max_level1_id = data.map(_.getAs[String]("max_level1_id")).head.toString val max_level1_count = data.map(_.getAs[Long]("max_level1_count")).head.toString (device_id,stat_date,max_level1_id,max_level1_count) }.filter(_._1!=null) max_count_tag_rdd.foreachPartition(GmeiConfig.updateDeviceFeat) max_count_tag_rdd.take(10).foreach(println) println(max_count_tag_rdd.count()) sc.stop() } } } object GetLevelCount { Logger.getLogger("org.apache.spark").setLevel(Level.WARN) Logger.getLogger("org.apache.eclipse.jetty.server").setLevel(Level.OFF) case class Params(env: String = "dev", path: String = null ) extends AbstractParams[Params] with Serializable val defaultParams = Params() val parser = new OptionParser[Params]("Feed_EDA") { head("EsmmData") opt[String]("env") .text(s"the databases environment you used") .action((x, c) => c.copy(env = x)) opt[String]("path") .text(s"the path you used") .action((x,c) => c.copy(path = x)) note( """ |For example, the following command runs this app on a tidb dataset: | | spark-submit --class com.gmei.EsmmData ./target/scala-2.11/feededa-assembly-0.1.jar \ """.stripMargin + s"| --env ${defaultParams.env}" ) } def main(args: Array[String]): Unit = { parser.parse(args, defaultParams).map { param => GmeiConfig.setup(param.env) val spark_env = GmeiConfig.getSparkSession() val sc = spark_env._2 val ti = new TiContext(sc) ti.tidbMapTable(dbName = "jerry_prod",tableName = "data_feed_click") ti.tidbMapTable(dbName = "jerry_prod",tableName = "diary_feat") import sc.implicits._ val stat_date = GmeiConfig.getMinusNDate(1).replace("-","") // val diary_queue = sc.read.json(param.path).rdd.map(x => x(0).toString).distinct().collect().toList.mkString(",") val diary_queue = "16283654,16211979,15331340,15534094,13602830,16228368,16211987,15990809,16234522,15386660,15843365,15759398,16306213,15597614,15298621,16134214,15302730,15652939,16193613,15269965,15302734,15466578,15386706,15491159,16101468,15515751,12777586,16304243,15521916,15978625,16435351,15650970,15712411,15544488,15294642,16277682,16425141,16203962,15202492,15386814,15474889,15636685,16101582,16251087,15300823,15300825,15345884,16257252,16214244,16234732,16056557,15247597,16199918,15378686,16267518,16240897,16195843,16220434,16257303,16232729,15491360,16199977,15391028,15491383,15628603,16089403,16357697,16339269,16298324,14969178,15573339,16193883,16419166,15360356,15573353,16132458,14229868,15475055,16234869,15827317,16413055,16298367,16425343,16193927,13986185,16361866,15475082,16245135,15292816,16425364,15544727,16116121,16085403,16136607,16253346,15419823,15481272,16202171,16431553,16419272,15385035,16269779,16417251,15954409,15890925,15731191,16261624,16157187,16130565,15206918,14492168,16294414,15729169,16419346,15479315,16054807,16175641,15239718,15299111,15309353,16173613,15231542,16269882,16251451,16353856,16228931,16300613,15346247,15874634,16308812,16134739,15577701,16208485,15420015,15628919,16061066,16140950,16122519,15751833,16298666,16282308,16141002,16239307,15841996,15565517,12747475,16134867,16122580,16083671,15485655,15196891,16134876,16202461,16202460,16354020,15903463,15082216,15842031,15299312,16397053,15430398,15506175,15387395,16177932,16272144,15891227,16098076,16255792,15594296,14971705,15649596,16118595,16294724,15741766,15287122,15387482,16108382,15477602,16354162,15764357,15883142,15887237,16450441,15477641,16049036,15371151,15276945,15416220,15471518,15360927,15246246,15748007,15578022,15195049,15860650,15489962,16163758,16214959,15332272,16049074,16055221,16296887,15881144,15256507,16200635,15272899,16272333,15338446,16376782,13278160,15385553,15967185,15338448,15467478,15299545,16397281,15461348,12284918,15901687,15361021,15266817,16114690,15625223,15256584,16194569,16194571,15950864,16204819,16049173,15531030,15397912,15883288,15604767,15729700,15504420,15987751,15572010,15615019,16403502,16225332,15891509,15778870,15903804,15545409,15569985,16297034,15572042,15572044,16198733,15545422,15946834,16116818,15336535,16116832,15336547,16266340,16323686,16116854,15621245,15414398,16297085,16116869,16221320,15887497,16225416,16112786,16225427,16123026,16430232,16204953,15895704,16434331,15545497,15912093,16299168,16059552,16204980,15299765,15420603,16399555,15639757,16084175,15361235,15633625,16116953,16065775,16233712,15856889,15375611,16194812,15594747,15609095,15594779,16262442,15420718,16035120,16137522,16405818,15420734,16233792,15570251,15967572,16266581,15639895,16084313,16293219,15592807,16371047,16422248,16246122,16153967,16131449,15349114,15746428,15746434,15297929,15527308,16145806,16317847,16061852,16246173,15912356,13163949,15429039,16041397,16197047,15803831,16207296,15443404,16121301,16127449,16213470,16115168,15629799,15336944,16338429,15629822,15750663,16129543,15568395,15564307,15646229,15441430,15369765,16354853,15441449,15576619,16301612,16199213,16215596,15644209,15994422,16258615,15482427,16096830,15595074,16299587,15414853,15418950,16268873,15988304,16084561,16305752,15603296,15328874,16399988,15877749,16354954,15949451,14542485,16219798,16107161,15345305,15990434,16400037,15720101,16035495,15859365,16375466,15214253,15769263,15328957,15976127,15769280,15519424,16238276,15576775,15253194,16197323,15261387,15591116,16197330,15390421,15306456,15388381,15515359,16258786,16258787,15519458,15990507,16258797,15519472,16166642,15904499,15199988,15990518,15748854,16422648,15533817,16140026,16004862,15986431,15296256,15910656,16193282,15714050,15931142,15834886,16049931,15232783,16426770,16115479,15519511,15519517,16228125,16424738,16297765,16162597,16142120,15980332,15458095,16244538,15580990,15988542,15398719,16269126,16119624,15458127,15966031,16420691,15880026,16185182,16406366,15880033,15880036,15521638,16088936,15533937,16213880,16111482,16199552,15513474,15961993,15986570,15970190,15644562,16138136,16424856,15490981,15402927,16406450,15511478,15747009,15632328,16068554,15966159,15271888,15302622,16191459,16222181,15890407,15966189,16275439,15237104,16424945,16300020,15300599,16050175" val diary_level1 = sc.sql( s""" |select diary_id,explode(split(level1_ids,';')) level1_id from diary_feat |where diary_id in (${diary_queue}) """.stripMargin ) diary_level1.show() println(diary_level1.count()) //胸部日记id val cid_xiong = diary_level1.rdd.filter(_.getAs("level1_id")=="7") cid_xiong.collect().foreach(println) //计算各类别日记的数量 val level1_count = diary_level1.rdd.map(x => (x(1).toString)).map(level1 => (level1,1)).reduceByKey((a,b) => a+b).sortBy(_._2,false).toDF("level1_id","count") level1_count.show() level1_count.createOrReplaceTempView("tmp") val level1_name = sc.sql( s""" |select a.level1_id,a.count,b.level1_name from tmp a |left join (select distinct level1_id,level1_name from online.bl_tag_hierarchy_detail where partition_date = '${stat_date}') b |on a.level1_id = b.level1_id order by a.count desc """.stripMargin ) level1_name.show() sc.stop() } } }