SparkSQL -- 02 【SparkSQL檔案的讀取與落地，和Hive的整合，內建函式，自定義函式】

xiaoxiao______發表於2020-11-25
SparkSQLHive函式

文章目錄

一、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")

        //簡寫，也可以使用option指定配置
        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

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)
}

相關文章