电商数据处理训练任务

环境说明：

集群启动命令为在Master上执行allstart.sh； 各主机可通过SSH客户端进行SSH访问（需通过直连IP访问）； Master节点MySQL数据库用户名/密码：root/123456（已配置远程连接，电商离线数据库为ds_db01）； Hive的配置文件位于/opt/module/hive-3.1.2/conf/   Spark任务在Yarn上用Client运行，方便观察日志。

离线数据处理

任务一：数据抽取

使用Scala编写spark工程代码，将MySQL的ds_db01库中表customer_inf、customer_inf、order_detail、order_master、product_info的数据增量抽取到Hive的ods库（需自建）中对应表customer_inf、order_detail、order_master、product_info中。 

1. 抽取ds_db01库中customer_inf的增量数据进入Hive的ods库中表customer_inf。根据ods.user_info表中modified_time作为增量字段，只将新增的数据抽入，字段名称、类型不变，同时添加静态分区，分区字段为etl_date，类型为String，且值为当前日期的前一天日期（分区字段格式为yyyyMMdd）。使用hive cli执行show partitions ods.customer_inf命令；

1. 抽取ds_db01库中product_info的增量数据进入Hive的ods库中表product_info。根据ods.product_info表中modified_time作为增量字段，只将新增的数据抽入，字段名称、类型不变，同时添加静态分区，分区字段为etl_date，类型为String，且值为当前日期的前一天日期（分区字段格式为yyyyMMdd）。使用hive cli执行show partitions ods.product_info命令；

1. 抽取ds_db01库中order_master的增量数据进入Hive的ods库中表order_master，根据ods.order_master表中modified_time作为增量字段，只将新增的数据抽入，字段名称、类型不变，同时添加静态分区，分区字段为etl_date，类型为String，且值为当前日期的前一天日期（分区字段格式为yyyyMMdd）。使用hive cli执行show partitions ods.order_master命令；

1. 抽取ds_db01库中order_detail的增量数据进入Hive的ods库中表order_detail，根据ods.order_detail表中modified_time作为增量字段，只将新增的数据抽入，字段名称、类型不变，同时添加静态分区，分区字段为etl_date，类型为String，且值为当前比赛日的前一天日期（分区字段格式为yyyyMMdd）。使用hive cli执行show partitions ods.order_detail命令。

任务二：数据清洗

提示：若完成了实时计算的任务二中的第3小题后，可以将HBase中备份的数据与ods中的离线数据进行合并，抽取到dwd（需自建）的对应表中。

编写Hive SQL或者Spark Sql代码，将ods库中相应表数据（经过数据抽取得数据）抽取到Hive的dwd库中对应表中。表中有涉及到timestamp类型的，均要求按照yyyy-MM-dd HH:mm:ss，不记录毫秒数，若与日期有关的数据，必须转为timestamp，若原数据中只有年月日，则在时分秒的位置添加00:00:00，添加之后使其符合yyyy-MM-dd HH:mm:ss。

1. 抽取ods库中customer_inf表中昨天的分区（任务一生成的分区）数据，并结合dim_customer_inf最新分区现有的数据，根据customer_id合并数据到dwd库中dim_customer_inf的分区表（合并是指对dwd层数据进行插入或修改，需修改的数据以customer_id为合并字段，根据modified_time排序取最新的一条），分区字段为etl_date且值与ods库的相对应表该值相等，并添加dwd_insert_user、dwd_insert_time、dwd_modify_user、dwd_modify_time四列,其中dwd_insert_user、dwd_modify_user均填写“user1”。若该条记录第一次进入数仓dwd层则dwd_insert_time、dwd_modify_time均存当前操作时间，并进行数据类型转换。若该数据在进入dwd层时发生了合并修改，则dwd_insert_time时间不变，dwd_modify_time存当前操作时间，其余列存最新的值。使用hive cli执行show partitions dwd.dim_customer_inf命令；

1. 抽取ods库中product_info表中昨天的分区（任务一生成的分区）数据，并结合dim_product_info最新分区现有的数据，根据product_id合并数据到dwd库中dim_product_info的分区表（合并是指对dwd层数据进行插入或修改，需修改的数据以product_id为合并字段，根据modified_time排序取最新的一条），分区字段为etl_date且值与ods库的相对应表该值相等，并添加dwd_insert_user、dwd_insert_time、dwd_modify_user、dwd_modify_time四列,其中dwd_insert_user、dwd_modify_user均填写“user1”。若该条记录第一次进入数仓dwd层则dwd_insert_time、dwd_modify_time均存当前操作时间，并进行数据类型转换。若该数据在进入dwd层时发生了合并修改，则dwd_insert_time时间不变，dwd_modify_time存当前操作时间，其余列存最新的值。使用hive cli执行show partitions dwd.dim_product_info命令；

1. 将ods库中order_master表昨天的分区（任务一生成的分区）数据抽取到dwd库中fact_order_master的动态分区表，分区字段为etl_date，类型为String，取create_time值并将格式转换为yyyyMMdd，并添加dwd_insert_user、dwd_insert_time、dwd_modify_user、dwd_modify_time四列，其中dwd_insert_user、dwd_modify_user均填写“user1”，dwd_insert_time、dwd_modify_time均填写当前操作时间，并进行数据类型转换，需要过滤掉city字段长度大于8。使用hive cli执行show partitions dwd.fact_order_info命令；

提示：若完成实时任务二中的第3小题后，可将hbase中的数据合并后在进行清洗，清洗后数据添加至DWD中

1. 将ods库中order_detail表昨天的分区（任务一生成的分区）数据抽取到dwd库中fact_order_detail的动态分区表，分区字段为etl_date，类型为String，取create_time值并将格式转换为yyyyMMdd，并添加dwd_insert_user、dwd_insert_time、dwd_modify_user、dwd_modify_time四列，其中dwd_insert_user、dwd_modify_user均填写“user1”，dwd_insert_time、dwd_modify_time均填写当前操作时间，并进行数据类型转换。使用hive cli执行show partitions dwd.fact_order_detail命令；

提示：若完成实时任务二中的第3小题后，可将hbase中的数据合并后在进行清洗，清洗后数据添加至DWD中

任务三：指标计算

注：与订单金额计算相关使用order_money字段，同一个订单无需多次重复计算，需要考虑退款或者取消的订单

1. 根据dwd或者dws层表统计每人每天下单的数量和下单的总金额，存入dws层（需自建）的user_consumption_day_aggr表中（表结构如下），然后使用hive cli按照客户主键、订单总金额均为降序排序，查询出前5条；

字段	类型	中文含义	备注
customer_id	int	客户主键	customer_id
customer_name	string	客户名称	customer_name
total_amount	double	订单总金额	当天订单总金额
total_count	int	订单总数	当天订单总数
year	int	年	订单产生的年,为动态分区字段
month	int	月	订单产生的月,为动态分区字段
day	int	日	订单产生的日,为动态分区字段

1. 根据dwd或者dws层表统计每个城市每月下单的数量和下单的总金额（以order_master中的地址为判断依据），并按照province_name，year，month进行分组,按照total_amount逆序排序，形成sequence值，将计算结果存入Hive的dws数据库city_consumption_day_aggr表中（表结构如下），然后使用hive cli根据订单总数、订单总金额均为降序排序，查询出前5条，在查询时对于订单总金额字段将其转为bigint类型（避免用科学计数法展示）；

字段	类型	中文含义	备注
city_name	string	城市名称
province_name	string	省份名称
total_amount	double	订单总金额	当月订单总金额
total_count	int	订单总数	当月订单总数
sequence	int	次序	即当月中该城市消费额在该省中的排名
year	int	年	订单产生的年,为动态分区字段