赛题来源2023年全国职业院校技能大赛赛题第1套任务B中数据清洗模块
子任务二：数据清洗
编写Scala代码，使用Spark将ods库中相应表数据全量抽取到Hive的dwd库中对应表中。表中有涉及到timestamp类型的，均要求按照yyyy-MM-dd HH:mm:ss，不记录毫秒数，若原数据中只有年月日，则在时分秒的位置添加00:00:00，添加之后使其符合yyyy-MM-dd HH:mm:ss。
第一题
抽取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】中对应的任务序号下
import org.apache.spark.SparkConf
import org.apache.spark.sql.SparkSession
object DWD01 {
def main(args: Array[String]): Unit = {
System.setProperty("HADOOP_USER_NAME", "atguigu")
// TODO 创建Spark连接
val conf = new SparkConf().setMaster("local[*]").setAppName("DWD01")
val spark = SparkSession.builder().config(conf).enableHiveSupport().getOrCreate()
spark.conf.set("hive.exec.dynamic.partition.mode", "nonstrict")
spark.sparkContext.setLogLevel("OFF")
// TODO 执行核心SQL
val result = spark.sql(
"""
|select
| new.id,
| new.login_name,
| new.nick_name,
| new.passwd,
| new.name,
| new.phone_num,
| new.email,
| new.head_img,
| new.user_level,
| new.birthday,
| new.gender,
| new.create_time,
| `if`(nvl(new.operate_time), new.create_time, new.operate_time) as `operate_time`,
| new.status,
| 'user1' dwd_insert_user,
| `if`(nvl(new.operate_time),
| new.operate_time,
| date_format(`current_date`(), 'yyyy-MM-dd HH:mm:ss')) dwd_insert_time,
| 'user1' dwd_modify_user,
| date_format(`current_date`(), 'yyyy-MM-dd HH:mm:ss') dwd_modify_time,
| new.etldate
|from (
| select
| id,
| login_name,
| nick_name,
| passwd,
| name,
| phone_num,
| email,
| head_img,
| user_level,
| birthday,
| gender,
| create_time,
| operate_time,
| status,
| etldate
| from ods.user_info
| where etldate = '20240101'
|) new
|left join (
| select
| id,
| login_name,
| nick_name,
| passwd,
| name,
| phone_num,
| email,
| head_img,
| user_level,
| birthday,
| gender,
| create_time,
| operate_time,
| status,
| etldate
| from dwd.user_info
| where etldate = (
| select -- 这里使用直接在查询条件中使用子查询返回单个值
| max(etldate) max_time
| from dwd.user_info
| )
|) old
|on old.id = new.id
|""".stripMargin)
// TODO 查询结果导出
result
.write
.mode("append")
.partitionBy("etldate")
.saveAsTable("dwd.dim_user_info")
// TODO 关闭spark连接
spark.close()
}
}第二题
抽取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】中对应的任务序号下
import org.apache.spark.SparkConf
import org.apache.spark.sql.SparkSession
object DWD02 {
def main(args: Array[String]): Unit = {
// TODO 创建Spark连接
val conf = new SparkConf().setMaster("local[*]").setAppName("DWD02")
val spark = SparkSession.builder().config(conf).enableHiveSupport().getOrCreate()
spark.conf.set("hive.exec.dynamic.partition.mode", "nonstrict")
spark.sparkContext.setLogLevel("OFF")
// TODO 执行核心SQL
val result = spark.sql(
"""
|select new.id,
| new.spu_id,
| new.price,
| new.sku_name,
| new.sku_desc,
| new.weight,
| new.tm_id,
| new.category3_id,
| new.sku_default_img,
| new.is_sale,
| new.create_time,
| new.operate_time,
| 'user1' dwd_insert_user,
| `if`(nvl(new.operate_time), new.operate_time,date_format(`current_date`(), 'yyyy-MM-dd HH:mm:ss')) dwd_insert_time,
| 'user1' dwd_modify_user,
| date_format(`current_date`(), 'yyyy-MM-dd HH:mm:ss') dwd_modify_time,
| new.etldate
|from (
| select id,
| spu_id,
| price,
| sku_name,
| sku_desc,
| weight,
| tm_id,
| category3_id,
| sku_default_img,
| is_sale,
| create_time,
| operate_time,
| etldate
| from ods.sku_info
| where etldate = '20240101'
|) new
|left join
|(
| select id,
| spu_id,
| price,
| sku_name,
| sku_desc,
| weight,
| tm_id,
| category3_id,
| sku_default_img,
| is_sale,
| create_time,
| operate_time,
| etldate
| from dwd.dim_sku_info
| where etldate = (
| select max(etldate)
| from dwd.dim_sku_info
| )
|) old
|on old.id = new.id
|""".stripMargin)
// TODO 查询结果导出
result
.write
.mode("append")
.partitionBy("etldate")
.saveAsTable("dwd.dim_sku_info")
// TODO 关闭spark连接
spark.close()
}
}使用hive cli查询表dim_sku_info的字段id、sku_desc、dwd_insert_user、dwd_modify_time、etl_date，条件为最新分区的数据，id大于等于15且小于等于20，并且按照id升序排序
select
id,
sku_desc,
dwd_insert_user,
dwd_modify_time,
etldate
from dwd.dim_sku_info
where etldate = (
select max(etldate)
from dwd.dwd.dim_sku_info
)
and id between 15 and 20
order by id asc;第三题
抽取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】中对应的任务序号下
import org.apache.spark.SparkConf
import org.apache.spark.sql.SparkSession
object DWD03 {
def main(args: Array[String]): Unit = {
System.setProperty("HADOOP_USER_NAME", "atguigu")
// TODO 创建Spark连接
val conf = new SparkConf().setMaster("local[*]").setAppName("DWD03")
val spark = SparkSession.builder().config(conf).enableHiveSupport().getOrCreate()
spark.conf.set("hive.exec.dynamic.partition.mode", "nonstrict")
spark.sparkContext.setLogLevel("OFF")
// TODO 执行核心SQL
val result = spark.sql(
"""
|select
| new.id,
| new.name,
| new.region_id,
| new.area_code,
| new.iso_code,
| new.iso_3166_2,
| new.create_time,
| new.operate_time,
| 'user1' dwd_insert_user,
| `if`(nvl(new.operate_time), new.operate_time,date_format(`current_date`(), 'yyyy-MM-dd HH:mm:ss')) dwd_insert_time,
| 'user1' dwd_modify_user,
| date_format(`current_date`(), 'yyyy-MM-dd HH:mm:ss') dwd_modify_time,
| new.etldate
|from (
| select id,
| name,
| region_id,
| area_code,
| iso_code,
| iso_3166_2,
| create_time,
| operate_time,
| etldate
| from ods.base_province
| where etldate = '20240101'
|) new
|left join
|(
| select id,
| name,
| region_id,
| area_code,
| iso_code,
| iso_3166_2,
| create_time,
| operate_time,
| etldate
| from dwd.base_province
| where etldate = (
| select max(etldate)
| from dwd.base_province
| )
|) old
|on old.id = new.id
|""".stripMargin)
// TODO 查询结果导出
result
.write
.mode("append")
.partitionBy("etldate")
.saveAsTable("dwd.dim_province")
// TODO 关闭spark连接
spark.close()
}
}使用hive cli在表dwd.dim_province最新分区中，查询该分区中数据的条数
select count(*)
from dwd.dim_province
where etldate = (
select max(etldate)
from dwd.base_province
);第四题
抽取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】中对应的任务序号下
import org.apache.spark.SparkConf
import org.apache.spark.sql.SparkSession
object DWD04 {
def main(args: Array[String]): Unit = {
System.setProperty("HADOOP_USER_NAME", "atguigu")
// TODO 创建Spark连接
val conf = new SparkConf().setMaster("local[*]").setAppName("DWD04")
val spark = SparkSession.builder().config(conf).enableHiveSupport().getOrCreate()
spark.conf.set("hive.exec.dynamic.partition.mode", "nonstrict")
spark.sparkContext.setLogLevel("OFF")
// TODO 执行核心SQL
val result = spark.sql(
"""
|select
| new.id,
| new.region_name,
| new.create_time,
| new.operate_time,
| 'user1' dwd_insert_user,
| `if`(nvl(new.operate_time),
| date_format(`current_date`(), 'yyyyMMdd HH:mm:ss'),
| new.operate_time) dwd_insert_time,
| 'user1' dwd_modify_user,
| date_format(`current_date`(), 'yyyyMMdd HH:mm:ss') dwd_modify_time,
| new.etldate
|from (
| select
| id,
| region_name,
| create_time,
| operate_time,
| etldate
| from ods.base_region
| where etldate = '20240101'
|) new
|left join (
| select
| id,
| region_name,
| create_time,
| operate_time,
| etldate
| from dwd.dim_region
| where etldate = (
| select max(etldate)
| from dwd.dim_region
| )
|) old on old.id = new.id
|""".stripMargin)
// TODO 查询结果导出
result
.write
.mode("append")
.partitionBy("etldate")
.saveAsTable("dwd.dim_region")
// TODO 关闭spark连接
spark.close()
}
}使用hive cli在表dwd.dim_region最新分区中，查询该分区中数据的条数
select
count(*)
from dwd.dim_region
where etldate = (
select max(etldate)
from dwd.dim_region
);第五题
将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】中对应的任务序号下
import org.apache.spark.SparkConf
import org.apache.spark.sql.SparkSession
object DWD05 {
def main(args: Array[String]): Unit = {
System.setProperty("HADOOP_USER_NAME", "atguigu")
// TODO 创建Spark连接
val conf = new SparkConf().setMaster("local[*]").setAppName("DWD05")
val spark = SparkSession.builder().config(conf).enableHiveSupport().getOrCreate()
spark.conf.set("hive.exec.dynamic.partition.mode", "nonstrict")
spark.sparkContext.setLogLevel("OFF")
// TODO 执行核心SQL
val result = spark.sql(
"""
|select id,
| consignee,
| consignee_tel,
| total_amount,
| order_status,
| user_id,
| payment_way,
| delivery_address,
| order_comment,
| out_trade_no,
| trade_body,
| create_time,
| if(nvl(operate_time), create_time) as operate_time,
| expire_time,
| process_status,
| tracking_no,
| parent_order_id,
| img_url,
| province_id,
| activity_reduce_amount,
| coupon_reduce_amount,
| original_total_amount,
| feight_fee,
| feight_fee_reduce,
| refundable_time,
| 'user1' dwd_insert_user,
| `if`(nvl(new.operate_time),
| date_format(`current_date`(), 'yyyyMMdd HH:mm:ss'),
| new.operate_time) dwd_insert_time,
| 'user1' dwd_modify_user,
| date_format(`current_date`(), 'yyyyMMdd HH:mm:ss') dwd_modify_time,
| date_format(create_time, 'yyyyMMdd') as etldate
|from ods.order_info
|where etldate = '20240101'
|""".stripMargin)
// TODO 查询结果导出
result
.write
.mode("append")
.partitionBy("etldate")
.saveAsTable("dwd.fact_order_info")
// TODO 关闭spark连接
spark.close()
}
}第六题
将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】中对应的任务序号下
import org.apache.spark.SparkConf
import org.apache.spark.sql.SparkSession
object DWD06 {
def main(args: Array[String]): Unit = {
System.setProperty("HADOOP_USER_NAME", "atguigu")
// TODO 创建Spark连接
val conf = new SparkConf().setMaster("local[*]").setAppName("DWD06")
val spark = SparkSession.builder().config(conf).enableHiveSupport().getOrCreate()
spark.conf.set("hive.exec.dynamic.partition.mode", "nonstrict")
spark.sparkContext.setLogLevel("OFF")
// TODO 执行核心SQL
val result = spark.sql(
"""
|select
| id,
| order_id,
| sku_id,
| sku_name,
| img_url,
| order_price,
| sku_num,
| create_time,
| split_total_amount,
| split_activity_amount,
| split_coupon_amount,
| operate_time,
| date_format(create_time, 'yyyMMdd') as etldate
|from ods.order_detail
|where etldate = '20240101'
|""".stripMargin)
// TODO 查询结果导出
result
.write
.mode("append")
.partitionBy("etldate")
.saveAsTable("dwd.fact_order_detail")
// TODO 关闭spark连接
spark.close()
}
}我认为数据清理是离线数据处理模块中最轻松的部分，此部分只需要编写固定的SparkSQL或HiveSQL代码就可以实现数据清理的效果，因此在这里要尽量不失分
2202
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
