#Spark SQL介绍
Spark SQL是一个用于结构化数据处理的Spark组件。所谓结构化数据,是指具有Schema信息的数据,例如JSON、Parquet、Avro、CSV格式的数据。与基础的Spark RDD API不同,Spark SQL提供了对结构化数据的查询和计算接口。
Spark SQL的主要特点:
- 将SQL查询与Spark应用程序无缝组合
Spark SQL允许使用SQL或熟悉的API在Spark程序中查询结构化数据。与Hive不同的是,Hive是将SQL翻译成MapReduce作业,底层是基于MapReduce的;而Spark SQL底层使用的是Spark RDD。
- 可以连接到多种数据源
Spark SQL提供了访问各种数据源的通用方法,数据源包括Hive、Avro、Parquet、ORC、JSON、JDBC等。
- 在现有的数据仓库上运行SQL或HiveQL查询
Spark SQL支持HiveQL语法以及Hive SerDes和UDF (用户自定义函数) ,允许访问现有的Hive仓库。
DataFrame和DataSet
- DataFrame的结构
DataFrame是Spark SQL提供的一个编程抽象,与RDD类似,也是一个分布式的数据集合。但与RDD不同的是,DataFrame的数据都被组织到有名字的列中,就像关系型数据库中的表一样。
DataFrame在RDD的基础上添加了数据描述信息(Schema,即元信息) ,因此看起来更像是一张数据库表。例如,在一个RDD中有3行数据,将该RDD转成DataFrame后,其中的数据可能如图所示:
-
DataSet的结构
Dataset是一个分布式数据集,是Spark 1.6中添加的一个新的API。相比于RDD, Dataset提供了强类型支持,在RDD的每行数据加了类型约束。
在Spark中,一个DataFrame代表的是一个元素类型为Row的Dataset,即DataFrame只是Dataset[Row]的一个类型别名。
Spark SQL的基本使用
Spark Shell启动时除了默认创建一个名为sc的SparkContext的实例外,还创建了一个名为spark的SparkSession实例,该spark变量可以在Spark Shell中直接使用。
SparkSession只是在SparkContext基础上的封装,应用程序的入口仍然是SparkContext。SparkSession允许用户通过它调用DataFrame和Dataset相关API来编写Spark程序,支持从不同的数据源加载数据,并把数据转换成DataFrame,然后使用SQL语句来操作DataFrame数据。
Spark SQL基本使用案例
在HDFS中有一个文件/input/person.txt,文件内容如下
Spark SQL基本使用案例![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/7dabe48afaaa4eae8fbabaea9fd00354.png)
现需要使用Spark SQL将该文件中的数据按照年龄降序排列,步骤如下:
进入spark-shell环境
- 加载数据为Dataset
val d1 = spark.read.textFile("hdfs://192.168.121.131:9000/input/person.txt")
d1.show() # 查看d1中的数据内容
从上述代码的结果可以看出,Dataset将文件中的每一行看作一个元素,并且所有元素组成了一列,列名默认为value。
- 给Dataset添加元数据信息
定义一个样例类Person,用于存放数据描述信息,代码如下:
case class Person(id:Int,name:String,age:Int)
注:Scala有一种特殊的类叫做样例类(case class)。默认情况下,样例类一般用于不可变对象(样例类构造参数默认声明为val)。
调用Dataset的map()算子将每一个元素拆分并存入Person类中,代码如下:
val personDataset = d1.map(line=>{
val fields = line.split(",")
val id = fields(0).toInt
val name = fields(1)
val age = fields(2).toInt
Person(id,name,age)
})
personDataset.show() # 查看personDataset中的数据内容
可以看到,personDataset中的数据类似于一张关系型数据库的表。
- 将Dataset转为DataFrame
Spark SQL查询的是DataFrame中的数据,因此需要将存有元数据信息的Dataset转为DataFrame。
调用Dataset的toDF()方法,将存有元数据的Dataset转为DataFrame,代码如下:
val pdf = personDataset.toDF()
- 执行SQL查询
在DataFrame上创建一个临时视图v_person,并使用SparkSession对象执行SQL查询,代码如下:
pdf.createTempView("v_person")
val result = spark.sql("select * from v_person order by age desc")
result.show()
Spark SQL函数
内置函数
Spark SQL内置了大量的函数,位于API org.apache.spark.sql.functions
中。其中大部分函数与Hive中的相同。
使用内置函数有两种方式:一种是通过编程的方式使用;另一种是在SQL
语句中使用。
- 以编程的方式使用lower()函数将用户姓名转为小写/大写,代码如下:
df.select(lower(col("name")).as("greet")).show()
df.select(upper(col("name")).as("greet")).show()df.select(lower(col("name")).as("greet")).show()
df.select(upper(col("name")).as("greet")).show()
上述代码中,df指的是DataFrame对象,使用select()方法传入需要查询的列,使用as()方法指定列的别名。代码col(“name”)指定要查询的列,也可以使用$"name"代替,代码如下:
df.select(lower($"name").as("greet")).show()
- 以SQL语句的方式使用lower()函数,代码如下:
df.createTempView("temp")
spark.sql("select upper(name) as greet from temp").show()
除了可以使用select()方法查询指定的列外,还可以直接使用filter()、groupBy()等方法对DataFrame数据进行过滤和分组,例如以下代码:
df.printSchema() # 打印Schema信息
df.select("name").show() # 查询name列
#查询name列和age列,其中将age列的值增加1
df.select($"name",$"age"+1).show()
df.filter($"age">25).show() # 查询age>25的所有数据
根据age进行分组,并求每一组的数量
df.groupBy("age").count().show()
自定义函数
当Spark SQL提供的内置函数不能满足查询需求时,用户可以根据需求编写自定义函数(User Defined Functions, UDF),然后在Spark SQL中调用。
例如有这样一个需求:为了保护用户的隐私,当查询数据的时候,需要将用户手机号的中间4位数字用星号()代替,比如手机号180***2688。这时就可以编写一个自定义函数来实现这个需求,实现代码如下:
package spark.demo.sql
import org.apache.spark.rdd.RDD
import org.apache.spark.sql.types.{StringType, StructField, StructType}
import org.apache.spark.sql.{Row, SparkSession}
/**
* 用户自定义函数,隐藏手机号中间4位
*/
object SparkSQLUDF {
def main(args: Array[String]): Unit = {
//创建或得到SparkSession
val spark = SparkSession.builder()
.appName("SparkSQLUDF")
.master("local[*]")
.getOrCreate()
//第一步:创建测试数据(或直接从文件中读取)
//模拟数据
val arr=Array("18001292080","13578698076","13890890876")
//将数组数据转为RDD
val rdd: RDD[String] = spark.sparkContext.parallelize(arr)
//将RDD[String]转为RDD[Row]
val rowRDD: RDD[Row] = rdd.map(line=>Row(line))
//定义数据的schema
val schema=StructType(
List{
StructField("phone",StringType,true)
}
)
//将RDD[Row]转为DataFrame
val df = spark.createDataFrame(rowRDD, schema)
//第二步:创建自定义函数(phoneHide)
val phoneUDF=(phone:String)=>{
var result = "手机号码错误!"
if (phone != null && (phone.length==11)) {
val sb = new StringBuffer
sb.append(phone.substring(0, 3))
sb.append("****")
sb.append(phone.substring(7))
result = sb.toString
}
result
}
//注册函数(第一个参数为函数名称,第二个参数为自定义的函数)
spark.udf.register("phoneHide",phoneUDF)
//第三步:调用自定义函数
df.createTempView("t_phone") //创建临时视图
spark.sql("select phoneHide(phone) as phone from t_phone").show()
// +-----------+
// | phone|
// +-----------+
// |180****2080|
// |135****8076|
// |138****0876|
// +-----------+
}
}
窗口(开窗)函数
开窗函数是为了既显示聚合前的数据,又显示聚合后的数据,即在每一行的最后一列添加聚合函数的结果。开窗口函数有以下功能:
- 同时具有分组和排序的功能
- 不减少原表的行数
- 开窗函数语法:
聚合类型开窗函数
sum()/count()/avg()/max()/min() OVER([PARTITION BY XXX] [ORDER BY XXX [DESC]])
排序类型开窗函数
ROW_NUMBER() OVER([PARTITION BY XXX] [ORDER BY XXX [DESC]])
- 以row_number()开窗函数为例:
开窗函数row_number()是Spark SQL中常用的一个窗口函数,使用该函数可以在查询结果中对每个分组的数据,按照其排列的顺序添加一列行号(从1开始),根据行号可以方便地对每一组数据取前N行(分组取TopN)。row_number()函数的使用格式如下:
row_number() over (partition by 列名 order by 列名 desc) 行号列别名
上述格式说明如下:
partition by:按照某一列进行分组;
order by:分组后按照某一列进行组内排序;
desc:降序,默认升序。
例如,统计每一个产品类别的销售额前3名,代码如下:
package spark.demo.sql
import org.apache.spark.sql.types._
import org.apache.spark.sql.{Row, SparkSession}
/**
* 统计每一个产品类别的销售额前3名(相当于分组求TOPN)
*/
object SparkSQLWindowFunctionDemo {
def main(args: Array[String]): Unit = {
//创建或得到SparkSession
val spark = SparkSession.builder()
.appName("SparkSQLWindowFunctionDemo")
.master("local[*]")
.getOrCreate()
//第一步:创建测试数据(字段:日期、产品类别、销售额)
val arr=Array(
"2019-06-01,A,500",
"2019-06-01,B,600",
"2019-06-01,C,550",
"2019-06-02,A,700",
"2019-06-02,B,800",
"2019-06-02,C,880",
"2019-06-03,A,790",
"2019-06-03,B,700",
"2019-06-03,C,980",
"2019-06-04,A,920",
"2019-06-04,B,990",
"2019-06-04,C,680"
)
//转为RDD[Row]
val rowRDD=spark.sparkContext
.makeRDD(arr)
.map(line=>Row(
line.split(",")(0),
line.split(",")(1),
line.split(",")(2).toInt
))
//构建DataFrame元数据
val structType=StructType(Array(
StructField("date",StringType,true),
StructField("type",StringType,true),
StructField("money",IntegerType,true)
))
//将RDD[Row]转为DataFrame
val df=spark.createDataFrame(rowRDD,structType)
//第二步:使用开窗函数取每一个类别的金额前3名
df.createTempView("t_sales") //创建临时视图
//执行SQL查询
spark.sql(
"select date,type,money,rank from " +
"(select date,type,money," +
"row_number() over (partition by type order by money desc) rank "+
"from t_sales) t " +
"where t.rank<=3"
).show()
}
}