SparkSQL快速入门（详解）

Spark SQL

一、概述

http://spark.apache.org/docs/latest/sql-programming-guide.html

Spark SQL是Spark的核心模块主要用以对结构化的数据（流&批）进行处理。Spark SQL依然是建立在RDD之上的ETL工具（数据源到数据仓库的一系列处理过程）。Spark SQL在RDD之上抽象出来Dataset(数据集)和DataFrame（数据帧）提供类似RDD的处理功能，对数据进行处理时有两种方式一种强类型（对象）和无类型操作（SQL字段)，最后可以将计算你的结果写出到外部的存储系统；

Dataset和DataFrame的区别？

Dataset代表的是一个分布式数据集。Dataset是Spark1.6版本提出新的API，该API建立在RDD之上，同时可以借助于Spark SQL执行优化引擎开发高效的Spark应用。可以理解为一个功能增强的RDD；

DataFrame是Dataset的一种特殊形式，如：在Dataset中可以存储任意对象类型的数据作为Dataset的元素，但是DataFrame中元素的类型只能是Row类型，Row类似于数据库行，基于Row查询和传统数据库的ResultSet操作极其相似。因为Row类型的数据时DataaFrame中的一个元素，类似于数据库的一行记录，这些行中的元素都可以通过下标或者列名方式访问获取。

RDD —> DataSet(加强RDD)—> DataFrame（特殊Dataset[Row]）

二、入门案列

导入依赖

<dependency>
  <groupId>org.apache.spark</groupId>
  <artifactId>spark-core_2.11</artifactId>
  <version>2.4.4</version>
</dependency>
<dependency>
  <groupId>org.apache.spark</groupId>
  <artifactId>spark-sql_2.11</artifactId>
  <version>2.4.4</version>
</dependency>

开发应用

package quickstart

import org.apache.spark.sql.SparkSession

object SparkSQLWordCount {
  def main(args: Array[String]): Unit = {
    //1. 构建Spark SQL中核心对象SparkSession
    val spark = SparkSession.builder().appName("wordcount").master("local[*]").getOrCreate()

    //2. 通过spark session对象构建dataset或者dataframe
    val rdd = spark.sparkContext.makeRDD(List("Hello Hadoop", "Hello Kafka"))

    // rdd转换为ds或者df
    import spark.implicits._
    // scala隐式转换
    val dataset = rdd.toDS()

    // 如：强类型操作(操作的是类型) + 无类型操作(操作的是字段)
    // 方法一：
    /*
    dataset
      .flatMap(_.split("\\s"))
      .map((_,1))
      .groupBy("_1")  //  无类型操作 _1 第一列值 Word
      .sum("_2")  //  无类型操作 _2  1+1
      .show()               // 展示最终的处理结果
    */


    // 方法二：
    val flatMapDS = dataset
      .flatMap(_.split("\\s"))

    // 基于flatMapDS创建一个表t_word
    flatMapDS.createTempView("t_word")

    spark
      .sql("select value as word,count(value) from t_word group by value")
      .show()

    spark.stop()
  }
}

+------+------------+
|  word|count(value)|
+------+------------+
| Kafka|           1|
| Hello|           2|
|Hadoop|           1|
+------+------------+

三、DataSet和DataFrame的创建方法

DataSet

样例类

package datasource

import org.apache.spark.sql.SparkSession

object DataSetCreateDemo {
  def main(args: Array[String]): Unit = {
    val aa = SparkSession.builder().appName("datasource").master("local[*]").getOrCreate()
    val rdd = aa.sparkContext.makeRDD(List(Dog(1,"小黑"),Dog(2,"小白")))
    // rdd ---> DS
    import aa.implicits._
    val ds = rdd.toDS()
    ds.show()

    aa.stop()
  }
}
case class Dog(id:Int,name:String)

Tuple元组

val rdd = aa.sparkContext.makeRDD(List((1,"小黑",true),(2,"小白",false)))
// _n 表示ds中的第n列
val ds = rdd.toDS()
ds.show()

JSON数据

# 测试数据
{"id":1,"name": "zs","sex":true}
{"id":2,"name": "ls","sex":false}
{"id":3,"name": "ww","sex":true}


val ds:Dataset[User] = aa
      .read
      .json("/Users/gaozhy/工作/代码仓库/训练营备课代码/BigData/sparksql/src/main/resources")
      // int 类型bigInt处理
      .as[User]


case class User(id:Long,name:String,sex:Boolean)

文件系统

// spark版本的nginx访问日志分析
val rdd = aa.sparkContext.textFile("hdfs://spark:9000/access.log")
val ds = rdd.toDS()
ds
.map(line => {
  val regex: String = "^(\\d{1,3}\\.\\d{1,3}\\.\\d{1,3}\\.\\d{1,3}).*\\[(.*)\\]\\s\"(\\w{3,6})\\s(.*)\\sHTTP\\/1.1\"\\s(\\d{3})\\s(.*)$"
  val pattern: Pattern = Pattern.compile(regex, Pattern.MULTILINE)
  val matcher: Matcher = pattern.matcher(line)
  matcher.find()
  val ip = matcher.group(1)
  val time = matcher.group(2)
  val method = matcher.group(3)
  val uri = matcher.group(4)
  val status = matcher.group(5)
  val responseSize = matcher.group(6)
  (ip, time, method, uri, status, responseSize)
})
// 统计状态码分布情况  200 2
// Sql: select status,count(status) from t_log group by status
.groupByKey(t6 => t6._5)
.agg(typed.count(t6 => t6._5))
.withColumnRenamed("value","status")
.withColumnRenamed("TypedCount(scala.Tuple6)","count")
.show()

DataSet有两种创建方法

• 通过RDD隐式转换获得
• 通过Dataset转换操作获得

DataFrame

类似于DataSet，DataFrame是一个命名列的数据集(Dataset[Row])，使用时建议使用无类型操作

样例类

val rdd = spark.sparkContext.makeRDD(List(Person(1, "zs"), Person(2, "ls")))
// rdd ---> DataFrame
//val df = rdd.toDF()
val df = rdd.toDF("newId", "newName") // 列名
df
.show()

+-----+-------+
|newId|newName|
+-----+-------+
|    1|     zs|
|    2|     ls|
+-----+-------+

元组

val rdd = spark.sparkContext.makeRDD(List((1, "zs"), (2, "ls")))
val df = rdd.toDF("id","name")
df.show()

+---+----+
| id|name|
+---+----+
|  1|  zs|
|  2|  ls|
+---+----+

JSON数据

val df = spark.read.json("/Users/gaozhy/工作/代码仓库/训练营备课代码/BigData/sparksql/src/main/resources")
df.show()

+---+----+-----+
| id|name|  sex|
+---+----+-----+
|  4| zs1| true|
|  5| ls2|false|
|  6| ww3| true|
|  1|  zs| true|
|  2|  ls|false|
|  3|  ww| true|
+---+----+-----+

文件系统（重点）

val rdd = spark.sparkContext.textFile("hdfs://spark:9000/access.log")
val df = rdd.toDF
df.show()

RDD[Row]转换为DF（重点）

// RDD[Person] ---> RDD[Row]
val rdd = spark.sparkContext.makeRDD(List(Person(1, "zs"), Person(2, "ls")))
val rdd2: RDD[Row] = rdd
.map(person => Row(person.id, person.name))

// structType 主要定义的是结果表的schema
val structType = new StructType()
// 列名 + 数据类型 + 是否允许为空
.add("id", IntegerType, true, "注释1")
.add(name = "name", StringType, true, "注释2")

// RDD[Row]  ---> DF
val df = spark.createDataFrame(rdd2, structType)
df.show()


+---+----+
| id|name|
+---+----+
|  1|  zs|
|  2|  ls|
+---+----+

四、强类型和无类型操作

所谓强类型操作操作的是对象类型，而无类型操作操作的字段

Dataset强类型操作

package operation.strong

import org.apache.spark.sql.SparkSession
import org.apache.spark.sql.expressions.scalalang.typed

/**
 * dataset的强类型操作
 */
object DatasetStrongTypedOperationDemo {
  def main(args: Array[String]): Unit = {
    //1. 构建Spark SQL中核心对象SparkSession
    val spark = SparkSession.builder().appName("wordcount").master("local[*]").getOrCreate()

    //2. 通过spark session对象构建dataset或者dataframe
    val rdd = spark.sparkContext.makeRDD(List("Hello Hadoop", "Hello Kafka"))

    // rdd转换为ds或者df
    import spark.implicits._
    // scala隐式转换
    val dataset = rdd.toDS()

    // 方法一：
    dataset
      .flatMap(line => line.split("\\s"))
      .map(word => (word, 1))
      .groupByKey(t2 => t2._1)
      .agg(typed.sum(t2 => t2._2))
      .show() // 展示最终的处理结果

    spark.stop()
  }
}

+------+----------------------------+
| value|TypedSumDouble(scala.Tuple2)|
+------+----------------------------+
| Kafka|                         1.0|
| Hello|                         2.0|
|Hadoop|                         1.0|
+------+----------------------------+

Dataset无类型操作

package operation.strong

import org.apache.spark.sql.SparkSession

/**
 * dataset的无类型操作
 */
object DatasetUnTypedOperationDemo {
  def main(args: Array[String]): Unit = {
    //1. 构建Spark SQL中核心对象SparkSession
    val spark = SparkSession.builder().appName("wordcount").master("local[*]").getOrCreate()

    //2. 通过spark session对象构建dataset或者dataframe
    val rdd = spark.sparkContext.makeRDD(List("Hello Hadoop", "Hello Kafka"))

    // rdd转换为ds或者df
    import spark.implicits._
    val ds = rdd
      .flatMap(line => line.split("\\s"))
      .map((_, 1))
      .toDS

    ds
      .groupBy($"_1") // 隐式转换 $"列名"  string列 隐式转换为Column类型
      //.groupBy("_1") // 隐式转换 $"列名"  string列 隐式转换为Column类型
      .sum("_2")
      .show()

    spark.stop()
  }
}

+------+-------+
|    _1|sum(_2)|
+------+-------+
| Kafka|      1|
| Hello|      2|
|Hadoop|      1|
+------+-------+

DataFrame强类型操作

package operation

import org.apache.spark.sql.SparkSession
import org.apache.spark.sql.expressions.scalalang.typed

/**
 * dataset的强类型操作
 */
object DataFrameStrongTypedOperationDemo {
  def main(args: Array[String]): Unit = {
    //1. 构建Spark SQL中核心对象SparkSession
    val spark = SparkSession.builder().appName("wordcount").master("local[*]").getOrCreate()

    //2. 通过spark session对象构建dataset或者dataframe
    val rdd = spark.sparkContext.makeRDD(List("Hello Hadoop", "Hello Kafka"))
    // rdd转换为ds或者df
    import spark.implicits._

    val df = rdd
      .flatMap(_.split("\\s"))
      .map((_, 1))
      .toDF

    df
      //.groupByKey(row => row.getString(0)) // 0 代表的是行对象的第一值
      .groupByKey(row => row.getAs[String]("_1")) // 0 代表的是行对象的第一值
      .agg(typed.sum(row => row.getInt(1)))
      .show()

    spark.stop()
  }
}

+------+----------------------------------------+
| value|TypedSumDouble(org.apache.spark.sql.Row)|
+------+----------------------------------------+
| Kafka|                                     1.0|
| Hello|                                     2.0|
|Hadoop|                                     1.0|
+------+----------------------------------------+

DataFrame的无类型操作

package operation

import org.apache.spark.sql.SparkSession

/**
 * dataFrame的无类型操作
 */
object DataFrameUnTypedOperationDemo {
  def main(args: Array[String]): Unit = {
    //1. 构建Spark SQL中核心对象SparkSession
    val spark = SparkSession.builder().appName("wordcount").master("local[*]").getOrCreate()

    //2. 通过spark session对象构建dataset或者dataframe
    val rdd = spark.sparkContext.makeRDD(List("Hello Hadoop", "Hello Kafka"))

    // rdd转换为ds或者df
    import spark.implicits._
    val df = rdd
      .flatMap(line => line.split("\\s"))
      .map((_, 1))
      .toDF

    df
      .groupBy($"_1")
      .sum("_2")
      .filter("_1 != 'Hadoop'")
      .orderBy($"sum(_2)" desc)
      .limit(1)
      .show()

    spark.stop()
  }
}

五、Spark SQL语法

调用方法

通过调用方法实现数据分析

show

测试或者开发使用

以表格的形式展示数据集中前N行(20)记录

df
.show()

select

投影查询，指定查询的字段

//2. 通过spark session对象构建dataset或者dataframe
val rdd = spark.sparkContext.makeRDD(List((1, "zs", true), (2, "ls", false)))

// rdd转换为ds或者df
import spark.implicits._
// scala隐式转换
val df = rdd.toDF("id", "name", "sex")

df
.select("id", "name")
.show()

+---+----+
| id|name|
+---+----+
|  1|  zs|
|  2|  ls|
+---+----+

selectExpr

支持表达式(基本运算或者别名)的投影查询

//2. 通过spark session对象构建dataset或者dataframe
val rdd = spark.sparkContext.makeRDD(List((1, "zs", true), (2, "ls", false)))

// rdd转换为ds或者df
import spark.implicits._
// scala隐式转换
val df = rdd.toDF("id", "name", "sex")

df
.selectExpr("id+10", "name as username")
.show()

withColumn

添加额外列方法

val rdd = spark.sparkContext.makeRDD(List((1, "zs", true, 2000D), (2, "ls", false, 3000D)))

// rdd转换为ds或者df
import spark.implicits._
// scala隐式转换
val df = rdd.toDF("id", "name", "sex", "salary")

df
//.select("id", "name")
//.selectExpr("id+10", "name as username")
.withColumn("year_salary", $"salary" * 12)
.show()

// select id,name,sex,salary,salary * 12 as  yearsalary from t_user

+---+----+-----+------+-----------+
| id|name|  sex|salary|year_salary|
+---+----+-----+------+-----------+
|  1|  zs| true|2000.0|    24000.0|
|  2|  ls|false|3000.0|    36000.0|
+---+----+-----+------+-----------+

withColumnRenamed

给列重命名方法 as 别名

val rdd = spark.sparkContext.makeRDD(List((1, "zs", true, 2000D), (2, "ls", false, 3000D)))

// rdd转换为ds或者df
import spark.implicits._
// scala隐式转换
val df = rdd.toDF("id", "name", "sex", "salary")

df
//.select("id", "name")
//.selectExpr("id+10", "name as username")
.withColumn("year_salary", $"salary" * 12)
.withColumnRenamed("name","username")
.show()

+---+--------+-----+------+-----------+
| id|username|  sex|salary|year_salary|
+---+--------+-----+------+-----------+
|  1|      zs| true|2000.0|    24000.0|
|  2|      ls|false|3000.0|    36000.0|
+---+--------+-----+------+-----------+

printSchema

打印输出表的结果

df
  //.select("id", "name")
  //.selectExpr("id+10", "name as username")
  .withColumn("year_salary", $"salary" * 12)
  .withColumnRenamed("name", "username")
  //.show()
  .printSchema()


root
 |-- id: integer (nullable = false)
 |-- username: string (nullable = true)
 |-- sex: boolean (nullable = false)
 |-- salary: double (nullable = false)
 |-- year_salary: double (nullable = false)

drop

用来删除特定列方法

val rdd = spark.sparkContext.makeRDD(List((1, "zs", true, 2000D), (2, "ls", false, 3000D)))

// rdd转换为ds或者df
import spark.implicits._
// scala隐式转换
val df = rdd.toDF("id", "name", "sex", "salary")

df
//.select("id", "name")
//.selectExpr("id+10", "name as username")
.withColumn("year_salary", $"salary" * 12)
.withColumnRenamed("name", "username")
.drop($"salary")  // 等价于：select id,name,sex,salary*12 as year_salary from t_user
.show()

+---+--------+-----+-----------+
| id|username|  sex|year_salary|
+---+--------+-----+-----------+
|  1|      zs| true|    24000.0|
|  2|      ls|false|    36000.0|
+---+--------+-----+-----------+

dropDuplicats

特定列内容重复的数据只保留一个 (结果去重)

import spark.implicits._
val df = List(
  (1, "zs", false, 1, 15000),
  (2, "ls", false, 1, 18000),
  (3, "ww", true, 1, 19000),
  (4, "zl", false, 1, 18000)
).toDF("id", "name", "sex", "dept", "salary")

df
.dropDuplicates("sex") // db distinct
.show()

+---+----+-----+----+------+
| id|name|  sex|dept|salary|
+---+----+-----+----+------+
|  3|  ww| true|   1| 19000|
|  1|  zs|false|   1| 15000|
+---+----+-----+----+------+

orderBy | sort

结果排序

import spark.implicits._
val df = List(
  (1, "zs", false, 1, 15000),
  (2, "ls", false, 1, 18000),
  (3, "ww", true, 1, 19000),
  (4, "zl", false, 1, 18000)
).toDF("id", "name", "sex", "dept", "salary")

df
// 根据salary和id进行结果降序排列 (依次排序 如果第一个列内容相同再根据第二个列的内容排序)
//.orderBy($"salary" desc,$"id" desc)
.sort($"salary" desc,$"id" desc)
.show()

+---+----+-----+----+------+
| id|name|  sex|dept|salary|
+---+----+-----+----+------+
|  3|  ww| true|   1| 19000|
|  4|  zl|false|   1| 18000|
|  2|  ls|false|   1| 18000|
|  1|  zs|false|   1| 15000|
+---+----+-----+----+------+

where

条件过滤方法

def m4(spark: SparkSession): Unit = {
  import spark.implicits._
  val df = List(
    (1, "zs", false, 1, 15000),
    (2, "ls", false, 1, 18000),
    (3, "ww", true, 1, 19000),
    (4, "zl", false, 1, 18000)
  ).toDF("id", "name", "sex", "dept", "salary")

  df
  //.where("name='zs' or salary > 18000")
  // === 类似于JS判断语法  值和类型
  .where($"name" === "zs")
  .show()
}

+---+----+-----+----+------+
| id|name|  sex|dept|salary|
+---+----+-----+----+------+
|  1|  zs|false|   1| 15000|
+---+----+-----+----+------+

groupBy

分组方法，将内容相同的数据分为一组

def m5(spark: SparkSession): Unit = {
  import spark.implicits._
  val df = List(
    (1, "zs", false, 1, 15000),
    (2, "ls", false, 1, 18000),
    (3, "ww", true, 2, 19000),
    (4, "zl", false, 1, 18000)
  ).toDF("id", "name", "sex", "dept", "salary")

  df
  // 统计不同部门员工的最高工资 select dept,max(salary) from t_user group by dept
  .groupBy($"dept")
  .max("salary")
  .where("dept = 2") // 等价于having 
  .show()
}


+----+-----------+
|dept|max(salary)|
+----+-----------+
|   2|      19000|
+----+-----------+

limit

限制返回的结果条数

// select ... from 表明 where 过滤条件 group by 分组字段 having 筛选条件 order by 排序字段 asc|desc limit 限制返回几条结果
def m6(spark: SparkSession): Unit = {
  import spark.implicits._
  val df = List(
    (1, "zs", false, 1, 15000),
    (2, "ls", false, 1, 18000),
    (3, "ww", true, 2, 19000),
    (4, "zl", false, 1, 18000)
  ).toDF("id", "name", "sex", "dept", "salary")

  df
  .orderBy($"salary" desc)
  .limit(3) // 用户中工资最高的前三个人
  .show()
}

+---+----+-----+----+------+
| id|name|  sex|dept|salary|
+---+----+-----+----+------+
|  3|  ww| true|   2| 19000|
|  2|  ls|false|   1| 18000|
|  4|  zl|false|   1| 18000|
+---+----+-----+----+------+

agg

聚合方法

/**
   * agg方法
   *
   * @param spark
   */
def m7(spark: SparkSession): Unit = {
  import spark.implicits._
  val df = List(
    (1, "zs", false, 1, 15000),
    (2, "ls", false, 1, 18000),
    (3, "ww", true, 2, 19000),
    (4, "zl", false, 1, 18000)
  ).toDF("id", "name", "sex", "dept", "salary")

  df
  .groupByKey(row => row.getInt(3)) // 3是第四列的下标
  // 聚合操作支持：count sum avg 三种
  .agg(typed.sumLong(row => row.getInt(4)))
  .show()
}

+-----+--------------------------------------+
|value|TypedSumLong(org.apache.spark.sql.Row)|
+-----+--------------------------------------+
|    1|                                 51000|
|    2|                                 19000|
+-----+--------------------------------------+

pivot(透视)

透视：行转列的过程

方法一：

case…when语句

select 
	id,
	max(case course when 'math' then score else 0 end) as math,
	max(case course when 'chinese' then score else 0 end) as chinese,
	max(case course when 'english' then score else 0 end) as english
from 
	t_score
	group by id

// 如何通过spark sql实现行转列的处理呢？
/**
   * pivot 透视语法
   *
   * @param spark
   */
def m8(spark: SparkSession): Unit = {
  import spark.implicits._
  val df = List(
    (1, "math", 85),
    (1, "chinese", 80),
    (1, "english", 90),
    (1, "english", 99),
    (2, "math", 90),
    (2, "chinese", 80)
  ).toDF("id", "course", "score")

  // 方法一：case ...when...语句
  /*
      select
        id,
        max(case course when 'math' then score else 0 end) as math,
        max(case course when 'chinese' then score else 0 end) as chinese,
        max(case course when 'english' then score else 0 end) as english
      from
        t_score
        group by id
     */
  df
  // 基于表达式的投影查询
  .selectExpr(
    "id",
    "case course when 'math' then score else 0 end as math",
    "case course when 'chinese' then score else 0 end as chinese",
    "case course when 'english' then score else 0 end as english"
  )
  .groupBy($"id")
  .max("math","chinese","english")
  .show()

  spark.stop()
}

+---+---------+------------+------------+
| id|max(math)|max(chinese)|max(english)|
+---+---------+------------+------------+
|  1|       85|          80|          99|
|  2|       90|          80|           0|
+---+---------+------------+------------+

方法二：

pivot透视方法，简化行转列的处理

// 方法二：pivot透视方法实现（更为简单）
df
.groupBy("id")
.pivot("course") // 将课程字段math、chinese、english转换化结果表的字段
.max("score") // 保留每个科目分数最高的结果
.show()


+---+-------+-------+----+
| id|chinese|english|math|
+---+-------+-------+----+
|  1|     80|     99|  85|
|  2|     80|   null|  90|
+---+-------+-------+----+

na(空值处理)

对结果表null指的一种处理方法

• 赋予初始值
• 删除含有null值一行数据

/**
   * na null值处理方法
   *
   * @param spark
   */
def m9(spark: SparkSession): Unit = {
  import spark.implicits._
  val df = List(
    (1, "math", 85),
    (1, "chinese", 80),
    (1, "english", 90),
    (1, "english", 99),
    (2, "math", 90),
    (2, "chinese", 80)
  ).toDF("id", "course", "score")
  df
  .groupBy("id")
  .pivot("course") // 将课程字段math、chinese、english转换化结果表的字段
  .max("score") // 保留每个科目分数最高的结果
  //.na.fill(-1,Array[String]("english")) // null 只对english字段的null填充一个初始值
  .na.drop(Array[String]("math"))  // null english字段中含有null值则删除这一行记录
  .show()

  spark.stop()
}

+---+-------+-------+----+
| id|chinese|english|math|
+---+-------+-------+----+
|  1|     80|     99|  85|
|  2|     80|   null|  90|
+---+-------+-------+----+

over(窗口函数)

所谓的窗口函数指的是对多行数据进行处理返回普通列和聚合列的过程；

详细语法：窗口函数() over([partition by 分区 order by 排序规则 ...])

窗口函数有三种分类：

• 聚合窗口函数
• 排名窗口函数
• 数据分析窗口函数

Function Type	SQL	DataFrame API	Description
Ranking	rank	rank	rank值可能是不连续的
Ranking	dense_rank	denseRank	rank值一定是连续的
Ranking	percent_rank	percentRank	相同的分组中 (rank -1) / ( count(score) - 1 )
Ranking	ntile	ntile	将同一组数据循环的往n个桶中放，返回对应的桶的index，index从1开始
Ranking	row_number	rowNumber	很单纯的行号，类似excel的行号
Analytic	cume_dist	cumeDist
Analytic	first_value	firstValue	相同的分组中最小值
Analytic	last_value	lastValue	相同的分组中最大值
Analytic	lag	lag	取前n行数据
Analytic	lead	lead	取后n行数据
Aggregate	min	min	最小值
Aggregate	max	max	最大值
Aggregate	sum	sum	求和
Aggregate	avg	avg	求平均

count(...) over(partition by ... order by ...) --求分组后的总数。
sum(...) over(partition by ... order by ...)   --求分组后的和。
max(...) over(partition by ... order by ...)   --求分组后的最大值。
min(...) over(partition by ... order by ...)   --求分组后的最小值。
avg(...) over(partition by ... order by ...)   --求分组后的平均值。
rank() over(partition by ... order by ...)     --rank值可能是不连续的。
dense_rank() over(partition by ... order by ...) --rank值是连续的。
first_value(...) over(partition by ... order by ...) --求分组内的第一个值。
last_value(...) over(partition by ... order by ...)  --求分组内的最后一个值。
lag() over(partition by ... order by ...)   --取出前n行数据。　　
lead() over(partition by ... order by ...)	--取出后n行数据。
ratio_to_report() over(partition by ... order by ...)  --Ratio_to_report() 括号中就是分子，over() 括号中就是分母。
percent_rank() over(partition by ... order by ...)  --

实际应用

• 求某天每个用户访问页面次数前10的页面

  解题思路参考：https://blog.csdn.net/qq_31871785/article/details/104255641

  1.每个用户访问不同页面的次数
  	select user_id,page_id,count(page_id) from t_log group by user_id,page_id
  2.对每个用户点击页面次数降序排列，并且使用窗口函数中的排名函数
  3.获得每个用户访问次数前十的页面  where rank < 10
  
  /**
     * 窗口函数 案例1：统计每个用户访问页面次数最多的前10个
     *
     * @param spark
     */
  def m10(spark: SparkSession): Unit = {
    val rdd = spark.sparkContext.makeRDD(
      List(
        ("2018-01-01", 1, "www.baidu.com", "10:01"),
        ("2018-01-01", 2, "www.baidu.com", "10:01"),
        ("2018-01-01", 1, "www.sina.com", "10:01"),
        ("2018-01-01", 3, "www.baidu.com", "10:01"),
        ("2018-01-01", 3, "www.baidu.com", "10:01"),
        ("2018-01-01", 1, "www.sina.com", "10:01")
      ))
    import spark.implicits._
    val df = rdd.toDF("day", "user_Id", "page_id", "time")
    // 1. 每个用户访问不同页面的次数
    // select user_id,page_id,count(page_id) from t_log group by user_id,page_id
  
    // 窗口函数声明
    val w1 = Window
    .partitionBy("user_id") // 将user_id相同的数据划分到同一个窗口中
    .orderBy($"count" desc)
  
    import org.apache.spark.sql.functions._
  
    df
    .selectExpr("user_Id", "page_id")
    .groupBy("user_id", "page_id")
    .count()
    // 用以描述排名信息
    .withColumn("rank", rank() over (w1))
    .where("rank <= 1")
    .show()
  
    spark.stop()
  }
  

• JD热卖榜统计(相同问题)

  /**
     * 窗口函数 案例2：JD热卖榜统计(每个分类下商品的热销榜)
     *
     * @param spark
     */
  def m11(spark: SparkSession): Unit = {
    val rdd = spark.sparkContext.makeRDD(
      List(
        (101, "2018-01-01", "洗衣机", "海尔", 1, 999),
        (102, "2018-01-01", "洗衣机", "海尔", 1, 999),
        (103, "2018-01-01", "洗衣机", "小天鹅", 1, 999),
        (104, "2018-01-01", "洗衣机", "西门子", 1, 999),
        (105, "2018-01-01", "手机", "iphone", 1, 999)
      ))
    import spark.implicits._
    val df = rdd.toDF("order_id", "create_time", "category", "product","num","price")
  
    // 窗口函数声明
    val w1 = Window
    .partitionBy("category") // 将user_id相同的数据划分到同一个窗口中
    .orderBy($"count" desc)
  
    import org.apache.spark.sql.functions._
  
    df
    .selectExpr("category", "product")
    .groupBy("category", "product")
    .count()
    // 用以描述排名信息
    .withColumn("rank", rank() over (w1))
    .where("rank <= 10")
    .show()
  
    spark.stop()
  }
  

其余窗口函数的使用语法

• 需求1： 获得当前用户所在部门的最高工资

val df=List(
  (1,"zs",true,1,15000),
  (2,"ls",false,2,18000),
  (3,"ww",false,2,14000),
  (4,"zl",false,1,18000),
	(5,"win7",false,1,16000)
)
id name sex dept salary max_dept_salary(窗口函数)
1  zs   true  1   15000  18000
2  ls   false 2   18000  18000

max窗口函数 求窗口中的最大值
val w1 = Window.partitionBy("dept")
max(salary) over(w1)

w1:
	(1,"zs",true,1,15000)
	(4,"zl",false,1,18000)
	(5,"win7",false,1,16000)
w2:
	(2,"ls",false,2,18000)
	(3,"ww",false,2,14000)

需求2： 获得当前用户所在部门的平均工资

val df=List(
  (1,"zs",true,1,15000),
  (2,"ls",false,2,18000),
  (3,"ww",false,2,14000),
  (4,"zl",false,1,18000),
	(5,"win7",false,1,16000)
)
id name sex dept salary avg_dept_salary(窗口函数)
1  zs   true  1   15000  
2  ls   false 2   18000  

avg窗口函数 求窗口中的平均值
val w1 = Window.partitionBy("dept")
avg(salary) over(w1)

w1:
	(1,"zs",true,1,15000)
	(4,"zl",false,1,18000)
	(5,"win7",false,1,16000)
w2:
	(2,"ls",false,2,18000)
	(3,"ww",false,2,14000)

其它需求：

List(
  (1, "zs", true, 1, 15000),
  (2, "ls", false, 2, 18000),
  (3, "ww", false, 2, 14000),
  (4, "zl", false, 1, 18000),
  (5, "win7", false, 1, 18000)
)
// 当前数据根据部门id的不同划分为了两个窗口
w1:
	(1,"zs",true,1,15000)
	(4,"zl",false,1,18000)
	(5,"win7",false,1,16000)
w2:
	(2,"ls",false,2,18000)
	(3,"ww",false,2,14000)

// 窗口内数据的有效范围（可是范围）是rowsBetween(-1,1)

基准数据：
	(1,"zs",true,1,15000)   id = 1 、4     
  (4,"zl",false,1,18000)  id = 1 、4、5   
	(5,"win7",false,1,16000) id = 4、5     
	(2,"ls",false,2,18000)   id = 2、3
	(3,"ww",false,2,14000)   id = 2、3

package method

import org.apache.spark.sql.SparkSession
import org.apache.spark.sql.expressions.Window

object SparkSQLWordCountOnWindowFunction {
  def main(args: Array[String]): Unit = {
    //1. 构建Spark SQL中核心对象SparkSession
    val spark = SparkSession.builder().appName("wordcount").master("local[*]").getOrCreate()
    val rdd = spark
      .sparkContext
      .makeRDD(List(
        (1, "zs", true, 1, 15000),
        (2, "ls", false, 2, 18000),
        (3, "ww", false, 2, 14000),
        (4, "zl", false, 1, 18000),
        (5, "win7", false, 1, 18000),
        (5, "win7", false, 1, 20000)
      ))

    import org.apache.spark.sql.functions._
    import spark.implicits._
    val df = rdd.toDF("id", "name", "sex", "dept", "salary")
    val w1 = Window.partitionBy("dept")
    val w2 = Window.partitionBy("dept").orderBy($"salary" asc)

    /**
     * 函数数据可视区间窗口
     *
     * 在划分窗口时，还可以定义窗口内的有效数据范围，需要在创建Window对象时定义
     */
    val w3 = Window
      .partitionBy("dept")
      .orderBy($"salary" desc)
      // 窗口内的有效数据范围
      // value = 0  当前行
      // value = n 当前行的下n行
      // value = -n 当前行的上n行
      //.rowsBetween(-1,1)
      .rowsBetween(0,1)

    df
      // 获得当前员工信息（普通列）及所在部门的最高工资（聚合列，通过窗口函数获得）
      // 窗口函数 + 窗口划分依据
      .withColumn("max_dept_salary", max("salary") over (w1))
      // 获得当前用户信息（普通列）及所在部门的平均工资聚合列，通过窗口函数获得）
      .withColumn("avg_dept_salary", avg("salary") over (w1))
      // 排名的窗口函数 必须排序后使用 相同内容 并列
      // 非紧密排名 1 2 2 4
      .withColumn("rank", rank() over (w2))
      // 紧密排名 1 2 2 3
      .withColumn("dense_rank", dense_rank() over (w2))
      // 数据分析窗口函数 lag 前n行的结果
      // 取当前窗口内 基准行的前两行数据的salary作为lag字段结果 注意：需要对窗口内的数据进行排序
      .withColumn("lag", lag("salary", 2) over (w2))
      .withColumn("lead", lead("salary", 1) over (w2))
      .withColumn("max_salary_range", max("salary") over(w3))
      .show()

    spark.stop()
  }
}

面试题：

问题
A表里面有三笔记录 字段是 ID start_date end_date
数据是：

1 2018-02-03 2019-02-03;
2 2019-02-04 2020-03-04;
3 2018-08-04 2019-03-04；

根据已知的三条记录用sql写出结果为：

A 2018-02-03 2018-08-04;
B 2018-08-04 2019-02-03; 
C 2019-02-03 2019-02-04;
D 2019-02-04 2019-03-04;
E 2019-03-04 2020-03-04;

+----------+----------+
|     value|      next|
+----------+----------+
|2018-02-03|2018-08-04|
|2018-08-04|2019-02-03|
|2019-02-03|2019-02-04|
|2019-02-04|2019-03-04|
|2019-03-04|2020-03-04|
|2020-03-04|2020-03-04|
+----------+----------+

（提示：请把问题看做是一个判断区间内有零散时间点的重新建立时间区间连续以及断点的问题）

解决:
1. 拆解时间数据
2. 升序排列日期
3. 窗口函数（连续时间区间）
package method

import org.apache.spark.sql.SparkSession
import org.apache.spark.sql.expressions.Window

object SparkSQLWordCountOnWindowFunction2 {
  def main(args: Array[String]): Unit = {
    //1. 构建Spark SQL中核心对象SparkSession
    val spark = SparkSession.builder().appName("wordcount").master("local[*]").getOrCreate()
    import spark.implicits._

    val rdd = spark
    .sparkContext
    .makeRDD(List(
      (1, "2018-02-03", "2019-02-03"),
      (2, "2019-02-04", "2020-03-04"),
      (3, "2018-08-04", "2019-03-04")
    ))
    val df = rdd
    .flatMap(t3 => {
      Array[String](t3._2, t3._3)
    })
    .toDF("value")

    import org.apache.spark.sql.functions._

    val w1 = Window.orderBy($"value" asc).rowsBetween(0,1)
    df
    .orderBy($"value" asc)
    .withColumn("next", max("value") over (w1))
    .show()

    spark.stop()
  }
}

join(表连接)

对两个结果表的Join连接操作

package method

import org.apache.spark.sql.SparkSession

object SparkSQLWordCount2 {
  def main(args: Array[String]): Unit = {
    //1. 构建Spark SQL中核心对象SparkSession
    val spark = SparkSession.builder().appName("wordcount").master("local[*]").getOrCreate()

    val rdd1 = spark.sparkContext.makeRDD(List((1, "zs", "A"), (2, "ls", "B"), (3, "ww", "C")))
    val rdd2 = spark.sparkContext.makeRDD(List(("A", "市场部"), ("B", "后勤部")))
    import spark.implicits._
    val df1 = rdd1.toDF("id", "name", "dept_id")
    val df2 = rdd2.toDF("d_id", "dept_name")

    df1
      // 默认的连接类型为inner
      //.join(df2, $"dept_id" === $"d_id", "left_outer")
      .join(df2, $"dept_id" === $"d_id")
      .show()

    spark.stop()
  }
}

cube(多维度)

多维度分组，尝试根据数据的多重不同组合进行分组操作

/**
   * cube 多维度分组
   *
   *  最大好处：在进行数据分析时，根据cube字段的分组查询的结果一定在结果表
   *
   * @param spark
   */
def m2(spark: SparkSession) = {
  // 来源数据源HDFS、HBase
  import spark.implicits._
  val df = spark
  .sparkContext
  .makeRDD(List(
    (110, 50, 80, 80),
    (120, 60, 95, 75),
    (120, 50, 96, 70)))
  .toDF("height", "weight", "IQ", "EQ")

  df
  // 列名 表示根据那几个字段的不同组合进行分组操作
  // group by 110,50
  // group by 120,60
  // group by 120,50
  // group by 110,60
  // group by 110,null
  // group by 120,null
  // ...
  .cube("height", "weight")
  .count()
  .show()

  spark.stop()

}


+------+------+-----+
|height|weight|count|
+------+------+-----+
|   110|    50|    1|
|   120|  null|    2|
|   120|    60|    1|
|  null|    60|    1|
|  null|  null|    3|
|   120|    50|    1|
|   110|  null|    1|
|  null|    50|    2|
+------+------+-----+

纯SQL（推荐）

通过手写SQL语句实现数据分析

基本语法

SQL:

select [all|distinct] 字段1,字段2... from 表明 where 过滤条件 group by 分组字段 having 筛选条件 order by 排序字段 asc|desc limit 限制返回结果的条数

package method

import org.apache.spark.sql.SparkSession

/**
 * 纯SQL语法
 */
object SQLDemo {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder().appName("sql operation").master("local[*]").getOrCreate()

    val rdd = spark
    .sparkContext
    .makeRDD(List(
      (1, "zs", true, 1, 15000),
      (2, "ls", false, 2, 18000),
      (3, "ww", false, 2, 14000),
      (4, "zl", false, 1, 18000),
      (5, "win7", false, 1, 18000)
    ))
    import spark.implicits._
    val df = rdd.toDF("id", "name", "sex", "dept", "salary")

    // df起别名 视图名
    // 全局视图(跨多个spark session会话) + 临时视图(只能在创建它的spark session中使用)
    // 创建一个临时视图 t_user
    df.createOrReplaceTempView("t_user")
    // 如果创建的是全局视图，表会存放到 global_temp.t_user  默认default.xxx
    // df.createOrReplaceGlobalTempView("t_user")

    // 创建一个新的spark session对象
    val newSpark = spark.newSession()

    // newSpark
    spark
    // 查所有
    // .sql("select * from t_user")
    // where过滤
    //.sql("select * from t_user where sex = false")
    // 模糊查询 like
    //.sql("select * from t_user where name like '%s%'")
    // 排序查询 order by
    //.sql("select * from t_user where name like '%s%' order by salary desc")
    // 分组 group by
    //.sql("select dept,avg(salary) from t_user group by dept")
    // 分组后结果过滤  having
    .sql("select dept,avg(salary) from t_user group by dept having dept = 2")
    // 限制返回的结果条数 limit
    //.sql("select * from global_temp.t_user order by salary desc limit 3")
    .show()

    spark.stop()
  }
}

总结：

1. Spark SQL的纯SQL语法类似于数据库DB的语法
2. GlobalTempViewl全局视图和TempView的区别
   • 全局视图(存放在global_temp数据库中，可以跨多个spark session会话)
   • 临时视图（存放在default数据库中，只能够创建它的spark session会话使用）

高阶语法

case…when

spark
.sql("select id,name,case sex when true then '男性' else '女性' end as new_sex,dept,salary from t_user")
.show()

+---+----+-------+----+------+
| id|name|new_sex|dept|salary|
+---+----+-------+----+------+
|  1|  zs|   男性|   1| 15000|
|  2|  ls|   女性|   2| 18000|
|  3|  ww|   女性|   2| 14000|
|  4|  zl|   女性|   1| 18000|
|  5|win7|   女性|   1| 18000|
+---+----+-------+----+------+

pivot透视

方法一：

import spark.implicits._
val df2 = List(
  (1, "math", 85),
  (1, "chinese", 80),
  (1, "english", 90),
  (1, "english", 99),
  (2, "math", 90),
  (2, "chinese", 80)
).toDF("id", "course", "score")

df2.createOrReplaceTempView("t_score")

spark
.sql("select id, " +
     "max(case course when 'math' then score else -1 end) as math," +
     "max(case course when 'chinese' then score else -1 end) as chinese," +
     "max(case course when 'english' then score else -1 end) as english" +
     " from t_score group by id")
.show()

+---+----+-------+-------+
| id|math|chinese|english|
+---+----+-------+-------+
|  1|  85|     80|     99|
|  2|  90|     80|     -1|
+---+----+-------+-------+

方法二：

spark
// pivot(聚合函数 for 行转列的字段 in(最终结果表中列名))
.sql("select * from t_score pivot(max(score) for course in('math','chinese'))")
.show()

+---+----+-------+
| id|math|chinese|
+---+----+-------+
|  1|  85|     80|
|  2|  90|     80|
+---+----+-------+

null值处理

替换赋予初始值： case... when.

删除：where column is not null

over 窗口函数

窗口函数sql语法： 窗口函数名() over(partition by 划分窗口字段 order by 窗口内的排序规则 rows between start and end)

// 高阶语法：窗口函数
// 统计每个用户访问次数前十的页面
val rdd3 = spark.sparkContext.makeRDD(
  List(
    ("2018-01-01", 1, "www.baidu.com", "10:01"),
    ("2018-01-01", 2, "www.baidu.com", "10:01"),
    ("2018-01-01", 1, "www.sina.com", "10:01"),
    ("2018-01-01", 3, "www.baidu.com", "10:01"),
    ("2018-01-01", 3, "www.baidu.com", "10:01"),
    ("2018-01-01", 1, "www.sina.com", "10:01")
  ))
import spark.implicits._
val df3 = rdd3.toDF("day", "user_Id", "page_id", "time")

df3.createOrReplaceTempView("t_log")

// 窗口函数sql语法： 窗口函数名() over(partition by 划分窗口字段 order by 窗口内的排序规则 rows between start and end)
spark
// 注意： """包裹内容"""  自动进行字符串的拼接
.sql(
  """
          | select
          |   *
          | from
          |   (select
          |     user_id,
          |     page_id,
          |     num,
          |     rank() over (partition by user_id order by num desc) as rank
          |    from
          |       (select
          |         user_id,
          |         page_id,
          |         count(page_id) as num
          |        from t_log
          |        group by
          |         user_id,page_id
          |        )
          |   )
          | where rank <= 10
          |""".stripMargin)
.show()
+-------+-------------+---+----+
|user_id|      page_id|num|rank|
+-------+-------------+---+----+
|      1| www.sina.com|  2|   1|
|      1|www.baidu.com|  1|   2|
|      3|www.baidu.com|  2|   1|
|      2|www.baidu.com|  1|   1|
+-------+-------------+---+----+


// 案列2：查询用户的基本信息和用户所在部门的平均工资
// rows between(0,Long_MAX_Value)
spark
.sql(
  """
          | select
          |   id,
          |   name,
          |   sex,
          |   dept,
          |   salary,
          |   avg(salary) over(partition by dept order by salary desc rows between unbounded preceding and unbounded following) as avg_salary
          | from
          |   t_user
          |""".stripMargin)
.show()

+---+----+-----+----+------+----------+
| id|name|  sex|dept|salary|avg_salary|
+---+----+-----+----+------+----------+
|  4|  zl|false|   1| 18000|   17000.0|
|  5|win7|false|   1| 18000|   17000.0|
|  1|  zs| true|   1| 15000|   17000.0|
|  2|  ls|false|   2| 18000|   16000.0|
|  3|  ww|false|   2| 14000|   16000.0|
+---+----+-----+----+------+----------+

注意：

• SQL语句中的rows between unbounded preceding and unbounded following,其中
  • unbounded preceding：表示Long.MIN_VALUE，也就是可视当前行之前的所有数据
  • unbounded following： 表示Long.MAX_VALUE，也就是可视当前行之后的所有数据
  • current row: 表示当前行，也就是0
• 其余的窗口函数，只需要修改窗口函数名即可

join 表连接查询

spark
//.sql("select * from t_user t1 inner join t_dept t2 on t1.dept = t2.dept_id")
//.sql("select * from t_user t1 left outer join t_dept t2 on t1.dept = t2.dept_id")
//.sql("select * from t_user t1 right outer join t_dept t2 on t1.dept = t2.dept_id")
//.sql("select * from t_user t1 full outer join t_dept t2 on t1.dept = t2.dept_id")
//.sql("select * from t_user t1 cross join t_dept t2 on t1.dept = t2.dept_id")
// 左半开连接 left semi  类似于枚举查询 in  使用右表去过滤左表
//.sql("select * from t_user t1 left semi join t_dept t2 on t1.dept = t2.dept_id")
// anti 左半开连接的相反过程
.sql("select * from t_user t1 left anti join t_dept t2 on t1.dept = t2.dept_id")
.show()

子查询

类似于DB的子查询语法

cube(多维分组)

val df5 = spark
.sparkContext
.makeRDD(List(
  (110, 50, 80, 80),
  (120, 60, 95, 75),
  (120, 50, 96, 70)))
.toDF("height", "weight", "IQ", "EQ")

df5.createOrReplaceTempView("t_info")

spark
.sql("select height,weight,avg(IQ),avg(EQ) from t_info group by cube(height,weight)")
.show()


+------+------+-----------------+-------+
|height|weight|          avg(IQ)|avg(EQ)|
+------+------+-----------------+-------+
|   110|    50|             80.0|   80.0|
|   120|  null|             95.5|   72.5|
|   120|    60|             95.0|   75.0|
|  null|    60|             95.0|   75.0|
|  null|  null|90.33333333333333|   75.0|
|   120|    50|             96.0|   70.0|
|   110|  null|             80.0|   80.0|
|  null|    50|             88.0|   75.0|
+------+------+-----------------+-------+

六、自定义函数（UDF）

内置函数：Max、Avg、Sum、Count、Lower、Upper

所谓的自定义函数：自己定义函数，扩展内置函数库

单行函数

package function

import org.apache.spark.sql.SparkSession

/**
 * 自定义单行函数
 */
object CustomFunction1 {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder().appName("custom function").master("local[*]").getOrCreate()
    // 参数一：函数名
    spark.udf.register("sex_convert", (sex: Boolean) => {
      sex match {
        case true => "男"
        case false => "女"
        case _ => null
      }
    })

    // sex_convert(sex) ---> '男' 或 '女'
    import spark.implicits._
    val rdd = spark
    .sparkContext
    .makeRDD(List(
      (1, "zs", true, 1, 15000),
      (2, "ls", false, 2, 18000),
      (3, "ww", false, 2, 14000),
      (4, "zl", false, 1, 18000),
      (5, "win7", false, 1, 18000)
    ))
    val df = rdd.toDF("id", "name", "sex", "dept", "salary")

    df.createOrReplaceTempView("t_user")

    spark
    .sql("select id,name,sex_convert(sex),dept,salary from t_user")
    .show()

    spark.stop()
  }
}

聚合函数

package function

import org.apache.spark.sql.expressions.{MutableAggregationBuffer, UserDefinedAggregateFunction}
import org.apache.spark.sql.types.{DataType, IntegerType, StructType}
import org.apache.spark.sql.{Row, SparkSession}

/**
 * 自定义聚合函数（my_sum） 类似于内置Sum
 */
object CustomFunction2 {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder().appName("custom function").master("local[*]").getOrCreate()
    // 参数一：函数名  参数二：用以进行聚合操作的函数对象
    spark.udf.register("my_sum", new UserDefinedAggregateFunction {
      /**
       * 输入字段结构信息 my_sum(salary)
       *
       * @return
       */
      override def inputSchema: StructType = {
        new StructType().add("salary", IntegerType)
      }

      /**
       * 缓冲区结构信息（聚合的结果需要临时存放在缓冲区中）
       *
       * @return
       */
      override def bufferSchema: StructType = {
        // 中间结果1： 18000+15000 =33000
        // 中间结果2： 33000+18000
        new StructType().add("total", IntegerType)
      }

      /**
       * 聚合函数最终结果的类型信息
       *
       * @return
       */
      override def dataType: DataType = IntegerType

      /**
       * 表示参数类型是否和最终结果的类型一致
       *
       * @return
       */
      override def deterministic: Boolean = true

      /**
       * 初始化方法(为缓冲区写入初始值)
       *
       * @param buffer 缓冲区 临时存放中间结果的
       */
      override def initialize(buffer: MutableAggregationBuffer): Unit = {
        buffer.update(0, 0) // Array(0)
      }

      /**
       * 更新方法： 聚合操作处理每一行数据都会调用的方法
       *
       * @param buffer 缓冲区（当前行之前的聚合信息）
       * @param input  当前处理的行对象
       */
      override def update(buffer: MutableAggregationBuffer, input: Row): Unit = {
        // 0 聚合函数的第一个参数的值  my_sum(arg1,arg2,arg3)
        val arg1 = input.getInt(0)
        val history = buffer.getInt(0)
        buffer.update(0, arg1 + history)
      }

      /**
       * 合并方法： 聚合函数对多行数据进行操作处理可能需要多个Buffer缓冲区
       *
       * @param buffer1
       * @param buffer2
       */
      override def merge(buffer1: MutableAggregationBuffer, buffer2: Row): Unit = {
        val r1 = buffer1.getInt(0)
        val r2 = buffer2.getInt(0)
        buffer1.update(0, r1 + r2)
      }

      /**
       * 评估  返回聚合结果
       *
       * @param buffer
       * @return
       */
      override def evaluate(buffer: Row): Any = buffer.getInt(0)
    })

    // sex_convert(sex) ---> '男' 或 '女'
    import spark.implicits._
    val rdd = spark
      .sparkContext
      .makeRDD(List(
        (1, "zs", true, 1, 15000),
        (2, "ls", false, 2, 18000),
        (3, "ww", false, 2, 14000),
        (4, "zl", false, 1, 18000),
        (5, "win7", false, 1, 18000)
      ))
    val df = rdd.toDF("id", "name", "sex", "dept", "salary")

    df.createOrReplaceTempView("t_user")

    spark
      .sql("select dept,my_sum(salary) from t_user group by dept")
      .show()

    spark.stop()
  }
}

+----+--------------+
|dept|anon$1(salary)|
+----+--------------+
|   1|         51000|
|   2|         32000|
+----+--------------+

七、数据的加载和保存

其它方式的数据源构建和计算结果的存储

JDBC

基于JDBC的数据Load&Save操作

package loadsave

import java.util.Properties

import org.apache.spark.sql.{SaveMode, SparkSession}

object SparkSQLLoadAndSaveDemo {
  def main(args: Array[String]): Unit = {
    //1. 构建Spark SQL中核心对象SparkSession
    val spark = SparkSession.builder().appName("wordcount").master("local[*]").getOrCreate()

    // 通过JDBC构建数据源 创建DF
    val prop = new Properties()
    prop.put("user", "root")
    prop.put("password", "root")
    val df = spark
      .read
      .jdbc("jdbc:mysql://localhost:3306/test", "t_score", prop)

    df.createOrReplaceTempView("t_score")

    spark
      .sql("select * from t_score where id =1")
      .write
      .mode(SaveMode.Append)  // 保存模式：追加 默认是存在报错
      .jdbc("jdbc:mysql://localhost:3306/test", "t_result", prop)

    spark.stop()
  }
}

CSV

基于CSV文件的数据加载和结果写出

csv文件格式，用于存放结构化数据可以使用Excel文件打开

package loadsave

import java.util.Properties

import org.apache.spark.sql.{SaveMode, SparkSession}

object SparkSQLLoadAndSaveDemo {
  def main(args: Array[String]): Unit = {
    //1. 构建Spark SQL中核心对象SparkSession
    val spark = SparkSession.builder().appName("wordcount").master("local[*]").getOrCreate()
  
    csv(spark)

    spark.stop()
  }

  def csv(spark: SparkSession): Unit = {
    import spark.implicits._
    val df = spark
      .read
      .csv("/Users/gaozhy/data/csv")

    df
      .groupBy($"_c0")
      .count()
      .write
      .csv("/Users/gaozhy/data/170")
  }
}

JSON

基于JSON文件的数据加载和结果写出

def json(spark: SparkSession): Unit = {
  val df = spark
  .read
  .json("/Users/gaozhy/工作/代码仓库/训练营备课代码/BigData/sparksql/src/main/resources")

  df
  .write
  .json("/Users/gaozhy/data/170_v2")
}

parquet

Spark SQL默认使用的一种文件格式

def parquet(spark: SparkSession): Unit = {
  val df = spark
  .read
  .load("/Users/gaozhy/data/170_v3")
  .show()
  /*
    df
      .write
      // parquet文件格式
      .save("/Users/gaozhy/data/170_v3")
     */
}

Redis | HBase | MongoDB Save

def otherDataSource(spark: SparkSession): Unit = {
  val df = spark
  .read
  .load("/Users/gaozhy/data/170_v3")
  // DF ---> RDD[Row]
  df.printSchema()
  val rdd: RDD[Row] = df.rdd
  rdd
  .foreachPartition(itar => {
    val jedis = new Jedis("localhost", 6379)
    itar.foreach(row => {
      val id = row.getLong(0)
      val name = row.getString(1)
      val sex = row.getBoolean(2)
      // Redis中Hash类型
      jedis.hset(id.toString, "name", name)
      jedis.hset(id.toString, "sex", sex.toString)
    })
    jedis.close()
  })
}

HDFS

def hdfs(spark: SparkSession): Unit = {
  val df = spark
  .read
  .load("/Users/gaozhy/data/170_v3")

  df
  .write
  .parquet("hdfs://spark:9000/170_result")
}