sparksql用户自定义函数（UDF和聚合函数）

这段时间复习了spark，自己用spark sql 实现了udf和用户自定义函数。
废话少说，直接上代码：

DataFrame的用户自定义UDF函数
用户根据业务情况自定义的函数实现某些功能
//创建一个df
val df = spark.read.json(“in/user.json”)
//创建一个临时表
df.createOrReplaceTempView(“person”)
//创建一个udf函数名为addName 参数为String类型的x 功能为在x前面加一个Name:
spark.udf.register(“addName”,(x:String)=>“Name:”+x)
//使用udf函数
spark.sql(“select addName(name),age from person”).show

弱类型的DataFrame的用户自定义聚合函数
弱类型用户自定义聚合函数：通过继承UserDefinedAggregateFunction
来实现用户自定义聚合函数。
下面展示求平均年龄的自定义聚合函数：

package com.study.sql

import org.apache.spark.SparkConf
import org.apache.spark.sql.{Row, SparkSession}
import org.apache.spark.sql.expressions.{MutableAggregationBuffer, UserDefinedAggregateFunction}
import org.apache.spark.sql.types.{DataType, DoubleType, LongType, StructType}

object SparkSQL04_JuheFunc {
def main(args: Array[String]): Unit = {
//sparkconf
val sparkConf=new SparkConf().setMaster(“local[*]”).setAppName(“SparkSql”)
//构建sparksession
val spark = SparkSession.builder().config(sparkConf).getOrCreate()
//创建一个df
val df = spark.read.json(“in/user.json”)
df.createOrReplaceTempView(“user”)
//创建聚合函数对象
val udaf = new MyAgeAvg
//向spark注册此函数
spark.udf.register(“avgAge”,udaf)
//运用自定义聚合函数
spark.sql(“select avgAge(age) from user”).show
//释放资源
spark.stop()

}
}

//创建自定义聚合函数
class MyAgeAvg extends UserDefinedAggregateFunction{
//函数输入的参数的名字和类型
override def inputSchema: StructType = {
new StructType().add(“age”,LongType)
}
//缓冲区的结构 sum为年龄的总和 count为多少个人
override def bufferSchema: StructType = {
new StructType().add(“sum”,LongType).add(“count”,LongType)
}

//数据计算完毕之后的数据类型或者函数返回的数据类型
override def dataType: DataType = DoubleType
//函数是否稳定
override def deterministic: Boolean = true
//计算之前缓冲区的变量初始化
override def initialize(buffer: MutableAggregationBuffer): Unit = {
//buffer(0)对应sum buffer(1)对应count
buffer(0)=0L
buffer(1)=0L
}
//更新缓冲区
override def update(buffer: MutableAggregationBuffer, input: Row): Unit = {
//buffer.getLong(0) 取出上一次缓冲区中的sum值
buffer(0)=buffer.getLong(0)+input.getLong(0)
//count+1
buffer(1)=buffer.getLong(1)+1

}
//将多个缓冲区合并
override def merge(buffer1: MutableAggregationBuffer, buffer2: Row): Unit = {
//sum
buffer1(0)= buffer1.getLong(0)+buffer2.getLong(0)
//count
buffer1(1)= buffer1.getLong(1)+buffer2.getLong(1)
}
//最终的执行程序
override def evaluate(buffer: Row): Any = {
buffer.getLong(0).toDouble/buffer.getLong(1)
}
}

强类型的DataSet的用户自定义聚合函数
package com.study.sql

import org.apache.spark.SparkConf
import org.apache.spark.sql.expressions.Aggregator
import org.apache.spark.sql.{Encoder, Encoders, Row, SparkSession}

object SparkSQL05_DS_JuheFunc {
def main(args: Array[String]): Unit = {
//sparkconf
val sparkConf=new SparkConf().setMaster(“local[*]”).setAppName(“SparkSql”)
//构建sparksession
val spark = SparkSession.builder().config(sparkConf).getOrCreate()
import spark.implicits._
//创建一个df
val df = spark.read.json(“in/user.json”)

//创建聚合函数对象
val udaf = new MyAgeAvgClass
//将聚合函数转换为查询列
val avgCol = udaf.toColumn.name("avgAge")
//转化为DS
val userDS = df.as[UserBean]

//调用聚合函数
userDS.select(avgCol).show()
//释放资源
spark.stop()

}
}
//样例类
case class UserBean(name:String,age:BigInt)
case class AvgBuffer(var sum:BigInt, var count:BigInt)
//创建自定义聚合函数
class MyAgeAvgClass extends Aggregator[ UserBean, AvgBuffer, Double]{
//缓冲区初始化
override def zero: AvgBuffer = {
AvgBuffer(0,0)
}
//缓冲区的计算过程
override def reduce(b: AvgBuffer, a: UserBean): AvgBuffer = {
b.sum=b.sum+a.age
b.count=b.count+1
b
}
//缓冲区的合并
override def merge(b1: AvgBuffer, b2: AvgBuffer): AvgBuffer = {
b1.count=b1.count+b2.count
b1.sum=b1.sum+b2.sum
b1
}
//最终的计算
override def finish(reduction: AvgBuffer): Double = {
reduction.sum.toDouble/reduction.count.toDouble
}

//Encoder[AvgBuffer] 中括号里如果是自定义的就是Encoders.product 如果不是自定义的就找系统自带的类型
override def bufferEncoder: Encoder[AvgBuffer] = Encoders.product

override def outputEncoder: Encoder[Double] = Encoders.scalaDouble
}