Spark SQL用户自定义函数UDF及Apache日志实例

临时UDF

创建临时UDF，在SQL中使用：
（注：涉及文档上一篇博文《Spark SQL基础笔记及简单案例》给出》

//创建DataFrame
case class Student(id: String, name : String, age: Int)
val rdd=sc.textFile("hdfs://node01:9000/sqldata/students.txt").map(_.split(",")).map(p => Student(p(0), p(1), p(2).trim.toInt))
val students = rdd.toDF()

//定义函数，将表中三个列合并为一个列，使用','分割
def allInOne(seq: Seq[Any], sep: String): String = seq.mkString(sep)

def allInOne1(s1:String, s2:String,s3:Int): String = s1+s2+s3.toString

//注册函数allInOne
sqlContext.udf.register("allInOne", allInOne _)
sqlContext.udf.register("allInOne1", allInOne1 _)
//或者直接使用匿名函数做参数
sqlContext.udf.register( "allInOne", (seq: Seq[Any], sep: String)=>seq.mkString(sep)  )

//使用函数allInOne：在DataFrame的接口中使用
import org.apache.spark.sql.functions.{udf,array,lit}
val myFunc = udf(allInOne _)
val cols = array("id","name","age")
val sep = lit(",")

students.select(myFunc(cols,sep).alias("col")).show()

//使用函数allInOne：在SQL中使用

students.registerTempTable("tb_students")
sqlContext.sql("""select allInOne1(id,name,age) as col from tb_students""").show()  //myFunc适用于DataFrame中的操作


//在DataFrame中添加一个新列bonus，表示奖金，若年龄>=20，则奖金数为一个实数(age*10+2.5)
//否则奖金为(age*8+1.5),编写一个UDF实现对奖金的计算。

case class Student(id: String, name : String, age: Int)
val rdd=sc.textFile("hdfs://cloud01:9000/sqldata/students.txt").map(_.split(",")).map(p => Student(p(0), p(1), p(2).trim.toInt))
val students = rdd.toDF()

def setBonus(age: Int): Double = if(age.toInt>=20) (age.toDouble)*10.0+2.5 else (age.toDouble)*8.0+1.5

sqlContext.udf.register("setBonus", setBonus _)
import org.apache.spark.sql.functions.{udf,array,lit}
val myFunc = udf(setBonus _)

val s1=students.withColumn("bonus",myFunc($"age"))
val s2=s1.select("age","bonus")
s2.show

---

基于Apache日志的SparkSQL查询

链接: https://pan.baidu.com/s/1nveIp4H 密码: b5v2

先看看数据结构：

1. 199.72.81.55是向服务器发出请求的客户端（远程主机）IP地址（或主机名，如果可用）
2. 输出中的第一个-表示所请求的信息（来自远程机器的用户身份）不可用
3. 输出中的第二个-表示所请求的信息（来自本地登录的用户身份）不可用
4. 格式为：[日/月/年：小时：分：秒 时区]
5. 请求方法（例如，GET，POST等）
6. 端点（统一资源标识符，请求的数据）
7. 客户端协议版本（’HTTP / 1.0’）
8. 200这是服务器返回客户端的状态代码。这些信息非常有价值：成功回复（从2开始的代码），重定向（从3开始的代码），客户端导致的错误（以4开头的代码），服务器错误（代码从5开始）
9. 最后一个条目表示返回给客户端的对象大小。如果没有返回任何内容则是-或0

package sparksql

import org.apache.log4j.{Level, Logger}
import org.apache.spark.rdd.RDD
import org.apache.spark.sql.{DataFrame, SQLContext}
import org.apache.spark.{SparkConf, SparkContext}
import org.apache.spark.sql.functions.{udf,array,lit}

case class ApacheLog(
                      host: String,
                      user: String,
                      password: String,
                      timestamp: String,
                      method: String,
                      endpoint: String,
                      protocol: String,
                      code: Integer,
                      size: Integer
                      )

object SQL_ApacheLog{

  def main(args:Array[String]) {

    Logger.getLogger("org.apache.spark").setLevel(Level.WARN)
    Logger.getLogger("org.eclipse.jetty.server").setLevel(Level.OFF)

    //flatMap将parse_logline函数应用于rawData的每一行，并将所有结果收集到apacheLogs中，丢弃所有不可解析的日志行（所有结果的形式None）

    def parseLogLine(line: String): ApacheLog = {
      //正则表达式包含9个()对应9个捕获组，用于表示ApacheLog类的字段
      val apache_pattern = """^(\S+) (\S+) (\S+) \[([\w:/]+\s[+\-]\d{4})\] "(\S+) (\S+)\s*(\S*)" (\d{3}) (\S+)""".r
      line match {
        case apache_pattern(a, b, c, t, f, g, h, x, y) =>{   //apache_pattern.unapply(line)
          val size = if (y == "-") 0 else y.toInt
         ApacheLog(a, b, c, t, f, g, h, x.toInt, size)}

      }
    }
    //因为是截取了一部分数据集，这里本地试运行
    val conf = new SparkConf().setAppName("SparkSQL_ApacheLog").setMaster("local[2]") //2表示核数
    val sc = new SparkContext(conf)
    val sqlContext = new SQLContext(sc)
    sqlContext.setConf("spark.sql.shuffle.partitions", "20")

    //读取数据，创建RDD
    val rawData = sc.textFile("hdfs://node01:9000/sqldata/apachelog")

    //创建DF
    import sqlContext.implicits._  //否则无法使用后面的toDF方法

    val apacheLogsRDD:RDD[ApacheLog]= rawData.map(x=>parseLogLine(x)).cache

    val apacheLogsDF=apacheLogsRDD.toDF()

    //使用DataFrame中操作进行查询
    apacheLogsDF.filter($"code" === 404).groupBy($"endpoint").count().orderBy($"count".desc).limit(10).show

    //使用SQL语句进行查询
    apacheLogsDF.registerTempTable("apacheLogs")
    sqlContext.sql("select endpoint, count(*) as cou from apacheLogs where code =404 group by endpoint order by cou desc limit 10").show



def extractTLD(host:String):String={
        host.substring(host.lastIndexOf('.')+1)
}


sqlContext.udf.register("extractTLD", extractTLD _)


val myFunc = udf(extractTLD _)
apacheLogsDF.filter(myFunc($"host")==="net")


sqlContext.sql("select extractTLD(host) from apacheLogs")

  }

}