Spark SQL API -- DSL

Spark SQL API

DSL

代码 --> DSL <-- SQL

package com.shujia.sql

import org.apache.spark.sql.expressions.Window
import org.apache.spark.sql.{DataFrame, SparkSession}

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

    val spark: SparkSession = SparkSession
      .builder()
      .master("local")
      .appName("dsl")
      .getOrCreate()

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

    val df: DataFrame = spark
      .read
      .format("json")
      .load("data/students.json")

    df.show(100)


    /**
      * select 选择
      *
      */

    //通过列名获取列
    df.select("id", "name")
      .show()

    //通过列的对象的表达式获取列，可以对列进行加工处理
    df.select($"id", $"age".+(1).as("addAge"))
    
    //简写 -- 去掉 . ()
    df.select($"id", $"age" + 1 as "addAge")
      .show()


    /**
      * where ： 过滤
      * where 底层调用的还是 filter
      * =!=  不等于
      * ===  等于
      **/

    //列对象的表达式的方式
    //这里面的 等于和不等于要这样写
    // =!=  不等于
    // ===  等于
    df.where($"age" > 22 and $"gender" =!= "男")
      .show()

    //字符串表达式, sql语法
    //在 " " 中直接写SQL
    df.where("age > 22 and gender = '男'")
      .show()

    // 还可以使用 filter -- 不常用
    // df.filter()
    
    
    /**
      * groupBy -- 分组
      *
      */

    df.groupBy($"clazz")
      .count()
      .show()

    /**
      * 分组之后使用agg进行聚合计算
      * 分组的列和聚合的列会保留
      * sql 中所有的聚合函数在这里都可以使用
      *
      * select clazz,count(1),avg(age) from t group by clazz
      */

    df
      .groupBy($"clazz")
      .agg(count($"clazz") as "num", avg($"age") as "avgAge")
      .show()


    /**
      * sortby -- 排序
      * 默认升序
      */

    df.sort($"age".desc)
      .show()


    /**
      * join
      *
      */

    val scoreDF: DataFrame = spark
      .read
      .format("csv")
      .option("sep", ",")
      .schema("sId STRING,cId STRING,sco DOUBLE")
      .load("data/score.txt")

    //关联的字段名一样的时候，默认关联方式是 inner join
    //df.join(scoreDF, "id").show()

    //指定关联方式
    //List("id") -- 表示字段的List集合(多个字段)，API规定要这样写
    //df.join(scoreDF, List("id"), "inner").show()

    //关联字段名不一样的时候
    //$"id" === $"sId" -- 字段对象的表达式(关联条件)
    //注意书写的顺序:左表字段在前，右表字段在后
    df.join(scoreDF, $"id" === $"sId", "inner").show()


    /**
      * 使用 窗口函数
      *
      * 取出每个班级年龄最大的前10个学生
      */

    //将DF注册成一个临时的视图
    df.createOrReplaceTempView("student")

    // 在 sql 的括号中 输入三个 "
    spark.sql(
      """
        |
        |select * from (
        |select
        |*,row_number() over(partition by clazz order by age desc) as r
        |from
        |student
        |) as a
        |where a.r<=10
        |
      """.stripMargin)
    //.show(1000)

    /**
      * DSL 的写法
      *
      * withColumn : 通过一个表达式给 DF 增加一个新的列
      */

    df.withColumn("r", row_number().over(Window.partitionBy($"clazz").orderBy($"age".desc)))
      .where($"r" <= 10)
      .show(1000)


    /**
      * sql 执行顺序
      *
      * select * from
      * t1 as a
      * join
      * t2 as b
      * on a.id=b.id
      * where a.age>22
      * group by id
      * having age(age)> 23
      *
      * SQL底层会做优化 会先过滤
      * from --> join --> on --> where --> group by --> having --> select --> order by --> limit
      *
      * 在SQL中有where和having的区分，但是在代码里没有
      * 因为代码的执行顺序是从上往下的
      * 在代码中是没有having的，只有where
      */
  }
}