sparksql的简介
简介
- sparksql是spark中的一个重要组件
- 构建在SparkCore基础之上的一个基于SQL的计算模块。
- 前身叫shark,基于hive,hive的发展制约了shark的发展
- 后来shark项目停止了,独立出来一个新的,叫sparksql,带有shark的优点:基于内存的列式存储,动态字节码优化技术
- 用于处理具有结构化的大数据集
特点
- 集成: 可以在spark项目上使用sql,可以使用java、scala、python、r语言
- 统一的数据源访问: 使用通用的方法访问各种数据源:avro,parquet,orc,json,jdbc等
- 集成了sql和hivesql 支持普通的sql以及hivesql在hive warehouse上运行。
- 标准的连接方式 可以使用jdbc或者是odbc连接sparksql
sparksql的编程模型
两种编程模型
SQL编程模型
就是使用我们学过的通用的sql(包括标准sql以及hivesql)语句进行执行数据的查询和计算
注意:如果想要使用这种写法,那么sparksql中必须要将对应的数据维护成一个表。
DataFrame和DataSet编程模型
- DataFrame 是spark1.3以后出现的, DataSet是spark1.6以后出现的
- DataFrame和DataSet都是对真正要处理的数据的一个抽象,和RDD概念相似。
- DataFrame和DataSet比RDD多了一个schema描述。
- schema指的就是表名,表头,字段,字段类型等描述信息
称呼: RDD编程称之为spark第一代编程模型
DataFrame比RDD多了一个Schema元数据信息,被称之为Spark体系中的第二代编程模型
Dataset吸收了RDD的优点(强类型推断和强大的函数式编程)和DataFrame中的优化(SQL优化引擎,内存列存储),成为Spark的最新一代的编程模型。
RDD & DataFrame & DataSet的比较
- RDD和DataFrame的比较
- RDD描述的是数据结构,以及提供了操作方式,并且具有弹性特点,也可以理解为RDD是一张二维表
- DataFrame以前叫SchemaRDD,从名字上就可以知道,比RDD多了一个Schema,而schema就是元数据
元数据有表名,表头,字段,字段类型- DataFrame易用性更好,底层可以自动优化。即使你写的sql比较复杂,运行速度也非常快
- RDD和DataSet的比较
- 相同点:DataSet也引入了RDD的强类型推断,也是在RDD的每行数据加了类型约束
- 不同点:DataSet还可以映射成java对象 运行时,DataSet也会自动优化
- DataFrame与DataSet的比较 相同点:都有schema 不同点:DataFrame没有编译器检查机制
DataSet有编译器检查机制
编程入口
- 在sparkSql中,不再使用SparkConext,但是也会依赖于SparkContext。
- 在Spark2.0以前,具有两个入口,分别是
- SqlContext,就是提供了一些算子及其普通操作
- HiveContext:是SqlContext的子类,提供了一些特殊的方法,比如开窗函数等
- 在Spark2.0以后,将两者进行合并,成了一个SparkSession,SparkSession的构建会使用工厂方法。
pom依赖
<properties>
<scala.version>2.11.8</scala.version>
<spark.version>2.2.3</spark.version>
</properties>
<dependencies>
<dependency>
<groupId>org.scala-lang</groupId>
<artifactId>scala-library</artifactId>
<version>${scala.version}</version>
</dependency>
<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-sql_2.11</artifactId>
<version>${spark.version}</version>
</dependency>
<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-hive_2.11</artifactId>
<version>${spark.version}</version>
</dependency>
</dependencies>
package com.qf.sql.day01
import org.apache.spark.sql.hive.HiveContext
import org.apache.spark.sql.{DataFrame, SQLContext, SparkSession}
import org.apache.spark.{SparkConf, SparkContext}
/**
* sparksql的程序入口方法
*/
object AppAccessDemo {
def main(args: Array[String]): Unit = {
//spark2.0以前,有两个,分别是sqlContext和hiveContext
val conf: SparkConf = new SparkConf().setMaster("local").setAppName("test")
val sc = new SparkContext(conf)
//第一种:获取一个sqlContext
// val sqlContext = new SQLContext(sc)
// val df: DataFrame = sqlContext.read.json("data/a.json")
// df.show()
//第二种:获取一个hiveContext
// val hiveContext = new HiveContext(sc)
// val frame: DataFrame = hiveContext.table("")
// frame.show()
//第三种:使用spark2.0以后的SparkSession对象
val spark: SparkSession = SparkSession.builder().config(conf).getOrCreate()
val df: DataFrame = spark.read.json("data/a.json")
df.show()
sc.stop()
}
}
sparksql的基本编程
SparkSession的三种创建方式
package com.qf.sql.day01
import org.apache.spark.sql.hive.HiveContext
import org.apache.spark.sql.{DataFrame, SQLContext, SparkSession}
import org.apache.spark.{SparkConf, SparkContext}
/**
* sparksql的程序入口方法
*/
object _01AppAccessDemo {
def main(args: Array[String]): Unit = {
//spark2.0以前,有两个,分别是sqlContext和hiveContext
val conf: SparkConf = new SparkConf().setMaster("local").setAppName("test")
val sc = new SparkContext(conf)
//第一种:获取一个sqlContext
// val sqlContext = new SQLContext(sc)
// val df: DataFrame = sqlContext.read.json("data/a.json")
// df.show()
//第二种:获取一个hiveContext
// val hiveContext = new HiveContext(sc)
// val frame: DataFrame = hiveContext.table("")
// frame.show()
//第三种:使用spark2.0以后的SparkSession对象
val spark: SparkSession = SparkSession.builder().config(conf).getOrCreate()
val df: DataFrame = spark.read.json("data/a.json")
df.show()
sc.stop()
}
}
SparkSql的常用方法
几个常用的算子
package com.qf.sql.day01
import org.apache.spark.sql.types.StructType
import org.apache.spark.sql.{Column, DataFrame, SparkSession}
object _03SparkSqlFirst {
def main(args: Array[String]): Unit = {
//获取SparkSession对象
val spark = SparkSession.builder().appName("test").master("local").getOrCreate()
import spark.implicits._
//读取一个json文件,返回DataFrame对象,可以当成一个二维表
val df: DataFrame = spark.read.json("data/emp.json")
/**
* show(): 是一个行动算子,用于将结果打印到控制台上
* 无参数方法:表示列出Top20条
*
* show(numrows:Int) :可以指定要显示的行数
* show(numRows:Int,truncate:boolean) :第二个参数表示字段的值如果超出了20个字符,是否要截断显示
* true:表示截断,右对齐
* false:表示全显示,左对齐
*
* 相当于:select * from ....
*/
// df.show(10,false)
//显示schema: schema是元数据,即表名,表头,字段名,字段类型
// val schema: StructType = df.schema
// schema.printTreeString()
//简单写法,打印schema
// df.printSchema()
//select算子: 可以指定字段名称
// val df1: DataFrame = df.select("empno", "ename", "sal", "deptno")
// val df1: DataFrame = df.select(new Column("empno"),new Column("ename"),new Column("job"))
//如果想要使用$,需要导入SparkSession的隐式转换, 建议,不管是否使用隐式转换,也应该导入
// import spark.implicits._
// val df1: DataFrame = df.select($"empno",$"ename",$"job")
// df1.show()
//where算子: 用于指定条件的
/* val df2: DataFrame = df.select("empno", "ename", "sal", "deptno")
.where("deptno<30")
df2.show()
val df3: DataFrame = df.select("empno", "ename", "sal", "deptno")
.where("deptno<30 and sal<3000")
df3.show()
val df4: DataFrame = df.select("empno", "ename", "sal", "deptno")
.where("deptno<30")
.where("sal<3000")
df4.show()*/
//字段可以进行计算
/* val df5: DataFrame = df.select($"empno", $"ename",$"sal", $"sal"+1000, $"deptno")
val df5: DataFrame = df.select($"empno", $"ename",$"sal", $"sal".+(1000), $"deptno")
df5.show()*/
//取别名
// df.select($"empno", $"ename",$"sal".as("salary"), ($"sal"+1000).as("涨薪后"), $"deptno").show()
//分组算子:groupBy,
// 求总数: count
df.groupBy("deptno").count().show()
spark.stop()
}
}
其他Action操作
show
以表格的形式在输出中展示DataFrame中的数据,该方法有四种调用方式,分别为:
(1)show
只显示前20条记录。且过长的字符串会被截取
scala> employeeDF.show
+---+-----+------+--------+---------+
|age|depId|gender| name| salary|
+---+-----+------+--------+---------+
| 25| 1| male| Leo|20000.126|
| 30| 2|female| Marry| 25000.0|
| 35| 1| male| Jack| 15000.0|
| 42| 3| male| Tom| 18000.0|
| 21| 3|female| Kattie| 21000.0|
| 19| 2| male| Jen| 8000.0|
| 30| 2|female| Jen| 28000.0|
| 42| 3| male| Tom| 18000.0|
| 18| 3|female|XiaoFang| 58000.0|
+---+-----+------+--------+---------+
(2)show(numRows: Int)
显示numRows条
scala> employeeDF.show(3)
+---+-----+------+-----+---------+
|age|depId|gender| name| salary|
+---+-----+------+-----+---------+
| 25| 1| male| Leo|20000.126|
| 30| 2|female|Marry| 25000.0|
| 35| 1| male| Jack| 15000.0|
+---+-----+------+-----+---------+
only showing top 3 rows
(3)show(truncate: Boolean)
是否截取20个字符,默认为true。
scala> employeeDF.show(true)
+---+-----+------+--------+---------+
|age|depId|gender| name| salary|
+---+-----+------+--------+---------+
| 25| 1| male| Leo|20000.126|
| 30| 2|female| Marry| 25000.0|
| 35| 1| male| Jack| 15000.0|
| 42| 3| male| Tom| 18000.0|
| 21| 3|female| Kattie| 21000.0|
| 19| 2| male| Jen| 8000.0|
| 30| 2|female| Jen| 28000.0|
| 42| 3| male| Tom| 18000.0|
| 18| 3|female|XiaoFang| 58000.0|
+---+-----+------+--------+---------+
(4)show(numRows: Int, truncate: Int)
显示记录条数,以及截取字符个数,为0时表示不截取
scala> employeeDF.show(3, 2)
+---+-----+------+----+------+
|age|depId|gender|name|salary|
+---+-----+------+----+------+
| 25| 1| ma| Le| 20|
| 30| 2| fe| Ma| 25|
| 35| 1| ma| Ja| 15|
+---+-----+------+----+------+
only showing top 3 rows
collect
获取DataFrame中的所有数据并返回一个Array对象
scala> employeeDF.collect
res6: Array[org.apache.spark.sql.Row] = Array([25,1,male,Leo,20000.126], [30,2,female,Marry,25000.0], [35,1,male,Jack,15000.0], [42,3,male,Tom,18000.0], [21,3,female,Kattie,21000.0], [19,2,male,Jen,8000.0], [30,2,female,Jen,28000.0], [42,3,male,Tom,18000.0], [18,3,female,XiaoFang,58000.0])
collectAsList
功能和collect类似,只不过将返回结构变成了List对象
scala> employeeDF.collectAsList
res7: java.util.List[org.apache.spark.sql.Row] = [[25,1,male,Leo,20000.126], [30,2,female,Marry,25000.0], [35,1,male,Jack,15000.0], [42,3,male,Tom,18000.0], [21,3,female,Kattie,21000.0], [19,2,male,Jen,8000.0], [30,2,female,Jen,28000.0], [42,3,male,Tom,18000.0], [18,3,female,XiaoFang,58000.0]]
describe
这个方法可以动态的传入一个或多个String类型的字段名,结果仍然为DataFrame对象,用于统计数值类型字段的统计值,比如count, mean, stddev, min, max等。
scala> employeeDF.describe("age", "salary").show()
+-------+-----------------+------------------+
|summary| age| salary|
+-------+-----------------+------------------+
| count| 9| 9|
| mean|29.11111111111111|23444.458444444444|
| stddev|9.198429817697752|14160.779261027332|
| min| 18| 8000.0|
| max| 42| 58000.0|
+-------+-----------------+------------------+
first, head, take, takeAsList
(1)first获取第一行记录
(2)head获取第一行记录,head(n: Int)获取前n行记录
(3)take(n: Int)获取前n行数据
(4)takeAsList(n: Int)获取前n行数据,并以List的形式展现
以Row或者Array[Row]的形式返回一行或多行数据。first和head功能相同。
take和takeAsList方法会将获得到的数据返回到Driver端,所以,使用这两个方法时需要注意数据量,以免Driver发生OutOfMemoryError。
以上方法简单,使用过程和结果略。
4.2.3 条件查询和Join操作
以下返回为DataFrame类型的方法,可以连续调用。
where条件
等同于SQL语言中where关键字。传入筛选条件表达式,可以用and和or。得到DataFrame类型的返回结果。
scala> employeeDF.where("depId = 3").show
+---+-----+------+--------+-------+
|age|depId|gender| name| salary|
+---+-----+------+--------+-------+
| 42| 3| male| Tom|18000.0|
| 21| 3|female| Kattie|21000.0|
| 42| 3| male| Tom|18000.0|
| 18| 3|female|XiaoFang|58000.0|
+---+-----+------+--------+-------+
scala> employeeDF.where("depId = 3 and salary > 18000").show
+---+-----+------+--------+-------+
|age|depId|gender| name| salary|
+---+-----+------+--------+-------+
| 21| 3|female| Kattie|21000.0|
| 18| 3|female|XiaoFang|58000.0|
+---+-----+------+--------+-------+
filter过滤
传入筛选条件表达式,得到DataFrame类型的返回结果。和where使用条件相同。
scala> employeeDF.filter("depId = 3 or salary > 18000").show
+---+-----+------+--------+---------+
|age|depId|gender| name| salary|
+---+-----+------+--------+---------+
| 25| 1| male| Leo|20000.126|
| 30| 2|female| Marry| 25000.0|
| 42| 3| male| Tom| 18000.0|
| 21| 3|female| Kattie| 21000.0|
| 30| 2|female| Jen| 28000.0|
| 42| 3| male| Tom| 18000.0|
| 18| 3|female|XiaoFang| 58000.0|
+---+-----+------+--------+---------+
查询指定字段
select
根据传入的String类型字段名,获取指定字段的值,以DataFrame类型返回。
scala> employeeDF.select("name", "age").show(3)
+-----+---+
| name|age|
+-----+---+
| Leo| 25|
|Marry| 30|
| Jack| 35|
+-----+---+
only showing top 3 rows
还有一个重载的select方法,不是传入String类型参数,而是传入Column类型参数。可以实现select id, id+1 from table
这种逻辑。
scala> employeeDF.select(employeeDF("age"), employeeDF("age") + 1 ).show(5)
+---+---------+
|age|(age + 1)|
+---+---------+
| 25| 26|
| 30| 31|
| 35| 36|
| 42| 43|
| 21| 22|
+---+---------+
only showing top 5 rows
能得到Column类型的方法是column和col方法,col方法更简便一些。
scala> employeeDF.select(col("age"), col("age") + 1 ).show(5)
+---+---------+
|age|(age + 1)|
+---+---------+
| 25| 26|
| 30| 31|
| 35| 36|
| 42| 43|
| 21| 22|
+---+---------+
only showing top 5 rows
selectExpr
可以直接对指定字段调用UDF函数,或者指定别名等。传入String类型参数,得到DataFrame对象。
scala> employeeDF.selectExpr("name" , "depId as departmentId" , "round(salary)").show()
+--------+------------+----------------+
| name|departmentId|round(salary, 0)|
+--------+------------+----------------+
| Leo| 1| 20000.0|
| Marry| 2| 25000.0|
| Jack| 1| 15000.0|
| Tom| 3| 18000.0|
| Kattie| 3| 21000.0|
| Jen| 2| 8000.0|
| Jen| 2| 28000.0|
| Tom| 3| 18000.0|
|XiaoFang| 3| 58000.0|
+--------+------------+----------------+
col
只能获取一个字段,返回对象为Column类型。
scala> employeeDF.select(col("name")).show(3)
+-----+
| name|
+-----+
| Leo|
|Marry|
| Jack|
+-----+
only showing top 3 rows
drop
返回一个新的DataFrame对象,其中不包含去除的字段,一次只能去除一个字段。
scala> employeeDF.drop("gender").show(3)
+---+-----+-----+---------+
|age|depId| name| salary|
+---+-----+-----+---------+
| 25| 1| Leo|20000.126|
| 30| 2|Marry| 25000.0|
| 35| 1| Jack| 15000.0|
+---+-----+-----+---------+
only showing top 3 rows
limit
limit方法获取指定DataFrame的前n行记录,得到一个新的DataFrame对象。和take与head不同的是,limit方法不是Action操作。
scala> employeeDF.limit(3).show
+---+-----+------+-----+---------+
|age|depId|gender| name| salary|
+---+-----+------+-----+---------+
| 25| 1| male| Leo|20000.126|
| 30| 2|female|Marry| 25000.0|
| 35| 1| male| Jack| 15000.0|
+---+-----+------+-----+---------+
order by
orderBy和sort
按指定字段排序,默认为升序。加个“-”号表示降序排序。只对数字类型和日期类型生效。sort和orderBy使用方法相同。
scala> employeeDF.orderBy(- col("salary")).show(3)
scala> employeeDF.sort(- col("salary")).show(3)
+---+-----+------+--------+-------+
|age|depId|gender| name| salary|
+---+-----+------+--------+-------+
| 18| 3|female|XiaoFang|58000.0|
| 30| 2|female| Jen|28000.0|
| 30| 2|female| Marry|25000.0|
+---+-----+------+--------+-------+
only showing top 3 rows
sortWithinPartitions
和上面的sort方法功能类似,区别在于sortWithinPartitions方法返回的是按Partition排好序的DataFrame对象。
val repartitioned = employeeDF.repartition(2)
repartitioned.sortWithinPartitions("salary").show
+---+-----+------+--------+---------+
|age|depId|gender| name| salary|
+---+-----+------+--------+---------+
| 35| 1| male| Jack| 15000.0|
| 25| 1| male| Leo|20000.126|
| 21| 3|female| Kattie| 21000.0|
| 30| 2|female| Jen| 28000.0|
| 18| 3|female|XiaoFang| 58000.0|
| 19| 2| male| Jen| 8000.0|
| 42| 3| male| Tom| 18000.0|
| 42| 3| male| Tom| 18000.0|
| 30| 2|female| Marry| 25000.0|
+---+-----+------+--------+---------+
group by
groupBy
根据指定字段进行group by操作,可以指定多个字段。
groupBy方法有两种调用方式,可以传入String类型的字段名,也可传入Column类型的对象。
employeeDF.groupBy("depId").count.show
employeeDF.groupBy(employeeDF("depId")).count.show
+-----+-----+
|depId|count|
+-----+-----+
| 1| 2|
| 3| 4|
| 2| 3|
+-----+-----+
cube和rollup
功能类似于SQL中的group by cube/rollup。
cube:为指定表达式集的每个可能组合创建分组集。首先会对(A、B、C)进行group by,然后依次是(A、B),(A、C),(A),(B、C),(B),( C),最后对全表进行group by操作。
rollup:在指定表达式的每个层次级别创建分组集。group by A,B,C with rollup首先会对(A、B、C)进行group by,然后对(A、B)进行group by,然后是(A)进行group by,最后对全表进行group by操作。
employeeDF.cube("depId","gender").sum("salary").show()
+-----+------+------------------+
|depId|gender| sum(salary)|
+-----+------+------------------+
| 3|female| 79000.0|
| 1| male|35000.126000000004|
| 1| null|35000.126000000004|
| null| null| 211000.126|
| 3| null| 115000.0|
| 2| male| 8000.0|
| 3| male| 36000.0|
| null|female| 132000.0|
| 2| null| 61000.0|
| null| male| 79000.126|
| 2|female| 53000.0|
+-----+------+------------------+
employeeDF.rollup("depId","gender").sum("salary").show()
+-----+------+------------------+
|depId|gender| sum(salary)|
+-----+------+------------------+
| 3|female| 79000.0|
| 1| male|35000.126000000004|
| 1| null|35000.126000000004|
| null| null| 211000.126|
| 3| null| 115000.0|
| 2| male| 8000.0|
| 3| male| 36000.0|
| 2| null| 61000.0|
| 2|female| 53000.0|
+-----+------+------------------+
GroupedData对象
该方法得到的是GroupedData类型对象,在GroupedData的API中提供了group by之后的操作,比如:
-
max(colNames: String*),获取分组中指定字段或者所有的数字类型字段的最大值,只能作用于数字类型字段
-
min(colNames: String*),获取分组中指定字段或者所有的数字类型字段的最小值,只能作用于数字类型字段
-
mean(colNames: String*),获取分组中指定字段或者所有的数字类型字段的平均值,只能作用于数字类型字段
-
sum(colNames: String*),获取分组中指定字段或者所有的数字类型字段的和值,只能作用于数字类型字段
-
count(),获取分组中的元素个数
运行结果示例:
employeeDF.groupBy("depId").count.show +-----+-----+ |depId|count| +-----+-----+ | 1| 2| | 3| 4| | 2| 3| +-----+-----+ employeeDF.groupBy("depId").max("salary").show +-----+-----------+ |depId|max(salary)| +-----+-----------+ | 1| 20000.126| | 3| 58000.0| | 2| 28000.0| +-----+-----------+
distinct
distinct
返回当前DataFrame中不重复的Row记录。
employeeDF.distinct
+---+-----+------+--------+---------+
|age|depId|gender| name| salary|
+---+-----+------+--------+---------+
| 35| 1| male| Jack| 15000.0|
| 30| 2|female| Jen| 28000.0|
| 19| 2| male| Jen| 8000.0|
| 18| 3|female|XiaoFang| 58000.0|
| 25| 1| male| Leo|20000.126|
| 42| 3| male| Tom| 18000.0|
| 30| 2|female| Marry| 25000.0|
| 21| 3|female| Kattie| 21000.0|
+---+-----+------+--------+---------+
dropDuplicates
根据指定字段去重。类似于select distinct a, b操作。
employeeDF.dropDuplicates(Seq("name")).show
+---+-----+------+--------+---------+
|age|depId|gender| name| salary|
+---+-----+------+--------+---------+
| 35| 1| male| Jack| 15000.0|
| 42| 3| male| Tom| 18000.0|
| 19| 2| male| Jen| 8000.0|
| 30| 2|female| Marry| 25000.0|
| 21| 3|female| Kattie| 21000.0|
| 25| 1| male| Leo|20000.126|
| 18| 3|female|XiaoFang| 58000.0|
+---+-----+------+--------+---------+
聚合
聚合操作调用的是agg方法,该方法有多种调用方式。一般与groupBy方法配合使用。
以下示例其中最简单直观的一种用法,对age字段求最大值,对salary字段求和。
employeeDF.agg("age" -> "max", "salary" -> "sum").show
+--------+-----------+
|max(age)|sum(salary)|
+--------+-----------+
| 42| 211000.126|
+--------+-----------+
union
对两个DataFrame进行合并
employeeDF.union(employeeDF.limit(1)).show
+---+-----+------+--------+---------+
|age|depId|gender| name| salary|
+---+-----+------+--------+---------+
| 25| 1| male| Leo|20000.126|
| 30| 2|female| Marry| 25000.0|
| 35| 1| male| Jack| 15000.0|
| 42| 3| male| Tom| 18000.0|
| 21| 3|female| Kattie| 21000.0|
| 19| 2| male| Jen| 8000.0|
| 30| 2|female| Jen| 28000.0|
| 42| 3| male| Tom| 18000.0|
| 18| 3|female|XiaoFang| 58000.0|
| 25| 1| male| Leo|20000.126|
+---+-----+------+--------+---------+
join
在SQL语言中很多场景需要用join操作,DataFrame中同样也提供了join的功能。 并且提供了六个重载的join方法。
using一个字段形式
下面这种join类似于a join b using column1的形式,需要两个DataFrame中有相同的一个列名。
val employeeDF2 = spark.read.json("hdfs://node01:9000/company/employee.json")
employeeDF.join(employeeDF2, "depId").show
+-----+---+------+------+---------+---+------+--------+---------+
|depId|age|gender| name| salary|age|gender| name| salary|
+-----+---+------+------+---------+---+------+--------+---------+
| 1| 25| male| Leo|20000.126| 35| male| Jack| 15000.0|
| 1| 25| male| Leo|20000.126| 25| male| Leo|20000.126|
| 2| 30|female| Marry| 25000.0| 30|female| Jen| 28000.0|
| 2| 30|female| Marry| 25000.0| 19| male| Jen| 8000.0|
| 2| 30|female| Marry| 25000.0| 30|female| Marry| 25000.0|
| 1| 35| male| Jack| 15000.0| 35| male| Jack| 15000.0|
| 1| 35| male| Jack| 15000.0| 25| male| Leo|20000.126|
| 3| 42| male| Tom| 18000.0| 18|female|XiaoFang| 58000.0|
| 3| 42| male| Tom| 18000.0| 42| male| Tom| 18000.0|
| 3| 42| male| Tom| 18000.0| 21|female| Kattie| 21000.0|
| 3| 42| male| Tom| 18000.0| 42| male| Tom| 18000.0|
| 3| 21|female|Kattie| 21000.0| 18|female|XiaoFang| 58000.0|
| 3| 21|female|Kattie| 21000.0| 42| male| Tom| 18000.0|
| 3| 21|female|Kattie| 21000.0| 21|female| Kattie| 21000.0|
| 3| 21|female|Kattie| 21000.0| 42| male| Tom| 18000.0|
| 2| 19| male| Jen| 8000.0| 30|female| Jen| 28000.0|
| 2| 19| male| Jen| 8000.0| 19| male| Jen| 8000.0|
| 2| 19| male| Jen| 8000.0| 30|female| Marry| 25000.0|
| 2| 30|female| Jen| 28000.0| 30|female| Jen| 28000.0|
| 2| 30|female| Jen| 28000.0| 19| male| Jen| 8000.0|
+-----+---+------+------+---------+---+------+--------+---------+
using多个字段形式
除了上面这种using一个字段的情况外,还可以using多个字段,如下:
employeeDF.join(employeeDF2, Seq("name", "depId")).show
+--------+-----+---+------+---------+---+------+---------+
| name|depId|age|gender| salary|age|gender| salary|
+--------+-----+---+------+---------+---+------+---------+
| Leo| 1| 25| male|20000.126| 25| male|20000.126|
| Marry| 2| 30|female| 25000.0| 30|female| 25000.0|
| Jack| 1| 35| male| 15000.0| 35| male| 15000.0|
| Tom| 3| 42| male| 18000.0| 42| male| 18000.0|
| Tom| 3| 42| male| 18000.0| 42| male| 18000.0|
| Kattie| 3| 21|female| 21000.0| 21|female| 21000.0|
| Jen| 2| 19| male| 8000.0| 30|female| 28000.0|
| Jen| 2| 19| male| 8000.0| 19| male| 8000.0|
| Jen| 2| 30|female| 28000.0| 30|female| 28000.0|
| Jen| 2| 30|female| 28000.0| 19| male| 8000.0|
| Tom| 3| 42| male| 18000.0| 42| male| 18000.0|
| Tom| 3| 42| male| 18000.0| 42| male| 18000.0|
|XiaoFang| 3| 18|female| 58000.0| 18|female| 58000.0|
+--------+-----+---+------+---------+---+------+---------+
指定join类型
在上面的using多个字段的join情况下,可以写第三个String类型参数,指定join的类型,如下所示:
inner:内连
outer,full,full_outer:全连
left, left_outer:左连
right,right_outer:右连
left_semi:过滤出joinDF1中和joinDF2共有的部分
left_anti:过滤出joinDF1中joinDF2没有的部分
employeeDF.join(employeeDF2, Seq("name", "depId"), "inner").show
+--------+-----+---+------+---------+---+------+---------+
| name|depId|age|gender| salary|age|gender| salary|
+--------+-----+---+------+---------+---+------+---------+
| Leo| 1| 25| male|20000.126| 25| male|20000.126|
| Marry| 2| 30|female| 25000.0| 30|female| 25000.0|
| Jack| 1| 35| male| 15000.0| 35| male| 15000.0|
| Tom| 3| 42| male| 18000.0| 42| male| 18000.0|
| Tom| 3| 42| male| 18000.0| 42| male| 18000.0|
| Kattie| 3| 21|female| 21000.0| 21|female| 21000.0|
| Jen| 2| 19| male| 8000.0| 30|female| 28000.0|
| Jen| 2| 19| male| 8000.0| 19| male| 8000.0|
| Jen| 2| 30|female| 28000.0| 30|female| 28000.0|
| Jen| 2| 30|female| 28000.0| 19| male| 8000.0|
| Tom| 3| 42| male| 18000.0| 42| male| 18000.0|
| Tom| 3| 42| male| 18000.0| 42| male| 18000.0|
|XiaoFang| 3| 18|female| 58000.0| 18|female| 58000.0|
+--------+-----+---+------+---------+---+------+---------+
使用Column类型来join
如果不用using模式,灵活指定join字段的话,可以使用如下形式:
employeeDF.join(departmentDF, employeeDF("depId") === departmentDF("id")).show
+---+-----+------+--------+---------+---+--------------------+
|age|depId|gender| name| salary| id| name|
+---+-----+------+--------+---------+---+--------------------+
| 25| 1| male| Leo|20000.126| 1|Technical Department|
| 30| 2|female| Marry| 25000.0| 2|Financial Department|
| 35| 1| male| Jack| 15000.0| 1|Technical Department|
| 42| 3| male| Tom| 18000.0| 3| HR Department|
| 21| 3|female| Kattie| 21000.0| 3| HR Department|
| 19| 2| male| Jen| 8000.0| 2|Financial Department|
| 30| 2|female| Jen| 28000.0| 2|Financial Department|
| 42| 3| male| Tom| 18000.0| 3| HR Department|
| 18| 3|female|XiaoFang| 58000.0| 3| HR Department|
+---+-----+------+--------+---------+---+--------------------+
在指定join字段同时指定join类型
employeeDF.join(departmentDF, employeeDF("depId") === departmentDF("id"), "inner").show
+---+-----+------+--------+---------+---+--------------------+
|age|depId|gender| name| salary| id| name|
+---+-----+------+--------+---------+---+--------------------+
| 25| 1| male| Leo|20000.126| 1|Technical Department|
| 30| 2|female| Marry| 25000.0| 2|Financial Department|
| 35| 1| male| Jack| 15000.0| 1|Technical Department|
| 42| 3| male| Tom| 18000.0| 3| HR Department|
| 21| 3|female| Kattie| 21000.0| 3| HR Department|
| 19| 2| male| Jen| 8000.0| 2|Financial Department|
| 30| 2|female| Jen| 28000.0| 2|Financial Department|
| 42| 3| male| Tom| 18000.0| 3| HR Department|
| 18| 3|female|XiaoFang| 58000.0| 3| HR Department|
+---+-----+------+--------+---------+---+--------------------+
获取指定字段统计信息
stat方法可以用于计算指定字段或指定字段之间的统计信息,比如方差,协方差等。这个方法返回一个DataFramesStatFunctions类型对象。
下面代码演示根据字段,统计该字段值出现频率在30%以上的内容。在employeeDF中字段depId的内容为"1,2,3"。其中"1,2,3"出现频率均大于0.3。
employeeDF.stat.freqItems(Seq ("depId") , 0.3).show()
+---------------+
|depId_freqItems|
+---------------+
| [2, 1, 3]|
+---------------+
获取两个DataFrame中共有的记录
intersect方法可以计算出两个DataFrame中相同的记录。
employeeDF.intersect(employeeDF.limit(1)).show()
+---+-----+------+----+---------+
|age|depId|gender|name|salary |
+---+-----+------+----+---------+
|25 |1 |male |Leo |20000.126|
+---+-----+------+----+---------+
获取一个DataFrame中有另一个DataFrame中没有的记录
employeeDF.except(employeeDF.limit(1)).show()
+---+-----+------+--------+-------+
|age|depId|gender|name |salary |
+---+-----+------+--------+-------+
|42 |3 |male |Tom |18000.0|
|21 |3 |female|Kattie |21000.0|
|19 |2 |male |Jen |8000.0 |
|30 |2 |female|Jen |28000.0|
|35 |1 |male |Jack |15000.0|
|30 |2 |female|Marry |25000.0|
|18 |3 |female|XiaoFang|58000.0|
+---+-----+------+--------+-------+
操作字段名
withColumnRenamed
如果指定的字段名不存在,不进行任何操作。下面示例中将employeeDF中的depId字段重命名为departmentId。
employeeDF.withColumnRenamed("depId", "departmentId").show
+---+------------+------+--------+---------+
|age|departmentId|gender| name| salary|
+---+------------+------+--------+---------+
| 25| 1| male| Leo|20000.126|
| 30| 2|female| Marry| 25000.0|
| 35| 1| male| Jack| 15000.0|
| 42| 3| male| Tom| 18000.0|
| 21| 3|female| Kattie| 21000.0|
| 19| 2| male| Jen| 8000.0|
| 30| 2|female| Jen| 28000.0|
| 42| 3| male| Tom| 18000.0|
| 18| 3|female|XiaoFang| 58000.0|
+---+------------+------+--------+---------+
withColumn
whtiColumn(colName: String , col: Column)方法根据指定colName往DataFrame中新增一列,如果colName已存在,则会覆盖当前列。
employeeDF.withColumn("salary2", employeeDF("salary")).show
+---+-----+------+--------+---------+---------+
|age|depId|gender| name| salary| salary2|
+---+-----+------+--------+---------+---------+
| 25| 1| male| Leo|20000.126|20000.126|
| 30| 2|female| Marry| 25000.0| 25000.0|
| 35| 1| male| Jack| 15000.0| 15000.0|
| 42| 3| male| Tom| 18000.0| 18000.0|
| 21| 3|female| Kattie| 21000.0| 21000.0|
| 19| 2| male| Jen| 8000.0| 8000.0|
| 30| 2|female| Jen| 28000.0| 28000.0|
| 42| 3| male| Tom| 18000.0| 18000.0|
| 18| 3|female|XiaoFang| 58000.0| 58000.0|
+---+-----+------+--------+---------+---------+
行转列
有时候需要根据某个字段内容进行分割,然后生成多行,这时可以使用explode方法 。
下面代码中,根据name字段中的空格将字段内容进行分割,分割的内容存储在新的字段name_中,如下所示:
departmentDF.explode("name", "name_"){x:String => x.split(" ")}.show
+---+--------------------+----------+
| id| name| name_|
+---+--------------------+----------+
| 1|Technical Department| Technical|
| 1|Technical Department|Department|
| 2|Financial Department| Financial|
| 2|Financial Department|Department|
| 3| HR Department| HR|
| 3| HR Department|Department|
+---+--------------------+----------+
4.3 两种风格写法的说明
package com.qf.sql.day01
import org.apache.spark.sql.{DataFrame, Dataset, Row, SparkSession}
object _04SparkSqlExercise {
def main(args: Array[String]): Unit = {
val spark = SparkSession.builder().appName("test").master("local").getOrCreate()
import spark.implicits._
/**
* DSL风格的写法:
* Domain-specific language :特殊领域的语言
*/
val df: DataFrame = spark.read.json("data/emp.json")
val value: Dataset[Row] = df.select($"empno", $"ename",$"deptno")
.where("sal<3000 and deptno <30")
.orderBy($"deptno".desc)
value.show()
/**
* SQL风格的写法,说的就是SQl编程模型
*
* 注意:需要为读取到的数据维护一张表
*
* df.createOrReplaceGlobalTempView(): global是全局的意思,表示整个spark程序中都可以访问到
* df.createGlobalTempView() 没有global:仅当前任务中可以访问的
* df.createOrReplaceTempView() replace: 有replace,表示如果存在就会替换,不存在也创建
* df.createTempView() 无replace, 如果存在就报错,不存在就创建
*
*
* 注意:建议使用 createOrReplaceTempView 或 createTempView
*
*/
df.createOrReplaceTempView("emp")
val sql = "select deptno, count(*),max(sal),min(comm) from emp group by deptno"
val frame: DataFrame = spark.sql(sql.stripMargin)
frame.show()
spark.stop()
}
}
4.4 DataFrame和Dataset的创建方式
4.4.1 DataFrame的创建方式
1. 可以直接使用SparkSession.read获取DataFrameReader阅读器对象,然后调用相应的读取文件的方法来返回一
个Dataframe对象
2. 使用javaBean+反射机制,获取DataFrame对象
3. 使用动态编程方式
4.5 RDD与DataFrame、Dataset之间的转换
4.5.1 RDD的转换
RDD–>DataFrame: 可以调用createDataFrame()
RDD->Dataset:可以调用createDataset()
--也可以直接调用相应方法转
比如:toDF(),toDS(), 需要隐式导入
4.5.2 DataFrame的转换
DataFrame->RDD : df.rdd
DataFrame->Dataset :不能直接转,需要调用其他算子
4.5.3 Dataset的转换
Dataset->RDD : ds.rdd
Dataset->DataFrame : ds.toDF()