SparkSQL -- 02 【SparkSQL文件的读取与落地，和Hive的整合，内置函数，自定义函数】

xiaoxiao______

已于 2022-11-10 16:21:27 修改

阅读量319

点赞数

分类专栏： Spark 文章标签： SparkSQL

于 2020-11-25 21:41:11 首次发布

本文链接：https://blog.csdn.net/xiaoxiao______/article/details/110144826

版权

Spark 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

文章目录

一、SparkSQL文件的读取与落地
- 1.1、文件读取
- 1.2、文件的落地
二、和Hive的整合
三、内置函数
四、用户自定义函数

一、SparkSQL文件的读取与落地

1.1、文件读取

package com.xxx.SparkSQL.Day02

import java.util.Properties

import org.apache.spark.sql.{DataFrame, SparkSession}

object _01SparkFileLoad {
    def main(args: Array[String]): Unit = {
        val spark: SparkSession = SparkSession.builder().master("local").appName("load").getOrCreate()
        val df: DataFrame = spark.read.json("data/emp.json")
        import spark.implicits._

        val df1: DataFrame = spark.read.format("json").load("data/emp.json")
        //用option方法指定配置，下面指定分隔符
        val df2: DataFrame = spark.read.format("csv").option("sep", ",").load("data/student.csv")
        //下面指定第一行作为表头
        val df3: DataFrame = spark.read.format("csv").option("header", true).load("data/ip-pprovince-count.csv")
        
        val df4: DataFrame = spark.read.option("header","true").option("sep",";").csv("data/ip-pprovince-count.csv")

        //连接mysql
        val pro = new Properties()
        pro.put("user","root")
        pro.put("password","000000")

        spark.read.jdbc("jdbc:mysql://localhost:3306/xiao","class",pro).show()

        spark.stop()
    }
}

1.2、文件的落地

package com.xxx.SparkSQL.Day02

import java.util.Properties

import org.apache.spark.rdd.RDD
import org.apache.spark.sql.{DataFrame, SaveMode, SparkSession}

object _02SparkFIleSave {
    def main(args: Array[String]): Unit = {
        val spark: SparkSession = SparkSession.builder().master("local").appName("save").getOrCreate()

        val rdd1: RDD[String] = spark.sparkContext.textFile("data/emp.json")
        //也可以读rdd
        val df: DataFrame = spark.read.json(rdd1)

        /**
         * 保存
         * 默认保存格式是parquet,还可以是json，csv,orc,text
         * 注意：在保存为text时，只能是单列的，并且为String类型
         */

        df.write.csv("out2")

        //读mysql中的数据
        val pro = new Properties()
        pro.put("user","root")
        pro.put("password","000000")
        //设置参数
        df.write.mode(SaveMode.Overwrite).jdbc("jdbc:mysql://localhost:3306/database","emp222",pro)

        spark.stop()
    }

}

二、和Hive的整合

和hive整合要在IDEA中的resource中导入三个包

hadoop的 core-site.xml hdfs-site.xml
hive的 hive-site.xml

SparkSQL和Hive的整合实际上只是从hive中拉取表，（连接memstore：元数据），再到hdfs中拉取数据到内存中，用Spark进行计算，执行引擎是spark，而不是mr。

package com.xxx.SparkSQL.Day02

import org.apache.spark.sql.{DataFrame, SaveMode, SparkSession}

object _03SparkSqlToHive {
    def main(args: Array[String]): Unit = {
        //记得开启hive支持
        val spark: SparkSession = SparkSession.builder().master("local").appName("tohive").enableHiveSupport().getOrCreate()

        //读取hive中的表
        val df: DataFrame = spark.table("myhive.emp")

        //使用DSL风格查询每个部门的平均工资，最高工资，最低工资，总人数
        df.groupBy("deptno").avg("sal").show() //平均工资
        df.groupBy("deptno").max("sal").show() //最高工资
        df.groupBy("deptno").min("sal").show() //最低工资
        df.groupBy("deptno").count().show() //总人数

        //另一种写法
        df.groupBy("deptno").agg("max" -> "sal", "min" -> "sal", "avg" -> "sal").show()


        //使用sql风格查询
        //首先维护一张表
        df.createTempView("emp")
        spark.sql(
            """
              |select
              |deptno,
              |avg(nvl(sal,0)),
              |max(sal),
              |min(sal),
              |count(1)
              |from emp
              |groupBy deptno
              |""".stripMargin).show()


        /**
         * 下面是将DataFrame保存到hive中
         * saveAsTable(tableName:String), 可以将数据作为一张表保存到hive中。
         * insertInto(tableName:String)  向表中插入数据， 表不存在会抛异常
         * mode(..).insertInto(...)   :默认的模式就是追加
         * SaveMode.Append
         * SaveMode.ErrorIfExists       不生效
         * SaveMode.Overwrite           覆盖，重写，生效
         * SaveMode.Ignore              忽略不生效，但是是追加。
         */

        //此方法会自动创建表
        df.write.saveAsTable("myhive.emp111")
        //可以设置保存模式,不写默认为追加，表要提前存在
        df.write.mode(SaveMode.Overwrite).insertInto("myhive.emp222")

        spark.stop()
    }
}

三、内置函数

注意： 使用内置函数时要导包

package com.xxx.SparkSQL.Day02

import org.apache.spark.sql.{DataFrame, SparkSession}

object _04SparkInnerFunctionDemo {
    def main(args: Array[String]): Unit = {
        val spark = SparkSession.builder().master("local").appName("function").getOrCreate()
        val df: DataFrame = spark.read.json("file:///E:\\IDEAproject\\SparkSQL\\data\\emp.json")
        import spark.implicits._

        //计算每个部门的最高工资，最高奖金，总人数
        df.groupBy("deptno","job").agg("sal"->"max","comm"->"max","empno"->"count").show()

        //使用内置函数，需要导包
        import org.apache.spark.sql.functions._
        df.groupBy("deptno","job").agg(max("sal"),
            min("sal"),avg("sal"),
            count("sal"),
            countDistinct("sal")).show()
    }
}

四、用户自定义函数

UDF：用户自定义函数（一对一）
UDAF：用户自定义聚合函数（多对一）
UDTF：用户自定义表生成函数（一对多）

4.1、UDF

案例1、

package com.xxx.SparkSQL.Day02

import org.apache.spark.sql.{DataFrame, SparkSession}

/**
 * 用户自定义函数：
 * 1、定义一个函数
 * 2、注册函数：
 *              register（函数名：String,函数：Function）
 */
object _05UserDefineFunction {
    def main(args: Array[String]): Unit = {
        val spark = SparkSession.builder().master("local").appName("function").getOrCreate()
        val df: DataFrame = spark.read.json("file:///E:\\IDEAproject\\SparkSQL\\data\\emp.json")
        import spark.implicits._

        //定义一个函数
        val getLength=(str:String)=>str.length

        //维护一个临时表
        df.createTempView("emp")

        //注册函数
        spark.udf.register("getlength",getLength)
        spark.sql(
            """
              |
              |""".stripMargin)

        spark.stop()
    }
}

案例2、

package com.xxx.SparkSQL.Day02

import org.apache.spark.sql.{DataFrame, SparkSession}

object _06UserDefineFunction {
    def main(args: Array[String]): Unit = {
        val spark = SparkSession.builder().master("local").appName("function").getOrCreate()
        val df: DataFrame = spark.read.json("file:///E:\\IDEAproject\\SparkSQL\\data\\emp.json")
        import spark.implicits._

        //
        df.createTempView("emp")

//        spark.sql(
//            """
//              |select
//              |ename,
//              |job,
//              |sal,
//              |case when sal>3000 then 'level1'
//              |when sal>1500 then 'level2'
//              |else 'level3' end as level
//              |from
//              |emp
//              |""".stripMargin).show()

        //注册
        spark.udf.register("getLevel",getLevel _)
        spark.sql(
            """
              |select
              |job,
              |sal,
              |getLevel(sal)
              |from
              |emp
              |""".stripMargin).show()
        spark.stop()
    }

    //自定义函数
    def getLevel(sal:Int): String ={
        if (sal > 3000) "level1" else if (sal > 1500) "level2" else "level3"
    }
}

4.2、UDAF

package com.qf.sql.day02

import org.apache.spark.rdd.RDD
import org.apache.spark.sql.expressions.{MutableAggregationBuffer, UserDefinedAggregateFunction}
import org.apache.spark.sql.types.{DataType, DataTypes, StructField, StructType}
import org.apache.spark.sql.{Dataset, Row, SparkSession}

/**
 * 用户自定义分析函数UDAF案例演示:
 *    自定义一个求平均值的函数：   多对一。
 *
 * @param id
 * @param name
 * @param score
 */

case class Score(id:Int,name:String,score:Double)
object Test04 {
    def main(args: Array[String]): Unit = {
        val spark = SparkSession.builder().appName("udaf").master("local").getOrCreate()
        val list = List(
            Score(1,"张三",99.9),
            Score(2,"李四",88.9),
            Score(3,"小明",77.9),
            Score(1,"张三",91.9),
            Score(2,"李四",81.9),
            Score(3,"小明",71.9)
        )

        import spark.implicits._
        //scala的对象可以直接转DS或者是DF， 当然需要导入隐式转换
        val ds: Dataset[Score] = list.toDS()
        /*维护一张表*/
        ds.createTempView("tmp")
        //注册函数：
        spark.udf.register("myavg",new MyAvgUDAF)

        val sql = "select avg(score), myavg(score),name from tmp group by name"
        spark.sql(sql).show()
    }
}

/**
 * 1:需要继承 UserDefinedAggregateFunction
 * 2:重写方法
 */
class  MyAvgUDAF extends  UserDefinedAggregateFunction{
    /**
     *  指定用户自定义udaf输入参数的元数据
     * @return
     */
    override def inputSchema: StructType = {
        StructType(Array(StructField("score",DataTypes.DoubleType)))
    }

    /**
     * udaf自定义函数求解过程中的临时变量的数据类型
     *
     * 因为要求平均值，正常逻辑是求出总和以及个数，然后做除法运算，因此要有两个临时变量
     * @return
     */
    override def bufferSchema: StructType = {
        StructType(Array(
            StructField("sum",DataTypes.DoubleType),
            StructField("count",DataTypes.IntegerType)
        ))
    }

    /**
     * udaf返回值的数据类型
     * @return
     */
    override def dataType: DataType = DataTypes.DoubleType

    override def deterministic: Boolean = true

    /**
     * 临时变量是存储在buffer中，我们定义的buffer有两个元素，第一元素是sum，第二个元素是count
     * @param buffer
     */
    override def initialize(buffer: MutableAggregationBuffer): Unit = {
        buffer.update(0,0d) //设置第一个元素的的初始值为0d
        buffer.update(1,0) // 设置第二个元素的初始值为0
    }

    /**
     * 分区内的局部累加操作
     * @param buffer
     * @param input
     */
    override def update(buffer: MutableAggregationBuffer, input: Row): Unit = {
        val score: Double = input.getAs[Double](0)
        buffer.update(0,buffer.getDouble(0)+score) //将新进来的行中的分数累加到第一个元素sum上
        buffer.update(1,buffer.getInt(1)+1)        //将第二个元素count累加一个1
    }

    /**
     * 分区间的累加操作
     * @param buffer1
     * @param buffer2
     */
    override def merge(buffer1: MutableAggregationBuffer, buffer2: Row): Unit = {
        buffer1.update(0,buffer1.getDouble(0)+buffer2.getDouble(0))
        buffer1.update(1,buffer1.getInt(1)+buffer2.getInt(1))
    }

    /**
     * 用于计算输出结果
     * @param buffer
     * @return
     */
    override def evaluate(buffer: Row): Any = buffer.getDouble(0)/buffer.getInt(1)
}