SparkSQL的UDF函数和UDFA函数例子

UDF例子（多对多）

• 数据

alice   jogging,Coding,cooking  
lina    travel,dance            

• 需求
  第一个字段为str1,第二个字段为str2.
  统计第二个字段str2中爱好的个数

• 代码如下

package sparksql

import org.apache.spark.sql.SparkSession


object hobbies_UDF {
  def main(args: Array[String]): Unit = {


    val spark = SparkSession.builder()
      .appName("SalesApp")
      .master("local[2]")
      .getOrCreate()
    val arr = Array("alice jogging,Coding,cooking",
      "lina travel,dance")
    val hubrdd = spark.sparkContext.parallelize(arr)//创建RDD
    import spark.implicits._//隐式调用，转换toDF()
    hubrdd.map(_.split(" ")).map(x => Hub(x(0),x(1))).toDF().createOrReplaceTempView("hub")//转换为DataFrame
    spark.udf.register("number", (str: String) => str.split(",").length)//注册UDF函数，及书写UDF匿名函数
    spark.sql("select str1,number(str2) as num from hub").show()
    spark.stop()

  }
  case class Hub(str1: String, str2: String)
}

• 显示结果

+-----+---+
| str1|num|
+-----+---+
|alice|  3|
| lina|  2|
+-----+---+

UDFA例子（多对一）

• 数据

alice   ip1
lina    ip2
sven    ip3
alice   ip1
sven    ip2
alice   ip4

• 需求

第一个字段为name，第二个字段为ip
统计网站，ip的访问次数

• 代码如下

package sparksql

import org.apache.spark.sql.{Row, SparkSession}
import org.apache.spark.sql.expressions.{MutableAggregationBuffer, UserDefinedAggregateFunction}
import org.apache.spark.sql.types._

object log_UDF {
  def main(args: Array[String]): Unit = {
    val spark=SparkSession.builder().appName("log_UDF").master("local[2]").getOrCreate()
    val log=Array("alice ip1",
      "lina ip2",
      "sven ip3",
      "alice ip1",
      "sven ip2",
      "alice ip4")
    import spark.implicits._
    val logrdd=spark.sparkContext.parallelize(log)
    logrdd.map(_.split(" ")).map(x=>Log(x(0),x(1))).toDF().createOrReplaceTempView("logtable")
    spark.udf.register("logcount",new Logcount)//UDFA函数的注册
    spark.sql("select name,logcount(ip) from logtable group by name").show()






  }
  case class Log(name:String,ip:String)
}
class Logcount extends UserDefinedAggregateFunction{//通过实现接口的方式来实现UDFA函数

 /*
 * 指定输入数据类型
 * */
  override def inputSchema: StructType =StructType(Array(StructField("input",StringType,true)))

  override def bufferSchema: StructType = StructType(Array(StructField("count",IntegerType,true)))

  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 =
  {
    buffer(0)=buffer.getAs[Int](0)+1
  }

  override def merge(buffer1: MutableAggregationBuffer, buffer2: Row): Unit = {

    buffer1(0)=buffer1.getAs[Int](0)+buffer2.getAs[Int](0)
  }

  override def evaluate(buffer: Row): Any = buffer.getAs[Int](0)
}

• 结果如下

+-----+------------+
| name|logcount(ip)|
+-----+------------+
| lina|           1|
|alice|           3|
| sven|           2|
+-----+------------+