SparkSQL常用API总结

SparkSQL常用API总结

读取数据

• 文件读取

  #本地文件读取
  	#创建SparkSession
      val spark=SparkSession.builder()
      .appName("RW")
      .master("local[6]")	#本地运行
      .getOrCreate()
  	
  	#隐式转换导入，DataFrame、Dataset与序列集合之间的转换
  	import spark.implicits._
  	
  	#Schema定义
  	val schema=types.StructType{
        List(
          StructField("id",IntegerType),
          StructField("year",IntegerType),
          StructField("month",IntegerType),
          StructField("day",IntegerType),
          StructField("hour",IntegerType),
          StructField("season",IntegerType),
          StructField("pm",DoubleType)
        )
      }
  
  	#文件读取
      val df=spark.read
  	//.schema(schema)
  	.option("delimiter","\t")	#指定数据分隔符
      .option("header",value = true)	#将文件第一行作为header
      .option("inferSchema",value = true)	#自动推断类型，也可以自己定义Schema
      .csv("dataset/BeijingPM.csv")
  
  
  
  #HDFS文件读取
  	#文件读取
  	val df=spark.read
  	//.schema(schema)
  	.option("delimiter","\t")	#指定数据分隔符
      .option("header",value = true)	#将文件第一行作为header
      .option("inferSchema",value = true)	#自动推断类型，也可以自己定义Schema
      .csv("hdfs://host:port/dataset/BeijingPM.csv")
  

• Mysql数据源

  val df=spark.read
  	.format("jdbc")
      .option("url","jdbc:mysql://192.168.2.136:3306/spark")
  	.option("user","root")
  	.option("password","root")
  	.option("dbtable","student")
  	.option("driver","com.mysql.jdbc.Driver")
  	.load()
  

• Hive数据源

  #访问Hive，必须在集群中运行
  val spark=SparkSession.builder()
        .appName("Hive")
        .enableHiveSupport()	#启用Hive支持
        .config("hive.metastore.uris","thrift://node03:9083")		#通过thirft服务访问hive
        .config("spark.sql.warehouse.dir","/dataset/hive")
        .getOrCreate()
  val schema=StructType{
        List(
          StructField("name",StringType),
          StructField("age",IntegerType),
          StructField("gpa",FloatType)
        )
      }
  val df=spark.read
      .option("delimiter","\t")
      .schema(schema)
      .csv("hdfs://node01:8020/dataset/studenttab10k")
  

数据写入

• 文件写入

  #本地文件写入
  df.write
        .partitionBy("year","month")	#按列名指定分区
        .mode(SaveMode.Overwrite)	#写入模式，error、append、overwrite、ignore
        .csv("dataset/pm_partitions")
  #HDFS写入
  df.write
        .mode(SaveMode.Append)
        .save("hdfs://192.168.2.135:8020/mr")
  

• Mysql写入

  df.write
  	.format("jdbc")
      .option("url","jdbc:mysql://192.168.2.136:3306/spark")
  	.option("user","root")
  	.option("password","root")
  	.option("dbtable","student")
  	.option("driver","com.mysql.jdbc.Driver")
  	.save()
  

• Hive写入

  #集群运行，同“数据写入”代码
  df.write
        .mode(SaveMode.Overwrite)
        .saveAsTable("spark1.student")
  

API操作

有类型转换

• 转换操作

  #通过 flatMap 可以将一条数据转为一个数组, 后再展开这个数组放入 Dataset
  import spark.implicits._
  val ds = Seq("hello world", "hello pc").toDS()
  ds.flatMap( _.split(" ") ).show()
  
  #map 可以将数据集中每条数据转为另一种形式
  import spark.implicits._
  val ds = Seq(Person("zhangsan", 15), Person("lisi", 15)).toDS()
  ds.map( person => Person(person.name, person.age * 2) ).show()
  
  #mapPartitions 和 map 一样, 但是 map 的处理单位是每条数据, mapPartitions 的处理单位是每个分区
  import spark.implicits._
  val ds = Seq(Person("zhangsan", 15), Person("lisi", 15)).toDS()
  ds.mapPartitions( iter => {
      val returnValue = iter.map(
        item => Person(item.name, item.age * 2)
      )
      returnValue
    } )
    .show()
  
  #map 和 mapPartitions 以及 transform 都是转换, map 和 mapPartitions 是针对数据, 而 transform 是针对整个数据集, 这种方式最大的区别就是 transform 可以直接拿到 Dataset 进行操作
  import spark.implicits._
  val ds = spark.range(5)
  ds.transform( dataset => dataset.withColumn("doubled", 'id * 2) )
  
  #as[Type] 算子的主要作用是将弱类型的 Dataset 转为强类型的 Dataset, 它有很多适用场景, 但是最常见的还是在读取数据的时候, 因为 DataFrameReader 体系大部分情况下是将读出来的数据转换为 DataFrame 的形式, 如果后续需要使用 Dataset 的强类型 API, 则需要将 DataFrame 转为 Dataset. 可以使用 as[Type] 算子完成这种操作
  import spark.implicits._
  
  val structType = StructType(
    Seq(
      StructField("name", StringType),
      StructField("age", IntegerType),
      StructField("gpa", FloatType)
    )
  )
  
  val sourceDF = spark.read
    .schema(structType)
    .option("delimiter", "\t")
    .csv("dataset/studenttab10k")
  
  val dataset = sourceDF.as[Student]
  dataset.show()
  

• filter

  #filter 用来按照条件过滤数据集
  import spark.implicits._
  val ds = Seq(Person("zhangsan", 15), Person("lisi", 15)).toDS()
  ds.filter( person => person.name == "lisi" ).show()
  

• groupByKey

  #grouByKey 算子的返回结果是 KeyValueGroupedDataset, 而不是一个 Dataset, 所以必须要先经过 KeyValueGroupedDataset 中的方法进行聚合, 再转回 Dataset, 才能使用 Action 得出结果
  
  其实这也印证了分组后必须聚合的道理
  import spark.implicits._
  val ds = Seq(Person("zhangsan", 15), Person("zhangsan", 15), Person("lisi", 15)).toDS()
  ds.groupByKey( person => person.name ).count().show()
  

• sample

  #sample 会随机在 Dataset 中抽样
  val ds = spark.range(15)
  ds.sample(withReplacement = false, fraction = 0.4).show()
  

• 排序

  #orderBy 配合 Column 的 API, 可以实现正反序排列
  import spark.implicits._
  val ds = Seq(Person("zhangsan", 12), Person("zhangsan", 8), Person("lisi", 15)).toDS()
  ds.orderBy("age").show()
  ds.orderBy('age.desc).show()
  
  #其实 orderBy 是 sort 的别名, 所以它们所实现的功能是一样的
  import spark.implicits._
  val ds = Seq(Person("zhangsan", 12), Person("zhangsan", 8), Person("lisi", 15)).toDS()
  ds.sort('age.desc).show()
  

• 分区

  #减少分区, 此算子和 RDD 中的 coalesce 不同, Dataset 中的 coalesce 只能减少分区数, coalesce 会直接创建一个逻辑操作, 并且设置 Shuffle 为 false
  val ds = spark.range(15)
  ds.coalesce(1).explain(true)
  
  #repartitions 有两个作用, 一个是重分区到特定的分区数, 另一个是按照某一列来分区, 类似于 SQL 中的 DISTRIBUTE BY
  val ds = Seq(Person("zhangsan", 12), Person("zhangsan", 8), Person("lisi", 15)).toDS()
  ds.repartition(4)
  ds.repartition('name)
  

• 去重

  #使用 dropDuplicates 可以去掉某一些列中重复的行
  import spark.implicits._
  val ds = spark.createDataset(Seq(Person("zhangsan", 15), Person("zhangsan", 15), Person("lisi", 15)))
  ds.dropDuplicates("age").show()
  
  #当 dropDuplicates 中没有传入列名的时候, 其含义是根据所有列去重, dropDuplicates() 方法还有一个别名, 叫做 distinct
  import spark.implicits._
  val ds = spark.createDataset(Seq(Person("zhangsan", 15), Person("zhangsan", 15), Person("lisi", 15)))
  ds.distinct().show()
  

无类型转换

• 选择

  #select 用来选择某些列出现在结果集中
  import spark.implicits._
  val ds = Seq(Person("zhangsan", 12), Person("zhangsan", 8), Person("lisi", 15)).toDS()
  ds.select($"name").show()
  
  #在 SQL 语句中, 经常可以在 select 子句中使用 count(age), rand() 等函数, 在 selectExpr 中就可以使用这样的 SQL 表达式, 同时使用 select 配合 expr 函数也可以做到类似的效果
  import spark.implicits._
  import org.apache.spark.sql.functions._
  val ds = Seq(Person("zhangsan", 12), Person("zhangsan", 8), Person("lisi", 15)).toDS()
  ds.selectExpr("count(age) as count").show()
  ds.selectExpr("rand() as random").show()
  ds.select(expr("count(age) as count")).show()
  
  #通过 Column 对象在 Dataset 中创建一个新的列或者修改原来的列
  import spark.implicits._
  import org.apache.spark.sql.functions._
  val ds = Seq(Person("zhangsan", 12), Person("zhangsan", 8), Person("lisi", 15)).toDS()
  ds.withColumn("random", expr("rand()")).show()
  
  #修改列名
  import spark.implicits._
  val ds = Seq(Person("zhangsan", 12), Person("zhangsan", 8), Person("lisi", 15)).toDS()
  ds.withColumnRenamed("name", "new_name").show()
  

• drop

  #剪掉某个列
  import spark.implicits._
  val ds = Seq(Person("zhangsan", 12), Person("zhangsan", 8), Person("lisi", 15)).toDS()
  ds.drop('age).show()
  

• groupBy

  #按照给定的行进行分组
  import spark.implicits._
  val ds = Seq(Person("zhangsan", 12), Person("zhangsan", 8), Person("lisi", 15)).toDS()
  ds.groupBy('name).count().show()
  

聚合

• 一维聚合（groupBy）

  groupBy 算子会按照列将 Dataset 分组, 并返回一个 RelationalGroupedDataset 对象, 通过 RelationalGroupedDataset 可以对分组进行聚合
  
  Step 1: 加载实验数据
  private val spark = SparkSession.builder()
      .master("local[6]")
      .appName("aggregation")
      .getOrCreate()
  
    import spark.implicits._
  
    private val schema = StructType(
      List(
        StructField("id", IntegerType),
        StructField("year", IntegerType),
        StructField("month", IntegerType),
        StructField("day", IntegerType),
        StructField("hour", IntegerType),
        StructField("season", IntegerType),
        StructField("pm", DoubleType)
      )
    )
  
    private val pmDF = spark.read
      .schema(schema)
      .option("header", value = true)
      .csv("dataset/pm_without_null.csv")
  Step 2: 使用 functions 函数进行聚合
  import org.apache.spark.sql.functions._
  
  val groupedDF: RelationalGroupedDataset = pmDF.groupBy('year)
  
  groupedDF.agg(avg('pm) as "pm_avg")
    .orderBy('pm_avg)
    .show()
  Step 3: 除了使用 functions 进行聚合, 还可以直接使用 RelationalGroupedDataset 的 API 进行聚合
  groupedDF.avg("pm")
    .orderBy('pm_avg)
    .show()
  
  groupedDF.max("pm")
    .orderBy('pm_avg)
    .show()
  

• 多维聚合（rollup）

  rollup 操作符其实就是 groupBy 的一个扩展, rollup 会对传入的列进行滚动 groupBy, groupBy 的次数为列数量 + 1, 最后一次是对整个数据集进行聚合
  例如：rollup("year","month"),他会进行groupBy("year","month")、groupBy("year")、groupBy(null)三次聚合，然后进行数据合并
  
  Step 1: 创建数据集
  import org.apache.spark.sql.functions._
  
  val sales = Seq(
    ("Beijing", 2016, 100),
    ("Beijing", 2017, 200),
    ("Shanghai", 2015, 50),
    ("Shanghai", 2016, 150),
    ("Guangzhou", 2017, 50)
  ).toDF("city", "year", "amount")
  Step 1: rollup 的操作
  sales.rollup("city", "year")
    .agg(sum("amount") as "amount")
    .sort($"city".desc_nulls_last, $"year".asc_nulls_last)
    .show()
  
  /**
    * 结果集:
    * +---------+----+------+
    * |     city|year|amount|
    * +---------+----+------+
    * | Shanghai|2015|    50| <-- 上海 2015 的小计
    * | Shanghai|2016|   150|
    * | Shanghai|null|   200| <-- 上海的总计
    * |Guangzhou|2017|    50|
    * |Guangzhou|null|    50|
    * |  Beijing|2016|   100|
    * |  Beijing|2017|   200|
    * |  Beijing|null|   300|
    * |     null|null|   550| <-- 整个数据集的总计
    * +---------+----+------+
    */
  Step 2: 如果使用基础的 groupBy 如何实现效果?
  val cityAndYear = sales
    .groupBy("city", "year") // 按照 city 和 year 聚合
    .agg(sum("amount") as "amount")
  
  val city = sales
    .groupBy("city") // 按照 city 进行聚合
    .agg(sum("amount") as "amount")
    .select($"city", lit(null) as "year", $"amount")
  
  val all = sales
    .groupBy() // 全局聚合
    .agg(sum("amount") as "amount")
    .select(lit(null) as "city", lit(null) as "year", $"amount")
  
  cityAndYear
    .union(city)
    .union(all)
    .sort($"city".desc_nulls_last, $"year".asc_nulls_last)
    .show()
  
  /**
    * 统计结果:
    * +---------+----+------+
    * |     city|year|amount|
    * +---------+----+------+
    * | Shanghai|2015|    50|
    * | Shanghai|2016|   150|
    * | Shanghai|null|   200|
    * |Guangzhou|2017|    50|
    * |Guangzhou|null|    50|
    * |  Beijing|2016|   100|
    * |  Beijing|2017|   200|
    * |  Beijing|null|   300|
    * |     null|null|   550|
    * +---------+----+------+
    */
  很明显可以看到, 在上述案例中, rollup 就相当于先按照 city, year 进行聚合, 后按照 city 进行聚合, 最后对整个数据集进行聚合, 在按照 city 聚合时, year 列值为 null, 聚合整个数据集的时候, 除了聚合列, 其它列值都为 null
  

• 多维聚合（cube）

  #cube 的功能和 rollup 是一样的, 但也有区别, 区别如下
  
  rollup(A, B).sum
  
  其结果集中会有三种数据形式: A B , A null, null null
  
  不知道大家发现没, 结果集中没有对 B 列的聚合结果
  
  cube(A, B).sum
  
  其结果集中会有四种数据形式: A B , A null , null null , null B 
  
  不知道大家发现没, 比 rollup 的结果集中多了一个 null B , 也就是说, rollup 只会按照第一个列来进行组合聚合, 但是 cube 会将全部列组合聚合
  

连接

 /**
   * cross链接，笛卡尔积
   */
  @Test
  def crossJoin(): Unit ={
    person.crossJoin(cities)
      .where(person.col("cityId")===cities.col("id"))
      .show()
  }

  /**
   * 内连接
   */
  @Test
  def innerJoin(): Unit ={
    person.join(cities,person.col("cityId")===cities.col("id"),"inner")
      .show()
  }

  /**
   * 全外连接
   */
  @Test
  def fullouterJoin(): Unit ={
    person.join(cities,
      person.col("cityId")===cities.col("id")
    ,"full").show()
  }

  /**
   * 左外链接
   */
  @Test
  def leftJoin(): Unit ={
    person.join(cities,
      person.col("cityId")===cities.col("id"),
    "left").show()
    person.join(cities,
      person.col("cityId")===cities.col("id"),
    "right").show()
  }

  /**
   * semi     anti
   */
  @Test
  def semiAndAnti(): Unit ={
    person.join(cities,person.col("cityId")===cities.col("id"),
    "leftanti").show()

    person.join(cities,person.col("cityId")===cities.col("id"),
    "leftsemi").show()
  }

函数

UDF

#用户可以将自定义函数，并将其注册为SparkSql函数来使用
 /**
   * UDF
   */
  @Test
  def myudf(): Unit ={
    import org.apache.spark.sql.functions._
    val df=Seq("1","2","3").toDF("value")
    val toAppendStr1=udf(toAppendStr _)		#注册语法，返回函数
    df.select(toAppendStr1('value)).show()
  }

  def toAppendStr(str:String): String ={
    "LIHAO_"+str
  }

窗口函数

#数据进行每组内操作，即分组后对组内数据进行操作
#语法，（作用函数） over (window) as "别名"
  @Test
  def firstSecond(): Unit = {
    val spark = SparkSession.builder()
      .appName("window")
      .master("local[6]")
      .getOrCreate()

    import spark.implicits._

    import org.apache.spark.sql.functions._

    val data = Seq(
      ("Thin", "Cell phone", 6000),
      ("Normal", "Tablet", 1500),
      ("Mini", "Tablet", 5500),
      ("Ultra thin", "Cell phone", 5000),
      ("Very thin", "Cell phone", 6000),
      ("Big", "Tablet", 2500),
      ("Bendable", "Cell phone", 3000),
      ("Foldable", "Cell phone", 3000),
      ("Pro", "Tablet", 4500),
      ("Pro2", "Tablet", 6500)
    )

    val source = data.toDF("product", "category", "revenue")

    val window=Window.partitionBy("category")
      .orderBy('revenue.desc)

    source.select('product,'category,'revenue,dense_rank() over window as "rank")
      .where('rank<=2)
      .select('product,'category,'revenue)
      .show()

    val meanWindow=Window.partitionBy("category")
        .orderBy('revenue.desc)
    

    source.select('product,'category,'revenue,(max('revenue) over(meanWindow)) -'revenue  as "mean" )
      .show()

  }