SparkSQL

Git122

已于 2024-07-18 19:59:49 修改

阅读量807

点赞数 14

分类专栏： spark 文章标签： spark

于 2024-07-18 11:18:04 首次发布

本文链接：https://blog.csdn.net/2302_76694571/article/details/140503598

版权

spark 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

SparkSQL

【SparkSQL是Spark用于结构化数据（structured data）处理的Spark模块】

SparkSQL 运行原理

/*
Spark SQL运行原理
	Catalyst优化器是Spark SQL的核心
	SELECT name FROM
       (
           SELECT id, name FROM people
       ) p
       WHERE p.id = 1
*/

SparkSQL API

1、SparkContext

2、SQLContext

SparkSQL的编程入口

3、HiveContext

SQLContext的子集，包含更多功能

4、SparkSession（Spark 2之后推荐）

SparkSession：合并了SQLContext与HiveContext
提供与Spark功能交互单一入口点，并允许使用DataFrame和Dataset API对Spark进行编程

若同时创建SparkContext和SparkSession，必须先创建SparkContext再SparkSession
否则会报出如下异常：即重复创建SparkContext

org.apache.spark.SparkException: Only one SparkContext should be running in this JVM (see SPARK-2243)

# SparkContext的创建
【配置】
	master:
		local[*] : CPU核数为当前环境的最大值
		local[2] : CPU核数为2
		local : CPU核数为1
		yarn
		
# SparkSession的创建
val spark = SparkSession.builder
	.master("master")
	.appName("appName")
	.getOrCreate()

SparkSQL数据集

Dataset【分布式数据集合】

从Spark1.6开始引入的新的抽象，特定领域对象中的【强类型】集合

可以使用函数或者相关操作并行地进行转换等操作（操作map, flatMap, filter等）

可以用样例类来对DataSet中定义数据的结构信息，样例类中每个属性的名称直接映射到DataSet中的字段名称

等于 RDD + Schema

Schema 
在Apache SparkSQL中，schema定义了DataFrame的结构，包括列名、数据类型和列的可选描述。
Schema是DataFrame所包含数据的蓝图，它类似于传统数据库中的表模式。

【Dataset 案例】

# 样例类 case class
case class Product(id:Int,name:String,price:Float)
def main(args: Array[String]): Unit = {
	# 配置 SparkContext ==> conf
	val conf = new SparkConf()
		.setMaster("local[4]") 		// local
		.setAppName("spark-sql-01")
	# 创建 SparkSession ==> spark
	val spark: SparkSession = SparkSession
		.builder()
		.config(conf)
		.getOrCreate()
	# 导包
	import spark.implicits._

	val dsPhone: Dataset[Product] = spark.createDataset(Seq(
		Product(1, "Huawei Handset", 5888.0f),
		Product(1, "IPhone", 6666.0f),
		Product(1, "Samsung", 3456.0f)
	))
	dsPhone.printSchema()
}

DataFrame【以RDD为基础的分布式数据集】

DataFrame是特殊的Dataset：DataFrame=Dataset[Row]
	所以可以通过as方法将DataFrame转换为DataSet，
	Row是一个类型，所有的表结构信息都用Row来表示，获取数据时需要指定顺序
	
类似传统数据的二维表格

在RDD基础上加入了Schema（数据结构信息）

DataFrame Schema支持嵌套数据类型
	struct
	map
	array
	
提供更多类似SQL操作的API				

【DataFrame与RDD的区别】			
	DataFrame带有schema元信息，即DataFrame所表示的二维表数据集的每一列都带有名称和类型。
	这使得SparkSQL得以洞察更多的数据信息，从而对藏于DataFrame背后的数据源以及作用于DataFrame之上的变换进行了针对性的优化，
	最终达到大幅提升运行时效率的目标。
	由于RDD无从得知锁存数据元素的具体内部结构，Spark Core只能在stage层面进行简单、通用的流水线优化。
	
	同时，DataFrame也支持嵌套数据类型（struct、array和map）。
	从API易用性的角度上看，DataFrame API提供的是一套高层的关系操作，比函数式的RDD API要更加友好。		
		
	DataFrame为数据提供了Schema的视图，可以直接当做数据库中的一张表来对待。
	DataFrame也是懒执行的，但性能上比RDD要高，
	主要原因：优化的执行计划，即查询计划通过Spark catalyst optimiser进行优化

【DataFrame 案例】

# 配置 SparkContext ==> conf
val conf = new SparkConf()
	.setMaster("local[4]") 		// local
	.setAppName("spark-sql-01")
# 创建 SparkSession ==> spark
val spark: SparkSession = SparkSession
	.builder()
	.config(conf)
	.getOrCreate()	
# 定义 schema 
import org.apache.spark.sql.types.{StructType, StructField, StringType, LongType, IntegerType}
val schema : StructType = StructType(
	Seq(
		StructField("user_id",LongType),
		StructField("locale",StringType ),
		StructField("birthYear",IntegerType),
		StructField("gender",StringType),
		StructField("joinedAt",StringType),
		StructField("location",StringType),
		StructField("timezone",StringType)
	)
)
# 创建 DataFrame
val frmUsers: DataFrame = spark.read
	.schema(schema)
	.option("separator", ",")
	.option("header", true)
	.csv("hdfs://single01:9000/spark/cha02/users.csv") // HDFS 上的路径文件
	.repartition(4)
	.cache()

【SQL 简单查询】

select
	col,cols*,agg*
where
	conditionCols
group by
	col,cols*
having
	condition
order by
	col asc|desc
limit
	n

查询 select ：$“colName” = col(“colName”)，支持各种函数对字段处理

条件筛选 where

# 等于 ===     不等于 =!=   为空 isNull   不为空 isNotNull
frmUsers	
	.where($"birthYear"===1999)
	.where($"location".isNotNull)
	.where($"locale".isin("id_ID","hu_HU"))
	.where($"birthYear">=1995)
	.where($"birthYear".between(1996,1997))
	.where($"location".like("%Indonesia")) // 模糊查询
	.where($"location".rlike(".*Sum.*")) // 正则
	.where($"birthYear".equalTo(1995)) // 等于
	.where($"birthYear".notEqual(1995)) // 不等于
	.where($"birthYear".lt(1995)) // 大于
	.where($"birthYear".leq(1995)) // 大于等于
	.where($"birthYear".gt(1995)) // 小于
	.where($"birthYear".geq(1995)) // 小于等于
	.where($"location".isInCollection(Array("Bekasi","Bangkalan")))
	.where(not($"location".isInCollection(Array("Bekasi","Bangkalan")))) // 非 => not()

多条件

newCol:Column = ColOne and ColTwo			与
   newCol:Column = ColOne or ColTwo			或
   newCol:Column = not(Column)				非

val rddWhere: Dataset[Row] = dfCustomer.where(
	$"cus_state".isin("TX", "PR") and 
	length($"cus_lname").gt(6)
)

分组 group by

frmUsers
	.select($"locale",$"birthYear")
	.where($"birthYear">1990)
	.groupBy($"gender",$"locale")
	.count() // 单一聚合

聚合 agg（aggregate）

agg 允许在一个函数中调用中使用多个聚合函数

agg 通常在 groupBy 之后使用，首先按照某些列对数据进行分组，然后对每个分组应用聚合函数

内置聚合函数：count, sum, avg, min, max, first, last 等，还支持自定义聚合函数

agg 的结果是一个包含聚合结果的新 DataFrame，其列数取决于你使用的聚合函数的数量

frmUsers
	.select($"locale",$"birthYear")
	.where($"birthYear">1990)
	.groupBy($"gender",$"locale")
	.agg(
		count($"locale").as("locale_count")，
		round(avg($"birthYear"),2).as("avg_birth_year")
	)

二次筛选 having = where

frmUsers
	.select($"locale",$"birthYear")
	.where($"birthYear">1990)
	.groupBy($"gender",$"locale")
	.agg(
		count($"locale").as("locale_count")，
		round(avg($"birthYear"),2).as("avg_birth_year")
	)
	.where($"locale_count">=10 and $"avg_birth_year">=1993)

排序 order by

支持原始字段和聚合字段（$“field”.asc | desc）

frmUsers
	.select($"locale",$"birthYear")
	.where($"birthYear">1990)
	.groupBy($"gender",$"locale")
	.agg(
		count($"locale").as("locale_count")，
		round(avg($"birthYear"),2).as("avg_birth_year")
	)
	.where($"locale_count">=10 and $"avg_birth_year">=1993)
	.orderBy($"locale_count".desc)

限制 limit ==> show(10)

frmUsers
	.select($"locale",$"birthYear")
	.where($"birthYear">1990)
	.groupBy($"gender",$"locale")
	.agg(
		count($"locale").as("locale_count")，
		round(avg($"birthYear"),2).as("avg_birth_year")
	)
	.where($"locale_count">=10 and $"avg_birth_year">=1993)
	.orderBy($"locale_count".desc)
	.show(10)

【多重分组】<==>【多字段多维度分组并表】

等同于将多个不同维度聚合的数据纵向组合成一个数据集

/**
select ... from T group by a
union all
select ... from T group by a,b
...
*/
# 语法:	
grouping sets 定制分组
	如 grouping sets(a,b,c,(a,c))
rollup 右滚动排列组合
	如 rollup(a,b,c)  <=> grouping sets(a,(a,b),(a,b,c))
cube  全量排列组合
	如 cube(a,b,c) <=> grouping sets(,a,b,c,(a,b),(a,c),(b,c),(a,b,c))
	
select grouping__id,a,b,c,count(*) as cnt from T
group by a,b,c grouping sets(a,b,c,(a,c))

select grouping__id,a,b,c,count(*) as cnt from T
group by rollup(a,b,c)

select grouping__id,a,b,c,count(*) as cnt from T
group by cube(a,b,c)

Spark 不支持 grouping sets API, 可以通过spark.sql(sql:String)来实现

spark.sql(
  """
	|select gid
	|from (
	|select grouping__id as gid, gender, birthYear, count(*) as cnt from user_info
	|group by cube(gender,birthYear)
	|)A group by gid
	|""".stripMargin)
  .show(100)

frmUsers
	.rollup($"gender",$"birthYear")
	.agg(count("*").as("cnt"))
	.select(grouping_id().as("group_id"),$"gender",$"birthYear",$"cnt")
	.show(100)

关联查询

指定连接类型：JoinType: 
	inner 							内部连接：【交集】  缺省默认 ✔
	outer = full = fullouter		全外连接：【双全集】
	left = leftouter 				左外连接：【左全集】：先返回两表笛卡尔积，再进行ON条件筛选
										若右表的数据也需要提取，则只能使用left
	semi = leftsemi 				左外连接：【左交集】：右表只做存在性检查，代替子查询in
										只返回左表在右表中有匹配记录的数据，不提取右表数据
	anti = leftanti 				左外连接：【左差集】：右表只做存在性检查，代替子查询not in
	right = rightouter 				右外连接：【右全集】
	cross 							默认：交叉连接：【笛卡尔积】

潜在的【using】：若join操作采用如下语法【不提供joinType:inner】且【关联字段在两张表中同名】
	join(dataset:DataSet[_],oneCol:String)
	join(dataset:DataSet[_],cols:Seq[String])
	实际的运行结果，两张表中会执行【同名列去重】

【案例】

case class Student(name:String,classId:Int)
  case class Class(classId:Int,className:String)
  def main(args: Array[String]): Unit = {
    val ssb: SparkSessionBuilder = SparkSessionBuilder("local[*]", "spark-sql-join-01")
    val spark: SparkSession = ssb.spark

    import spark.implicits._

    val frmClass: DataFrame = spark.createDataFrame(
      Seq(
        Class(1, "12212"),
        Class(2, "12309"),
        Class(3, "12401")
      )
    )

    val frmStu : DataFrame = spark.createDataFrame(
      Seq(
        Student("henry",1),
        Student("ariel",2),
        Student("jack",1),
        Student("rose",4),
        Student("jerry",2),
        Student("mary",1),
      )
    )

    // join 默认是笛卡尔积
    frmStu.as("S")
      .join(frmClass.as("C")) // 笛卡尔积
      .join(frmClass.as("C"),$"S.classId"===$"C.classId","inner")
      .join(frmClass.as("C"),Seq("classId"),"inner") // 默认启用 using（同名字段去重）
      .join(frmClass.as("C"),$"S.classId"===$"C.classId","left") // 左外
      .join(frmClass.as("C"),$"S.classId"===$"C.classId","right") // 右外
      .join(frmClass.as("C"),$"S.classId"===$"C.classId","anti") // 左差集
      .join(frmClass.as("C"),$"S.classId"===$"C.classId","semi") // 左交集

      .join(frmClass.as("C"),$"S.classId"===$"C.classId","full") // 全外
      .show(100)

    ssb.stop()
  }

数据提取

DataSet[Row] dataSet = df.limit(n:Int)
Array[Row] rows = df.collect()
Array[Row] rows = df.tail(n:Int)
Array[Row] rows = df.take(n:Int)

数据展示

df.show([n:Int])

【SQL 函数】

常用函数

$"NAME" = col("NAME")								// 取列值

as("ALIAS_NAME")									// 别名

as(alias:Seq[String])								// 多个别名 pos_explode(Array), explode(Map)

when(CONDITION,V1)....otherwise(VN)					// 条件
when($"score"<60,"D")
	.when($"score"<80,"C")
	.when($"score"<90,"B")
	.otherwise("A")
	.as("score_level")
	
lit(VALUE)											//常量列
lit(5).as("new_const_col")
	
withColumn(colName:String, col:Column)				// 扩展列，通常用于使用【窗口函数】扩展表的列
	
cast(DataType)										// 类型转换	
	
explode	和 pos_explode								// 一个 select 子句中只能出现一个 explode	或 pos_explode

【案例】

case class Test(id:Int,hobbies:Array[String],scores:Map[String,Int])
def main(args: Array[String]): Unit = {
	val builder: SparkSessionBuilder = SparkSessionBuilder("local[*]", "spark-sql-test2")
	val spark: SparkSession = builder.spark

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

	val frm: DataFrame = spark.createDataFrame(Seq(
		Test(1,Array("money","freedom"),Map("java"->85,"mysql"->67)),
		Test(2,Array("beauty","beauty"),Map("java"->72,"mysql"->90)),
		Test(3,Array("sports","beauty"),Map("java"->76,"html"->52))
	))
	
	frm
		.select($"id",explode($"hobbies").as("hobby"),$"scores")
		.select($"id",$"hobby",explode($"scores").as(Seq("subject","score")))
		.select($"id",$"hobby",$"subject",$"score",
			when($"score"<60,"D")
				.when($"score"<80,"C")
				.when($"score"<90,"B")
				.otherwise("A")
				.as("score_level"),
			lit(5).as("new_const_col")
		).show()

	builder.stop()
}
// 输出结果
+---+-------+-------+-----+-----------+-------------+
| id|  hobby|subject|score|score_level|new_const_col|
+---+-------+-------+-----+-----------+-------------+
|  1|  money|   java|   85|          B|            5|
|  1|  money|  mysql|   67|          C|            5|
|  1|freedom|   java|   85|          B|            5|
|  1|freedom|  mysql|   67|          C|            5|
|  2| beauty|   java|   72|          C|            5|
|  2| beauty|  mysql|   90|          A|            5|
|  2| beauty|   java|   72|          C|            5|
|  2| beauty|  mysql|   90|          A|            5|
|  3| sports|   java|   76|          C|            5|
|  3| sports|   html|   52|          D|            5|
|  3| beauty|   java|   76|          C|            5|
|  3| beauty|   html|   52|          D|            5|
+---+-------+-------+-----+-----------+-------------+

集合函数

array
	size(collectCol:Column)
	array(cols:Column*)							// 一行中的多列转为单列数组类型
	array_sort(arrayCol:Column)
	array_contains(arrayCol:Column,value:Any)
	array_distinct(arrayCol:Column)
	array_join(arrayCol:Column,sep:String,nullReplacement:String)

	array_except(arrayCol:Column)				// 差
	array_intersect(arrayCol:Column)			// 交
	array_union(arrayCol:Column)				// 并
	
map
	map_keys(mapCol:Column)
	map_values(mapCol:Column)
	map_entries(mapCol:Column)

【案例】


val builder: SparkSessionBuilder = SparkSessionBuilder("local[*]", "spark-sql-test2")
val spark: SparkSession = builder.spark

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

val frm: DataFrame = spark.createDataFrame(Seq(
	Test(1,Array("money","freedom"),Map("java"->85,"mysql"->67)),
	Test(2,Array("beauty","beauty"),Map("java"->72,"mysql"->90)),
	Test(3,Array("sports","beauty"),Map("java"->76,"html"->52))
))
frm
	.select(
		$"id",
		map_keys($"scores").as("subjects"),
		map_values($"scores").as("scores"),
		map_entries($"scores").as("subject_scores")
	).show()
	
// 输出结果
+---+-------------+--------+--------------------+
| id|     subjects|  scores|      subject_scores|
+---+-------------+--------+--------------------+
|  1|[java, mysql]|[85, 67]|[{java, 85}, {mys...|
|  2|[java, mysql]|[72, 90]|[{java, 72}, {mys...|
|  3| [java, html]|[76, 52]|[{java, 76}, {htm...|
+---+-------------+--------+--------------------+

字符串函数

提取

提取json

json_tuple(jsonCol:Column, fields:String*)				// fields => field1,field2
get_json_object(jsonCol:Column, path:String)			// path => $.field1[.field2]

【案例】

val builder: SparkSessionBuilder = SparkSessionBuilder("local[*]", "spark-sql-json")
val spark: SparkSession = builder.spark

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

spark
	.createDataFrame(Seq(
		Json(1,"""{"name":"jack","age":22,"hobbies":["beauty","money","power"],"address":{"province":"jiangsu","city":"nanjing"}}"""),
		Json(2,"""{"name":"ariel","age":12,"hobbies":["beauty","game"],"address":{"province":"jiangsu","city":"wuxi"}}"""),
		Json(3,"""{"name":"tom","age":38,"hobbies":["handsome","money","car"],"address":{"province":"jiangsu","city":"nanjing"}}""")
	))
	.select($"id",
		json_tuple($"json","name","age","hobbies").as(Seq("name","age","hobbies")),
		get_json_object($"json","$.address.province").as("province"),
		get_json_object($"json","$.address.city").as("city")
	)
	.show()
// 输出结果
+---+-----+---+--------------------+--------+-------+
| id| name|age|             hobbies|province|   city|
+---+-----+---+--------------------+--------+-------+
|  1| jack| 22|["beauty","money"...| jiangsu|nanjing|
|  2|ariel| 12|   ["beauty","game"]| jiangsu|   wuxi|
|  3|  tom| 38|["handsome","mone...| jiangsu|nanjing|
+---+-----+---+--------------------+--------+-------+

提取：正则

regexp_extract(col:Column, pattern:String, groupId:Int)

【案例】

case class Line(line:String)
def main(args: Array[String]): Unit = {
	val builder: SparkSessionBuilder = SparkSessionBuilder("local[*]", "spark-sql-regex")
	val spark: SparkSession = builder.spark
	import org.apache.spark.sql.functions._
	import spark.implicits._
	import org.apache.spark.sql.types.{IntegerType, LongType, StructField, StringType}


	val regex_line = "(.*?) (INFO|WARN|ERROR) (.*): (.*)"
	val regex_log = "^\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2},\\d{3} (INFO|WARN|ERROR) .*"

	spark
		.read
		.text("spark-warehouse/datanode.log")// idea上的相对路径【hadoop上的datanode的一个日志文件】
		.toDF("line")
		.where($"line".rlike(regex_log))
		.select(
			regexp_extract($"line",regex_line,1).as("log_in_time"),
			regexp_extract($"line",regex_line,2).as("log_type"),
			regexp_extract($"line",regex_line,3).as("log_full_pack"),
			regexp_extract($"line",regex_line,4).as("log_detail")
		)
		.where($"log_type".equalTo("ERROR"))
		.show()
}
// 输出结果
+--------------------+--------+--------------------+--------------------+
|         log_in_time|log_type|       log_full_pack|          log_detail|
+--------------------+--------+--------------------+--------------------+
|2023-10-15 17:59:...|   ERROR|org.apache.hadoop...|             SIGTERM|
|2023-10-21 23:13:...|   ERROR|org.apache.hadoop...|/192.168.83.128:9866|
|2023-10-21 23:30:...|   ERROR|org.apache.hadoop...|/192.168.83.128:9866|
|2023-10-22 00:59:...|   ERROR|org.apache.hadoop...|              SIGHUP|
|2023-10-22 00:59:...|   ERROR|org.apache.hadoop...|             SIGTERM|
|2023-10-22 11:06:...|   ERROR|org.apache.hadoop...|              SIGHUP|
|2023-10-22 11:06:...|   ERROR|org.apache.hadoop...|             SIGTERM|
|2023-10-22 11:36:...|   ERROR|org.apache.hadoop...|/192.168.83.128:9866|
|2023-10-22 17:13:...|   ERROR|org.apache.hadoop...|/192.168.83.128:9866|
|2023-10-22 17:45:...|   ERROR|org.apache.hadoop...|             SIGTERM|
|2023-10-23 19:13:...|   ERROR|org.apache.hadoop...|             SIGTERM|
|2023-10-24 08:45:...|   ERROR|org.apache.hadoop...|             SIGTERM|
|2023-10-24 11:25:...|   ERROR|org.apache.hadoop...|/192.168.83.128:9866|
|2023-10-24 18:21:...|   ERROR|org.apache.hadoop...|             SIGTERM|
|2023-10-24 18:21:...|   ERROR|org.apache.hadoop...|              SIGHUP|
|2023-10-29 01:34:...|   ERROR|org.apache.hadoop...|             SIGTERM|
|2023-10-29 11:51:...|   ERROR|org.apache.hadoop...|/192.168.83.128:9866|
|2023-10-29 14:39:...|   ERROR|org.apache.hadoop...|/192.168.83.128:9866|
|2023-10-29 14:43:...|   ERROR|org.apache.hadoop...|/192.168.83.128:9866|
|2023-10-29 17:50:...|   ERROR|org.apache.hadoop...|             SIGTERM|
+--------------------+--------+--------------------+--------------------+
only showing top 20 rows

分裂和截取

split(col:Column,pattern:String)
substring(col:Column,pos:Int,len:Int)

-- groupId +N 		从左向右前N个
-- groupId -N 		从右向左前N个
-- 第N个  ==》 substring_index(substring_index(COL,SEP,+N),SEP,-1)（SEP为参数之间的分隔符）
substring_index(col:Column,sep:String,groupId:Int)

子字符串在字段中的未知

locate(subStr:String,col:Column)		// 有则>0，否则=0
instr(col:Column,subStr:String)

【案例】

case class Line(line:String)
def main(args: Array[String]): Unit = {
	val builder: SparkSessionBuilder = SparkSessionBuilder("local[*]", "spark-sql-String")
    val spark: SparkSession = builder.spark
	
    import spark.implicits._
    import org.apache.spark.sql.functions._

    spark
        .createDataFrame(Seq(
          Line("beauty,money,power")
        ))
        .select(
			split($"line",",").as("hobbies"),
			substring_index(substring_index($"line",",",2),",",-1).as("hobbies2"),
			locate("money",$"line").as("pos_money")		-- 输出为8
        ).show()
}

字符串拼接

concat(cols:Column*)
concat_ws(sep:String,cols:Column*)

内容长度

length(col:Column)							// 字符长度
// 字节长度，未提供算子，需要通过 spark.sql(""" select octet_length(...)""") 实现

定长填充

lpad(col:Column,len:Int,pad:String)
rpad(col:Column,len:Int,pad:String)

清除两端空格

ltrim(col:Column)
rtrim(col:Column)
trim(col:Column)

大小写转换

initcap(col:Column)							// 每个单词首字母大写
upper(col:Column)							// 全大写
lower(col:Column)							// 全小写

【案例】

case class Line2(firstName:String, lastName:String)
def main(args: Array[String]): Unit = {
	val builder: SparkSessionBuilder = SparkSessionBuilder("local[*]", "spark-sql-String")
    val spark: SparkSession = builder.spark
	
    import spark.implicits._
    import org.apache.spark.sql.functions._

	spark
		.createDataFrame(Seq(
		Line2("henry","chen"),
		Line2("pola","li"),
		Line2("jack","chan"),
		))
		.select(
			concat($"firstName", lit(" "), $"lastName") as "full_name_1",
			concat_ws(" ", initcap($"lastName"), initcap($"firstName")) as "full_name_2",
			length($"firstName")+length($"lastName").as("name_size"),
			lpad($"firstName",10,"_").as("name_size_10"),
			trim($"firstName").as("name_clear")
		)
		.show()
		
    builder.stop()
}
// 输出结果
+-----------+-----------+-----------------------------------------------------+------------+----------+
|full_name_1|full_name_2|(length(firstName) + length(lastName) AS `name_size`)|name_size_10|name_clear|
+-----------+-----------+-----------------------------------------------------+------------+----------+
| henry chen| Chen Henry|                                                    9|  _____henry|     henry|
|    pola li|    Li Pola|                                                    6|  ______pola|      pola|
|  jack chan|  Chan Jack|                                                    8|  ______jack|      jack|
+-----------+-----------+-----------------------------------------------------+------------+----------+

转换

hash(col:Column)							// 去哈希值
regexp_replace(col:Column,pattern:String,replace:String)	// 正则替换
translate(col:Column,from:String,to:String)	// 按字母转换
reverse(col:Column)							// 翻转

转码

encode(col:Column, charSet:String)
decode(col:Column, charSet:String)

非对称加密

sha1(col:Column)
md5(col:Column)

spark 自定义函数

-- 建议将自定义函数实现，单独建对象保存
import java.nio.charset.{StandardCharsets}
import java.util.Base64
import javax.crypto.Cipher
import javax.crypto.spec.SecretKeySpec

object SparkUtil {
	private def secretInit(secret:String)={
		val allowNumBits: Array[Int] = Array(16, 24, 32)
		if (allowNumBits.contains(secret.size)) {
			new SecretKeySpec(secret.getBytes(StandardCharsets.UTF_8),"AES")
		}else{
			throw new RuntimeException(s"numBits ${secret.size} not in permitted values (${allowNumBits.mkString(",")})")
		}
	}

	def encrypt(src:String,secret:String)={
		val cipher: Cipher = Cipher.getInstance("AES")
		cipher.init(Cipher.ENCRYPT_MODE,secretInit(secret))
		val bytes: Array[Byte] = cipher.doFinal(src.getBytes(StandardCharsets.UTF_8))
		Base64.getEncoder.encodeToString(bytes)
	}

	def decrypt(dest:String,secret:String)={
		val cipher: Cipher = Cipher.getInstance("AES")
		cipher.init(Cipher.DECRYPT_MODE,secretInit(secret))
		val bytes: Array[Byte] = cipher.doFinal(Base64.getDecoder.decode(dest))
		new String(bytes, StandardCharsets.UTF_8)
	}
}

-- 在 Spark 环境下导入对象实现的方法，并在 SparkSession 中注册 UDF 函数
import core.SparkUtil.{encrypt,decrypt}
    spark.udf.register("aes_encrypt",(src:String,secret:String)=>encrypt(src,secret),StringType)
    spark.udf.register("aes_decrypt",(src:String,secret:String)=>decrypt(src,secret),StringType)

-- 在 SparkSql 中调用注册函数
val frm: DataFrame = spark.createDataFrame(Seq(
	Test(1,Array("money","freedom"),Map("java"->85,"mysql"->67)),
  	Test(2,Array("beauty","beauty"),Map("java"->72,"mysql"->90)),
  	Test(3,Array("sports","beauty"),Map("java"->76,"html"->52))
))

val secret = "henryyb2211ariel"

val frmEncrypt: DataFrame = frm
	.select($"id",
		callUDF(
		"aes_encrypt",
		array_join($"hobbies", ","),
		lit(secret)
	).as("encrypted_hobbies")
  )

val frmDecrypt: DataFrame = frmEncrypt
	.select(
		$"id",
		split(callUDF("aes_decrypt",$"encrypted_hobbies",lit(secret)),","
	).as("hobbies")
  ).show()

聚合函数

count(col:Column)									// 计数
countDistinct(col:Column,cols:Column*)				// 去重计数
sum(numCol:Column)									// 求和
sumDistinct(numCol:Column)							// 去重求和
avg(numCol:Column)									// 求均值
max(numCol:Column)									// 求最大值
min(numCol:Column)									// 求最小值
collect_set(col:Column)								// 去重收集
collect_list(col:Column)							// 列表收集

分析函数

// 方差：每个样本值与全体样本值的平均数之差的平方值的平均数，反应数据的偏离程度
var_samp(numCol:Column)						// 样本方差
var_pop(numCol:Column)						// 方差
// 标准差：方差的算术平方根，统计分布程度上的测量依据，也能反应数据的偏离程度
stddev_samp(numCol:Column)					// 标准样本差
stddev_pop(numCol:Column)					// 标准差
// 协方差：两个变量之间的线性关系，两个变量在方向和幅度上的一致性
/*
	如果两个变量的变化趋势一致:
		两者都大于自身的期望值时，协方差就是正值
		一个大于自身期望值，另一个小于自身期望值，协方差就是负值
	如果协方差为正，则表明X和Y同向变化，反之，协方差为负则表明反向变化；
	协方差的绝对值越大，表示同向或反向的程度越深
*/
covar_pop(numCol1:Column,numCol2:Column)	// 协方差
covar_samp(numCol1:Column,numCol2:Column)	// 样本协方差

【案例】

case class Product(name:String,price:Float,date:String,market:String,province:String,city:String)
def main(args: Array[String]): Unit = {
	val path = "D:\\courses\\2_phrase_snd\\6_spark\\products.txt"
	val ssb: SparkSessionBuilder = SparkSessionBuilder("local[*]","spark-sql-product") // 本地
	val sc: SparkContext = ssb.sc
	val rddProduct: RDD[Product] = sc.textFile(path)
		.mapPartitions(_.map(_.toProduct))
	
	val spark: SparkSession = ssb.spark
	
	import spark.implicits._
	import org.apache.spark.sql.functions._
	
	spark
		.createDataFrame(rddProduct)
		.groupBy($"province")
		.agg(
			count($"city").as("city_count"),
			size(collect_set($"city")).as("unique_city_count"),
			stddev($"price").as("dev_price"),
			var_pop($"price").as("pop_price")
		)
		.show()
		
	ssb.stop()
}

窗口函数和OVER从句

// over 从句，配合窗口函数或聚合函数使用
val specFull: WindowSpec = Window			// 全局窗口：unbounded preceding ~ following
  .partitionBy(col:Column)
val specTop2Curr: WindowSpec = specFull		// 排序窗口: unbounded preceding ~ current
  .orderBy(col:Column)
val specThree: WindowSpec = specTop2Curr	// 粒度窗口：start ~ current ~ stop
  .rowsBetween(start:Int, stop:Int)			// rowsBetween 物理偏移 rangeBetween 逻辑偏移

// 窗口函数
first(col:Column).over(specFull:WindowSpec)				// 窗口内首行
lag(col:Column,offset:Int).over(specOrderBy:WindowSpec)	// curr 上 offset 行
lead(col:Column,offset:Int).over(specOrderBy:WindowSpec)// curr 下 offset 行
last(col:Column).over(specFull)							// 窗口内末行
nth_value(col:Column,nth:Int).over(specOrderBy:WindowSpec)	// 窗口第 n 行
dense_rank().over(specOrderBy:WindowSpec)				// 排名（有相同排名时，不进行跳数）
percent_rank().over(specOrderBy:WindowSpec)				// 百分比排名
row_number().over(specOrderBy:WindowSpec)				// 行号
ntile(n:Int).over(specOrderBy:WindowSpec)				// 切片(抽样)
cume_dist().over(specOrderBy:WindowSpec)				// 分布

【案例】

case class Product(name:String,price:Float,date:String,market:String,province:String,city:String)
def main(args: Array[String]): Unit = {
	val path = "D:\\courses\\2_phrase_snd\\6_spark\\products.txt"
	val ssb: SparkSessionBuilder = SparkSessionBuilder("local[*]","spark-sql-product") // 本地
	val sc: SparkContext = ssb.sc
	val rddProduct: RDD[Product] = sc.textFile(path)
		.mapPartitions(_.map(_.toProduct))
	
	val spark: SparkSession = ssb.spark
	
	import spark.implicits._
	import org.apache.spark.sql.functions._
	
	spark.createDataFrame(rddProduct)
		.groupBy($"province",$"name")
		.agg(
			avg($"price").as("avg_price"),
			count("*").as("product_cnt")
		)
		.select(
			$"province",$"name",$"avg_price",$"product_cnt",
			dense_rank().over(
				Window.partitionBy($"province")
					.orderBy($"avg_price")
			).as("rnk_by_product_cnt_desc")
		)
		.show()

	ssb.stop()
}

// 输出结果
+--------+------+------------------+-----------+-----------------------+
|province|  name|         avg_price|product_cnt|rnk_by_product_cnt_desc|
+--------+------+------------------+-----------+-----------------------+
|    广东|白萝卜| 0.824999988079071|          2|                      1|
|    广东|洋白菜| 0.949999988079071|          2|                      2|
|    广东|大白菜|0.9500000178813934|          2|                      3|
|    广东|  冬瓜|              1.25|          2|                      4|
|    广东|  莴笋|1.5999999642372131|          2|                      5|
|    广东|  南瓜| 1.800000011920929|          2|                      6|
|    广东|绿豆芽| 1.899999976158142|          1|                      7|
|    广东|  菜瓜| 2.200000047683716|          1|                      8|
|    广东|  大蒜| 2.200000047683716|          1|                      8|
|    广东|胡萝卜|2.4000000953674316|          1|                      9|
|    广东|  小葱|               2.5|          1|                     10|
|    广东|  生菜|               2.5|          2|                     10|
|    广东|  青笋|2.5999999046325684|          1|                     11|
|    广东|小白菜|2.5999999046325684|          1|                     11|
|    广东|  香蕉|2.5999999046325684|          1|                     11|
|    广东|  土豆|2.6999999284744263|          2|                     12|
|    广东|  菠萝| 2.799999952316284|          1|                     13|
|    广东|  黄瓜| 2.799999952316284|          1|                     13|
|    广东|  菜花|2.8000000715255737|          2|                     14|
|    广东|西洋芹|2.9000000953674316|          1|                     15|
+--------+------+------------------+-----------+-----------------------+
only showing top 20 rows

日期函数

year(dateCol:Column)     			// 年
quarter(dateCol:Column)     		// 季
month(dateCol:Column)     			// 月
dayofweek(dateCol:Column)     		// 星期几 ：周日~周六 1~7
weekofyear(dateCol:Column)     		// 年周
dayofmonth(dateCol:Column)     		// 月日
dayofyear(dateCol:Column)     		// 年日
hour(dateCol:Column)     			// 时
minute(dateCol:Column)     			// 分
second(dateCol:Column)     			// 秒

// 适用于将日期的不严格格式，格式化为严格格式
date_format(dateCol:Column,format:String)		// 日期格式化：yyyy-MM-dd HH:mm:ss
last_day(dateCol:Column)						// 日期当月最后一天			

// 【日期计算】
datediff(dateBig:Column,dateSmall:Column)		// 两个日期之间天数差
months_between(dateBig:Column,dateSmall:Column)	// 两个日期之间月数差
date_add(dateCol:Column,days:Int),				// 日期按天计算
add_months(dateCol:Column,months:Int)			// 日期按月计算
// 星期格式，英文单词前三个字母，如：MON TUE ...
next_day(dateCol:Column,weekFormat:String)		// 下一个星期几(未至本周，已至下周)

current_date()						// 当前日期 yyyy-MM-dd
current_timestamp()					// 当前日期 yyyy-MM-dd HH:mm:ss
unix_timestamp()					// 当前时间戳 1715307735 秒

// 如下操作必须进行 SparkSession 的配置
spark.conf.set("spark.sql.legacy.timeParserPolicy", "LEGACY")

// 【日期转时间戳】十位整数
// 获取系统当前时间戳
// 等同于 unix_timestamp(current_timestamp(), yyyy-MM-dd HH:mm:ss)
unix_timestamp()
// 获取指定日期时间戳，默认格式为 yyyy-MM-dd HH:mm:ss 严格格式
// 等同于 unix_timestamp(2013-07-25 00:00:00, yyyy-MM-dd HH:mm:ss
unix_timestamp(dateCol:Column)
// 获取指定日期指定格式时间戳，指定格式亦为严格格式，若不匹配报错
// 等同于 unix_timestamp(2013-07-25 00:00:00, yyyy-MM-dd)
unix_timestamp(dateCol:Column,format:String)

// 【日期转时区】
to_utc_timestamp(lit("2013-07-25 00:00:00"),"GMT+8")

// 【时间戳转日期】
// 将指定时间戳转日期：from_unixtime(lit(1715307735L))
from_unixtime(numCol:Column)
// 将指定时间戳按指定格式转日期：from_unixtime(lit(1715307735L),"yyyy-MM-dd")
from_unixtime(numCol:Column, format:String)

// 进保留日期 yyyy-MM-dd
to_date(dateCol:Column)

/*
	提取日期中指定格式的第一天的日期
    YEAR
    QUARTER
    MONTH
    WEEK
*/
date_trunc(format:String,date:Column)

【案例】

case class Line(date:String)
case class Line2(timestamp:Long)
def main(args: Array[String]): Unit = {
	val builder: SparkSessionBuilder = SparkSessionBuilder("local[*]", "spark-sql-data-test")
	
    val spark: SparkSession = builder.spark
    import spark.implicits._
    import org.apache.spark.sql.functions._
    import demo01.SparkUtil.weekOfMonth

    spark
      .createDataFrame(Seq(
        Line2(1234786543),
        Line2(125986325),
        Line2(643757987)
      ))
        .select(
          from_unixtime($"timestamp").as("datetime")
        )
        .show()
// 输出结果
+-------------------+
|           datetime|
+-------------------+
|2009-02-16 20:15:43|
|1973-12-29 12:12:05|
|1990-05-27 06:39:47|
+-------------------+
     spark
      .createDataFrame(Seq(
        Line2(1234786543),
        Line2(125986325),
        Line2(643757987)
      ))
        .select(
          from_unixtime($"timestamp").as("datetime")
        ) 
        .select(
          date_trunc("YEAR",$"datetime").as("year"),
          date_trunc("QUARTER",$"datetime").as("quarter"),
          date_trunc("MONTH",$"datetime").as("month"),
          date_trunc("WEEK",$"datetime").as("week"),
          date_trunc("DAY",$"datetime").as("day")
        )
        .show()
// 输出结果
+-------------------+-------------------+-------------------+-------------------+-------------------+
|               year|            quarter|              month|               week|                day|
+-------------------+-------------------+-------------------+-------------------+-------------------+
|2009-01-01 00:00:00|2009-01-01 00:00:00|2009-02-01 00:00:00|2009-02-16 00:00:00|2009-02-16 00:00:00|
|1973-01-01 00:00:00|1973-10-01 00:00:00|1973-12-01 00:00:00|1973-12-24 00:00:00|1973-12-29 00:00:00|
|1990-01-01 00:00:00|1990-04-01 00:00:00|1990-05-01 00:00:00|1990-05-21 00:00:00|1990-05-27 00:00:00|
+-------------------+-------------------+-------------------+-------------------+-------------------+

    spark
      .createDataFrame(Seq(
        Line("2024-5-10"),
        Line("2024-3-5"),
        Line("2024-11-12")
      ))
      // date_format() 函数将日期转化为严格格式
      .select(date_format($"date","yyyy-MM-dd").as("date"))
        .select(
          datediff(current_date(),$"date").as("day_diff"),
          months_between(current_date(),$"date").as("month_diff"),
          date_add($"date",7).as("date_next_week"),
          add_months($"date",1).as("date_next_month"),
          next_day($"date","WED").as("next_wednesday"),
          unix_timestamp($"date","yyyy-MM-dd").as("timestamp")
        ).show()
// 输出结果
+--------+-----------+--------------+---------------+--------------+----------+
|day_diff| month_diff|date_next_week|date_next_month|next_wednesday| timestamp|
+--------+-----------+--------------+---------------+--------------+----------+
|      69| 2.25806452|    2024-05-17|     2024-06-10|    2024-05-15|1715270400|
|     135| 4.41935484|    2024-03-12|     2024-04-05|    2024-03-06|1709568000|
|    -117|-3.80645161|    2024-11-19|     2024-12-12|    2024-11-13|1731340800|
+--------+-----------+--------------+---------------+--------------+----------+
       

    builder.stop()
}

数学函数

abs(col:Column)							// 绝对值
degrees(col:Column)						// 弧度转角度 
radians(col:Column)						// 角度转弧度 
sin(col:Column)							// 正弦
cos(col:Column)							// 余弦
tan(col:Column)							// 正切
asin(col:Column)						// 反正弦
acos(col:Column)						// 反余弦
atan(col:Column)						// 反正切
round(col:Column,n:Int)					// 四舍五入
ceil(col:Column)						// 向上取整
floor(col:Column)						// 向下取整
format_number(col:Column,n:Int)			// 数值格式化
pow(col:Column,n:Int)					// 幂
log(n:Int,col:Column)					// 对数
rand()									// 随机数
greatest(col:Column*)				// 多列最大值
least(col:Column*)					// 多列最小值

【案例】

case class Scores(stu_id:Int,javaScore:Int,mysqlScore:Int,sparkScore:Int)
def main(args: Array[String]): Unit = {
  val builder: SparkSessionBuilder = SparkSessionBuilder("local[*]", "spark-sql-data-test")

  val spark: SparkSession = builder.spark
  import org.apache.spark.sql.functions._
  import spark.implicits._

  spark
	.createDataFrame(Seq(
		Scores(1,89,78,65),
		Scores(2,66,58,54),
		Scores(3,78,69,76)
    ))
    .select(
        $"stu_id",
        greatest($"javaScore",$"mysqlScore",$"sparkScore").as("maxScore"),
        least($"javaScore",$"mysqlScore",$"sparkScore").as("minScore")
     )
    .show()

  builder.stop()
}

SparkSQL 优化

// 查看表结构
df.printSchema()
// 查看执行计划
df.explain(true)

// 数据倾斜
/*
	数据过载：调整并行度(分区数)，随机前缀/加盐/Map端聚合
		reduceByKey, combineByKey, aggregateByKey
	维度数据导致：小表+大表，可以采用广播变量
		broadcast
	抽样确定key的倾斜度，key值平均大小，重新分区，让数据均匀分布
	使用 skewness|kurtosis 算子检测数据倾斜
*/