spark读取mysql中数据并进行数据分析案例---mysql经典50题

一、读取mysql中的数据

1.数据存在school数据库中，因此需要建立与mysql的连接

   //连接mysql的url，school为数据库的名字
   //school后面的编码设置是为了访问中文数据的时候不会出现查询不到的情况
    val url = "jdbc:mysql://master:3306/school?useUnicode=true&characterEncoding=utf8"
    //将用户名和密码封装到一个 Properties对象中
    val prop = new Properties()
    prop.setProperty("user","root")
    prop.setProperty("password","123456")

2.创建SparkSession对象

val spark = SparkSession
      .builder()
      .appName("mysql50")
      .master("spark://master:7078")
      .getOrCreate()

其中master方法中的url要根据你实际的主机名及对应的端口号进行修改

3.隐式导包

import spark.implicits._

4.分别读取school数据库中的4张表

    val studentDf = spark.read.jdbc(url,"Student",prop)
    val teacherDf = spark.read.jdbc(url,"Teacher",prop)
    val courseDf = spark.read.jdbc(url,"Course",prop)
    val scoreDf = spark.read.jdbc(url,"Score",prop)

二、具体题目的分析与完成

1.查询成绩大于60的学生的姓名

    /*
    分析如下：
    A.做有条件的查询：查询s_score大于60的数据---filter/where
    B.按照学号将A中查询的结果与studentDf进行连接---join
    C.查询指定字段：姓名---select
    * /```
scoreDf
      .where("s_score>60")
      .join(studentDf,"s_id")
      .select("s_name")
      .show()

2.查询"01"课程比"02"课程成绩高的学生的信息及课程分数

/* A.将两个相同的scoreDf按照学号做连接---join
* B.有条件的查询---filter/where
* C.将B查询的结果与studentDf按照学号进行连接---join
* /
scoreDf
  .as("s1")
  .join(scoreDf.as("s2"),"s_id")
  .where($"s1.c_id"==="01" && $"s2.c_id"=="02" &&$"s1.s_score">$"s2.s_score")
  .join(studentDf,"s_id")
  .show()

3.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

    /*A.按照s_id进行分组---groupBy
    *B.对s_score求平均值---avg
    *C.有条件的查询---where/filter
    * D.和studentDF按照s_id进行连接
    * /
    scoreDf
      .groupBy("s_id")
      .avg("s_score")
      .where($"avg(s_score)">=60)
      .join(studentDf,"s_id")
      .select("s_id","s_name","avg(s_score)")
      .show()

4.按各科成绩进行排序，并显示排名

scoreDf
      .withColumn("rank",row_number over(
        Window.partitionBy("c_id").orderBy($"s_score".desc)))
      .show()