Spark SQL
一、Spark SQL概述
Spark SQL是Spark用来处理结构化数据的一个模块，它提供了2个编程抽象：DataFrame和DataSet，并且作为分布式SQL查询引擎的作用。
对比：
Hive：Hive SQL转换为MapReduce然后提交到集群上执行，大大简化了编写MapReduc的程序的复杂性，由于MapReduce这种计算模型执行效率比较慢。
Spark SQL:是将Spark SQL转换成RDD，然后提交到集群执行，执行效率非常快！
1. DataFrame概念
与RDD类似，DataFrame也是一个分布式数据容器。然而DataFrame更像传统数据库的二维表格，除了数据以外，还记录数据的结构信息，即schema。
右侧的DataFrame却提供了详细的结构信息，使得Spark SQL可以清楚地知道该数据集中包含哪些列，每列的名称和类型各是什么。DataFrame是为数据提供了Schema的视图。可以把它当做数据库中的一张表来对待，DataFrame也是懒执行的。性能上比RDD要高，主要原因：优化的执行计划，查询计划通过Spark catalyst optimiser进行优化。
2. DataSet概念
1）是Dataframe API的一个扩展，是Spark最新的数据抽象。
2）用户友好的API风格，既具有类型安全检查也具有Dataframe的查询优化特性。
3）样例类被用来在Dataset中定义数据的结构信息，样例类中每个属性的名称直接映射到DataSet中的字段名称。
4） Dataframe是Dataset的特列，DataFrame=Dataset[Row] ，所以可以通过as方法将Dataframe转换为Dataset。Row是一个类型，跟Car、Person这些的类型一样，所有的表结构信息我都用Row来表示。
5）DataSet是强类型的。比如可以有DataSet[Car]，DataSet[Person].
6）DataFrame只是知道字段，但是不知道字段的类型，所以在执行这些操作的时候是没办法在编译的时候检查是否类型失败的，比如你可以对一个String进行减法操作，在执行的时候才报错，而DataSet不仅仅知道字段，而且知道字段类型，所以有更严格的错误检查。就跟JSON对象和类对象之间的类比。
二、Spark SQL编程
添加依赖
<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-core_2.11</artifactId>
<version>2.4.3</version>
</dependency>
<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-sql_2.11</artifactId>
<version>2.4.3</version>
</dependency>
spark-sql包括spark-core
1. DataFrame
1.1 创建DataFrame
-
创建 a.json
{“name”:“zhangsan”,“age”:20}
{“name”:“李四”,“age”:22}
{“name”:“wanguw”,“age”:24} -
读取上边创建的文件,创建DataFrame对象
val spark: SparkSession = SparkSession.builder() .appName("ceshi") .master("local[*]") .getOrCreate() val df: DataFrame = spark.read.json("F:\\datas\\a.json") df.show() spark.stop() -
展示结果
+---+--------+ |age| name| +---+--------+ | 20|zhangsan| | 22| 李四| | 24| wanguw| +---+--------+
1.2 SQL风格语法
需求：读取json文件创建DataFrame，建立临时表，通过SQL语句查询数据
val df: DataFrame = spark.read.json("F:\\datas\\a.json")
df.createOrReplaceTempView("t_user")
val sqlDF: DataFrame = spark.sql("select * from t_user")
sqlDF .show()
展示结果：
+---+--------+
|age| name|
+---+--------+
| 20|zhangsan|
| 22| 李四|
| 24| wanguw|
+---+--------+
1.3 DSL风格语法
需求：读取json文件创建DataFrame，通过DSL方法查询
//这是一行代码,做隐式转换 spark是上边定义的变量
import spark.implicits._
val df = spark.read.json("F:\\datas\\a.json")
df.printSchema() //打印Schema结构
//指定列查询
df.select($"name",$"age").show() //select name,age from xxx
//查询大于20岁的
df.filter($"age">20).show() //select * from xx where age > 20
//根据年龄分组，统计个数
df.groupBy("age").count().show()
1.4 RDD转换为DataFrame
注意：如果需要RDD与DF或者DS之间操作，那么都需要引入
import spark.implicits._（导入隐式转换）
-
创建a.txt
zhangsan 20 lisi 20 wangwu 23 zhaoliu 45 -
读取上边创建的文件,创建RDD后，转换为DataFrame对象
import spark.implicits._ //这是一行代码,做隐式转换 spark是上边定义的变量 val rdd1: RDD[String] = spark.sparkContext.textFile("F:\\datas\\a.txt") val df: DataFrame = rdd1.map(v => { val p: Array[String] = v.split("\t") (p(0), p(1)) }).toDF("name", "age") df.show()
1.5 DataFrame转换为RDD
val df = spark.read.json("F:\\datas\\a.json")
val rdd: RDD[Row] = df.rdd
rdd.collect().foreach(v=>println(v))
2. DataSet
Dataset是具有强类型的数据集合，需要提供对应的类型信息。
2.1 创建DataSet
-
定义case class
case class Person(name:String,age:Long) -
创建List集合，转换为DataSet对象
import spark.implicits._ val ds: Dataset[Person] = List(Person("zhangsan",20),Person("lisi",22)).toDS() ds.show()
2.2 RDD转换为DataSet
import spark.implicits._
val rdd1: RDD[String] = spark.sparkContext.textFile("F:\\datas\\a.txt")
rdd1.map(v=>{
val arr: Array[String] = v.split("\t")
Person(arr(0),arr(1).toLong)
}).toDS().show()
2.3 DataSet转换为RDD
val ds: Dataset[Person] = List(Person("zhangsan",20),Person("lisi",23)).toDS()
val rdd: RDD[Person] = ds.rdd
3. DataFrame和DataSet的互操作
3.1 DataFrame转换为DataSet
import spark.implicits._
val df: DataFrame = spark.read.json("F:\\datas\\a.json")
val ds: Dataset[Person] = df.as[Person]
ds.show()
3.2 DataSet转换为DataFrame
import spark.implicits._
val ds: Dataset[Person] = List(Person("zhangsan",20),Person("lisi",23)).toDS()
val df: DataFrame = ds.toDF()
df.show()
三、自定义函数
1. 单行函数
import spark.implicits._
val df: DataFrame = List(("zhangsan", 20, 1), ("lisi", 20, 1), ("xiaohong", 18, 0))
.toDF("name", "age", "sex")
df.createOrReplaceTempView("t_user")
spark.udf.register("convert_sex",(sex:Int)=>{
sex match {
case 0 => "女"
case 1 => "男"
}
})
spark.sql("select name,age,convert_sex(sex) sex from t_user").show()
2. 聚合函数
-
定义求和的聚合函数
// 聚合函数(x1,x2) class CustomSum extends UserDefinedAggregateFunction{ //extends UDAF //聚会函数输入参数的类型 override def inputSchema: StructType = { new StructType().add("input1",IntegerType) } //聚会缓冲区中值的数据类型 override def bufferSchema: StructType = { new StructType().add("sum",IntegerType).add("c",IntegerType) } //返回值的数据类型 override def dataType: DataType = IntegerType //对于相同的输入是否一直返回相同的输出 override def deterministic: Boolean = true //初始化 override def initialize(buffer: MutableAggregationBuffer): Unit = { buffer(0) = 0 } //将缓冲区的数据加上输入的数据，然后更新到缓冲区中 override def update(buffer: MutableAggregationBuffer, input: Row): Unit = { val d1: Int = buffer.getAs[Int](0) val d2: Int = input.getAs[Int](0) buffer.update(0,d1+d2) } //合并缓冲区的数据 override def merge(buffer1: MutableAggregationBuffer, buffer2: Row): Unit = { val d1: Int = buffer1.getAs[Int](0) val d2: Int = buffer2.getAs[Int](0) buffer1.update(0,d1+d2) } //返回最终结果 override def evaluate(buffer: Row): Any = { buffer.getAs[Int](0) } } -
测试自定义求和聚合函数
val df: DataFrame = List(("zhangsan", 20, 1), ("lisi", 20, 1), ("xiaohong", 18, 0)) .toDF("name", "age", "sex") df.createOrReplaceTempView("t_user") spark.udf.register("customSum",new CustomSum) spark.sql("select customSum(age) sex from t_user").show()
四、Load/Save
Spark SQL的DataFrame接口支持多种数据源的操作。
1.parquet
Parquet是一种流行的列式存储格式，可以高效地存储具有嵌套字段的记录。Parquet格式经常在Hadoop生态圈中被使用，它也支持Spark SQL的全部数据类型。Spark SQL 提供了直接读取和存储 Parquet 格式文件的方法。
//保存数据
import spark.implicits._
val df: DataFrame = List((1,"zhangsan",20),(2,"lisi",23),(3,"wangwu",23)).toDF("id","name","age")
df.write.parquet("f:\\datas\\parquet")
//读取数据
spark.read.parquet("f:\\datas\\parquet").show()
SaveMode 执行存储操作：
SaveMode定义了对数据的处理模式。需要注意的是，这些保存模式不使用任何锁定，不是原子操作。此外，当使用Overwrite方式执行时，在输出新数据之前原数据就已经被删除。
eg：df.write.mode(SaveMode.Overwrite).parquet("f:\datas\parquet")
| Scala | Meaning |
|---|---|
| SaveMode.ErrorIfExists (default) | 如果文件存在，则报错 |
| SaveMode.Append | 追加 |
| SaveMode.Overwrite | 覆盖 |
| SaveMode.Ignore | 数据存在，则忽略 |
2. json
import spark.implicits._
val df: DataFrame = List((1,"zhangsan",20),(2,"lisi",23),(3,"wangwu",23)).toDF("id","name","age")
df.write.mode(SaveMode.Overwrite).json("f:\\datas\\json")
spark.read.json("f:\\datas\\json").show()
3. CSV
val df: DataFrame = List((1,"zhangsan",20),(2,"lisi",23),(3,"wangwu",23)).toDF("id","name","age")
df.write.mode(SaveMode.Overwrite).option("header", "true").csv("f:\\datas\\csv")
spark.read.option("header", "true").csv("f:\\datas\\csv").show()
4. JDBC
Spark SQL可以通过JDBC从关系型数据库中读取数据的方式创建DataFrame，通过对DataFrame一系列的计算后，还可以将数据再写回关系型数据库中。
需要添加jdbc的驱动依赖
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.38</version>
</dependency>
代码：
val properties = new Properties()
properties.put("user", "root")
properties.put("password", "123456")
val df: DataFrame = List((1,"zhangsan",20),(2,"lisi",23),(3,"wangwu",23)).toDF("id","name","age")
//写入mysql
df.write.jdbc("jdbc:mysql://localhost:3306/test1", "df_user", properties)
//读出
spark.read.jdbc("jdbc:mysql://localhost:3306/test1","df_user",properties).show()
Spark整合Hive
在命令行访问Hive
Spark 要接管 Hive 需要把 hive-site.xml copy 到conf/目录下
• 把 Mysql 的驱动 copy 到 jars/目录下.
• 如果访问不到hdfs, 则需要把core-site.xml和hdfs-site.xml 拷贝到conf/目录下.
启动 spark-shell
scala> spark.sql("use baizhi")
scala> spark.sql("select * from t_user").show
在代码中访问 Hive
步骤1: 拷贝 hive-site.xml 到 resources 目录下
步骤2: 添加依赖
<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-hive_2.11</artifactId>
<version>2.4.3</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.38</version>
</dependency>
步骤3: 代码
import org.apache.spark.sql.SparkSession
object HiveDemo {
def main(args: Array[String]): Unit = {
val spark: SparkSession = SparkSession
.builder()
.master("local[*]")
.appName("Test")
.enableHiveSupport()
.getOrCreate()
import spark.implicits._
spark.sql("use baizhi")
spark.sql("select * from t_user").show
spark.stop()
}
}
SQL语句练习
1. 条件查询
import spark.implicits._
val df: DataFrame = List((1,"张三",20),(2,"李四",30)).toDF("id","name","age")
df.createOrReplaceTempView("t_user")
spark.sql("select * from t_user where id = 1").show()
2. 模糊查询
val df: DataFrame = List((1,"张三",20),(2,"李四",30)).toDF("id","name","age")
df.createOrReplaceTempView("t_user")
spark.sql("select * from t_user where name like '%三%'").show()
3. 排序
val df: DataFrame = List((1,"张三",20),(2,"李四",30)).toDF("id","name","age")
df.createOrReplaceTempView("t_user")
spark.sql("select * from t_user order by age desc").show()
4. limit
val df: DataFrame = List((1,"张三",20),(2,"李四",30),(3,"王五",25),
(4,"赵六",25)).toDF("id","name","age")
df.createOrReplaceTempView("t_user")
spark.sql("select * from t_user order by age desc limit 2").show()
5. 去重
val df: DataFrame = List((1,"张三",20),(2,"李四",30),(3,"王五",25),
(4,"赵六",25)).toDF("id","name","age")
df.createOrReplaceTempView("t_user")
spark.sql("select distinct age from t_user").show()
6. 分组查询
val df: DataFrame = List((1,"张三",20),(2,"李四",30),(3,"王五",25),
(4,"赵六",25)).toDF("id","name","age")
df.createOrReplaceTempView("t_user")
spark.sql("select age,count(*) from t_user group by age").show()
7. case-when
case 列名 when 值1 then 输出值 when 值2 then 输出值 else 输出值 end
val df: DataFrame = List((1,"张三",20,1),(2,"李四",30,1),
(3,"小红",23,0)).toDF("id","name","age","sex")
df.createOrReplaceTempView("t_user")
val sql =
"""
select id,name,age,sex,
case sex when 1 then '男' when 0 then '女' else '其他' end as sex2
from t_user t1
"""
spark.sql(sql).show()
8. 行转列
var scoreDF=List((1, "语文", 100), (1, "数学", 100),(1, "英语", 100),
(2, "数学", 79),(2, "语文", 80),(2, "英语", 100))
.toDF("id","course","score")
scoreDF.createOrReplaceTempView("t_course")
val sql =
"""
select id ,
max(case course when '语文' then score else 0 end ) chinese,
max(case course when '数学' then score else 0 end ) math,
max(case course when '英语' then score else 0 end ) english
from t_course
group by id
"""
spark.sql(sql).show()
9. join计算
val stuDF = List((1, "张三", 1001), (2, "李四", 1001), (3, "王五", 1002), (3, "赵六", 1003))
.toDF("id","name","cid")
val classDF = List((1001, "Java班"), (1002, "UI班"))
.toDF("cid","cname")
stuDF.createOrReplaceTempView("t_student")
classDF.createOrReplaceTempView("t_class")
val sql =
"""
select * from t_student t1 left join t_class t2 on t1.cid = t2.cid
"""
spark.sql(sql).show()
10. 开窗函数
开窗函数也叫分析函数，有两类：一类是聚合开窗函数，一类是排序开窗函数。
开窗函数的调用格式为：
函数名(列名) OVER(partition by 列名 order by列名) 。
如果你没听说过开窗函数，看到上面开窗函数的调用方法，你可能还会有些疑惑。但只要你了解聚合函数，那么理解开窗函数就非常容易了。
我们知道聚合函数对一组值执行计算并返回单一的值，如sum()，count()，max()，min()， avg()等，这些函数常与group by子句连用。除了 COUNT 以外，聚合函数忽略空值。
但有时候一组数据只返回一组值是不能满足需求的，如我们经常想知道各个地区的前几名、各个班或各个学科的前几名。这时候需要每一组返回多个值。用开窗函数解决这类问题非常方便。
row_number ( 排序 )
var 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)).toDF("id","name","sex","dept","salary")
df.createTempView("t_emp")
val sql =
"""
|select id,name,salary,dept,
|row_number() over(partition by dept order by salary desc) x1
|from t_emp
""".stripMargin
spark.sql(sql).show()
rank ( 排序 )
var df=List((1,"zs",true,1,18000),(2,"ls",false,2,18000),(3,"ww",false,2,14000),(4,"zl",false,1,18000),
(5,"win7",false,1,16000)).toDF("id","name","sex","dept","salary")
df.createTempView("t_emp")
val sql =
"""
|
|select id,name,salary,dept,
|rank() over(partition by dept order by salary desc) x1
|from t_emp
""".stripMargin
spark.sql(sql).show()
dense_rank ( 排序 )
var df=List((1,"zs",true,1,18000),(2,"ls",false,2,18000),(3,"ww",false,2,14000),(4,"zl",false,1,18000),
(5,"win7",false,1,16000)).toDF("id","name","sex","dept","salary")
df.createTempView("t_emp")
val sql =
"""
|select id,name,salary,dept,
|dense_rank () over(partition by dept order by salary desc) x1
|from t_emp
""".stripMargin
spark.sql(sql).show()
聚合函数(sum、avg、count、max、min)
聚合函数(字段) over ([partition by 字段 order by 字段 asc])
例如: sum(salary) over(partition by xx order by xx)
有order by；按照排序连续累加；
无order by，计算partition by后的和；
over()中没有partition by，计算所有数据总和
var df=List((1,"zs",true,1,100),(2,"ls",false,2,300),(3,"ww",false,2,500),(4,"zl",false,1,200),
(5,"win7",false,1,150)).toDF("id","name","sex","dept","salary")
df.createTempView("t_emp")
val sql =
"""
|select id,name,sex,dept,salary,
|sum(salary) over() as x1,
|sum(salary) over(partition by dept) as x2,
|sum(salary) over(partition by dept order by salary asc) as x3
|from t_emp
|order by salary asc
""".stripMargin
spark.sql(sql).show()
实例：
625
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
