子任务一：数据抽取
编写Scala代码，使用Spark将MySQL的shtd_store库中表user_info、sku_info、base_province、base_region、order_info、order_detail的数据增量抽取到Hive的ods库中对应表user_info、sku_info、base_province、base_region、order_info、order_detail中。(若ods库中部分表没有数据，正常抽取即可)
1、抽取shtd_store库中user_info的增量数据进入Hive的ods库中表user_info。根据ods.user_info表中operate_time或create_time作为增量字段(即MySQL中每条数据取这两个时间中较大的那个时间作为增量字段去和ods里的这两个字段中较大的时间进行比较)，只将新增的数据抽入，字段名称、类型不变，同时添加静态分区，分区字段为etl_date，类型为String，且值为当前比赛日的前一天日期（分区字段格式为yyyyMMdd）。使用hive cli执行show partitions ods.user_info命令，将结果截图粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下；
def main(args: Array[String]): Unit = {
val spark: SparkSession = sparkSessionUtils.getSession
import spark.implicits._
val maxOdsTime: String = spark.sql("select max(case when 'operate_time' > 'create_time' then operate_time else create_time end) re from ods.user_info")
.map((_: Row)(0).toString).collect()(0)
mysqlUtils.read(spark, "ds_db01", "user_info")
.withColumn("maxTime", when(
col("operate_time") > col("create_time"), col("operate_time")
).otherwise(
col("create_time")
))
.where(col("maxTime") > maxOdsTime)
.withColumn("etldate", lit("20230510"))
.drop("maxTime")
.write
.mode(saveMode = SaveMode.Append)
.saveAsTable("user_info")
sparkSessionUtils.close(spark)
}
2、抽取shtd_store库中sku_info的增量数据进入Hive的ods库中表sku_info。根据ods.sku_info表中create_time作为增量字段，只将新增的数据抽入，字段名称、类型不变，同时添加静态分区，分区字段为etl_date，类型为String，且值为当前比赛日的前一天日期（分区字段格式为yyyyMMdd）。使用hive cli执行show partitions ods.sku_info命令，将结果截图粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下；
def main(args: Array[String]): Unit = {
val spark = sparkSessionUtils.getSession
import spark.implicits. _
val ods_user_nfo: Dataset[Row] = spark
.table("ods.user_info")
.where(col("etldate") === "20230321")
val dwd_user_info: Dataset[Row] = spark
.table("dwd.dim_user_info")
.where("etldate = (select max(etldate) from dwd.dim_user_info)")
val merged_user_info: Dataset[Row] = ods_user_nfo.union(dwd_user_info)
.withColumn("rowNumber",
row_number()
.over(Window.partitionBy("id").orderBy(desc("operate_time")))
)
.where(col("rowNumber") === 1)
.drop("rowNumber")
val user_info_with_operate_time: DataFrame = merged_user_info.withColumn("operate_time",
when(col("operate_time").isNull, col("create_time")).otherwise(col("operate_time"))
)
val modify_timeFunction: UserDefinedFunction = spark
.udf
.register("modify_time", (id: Long) => {
val modify_time: String = dwd_user_info.filter((r: Row) => {
r.get(0).toString.toLong.equals(id)
}).select("dwd_modify_time").first().get(0).toString
modify_time
})
val ids: Array[Long] = dwd_user_info.select("id").map((_: Row) (0).toString.toLong).collect()
val user_info_with_dwd_cols: DataFrame = user_info_with_operate_time
.withColumn("dwd_insert_user", lit("user1"))
.withColumn("dwd_modify_user", lit("user1"))
.withColumn("dwd_insert_time",
when(col("id").isin(ids: _*), modify_timeFunction(col("id")))
.otherwise(date_format(current_timestamp(), "yyyy-MM-dd HH:mm:ss"))
)
.withColumn("dwd_modify_time",
when(col("dwd_modify_time").isNull, date_format(current_timestamp(), "yyyy-MM-dd HH:mm:ss"))
.otherwise(when(col("id").isin(ids: _*), date_format(current_timestamp(), "yyyy-MM-dd HH:mm:ss"))
.otherwise(col("dwd_modify_time"))
)
)
user_info_with_dwd_cols.write
.mode("overwrite")
.partitionBy("etldate")
.saveAsTable("dwd.dim_user_info")
}
3、抽取shtd_store库中base_province的增量数据进入Hive的ods库中表base_province。根据ods.base_province表中id作为增量字段，只将新增的数据抽入，字段名称、类型不变并添加字段create_time取当前时间，同时添加静态分区，分区字段为etl_date，类型为String，且值为当前比赛日的前一天日期（分区字段格式为yyyyMMdd）。使用hive cli执行show partitions ods.base_province命令，将结果截图粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下；
4、抽取shtd_store库中base_region的增量数据进入Hive的ods库中表base_region。根据ods.base_region表中id作为增量字段，只将新增的数据抽入，字段名称、类型不变并添加字段create_time取当前时间，同时添加静态分区，分区字段为etl_date，类型为String，且值为当前比赛日的前一天日期（分区字段格式为yyyyMMdd）。使用hive cli执行show partitions ods.base_region命令，将结果截图粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下；
5、抽取shtd_store库中order_info的增量数据进入Hive的ods库中表order_info，根据ods.order_info表中operate_time或create_time作为增量字段(即MySQL中每条数据取这两个时间中较大的那个时间作为增量字段去和ods里的这两个字段中较大的时间进行比较)，只将新增的数据抽入，字段名称、类型不变，同时添加静态分区，分区字段为etl_date，类型为String，且值为当前比赛日的前一天日期（分区字段格式为yyyyMMdd）。使用hive cli执行show partitions ods.order_info命令，将结果截图粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下；
6、抽取shtd_store库中order_detail的增量数据进入Hive的ods库中表order_detail，根据ods.order_detail表中create_time作为增量字段，只将新增的数据抽入，字段名称、类型不变，同时添加静态分区，分区字段为etl_date，类型为String，且值为当前比赛日的前一天日期（分区字段格式为yyyyMMdd）。使用hive cli执行show partitions ods.order_detail命令，将结果截图粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下。
子任务二：数据清洗
编写Scala代码，使用Spark将ods库中相应表数据全量抽取到Hive的dwd库中对应表中。表中有涉及到timestamp类型的，均要求按照yyyy-MM-dd HH:mm:ss，不记录毫秒数，若原数据中只有年月日，则在时分秒的位置添加00:00:00，添加之后使其符合yyyy-MM-dd HH:mm:ss。(若dwd库中部分表没有数据，正常抽取即可)
1、抽取ods库中user_info表中昨天的分区（子任务一生成的分区）数据，并结合dim_user_info最新分区现有的数据，根据id合并数据到dwd库中dim_user_info的分区表（合并是指对dwd层数据进行插入或修改，需修改的数据以id为合并字段，根据operate_time排序取最新的一条），分区字段为etl_date且值与ods库的相对应表该值相等，同时若operate_time为空，则用create_time填充，并添加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_user_info命令，将结果截图粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下；
2、抽取ods库sku_info表中昨天的分区（子任务一生成的分区）数据，并结合dim_sku_info最新分区现有的数据，根据id合并数据到dwd库中dim_sku_info的分区表（合并是指对dwd层数据进行插入或修改，需修改的数据以id为合并字段，根据create_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查询表dim_sku_info的字段id、sku_desc、dwd_insert_user、dwd_modify_time、etl_date，条件为最新分区的数据，id大于等于15且小于等于20，并且按照id升序排序，将结果截图粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下；
3、抽取ods库base_province表中昨天的分区（子任务一生成的分区）数据，并结合dim_province最新分区现有的数据，根据id合并数据到dwd库中dim_province的分区表（合并是指对dwd层数据进行插入或修改，需修改的数据以id为合并字段，根据create_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在表dwd.dim_province最新分区中，查询该分区中数据的条数，将结果截图粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下；
4、抽取ods库base_region表中昨天的分区（子任务一生成的分区）数据，并结合dim_region最新分区现有的数据，根据id合并数据到dwd库中dim_region的分区表（合并是指对dwd层数据进行插入或修改，需修改的数据以id为合并字段，根据create_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在表dwd.dim_region最新分区中，查询该分区中数据的条数，将结果截图粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下；
5、将ods库中order_info表昨天的分区（子任务一生成的分区）数据抽取到dwd库中fact_order_info的动态分区表，分区字段为etl_date，类型为String，取create_time值并将格式转换为yyyyMMdd，同时若operate_time为空，则用create_time填充，并添加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_info命令，将结果截图粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下；
6、将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命令，将结果截图粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下。
子任务三：指标计算
编写Scala代码，使用Spark计算相关指标。
注：在指标计算中，不考虑订单信息表中order_status字段的值，将所有订单视为有效订单。计算订单金额或订单总金额时只使用final_total_amount字段。需注意dwd所有的维表取最新的分区。
1、本任务基于以下2、3、4小题完成，使用Azkaban完成第2、3、4题任务代码的调度。工作流要求，使用shell输出“开始”作为工作流的第一个job（job1），2、3、4题任务为串行任务且它们依赖job1的完成（命名为job2、job3、job4），job2、job3、job4完成之后使用shell输出“结束”作为工作流的最后一个job（endjob），endjob依赖job2、job3、job4，并将最终任务调度完成后的工作流截图，将截图粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下；
2、根据dwd层表统计每个省份、每个地区、每个月下单的数量和下单的总金额，存入MySQL数据库shtd_result的provinceeverymonth表中（表结构如下），然后在Linux的MySQL命令行中根据订单总数、订单总金额、省份表主键均为降序排序，查询出前5条，将SQL语句复制粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下，将执行结果截图粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下;
字段 类型 中文含义 备注
| 字段 | 类型 | 中文含义 | 备注 |
| provinceid | int | 省份表主键 | |
| provincename | text | 省份名称 | |
| regionid | int | 地区表主键 | |
| regionname | text | 地区名称 | |
| totalconsumption | double | 订单总金额 | 当月订单总金额 |
| totalorder | int | 订单总数 | 当月订单总数 |
| year | int | 年 | 订单产生的年 |
| month | int | 月 | 订单产生的月 |
def main(args: Array[String]): Unit = {
val spark = sparkSessionUtils.getSession
val order_info = mysqlUtils.read(spark, "ds_db01", "order_info")
val base_province = mysqlUtils.read(spark, "ds_db01", "base_province")
val base_region = mysqlUtils.read(spark, "ds_db01", "base_region")
// 执行数据处理和分析逻辑
val result: DataFrame = order_info
.join(base_province, order_info("province_id") === base_province("id"))
.join(base_region, base_province("region_id") === base_region("id"))
.select(
base_province("id") as "provinceid",
base_province("name") as "provincename",
base_region("id") as "regionid",
base_region("region_name") as "regionname",
order_info("final_total_amount") as "amount",
year(col("create_time")) as "year",
month(col("create_time")) as "month"
)
.groupBy("provinceid", "provincename", "regionid", "regionname", "year", "month")
.agg(
sum(col("amount")) as "totalconsumption",
count(col("amount")) as "totalorder"
)
.select("provinceid", "provincename", "regionid", "regionname", "totalconsumption", "totalorder", "year", "month")
mysqlUtils.write(result, "shtd_result", "provinceeverymonth")
sparkSessionUtils.close(spark)
}
3、请根据dwd层表计算出2020年4月每个省份的平均订单金额和所有省份平均订单金额相比较结果（“高/低/相同”）,存入MySQL数据库shtd_result的provinceavgcmp表（表结构如下）中，然后在Linux的MySQL命令行中根据省份表主键、该省平均订单金额均为降序排序，查询出前5条，将SQL语句复制粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下，将执行结果截图粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下;
| 字段 | 类型 | 中文含义 | 备注 |
| provinceid | int | 省份表主键 | |
| provincename | text | 省份名称 | |
| provinceavgconsumption | double | 该省平均订单金额 | |
| allprovinceavgconsumption | double | 所有省平均订单金额 | |
| comparison | text | 比较结果 | 该省平均订单金额和所有省平均订单金额比较结果，值为：高/低/相同 |
def main(args: Array[String]): Unit = {
val spark = sparkSessionUtils.getSession
val order_info = mysqlUtils.read(spark, "ds_db01", "order_info")
val base_province = mysqlUtils.read(spark, "ds_db01", "base_province")
// 计算全省平均消费金额
val allprovinceavgconsumption: java.math.BigDecimal = order_info
.join(base_province, order_info("province_id") === base_province("id"))
.select(
base_province("id") as "provinceid",
base_province("name") as "provincename",
order_info("final_total_amount") as "amount"
)
.select(avg("amount") as "allprovinceavgconsumption")
.take(1)(0).getAs[java.math.BigDecimal]("allprovinceavgconsumption")
// 计算各省份的平均消费金额，并与全省平均值进行比较
val result: DataFrame = order_info
.join(base_province, order_info("province_id") === base_province("id"))
.select(
base_province("id") as "provinceid",
base_province("name") as "provincename",
order_info("final_total_amount") as "amount"
)
.groupBy("provinceid", "provincename")
.agg(
avg(col("amount")) as "provinceavgconsumption"
)
.withColumn("allprovinceavgconsumption", lit(allprovinceavgconsumption))
.withColumn("comparison",
when(col("provinceavgconsumption") > col("allprovinceavgconsumption"), "高")
.when(col("provinceavgconsumption") < col("allprovinceavgconsumption"), "低")
.otherwise("相同")
)
.select("provinceid", "provincename", "provinceavgconsumption", "allprovinceavgconsumption", "comparison")
mysqlUtils.write(result,"shtd_result","provinceavgcmp")
sparkSessionUtils.close(spark)
}
4、根据dwd层表统计在两天内连续下单并且下单金额保持增长的用户，存入MySQL数据库shtd_result的usercontinueorder表(表结构如下)中，然后在Linux的MySQL命令行中根据订单总数、订单总金额、客户主键均为降序排序，查询出前5条，将SQL语句复制粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下，将执行结果截图粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下；
| 字段 | 类型 | 中文含义 | 备注 |
| userid | int | 客户主键 | |
| username | text | 客户名称 | |
| day | text | 日 | 记录下单日的时间，格式为 yyyyMMdd_yyyyMMdd 例如： 20220101_20220102 |
| totalconsumption | double | 订单总金额 | 连续两天的订单总金额 |
| totalorder | int | 订单总数 | 连续两天的订单总数 |
def main(args: Array[String]): Unit = {
val spark = sparkSessionUtils.getSession
val order_info = mysqlUtils.read(spark, "ds_db01", "order_info")
val user_info = mysqlUtils.read(spark, "ds_db01", "user_info")
// 进行表连接操作，计算用户的消费情况
val dataFrame: DataFrame = order_info
.join(user_info, order_info("user_id") === user_info("id"))
.select(
user_info("id") as "userid",
user_info("name") as "username",
date_format(order_info("create_time"), "yyyy-MM-dd") as "create_time",
order_info("final_total_amount") as "amount"
)
.groupBy("userid", "username", "create_time")
.agg(
sum("amount") as "totalconsumption",
count("amount") as "totalorder"
)
// 进行自连接操作，查找连续两天消费金额增长的用户
val result: DataFrame = dataFrame.as("df1")
.join(dataFrame.as("df2"), col("df1.userid") === col("df2.userid") and datediff(col("df2.create_time"), col("df1.create_time")) === 1)
.withColumn("day", concat(date_format(col("df1.create_time"), "yyyyMMdd"), lit("_"), date_format(col("df2.create_time"), "yyyyMMdd")))
.filter(col("df1.totalconsumption") < col("df2.totalconsumption"))
.select(
col("df1.userid"),
col("df1.username"),
col("day"),
(col("df1.totalconsumption") + col("df2.totalconsumption")) as "totalconsumption",
(col("df1.totalorder") + col("df2.totalorder")) as "totalorder"
)
mysqlUtils.write(result,"shtd_result","usercontinueorder")
sparkSessionUtils.close(spark)
}
扫一扫
2729
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
