基本原理
Spark SQL用于处理结构化数据的Spark模块,兼容但不受限于Hive。而Hive不受限于单一引擎,可以采用Spark, Map-Reduce等引擎。
SparkSQL可以简化RDD的开发,提高开发效率,提升执行效率,其提供了DataFrame与DataSet两个编程抽象,类似Spark Core的RDD。
Spark SQL特点:
- 易整合:整合Spark编程与SQL查询
- 统一的数据访问:使用相同方式连接不同数据源
- 兼容Hive:兼容Hive语法
- 标准数据连接:通过JDBC或ODBC连接
DSL语法
DSL是DataFrame提供的用来操作结构化数据的一种特定领域语言,以下列出几种操作示例:
- 查询字段
- 更改查询结果的值
- 别名
- 过滤条件
- 分组
DataFrame
DataFrame是一种以RDD为基础的分布式数据集。其与RDD的主要区别在于注重点不同,DataFrame注重源数据的结构信息,而RDD更注重数据本身。
DataFrame为数据提供Schema视图(类似数据库的表)并且DataFrame是懒执行的但是性能比RDD高(优化的执行计划);
DataFrame支持嵌套数据类型struct, array和map。
创建DataFrame三种方式
- 从Spark数据源进行创建
示例:源文件test.json
注意:普通的TempView仅对当前Spark Session有效,而全局TempView是对所有Spark Session有效;
- 从一个存在的RDD进行创建
- RDD转为DataFrame
rdd.toDF(列名1,列名2)
- DataFrame转为RDD
df.rdd
- 从Hive Table进行查询返回
DataSet
DataSet是Spark1.6添加的新分布式数据集,是DataFrame的扩展,提供了RDD的优势以及SparkSQL优化执行引擎的优点,其相较于DataFrame更关注于字段类型,是具有强类型的数据集,使用时需要提供对应类型信息。
DataSet也可以使用功能性的转换,如map, flatMap, filter等。
创建DataSet
- 样例类序列创建
toDS
DataFrame与DataSet互转
- DataFrame转为DataSet
df.as[样例类型]
- DataSet转为DataFrame
ds.toDF
RDD与DataSet互转
- RDD转为DataSet
样例类RDD.toDS
- DataSet转为RDD
ds.rdd
RDD、DataFrame与DataSet三者转换关系
idea演示示例
package com.itjeffrey.spark.sql.basic
import org.apache.spark.rdd.RDD
import org.apache.spark.{SparkConf, SparkContext}
import org.apache.spark.sql.{DataFrame, Dataset, Row, SparkSession}
/**
* Spark SQL--演示DataFrame、DataSet与RDD之间的转换操作
*
* @From: Jeffrey
* @Date: 2022/11/17
*/
object SparkSqlDemo {
def main(args: Array[String]): Unit = {
//创建SparkSQL运行环境
val conf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("sparkSql")
val ss: SparkSession = SparkSession.builder().config(conf).getOrCreate()
//导入sparkSession的隐式转换,用于对字段结果做计算的转换操作
import ss.implicits._
//1.DataFrame(DataFrame本质是特定泛型[Row]的DataSet)
val df: DataFrame = ss.read.json("datas/user.json")
//1.1 演示DataFrame SQL操作
df.createOrReplaceTempView("user")
ss.sql("select * from user").show()
ss.sql("select name, age from user").show()
ss.sql("select avg(age) from user").show()
//1.2 演示DataFrame DSL操作
df.select("name", "age").show()
df.select($"name", $"age" + 1).show() //对age字段做计算操作需要导入隐式转换规则依赖
df.select('name, 'age + 1).show() //同上
//2.DataSet(DataSet包含DataFrame所有API操作)
val seq = Seq(1, 2, 3, 4)
val ds: Dataset[Int] = seq.toDS()
ds.show()
//3.RDD、DataFrame与DataSet互转操作
//3.1 RDD与DataFrame互转
val sc: SparkContext = ss.sparkContext
val rdd: RDD[(Long, String, Double)] = sc.makeRDD(List((1001L, "jeffrey", 8888.00), (1002L, "qiutee", 10000.00), (1003L, "zfy", 6666.00)))
val df1: DataFrame = rdd.toDF("id", "name", "salary")
val rdd1: RDD[Row] = df1.rdd
//3.2 DataFrame与DataSet互转
val ds1: Dataset[Employee] = df1.as[Employee]
val df2: DataFrame = ds1.toDF()
//3.3 DataSet与RDD互转
val rdd2: RDD[Employee] = ds1.rdd
val mapRDD: RDD[Employee] = rdd.map {
case (id, name, salary) => Employee(id, name, salary)
}
val ds2: Dataset[Employee] = mapRDD.toDS()
//关闭环境
ss.close()
}
//员工样例类
case class Employee(id:Long, name:String, salary:Double)
}
UDF用户自定义函数
package com.itjeffrey.spark.sql.udf
import org.apache.spark.SparkConf
import org.apache.spark.sql.{DataFrame, SparkSession}
/**
* UDF: 用户自定义函数
* @From: Jeffrey
* @Date: 2022/11/17
*/
object UdfDemo {
def main(args: Array[String]): Unit = {
//创建SparkSQL运行环境
val conf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("sparkSql")
val ss: SparkSession = SparkSession.builder().config(conf).getOrCreate()
import ss.implicits._
val df: DataFrame = ss.read.json("datas/user.json")
df.createOrReplaceTempView("user")
//注册UDF函数compute,计算age
ss.udf.register("compute", (age:Long) => {
age + 10
})
ss.sql("select name, compute(age) from user").show()
ss.close()
}
}
UDAF用户自定义聚合函数
Spark3.0版本后推荐使用Aggregator[IN,BUFF,OUT]
package com.itjeffrey.spark.sql.udf
import org.apache.spark.SparkConf
import org.apache.spark.sql.expressions.{Aggregator, MutableAggregationBuffer, UserDefinedAggregateFunction}
import org.apache.spark.sql.types.{DataType, LongType, StructField, StructType}
import org.apache.spark.sql.{DataFrame, Encoder, Encoders, Row, SparkSession, functions}
/**
* UDAF: 用户自定义聚合函数(强类型与弱类型实现)
*
* @From: Jeffrey
* @Date: 2022/11/17
*/
object UdafDemo {
def main(args: Array[String]): Unit = {
//创建SparkSQL运行环境
val conf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("sparkSql")
val ss: SparkSession = SparkSession.builder().config(conf).getOrCreate()
val df: DataFrame = ss.read.json("datas/user.json")
df.createOrReplaceTempView("user")
//注册弱类型用户自定义函数
// ss.udf.register("avgAge", new CustomAgeAvgADUF())
//注册强类型用户自定义函数
ss.udf.register("avgAge", functions.udaf(new CustomAgeAvgADUF()))
ss.sql("select avgAge(age) from user").show()
ss.close()
}
//自定义聚合函数类--计算年龄平均值(强类型实现)
class CustomAgeAvgADUF extends Aggregator[Long, Buff, Long]{
//缓冲区初始化
override def zero: Buff = Buff(0L, 0L)
//根据输入数据更新缓冲区
override def reduce(b: Buff, a: Long): Buff = {
b.sumAge = b.sumAge + a
b.count = b.count + 1
b
}
//合并缓冲区
override def merge(b1: Buff, b2: Buff): Buff = {
b1.sumAge = b1.sumAge + b2.sumAge
b1.count = b1.count + b2.count
b1
}
//计算结果
override def finish(reduction: Buff): Long = {
reduction.sumAge / reduction.count
}
//缓冲区编码, Encoders.product自定义类型
override def bufferEncoder: Encoder[Buff] = Encoders.product
//输出编码, Scala自定类型
override def outputEncoder: Encoder[Long] = Encoders.scalaLong
}
//自定义缓冲类
case class Buff(var sumAge:Long, var count:Long)
//自定义聚合函数类--计算年龄平均值(弱类型实现)
// class CustomAgeAvgADUF extends UserDefinedAggregateFunction{
// //输入数据结构
// override def inputSchema: StructType = {
// StructType(Array(
// StructField("age", LongType)
// ))
// }
// //缓冲区数据结构--用作临时计算
// override def bufferSchema: StructType = {
// StructType(Array(
// //累计年龄总和
// StructField("sumAge", LongType),
// //累计总人数
// StructField("count", LongType)
// ))
// }
// //输出数据类型
// override def dataType: DataType = LongType
// //函数稳定性
// override def deterministic: Boolean = true
// //缓冲区初始化
// override def initialize(buffer: MutableAggregationBuffer): Unit = {
buffer(0) = 0L
buffer(1) = 0L
// //update第一个参数为索引下标
// buffer.update(0, 0L)
// buffer.update(1, 0L)
// }
// //根据输入的值更新缓冲区数据
// override def update(buffer: MutableAggregationBuffer, input: Row): Unit = {
// buffer.update(0, buffer.getLong(0) + input.getLong(0))
// buffer.update(1, buffer.getLong(1) + 1)
// }
// //缓冲区数据合并
// override def merge(buffer1: MutableAggregationBuffer, buffer2: Row): Unit = {
// buffer1.update(0, buffer1.getLong(0) + buffer2.getLong(0))
// buffer1.update(1, buffer1.getLong(1) + buffer2.getLong(1))
// }
// //计算年龄平均值(年龄总和/总人数)
// override def evaluate(buffer: Row): Any = {
// buffer.getLong(0) / buffer.getLong(1)
// }
// }
}
数据加载与保存
SparkSQL默认数据源格式:parquet
- 加载数据
spark.read.json 通用数据加载方式,默认加载文件格式为parquet
或者指定加载文件格式
val df = spark.read.format(“json”).load(“datas/user.json”)
或者直接用特性文件格式加载
val df = spark.read.json(“datas/user.json”)
读取csv文件:
spark.read.format(“csv”).option(“sep”, “;”).option(“inferSchema”, “true”).option(“header”, “true”).load(“datas/test.csv”)
- 保存数据
spark.write.save 通用数据保存方式,默认保存文件格式也为parquet
或者指定保存文件格式
spark.write.format(“json”).save(“output”)
保存模式:默认为SaveMode.ErrorIfExists, 即如果文件已存在则抛异常
改变保存模式
df.wirte.mode(“append”).json(“output”) //有则追加
df.write.mode(“overwrite”).json(“output”) //有则覆盖
df.write.mode(“ignore”).json(“output”) //有则忽略
SparkSQL操作MySQL
package com.itjeffrey.spark.sql.jdbc
import org.apache.spark.SparkConf
import org.apache.spark.sql.{DataFrame, SaveMode, SparkSession}
/**
* 通过JDBC操作数据库
*
* @From: Jeffrey
* @Date: 2022/11/18
*/
object JdbcDemo {
def main(args: Array[String]): Unit = {
val conf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("sparkSql")
val ss: SparkSession = SparkSession.builder().config(conf).getOrCreate()
import ss.implicits._
//读取数据库表数据
val df: DataFrame = ss.read.format("jdbc")
.option("driver", "com.mysql.cj.jdbc.Driver")
.option("dbtable", "test1")
.option("url", "jdbc:mysql://localhost:3306/dtl")
.option("user", "root")
.option("password", "root")
.load()
//写入数据库表
df.write.format("jdbc")
.option("driver", "com.mysql.cj.jdbc.Driver")
.option("dbtable", "test2")
.option("url", "jdbc:mysql://localhost:3306/dtl")
.option("user", "root")
.option("password", "root")
.mode(SaveMode.Append)
.save()
ss.close()
}
}
SparkSQL操作内置Hive
spark-shell
spark.sql(“create table user(name string, age int)”)
spark.sql(“load data local inpath ‘data/user.json’ into table user”)
spark.sql(“select * from user”)
在spark根目录会新增两个文件夹
SparkSQL操作外置Hive
配置步骤:
- 将外置hive的hive-site.xml拷贝到spark的conf/目录下
- 将msyql的驱动拷贝到spark的jars/目录下
- 若访问不到hdfs,则需要将core-site.xml和hdfs-site.xml拷贝到spark的conf/目录下
- 重启spark-shell
项目使用:
- 引依赖
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.30</version>
</dependency>
<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-hive_2.12</artifactId>
<version>3.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>3.0.0</version>
</dependency>
- 将hive-site.xml添加至类路径resources/下
- 启用hive支持
val ss: SparkSession = SparkSession.builder().enableHiveSupport().config(conf).getOrCreate()
Spark beeline操作hive
- 将外置hive的hive-site.xml拷贝到spark的conf/目录下
- 将msyql的驱动拷贝到spark的jars/目录下
- 若访问不到hdfs,则需要将core-site.xml和hdfs-site.xml拷贝到spark的conf/目录下
- 启动Thrift Server
sbin/start-thriftserver.sh
- 使用beeline连接Thrift Server
bin/beeline -u jdbc:hive2://linux1:10000 -n root
综合案例
package com.itjeffrey.spark.sql.cases
import org.apache.spark.SparkConf
import org.apache.spark.sql.expressions.Aggregator
import org.apache.spark.sql.{Encoder, Encoders, SparkSession, functions}
import scala.collection.mutable
import scala.collection.mutable.ListBuffer
/**
* SparkSQL综合案例(注:该案例使用外置hive操作)
* 需求:统计各区域热门商品Top3
查询出来所有的点击记录,并与 city_info 表连接,得到每个城市所在的地区,与product_info 表连接得到产品名称。
按照地区和商品 id 分组,统计出每个商品在每个地区的总点击次数。
每个地区内按照点击次数降序排列。
只取前三名。
城市备注需要自定义 UDAF 函数。
这里的热门商品是从点击量的维度来看的,计算各个区域前三大热门商品,并备注上每个商品在主要城市中的分布比例,超过两个城市用其他显示。
地区 商品名称 点击次数 城市备注
华北 商品A 100000 北京21.2%,天津 13.2%,其他 65.6%
华北 商品 P 80200 北京 63.0%,太原 10%,其他 27.0%
华北 商品 M 40000 北京 63.0%,太原 10%,其他 27.0%
东北 商品 J 92000 大连 28%,辽宁 17.0%,其他 55.0%
*
* @From: Jeffrey
* @Date: 2022/11/21
*/
object ComprehensiveCase {
def main(args: Array[String]): Unit = {
System.setProperty("HADOOP_USER_NAME", "root")
val sparkConf = new SparkConf().setMaster("local[*]").setAppName("sparkSQL")
val spark =
SparkSession
.builder()
.enableHiveSupport()
.config(sparkConf)
.config("dfs.client.use.datanode.hostname", "true")
.config("dfs.replication", "2")
.getOrCreate()
//初始化数据库和表
initData(spark)
//执行业务
executeBiz(spark)
spark.close()
}
def executeBiz(spark: SparkSession): Unit = {
// 查询基本数据
spark.sql(
"""
|select
| a.*,
| p.product_name,
| c.area,
| c.city_name
|from user_visit_action a
|join product_info p on a.click_product_id = p.product_id
|join city_info c on a.city_id = c.city_id
|where a.click_product_id > -1
""".stripMargin).createOrReplaceTempView("t1")
// 对区域商品组中的数据进行聚合
spark.udf.register("cityRemark", functions.udaf(new CityRemarkUDAF()))
spark.sql(
"""
|select
| area,
| product_name,
| count(*) as clickCnt,
| cityRemark(city_name) as city_remark
|from t1 group by area, product_name
""".stripMargin).createOrReplaceTempView("t2")
// 区域内对点击数量进行排行
//1、partition by 用于给结果集分组,如果没有指定那么它把整个结果集作为一个分组
//2、order by xx desc 用于给分组后的结果集进行降序排序
//2、rank() 在每个分组内部进行排名1,2,3...
spark.sql(
"""
|select
| *,
| rank() over( partition by area order by clickCnt desc ) as rank
|from t2
""".stripMargin).createOrReplaceTempView("t3")
// 取前三名
// truncate = false : 避免因内容过长被截取
spark.sql(
"""
|select
| *
|from t3 where rank <= 3
""".stripMargin).show(false)
}
def initData(spark: SparkSession): Unit = {
spark.sql("use hive")
// 准备数据
spark.sql(
"""
|CREATE TABLE `user_visit_action`(
| `date` string,
| `user_id` bigint,
| `session_id` string,
| `page_id` bigint,
| `action_time` string,
| `search_keyword` string,
| `click_category_id` bigint,
| `click_product_id` bigint,
| `order_category_ids` string,
| `order_product_ids` string,
| `pay_category_ids` string,
| `pay_product_ids` string,
| `city_id` bigint)
|row format delimited fields terminated by '\t'
""".stripMargin)
spark.sql(
"""
|load data local inpath './datas/user_visit_action.txt' into table hive.user_visit_action
""".stripMargin)
spark.sql(
"""
|CREATE TABLE `product_info`(
| `product_id` bigint,
| `product_name` string,
| `extend_info` string)
|row format delimited fields terminated by '\t'
""".stripMargin)
spark.sql(
"""
|load data local inpath './datas/product_info.txt' into table hive.product_info
""".stripMargin)
spark.sql(
"""
|CREATE TABLE `city_info`(
| `city_id` bigint,
| `city_name` string,
| `area` string)
|row format delimited fields terminated by '\t'
""".stripMargin)
spark.sql(
"""
|load data local inpath './datas/city_info.txt' into table hive.city_info
""".stripMargin)
spark.sql("""select * from city_info""").show
}
case class Buffer(var total: Long, var cityMap: mutable.Map[String, Long])
// 自定义聚合函数: 实现城市备注功能
// 继承Aggregator
// IN : 城市名称
// BUF :【当前地区商品组中包含多个城市的总点击数量 total, 当前地区商品组中每个城市及对应点击数量 Map[(city, cnt), (city, cnt)]】
// OUT : 备注信息
class CityRemarkUDAF extends Aggregator[String, Buffer, String] {
// 缓冲区初始化
override def zero: Buffer = {
Buffer(0, mutable.Map[String, Long]())
}
// 更新缓冲区
override def reduce(buff: Buffer, city: String): Buffer = {
buff.total += 1
val newCount = buff.cityMap.getOrElse(city, 0L)+ 1
buff.cityMap.update(city, newCount)
buff
}
override def merge(buff1: Buffer, buff2: Buffer): Buffer = {
buff1.total += buff2.total
val map1 = buff1.cityMap
val map2 = buff2.cityMap
//foldLeft-将右边map合并到左边map
// buff1.cityMap = map1.foldLeft(map2) {
// case (map, (city, cnt)) => {
// val newCount = map.getOrElse(city, 0L) + cnt
// map.update(city, newCount)
// map
// }
// }
//或者直接对map2进行遍历,一个个匹配更新map1
map2.foreach{
case (city, cnt) => {
val newCount = map1.getOrElse(city, 0L) + cnt
map1.update(city, newCount)
}
}
buff1.cityMap = map1
buff1
}
// 将统计的结果生成字符串信息
override def finish(buff: Buffer): String = {
//定义可变集合
val remarkList = ListBuffer[String]()
val totalcnt = buff.total
val cityMap = buff.cityMap
// 降序排列取前两个
var cityCntList = cityMap.toList.sortWith(
(left, right) => {
left._2 > right._2
}
).take(2)
val hasMore = cityMap.size > 2
var rsum = 0L
cityCntList.foreach {
case (city, cnt) => {
val r = cnt * 100 / totalcnt
remarkList.append(s"${city} ${r}%")
rsum += r
}
}
if (hasMore) {
remarkList.append(s"其他${100 - rsum}%")
}
remarkList.mkString(",")
}
override def bufferEncoder: Encoder[Buffer] = Encoders.product
override def outputEncoder: Encoder[String] = Encoders.STRING
}
}