Spark-SQL
这里写目录标题
1、Shark
- Shark 是基于 Spark 计算框架之上且兼容 Hive 语法的 SQL 执行引擎,由于底层的计算采用了 Spark , 性能比 MapReduce 的 Hive 普遍快2倍以上,当数据全部加载在内存的话,将快10倍以上,因此 Shark 可以作为交互式查询应用服务来使用。
- 除了基于 Spark 的特性外, Shark 是完全兼容 Hive 的语法,表 结构以及 UDF 函数等,已有的 Hive Sql 可以直接进行迁移至 Shark 上, Shark 底层依赖于 Hive 的解析器,查询优化器。
- 由于 shark 的整体设计架构对 Hive 的依赖性太强,难以支持其长远发展,比如不能和 Spark 的其他组件进行很好的集成,无法满足 Spark 的一站式解决大数据处理的需求。
2、SparkSQL
2.1. SparkSQL介绍
- Shark 是 SparkSQL 的前身, SparkSQL 产生的根本原因是其完全脱离了 Hive 的限制。
- Spark SQL在Hive兼容层面仅依赖HiveQL解析、Hive元数据。
- 从 HQL被解析成抽象语法树(AST)起,就全部由Spark SQL接管了。Spark SQL执行计划生成和优化都由Catalyst(函数式关系查询优化框架)负责。
- SparkSQL 支持查询原生的 RDD 。 RDD 是 Spark 的核心概念,是 Spark 能够高效的处理大数据的 各种场景的基础。
- Spark SQL增加了SchemaRDD(即带有Schema信息的RDD),使用户可以在Spark SQL中执行SQL语句,数据既可以来自RDD,也可以是Hive、 HDFS、Cassandra等外部数据源,还可以是JSON格式的数据。
- Spark SQL目前支持Scala、Java、Python三种语言。
2.2. Spark on Hive和Hive on Spark
- Spark on Hive :Hive只作为储存角色,Spark负责sql解析优化,执行。
- Hive on Spark :Hive即作为存储又负责sql的解析优化,Spark负责执行。
2.3.创建sparksql
def main(args: Array[String]): Unit = {
//创建环境
val sparkContext = new SparkContext(new SparkConf().setMaster("local").setAppName("Hello01RDDAvg"))
//读取数据
val lines: RDD[String] = sparkContext.textFile("src/main/resources/emp.txt")
//开始转换
val deptnoSal: RDD[(String, Int)] = lines.map(ele => (ele.split(",")(7), ele.split(",")(5).toInt))
//开始分组
val groupSal: RDD[(String, Iterable[Int])] = deptnoSal.groupByKey()
//开始迭代
groupSal.foreach(ele => {
val list: Seq[Int] = ele._2.toList
val sum = list.sum
val avg = sum / list.size
println(ele._1 + "--" + avg)
})
//关闭
sparkContext.stop()
}
2.4. SparkSQL的数据源
- SparkSQL 的数据源可以是
JSON
类型的字符串,JDBC
,Parquet
,Hive
,HDFS
等。
3、 Dataset与DataFrame
- Dataset 也是一个分布式数据容器。与 RDD 类似,然而 Dataset 更像传统数据库的二维表格,
- 除了数据以外,还掌握数据的结构信息,即 schema 。Schema将数据和表结构映射到一起
- DataSet = RDD + Schema
- 同时,与 Hive 类似, Dataset 也支持嵌套数据类型 ( struct 、 array 和 map )。
Dataset
的底层封装的是 RDD ,当 RDD [Row ]类型的时候,我们也可以称它为DataFrame
。即
Dataset[Row] = DataFrame
3.2. 创建Dataset&DataFrame方式
方式一 :
//将RDD转成DataFrame
import sparkSession.implicits._
val Dataset: Dataset[Emp] = emps.toDS()
方式二:
//开始转换,
val dataFrame = sparkSession.createDataFrame(empRDD, empStructType)
3.2.1. 样式类 case class
case class Emp(empno: Int, ename: String, job: String, mgr: Int, hiredate: String, sal: Double, comm: Double, deptno: Int)
//进行转换(每行都构建一个Emp)
val emps: RDD[Emp] = lines.map(_.split(",")).map(e => Emp(e(0).toInt, e(1), e(2), e(3).toInt, e(4), e(5).toDouble, e(6).toDouble, e(7).toInt))
//将RDD转成DataFrame
import sparkSession.implicits._
val Dataset: Dataset[Emp] = emps.toDS()
3.2.2 结构化类型 structType
/**
* 结构化类型
*/
private val empStructType: StructType = StructType(
List(
StructField("empno", DataTypes.IntegerType),
StructField("ename", DataTypes.StringType),
StructField("job", DataTypes.StringType),
StructField("mrg", DataTypes.IntegerType),
StructField("hiredate", DataTypes.StringType),
StructField("sal", DataTypes.DoubleType),
StructField("comm", DataTypes.DoubleType),
StructField("deptno", DataTypes.IntegerType)
)
)
//进行转换(每行都构建一个Emp)
val empRDD: RDD[Row] = lines.map(_.split(",")).map(e => Row(e(0).toInt, e(1), e(2), e(3).toInt, e(4), e(5).toDouble, e(6).toDouble, e(7).toInt))
//开始转换
val dataFrame = sparkSession.createDataFrame(empRDD, empStructType)
3.3. 使用SQL方式查询
- createOrReplaceTempView(“表名”) 创建一个零时表,通过SQL方式查询
def main(args: Array[String]): Unit = {
val sparkSession: SparkSession = SparkSession.builder().master("local").appName("StructType").getOrCreate()
val value: RDD[String] = sparkSession.sparkContext.textFile("src/main/resources/emp.txt")
//进行转换(每行都构建一个Emp)
val empRDD: RDD[Row] = value.map(_.split(",")).map(e => Row(e(0).toInt, e(1), e(2), e(3).toInt, e(4), e(5).toDouble, e(6).toDouble, e(7).toInt))
//转成dataFrame
val dfr: DataFrame = sparkSession.createDataFrame(empRDD,empSt)
dfr.createOrReplaceTempView("t_emp")
sparkSession.sql("select * from t_emp").show()
}
4、DSL数据操作
4.1. Action算子
- show()显示前20行 show(n)显示前n行
- collect() :获取所有数据返回Array
- collectAsList() :获取所有数据返回list
- describe() : 获取指定字段
- first(),head(),take(N) :获取第一行
- takeAsList() 获取前n行返回list
4.2. 查询
- where 条件过滤
- filter 条件过滤
- select 指定字段查询
- selectExpr : 指定字段进行特殊处理
- col、apply : 获取指定字段
- drop :删除指定字段
- limit : 返回前n行
4.3. 排序
- orderby : 排序
- sort : 排序
4.4. 组函数
- groupby : 分组
- cube/rollup :分组
- grouped data :组函数如 max ,min,avg
4.5. 去重
- distinct 去重
- dropdulicates : 根据指定字段去重
5、spark SQL 底层结构
-
使用SQL的方式就能够实现大数据的开发,
-
它同时支持DSL以及SQL的语法风格,
-
目前在spark的整个架构设计当中,所有的spark模块,例如SQL,SparkML,sparkGrahpx以及Structed Streaming等都是基于 Catalyst Optimization & Tungsten Execution模块之上运行。
-
SparkSQL 的 Dataset 和 SQL 并不是直接生成计划交给集群执行, 而是经过了一个叫做 Catalyst 的优化器, 这个优化器能够自动帮助开发者优化代码。
-
用于处理结构化数据和半结构化数据, 所以 SparkSQL 可以获知数据的 Schema, 从而根据其 Schema 来进行优化。
-
1.API 层简单的说就是 Spark 会通过一些 API 接受 SQL 语句
2.收到 SQL 语句以后, 将其交给 Catalyst, Catalyst 负责解析 SQL, 生成执行计划等
3.Catalyst 的输出应该是 RDD 的执行计划
4.最终交由集群运行
5.1. Catalyst优化器
- Spark SQL的核心是Catalyst优化器,它以一种新颖的方式利用高级编程语言功能(例如Scala的模式匹配和quasiquotes)来构建可扩展的查询优化器。
- Catalyst 的主要运作原理是分为三步, 先对 SQL 或者 Dataset 的代码解析, 生成逻辑计划, 后对逻辑计划进行优化, 再生成物理计划, 最后生成代码到集群中以 RDD 的形式运行。
- 1-首先SaprkSQL底层解析成RDD,通过两个阶段RBO和CBO
2-RBO就是通过逻辑执行计划通过常见的优化达到优化逻辑执行计划
3-CBO就是从优化后的逻辑计划到物理执行计划
- 1-首先SaprkSQL底层解析成RDD,通过两个阶段RBO和CBO
- sql解析阶段 parse
- 生成逻辑计划 Analyzer
- sql语句调优阶段 Optimizer
- 生成物理查询计划 planner
5.2. 谓词下推
谓词下推就是指将各个条件先应用到对应的数据上,而不是根据写入的顺序执行,这样就可以先过滤掉部分数据,降低join等一系列操作的数据量级,提高运算速度,如下图:
6、数据获取/输出的几种格式
//读入 方式一
val dFt1: DataFrame = sparkSession.read.format("格式").load("Path")
//读入 方式二
val dFt2: Dataset[Row] = sparkSession.read.格式("Path").as("Class类")
6.0. save()与mode()
- 可以将 Dataset 存储成 其他格式文件。保存成其他格式文件的方式有两种。
//写出方式
df.write().mode(SaveMode.Overwrite).format("格式").save("path");
df.write().mode(SaveMode.Overwrite).格式("path");
- mode(SaveMode.???) 保存文件模式
- SaveMode 指定文件保存时的模式。
- Overwrite :覆盖
- Append :追加
- ErrorIfExists :如果存在就报错
- Ignore :如果存在就忽略
pom.xml
<!-- https://mvnrepository.com/artifact/org.apache.spark/spark-hive -->
<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-hive_2.12</artifactId>
<version>2.4.6</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.hive/hive-exec -->
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>3.1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.32</version>
</dependency>
6.1. text
//读取
sparkSession.sparkContext.textFile("src/main/resources/emp.txt")
//写出
empSal.saveAsTextFile("src/main/resources/t_emp_sal.txt")
def main(args: Array[String]): Unit = {
val sparkSession: SparkSession = SparkSession.builder().master("local").appName("hello01SparkSql").getOrCreate()
val value: RDD[String] = sparkSession.sparkContext.textFile("src/main/resources/emp.txt")
//开始转换
val sqlSal: RDD[(String, Int)] = value.map(ele => (ele.split(",")(7), ele.split(",")(5).toInt))
//分组求和
val empsql: RDD[(String, Int)] = sqlSal.groupByKey().mapValues(iter => {
val list: List[Int] = iter.toList
val sum: Int = list.sum
val i: Int = sum / list.size
i
})
empsql.foreach(println)
//输出text
empsql.saveAsTextFile("src/main/resources/t_emp_sal.txt")
//关闭
sparkSession.stop()
}
6.2. json
def main(args: Array[String]): Unit = {
val sparkSession: SparkSession = SparkSession.builder().master("local").appName("hello01_sql").getOrCreate()
//方式一:读取
val dFt1: DataFrame = sparkSession.read.json("src/main/resources/t_emp1.txt").as("Emp")
//方式二:读取
//val dFt2: DataFrame = sparkSession.read.format("json").load("src/main/resources/t_emp.txt")
//写出
//dFt1.write.mode(SaveMode.Overwrite).format("json").save("src/main/resources/t_emp1.txt")
//写出 方式二
dFt1.write.mode(SaveMode.Overwrite).json("src/main/resources/t_emp.txt")
dFt1.show()
//dFt2.show()
}
6.3. parquet
def main(args: Array[String]): Unit = {
val sparkSession: SparkSession = SparkSession.builder().master("local").appName("sql04_parquet").getOrCreate()
//读入 方式一
//val dFt1: DataFrame = sparkSession.read.format("parquet").load("src/main/resources/t_emp.txt")
//读入 方式二
val dFt2: Dataset[Row] = sparkSession.read.parquet("src/main/resources/t_emp.txt").as("Emp")
//写出 方式一
//dFt2.write.mode(SaveMode.Overwrite).format("parquet").save("src/main/resources/t_emp1.txt")
//写出 方式二
dFt2.write.mode(SaveMode.Overwrite).parquet("src/main/resources/t_emp1.txt")
dFt2.show()
}
6.4. Jdbc
def main(args: Array[String]): Unit = {
//创建SQL环境
val sparkSession: SparkSession = SparkSession.builder().master("local").appName("sql04_parquet").getOrCreate()
//数据库参数
val map = new mutable.HashMap[String, String]()
map.put("url", "jdbc:mysql://localhost:3306/java46?useSSL=false&serverTimezone=UTC&characterEncoding=utf8")
map.put("driver", "com.mysql.cj.jdbc.Driver")
map.put("user", "root")
map.put("password", "root")
map.put("dbtable", "emp")
//读取JDBC数据 方式一
val dFt1: DataFrame = sparkSession.read.format("jdbc").options(map).load()
//数据库参数
val properties = new Properties()
properties.setProperty("driver", "com.mysql.cj.jdbc.Driver")
properties.setProperty("user", "root")
properties.setProperty("password", "root")
//读取JDBC数据 方式二
val dFt2: DataFrame = sparkSession.read
.jdbc("jdbc:mysql://localhost:3306/java46?useSSL=false&serverTimezone=UTC&characterEncoding=utf8", "emp", properties)
//写入JDBC数据 方式一
dFt2.write.mode(SaveMode.Overwrite)
.jdbc("jdbc:mysql://localhost:3306/java46?serverTimezone=UTC&characterEncoding=utf8&useSSL=false","t_emp",properties)
//写入JDBC数据 方式二
map.put("dbtable", "t_emp3")
dFt1.write.mode(SaveMode.Overwrite).format("jdbc")
.options(map).save("jdbc:mysql://localhost:3306/java46?serverTimezone=UTC&characterEncoding=utf8&useSSL=false")
dFt1.show()
dFt2.show()
}
6.5. hive
- 拷贝配置文件,从linux上拷贝hadoop、hive配置
- hdfs-site.xml
- core-site.xml
- hive-site.xml
package com.yjxxt
import org.apache.spark.sql.SparkSession
object HelloSourceHive {
def main(args: Array[String]): Unit = {
//搭建环境
val spark =SparkSession.builder().master("local").appName("HelloSourceHive").enable
HiveSupport().getOrCreate()
//操作数据
spark.sql("use yjx")
val dataFrame = spark.sql("select * from t_user")
dataFrame.show()
//关闭
sparkSession.stop()
}
}
7、Spark On Hive
7.1. UDF
- UDF:one to one,进来一个出去一个,row mapping。是row级别操作,如:upper、substr函数
def main(args: Array[String]): Unit = {
//搭建环境
val sparkSession: SparkSession = SparkSession.builder().master("local").appName("HelloHiveUDF").enableHiveSupport().getOrCreate()
//定义UDF
sparkSession.udf.register("strLen", (x: String) => x.size, DataTypes.IntegerType)
sparkSession.udf.register("yjxConcat", (x: String, y: String) => x + y, DataTypes.StringType)
//SQL
sparkSession.sql("use yjx")
val dataFrame1 = sparkSession.sql("select id,strLen(uname) from t_user")
val dataFrame2 = sparkSession.sql("select id,yjxConcat(uname,gender) from t_user")
//打印数据
dataFrame1.show()
dataFrame2.show()
//关闭
sparkSession.stop()
}
7.2. UDAF
- UDAF:many to one,进来多个出去一个,row mapping。是row级别操作,如sum/min。
- 实现UDAF函数如果要自定义类要实现UserDefinedAggregateFunction类实现其中的方法。
object Hello92HiveUDAF {
def main(args: Array[String]): Unit = {
//搭建环境
val sparkSession = SparkSession.builder().master("local").appName("HelloHiveUDAF").enableHiveSupport().getOrCreate()
//注册函数
sparkSession.udf.register("yjxAvg", YjxAvgUDAF)
//开始查询
sparkSession.sql("use yjx")
sparkSession.sql("select id,uname,gender,age from t_user").show()
sparkSession.sql("select gender,yjxAvg(age) from t_user group by gender").show()
//关闭
sparkSession.stop()
}
}
/**
* 继承用户自定义组合函数的抽象类
*/
object YjxAvgUDAF extends UserDefinedAggregateFunction {
// 聚合函数的输入数据结构
override def inputSchema: StructType = StructType(StructField("input", LongType) :: Nil)
// 缓存区数据结构
override def bufferSchema: StructType = StructType(StructField("sum", LongType) :: StructField("count", LongType) :: Nil)
// 聚合函数返回值数据结构
override def dataType: DataType = DoubleType
// 聚合函数是否是幂等的,即相同输入是否总是能得到相同输出
override def deterministic: Boolean = true
// 初始化缓冲区
override def initialize(buffer: MutableAggregationBuffer): Unit = {
//求和
buffer(0) = 0L
//计数
buffer(1) = 0L
}
// 给聚合函数传入一条新数据进行处理
override def update(buffer: MutableAggregationBuffer, input: Row): Unit = {
if (input.isNullAt(0)) return
buffer(0) = buffer.getLong(0) + input.getLong(0)
buffer(1) = buffer.getLong(1) + 1
}
// 合并聚合函数缓冲区
override def merge(buffer1: MutableAggregationBuffer, buffer2: Row): Unit = {
buffer1(0) = buffer1.getLong(0) + buffer2.getLong(0)
buffer1(1) = buffer1.getLong(1) + buffer2.getLong(1)
}
// 计算最终结果
override def evaluate(buffer: Row): Any = {
buffer.getLong(0).toDouble / buffer.getLong(1)
}
}
7.3. UDTF
- UDTF:one to mang,进来一个出去多行。如lateral view 与 explode,T:table-generating
- 通过实现抽象类org.apache.hadoop.hive.ql.udf.generic.GenericUDTF来自定义UDTF算子
object Hello93HiveUDTF {
def main(args: Array[String]): Unit = {
//搭建环境
val sparkSession = SparkSession.builder().master("local").appName("Hello93HiveUDTF").enableHiveSupport().getOrCreate()
//注册utdf算子,这里无法使用sparkSession.udf.register()
sparkSession.sql("CREATE TEMPORARY FUNCTION UserDefinedUDTF as 'com.yjjxt.UserDefinedUDTF'")
sparkSession.sql("use yjx")
sparkSession.sql("select id , UserDefinedUDTF(uname) from t_user").show()
//关闭
sparkSession.stop()
}
}
class UserDefinedUDTF extends GenericUDTF {
/**
* 方法的作用:1.输入参数校验 2. 输出列定义,可以多于1列,相当于可以生成多行多列数据
*
* @param args
* @return
*/
override def initialize(args: Array[ObjectInspector]): StructObjectInspector = {
if (args.length != 1) {
throw new UDFArgumentLengthException("UserDefinedUDTF takes only one argument")
}
if (args(0).getCategory() != ObjectInspector.Category.PRIMITIVE) {
throw new UDFArgumentException("UserDefinedUDTF takes string as a parameter")
}
val fieldNames = new java.util.ArrayList[String]
val fieldOIs = new java.util.ArrayList[ObjectInspector]
//这里定义的是输出列默认字段名称
fieldNames.add("col1")
//这里定义的是输出列字段类型
fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector)
ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames, fieldOIs)
}
//这是处理数据的方法,入参数组里只有1行数据,即每次调用process方法只处理一行数据
override def process(args: Array[AnyRef]): Unit = {
//将字符串切分成单个字符的数组
val strLst = args(0).toString.split("")
for (i <- strLst) {
var tmp: Array[String] = new Array[String](1)
tmp(0) = i
//调用forward方法,必须传字符串数组,即使只有一个元素
forward(tmp)
}
}
override def close(): Unit = {}
}