网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。
一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!
1:hive里面得表进行分析处理计算
2:存入mysql
三:代码实现
–还很贴心的每一步的实现都写了一个输出语句,看输出就知道自己卡在了哪里
计算1代码如下:
import java.util.Properties
import org.apache.spark.SparkConf
import org.apache.spark.sql.SparkSession
object cal01 {
def main(args: Array[String]): Unit = {
// 连接到 MySQL 数据库
val mysqlUrl = "jdbc:mysql://master:3306/shtd_store?useUnicode=true&characterEncoding=utf8"
val mysqlProperties = new Properties()
mysqlProperties.setProperty("user", "root")
mysqlProperties.setProperty("password", "1234")
// 连接hive
val conf = new SparkConf().setMaster("local").setAppName("hive")
val spark = SparkSession.builder().config(conf).enableHiveSupport().getOrCreate()
spark.sql("use dwd")
println("Connected to hive database: dwd")
// 执行 SQL 查询
println("----- provinceeverymonth -----")
val provinceeverymonth = spark.sql(
"""
|select
|distinct b.id province_id,
| b.name province_name,
| c.id region_id,
| c.region_name region_name,
| sum(a.final_total_amount) over(partition by b.id,c.id, month(a.create_time)) total_consumption,
| count(a.id) over(partition by b.id,c.id, month(a.create_time)) total_order
|from
|fact_order_info a
|join dim_province b on a.province_id = b.id
|join dim_region c on b.region_id = c.id
|""".stripMargin)
provinceeverymonth.show(200)
// 将结果写入 MySQL 的 shtd_store.provinceeverymonth 表中,并指定字符集编码为 utf8
provinceeverymonth.write.mode("overwrite").jdbc(mysqlUrl, "provinceeverymonth", mysqlProperties)
// 输出连接成功信息
println("Connected to MySQL database: shtd_store")
}
}
计算2代码如下:
import java.util.Properties
import org.apache.spark.SparkConf
import org.apache.spark.sql.SparkSession
object cal02 {
def main(args: Array[String]): Unit = {
// 连接到 MySQL 数据库
val mysqlUrl = "jdbc:mysql://master:3306/shtd_store?useUnicode=true&characterEncoding=utf8"
val mysqlProperties = new Properties()
mysqlProperties.setProperty("user", "root")
mysqlProperties.setProperty("password", "1234")
// 连接hive
val conf = new SparkConf().setMaster("local").setAppName("cal02")
val spark = SparkSession.builder().config(conf).enableHiveSupport().getOrCreate()
spark.sql("use dwd")
println("Connected to Hive database: dwd")
// 执行 SQL 查询
println("----- provinceavgcmp -----")
val provinceavgcmp = spark.sql(
"""
|SELECT
| DISTINCT b.id AS province_id,
| b.name AS province_name,
| AVG(a.final_total_amount) OVER (PARTITION BY b.id) AS every_province_avgconsumption,
| AVG(a.final_total_amount) OVER () AS all_province_avgconsumption,
| CASE
| WHEN AVG(a.final_total_amount) OVER (PARTITION BY b.id) > AVG(a.final_total_amount) OVER () THEN '大于'
| WHEN AVG(a.final_total_amount) OVER (PARTITION BY b.id) < AVG(a.final_total_amount) OVER () THEN '小于'
| ELSE '相同'
| END AS comparison
|FROM
| fact_order_info a
| JOIN dim_province b ON a.province_id = b.id
|WHERE
| MONTH(a.create_time) = 4 AND YEAR(a.create_time) = 2020
""".stripMargin)
provinceavgcmp.show(200)
// 将结果写入 MySQL 的 shtd_store.provinceavgcmp 表中
provinceavgcmp.write.mode("overwrite").jdbc(mysqlUrl, "provinceavgcmp", mysqlProperties)
// 输出连接成功信息
println("Connected to MySQL database: shtd_store")
}
}
计算3代码如下:
import java.util.Properties
import org.apache.spark.SparkConf // 导入SparkConf类
import org.apache.spark.sql.{SparkSession, functions} // 导入SparkSession和functions类
import org.apache.spark.sql.expressions.Window // 导入Window类用于分组排序
object cal03 {
def main(args: Array[String]): Unit = {
// 连接到 MySQL 数据库
val mysqlUrl = "jdbc:mysql://master:3306/shtd_store?useUnicode=true&characterEncoding=utf8"
val mysqlProperties = new Properties()
mysqlProperties.setProperty("user", "root")
mysqlProperties.setProperty("password", "1234")
// 创建 SparkSession,并启用 Hive 支持
val conf = new SparkConf().setMaster("local").setAppName("cal03")
val spark = SparkSession.builder().config(conf).enableHiveSupport().getOrCreate()
// 连接到 Hive 数据库
spark.sql("use dwd")
println("Connected to hive database: dwd")
import spark.implicits._
import org.apache.spark.sql.functions._
// 执行 SQL 查询
![img](https://img-blog.csdnimg.cn/img_convert/bede2e348974a16c663d2bcf308413ab.png)
![img](https://img-blog.csdnimg.cn/img_convert/6aafa44a2a14e73e660b40d8280f7c3e.png)
![img](https://img-blog.csdnimg.cn/img_convert/51a5ec31f0b959b78950176d04304fd7.png)
**既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,涵盖了95%以上大数据知识点,真正体系化!**
**由于文件比较多,这里只是将部分目录截图出来,全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频,并且后续会持续更新**
**[需要这份系统化资料的朋友,可以戳这里获取](https://bbs.csdn.net/forums/4f45ff00ff254613a03fab5e56a57acb)**
系化!**
**由于文件比较多,这里只是将部分目录截图出来,全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频,并且后续会持续更新**
**[需要这份系统化资料的朋友,可以戳这里获取](https://bbs.csdn.net/forums/4f45ff00ff254613a03fab5e56a57acb)**