12.SparkSql

最新推荐文章于 2025-02-08 18:45:50 发布

原创最新推荐文章于 2025-02-08 18:45:50 发布 · 364 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#hive #大数据 #spark #hadoop #sql

SparkSql

Spark SQL is Apache Spark's module for working with structured data。是spark处理结构化数据的模块。具有Integrated，Uniform Data Access，Hive Integration，Standard Connectivity的特点。

DataFrame, DataSet and RDD

DataSet是在1.6引入的一个分布式数据集合。it provides the benefits of RDDs (strong typing, ability to use powerful lambda functions) with the benefits of Spark SQL’s optimized execution engine.

A DataFrame is a Dataset organized into named columns，DataFrames can be constructed from a wide array of sources such as: structured data files, tables in Hive, external databases, or existing RDDs. 从各种源读来的数据都变成了DF

实际上，DF就是DS,在spark中有如下定义 type DataFrame = Dataset[Row],即DF就是泛型为[Row]的DS，DS为强类型，DF为弱类型

DF与RDD相比，DF中还记录了数据的结构信息，即schema。并且对RDD的一些执行过程做出了优化。

DF，DS和RDD的相互转换

package cn.lpc.spark.sql

import org.apache.spark.SparkContext
import org.apache.spark.rdd.RDD
import org.apache.spark.sql.{DataFrame, Dataset, Row, SparkSession}

object RDD2DF2DS {
    def main(args: Array[String]): Unit = {
        val builder: SparkSession.Builder = SparkSession.builder().master("local[2]").appName("sql")
        val spark: SparkSession = builder.getOrCreate()
        val list: List[Person] = Person("zs", 11) :: Person("ls", 13) :: Person("ww", 15) :: Nil
        // RDD
        val sc: SparkContext = spark.sparkContext
        val rdd: RDD[Person] = sc.parallelize(list)
        import spark.implicits._
        //rdd2df
        val df: DataFrame = rdd.toDF()
        println("rdd2df")
        df.show()
        //rdd2ds
        println("rdd2ds")
        val ds: Dataset[Person] = rdd.toDS()
        ds.show()
        //df2rdd
        println("df2rdd")
        val df2rdd: RDD[Row] = df.rdd
        df2rdd.collect().foreach(println)
        println("ds2rdd")
       //ds2rdd
       val ds2rdd: RDD[Person] = ds.rdd
        ds2rdd.collect().foreach(println)
        //df2ds
        println("df2ds")
        val df2ds: Dataset[Person] = df.as[Person]
        df2ds.show()
        //ds2df
        println("ds2df")
        val ds2df: DataFrame = ds.toDF()
        ds2df.show()
        spark.stop()
    }
}
case class Person(val name:String, val age:Int)

SparkSql的编程

sql编程无非就是读如数据，对数据进行计算后将数据写出或者传输。

读写数据

spark在读写数据时也是一种懒，需要load()和save()两种方法让它动起来。

读写文件系统中的数据

读：通过sparkSession的实例化对象进行操作，最终返回的是一个df。sparkSession.read生成一个reader,.format()是声明读取的文件的类型，可以为json,parquet等等。load()最终进行操作，需要输入路径。
写：通过DataFrame(DS)进行操作，将对象中存的数据写入到磁盘中。df.write生成一个writer，.format指定格式。save中指定路径并开始执行。由于文件的写入需要指定mode来对重复写进行处理,默认为error。

object ReadAndWrite {
    def main(args: Array[String]): Unit = {

        val spark: SparkSession = SparkSession.builder().master("local[2]").appName("ReadAndWrite").getOrCreate()
        val sc: SparkContext = spark.sparkContext
        import spark.implicits._


        val list: List[Person] = Person("zs", 11) :: Person("ls", 13) :: Person("ww", 15) :: Nil
        // RDD

        val rdd: RDD[Person] = sc.parallelize(list)
        val df: DataFrame = rdd.toDF()
        df.write.format("json").mode(SaveMode.Append).save("c://temp")

        val df1: DataFrame = spark.read.format("json").load("C:\\temp")

        

        spark.sql("select * from json.`C:\\temp`").show()
    }
}

读写数据库中的数据

读写数据库中的数据与文件的操作基本一致，注意的是要指定一些参数。

 def main(args: Array[String]): Unit = {
        val builder: SparkSession.Builder = SparkSession.builder().master("local[2]").appName("sql")
        val spark: SparkSession = builder.getOrCreate()
        val list: List[Person] = Person("zs", 11) :: Person("ls", 13) :: Person("ww", 15) :: Nil
        // RDD
        val sc: SparkContext = spark.sparkContext

        val rdd1: RDD[Person] = sc.parallelize(list)
        import spark.implicits._
        val df: DataFrame = rdd1.toDF()
        df.write.format("jdbc")
            .option("url", "jdbc:mysql://192.168.6.200:3306/gmall")
            .option("user", "root")
            .option("password", "123456")
            .option("dbtable", "person")
            .save()
    }

def main(args: Array[String]): Unit = {
        val spark: SparkSession = SparkSession.builder().master("local[2]").appName("ReadAndWrite").getOrCreate()
        val sc: SparkContext = spark.sparkContext
        import spark.implicits._
        val df: DataFrame = spark.read
            .format("jdbc")
            .option("url", "jdbc:mysql://192.168.6.200:3306/gmall")
            .option("user", "root")
            .option("password", "123456")
            .option("dbtable", "person")
            .load()

        df.createOrReplaceTempView("temp_user_info")
        spark.sql("select *  from temp_user_info").show()
    }

数据的计算

不管怎么计算，最后都要show()一下

三种方式执行计算数据

可以直接通过执行spark.sql获取df后show即可,不过如果需要操作某个df或者ds，需要首先用ds或者df创建一个临时的视图，案例如下

    df.createOrReplaceTempView("user")
    spark.sql("select age from user").show()

直接操作某个文件中的数据时可以直接用sql读取，其实本质还是sql

spark.sql("select * from json.`C:\\temp`").show()

除了以上两种，其实还可以使用rdd风格的编程

 ds.select("age")

自定义函数

自定义函数可以分为UDF和UDAF，UDF的定义比较简单直接使用匿名函数即可。注意需要注册。

def main(args: Array[String]): Unit = {
        val builder: SparkSession.Builder = SparkSession.builder().master("local[2]").appName("sql")
        val spark: SparkSession = builder.getOrCreate()
        val list: List[Person] = Person("zs", 11) :: Person("ls", 13) :: Person("ww", 15) :: Nil
        // RDD
        val sc: SparkContext = spark.sparkContext

        val rdd: RDD[Person] = sc.parallelize(list)
        import spark.implicits._
        val df: DataFrame = rdd.toDF()
        spark.udf.register("doubleName",(s:String)=>s+s)
        df.createOrReplaceTempView("user")
        val sql : String = "select doubleName(name) as dname,age from user"
        spark.sql(sql).show()
        spark.stop()
    }

UDAF的函数比较复杂，复杂在于spark中的数据是分区的一个逻辑不能进行聚合，其实和RDD中的aggregate一致，需要有初值，分区内的计算，分区间的计算，还要指定输入和输出格式。

aggregate(num)(func,func1) num对应initialize的输出。func对应update，func1对应merge。逻辑上不难，注意处理update时输入的数据可能为null的情形。还需要注意注册。

对于DataSet的操作也可以定义UDAF。UDAF需要定义输入，缓冲区还有返回值的泛型。还有输入和输出的编码，如果是样例类就定义为product把。在使用时需要将定义的UDAF new一个并生成列

package cn.lpc.spark.sql

import org.apache.spark.SparkContext
import org.apache.spark.rdd.RDD
import org.apache.spark.sql.catalyst.expressions.Encode
import org.apache.spark.sql.{DataFrame, Dataset, Encoder, Encoders, Row, SparkSession, TypedColumn}
import org.apache.spark.sql.expressions.{Aggregator, MutableAggregationBuffer, UserDefinedAggregateFunction}
import org.apache.spark.sql.types.{DataType, DoubleType, IntegerType, StructField, StructType}

object DefineUDAF {
    def main(args: Array[String]): Unit = {
        // 用于弱类型，在sql语句中
        val builder: SparkSession.Builder = SparkSession.builder().master("local[2]").appName("sql")
        val spark: SparkSession = builder.getOrCreate()
        val list: List[Person] = Person("zs", 11) :: Person("ls", 13) :: Person("ww", 15) :: Nil
        val sc: SparkContext = spark.sparkContext

        val rdd: RDD[Person] = sc.parallelize(list)
        import spark.implicits._
        val df: DataFrame = rdd.toDF()

        df.createOrReplaceTempView("user")

        spark.udf.register("my_avg",new MyAvg)

        spark.sql("select my_avg(age) from user").show()

        val ds: Dataset[Person] = df.as[Person]

        // new一个并生成列。
        val value: TypedColumn[Person, Double] = new MyAvgDS().toColumn.name("ageAvg")
        // 输入的时person所以都弄好了
        ds.select(value).show()
        // 用于强类型，给ds使用
    }
}

// 自定义弱类型
 class MyAvg extends UserDefinedAggregateFunction{
     //rdd2.aggregateByKey(缓冲区类型)（）
     //输入数据类型
    override def inputSchema: StructType = StructType(StructField("c",DoubleType)::Nil)

     // 缓冲区类型
    override def bufferSchema: StructType = StructType(StructField("sum",DoubleType)::StructField("count",IntegerType)::Nil)

     //  The `DataType` of the returned value of this [[UserDefinedAggregateFunction]].
    override def dataType: DataType = DoubleType

     // 相同输入是否应该有相同的输出
    override def deterministic: Boolean = true

     // 对缓冲区做初始化
    override def initialize(buffer: MutableAggregationBuffer): Unit = {
        buffer(0)=0D
        buffer(1)=0
    }

     // 分区内聚合
    override def update(buffer: MutableAggregationBuffer, input: Row): Unit = {
        if(!input.isNullAt(0)){
            val value: Double = input.getDouble(0)
            buffer(0)= buffer.getDouble(0)+value
            buffer(1) = buffer.getInt(1) +1
        }
    }

     // 分区间聚合
    override def merge(buffer1: MutableAggregationBuffer, buffer2: Row): Unit = {
        buffer1(0) = buffer2.getDouble(0)+buffer1.getDouble(0)
        buffer1(1)  = buffer2.getInt(1)+buffer1.getInt(1)
    }


    override def evaluate(buffer: Row): Any = {
        buffer.getDouble(0)/buffer.getInt(1)
    }
}

// 自定义的强类型
class MyAvgDS extends Aggregator[Person,AgeAvg,Double] {
    // 对缓冲区做初始化
    override def zero: AgeAvg = {AgeAvg(0D,0L)}

    // 分区内聚合
    override def reduce(b: AgeAvg, a: Person): AgeAvg = {
        AgeAvg(b.ageSum+a.age,b.ageCount+1)
    }

    // 分区间聚合
    override def merge(b1: AgeAvg, b2: AgeAvg): AgeAvg = {
        AgeAvg(b1.ageSum + b2.ageSum,b1.ageCount+b2.ageCount)
    }

    // 返回最终聚合的值
    override def finish(reduction: AgeAvg): Double = {
        reduction.getAvg()
    }

    // 缓冲的编码器
    override def bufferEncoder: Encoder[AgeAvg] = Encoders.product

    // 输出的编码器
    override def outputEncoder: Encoder[Double] = Encoders.scalaDouble
}

case class AgeAvg(ageSum:Double,ageCount:Long){
    def getAvg()={
        ageSum/ageCount
    }
}

Spark和hive的操作

Spark具有内置hive

直接执行Sql语句就是在内置hive中执行

Spark操作外置hive

Spark操作Hive有三个步骤：

将hive-site放入conf目录，如果使用Idea，则将该文件放入resource目录里面
要有连接hive元数据的Jar包，比如元数据存在mysql中就需要mysql的依赖
在创建sparkSession时需要有enableHiveSupport
spark提供了Spark-sql命令行工具直接执行sql语句
spark还提供了类似hiveserver2的工具thrift。1.使用bin/start-server,2.使用sbin/beeline连接。!connect jdbc:hive2://hadoop101:10000
使用idea访问

def main(args: Array[String]): Unit = {
        val spark: SparkSession = SparkSession.builder()
            .master("local[2]")
            .appName("hive")
            .enableHiveSupport()
            .getOrCreate()

        spark.sql("show tables").show()
    }

Spark练习

计算各个区域前三大热门商品，并备注上每个商品在主要城市中的分布比例，超过两个城市用其他显示。

data
area    省份    商品名
华北    北京    商品A
华南    广东    商品B

table
地区	商品名称		点击次数	城市备注
华北	商品A		100000	北京21.2%，天津13.2%，其他65.6%
华北	商品P		80200	北京63.0%，太原10%，其他27.0%
华北	商品M		40000	北京63.0%，太原10%，其他27.0%
东北	商品J		92000	大连28%，辽宁17.0%，其他 55.0%

该问题的主要难点在于城市备注的实现。

按照area和商品分组，并且制作备注，和统计次数

 spark.sql(
            """
              |select
              |area,
              | product_name,
              | remark(city_name) remark,
              | count(*) times
              |from
              |firstData
              |group by area,product_name
              |""".stripMargin).createOrReplaceTempView("t2")
// remark为自定义函数

class Remark extends UserDefinedAggregateFunction {
    override def inputSchema: StructType = StructType(StructField("city_name",StringType)::Nil)

    override def bufferSchema: StructType = StructType(StructField("buffer",MapType(StringType,LongType))::StructField("count",LongType)::Nil)

    override def dataType: DataType = StringType

    override def deterministic: Boolean = true

    override def initialize(buffer: MutableAggregationBuffer): Unit = {
        buffer(0) = Map[String,Long]()
        buffer(1) = 0L
    }

    override def update(buffer: MutableAggregationBuffer, input: Row): Unit = {
        if(!input.isNullAt(0)){
            val city_name: String = input.getString(0)
            val temp_map: collection.Map[String, Long] =  buffer.getMap[String,Long](0)
            buffer(0) = temp_map + (city_name->(temp_map.getOrElse(city_name,0L)+1L))
            buffer(1) = buffer.getLong(1) + 1L
        }
    }

    override def merge(buffer1: MutableAggregationBuffer, buffer2: Row): Unit = {
        val temp_map1: collection.Map[String, Long] =  buffer1.getMap[String,Long](0)
        val temp_map2: collection.Map[String, Long] =  buffer2.getMap[String,Long](0)
        val count1: Long = buffer1.getLong(1)
        val count2: Long = buffer2.getLong(1)
        buffer1(0) = temp_map1.foldLeft(temp_map2){
            case (map,(k,v))=>map + ( k ->(map.getOrElse(k,1L)+v))
        }
        buffer1(1) = count1+count2

    }

    override def evaluate(buffer: Row): Any = {
        val count: Long = buffer.getLong(1)
        val format = new DecimalFormat(".00%")
        val tuples: List[(String, Long)] = buffer.getMap[String, Long](0).toList.sortBy(-_._2).take(2)
        val countTop2: Long = tuples.foldLeft(0L)((x, y) => x + y._2)
        tuples.map {
            case (k, v) => {
                k + ":" + format.format(v.toDouble/count)
            }
        }.mkString(", ") + ", 其他" +":"+ format.format((count-countTop2).toDouble / count)


    }
}

按次数排序，需要开窗

 spark.sql(
            """
              |select
              |area,
              |product_name,
              |times,
              |remark,
              |rank() over(partition by area order by times desc) rk
              |from t2
              |""".stripMargin).createOrReplaceTempView("t3")

取前三存入数据库之中

  spark.sql(
            """
              |select
              |area,
              |product_name,
              |remark,
              |times
              |from
              |t3
              |where rk <= 3
              |""".stripMargin).write.format("jdbc")
                    .option("url", "jdbc:mysql://192.168.6.200:3306/gmall")
                    .option("user", "root")
                    .option("password", "123456")
                    .option("dbtable", "adsTop3")
            .save()