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 TypeSQLDataFrame APIDescription
Rankingrankrankrank值可能是不连续的
Rankingdense_rankdenseRankrank值一定是连续的
Rankingpercent_rankpercentRank相同的分组中 (rank -1) / ( count(score) - 1 )
Rankingntilentile将同一组数据循环的往n个桶中放,返回对应的桶的index,index从1开始
Rankingrow_numberrowNumber很单纯的行号,类似excel的行号
Analyticcume_distcumeDist
Analyticfirst_valuefirstValue相同的分组中最小值
Analyticlast_valuelastValue相同的分组中最大值
Analyticlaglag取前n行数据
Analyticleadlead取后n行数据
Aggregateminmin最小值
Aggregatemaxmax最大值
Aggregatesumsum求和
Aggregateavgavg求平均
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")
}
  • 1
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Spark SQL是Apache Spark中用于处理结构化数据的模块,它提供了一种类似于SQL的查询语言,可以用于查询和分析大规模的数据集。下面是Spark SQL的一些常用语法和功能: 1. 创建表: - 使用`CREATE TABLE`语句创建表,可以指定表名、列名和数据类型等信息。 - 也可以使用`CREATE TABLE AS SELECT`语句根据查询结果创建表。 2. 插入数据: - 使用`INSERT INTO`语句将数据插入到表中,可以插入单条数据或者批量插入数据。 3. 查询数据: - 使用`SELECT`语句查询表中的数据,可以使用`WHERE`子句进行条件过滤。 - 支持常见的聚合函数(如SUM、AVG、COUNT等)和分组操作(GROUP BY)。 - 可以使用`JOIN`语句进行多表关联查询。 4. 更新和删除数据: - 使用`UPDATE`语句更新表中的数据,可以根据条件进行更新。 - 使用`DELETE FROM`语句删除表中的数据,也可以根据条件进行删除。 5. 数据转换和处理: - 支持常见的数据转换函数,如`CAST`、`CONCAT`、`SUBSTRING`等。 - 提供了日期和时间函数,如`DATE_ADD`、`DATE_SUB`、`CURRENT_DATE`等。 - 支持正则表达式函数,如`REGEXP_REPLACE`、`REGEXP_EXTRACT`等。 6. 数据分析和统计: - 提供了窗口函数(Window Functions)用于进行数据分析和统计。 - 支持排序函数(ORDER BY)和分页查询(LIMIT)。 7. 数据存储和读取: - 可以将查询结果保存到文件系统(如HDFS、S3等)或数据库中。 - 支持读取和写入多种数据格式,如Parquet、Avro、JSON、CSV等。 8. 执行SQL语句: - 使用`spark.sql()`方法执行SQL语句,可以直接在Spark应用程序中执行SQL查询。 以上是Spark SQL的一些常用语法和功能,可以根据具体需求进行灵活应用。如果有具体的问题或者需要更详细的介绍,请告诉我。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值