SparkSql笔记分享

1、SparkSql概述
    1、什么是SparkSql?
        SparkSql是一个处理结构化数据的spark组件。
    2、Sparksql的应用场景
        sparksql用于离线处理
    3、什么是DataFrame
        DataFrame就是类似一个mysql的表,有列名列类型
        DataFrame只关注列,不关注行[不管每一行保存的是什么类型,显示的都是Row类型]
    4、什么是DataSet
        DataSet就是类似一个mysql的表
        DataSet既关注列[有列名列类型],也关注行[每一行要求必须是固定类型]
        DataSet是强类型[比如DataSet[Car]要求每一行必须是Car类型],DataFrame是弱类型[不管每一行保存的是什么类型,显示的都是Row类型]
    5、RDD、DataFrame、DataSet关系
        RDD、DataFrame、DataSet:都是弹性分布式数据集
        RDD、DataFrame、DataSet: 都有分区
        RDD、DataFrame、DataSet: 都是中间结果优先放入内存,内存不足放入磁盘
        RDD、DataFrame、DataSet: 都是惰性,需要使用类似action算子触发计算
2、SparkSql编程
    show方法默认只显示20行，可以自定义设置    .show[(numRows)]
    1、sparksession的创建:
        1、val spark = new SparkSession.Builder().appName(..).master(..).getOrCreate()
        2、val spark = SparkSession.builder().appName(..).master(..).getOrCreate()
    2、DataFrame的创建
        1、通过toDF方法创建
            使用toDF方法必须导入隐式转换: import spark.implicits._
                集合.toDF()/集合.toDF(列名,...)
                rdd.toDF/rdd.toDF(列名,...)
                集合/rdd中元素如果是样例类,那么转成dataFrame的时候列名就是样例类的属性名
                集合/rdd中元素如果是元组,那么转成dataFrame的时候列名就是_N,此时可以使用有参的toDF方法重定义列名。
        2、通过读取文件创建
            spark.read.json/csv/parquet/orc/...
        3、通过其他DataFrame衍生
            val df:DataFrame = ...
            val df2:DataFrame = df.filter/distinct/where/select/selectExpr/...("...")
        4、通过createDataFrame方法创建
            val rdd:RDD[Row] = ...
            val fields = Array( StructFiled(列名,列类型),.... )
            val schema:StructType = StructType( fields )
            spark.createDataFrame(rdd,schema)
    3、DataSet的创建
        1、通过toDS方法创建
            使用toDS方法必须导入隐式转换: import spark.implicits._
                集合.toDS()
                rdd.toDS
                集合/rdd中元素如果是样例类,那么转成dataSet的时候列名就是样例类的属性名
                集合/rdd中元素如果是元组,那么转成dataSet的时候列名就是_N,此时可以使用有参的toDF方法重定义列名。然后再toDS。
        2、通过读取文件创建[只能读textfile]
            val ds:DataSet[String] = spark.read.textFile
        3、通过其他DataSet衍生
            val ds:DataSet = ...
            val ds2:DataSet = ds.map/flatMap/filter/...
        4、通过createDataSet方法创建
            val ds:DataSet[rdd元素类型] = spark.createDataSet(rdd)
    4、sparksql编程方式
        1、命令式[使用一些方法算子操作数据]
            1、过滤: filter("sql过滤条件")/where("sql过滤条件")
            2、列裁剪: selectExpr("列名","函数(列名) 别名",...)
            3、去重
                1、所有列都相同才会去重: distinct
                2、指定列相同就会去重: dropDumplicates("列名",...)    [保留第一个列]
        2、声明式[使用sql语句操作数据]
            1、将df/ds的数据集注册成表: 
                df/ds.createOrReplaceTempView("表名"): 此种方法注册的表只能在生成当前df/ds的sparksession中使用
                df/ds.createOrReplaceGlobalTempView("表名") : 此种方法注册的表可以在多个sparksession中使用,使用的时候必须通过 global_temp.表名 使用
            2、写sql操作表数据: spark.sql("sql语句")
    5、RDD、DataFrame、DataSet相互转换
        RDD转DataFrame： rdd.toDF()/rdd.toDF(列名,...)
        RDD转DataSet： rdd.toDS()
        DataFrame转RDD: val rdd:RDD[Row] = df.rdd
            row类型的取值:  row.getAs[列类型]("列名")
        DataSet转RDD: val rdd:RDD[DataSet行类型] = ds.rdd
        DataSet转DataFrame: ds.toDF()/ds.toDF(列名,...)
        DataFrame转DataSet: val ds:DataSet[行类型] = df.as[行类型]
            as中的行类型的泛型如果是样例类,那么样例类的属性名可以不写,如果写属性,要求属性名必须和列名一致,并且属性的个数不能大于列的个数。
                如:
                    case calss Man(name：String,age：Int)
                    case calss Man1(name1：String,age1：Int)
                    case calss Man2()
                    df:DF(name：String,age：Int,salary：Double)        
                    df.as(man)    //得到    name：String,age：Int,salary：Double
                    df.as(man1)    //报错
                    df.as(man2)    //得到    name：String,age：Int,salary：Double
            as中的行类型的泛型如果是元组,要求元组的元素个数必须和列的个数一致,并且元组第N个元素的类型必须和第N个列的类型一致。
                如:
                    df:DF(name：String,age：Int,salary：Double)        
                    df.as[(String,Int,Double)]    //得到    name：String,age：Int,salary：Double
            [推荐写元组]
    6、自定义函数
        1、自定义UDF函数
            1、定义一个函数
            2、注册成udf函数: spark.udf.register("函数名",自定义函数对象)
            3、使用
        2、自定义UDAF函数
            1、自定义弱类型UDAF函数
                1、弱类型UDAF定义
                    1、创建class继承UserDefinedAggregateFunction
                    2、重写抽象方法
                        1、指定自定义UDAF函数参数的类型
                        2、指定中间变量的数据类型
                        3、UDAF函数最终结果类型
                        4、一致性
                        5、初始化中间变量
                        6、combiner计算
                        7、reducer计算
                        8、返回最终结果
                2、弱类型UDAF的使用
                    1、创建弱类型UDAF对象
                    2、对象注册: spark.udf.register("函数名",弱类型UDAF对象)
                    3、使用spark.sql中写sql使用
                3、代码
                class UserDefinedWeakUDAF extends UserDefinedAggregateFunction{
                  /**
                    * 指定自定义UDAF函数参数的类型
                    * @return
                    */
                  override def inputSchema: StructType = {

                    //StructType( Array( StructField("age",IntegerType) ) )
                    new StructType()
                      .add("age1",IntegerType)
                  }

                  /**
                    *指定中间变量的数据类型[ sum, count]
                    * @return
                    */
                  override def bufferSchema: StructType = {
                    //StructType( Array( StructField("sum",IntegerType) , StructField("count",IntegerType) )  )
                    new StructType()
                      .add("sum",IntegerType)
                      .add("count",IntegerType)
                  }

                  /**
                    * UDAF函数最终结果类型
                    * @return
                    */
                  override def dataType: DataType = DoubleType

                  /**
                    * 一致性
                    * @return
                    */
                  override def deterministic: Boolean = true

                  /**
                    * 初始化中间变量
                    * @param buffer
                    */
                  override def initialize(buffer: MutableAggregationBuffer): Unit = {
                    //初始化sum
                    buffer(0) = 0

                    //初始化count
                    buffer(1) = 0
                  }

                  /**
                    * combiner计算
                    * @param buffer
                    * @param input
                    */
                  override def update(buffer: MutableAggregationBuffer, input: Row): Unit = {
                    println(s"update ${Thread.currentThread().getName} buffer=${buffer}  input=${input}")
                    //更新中间结果变量sum  = sum + age
                    buffer(0) = buffer.getAs[Int](0) + input.getAs[Int](0)

                    //更新中间结果变量 count = count + 1
                    buffer(1) = buffer.getAs[Int](1) + 1
                  }

                  /**
                    * reducer计算
                    * @param buffer1
                    * @param buffer2
                    */
                  override def merge(buffer1: MutableAggregationBuffer, buffer2: Row): Unit = {
                    println(s"merge ${Thread.currentThread().getName} buffer1=${buffer1}  buffer2=${buffer2}")
                    // sum = sum + 分区的sum
                    buffer1(0) = buffer1.getAs[Int](0) + buffer2.getAs[Int](0)
                    // count = count + 分区的count
                    buffer1(1) = buffer1.getAs[Int](1) + buffer2.getAs[Int](1)
                  }

                  /**
                    * 返回最终结果
                    * @param buffer
                    * @return
                    */
                  override def evaluate(buffer: Row): Any = {
                    buffer.getAs[Int](0).toDouble / buffer.getAs[Int](1)
                  }
                }
            2、自定义强类型UDAF函数
                1、定义
                    1、定义一个class继承Aggregator[IN,BUFF,OUT]
                        IN: UDAF函数参数类型
                        BUFF: UDAF函数中间结果类型
                        OUT: UDAF函数结果类型
                    2、重写抽象方法
                        1、中间变量赋予初始值
                        2、combiner计算
                        3、reducer计算
                        4、计算最终结果
                        5、指定中间变量的编码格式
                        6、最终结果的编码格式
                2、使用
                    1、创建一个自定义UDAF对象
                    2、udaf函数转换: 
                        import org.apache.spark.sql.function._
                        udaf(自定义udaf对象)
                    3、注册: spark.udf.register(函数名,udaf函数转换结果)
                    4、使用spark.sql中写sql使用
                3、代码
                case class AvgBuff(sum:Int,count:Int)

                class UserDefinedStrongUDAF extends Aggregator[Int,AvgBuff,Double]{
                  /**
                    * 中间变量赋予初始值
                    * @return
                    */
                  override def zero: AvgBuff = {
                    AvgBuff(sum=0,count=0)
                  }

                  /**
                    * combiner计算
                    * @param b
                    * @param a
                    * @return
                    */
                  override def reduce(buff: AvgBuff, age: Int): AvgBuff = {
                    AvgBuff( buff.sum + age, buff.count+1 )
                  }

                  /**
                    * reducer计算
                    * @param b1
                    * @param b2
                    * @return
                    */
                  override def merge(buff1: AvgBuff, buff2: AvgBuff): AvgBuff = {
                    AvgBuff( buff1.sum+buff2.sum , buff1.count+buff2.count )
                  }

                  /**
                    * 计算最终结果
                    * @param reduction
                    * @return
                    */
                  override def finish(reduction: AvgBuff): Double = reduction.sum.toDouble / reduction.count

                  /**
                    * 指定中间变量的编码格式
                    * @return
                    */
                  override def bufferEncoder: Encoder[AvgBuff] = Encoders.product[AvgBuff]

                  /**
                    * 最终结果的编码格式
                    * @return
                    */
                  override def outputEncoder: Encoder[Double] = Encoders.scalaDouble
                }
3、sparksql数据加载和保存
    1、读取数据
        1、读取文本文件: spark.read.textFile(路径)
        2、读取json文件: spark.read.json(路径)
        3、读取csv文件:    
            spark.read
            [.option("sep","指定字段之间的分隔符")]
            [.option("header","指定是否以第一行作为列名")]
            [.option("inferSchema","指定是否对列进行类型推断")]
            .csv(路径)
        4、读取parquet： spark.read.parquet(路径)
        5、读取mysql
            1、第一种方式: [此种方式读取mysql生成的DataFrame只有一个分区,只适合小数据量场景 10W line]
                spark.read.jdbc(url,tableName,prop)
                //连接参数
                val prop = new Properties()
                prop.setProperty("user","root")
                prop.setProperty("password","root123")
                val url = "jdbc:mysql://hadoop102:3306/test"
                val tableName = "user_info" //表示查询整表
                或
                val tableName = "(select id,name,age from user_info where id>100) user" //表示查询指定行和列
            2、第二种方式: [此种方式读取mysql生成的DataFrame的分区数 = condition数组中元素个数] [一般不用]
                spark.read.jdbc(url,tableName,condition,prop)
                //连接参数
                val condition = Array("id<100","id>=100 and id<200","id>=200") //condition中元素就是每个分区拉取的数据的where条件
            3、第三种方式: [此种方式读取mysql生成的DataFrame的分区数 = min(upperBound-lowerBound,numPartitions),一般用于大数据量场景 ]
                spark.read.jdbc(url,tableName,columnName,lowerBound,upperBound,numPartitions,props)
                //连接参数
                columnName必须是数字、日期、时间戳类型的mysql的列名[用于作为分区列]
                lowerBound是columnName列的下限,用于决定每个分区取数的跨距
                upperBound是columnName列的上限,用于决定每个分区取数的跨距
                numPartitions是分区数
                    案例1：如col=id，分区数=3，lowerBound=1，upperBound=30
                        这时生成3个condition    id<11,id>=11 and id <21,id>=21 
                    案例2：如col=id，分区数=3，lowerBound=101，upperBound=130
                        这时生成3个condition    id<111,id>=111 and id <121,id>=121 
                    工作中lowerBound，upperBound一般设置成动态获取的表中分区列的最小值和最大值
                        val df4 = spark.read.jdbc(url,"(select min(id) min_id, max(id) max_id from user_info) user",prop)
                        val row = df4.collect().head
                        val min_id = row.getAs[Long]("min_id")
                        val max_id = row.getAs[Long]("max_id")
    2、保存数据       
        1、保存数据为文本: df/ds[.toJSON].write.mode(SaveMode.Overwrite).text(路径)
        2、保存数据为json: df/ds.write.mode(SaveMode.Overwrite).json(路径)
        3、保存数据为csv: df/ds.write.mode(SaveMode.Overwrite)
                            [.option("sep","指定保存字段之间的分隔符").option("header","指定是否将列名作为文件第一行保存")].csv(路径)
        4、保存数据到parquet: df/ds.write.mode(SaveMode.Overwrite).parquet(路径)
        5、保存数据到mysql: df/ds.write.mode(SaveMode.Append).jdbc(url,tableName,prop)
        常用的写入模式:
            1、SaveMode.Append: 代表如果写入数据的目录/表已经存在则追加数据
                [一般用于数据写入mysql并且mysql表没有主键或者唯一索引]
                    如果写入的表有主键,使用Append可能出现主键冲突的问题,
                    此时建议使用foreachPartition结合：
                        INSERT INTO 表名 VALUES(....) ON DUPLICATE KEY UPDATE 字段名=值,.... 解决
            2、SaveMode.Overwrite: 代表如果写入数据的目录/表已经存在则覆盖数据
                [一般用于数据写入HDFS (删掉文件/表重写)]
            3、ErrorIfExists：如果写入数据的目录/表已经存在则报错
            4、Ignore:如果写入数据的目录/表已经存在则不保存
    3、整合hive
            注：derby数据库只能单线程操作
        1、idea中如何操作hive
            1、引入依赖: spark-hive
            2、将hive的配置文件放入resource目录
            3、在创建sparksession的时候通过enableHiveSupport开启hive的支持
            4、操作hive数据
            5、[再idea本地执行时放在最前面声明,sparksession创建时会要加载环境配置,环境配置在加载后就不会改了]    
                    System.setProperty("HADOOP_USER_NAME","username")    /    (也可以在IDEA右上角 Edit VM options: -DHADOOP_USER_NAME=username)
        代码
            def main(args: Array[String]): Unit = {
            
                System.setProperty("HADOOP_USER_NAME","atguigu")
                
                import org.apache.spark.sql.SparkSession
                val spark = SparkSession.builder().master("local[4]").appName("test").enableHiveSupport().getOrCreate()
                //导入隐式转换
                import spark.implicits._

                //读取hive数据
                spark.sql("select * from student").show

                //保存数据到hive
                spark.sql("insert into student values(2,'wangwu',30)")
            }