Spark复习 Day03：SparkSQL

Spark复习 Day03：SparkSQL

1. 什么是SparkSQL
-----------------------------------------------
    - SparkSQL是Spark用来处理结构化[表]数据的一个模块。
    - 它提供了两个编程抽象：DataFrame和DataSet，底层还是RDD操作


2. DataFrame、DataSet 介绍
------------------------------------------------
    - DataFrame
        1. 与RDD类似，DataFrame也是一个分布式数据容器
        2. 不同的是，DataFrame更像是传统数据库的二维表格
        3. 除了记录了数据以外，还记录了数据的结构信息，即Schema
        4. 与Hive一样，DataFrame也支持嵌套数据类型[struct,array,map]
        5. DataFrame的API 比 RDD的API更加好用
        6. DataFrame是为数据提供了Schema的视图，可以把它当做数据库的一张表来对待

    - DataSet
        1. Dataset是DataFrameAPI的一个拓展，是Spark最新的数据抽象。DataFrame的升级版
        2. 用户友好的API风格，既有类型的安全检查，収DataFrame的查询优化特性
        3. DataSet支持编解码器，当需要访问非堆上的数据时，可以避免反序列化整个对象，提高了效率
        4. 样例类用来在DataSet中定义数据的结构信息Schema. 样例类的每个属性的名称直接映射到DataSet的字段名称
        5. DataFrame 是DataSet的特例
        6. DataFrame = DataSet[Row]
        7. 可以通过as方法将 DataFrame转换成DataSet
        8. Row 是一个Spark的类型，就和Car,Person一样。所有的表结构信息都用Row来表示
        9. DataSet是强类型的，必须指定类型。比如DataSet[Row],DataSet[Car]

    - DataFrame 把RDD数据当成表用，而DataSet把数据当成类用，当成属性的集合用


3. SparkSession
-----------------------------------------------
    - 作用等同于RDD的SparkContext
    - SQLContext + HiveContext
    - SparkSession 是创建DataFrame 和 执行SparkSQL的入口


4. 创建DataFrame的三种方式
--------------------------------------------------
    - 通过Spark数据源进行创建
        /**
           * 测试SparkSession
           */
         @Test
         def testSparkSession(): Unit = {
           val spark = SparkSession.builder().master("local").appName("spark").getOrCreate()
           val read: DataFrameReader = spark.read
           val frame: DataFrame = read.json("d:/Test/1.json")
           frame.show()
           // +---+----+
           //|age|name|
           //+---+----+
           //|  1|tom1|
           //|  2|tom2|
           //|  3|tom3|
           //|  4|tom4|
           //+---+----+
           // 创建一个临时表student -- 只读的，只能查不能改
           // frame.createTempView("student")
           // 创建一个全局的临时表，不仅限于当前会话。注意使用的时候要加上global_temp.
           frame.createGlobalTempView("student")
           // 使用sql查询临时表
           val res1: DataFrame = spark.sql("select avg(age) from global_temp.student")
           res1.show()
           //+--------+
           //|avg(age)|
           //+--------+
           //|     2.5|
           //+--------+
         }

    - 通过RDD进行创建
        1. 如果想RDD与DF或者DS之间相互转换，需要引入 import spark.implicits._
        注意，此处的spark不是包名，而是你的SparkSession对象
        2. 本质上 RDD + Schema = DataFrame
        3. Schema 可以在转换DF时手动指定，也可以通过转换成样例类的RDD进行DF转换操作
        4. 例
         /**
            * RDDtoDF
            */
          @Test
          def RDDtoDF(): Unit ={
            val sparkConf = new SparkConf().setMaster("local").setAppName("sc")
            val sc = new SparkContext(sparkConf)
            val rdd = sc.makeRDD(List(("tom1",1),("tom2",2),("tom3",3)))
            val spark = SparkSession.builder().master("local").appName("spark").getOrCreate()
            import spark.implicits._
            val frame: DataFrame = rdd.toDF("name","age")
            frame.show()
            //+----+---+
            //|name|age|
            //+----+---+
            //|tom1|  1|
            //|tom2|  2|
            //|tom3|  3|
            //+----+---+
          }
            @Test
            def CaseCalss2RDD(): Unit ={
              val sparkConf = new SparkConf().setMaster("local").setAppName("sc")
              val sc = new SparkContext(sparkConf)
              val rdd = sc.makeRDD(List(("tom1",1),("tom2",2),("tom3",3)))
              val spark = SparkSession.builder().master("local").appName("spark").getOrCreate()
              import spark.implicits._
              val rdd2: RDD[Student] = rdd.map(x => Student(x._1,x._2))
              val frame1: DataFrame = rdd2.toDF()
              frame1.show()
              //+----+---+
              //|name|age|
              //+----+---+
              //|tom1|  1|
              //|tom2|  2|
              //|tom3|  3|
              //+----+---+
            }
            case class Student(name:String,age:Int) extends java.io.Serializable

    - 通过HiveTable进行查询返回


5. 创建 DataSet
------------------------------------
    - DataSet是具有强类型的数据集合，需要提供对应的类型信息,通过面向对象的方式去访问数据
    - 创建
      1. 创建一个样例类
           case class Student(name:String,age:Int) extends java.io.Serializable
           @Test
           def testDataSet(): Unit ={
             val spark = SparkSession.builder().master("local").appName("spark").getOrCreate()
             import spark.implicits._
             val dataset: Dataset[Student] = List(Student("tom",1),Student("tom2",1)).toDS()
             dataset.show()
             //+----+---+
             //|name|age|
             //+----+---+
             //| tom|  1|
             //|tom2|  1|
             //+----+---+
           }


6. DSL风格的语法
-----------------------------
    - df.printSchema
    - df.select("name").show()
    - df.select($"name", $"age" + 1).show()
    - df.filter($"age" > 5).show()
    - df.groupBy("age").count().show()


7. RDD、DataFrame、DataSet的关系以及相互转换
--------------------------------------------
    - RDD、DataFrame、DataSet的关系
        1. RDD(spark1.0) --> DataFrame(spark1.3) --> DataSet(Spark1.6)
        2. 后期DataSet 会逐渐取代 DataFrame和RDD

    - RDD、DataFrame、DataSet的相互转换
        1. 引入import spark.implicits._
        2. RDD to DataFrame
            val rdd = sc.makeRDD(List(("tom1",1),("tom2",2),("tom3",3)))
            val frame: DataFrame = rdd.toDF("name","age")

        3. RDD to DataSet
            val rdd = sc.makeRDD(List(("tom1",1),("tom2",2),("tom3",3)))
            val ds: Dataset[Student] = rdd.map(x => Student(x._1,x._2)).toDS()

        4. DataFrame to RDD
            val rdd = sc.makeRDD(List(("tom1",1),("tom2",2),("tom3",3)))
            val frame: DataFrame = rdd.toDF("name","age")
            val rdd2: RDD[Row] = frame.rdd

        5. DataFrame to DataSet
            val rdd = sc.makeRDD(List(("tom1",1),("tom2",2),("tom3",3)))
            val frame: DataFrame = rdd.toDF("name","age")
            val ds: Dataset[Student] = frame.as[Student]

        6. DataSet to RDD
            val rdd = sc.makeRDD(List(("tom1",1),("tom2",2),("tom3",3)))
            val ds: Dataset[Student] = rdd.map(x => Student(x._1,x._2)).toDS()
            val rdd2: RDD[Student] = ds.rdd

        7. DataSet to DataFrame
            val rdd = sc.makeRDD(List(("tom1",1),("tom2",2),("tom3",3)))
            val ds: Dataset[Student] = rdd.map(x => Student(x._1,x._2)).toDS()
            val frame: DataFrame = ds.toDF()


8. 用户自定义函数
---------------------------------------
    - 自定义普通函数UDF
       @Test
        def testUDF(): Unit ={
          val conf = new SparkConf().setMaster("local").setAppName("sc")
          val spark = SparkSession.builder().config(conf).getOrCreate()
          val frame: DataFrame = spark.read.json("d:/Test/1.json")
          frame.createTempView("student")
          // 自定义UDF
          val func = (x:Int) => x + 10
          spark.udf.register("add_10", func)
          val frame1: DataFrame = spark.sql("select *, add_10(age) as nage from student")
          frame1.show()
        }

    - 自定义聚合函数UDAF - 弱类型,不是特别的规定类型以及对应
        // 声明自定义聚合函数 -- 求年龄的平均值
        class AvgUDAF extends UserDefinedAggregateFunction{

          // 定义输入数据的结构 - 输入的年龄
          override def inputSchema: StructType = {
            val age = StructField("age", IntegerType)
            StructType(Array(age))
          }

          // 缓冲区 - 做计算的数据的结构 - 计算平均值，需要年龄总和以及人数
          override def bufferSchema: StructType = {
            val sum = StructField("sum", IntegerType)
            val count = StructField("count", IntegerType)
            StructType(Array(sum,count))
          }

          // 数据计算完毕返回的数据类型
          override def dataType: DataType = {
            FloatType
          }

          // 稳定性 -- 相同值的输入，是否返回相同的输出
          override def deterministic: Boolean = {
            true
          }

          // 计算之前，计算缓冲区的初始化 -- 你的sum,count初始是什么值
          // 参数buffer是一个数组，数组索引对应bufferSchema中定义的结构
          // 注意，没有名称，只能通过索引去取
          override def initialize(buffer: MutableAggregationBuffer): Unit = {
            // sum
            buffer(0) = 0
            // count
            buffer(1) = 0
          }

          // 传入每一条数据，计算，更新自己的缓冲区
          // 参数buffer 表示自己的缓冲区
          // input 表示输入的数据 -- inputSchema 中定义的 -- 此处为单字段的age
          override def update(buffer: MutableAggregationBuffer, input: Row): Unit = {
            // sum + age
            buffer(0) = buffer.getInt(0) + input.getInt(0)
            // count + 1
            buffer(1) = buffer.getInt(1) + 1
          }

          // 将多个Executor的缓冲区数据进行合并
          // buffer1 代表当前缓冲区，buffer2 代表合并过来的缓冲区
          override def merge(buffer1: MutableAggregationBuffer, buffer2: Row): Unit = {
            // sum 合并
            buffer1(0) = buffer1.getInt(0) + buffer2.getInt(0)
            // count 合并
            buffer1(1) = buffer1.getInt(1) + buffer2.getInt(1)
          }

          // 计算缓冲区中的数值，得出最终的结果
          // 参数为自己的缓冲区 sum count
          override def evaluate(buffer: Row): Any = {
            val sum = buffer.getInt(0)
            val count = buffer.getInt(1).toFloat
            sum / count
          }
        }
        -------------------
          @Test
          def testUDAF(): Unit ={
            val conf = new SparkConf().setMaster("local").setAppName("sc")
            val spark = SparkSession.builder().config(conf).getOrCreate()
            val frame: DataFrame = spark.read.json("d:/Test/1.json")
            frame.createTempView("student")
            frame.show()
            val udaf = new AvgUDAF()
            // 注册聚合函数
            spark.udf.register("avgAge",udaf)
            val frame1: DataFrame = spark.sql("select avgAge(age) as avg from student")
            frame1.show()
          }

    - 自定义聚合函数UDAF - 强类型,必须规定类型以及对应，不容易出错，记混参数的位置和类型
        case class Student(name:String,age:Int) extends java.io.Serializable
        case class AvgBuffer(sum:Int,count:Int) extends java.io.Serializable

        class AvgUDAF_2 extends Aggregator[Student,AvgBuffer,Float]{
          // 初始化缓冲区
          override def zero: AvgBuffer = {
            AvgBuffer(0,0)
          }
          // 聚合逻辑
          override def reduce(b: AvgBuffer, a: Student): AvgBuffer = {
            val sum = b.sum + a.age
            val count = b.count + 1
            AvgBuffer(sum,count)
          }
          // 缓冲区合并逻辑
          override def merge(b1: AvgBuffer, b2: AvgBuffer): AvgBuffer = {
            val sum = b1.sum + b2.sum
            val count = b1.count + b2.count
            AvgBuffer(sum,count)
          }
          // 最终计算逻辑
          override def finish(reduction: AvgBuffer): Float = {
            reduction.sum.toFloat / reduction.count.toFloat
          }
          // 定义缓冲区的编解码器，自定义的用Encoders.product
          override def bufferEncoder: Encoder[AvgBuffer] = {
            Encoders.product[AvgBuffer]
          }
          // 定义输出类型的编解码器，常见类型直接用Encoders.scalaFloat等
          override def outputEncoder: Encoder[Float] = {
            Encoders.scalaFloat
          }
        }
        -------------------------------
         @Test
          def testUDAF(): Unit ={
            val conf = new SparkConf().setMaster("local").setAppName("sc")
            val spark = SparkSession.builder().config(conf).getOrCreate()
            import spark.implicits._
            val frame: DataFrame = spark.read.json("d:/Test/1.json")
            frame.show()
            frame.createTempView("student")
            val ds: Dataset[Student] = frame.as[Student]

            // 强类型的UDAF不能直接通过注册，得转换成列, 然后通过DSL风格去查询
            val udaf = new AvgUDAF_2()
            val avgColumn: TypedColumn[Student, Double] = udaf.toColumn.name("avgAge")
            val res: Dataset[Double] = ds.select(avgColumn)
            res.show()
          }


9. SparkSQL 读取和保存
-------------------------------------
    - READ
        1. load函数：默认为Parquet格式文件，一种面向列存储的数据格式
        2. 使用 spark.read.load("path") 这种方式只能读取Parquet格式文件，不能读取其他的文件格式
        3. 如果想使用load去读取其他格式，spark.read.format("json").load("jsonPath")
        4.  @Test
            def sparkSQLLoad(): Unit ={
              val conf = new SparkConf().setMaster("local").setAppName("sc")
              val spark = SparkSession.builder().config(conf).getOrCreate()
              import spark.implicits._
              val frame: DataFrame = spark.read.load("D:\\MyProgram\\spark\\examples\\src\\main\\resources\\users.parquet")
              frame.show()
              //+------+--------------+----------------+
              //|  name|favorite_color|favorite_numbers|
              //+------+--------------+----------------+
              //|Alyssa|          null|  [3, 9, 15, 20]|
              //|   Ben|           red|              []|
              //+------+--------------+----------------+
            }
        5. 读取JDBC[Spark lib下要有mysql的连接jar包]
            @Test
            def readJDBC(): Unit ={
              val conf = new SparkConf().setMaster("local").setAppName("sc")
              val spark = SparkSession.builder().config(conf).getOrCreate()
              import spark.implicits._
              val prop = new Properties()
              prop.put("user","root")
              prop.put("password","root")
              prop.put("driver","com.mysql.jdbc.Driver")
              val frame: DataFrame = spark.read.jdbc(
                url = "jdbc:mysql://localhost:3306/test",
                table = "student",
                prop
              )
              frame.show()
            }

    - WRITE
        1. spark.write.save() 默认保存Parquet格式文件
        2. 其他格式：
             - frame.write.format("json").save("d:/Test/out.json")
             - frame.write.json("d:/Test/out1.json")
        3. 保存时可选模式,默认是error,存在就报错
            frame.write.mode(SaveMode.Overwrite).json("d:/Test/out1.json")
        4. 保存jdbc [Spark lib下要有mysql的连接jar包]
            val prop = new Properties()
            prop.put("user","root")
            prop.put("password","root")
            prop.put("driver","com.mysql.jdbc.Driver")
            frame.write.mode(SaveMode.Append).jdbc(
              url = "jdbc:mysql://localhost:3306/test",
              table = "student" ,
              prop
            )


10. SparkSQL 连接Hive
-------------------------------------------
    - 首先Spark内部是集成有hive的，可以直接操作内置的hive, 也可以操作外部的Hive
        1. 添加pom依赖
            <!-- spark hive -->
            <dependency>
                <groupId>org.apache.spark</groupId>
                <artifactId>spark-hive_${scala.version}</artifactId>
                <version>${spark.version}</version>
            </dependency>
            <dependency>
                <groupId>org.apache.hive</groupId>
                <artifactId>hive-exec</artifactId>
                <version>${hive.version}</version>
            </dependency>

        2. Spark操作Hive[注意：将hive-site.xml放入spark的classpath下或者自己的resources下]
            @Test
            def TestReadHive(): Unit ={
              // 创建
              val conf = new SparkConf().setMaster("local").setAppName("Hive On Spark")
              val spark = SparkSession
                .builder()
                .config(conf)
                //打开Hive连接
                .enableHiveSupport()
                .getOrCreate()
              import spark.implicits._
              spark.sql("create database test")
              spark.sql("use test")
              spark.sql("create table xxx(id int)")
              spark.sql("insert into `test`.`xxx` values(1)")
              spark.sql("load data local inpath 'file:///D:/Test/a.txt' into table xxx")
            }