目录
子任务一：数据抽取
编写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中。
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】中对应的任务序号下；
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】中对应的任务序号下；
3.抽取shtd_store库中base_province的增量数据进入Hive的ods库中表base_province。根据ods.base_province表中id作为增量字段，只将新增的数据抽入，字段名称、类型不变并添加字段create_time取当前时间，同时添加静态分区，分区字段类型为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命令，将结果截图粘贴至对应报告中；
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】中对应的任务序号下。
实现代码
（1）定义工具类
该工具类包含两个方法。loadJDBC方法负责加载mysql中的数据，appendHive方法负责将增量抽取的数据追加写入到hive的相关表中。
package com.hbzy.GZ03
import org.apache.orc.OrcProto.ColumnEncoding
import org.apache.spark.sql.{DataFrame, SparkSession}
// 负责mysql的链接和hive的写入
object gz03utils {
// 加载jdbc
def loadJDBC(sparkSession: SparkSession, jdbcMap:Map[String, String]):DataFrame ={
val dataframe: DataFrame = sparkSession.read.format("jdbc").options(jdbcMap).load()
dataframe
}
// 增量写入hive
def appendHive(sparkSession: SparkSession, dataFrame: DataFrame, hiveMap:Map[String, String]):Unit = {
val db = hiveMap("db") // 确定数据库
val tb = hiveMap("tb") // 确定表
val partitionColumn = hiveMap.get("partitionColumn") // 确定分区列
sparkSession.sql(s"use ${db}") // 使用插值法填充
// 有的表需要分区，有的不需要。这里使用模式匹配来分别处理
partitionColumn match {
case Some(column) => dataFrame
.write
.format("parquet") // 这里的关键是写入格式问题，不能用hive，要用parquet
.mode("append")
.partitionBy(column)
.saveAsTable(tb)
case None => dataFrame
.write
.format("parquet")
.mode("append")
.saveAsTable(tb)
}
}
}
（2）定义工作类
本题需要增量抽取相同的数据库的6张不同的表到hive数据库中。所以定义了6个方法，分别对应了6张表的抽取。user_info、sku_info、base_province、base_region、order_info、order_detail。抽取的具体方法和逻辑大同小异。首先需要编写hiveSQL的查询语句，查询出数据库中最大的时间或者id。接着根据时间或者id在mysql中进行查找。然后将查找到的结果再追加写入到hive的表中，同时增加相关的列。
package com.hbzy.GZ03
import com.hbzy.GZ03.gz03utils.{appendHive, loadJDBC}
import org.apache.spark.SparkConf
import org.apache.spark.sql.functions.{current_timestamp, lit}
import org.apache.spark.sql.{DataFrame, SparkSession}
import java.sql.Timestamp
object gz03job {
def main(args: Array[String]): Unit = {
val SparkConf = new SparkConf().setMaster("local[*]").setAppName("gz03job")
val session: SparkSession = SparkSession
.builder() // 环境
.config(SparkConf) // 各种配置项
.config("hive.metastore.uris", "thrift://192.168.79.132:9083") // 加载元数据
// 打开hive动态分区
.config("hive.exec.dynamic.partition", "true")
.config("hive.exec.dynamic.partition.mode", "nonstrict")
// 需要根据分区值，覆盖原来的分区时，需要配置的参数
// .config("spark.sql.source.partitionOverwriteMode", "dynamic")
.enableHiveSupport() // 获得hive支持
.getOrCreate() // 创建
// 增量抽取user_info
job_user_info(session)
// 增量抽取sku_info
job_sku_info(session)
// 增量抽取base_province
job_base_province(session)
// 增量抽取base_region
job_base_region(session)
// 增量抽取order_info
job_order_info(session)
// 增量抽取order_detail
job_order_detail(session)
}
// 增量抽取user_info operate_time或create_time
def job_user_info(sparkSession: SparkSession):Unit = {
// 首先计算出user_info存量数据的最大时间
val hive_max_time = "select greatest(max(operate_time), max(create_time)) max_time from ds_ods.user_info"
val df: DataFrame = sparkSession.sql(hive_max_time)
// df.show()
val max_time = df.first().getTimestamp(0)
// println(max_time)
// ---------查询mysql
// 定义mysql查询语句
val querySQL = s"select * from user_info where operate_time > '${max_time}' or create_time > '${max_time}'"
// 将查询mysql的options参数定义为jdbcMap
val jdbcMap = Map(
"driver" -> "com.mysql.jdbc.Driver",
"url" -> "jdbc:mysql://192.168.79.132:3306/shtd_store?useSSL=false",
"query" -> querySQL,
"user" -> "root", // 致命性小错误，这里的参数是user而不是username
"password" -> "admin"
)
// 查询
val frame: DataFrame = loadJDBC(sparkSession, jdbcMap)
frame.show()
// 增加静态分区
val frame1: DataFrame = frame.withColumn(colName = "etl_date", lit("20230820"))
// 定义hiveMap,写入hive访问的基本信息
val hiveMap = Map(
"db" -> "ds_ods",
"tb" -> "user_info",
"partitionColumn" -> "etl_date"
)
// 将分区后的数据增量写入hive中
appendHive(sparkSession, frame1, hiveMap)
}
// 抽取sku_info 条件create_time
def job_sku_info(sparkSession: SparkSession):Unit = {
// 计算出sku_info中的最大时间
// 编写hive查询语句
val hive_max_time = "select max(create_time) max_time from ds_ods.sku_info"
// 运行查询语句
val df: DataFrame = sparkSession.sql(hive_max_time)
df.show()
val max_time: Timestamp = df.first().getTimestamp(0)
println(max_time)
// 定义mysql查询语句
val querySQL = s"select * from sku_info where create_time > '${max_time}'"
// 定义jdbcMap
val jdbcMap = Map(
"driver" -> "com.mysql.jdbc.Driver",
"url" -> "jdbc:mysql://192.168.79.132:3306/shtd_store?useSSL",
"query" -> querySQL,
"user" -> "root",
"password" -> "admin"
)
// 执行查询
val frame: DataFrame = loadJDBC(sparkSession, jdbcMap)
frame.show()
// 增加静态分区
val frame1: DataFrame = frame.withColumn("etl_date", lit("20230820"))
// 定义hiveMap
val hiveMap = Map(
"db" -> "ds_ods",
"tb" -> "sku_info",
"partitionColumn" -> "etl_date"
)
// 写入
appendHive(sparkSession, frame1, hiveMap)
}
// 抽取base_province 条件id
def job_base_province(sparkSession: SparkSession):Unit={
// 编写hive查询语句，计算最大的id
val hive_max_id = "select max(id) max_id from ds_ods.base_province"
// 执行查询
val df: DataFrame = sparkSession.sql(hive_max_id)
// 数据转换
val max_id = df.first().getLong(0)
// 通过id来查询mysql
// 定义mysql查询语句
val querySQL = s"select * from base_province where id > '${max_id}'"
// 定义jdbcMap
val jdbcMap = Map(
"driver" -> "com.mysql.jdbc.Driver",
"url" -> "jdbc:mysql://192.168.79.132:3306/shtd_store?useSSL=false",
"query" -> querySQL,
"user" -> "root",
"password" -> "admin"
)
// 通过工具方法执行
val frame: DataFrame = loadJDBC(sparkSession, jdbcMap)
frame.show()
// 增加create_time以及etl_date分区
val frame1: DataFrame = frame
.withColumn("create_time", current_timestamp())
.withColumn("etl_date", lit("20230820"))
// 定义hiveMap
val hiveMap = Map(
"db" -> "ds_ods",
"tb" -> "base_province",
"partitionColumn" -> "etl_date"
)
// 执行增量抽取
appendHive(sparkSession, frame1, hiveMap)
}
// 增量抽取base_region 条件id
def job_base_region(sparkSession: SparkSession):Unit={
// 查询最大id
val hive_max_id = "select max(id) from ds_ods.base_region"
// 执行
val df: DataFrame = sparkSession.sql(hive_max_id)
// 转换 // base_region中的这张表的id类型为varchar导致抽取到的类型为string类型，string类型不能直接转换long类型
val max_id: Long = df.first().getString(0).toLong
println(max_id)
// 根据id来查询
val querySQL = s"select * from base_region where id > '${max_id}'"
// 编写jdbcMap
val jdbcMap = Map(
"driver" -> "com.mysql.jdbc.Driver",
"url" -> "jdbc:mysql://192.168.79.132/shtd_store?useSSL=false",
"query" -> querySQL,
"user" -> "root",
"password" -> "admin"
)
// 执行查询
val frame: DataFrame = loadJDBC(sparkSession, jdbcMap)
frame.show()
// 增加create_time和etl_date分区
val frame1: DataFrame = frame
.withColumn("create_time", current_timestamp())
.withColumn("etl_date", lit("20230820"))
// 编写hiveMap
val hiveMap = Map(
"db" -> "ds_ods",
"tb" -> "base_region",
"partitionColumn" -> "etl_date"
)
appendHive(sparkSession, frame1, hiveMap)
}
// 增量抽取order_info 条件operate_time和create_time
def job_order_info(sparkSession: SparkSession):Unit={
// 查询hive中的最大时间
val hive_max_time = "select greatest(max(create_time), max(operate_time)) max_time from ds_ods.order_info"
// 执行
val df: DataFrame = sparkSession.sql(hive_max_time)
// 转换
val max_time: Timestamp = df.first().getTimestamp(0)
println(max_time)
// 根据使时间查询
val querySQL = s"select * from order_info where operate_time > '${max_time}' or create_time > '${max_time}'"
// 定义jdbcMap
val jdbcMap = Map(
"driver" -> "com.mysql.jdbc.Driver",
"url" -> "jdbc:mysql://192.168.79.132/shtd_store?useSSL",
"query" -> querySQL,
"user" -> "root",
"password" -> "admin"
)
val frame: DataFrame = loadJDBC(sparkSession, jdbcMap)
frame.show()
// 增加列
val frame1: DataFrame = frame.withColumn("etl_date", lit("20230820"))
// 编写hiveMap
val hiveMap = Map(
"db" -> "ds_ods",
"tb" -> "order_info",
"partitionColumn" -> "etl_date"
)
// 执行
appendHive(sparkSession, frame1, hiveMap)
}
// 增量抽取order_detail 条件create_time 2020-04-25 18:47:14.0
def job_order_detail(sparkSession: SparkSession):Unit={
// 查询hive中的最大时间
val hive_max_time = "select max(create_time) from ds_ods.order_detail"
// 执行查询
val df: DataFrame = sparkSession.sql(hive_max_time)
// 转换
val max_time = df.first().getTimestamp(0)
println(max_time)
// 根据时间查询mysql
val querySQL = s"select * from order_detail where create_time > '${max_time}'"
// 编写jdbcMap
val jdbcMap = Map(
"driver" -> "com.mysql.jdbc.Driver",
"url" -> "jdbc:mysql://192.168.79.132:3306/shtd_store?useSSL",
"query" -> querySQL,
"user" -> "root",
"password" -> "admin"
)
// 查询
val frame: DataFrame = loadJDBC(sparkSession, jdbcMap)
frame.show()
// 增加分区
val frame1: DataFrame = frame.withColumn("etl_date", lit("20230820"))
// 编写hiveMap
val hiveMap = Map(
"db" -> "ds_ods",
"tb" -> "order_detail",
"partitionColumn" -> "etl_date"
)
appendHive(sparkSession, frame1, hiveMap)
}
}
422
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
