Spark SQL

最新推荐文章于 2023-04-06 19:06:06 发布

炳烛之明科技

最新推荐文章于 2023-04-06 19:06:06 发布

阅读量264

点赞数

分类专栏： Java大数据

本文链接：https://blog.csdn.net/qq_40828705/article/details/103442175

版权

Java大数据专栏收录该内容

33 篇文章 0 订阅

订阅专栏

Spark SQL

一、概述

SQL：类似于Hive，数据仓库（Data WareHourse）工具，简化Spark应用开发

http://spark.apache.org/docs/latest/sql-programming-guide.html

Spark SQL是Spark中一个模块，用以对结构化数据进行处理。SparkSQL在RDD之上抽象出来Dataset/Dataframe 这两个类提供了类似RDD的功能，也就意味用户可以使用map、flatMap、filter等高阶算子，同时也通过了基于列的命名查询，也就是说Dataset/DataFrame提供了两套操作数据的API，这些API可以给Saprk引擎要提供更多信息，系统可以根据这些信息对计算实现一定的优化。

目前Spark SQL提供了两种交互方式：1） SQL 脚本，2） Dataset API (strong-typed类型、untyped类型操作)

Datasets & DataFrames

Dataset

Dataset类似于Spark RDD，代表的是一个分布式数据集。Spark RDD数据进行序列化传输处理时，使用的是Java序列化方案； Spark SQL DataSet

数据在进行序列化传输处理时，使用的是Encoder编码器，Spark SQL针对于编码器进行了大量执行优化。

Dataset是在spark-1.6提出新的API，该API构建在RDD（strong type，使用lambda表达式）之上同时可以借助于Spark SQL对执行引擎的优点，使得使用Dateset执行一些数据的转换比直接使用RDD算子功能和性能都有所提升。因此我们可以认为Python does not have the support for the Dataset API.

DataFrame

开发时，建议使用DF API

DataFrame 是特殊的Dataset。概念类似于数据库表，DataFrame包含了数据(一行行记录Row)和表结构(Schema)；通俗理解DataFrame 类似于Dataset[Row]

DataFrames 是Dataset的一种特殊情况。比如 Dataset中可以存储任意对象类型的数据作为Dataset的元素。但是Dataframe的元素只有一种类型Row类型，这种基于Row查询和传统数据库中ResultSet操作极其相似。因为Row类型的数据表示Dataframe的一个元素，类似数据库中的一行，这些行中的元素可以通过下标或者column name访问。由于Dateset是API的兼容或者支持度上不是多么的好，但是Dataframe在API层面支持的Scala、Java、R、Python支持比较全面。

总结：RDD —> Dataset （特殊的RDD）—> DataFrame（特殊的Dataset[Row]）

二、入门案列

导入开发依赖

<dependency>
    <groupId>org.apache.spark</groupId>
    <artifactId>spark-core_2.11</artifactId>
    <version>2.4.4</version>
</dependency>
<dependency>
    <groupId>org.apache.spark</groupId>
    <artifactId>spark-sql_2.11</artifactId>
    <version>2.4.4</version>
    <!--集群中运行打开，本地运行注释-->
    <!--<scope>provided</scope>-->
</dependency>

开发应用

package quickstart

import org.apache.spark.sql.{Dataset, SparkSession}

/**
  * spark sql的入门案列
  */
object SparkSqlExample1 {
  def main(args: Array[String]): Unit = {
    // RDD sc
    // Streaming  ssc
    // SQL SparkSession

    // 1. 初始化spark sql的应用入口对象 SparkSession
    val sparkSession = SparkSession.builder().appName("spark sql first example").master("local[*]").getOrCreate()

    // 2. 导入Spark SQL隐式转换
    import sparkSession.implicits._

    // 3. 构建Spark SQL数据源
    // Spark Sql数据集  rdd.toDS
    val rdd = sparkSession.sparkContext.makeRDD(List(("Hello", 1), ("Scala", 1), ("Spark", 1), ("Hello", 1)))

    val dataset: Dataset[(String, Int)] = rdd.toDS() // 隐式增强

    // 4. dataset进行操作
    // 方法1：函数操作   select word,sum(count) from t_word group by word;
    /*
    dataset
      .where("_1 != 'Scala'")
      .groupBy("_1") // _1 代表的RDD 元素的第一值 单词
      .sum("_2") // _2 sum聚合
      .withColumnRenamed("_1", "word")
      .withColumnRenamed("sum(_2)", "num")
      .show() // 展示结果集
    */

    // 方法2：SQL操作
    dataset.createOrReplaceTempView("t_word") // 对dataset起表名或者视图名
    sparkSession
      .sql("select _1 as word,sum(_2) as num from t_word where _1 != 'Scala' group by _1 order by num asc")
      .show()
    // 5. 释放资源
    sparkSession.close()
  }
}

三、Spark SQL Dataset和DataFrame构建方法

Dataset

Dataset类似于RDD，不同的是Spark SQL有一套自己的序列化规范独立于Spark RDD（Java/Kryo序列化）之上称为Encoders(编码器)。不同于SparkRDD序列化，由于Dataset支持无类型操作，用户无需获取操作的类型，操作仅仅是列名，因为Spark SQL在执行算子操作的时候可以省略反序列化的步骤，继而提升程序执行效率。

通过scala集合（元组）创建

// 通过scala的集合(元组)构建Dataset
val dataset = List((1,"zs",true,1000),(2,"ls",false,2000)).toDS()
dataset
	.show()  // _1 _2 _3 _4
//-------------------------------------------------------------
+---+---+-----+----+
| _1| _2|   _3|  _4|
+---+---+-----+----+
|  1| zs| true|1000|
|  2| ls|false|2000|
+---+---+-----+----+
//-------------------------------------------------------------

通过样例类(case class)创建

case class Person(name: String, sex: Boolean, address: String) // val 常量

// 通过样例类构建Dataset
val dataset = List(Person("zs",true,"bj"),Person("ls",false,"sh")).toDS()  // dataset中列名为样例类的属性名
dataset.show()

//-------------------------------------------------------------
+----+-----+-------+
|name|  sex|address|
+----+-----+-------+
|  zs| true|     bj|
|  ls|false|     sh|
+----+-----+-------+
//-------------------------------------------------------------

通过JSON文件创建

//-------------------------------------------------------------
{"id":1,"name": "zs","sex": true}
{"id":2,"name": "ls","sex": false}
{"id":3,"name": "ww","sex": true}
{"id":4,"name": "zl","sex": false}
//-------------------------------------------------------------

val dataset = spark.read.json("D:\\大数据训练营课程\\Code\\BigData\\sparksql-day1\\src\\main\\resources").as("user")

dataset.show()

//-------------------------------------------------------------
+---+----+-----+
| id|name|  sex|
+---+----+-----+
|  1|  zs| true|
|  2|  ls|false|
|  3|  ww| true|
|  4|  zl|false|
+---+----+-----+
//-------------------------------------------------------------

通过RDD创建

// Spark Sql数据集  rdd.toDS
val rdd = sparkSession.sparkContext.makeRDD(List(("Hello", 1), ("Scala", 1), ("Spark", 1), ("Hello", 1)))

val dataset: Dataset[(String, Int)] = rdd.toDS() // 隐式增强

通过DF转换获取

val dataset = dataframe.as("别名")

DataFrame

类似于Dataset

DataFrame是一个命名列的数据集，用户可以直接操作column 因此几乎所有Dataframe推荐操作都是无类型操作。用户也可以把一个Dataframe看做是 Dataset[Row] 类型的数据集。

通过JSON文件创建

// 通过JSON文件创建
val dataFrame = spark.read.json("D:\\大数据训练营课程\\Code\\BigData\\sparksql-day1\\src\\main\\resources")

// 数据
dataFrame.show()

// schema 结构
dataFrame.printSchema()

//-------------------------------------------------------------
+---+----+-----+
| id|name|  sex|
+---+----+-----+
|  1|  zs| true|
|  2|  ls|false|
|  3|  ww| true|
|  4|  zl|false|
+---+----+-----+

root
 |-- id: long (nullable = true)
 |-- name: string (nullable = true)
 |-- sex: boolean (nullable = true)
//-------------------------------------------------------------、

样例类创建

val userDF=List(User2(1,"zs",true)).toDF()
userDF.show()

//--------------------------------------------------------------------
+---+----+----+
| id|name| sex|
+---+----+----+
|  1|  zs|true|
+---+----+----+

CSV文件构建

// 通过csv文件创建  jdbc text orc parquet等创建方式
// csv相比于传统报表文件 excel 存放数量更大
val dataFrame = spark.read.csv("file:///d://a.csv")

// 数据
dataFrame.show()

// schema 结构
dataFrame.printSchema()
//--------------------------------------------------------------------
+---+---+-----+----+
|_c0|_c1|  _c2| _c3|
+---+---+-----+----+
|  1| zs|false|2000|
|  2| ls| true|3000|
+---+---+-----+----+

root
 |-- _c0: string (nullable = true)
 |-- _c1: string (nullable = true)
 |-- _c2: string (nullable = true)
 |-- _c3: string (nullable = true)

Tuple创建

// 通过Tuple创建
// val dataFrame = List((1,"zs"),(2,"ls")).toDF()  // 默认列名为 _1和_2
val dataFrame = List((1,"zs"),(2,"ls")).toDF("id","name")  // 对列起别名

// 数据
dataFrame.show()

// schema 结构
dataFrame.printSchema()

RDD创建

rdd.toDF(别名列表)

//************************************************************************************
// 通过RDD[Row]构建DF
val rdd = spark.sparkContext.makeRDD(List((1, "zs"), (2, "ls"))).map(t2 => Row(t2._1, t2._2)) //　RDD[(Int,String)] => RDD[Row]

val schema = new StructType()
.add("id", IntegerType)
.add("name", StringType)
//.add("sex", BooleanType)

val dataFrame = spark.createDataFrame(rdd, schema)

// 数据
dataFrame.show()

// schema 结构
dataFrame.printSchema()
//--------------------------------------------------------------------
+---+----+
| id|name|
+---+----+
|  1|  zs|
|  2|  ls|
+---+----+

root
 |-- id: integer (nullable = true)
 |-- name: string (nullable = true)

//************************************************************************************

// 通过JAVA Bean创建DF
# 注意 Java Bean必须实现序列化接口 因为分布式计算中数据需要进行网络传输
val rdd =spark.sparkContext.makeRDD(List(new Student(1,"zs",true),new Student(2,"ww",false)))

val dataFrame = spark.createDataFrame(rdd,classOf[Student])

// 数据
dataFrame.show()

// schema 结构
dataFrame.printSchema()
//--------------------------------------------------------------------
+---+----+-----+
| id|name|  sex|
+---+----+-----+
|  1|  zs| true|
|  2|  ww|false|
+---+----+-----+

root
 |-- id: integer (nullable = true)
 |-- name: string (nullable = true)
 |-- sex: boolean (nullable = true)

通过DS转换获取

// 通过DS转换获取
val dataset = List((1,"zs"),(2,"ls")).toDS()

val dataFrame = dataset.toDF("id","name")

+---+----+
| id|name|
+---+----+
|  1|  zs|
|  2|  ls|
+---+----+

root
 |-- id: integer (nullable = false)
 |-- name: string (nullable = true)

四、Spark SQL操作语法

操作分为两类：untyped（无类型操作）和strong typed（强类型操作）

untyped（无类型操作）：操作字段或者列名，不会类型信息
strong typed（强类型操作）: 操作对象，需要通过函数自定义操作规则; 在实际开发中，强类型几乎不会使用；

Strong Typed(了解)

DataSet

数据集

val spark = SparkSession.builder().appName("strong typed opt").master("local[*]").getOrCreate()

import spark.implicits._

val dataset = List(
    (1, "zs", true, "A", 2000),
    (2, "ls", false, "A", 3000),
    (3, "ww", true, "B", 4000),
    (4, "zl", false, "B", 5000),
    (5, "tq", true, "A", 6000)
).toDS()

// 强类型操作  统计每一个部门的男性员工工资总和
dataset
    .filter(t5 => t5._3 == true)
    .groupByKey(t5 => t5._4) // 操作对象
    .agg(typed.sum(t5 => t5._5))
    .withColumnRenamed("value","dept")
    .withColumnRenamed("TypedSumDouble(scala.Tuple5)","total")
    .show()

//---------------------------------------------------------------------
+----+------+
|dept| total|
+----+------+
|   B|4000.0|
|   A|8000.0|
+----+------+

DataFrame

数据帧

val spark = SparkSession.builder().appName("strong typed opt").master("local[*]").getOrCreate()

import spark.implicits._

val dataframe = List(
    (1, "zs", true, "A", 2000),
    (2, "ls", false, "A", 3000),
    (3, "ww", true, "B", 4000),
    (4, "zl", false, "B", 5000),
    (5, "tq", true, "A", 6000)
).toDF("id", "name", "sex", "dept", "salary")

// 强类型操作  统计每一个部门的男性员工工资总和
dataframe
    .filter(row => row.getBoolean(2) == true) // 通过下标获取第三列数据
    .groupByKey(row => row.getString(3)) // 操作对象
    .agg(typed.sum(row => row.getInt(4)))
    .withColumnRenamed("value", "dept")
    .withColumnRenamed("TypedSumDouble(org.apache.spark.sql.Row)", "total")
    .show()

spark.close()
//---------------------------------------------------------------------
+----+------+
|dept| total|
+----+------+
|   B|4000.0|
|   A|8000.0|
+----+------+

Untyped（函数）

Dataset

和DataFrame相同的

DataFrame

printSchema()

打印DataFrame 表结构 StructType

show

展示DataFrame数据，默认是前20行

df.show()
df.show(n) // n为前n行数据

select

投影查询，指定字段查询 select id,name from t_user

val spark = SparkSession.builder().appName("strong typed opt").master("local[*]").getOrCreate()

import spark.implicits._

val dataFrame = List(
    (1, "zs", true, "A", 2000),
    (2, "ls", false, "A", 3000),
    (3, "ww", true, "B", 4000),
    (4, "zl", false, "B", 5000),
    (5, "tq", true, "A", 6000)
).toDF("id", "name", "sex", "dept", "salary")

dataFrame
	//.select("id","name")  // 如果不添加select，则查所有列的数据
    .select($"id", $"name", $"dept") // $ 是方法名，可以字符串的列名 包装为Column对象
    .show()
//---------------------------------------------------------------------
+---+----+----+
| id|name|dept|
+---+----+----+
|  1|  zs|   A|
|  2|  ls|   A|
|  3|  ww|   B|
|  4|  zl|   B|
|  5|  tq|   A|
+---+----+----+

selectExpr

投影查询 + 表达式

dataFrame      
    //.selectExpr("id", "name as uname", "dept as udept")  // selectExpr 投影查询 并起别名并起别名
    //.selectExpr("count(id)") // selectExpr 聚合函数
    .selectExpr("id", "name", "sex", "salary", "salary * 12 as yearSalary") // selectExpr 聚合函数
    .show()
//---------------------------------------------------------------------
+---+----+-----+------+----------+
| id|name|  sex|salary|yearSalary|
+---+----+-----+------+----------+
|  1|  zs| true|  2000|     24000|
|  2|  ls|false|  3000|     36000|
|  3|  ww| true|  4000|     48000|
|  4|  zl|false|  5000|     60000|
|  5|  tq| true|  6000|     72000|
+---+----+-----+------+----------+

withColumn()

添加新列或者替换已有的列

dataFrame
    .selectExpr("id", "name", "sex", "salary")
    //.withColumn("yearSalary", $"salary" * 12) // 新增列, 结果表中新增一列数据为年度工资
    .withColumn("salary", $"salary" * 12) // 替换列, 结果表中使用新的计算结果替换salary列的内容
    .show()
//---------------------------------------------------------------------
+---+----+-----+------+
| id|name|  sex|salary|
+---+----+-----+------+
|  1|  zs| true| 24000|
|  2|  ls|false| 36000|
|  3|  ww| true| 48000|
|  4|  zl|false| 60000|
|  5|  tq| true| 72000|
+---+----+-----+------+

withColumnRenamed()

对列起别名

df.withColumnRenamed("列名","别名")

drop

删除已存在列

dataFrame
    .selectExpr("id", "name", "sex", "salary")
    //.withColumn("yearSalary", $"salary" * 12) // 新增列, 结果表中新增一列数据为年度工资
    .withColumn("salary", $"salary" * 12) // 替换列, 结果表中使用新的计算结果替换salary列的内容
    //.drop("sex")  // 保留id name salary
    .drop($"sex") // or
    .show()
//------------------------------------------------------------
+---+----+------+
| id|name|salary|
+---+----+------+
|  1|  zs| 24000|
|  2|  ls| 36000|
|  3|  ww| 48000|
|  4|  zl| 60000|
|  5|  tq| 72000|
+---+----+------+

dropDuplicates()

删除重复的数据，类似于数据库中distinct【重复数据只保留一个】

dataFrame
    //.selectExpr("sex", "dept")
    .dropDuplicates("sex","dept")  // 重复数据只保留1个
    //.dropDuplicates("sex")  // 重复数据只保留1个
    .show()
//------------------------------------------------------------
+---+----+-----+----+------+
| id|name|  sex|dept|salary|
+---+----+-----+----+------+
|  1|  zs| true|   A|  2000|
|  4|  zl|false|   B|  5000|
|  3|  ww| true|   B|  4000|
|  2|  ls|false|   A|  3000|
+---+----+-----+----+------+

OrderBy()| Sort()

结果排序

升序： asc

降序： desc

dataFrame
    //.orderBy($"id" desc)
    .sort($"salary" desc)
    .show()
//------------------------------------------------------------
+---+----+-----+----+------+
| id|name|  sex|dept|salary|
+---+----+-----+----+------+
|  5|  tq| true|   A|  6000|
|  4|  zl|false|   B|  5000|
|  3|  ww| true|   B|  4000|
|  2|  ls|false|   A|  3000|
|  1|  zs| true|   A|  2000|
+---+----+-----+----+------+

GroupBy ()

根据指定字段，进行分组操作，并且分组后需要进行聚合操作

dataFrame
    .groupBy($"dept")
    .sum("salary")
    .show()
//------------------------------------------------------------
|dept|sum(salary)|
+----+-----------+
|   B|       9000|
|   A|      11000|
+----+-----------+

agg()

聚合操作， sum＼avg\min\max\count

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

dataFrame
    .groupBy($"dept")
    //.sum("salary")
    .agg(max("salary"), min("salary"), count("salary"), sum("salary"), avg("salary"))
    .show()
//------------------------------------------------------------
+----+-----------+-----------+-------------+-----------+------------------+
|dept|max(salary)|min(salary)|count(salary)|sum(salary)|       avg(salary)|
+----+-----------+-----------+-------------+-----------+------------------+
|   B|       5000|       4000|            2|       9000|            4500.0|
|   A|       6000|       2000|            3|      11000|3666.6666666666665|
+----+-----------+-----------+-------------+-----------+------------------+

limit()

限制返回结果条数

dataFrame
    .sort($"id" desc)
    .limit(2) // 限制2条
    .show() // 默认返回所有数据
//------------------------------------------------------------
+---+----+-----+----+------+
| id|name|  sex|dept|salary|
+---+----+-----+----+------+
|  5|  tq| true|   A|  6000|
|  4|  zl|false|   B|  5000|
+---+----+-----+----+------+

where()

过滤条件

模糊查询等值查询条件查询

like ‘%s%’ id = 1 salary > 3000

dataFrame
    // select * from t_user where id <= 5 and name like '%s%' or name = 'ww'
    .where($"id" <= 5 and ($"name" like "%s%") or ($"name" === "ww"))
    .show()
//------------------------------------------------------------
+---+----+-----+----+------+
| id|name|  sex|dept|salary|
+---+----+-----+----+------+
|  1|  zs| true|   A|  2000|
|  2|  ls|false|   A|  3000|
|  3|  ww| true|   B|  4000|
+---+----+-----+----+------+

pivot() 【透视】

重要行转列

反透视：列转行

// 如： 订单数据 格式如下
val df = List(
    (2018, 1, 1000),
    (2018, 1, 2000),
    (2018, 2, 1500),
    (2018, 3, 1600),
    (2018, 3, 3000)
).toDF("year", "month", "total")

// 需求： 用户在2018年每个月消费情况

df
    .groupBy("year", "month")
    .pivot("month")  // 行转列
    .sum("total")
    .show()

//--------------------------------------------------------------------------
+----+-----+----+----+----+
|year|month|   1|   2|   3|
+----+-----+----+----+----+
|2018|    1|3000|null|null|
|2018|    3|null|null|4600|
|2018|    2|null|1500|null|
+----+-----+----+----+----+

na()

对空值(null) 处理方法

drop : 丢弃空值行
fill ：填充，默认值

df
    .groupBy("year", "month")
    .pivot("month") // 行转列
    .sum("total")
    .na
    //.fill(Map(("1", 0), ("2", 0))) // 填充方法
    //.fill(0)
    .drop() // 不需要添加任何的参数  表示删除null值所在行
    .show()

join()

表连接

val userInfoDF = spark.sparkContext.makeRDD(List((1, "zs"), (2, "ls"), (3, "ww"))).toDF("id", "name")

val orderInfoDF = spark.sparkContext.makeRDD(List((1, "iphone", 1000, 1), (2, "mi9", 999, 1), (3, "连衣裙", 99, 2))).toDF("oid", "product", "price", "id")

userInfoDF
    .join(orderInfoDF, $"id" === $"uid", "left_outer") // 默认inner
    //.join(orderInfoDF, List("id"), "left_outer") // 默认inner
    .show()

over()

窗口(开窗)函数

作用：窗口函数使用over，对一组数据进行操作，返回普通列和聚合列

DB 聚合查询： sum count avg 返回一行结果

窗口函数分为3大类:

聚合函数
排名函数
分析函数

语法：窗口函数类型 over( partition by 分组字段 [order by 排序条件 ...限制范围])

具体使用方法：
count(...) over(partition by ... order by ...) --求分组后的总数。
sum(...) over(partition by ... order by ...)   --求分组后的和。
max(...) over(partition by ... order by ...)--求分组后的最大值。
min(...) over(partition by ... order by ...)--求分组后的最小值。
avg(...) over(partition by ... order by ...)--求分组后的平均值。
rank() over(partition by ... order by ...)--rank值可能是不连续的。  1 2 3 4 4 4 7
dense_rank() over(partition by ... order by ...)--rank值是连续的。 1 2 3 4 4 4 5
first_value(...) over(partition by ... order by ...)--求分组内的第一个值。
last_value(...) over(partition by ... order by ...)--求分组内的最后一个值。
lag() over(partition by ... order by ...)--取出前n行数据。　　
lead() over(partition by ... order by ...)--取出后n行数据。
ratio_to_report() over(partition by ... order by ...) --Ratio_to_report() 括号中就是分子，over() 括号中就是分母。
percent_rank() over(partition by ... order by ...)

val df2 = List(
    (1, "zs", true, 1, 15000),
    (2, "ls", false, 2, 18000),
    (3, "ww", false, 2, 14000),
    (4, "zl", false, 1, 18000),
    (5, "win7", false, 1, 16000))
.toDF("id", "name", "sex", "dept", "salary")


//************************************************************************************
# 使用窗口函数  每一个用户新增聚合列 代表的当前用户所在部门的员工的最高工资
1  "zs"  true  1  15000  18000
2  "ls"  false 2  18000  18000
2  "ww"  false 2  14000  18000

val df2 = List(
    (1, "zs", true, 1, 15000),
    (2, "ls", false, 2, 17000),
    (3, "ww", false, 2, 14000),
    (4, "zl", false, 1, 18000),
    (5, "win7", false, 1, 16000))
.toDF("id", "name", "sex", "dept", "salary")
import org.apache.spark.sql.functions._

val w = Window
    .partitionBy($"dept")
	
df2
    .withColumn("dept_salary_max", max("salary") over (w))
    .show()

//************************************************************************************
val df2 = List(
    (1, "zs", true, 1, 15000),
    (2, "ls", false, 2, 17000),
    (3, "ww", false, 2, 14000),
    (4, "zl", false, 1, 18000),
    (5, "win7", false, 1, 16000))
.toDF("id", "name", "sex", "dept", "salary")
import org.apache.spark.sql.functions._

val w = Window
    .partitionBy($"dept")
    .orderBy($"salary" desc)
    // .rowsBetween(Window.unboundedPreceding,Window.unboundedFollowing) // 控制视图可视范围的 LONG.MIN_VALUE LONG.MIN_VALUE
    .rowsBetween(-1, 0) // 0 基准行  -1 基准行的上一行， 窗口内数据可视范围

df2
    .withColumn("dept_salary_max", max("salary") over (w))
    .show()

#图片

cube()

多维度，多维度分组

cube(A,B) // 通过A,B各种可能进行分组操作

分析：

A null

null B

A B

null null

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

List(
    (110,50,80,80),
    (120,60,95,75),
    (120,50,96,70))
.toDF("height","weight","IQ","EQ")
.cube($"height",$"weight") // 尝试进行多维度分组, 好处：不论使用什么样多维度查询指标，都可以cube结果表获取聚合结果
.agg(avg("IQ"),avg("EQ"))
.show()
						  // sql1: group by A  聚合查询
						  // sql2: group by A,B 聚合查询
						  // cube: group by A or B or A,B
//-----------------------------------------------------------------
(110,50)
(120,60)
(120,50)

分组1:  110 null
分组2： null 50
分组3： 110 50
分组4： 120 null
分组5： null 60
分组6： 120 60
分组7： 120 50
分组8： null null

+------+------+-----------------+-------+
|height|weight|          avg(IQ)|avg(EQ)|
+------+------+-----------------+-------+
|   110|    50|             80.0|   80.0|
|   120|  null|             95.5|   72.5|
|   120|    60|             95.0|   75.0|
|  null|    60|             95.0|   75.0|
|  null|  null|90.33333333333333|   75.0|
|   120|    50|             96.0|   70.0|
|   110|  null|             80.0|   80.0|
|  null|    50|             88.0|   75.0|
+------+------+-----------------+-------+

Untyped（纯SQL）

建议在工作中使用DataFrame的纯SQL操作（无类型的）

普通查询

package untyped.sql

import org.apache.spark.sql.SparkSession

/**
  * 重点
  */
object DataFrameSQLOpt {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder().appName("sql opt").master("local[*]").getOrCreate()

    import spark.implicits._

    val dataFrame = List(
      (1, "zs", true, "A", 2000),
      (2, "ls", true, "B", 1000),
      (3, "ww", false, "B", 8000),
      (4, "zl", true, "A", 5000)
    ).toDF("id", "name", "sex", "dept", "salary")

    // 给dataFrame起别名(视图名)
    // GlobalTempView  创建全局视图，跨多个spark session会话，有效范围是整个Application
    // TempView  创建会话视图，只能够被创建它的spark session会话使用，有效范围是SparkSession
    // createOrReplace  如果视图存在则替换 如果不存在创建
    // 注意：TempView 会话视图 视图表会存放在default数据库中；
    //      GlobalTempView 全局视图 视图表存放在global_temp数据库中

    // 视图使用方法
    // dataFrame.createGlobalTempView("tt_user")
    // dataFrame.createTempView("tt_user")
    /*
    spark
      //.sql("select * from tt_user")
      .sql("select * from global_temp.tt_user")  // 操作全局视图
      .show()
    */

    //--------------------------------------------------------------------
    val newSpark = spark.newSession()

    // 全局视图范围测试
    // dataFrame.createGlobalTempView("tt_user")
    // spark.sql("select * from global_temp.tt_user").show()
    // newSpark.sql("select * from global_temp.tt_user").show()

    // 会话视图范围测试
    dataFrame.createTempView("tt_user")
    spark.sql("select * from tt_user").show()
    newSpark.sql("select * from tt_user").show()

    spark.close()
    newSpark.close()
  }
}

给dataFrame起别名(视图名)

GlobalTempView 创建全局视图，跨多个spark session会话，有效范围是整个Application
TempView 创建会话视图，只能够被创建它的spark session会话使用，有效范围是SparkSession
createOrReplace 如果视图存在则替换如果不存在创建
注意：
TempView 会话视图视图表会存放在default数据库中；
GlobalTempView 全局视图视图表存放在global_temp数据库中
# 视图使用方法
dataFrame.createGlobalTempView("tt_user")
dataFrame.createTempView("tt_user")

条件查询

# 模糊查询 & 等值查询 & 条件范围查询
val dataFrame = List(
      (1, "zs", true, "A", 2000),
      (2, "ls", true, "B", 1000),
      (3, "ww", false, "B", 8000),
      (4, "zl", true, "A", 5000)
    ).toDF("id", "name", "sex", "dept", "salary")

    dataFrame.createOrReplaceTempView("t_user")

    spark
      .sql( // """ scala 字符串拼接语法
        """
          | select
          |   *
          | from
          |   t_user
          | where
          |   name
          | like
          |   '%z%'
          | and
          |   id = 1
          | or
          |   dept = 'B'
        """.stripMargin)
      .show()
//------------------------------------------------------------------
+---+----+-----+----+------+
| id|name|  sex|dept|salary|
+---+----+-----+----+------+
|  1|  zs| true|   A|  2000|
|  2|  ls| true|   B|  1000|
|  3|  ww|false|   B|  8000|
+---+----+-----+----+------+

排序查询

spark
      .sql( // """ scala 字符串拼接语法
        """
          | select
          |   *
          | from
          |   t_user
          | where
          |   name
          | like
          |   '%z%'
          | and
          |   id = 1
          | or
          |   dept = 'B'
          | order by
          |   id desc
        """.stripMargin)
      .show()
//------------------------------------------------------------------
+---+----+-----+----+------+
| id|name|  sex|dept|salary|
+---+----+-----+----+------+
|  3|  ww|false|   B|  8000|
|  2|  ls| true|   B|  1000|
|  1|  zs| true|   A|  2000|
+---+----+-----+----+------+

分组过滤查询

//****************************************************************
// 每个部门不同性别的平均工资, 过滤条件(排除) dept = B and sex=false
spark
.sql(
    """
          | select
          |   dept,sex,avg(salary)
          | from
          |   t_user
          | group by
          |   dept,sex
          | having
          |    dept != 'B'
          |   and
          |    sex != false
        """.stripMargin)
.show()
//------------------------------------------------------------------
+----+----+-----------+
|dept| sex|avg(salary)|
+----+----+-----------+
|   A|true|     3500.0|
+----+----+-----------+

Limit

限制返回结果条数，数据分析中不要分页

spark
      .sql(
        """
          | select
          |   dept,sex,avg(salary) as avg_salary
          | from
          |   t_user
          | group by
          |   dept,sex
          | having
          |    dept != 'A'
          | order by avg_salary asc
          | limit 1
        """.stripMargin)
      .show()
//------------------------------------------------------------------
+----+----+----------+
|dept| sex|avg_salary|
+----+----+----------+
|   B|true|    1000.0|
+----+----+----------+

case … when … else … end语句

类似于Java中学习过的Switch语句，根据不同条件进行不同的逻辑处理

val dataFrame = List(
      (1, "zs", true, "A", 2000),
      (2, "ls", true, "B", 1000),
      (3, "ww", false, "B", 8000),
      (4, "zl", true, "A", 5000)
    ).toDF("id", "name", "sex", "dept", "salary")

spark
      .sql(
        """
          |select
          |	id,name,dept,salary,
          |	case sex
          |		when true
          |			then '男'
          |		when false
          |			then '女'
          |		else
          |			'中性'
          |	end
          | as newSex
          |from
          |	t_user
        """.stripMargin)
      .show()

+---+----+----+------+------+
| id|name|dept|salary|newSex|
+---+----+----+------+------+
|  1|  zs|   A|  2000|    男|
|  2|  ls|   B|  1000|    男|
|  3|  ww|   B|  8000|    女|
|  4|  zl|   A|  5000|    男|
+---+----+----+------+------+

Pivot(行转列)

透视语法，将行数据处理列数据

//****************************************************************
// pivot (透视)
val scoreDF = List(
    (1, "语文", 100),
    (1, "数学", 100),
    (1, "英语", 100),
    (2, "数学", 79),
    (2, "语文", 80),
    (2, "英语", 100),
    (2, "英语", 120))
.toDF("id", "course", "score")
scoreDF.createOrReplaceTempView("t_course")

spark
.sql(
    """
          | select
          |   *
          | from
          |   t_course
          | pivot(sum(score) for course in('语文','数学','英语'))
        """.stripMargin) //　透视语法中缺省字段会作为分组依据
.show()

//-------------------------------------------------------------------
+---+----+----+----+
| id|语文|数学|英语|
+---+----+----+----+
|  1| 100| 100| 100|
|  2|  80|  79| 220|
+---+----+----+----+

Cube(多维度分组)

多维度分组， cube(A,B)

val df = List(
    (110, 50, 80, 80),
    (120, 60, 95, 75),
    (120, 50, 96, 70))
.toDF("height", "weight", "IQ", "EQ")
df.createOrReplaceTempView("t_user_info")

spark
.sql(
    """
          | select
          |   height,weight,avg(IQ),avg(EQ)
          | from
          |   t_user_info
          | group by
          |   cube(height,weight)
        """.stripMargin)
.show()

//-----------------------------------------------------------------------
+------+------+-----------------+-------+
|height|weight|          avg(IQ)|avg(EQ)|
+------+------+-----------------+-------+
|   110|    50|             80.0|   80.0|
|   120|  null|             95.5|   72.5|
|   120|    60|             95.0|   75.0|
|  null|    60|             95.0|   75.0|
|  null|  null|90.33333333333333|   75.0|
|   120|    50|             96.0|   70.0|
|   110|  null|             80.0|   80.0|
|  null|    50|             88.0|   75.0|
+------+------+-----------------+-------+

Join表连接查询

类似于DB的表连接操作： innner join， outer join

// join
val userInfoDF = List((1, "zs"), (2, "ls"), (3, "ww"))
.toDF("id", "name")
val orderInfoDF = List((1, "iphone", 1000, 1), (2, "mi9", 999, 1), (3, "连衣裙", 99, 2))
.toDF("oid", "product", "price", "uid")

userInfoDF.createTempView("t_user")
orderInfoDF.createTempView("t_order")

spark
.sql(
    """
          |select
          |   *
          |from
          |  t_user t1
          |left join
          |  t_order t2
          |on
          | t1.id = t2.uid
        """.stripMargin)
.show()

//--------------------------------------------------------------------
+---+----+----+-------+-----+----+
| id|name| oid|product|price| uid|
+---+----+----+-------+-----+----+
|  1|  zs|   2|    mi9|  999|   1|
|  1|  zs|   1| iphone| 1000|   1|
|  2|  ls|   3| 连衣裙|   99|   2|
|  3|  ww|null|   null| null|null|
+---+----+----+-------+-----+----+

子查询

类似于数据库子查询操作

//****************************************************************
val dataFrame = List(
    (1, "zs", true, "A", 2000),
    (2, "ls", true, "B", 1000),
    (3, "ww", false, "B", 8000),
    (4, "zl", true, "A", 5000)
).toDF("id", "name", "sex", "dept", "salary")

dataFrame.createOrReplaceTempView("t_user")

spark
.sql(
    """
          |
          | select
          |   id,name
          | from
          |   (select id,name,sex from t_user) t_new_user
        """.stripMargin)
.show()

//-----------------------------------------------------------------------
+---+----+
| id|name|
+---+----+
|  1|  zs|
|  2|  ls|
|  3|  ww|
|  4|  zl|
+---+----+

窗口函数

作用：对一组数据进行操作，返回普通列和聚合列

语法：

窗口函数名() over([partition by 分区字段] order by 字段 asc | desc [range | rows between unbounded preceding and unbounded following]）

val df2 = List(
    (1, "zs", true, 1, 15000),
    (2, "ls", false, 2, 18000),
    (3, "ww", false, 2, 14000),
    (4, "zl", false, 1, 18000),
    (5, "win7", false, 1, 16000))
.toDF("id", "name", "sex", "dept", "salary")


//************************************************************************************
# 使用窗口函数  每一个用户新增聚合列 代表的当前用户所在部门的员工的最高工资
1  "zs"  true  1  15000  18000
2  "ls"  false 2  18000  18000
2  "ww"  false 2  14000  18000

select
	id,name,sex,dept,salary,
	max(salary) over(partition by dept order by salary desc rows between unbounded preceding and unbounded following) as dept_max_salary
from
	t_user
//************************************************************************************

val df2 = List(
      (1, "zs", true, 1, 15000),
      (2, "ls", false, 2, 17000),
      (3, "ww", false, 2, 14000),
      (4, "zl", false, 1, 18000),
      (6, "zl2", false, 1, 18000),
      (5, "win7", false, 1, 16000))
      .toDF("id", "name", "sex", "dept", "salary")

    df2.createOrReplaceTempView("t_user")

    // 窗口函数1： 当前行为基准在  分区内数据可视范围  unbounded preceding(-2^63) and unbounded following (2^63-1)
    // 窗口函数2： 当前行为基准在  分区内数据可视范围  rowsBetween(start = -1,end = 1)  1 preceding and 1 following
    // 窗口函数3： 分区内数据可视范围  rowsBetween(start = -1,end = 0) 当前行 + 上一行
    // 窗口函数4： 分区内数据可视范围  rowsBetween(start = 0,end = 1) 当前行 + 下一行
    // 窗口函数5： 分区内数据可视范围  rangeBetween(start,end)  通过排序字段 动态计算范围区间[基准行的排序字段的值 - start,基准行的排序字段的值 + end]
    // 窗口函数6： 排行窗口函数（非连续排名）
    // 窗口函数7： 排行窗口函数（连续排名）
    // 窗口函数8： lead(列名,n) over(partition by ... order by ...)-- 取出基准行后n行数据作为聚合列的结果。
    // 窗口函数9： lag(列名,n) over(partition by ... order by ...)-- 取出基准行前n行数据作为聚合列的结果。
    spark
      .sql(
        """
          |select
          |	id,name,sex,dept,salary,
          |	  max(salary) over(partition by dept order by salary desc rows between unbounded preceding and unbounded following) as dept_max_salary,
          |	  max(salary) over(partition by dept order by salary desc rows between 1 preceding and 1 following) as dept_max_salary2,
          |	  max(salary) over(partition by dept order by salary desc rows between 1 preceding and 0 following) as dept_max_salary3,
          |	  max(salary) over(partition by dept order by salary desc rows between 0 preceding and 1 following) as dept_max_salary4,
          |	  max(salary) over(partition by dept order by salary desc range between 2000 preceding and 2000 following) as dept_max_salary5,
          |	  rank() over(partition by dept order by salary desc) as rank1,
          |	  dense_rank() over(partition by dept order by salary desc) as rank2,
          |	  lead(salary,2) over(partition by dept order by salary desc) as next,
          |	  lag(salary,1) over(partition by dept order by salary desc) as before
          |from
          |	  t_user
        """.stripMargin)
      .show()

//------------------------------------------------------------------------------------
略

五、UDF（用户自定义函数）

用户自定义函数

Spark SQL内置大量单行函数和聚合函数，如果内置函数不能满足需求时，自定义UDF，进行功能扩展

单行函数

对每一行数据应用函数操作，返回结果；如：upper | lower | length等

package function

import org.apache.spark.sql.SparkSession

object UDFTest1 {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder().appName("sql opt").master("local[*]").getOrCreate()

    import spark.implicits._

    val dataFrame = List(
      (1, "zs", true, "A", 2000),
      (2, "ls", true, "B", 1000),
      (3, "ww", false, "B", 8000),
      (4, "zl", true, "A", 5000)
    ).toDF("id", "name", "sex", "dept", "salary")

    dataFrame.createOrReplaceTempView("t_user")

    // select id,name,covert_sex(sex)

    // 自定义UDF(单行函数)，将用户性别转换为“男”或者“女”
    spark.udf.register("convert_sex", (sex: Boolean) => {
      sex match {
        case true => "男"
        case false => "女"
        case _ => "性别中立"
      }
    }) // 函数体

    spark.sql("select id,name,convert_sex(sex) from t_user").show()

    spark.close()
  }
}

聚合函数

对一组数据进行聚合操作，返回一行结果；

sum avg count max min

// 自定义聚合函数，实现等价于sum操作

package function

import org.apache.spark.sql.{Row, SparkSession}
import org.apache.spark.sql.expressions.{MutableAggregationBuffer, UserDefinedAggregateFunction}
import org.apache.spark.sql.types.{DataType, IntegerType, StructType}

object UDFTest2 {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder().appName("sql opt").master("local[*]").getOrCreate()

    import spark.implicits._

    val dataFrame = List(
      (1, "zs", true, "A", 2000),
      (2, "ls", true, "B", 1000),
      (3, "ww", false, "B", 8000),
      (4, "zl", true, "A", 5000)
    ).toDF("id", "name", "sex", "dept", "salary")

    dataFrame.createOrReplaceTempView("t_user")

    // select id,name,covert_sex(sex)

    // 自定义UDF(聚合函数) my_sum(salary)

    spark.udf.register("my_sum", new UserDefinedAggregateFunction {
      /**
        * my_sum(salary,sex)
        * 聚合函数参数列表的结构
        *
        * @return
        */
      override def inputSchema: StructType = new StructType().add("salary", IntegerType)


      /**
        * 缓冲区结构对象  缓冲区 主要是用来存放聚合中间结果的
        *
        * row1:salary + row2:salary + ... row100:salary
        *
        * @return
        */
      override def bufferSchema: StructType = new StructType().add("total", IntegerType)

      /**
        * 聚合函数的返回值的类型
        *
        * @return
        */
      override def dataType: DataType = IntegerType


      /**
        * 确定：聚合函数的参数类型和聚合结果的类型是否匹配(一致)
        *
        * @return
        */
      override def deterministic: Boolean = true

      /**
        * 初始化方法：初始化聚合操作使用缓冲区的
        *
        * @param buffer
        */
      override def initialize(buffer: MutableAggregationBuffer): Unit = {
        // 第一个0：第一参数  第二个0：聚合的初始值0
        buffer.update(0, 0)
      }

      /**
        * 更新方法 my_sum(salary)
        *
        * @param buffer 缓冲区
        * @param input  行   row.salary + 缓冲区.sumSalary
        */
      override def update(buffer: MutableAggregationBuffer, input: Row): Unit = {
        val bufferValue = buffer.getInt(0)
        val currentValue = input.getInt(0)
        // 使用最新的聚合结果 替换buffer缓冲区中历史聚合结果
        buffer.update(0, bufferValue + currentValue)
      }

      /**
        * 合并
        *
        * @param buffer1
        * @param buffer2
        */
      override def merge(buffer1: MutableAggregationBuffer, buffer2: Row): Unit = {
        buffer1.update(0, buffer1.getInt(0) + buffer2.getInt(0))
      }

      /**
        * 评估方法(返回聚合结果的)
        *
        * @param buffer
        * @return
        */
      override def evaluate(buffer: Row): Any = buffer.getInt(0)
    })

    // spark.sql("select my_sum(salary) from t_user").show()
    spark.sql("select sex,my_sum(salary) from t_user group by sex").show()

    spark.close()
  }
}

六、数据的Load&Save

JSON

// json文件格式的写入写出
spark.sql("select id,name,sex,salary from t_user").write.json("file:///d://json")

val df = spark.read.json("file:///d://json")
df.show()

Parquet

Spark 默认文件格式，Parquet文件格式基于列存储；数据在底层会编码成二进制数据

// 写出写入文件格式paquet

spark.sql("select id,name,sex,salary from t_user").write.save("file:///d://parquet") 

val df = spark.read.parquet("file:///d://parquet").show()

ORC

矢量化文件格式，底层会将数据压缩处理，比较节省磁盘空间

spark.sql("select id,name,sex,salary from t_user").write.orc("file:///d://orc")
val df = spark.read.orc("file:///d://orc").show()

CSV

// csv文件
//spark.sql("select id,name,sex,salary from t_user").write.csv("file:///d://csv")
/*
    spark.sql("select id,name,sex,salary from t_user").write.format("csv")
      .option("sep", "|")
      .option("inferSchema", "true")
      .option("header", "true")
      .save("file:///D://csv2")
    */

spark.read.format("csv")
    .option("sep", "|")
    .option("inferSchema", "true")
    .option("header", "true")
    .load("file:///D://csv2")
    .show()

JDBC

// 导入mysql 依赖
/*
    spark
      .sql("SELECT * FROM t_user")
      .write
      .format("jdbc")
      .mode(SaveMode.Overwrite) // 覆盖
      .option("user", "root")
      .option("password", "1234")
      .option("url", "jdbc:mysql://localhost:3306/test")
      .option("dbtable", "t_user")
      .save()
    */

val df = spark
    .read
    .format("jdbc")
    .option("user", "root")
    .option("password", "1234")
    .option("url", "jdbc:mysql://localhost:3306/test")
    .option("dbtable", "t_user")
    .load()
df.show()

DF转换为RDD写出

df.rdd.foreach(row => {
    println(row.getInt(0) + "\t" + row.getString(1))
})

RDD转换为DF

rdd.toDF

rquet文件格式基于列存储；数据在底层会编码成二进制数据

// 写出写入文件格式paquet

spark.sql("select id,name,sex,salary from t_user").write.save("file:///d://parquet") 

val df = spark.read.parquet("file:///d://parquet").show()

ORC

矢量化文件格式，底层会将数据压缩处理，比较节省磁盘空间

spark.sql("select id,name,sex,salary from t_user").write.orc("file:///d://orc")
val df = spark.read.orc("file:///d://orc").show()

CSV

// csv文件
//spark.sql("select id,name,sex,salary from t_user").write.csv("file:///d://csv")
/*
    spark.sql("select id,name,sex,salary from t_user").write.format("csv")
      .option("sep", "|")
      .option("inferSchema", "true")
      .option("header", "true")
      .save("file:///D://csv2")
    */

spark.read.format("csv")
    .option("sep", "|")
    .option("inferSchema", "true")
    .option("header", "true")
    .load("file:///D://csv2")
    .show()

JDBC

// 导入mysql 依赖
/*
    spark
      .sql("SELECT * FROM t_user")
      .write
      .format("jdbc")
      .mode(SaveMode.Overwrite) // 覆盖
      .option("user", "root")
      .option("password", "1234")
      .option("url", "jdbc:mysql://localhost:3306/test")
      .option("dbtable", "t_user")
      .save()
    */

val df = spark
    .read
    .format("jdbc")
    .option("user", "root")
    .option("password", "1234")
    .option("url", "jdbc:mysql://localhost:3306/test")
    .option("dbtable", "t_user")
    .load()
df.show()

DF转换为RDD写出

df.rdd.foreach(row => {
    println(row.getInt(0) + "\t" + row.getString(1))
})

RDD转换为DF

rdd.toDF

炳烛之明科技

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Spark SQL

Spark SQL一、概述SQL：类似于Hive，数据仓库（Data WareHourse）工具，简化Spark应用开发http://spark.apache.org/docs/latest/sql-programming-guide.htmlSpark SQL是Spark中一个模块，用以对结构化数据进行处理。SparkSQL在RDD之上抽象出来Dataset/Dataframe 这两个类...
复制链接

扫一扫