一、SparkSQL文件的读取与落地
1.1、文件读取
package com.xxx.SparkSQL.Day02
import java.util.Properties
import org.apache.spark.sql.{DataFrame, SparkSession}
object _01SparkFileLoad {
def main(args: Array[String]): Unit = {
val spark: SparkSession = SparkSession.builder().master("local").appName("load").getOrCreate()
val df: DataFrame = spark.read.json("data/emp.json")
import spark.implicits._
val df1: DataFrame = spark.read.format("json").load("data/emp.json")
//用option方法指定配置,下面指定分隔符
val df2: DataFrame = spark.read.format("csv").option("sep", ",").load("data/student.csv")
//下面指定第一行作为表头
val df3: DataFrame = spark.read.format("csv").option("header", true).load("data/ip-pprovince-count.csv")
val df4: DataFrame = spark.read.option("header","true").option("sep",";").csv("data/ip-pprovince-count.csv")
//连接mysql
val pro = new Properties()
pro.put("user","root")
pro.put("password","000000")
spark.read.jdbc("jdbc:mysql://localhost:3306/xiao","class",pro).show()
spark.stop()
}
}
1.2、文件的落地
package com.xxx.SparkSQL.Day02
import java.util.Properties
import org.apache.spark.rdd.RDD
import org.apache.spark.sql.{DataFrame, SaveMode, SparkSession}
object _02SparkFIleSave {
def main(args: Array[String]): Unit = {
val spark: SparkSession = SparkSession.builder().master("local").appName("save").getOrCreate()
val rdd1: RDD[String] = spark.sparkContext.textFile("data/emp.json")
//也可以读rdd
val df: DataFrame = spark.read.json(rdd1)
/**
* 保存
* 默认保存格式是parquet,还可以是json,csv,orc,text
* 注意:在保存为text时,只能是单列的,并且为String类型
*/
df.write.csv("out2")
//读mysql中的数据
val pro = new Properties()
pro.put("user","root")
pro.put("password","000000")
//设置参数
df.write.mode(SaveMode.Overwrite).jdbc("jdbc:mysql://localhost:3306/database","emp222",pro)
spark.stop()
}
}
二、和Hive的整合
和hive整合要在IDEA中的resource中导入三个包
hadoop的 core-site.xml hdfs-site.xml
hive的 hive-site.xml
SparkSQL和Hive的整合实际上只是从hive中拉取表,(连接memstore:元数据),再到hdfs中拉取数据到内存中,用Spark进行计算,执行引擎是spark,而不是mr。
package com.xxx.SparkSQL.Day02
import org.apache.spark.sql.{DataFrame, SaveMode, SparkSession}
object _03SparkSqlToHive {
def main(args: Array[String]): Unit = {
//记得开启hive支持
val spark: SparkSession = SparkSession.builder().master("local").appName("tohive").enableHiveSupport().getOrCreate()
//读取hive中的表
val df: DataFrame = spark.table("myhive.emp")
//使用DSL风格查询每个部门的平均工资,最高工资,最低工资,总人数
df.groupBy("deptno").avg("sal").show() //平均工资
df.groupBy("deptno").max("sal").show() //最高工资
df.groupBy("deptno").min("sal").show() //最低工资
df.groupBy("deptno").count().show() //总人数
//另一种写法
df.groupBy("deptno").agg("max" -> "sal", "min" -> "sal", "avg" -> "sal").show()
//使用sql风格查询
//首先维护一张表
df.createTempView("emp")
spark.sql(
"""
|select
|deptno,
|avg(nvl(sal,0)),
|max(sal),
|min(sal),
|count(1)
|from emp
|groupBy deptno
|""".stripMargin).show()
/**
* 下面是将DataFrame保存到hive中
* saveAsTable(tableName:String), 可以将数据作为一张表保存到hive中。
* insertInto(tableName:String) 向表中插入数据, 表不存在会抛异常
* mode(..).insertInto(...) :默认的模式就是追加
* SaveMode.Append
* SaveMode.ErrorIfExists 不生效
* SaveMode.Overwrite 覆盖,重写,生效
* SaveMode.Ignore 忽略不生效,但是是追加。
*/
//此方法会自动创建表
df.write.saveAsTable("myhive.emp111")
//可以设置保存模式,不写默认为追加,表要提前存在
df.write.mode(SaveMode.Overwrite).insertInto("myhive.emp222")
spark.stop()
}
}
三、内置函数
注意: 使用内置函数时要导包
package com.xxx.SparkSQL.Day02
import org.apache.spark.sql.{DataFrame, SparkSession}
object _04SparkInnerFunctionDemo {
def main(args: Array[String]): Unit = {
val spark = SparkSession.builder().master("local").appName("function").getOrCreate()
val df: DataFrame = spark.read.json("file:///E:\\IDEAproject\\SparkSQL\\data\\emp.json")
import spark.implicits._
//计算每个部门的最高工资,最高奖金,总人数
df.groupBy("deptno","job").agg("sal"->"max","comm"->"max","empno"->"count").show()
//使用内置函数,需要导包
import org.apache.spark.sql.functions._
df.groupBy("deptno","job").agg(max("sal"),
min("sal"),avg("sal"),
count("sal"),
countDistinct("sal")).show()
}
}
四、用户自定义函数
UDF:用户自定义函数(一对一)
UDAF:用户自定义聚合函数(多对一)
UDTF:用户自定义表生成函数(一对多)
4.1、UDF
案例1、
package com.xxx.SparkSQL.Day02
import org.apache.spark.sql.{DataFrame, SparkSession}
/**
* 用户自定义函数:
* 1、定义一个函数
* 2、注册函数:
* register(函数名:String,函数:Function)
*/
object _05UserDefineFunction {
def main(args: Array[String]): Unit = {
val spark = SparkSession.builder().master("local").appName("function").getOrCreate()
val df: DataFrame = spark.read.json("file:///E:\\IDEAproject\\SparkSQL\\data\\emp.json")
import spark.implicits._
//定义一个函数
val getLength=(str:String)=>str.length
//维护一个临时表
df.createTempView("emp")
//注册函数
spark.udf.register("getlength",getLength)
spark.sql(
"""
|
|""".stripMargin)
spark.stop()
}
}
案例2、
package com.xxx.SparkSQL.Day02
import org.apache.spark.sql.{DataFrame, SparkSession}
object _06UserDefineFunction {
def main(args: Array[String]): Unit = {
val spark = SparkSession.builder().master("local").appName("function").getOrCreate()
val df: DataFrame = spark.read.json("file:///E:\\IDEAproject\\SparkSQL\\data\\emp.json")
import spark.implicits._
//
df.createTempView("emp")
// spark.sql(
// """
// |select
// |ename,
// |job,
// |sal,
// |case when sal>3000 then 'level1'
// |when sal>1500 then 'level2'
// |else 'level3' end as level
// |from
// |emp
// |""".stripMargin).show()
//注册
spark.udf.register("getLevel",getLevel _)
spark.sql(
"""
|select
|job,
|sal,
|getLevel(sal)
|from
|emp
|""".stripMargin).show()
spark.stop()
}
//自定义函数
def getLevel(sal:Int): String ={
if (sal > 3000) "level1" else if (sal > 1500) "level2" else "level3"
}
}
4.2、UDAF
package com.qf.sql.day02
import org.apache.spark.rdd.RDD
import org.apache.spark.sql.expressions.{MutableAggregationBuffer, UserDefinedAggregateFunction}
import org.apache.spark.sql.types.{DataType, DataTypes, StructField, StructType}
import org.apache.spark.sql.{Dataset, Row, SparkSession}
/**
* 用户自定义分析函数UDAF案例演示:
* 自定义一个求平均值的函数: 多对一。
*
* @param id
* @param name
* @param score
*/
case class Score(id:Int,name:String,score:Double)
object Test04 {
def main(args: Array[String]): Unit = {
val spark = SparkSession.builder().appName("udaf").master("local").getOrCreate()
val list = List(
Score(1,"张三",99.9),
Score(2,"李四",88.9),
Score(3,"小明",77.9),
Score(1,"张三",91.9),
Score(2,"李四",81.9),
Score(3,"小明",71.9)
)
import spark.implicits._
//scala的对象可以直接转DS或者是DF, 当然需要导入隐式转换
val ds: Dataset[Score] = list.toDS()
/*维护一张表*/
ds.createTempView("tmp")
//注册函数:
spark.udf.register("myavg",new MyAvgUDAF)
val sql = "select avg(score), myavg(score),name from tmp group by name"
spark.sql(sql).show()
}
}
/**
* 1:需要继承 UserDefinedAggregateFunction
* 2:重写方法
*/
class MyAvgUDAF extends UserDefinedAggregateFunction{
/**
* 指定用户自定义udaf输入参数的元数据
* @return
*/
override def inputSchema: StructType = {
StructType(Array(StructField("score",DataTypes.DoubleType)))
}
/**
* udaf自定义函数求解过程中的临时变量的数据类型
*
* 因为要求平均值,正常逻辑是求出总和以及个数,然后做除法运算,因此要有两个临时变量
* @return
*/
override def bufferSchema: StructType = {
StructType(Array(
StructField("sum",DataTypes.DoubleType),
StructField("count",DataTypes.IntegerType)
))
}
/**
* udaf返回值的数据类型
* @return
*/
override def dataType: DataType = DataTypes.DoubleType
override def deterministic: Boolean = true
/**
* 临时变量是存储在buffer中,我们定义的buffer有两个元素,第一元素是sum,第二个元素是count
* @param buffer
*/
override def initialize(buffer: MutableAggregationBuffer): Unit = {
buffer.update(0,0d) //设置第一个元素的的初始值为0d
buffer.update(1,0) // 设置第二个元素的初始值为0
}
/**
* 分区内的局部累加操作
* @param buffer
* @param input
*/
override def update(buffer: MutableAggregationBuffer, input: Row): Unit = {
val score: Double = input.getAs[Double](0)
buffer.update(0,buffer.getDouble(0)+score) //将新进来的行中的分数累加到第一个元素sum上
buffer.update(1,buffer.getInt(1)+1) //将第二个元素count累加一个1
}
/**
* 分区间的累加操作
* @param buffer1
* @param buffer2
*/
override def merge(buffer1: MutableAggregationBuffer, buffer2: Row): Unit = {
buffer1.update(0,buffer1.getDouble(0)+buffer2.getDouble(0))
buffer1.update(1,buffer1.getInt(1)+buffer2.getInt(1))
}
/**
* 用于计算输出结果
* @param buffer
* @return
*/
override def evaluate(buffer: Row): Any = buffer.getDouble(0)/buffer.getInt(1)
}