电商数据处理训练任务

2023-09-19 10:46 作者:_邪月大魔王 0人读过 | 我要投稿

说明：此文章主要为（任务一）数据抽取、（任务二）数据清洗及（任务三）指标计算前五题，为另一文章“电商数据处理_指标计算”的优化（后面五题懒得写），简洁度及性能有较大的提高。Spark版本为3.1.1，Hadoop版本为3.1.3，Scala版本为2.12，Hive版本为3.1.2。

任务一：数据抽取

使用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中。（for循环秒了）

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命令；

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

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

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

任务二：数据清洗

编写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命令；屎山代码（不是）

2、抽取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命令；

3、将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_master命令；

4、将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命令；

任务三：指标计算

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

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

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

3、请根据dwd或者dws层表计算出每个城市每个月平均订单金额和该城市所在省份平均订单金额相比较结果（“高/低/相同”）,存入ClickHouse数据库shtd_result的cityavgcmpprovince表中（表结构如下），然后在Linux的ClickHouse命令行中根据城市平均订单金额、省份平均订单金额均为降序排序，查询出前5条；

4、请根据dwd或者dws层表计算出每个城市每个月平均订单金额和该城市所在省份订单金额中位数相比较结果（“高/低/相同”）,存入ClickHouse数据库shtd_result的citymidcmpprovince表中（表结构如下），然后在Linux的ClickHouse命令行中根据城市平均订单金额、省份平均订单金额均为降序排序，查询出前5条；