Spark RDD, application case of spark SQL

First step Prepare the environment first

1,IDEA Installed SDK yes 2.13.8 edition
2, newly build maven project , Depends on the following

 <!-- https://mvnrepository.com/artifact/org.apache.spark/spark-core -->
    <dependency>
      <groupId>org.apache.spark</groupId>
      <artifactId>spark-core_2.13</artifactId>
      <version>3.3.0</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.apache.spark/spark-sql -->
    <dependency>
      <groupId>org.apache.spark</groupId>
      <artifactId>spark-sql_2.13</artifactId>
      <version>3.3.0</version>
      <!--      <scope>provided</scope>-->
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.apache.spark/spark-streaming -->
    <dependency>
      <groupId>org.apache.spark</groupId>
      <artifactId>spark-streaming_2.13</artifactId>
      <version>3.3.0</version>
      <scope>provided</scope>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.apache.spark/spark-mllib -->
    <dependency>
      <groupId>org.apache.spark</groupId>
      <artifactId>spark-mllib_2.13</artifactId>
      <version>3.3.0</version>
      <scope>provided</scope>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.apache.spark/spark-hive -->
    <dependency>
      <groupId>org.apache.spark</groupId>
      <artifactId>spark-hive_2.13</artifactId>
      <version>3.3.0</version>
      <scope>provided</scope>
    </dependency>

Case study 1, Use spark sql Read json data

 Get ready json Formatted data 
{
    "name":"Michael","age":23}
{
    "name":"Andy", "age":30}
{
    "name":"Justin", "age":19}

The code is as follows

Case study 2 , Use spark sql Joint query of multiple tables

 Get ready emp The data table 
{
    "name": "zhangsan","age": 26,"depId": 1,"gender": " Woman ","salary": 20000}
{
    " name" : "lisi", "age": 36,"depld": 2,"gender": " male ","salary": 8500}
{
    "name": "wangwu", "age": 23,"depId": 1,"gender": " Woman ","salary": 5000}
{
    "name": "zhaoliu","age": 25,"depId": 3,"gender": " male ","salary": 7000}
{
    "name": "marry", "age": 19, "depId": 2,"gender": " male ","salary": 6600}
{
    "name": "Tom","age": 36,"depId": 4,"gender": " Woman ","salary" : 5000}
{
    "name": "kitty", "age": 43,"depId": 2,"gender" : " Woman ","salary": 6000}

 Prepare data of department table 
{
    "id": 1,"name": "Tech Department"}
{
    "id": 2,"name": "Fina Department"}
{
    "id": 3,"name": "HR Department"}

The code is as follows

package T6

import org.apache.spark.sql.SparkSession

object demo5 {
    

  def main(args: Array[String]): Unit = {
    
    val sparkSession = SparkSession.builder().appName("demo5").master("local").getOrCreate()

    import sparkSession.implicits._

    // Reading data 
    val emp_rdd = sparkSession.read.json("data/sql5.txt")
    val dept_rdd = sparkSession.read.json("data/sql6.txt")

    // hold json Data format conversion table 
    emp_rdd.createOrReplaceTempView("emp")
    dept_rdd.createOrReplaceTempView("dept")
    // Get the name of each employee , Department name , Wages 
    sparkSession.sql("select e.name,d.name,e.salary from emp e,dept d where e.depId=d.id").show()
    // Get the name of the highest paid female employee , Department name , Age 
    sparkSession.sql("select e.name,d.name,e.salary,e.age from emp e,dept d where e.depId=d.id and salary=" +
      " (select max(salary) from emp where gender=' Woman ')").show()
    // Get the average salary of female employees 
    sparkSession.sql("select avg(salary) from emp where gender=' Woman ' ").show()
   // select e.name,d.name,e.salary,e.age from emp e,dept d where e.depId=d.id and salary=

  }
}

Case study 3
Prepare the data

 full name 	 subject 1	 subject 2	 subject 3	 subject 4
 Little kara 	70	70	75	60
 Little black 	80	80	70	40
 Xiaoxian 	60	70	80	45
 Xiaohao 	70	80	90	100

1, Ask for the total score of each student's subject
2, Calculate the total score of each subject
, The code is as follows

package com.T5

import org.apache.spark.{
    SparkConf, SparkContext}

object demo5 {
    

  def main(args: Array[String]): Unit = {
    
    var sparkConf = new SparkConf().setAppName("demo5").setMaster("local")
    var sc = new SparkContext(sparkConf)

    // Define file path 
    var filepath = "data/aa.txt"
    // Get the first line 
    var firstfile = sc.textFile(filepath).first()
    // Ask for the total score of each student 
    sc.textFile(filepath).filter(!_.equals(firstfile)).map(line=>{
    
      // Cut each line 
      var arr = line.split("\t")
      var name = arr(0)
      var km1 = arr(1).toInt
      var km2 = arr(2).toInt
      var km3 = arr(3).toInt
      var km4 = arr(4).toInt
      // Output 
      (name,km1+km2+km3+km4)

    }).foreach(println)

    // Find the total score of each subject 
    var km1 = 0
    var km2 = 0
    var km3 = 0
    var km4 = 0
    var i =0
    sc.textFile(filepath).filter(!_.equals(firstfile)).map(line=>{
    
      var arr = line.split("\t")
      // Output 
      (arr(1).toInt,arr(2).toInt,arr(3).toInt,arr(4).toInt)
    }).foreach(
      x=>{
    
        km1+=x._1
        km2+=x._2
        km3+=x._3
        km4+=x._4
        i+=1
        if (i==4){
    
          println((" subject 1",km1),(" subject 2",km2),(" subject 3",km3),(" subject 4",km4))
        }
      }
    )

  }

}