有问题可以私聊我交流
前面的看我的上两篇文章即可
上一篇是把数据清洗到dwd层
这一篇是根据dwd层的数据进行数据分析
分析完的数据放到mysql里
直接上代码
//上一篇文章已经把数据从ods层清洗到了dwd层了
//这一篇文章,我们直接从dwd层抽取数据,进行数据分析
//根据dwd层中的订单表和客户表,找出消费额最高的五位用户,存入mysql中
val sparkSession = SparkSession
.builder()
.appName("topfive")
.enableHiveSupport()
.getOrCreate()
def saveMysql(sparkSession: SparkSession,tableName:String): DataFrame ={
val URL = "jdbc:mysql:// 数据库IP地址 / 库名"
val jdbcMap=Map(
"driver" -> "com.mysql.jdbc.Driver",
"url"->URL,
"dbtable"->tableName,
"user"->"root",
"password"->"123456"
)
sparkSession.read.format("jdbc").options(jdbcMap).load()
}
sparkSession.sql("use dwd")
val DF = sparkSession.sql(
"""
|select * from (
|select custkey,name,total,rank() over (order by total desc ) as priceRank
|from (
|select dcustomer.custkey,name, sum(totalprice) as total
|from dcustomer
|left join dwdorders on dcustomer.custkey = dwdorders.custkey
|group by dcustomer.custkey,name
|) cust_total ) temp where priceRank <= 5
|""".stripMargin)
DF.createOrReplaceTempView("temp")
val sqlDF = saveMysql(sparkSession,"topfive")
sqlDF.createOrReplaceTempView("topfive")
sparkSession.sql(
"""
|insert overwrite table topfive
|select * from temp
|""".stripMargin)
sparkSession.close()
后面的也看我的上两篇文章即可