Spark SQL

3.3 DF数据运算操作

3.3.1 纯SQL操作

核心要义：将DataFrame 注册为一个临时视图view，然后就可以针对view直接执行各种sql

临时视图有两种：session级别视图，global级别视图；

session级别视图是Session范围内有效的，Session退出后，表就失效了；

全局视图则在application级别有效；

注意使用全局表时需要全路径访问：global_temp.people

Scala // application全局有效 df.createGlobalTempView("stu") spark.sql(   """     |select * from global_temp.stu a order by a.score desc   """.stripMargin)     .show() ​

Scala // session有效 df.createTempView("s") spark.sql(   """     |select * from s order by score   """.stripMargin)   .show()

​

Scala val spark2 = spark.newSession() // 全局有效的view可以在session2中访问 spark2.sql("select id,name from global_temp.stu").show() // session有效的view不能在session2中访问 spark2.sql("select id,name from s").show()

以上只是对语法的简单示例，可以扩展到任意复杂的sql

挑战一下 ?

求出每个城市中，分数最高的学生信息；

Go go go !

3.3.2 DSL风格API(TableApi)语法

DSL风格API，就是用编程api的方式，来实现sql语法

DSL：特定领域语言

dataset的tableApi有一个特点：运算后返回值必回到dataframe

因为select后，得到的结果，无法预判返回值的具体类型，只能用通用的Row封装

数据准备

Scala val df = spark.read   .option("header", true)   .option("inferSchema", true)   .csv("data_ware/demodata/stu.csv")

​

（1）基本select及表达式

Scala /**   * 逐行运算   */ // 使用字符串表达"列" df.select("id","name").show() ​ // 如果要用字符串形式表达sql表达式，应该使用selectExpr方法 df.selectExpr("id+1","upper(name)").show // select方法中使用字符串sql表达式，会被视作一个列名从而出错 // df.select("id+1","upper(name)").show() ​ import spark.implicits._ // 使用$符号创建Column对象来表达"列" df.select($"id",$"name").show() ​ // 使用单边单引号创建Column对象来表达"列" df.select('id,'name).show() ​ // 使用col函数来创建Column对象来表达"列" import org.apache.spark.sql.functions._ df.select(col("id"),col("name")).show() ​ // 使用Dataframe的apply方法创建Column对象来表达列 df.select(df("id"),df("name")).show() ​ // 对Column对象直接调用Column的方法，或调用能生成Column对象的functions来实现sql中的运算表达式 df.select('id.plus(2).leq("4").as("id2"),upper('name)).show() df.select('id+2 <= 4 as "id2",upper('name)).show()

（3）字段重命名

Scala /**   * 字段重命名   */ // 对column对象调用as方法 df.select('id as "id2",$"name".as("n2"),col("age") as "age2").show() ​ // 在selectExpr中直接写sql的重命名语法 df.selectExpr("cast(id as string) as id2","name","city").show() ​ // 对dataframe调用withColumnRenamed方法对指定字段重命名 df.select("id","name","age").withColumnRenamed("id","id2").show() ​ // 对dataframe调用toDF对整个字段名全部重设 df.toDF("id2","name","age","city2","score").show()

（2）条件过滤

Scala /**   * 逐行过滤   */ df.where("id>4 and score>95") df.where('id > 4 and 'score > 95).select("id","name","age").show()

（4）分组聚合

Scala /**   * 分组聚合   */ df.groupBy("city").count().show() df.groupBy("city").min("score").show() df.groupBy("city").max("score").show() df.groupBy("city").sum("score").show() df.groupBy("city").avg("score").show() ​ df.groupBy("city").agg(("score","max"),("score","sum")).show() df.groupBy("city").agg("score"->"max","score"->"sum").show()

3.3.2.1 子查询

Scala /**   * 子查询   * 相当于:   * select   * *   * from   * (   *   select   *   city,sum(score) as score   *   from stu   *   group by city   * ) o   * where score>165   */ df.groupBy("city")   .agg(sum("score") as "score")   .where("score > 165")   .select("city", "score")   .show()

3.3.2.2 Join关联查询

Scala package cn.doitedu.sparksql ​ import org.apache.spark.sql.{DataFrame, SparkSession} ​ /**   *  用 DSL风格api来对dataframe进行运算  */ object Demo14_DML_DSLapi {   def main(args: Array[String]): Unit = { ​     val spark = SparkUtil.getSpark()     import spark.implicits._ ​     val df = spark.read.option("header",true).csv("data/stu2.csv")     val df2 = spark.read.option("header",true).csv("data/stu22.csv") ​     /**       * SQL中都有哪些运算？       *    1. 查询字段（id)       *    2. 查询表达式（算术运算，函数  age+10, upper(name)）       *    3. 过滤       *    4. 分组聚合       *    5. 子查询       *    6. 关联查询       *    7. union查询       *    8. 窗口分析       *    9. 排序       */ ​     //selectOp(spark,df)     //whereOp(spark,df)     //groupbyOp(spark,df)     joinOp(spark,df,df2) ​     spark.close()   }        /**     * 关联查询     * @param spark     * @param df1     */   def joinOp(spark:SparkSession,df1:DataFrame,df2:DataFrame): Unit ={ ​     // 笛卡尔积     //df1.crossJoin(df2).show() ​     // 给join传入一个连接条件； 这种方式，要求，你的join条件字段在两个表中都存在且同名     df1.join(df2,"id").show() ​     // 传入多个join条件列，要求两表中这多个条件列都存在且同名     df1.join(df2,Seq("id","sex")).show() ​ ​     // 传入一个自定义的连接条件表达式     df1.join(df2,df1("id") + 1 === df2("id")).show() ​ ​     // 还可以传入join方式类型： inner(默认)， left ,right, full ,left_semi, left_anti     df1.join(df2,df1("id")+1 === df2("id"),"left").show()     df1.join(df2,Seq("id"), "right").show() ​ ​     /**       * 总结:       * join方式：   joinType: String       * join条件：       * 可以直接传join列名：usingColumn/usingColumns : Seq(String) 注意：右表的join列数据不会出现结果中       *  可以传join自定义表达式： Column.+(1) === Column     df1("id")+1 === df2("id")       */   } }

​

3.3.2.3 Union操作

Scala Sparksql中的union，其实是union all df.union(df).show()

3.3.2.4 窗口分析函数调用

测试数据：

Plain Text id,name,age,sex,city,score 1,张飞,