赛题来源2023年全国职业院校技能大赛赛题第5套任务B中数据抽取模块
子任务一:数据抽取
编写Scala代码,使用Spark将MySQL库中表ChangeRecord,BaseMachine,MachineData, ProduceRecord全量抽取到Hive的ods库中对应表changerecord,basemachine, machinedata,producerecord中。
第一题
抽取MySQL的shtd_industry库中ChangeRecord表的全量数据进入Hive的ods库中表changerecord,字段排序、类型不变,同时添加静态分区,分区字段为etldate,类型为String,且值为当前比赛日的前一天日期(分区字段格式为yyyyMMdd)。使用hive cli执行show partitions ods.changerecord命令,将hive cli的执行结果截图粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下
import org.apache.spark.SparkConf
import org.apache.spark.sql.functions.{current_date, date_format, date_sub}
import org.apache.spark.sql.{DataFrame, SparkSession}
object ODS01 {
def main(args: Array[String]): Unit = {
// 设置连接hadoop的用户
System.setProperty("HADOOP_USER_NAME", "root")
// 创建Spark连接
val conf = new SparkConf().setMaster("local[*]").setAppName("ODS01")
val spark = SparkSession.builder().config(conf).enableHiveSupport().getOrCreate()
spark.conf.set("hive.exec.dynamic.partition.mode", "nonstrict")
spark.sparkContext.setLogLevel("OFF")
// 连接mysql
val df: DataFrame = spark.read
.format("jdbc")
.option("Driver", "com.mysql.jdbc.Driver")
.option("url", "jdbc:mysql://192.168.88.102:3306/shtd_industry")
.option("user", "root")
.option("password", "123456")
.option("dbtable", "ChangeRecord")
.load()
// 将获取到的数据转换为临时视图并起别名
df.createTempView("ods")
// 由于是抽取全量数据,这里只需要添加分区字段就可以了
val result = df
.withColumn("etldate", date_format(date_sub(current_date(), 1), "yyyyMMdd"))
// 写出到hive中的changerecord表
result
.write
.format("hive")
.mode("append")
.partitionBy("etldate")
.saveAsTable("ods.changerecord")
// 查看分区,这里的sql语句在hive客户端执行
spark.sql("show partitions ods.changerecord")
spark.close()
}
}
第二题
抽取MySQL的shtd_industry库中BaseMachine表的全量数据进入Hive的ods库中表basemachine,字段排序、类型不变,同时添加静态分区,分区字段为etldate,类型为String,且值为当前比赛日的前一天日期(分区字段格式为yyyyMMdd)。使用hive cli执行show partitions ods.basemachine命令,将hive cli的执行结果截图粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下
import org.apache.spark.SparkConf
import org.apache.spark.sql.SparkSession
import org.apache.spark.sql.functions.{current_date, date_format, date_sub}
object ODS02 {
def main(args: Array[String]): Unit = {
System.setProperty("HADOOP_USER_NAME", "root")
val conf = new SparkConf().setMaster("local[*]").setAppName("ODS02")
val spark: SparkSession = SparkSession.builder().config(conf).enableHiveSupport().getOrCreate()
spark.conf.set("hive.exec.dynamic.partition.mode", "nonstrict")
spark.sparkContext.setLogLevel("OFF")
// 连接mysql
val df = spark.read
.format("jdbc")
.option("Driver", "com.mysql.jdbc.Driver")
.option("url", "jdbc:mysql://192.168.88.102:3306/shtd_industry")
.option("user", "root")
.option("password", "123456")
.option("dbtable", "BaseMachine")
.load()
// 将获取到的数据转换为临时视图并起别名
df.createTempView("ods")
// 添加分区字段
val result = df
.withColumn("etldate", date_format(date_sub(current_date(), 1), "yyyyMMdd"))
// 写出到hive中的changerecord表
result.write
.format("hive")
.mode("append")
.partitionBy("etldate")
.saveAsTable("ods.basemachine")
// 查看分区,这里的sql语句在hive客户端执行
spark.sql("show partitions ods.basemachine")
spark.close()
}
}
第三题
抽取MySQL的shtd_industry库中ProduceRecord表的全量数据进入Hive的ods库中表producerecord,剔除ProducePrgCode字段,其余字段排序、类型不变,同时添加静态分区,分区字段为etldate,类型为String,且值为当前比赛日的前一天日期(分区字段格式为yyyyMMdd)。使用hive cli执行show partitions ods.producerecord命令,将hive cli的执行结果截图粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下
import org.apache.spark.SparkConf
import org.apache.spark.sql.SparkSession
import org.apache.spark.sql.functions.{current_date, date_format, date_sub}
object ODS03 {
def main(args: Array[String]): Unit = {
System.setProperty("HADOOP_USER_NAME", "root")
val conf = new SparkConf().setMaster("local[*]").setAppName("ODS03")
val spark: SparkSession = SparkSession.builder().config(conf).enableHiveSupport().getOrCreate()
spark.conf.set("hive.exec.dynamic.partition.mode", "nonstrict")
spark.sparkContext.setLogLevel("OFF")
// 连接mysql
val df = spark.read
.format("jdbc")
.option("Driver", "com.mysql.jdbc.Driver")
.option("url", "jdbc:mysql://192.168.88.102:3306/shtd_industry")
.option("user", "root")
.option("password", "123456")
.option("dbtable", "ProduceRecord")
.load()
df.createTempView("ods")
// 查询出除了ProduceRecord之外的字段
val queryResult = spark.sql(
"""
| select
| id, -- 此处为示例字段,实际情况下为除了要求剔除的所有字段
| name
| from ods
|""".stripMargin)
// 添加分区字段
val result = queryResult
.withColumn("etldate", date_format(date_sub(current_date(), 1), "yyyyMMdd"))
// 写出到hive中的changerecord表
result.write
.format("hive")
.mode("append")
.partitionBy("etldate")
.saveAsTable("ods.producerecord")
// 查看分区,这里的sql语句在hive客户端执行
spark.sql("show partitions ods.producerecord")
spark.close()
}
}
第四题
抽取MySQL的shtd_industry库中MachineData表的全量数据进入Hive的ods库中表machinedata,字段排序、类型不变,同时添加静态分区,分区字段为etldate,类型为String,且值为当前比赛日的前一天日期(分区字段格式为yyyyMMdd)。使用hive cli执行show partitions ods.machinedata命令,将hive cli的执行结果截图粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下
import org.apache.spark.SparkConf
import org.apache.spark.sql.SparkSession
import org.apache.spark.sql.functions.{current_date, date_format, date_sub}
object ODS04 {
def main(args: Array[String]): Unit = {
System.setProperty("HADOOP_USER_NAME", "root")
val conf = new SparkConf().setMaster("local[*]").setAppName("ODS04")
val spark = SparkSession.builder().config(conf).enableHiveSupport().getOrCreate()
spark.conf.set("hive.exec.dynamic.partition.mode", "nonstrict")
spark.sparkContext.setLogLevel("OFF")
val df = spark.read
.format("jdbc")
.option("Driver", "com.mysql.jdbc.Driver")
.option("url", "jdbc:mysql://192.168.88.102:3306/shtd_industry")
.option("user", "root")
.option("password", "123456")
.option("dbtable", "MachineData")
.load()
df.createTempView("ods")
val queryResult = spark.sql("select * fom ods")
val result = queryResult
.withColumn("etldate", date_format(date_sub(current_date(), 1), "yyyyMMdd"))
result.write
.format("hive")
.mode("append")
.partitionBy("etldate")
.saveAsTable("ods.machinedata")
// 查看分区,这里的sql语句在hive客户端执行
spark.sql("show partitions ods.machinedata")
spark.close()
}
}
数据抽取中一个重要的写法就是如果需要剔除脏数据,可以直接使用read读取表,然后使用filter剔除 以下是实例代码
// 读取数据并剔除脏数据
val df = spark.read
.table("dwd.fact_produce_record")
.filter("ProduceCodeEndTime != '1900-01-01 00:00:00'")
df.createOrReplaceTempView("tempView")