Spark 电商分析 Spark-sql 统计各区域热门商品统计 需求、设计、数据库

一、需求分析

1、根据用户指定的日期范围，统计各个区域下的最热门的top3商品

1. 区域信在哪里？各个城市信息不怎么变化，存储在mysql中
2. hive用户行为数据，hive和mysql城市信息 join（Hive和mysql 异构数据源使用，技术点1）关联之后是RDD，RDD转换成DataFrame，注册临时表，第二个技术点
3. 各个区域下各个商品的点击量，保留每个区域的城市列表数据？自定义UDAF函数，group_concat_distinct() 自定义聚合函数，对多行数据
4. product_id,join hive表中的商品信息,商品信息在哪里？Hive,商品的经营类型是什么？自定义UDF函数，get_json_object(),if() 自定义单行行数 对单行数据
5. 获取每个区域的点击量top3商品？开窗函数：给每个区域打上级别标记，西北大区，C类 北上广深 A区
6. SparkSQL的数据倾斜解决方案，双重groupby,随机key以及扩容表（自定义UDF函数，random_key(）） 内置reduce join转为map join shuffle并行度

2、 操作流程

二、 技术方案

1、查询task，获取日期范围，通过Spark SQL，查询user_visit_action表中的指定日期范围内的数据，过滤出，商品点击行为，click_product_id is not null；click_product_id != ‘NULL’；click_product_id != ‘null’；city_id，click_product_id
2、使用Spark SQL从MySQL中查询出来城市信息（city_id、city_name、area），用户访问行为数据要跟城市信息进行join，city_id、city_name、area、product_id，RDD，转换成DataFrame，注册成一个临时表
3、Spark SQL内置函数（case when），对area打标记（华东大区，A级，华中大区，B级，东北大区，C级，西北大区，D级），area_level
4、计算出来每个区域下每个商品的点击次数，group by area, product_id；保留每个区域的城市名称列表；自定义UDAF，group_concat_distinct()函数，聚合出来一个city_names字段，area、product_id、city_names、click_count
5、join商品明细表，hive（product_id、product_name、extend_info），extend_info是json类型，自定义UDF，get_json_object()函数，取出其中的product_status字段，if()函数（Spark SQL内置函数），判断，0 自营，1 第三方；（area、product_id、city_names、click_count、product_name、product_status）
6、开窗函数，根据area来聚合，获取每个area下，click_count排名前3的product信息；area、area_level、product_id、city_names、click_count、product_name、product_status
7、结果写入MySQL表中
8、Spark SQL的数据倾斜解决方案？双重group by、随机key以及扩容表（自定义UDF函数，random_key()）、Spark SQL内置的reduce join转换为map join、提高shuffle并行度
9、本地测试和生产环境的测试

数据添加表

基础数据的准备和设计

1、MySQL表中，要有city_info，city_id、city_name、area
2、Hive表中，要有一个product_info表，product_id、product_name、extend_info
3、MySQL中，设计结果表，task_id、area、area_level、product_id、city_names、click_count、product_name、product_status
创建city_info表并插入数据

CREATE DATABASE /*!32312 IF NOT EXISTS*/`spark_project` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `spark_project`;

/*Table structure for table `city_info` */

DROP TABLE IF EXISTS `city_info`;

CREATE TABLE `city_info` (
  `city_id` int(11) DEFAULT NULL,
  `city_name` varchar(255) DEFAULT NULL,
  `area` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `city_info` */

insert  into `city_info`(`city_id`,`city_name`,`area`) values (0,'北京','华北'),(1,'上海','华东'),(2,'南京','华东'),(3,'广州','华南'),(4,'三亚','华南'),(5,'武汉','华中'),(6,'长沙','华中'),(7,'西安','西北'),(8,'成都','西南'),(9,'哈尔滨','东北');

创建area_top3_product

CREATE DATABASE /*!32312 IF NOT EXISTS*/`spark_project` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `spark_project`;

/*Table structure for table `area_top3_product` */

DROP TABLE IF EXISTS `area_top3_product`;

CREATE TABLE `area_top3_product` (
  `task_id` int(11) DEFAULT NULL,
  `area` varchar(255) DEFAULT NULL,
  `area_level` varchar(255) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  `city_infos` varchar(255) DEFAULT NULL,
  `click_count` int(11) DEFAULT NULL,
  `product_name` varchar(255) DEFAULT NULL,
  `product_status` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;