Spark日志分析项目Demo(7)--临时表查询，各区域top3热门商品统计

如果是在关系数据库里实现各区域top3热门商品统计，需要编写sql查询语句。
现在要处理的是Hive数据，之前用RDD先排序，后获取top的方法实现top n，
下面换个途径，成用临时表的sql top查询来实现，流程是：
(1)创建SparkConf,构建Spark上下文
(2)注册自定义函数,可以在临时表的sql查询中使用这些函数
(3)准备模拟数据
(4)获取命令行传入的taskid，mysql查询对应的任务参数
(5)Hive数据源查询用户指定日期范围内的点击行为数据（city_id，在哪个城市发生的点击行为）
(6)MySQL中查询城市信息
(7)将点击行为数据和城市数据的关联,生成点击商品基础信息临时表,
生成各区域各商品点击次数的临时表,
生成包含完整商品信息的各区域各商品点击次数的临时表（使用了Join）
最后获取各个区域内点击次数排名前3的热门商品（使用了group by)。

参考的sql语句
（1）查询指定日期范围内的点击行为数据

// 从user_visit_action中，查询用户访问行为数据
// 第一个限定：click_product_id，限定为不为空的访问行为，那么就代表着点击行为
// 第二个限定：在用户指定的日期范围内的数据
String sql = 
                "SELECT "
                    + "city_id,"
                    + "click_product_id product_id "
                + "FROM user_visit_action "
                + "WHERE click_product_id IS NOT NULL "         
                + "AND date>='" + startDate + "' "
                + "AND date<='" + endDate + "'";

（2）生成各区域各商品点击次数临时表

// 按照area和product_id两个字段进行分组
// 计算出各区域各商品的点击次数
// 可以获取到每个area下的每个product_id的城市信息拼接起来的串
String sql = 
                "SELECT "
                    + "area,"
                    + "product_id,"
                    + "count(*) click_count, "  
                    + "group_concat_distinct(concat_long_string(city_id,city_name,':')) city_infos "  
                + "FROM tmp_click_product_basic "
                + "GROUP BY area,product_id ";

(3)生成包含完整商品信息的各区域各商品点击次数的临时表

        // 将之前得到的各区域各商品点击次数表，product_id
        // 去关联商品信息表，product_id，product_name和product_status
        // product_status要特殊处理，0，1，分别代表了自营和第三方的商品，放在了一个json串里面
        // get_json_object()函数，可以从json串中获取指定的字段的值
        // if()函数，判断，如果product_status是0，那么就是自营商品；如果是1，那么就是第三方商品
        // area, product_id, click_count, city_infos, product_name, product_status

        // 为什么要费时费力，计算出来商品经营类型
        // 你拿到到了某个区域top3热门的商品，那么其实这个商品是自营的，还是第三方的
        // 其实是很重要的一件事

        // 技术点：内置if函数的使用
    String sql = "SELECT "
                    + "tapcc.area,"
                    + "tapcc.product_id,"
                    + "tapcc.click_count,"
                    + "tapcc.city_infos,"
                    + "pi.product_name,"
                    + "if(get_json_object(pi.extend_info,'product_status')='0','Self','Third Party') product_status "
                + "FROM tmp_area_product_click_count tapcc "
                + "JOIN product_info pi ON tapcc.product_id=pi.product_id ";

(4)获取各区域top3热门商品

// 技术点：开窗函数

        // 使用开窗函数先进行一个子查询
        // 按照area进行分组，给每个分组内的数据，按照点击次数降序排序，打上一个组内的行号
        // 接着在外层查询中，过滤出各个组内的行号排名前3的数据
        // 其实就是咱们的各个区域下top3热门商品

        // 华北、华东、华南、华中、西北、西南、东北
        // A级：华北、华东
        // B级：华南、华中
        // C级：西北、西南
        // D级：东北

        // case when
        // 根据多个条件，不同的条件对应不同的值
        // case when then ... when then ... else ... end
        String sql = 
                "SELECT "
                    + "area,"
                    + "CASE "
                        + "WHEN area='China North' OR area='China East' THEN 'A Level' "
                        + "WHEN area='China South' OR area='China Middle' THEN 'B Level' "
                        + "WHEN area='West North' OR area='West South' THEN 'C Level' "
                        + "ELSE 'D Level' "
                    + "END area_level,"
                    + "product_id,"
                    + "click_count,"
                    + "city_infos,"
                    + "product_name,"
                    + "product_status "
                + "FROM ("
                    + "SELECT "
                        + "area,"
                        + "product_id,"
                        + "click_count,"
                        + "city_infos,"
                        + "product_name,"
                        + "product_status,"
                        + "row_number() OVER (PARTITION BY area ORDER BY click_count DESC) rank "
                    + "FROM tmp_area_fullprod_click_count "
                + ") t "
                + "WHERE rank<=3";

补充主流程源代码

    public static void main(String[] args) {
        // 创建SparkConf
        SparkConf conf = new SparkConf()
                .setAppName("AreaTop3ProductSpark");
        SparkUtils.setMaster(conf); 

        // 构建Spark上下文
        JavaSparkContext sc = new JavaSparkContext(conf);
        SQLContext sqlContext = SparkUtils.getSQLContext(sc.sc());

        // 注册自定义函数
        sqlContext.udf().register("concat_long_string", 
                new ConcatLongStringUDF(), DataTypes.StringType);
        sqlContext.udf().register("get_json_object", 
                new GetJsonObjectUDF(), DataTypes.StringType);
        sqlContext.udf().register("random_prefix", 
                new RandomPrefixUDF(), DataTypes.StringType);
        sqlContext.udf().register("remove_random_prefix", 
                new RemoveRandomPrefixUDF(), DataTypes.StringType);
        sqlContext.udf().register("group_concat_distinct", 
                new GroupConcatDistinctUDAF());

        // 准备模拟数据
        SparkUtils.mockData(sc, sqlContext);  

        // 获取命令行传入的taskid，查询对应的任务参数
        ITaskDAO taskDAO = DAOFactory.getTaskDAO();

        long taskid = ParamUtils.getTaskIdFromArgs(args, 
                Constants.SPARK_LOCAL_TASKID_PRODUCT);
        Task task = taskDAO.findById(taskid);

        JSONObject taskParam = JSONObject.parseObject(task.getTaskParam());
        String startDate = ParamUtils.getParam(taskParam, Constants.PARAM_START_DATE);
        String endDate = ParamUtils.getParam(taskParam, Constants.PARAM_END_DATE);

        // 查询用户指定日期范围内的点击行为数据（city_id，在哪个城市发生的点击行为）
        // 技术点1：Hive数据源的使用
        JavaPairRDD<Long, Row> cityid2clickActionRDD = getcityid2ClickActionRDDByDate(
                sqlContext, startDate, endDate);
        System.out.println("cityid2clickActionRDD: " + cityid2clickActionRDD.count());  

        // 从MySQL中查询城市信息
        // 技术点2：异构数据源之MySQL的使用
        JavaPairRDD<Long, Row> cityid2cityInfoRDD = getcityid2CityInfoRDD(sqlContext);
        System.out.println("cityid2cityInfoRDD: " + cityid2cityInfoRDD.count());  

        // 生成点击商品基础信息临时表
        // 技术点3：将RDD转换为DataFrame，并注册临时表
        generateTempClickProductBasicTable(sqlContext, 
                cityid2clickActionRDD, cityid2cityInfoRDD); 

        // 生成各区域各商品点击次数的临时表
        generateTempAreaPrdocutClickCountTable(sqlContext);

        // 生成包含完整商品信息的各区域各商品点击次数的临时表
        generateTempAreaFullProductClickCountTable(sqlContext);  

        // 使用开窗函数获取各个区域内点击次数排名前3的热门商品
        JavaRDD<Row> areaTop3ProductRDD = getAreaTop3ProductRDD(sqlContext);
        System.out.println("areaTop3ProductRDD: " + areaTop3ProductRDD.count());  

        // 这边的写入mysql和之前不太一样
        // 因为实际上，就这个业务需求而言，计算出来的最终数据量是比较小的
        // 总共就不到10个区域，每个区域还是top3热门商品，总共最后数据量也就是几十个
        // 所以可以直接将数据collect()到本地
        // 用批量插入的方式，一次性插入mysql即可
        List<Row> rows = areaTop3ProductRDD.collect();
        System.out.println("rows: " + rows.size());  
        persistAreaTop3Product(taskid, rows);

        sc.close();
    }