文章目录
- 一、概述
- 二、编程
- 2.1 DataFrame
- 2.2 DataSet
- 2.3 DF <=> DS
- 2.4 RDD/DF/DS 关系
- 2.5 IDEA创建SparkSQL程序
- 2.6 用户自定义函数
- 三、数据的加载与保存
- 3.1 通用加载保存方式
- 3.1.1 加载数据
- 3.1.2 保存数据
- 3.1.3 默认数据源
- 3.2 JSON
- 3.3 MySQL
- 3.4 Hive
- 四、项目实战
- 4.1 需求简介
- 4.2 Hive SQL 实现
- 4.3 Spark 代码实现
- 4.4 自定义函数实现城市备注
一、概述
- 简介
- Spark SQL是Spark用于结构化数据(
structured data)处理的Spark模块。 - Hive SQL转换成 MapReduce然后提交到集群上执行，大大简化了编写 MapReduce的程序的复杂性，但MapReduce这种计算模型执行效率比较慢。
- Spark SQL先将数据转换成 RDD 然后提交到集群执行，执行效率非常快。
- DataFrame
- 在Spark中，DataFrame是一种以RDD为基础的分布式数据集，类似于传统数据库中的二维表格。
- DataFrame与RDD的主要区别在于，前者带有schema元信息，即DataFrame所表示的二维表数据集的每一列都带有名称和类型。
- 反观RDD，由于无从得知所存数据元素的具体内部结构，Spark Core只能在stage层面进行简单、通用的流水线优化。
- DataSet
- DataSet是分布式数据集合，是DataFrame的一个扩展。
- 它提供了RDD的优势（强类型，使用强大的lambda函数的能力）以及Spark SQL优化执行引擎的优点。
- 用样例类来定义DataSet中数据的结构信息，样例类中每个属性的名称直接映射到DataSet中的字段名称；
- DataSet是强类型的。比如可以有DataSet[Car]，DataSet[Person]。
- DataFrame是DataSet的特例，DataFrame=DataSet[Row] ，所以可以通过as方法将DataFrame转换为DataSet。Row是一个类型，跟Car、Person这些的类型一样，所有的表结构信息都用Row来表示。
二、编程
SparkSession是SQLContext和HiveContext的组合，其内部封装了sparkContext，所以计算实际上是由sparkContext完成的。
2.1 DataFrame
- 创建方式
- 通过Spark的数据源进行创建；
- 从一个存在的RDD进行转换；
- 从Hive Table进行查询返回。
- 创建DS
scala> spark.read.json("/spark/data/sql/people.json")
res1: org.apache.spark.sql.DataFrame = [age: bigint, name: string]
scala> res1.show
+---+--------+
|age| name|
+---+--------+
| 18|qiaofeng|
| 19| duanyu|
| 20| xuzhu|
+---+--------+
scala>- SQL 风格语法
普通临时表是Session范围内的，如果想应用范围内有效，可以使用全局临时表。使用全局临时表时需要全路径访问，如：global_temp.people。
scala> val df = spark.read.json("/spark/data/sql/people.json")
df: org.apache.spark.sql.DataFrame = [age: bigint, name: string]
scala> df.create
createGlobalTempView createOrReplaceTempView createTempView
scala> df.createTempView("people")
scala> spark.sql("SELECT * FROM people").show
+---+--------+
|age| name|
+---+--------+
| 18|qiaofeng|
| 19| duanyu|
| 20| xuzhu|
+---+--------+
scala>- DSL 风格语法
DataFrame提供一个特定领域语言(domain-specific language, DSL)去管理结构化的数据，可以在 Scala, Java, Python 和 R 中使用 DSL，使用 DSL 语法风格不必去创建临时视图了。
scala> val df = spark.read.json("/spark/data/sql/people.json")
df: org.apache.spark.sql.DataFrame = [age: bigint, name: string]
scala> df.printSchema
root
|-- age: long (nullable = true)
|-- name: string (nullable = true)
scala> df.select("name").show
+--------+
| name|
+--------+
|qiaofeng|
| duanyu|
| xuzhu|
+--------+
scala> df.select("*").show
+---+--------+
|age| name|
+---+--------+
| 18|qiaofeng|
| 19| duanyu|
| 20| xuzhu|
+---+--------+
scala> df.select($"name",$"age"+1).show
+--------+---------+
| name|(age + 1)|
+--------+---------+
|qiaofeng| 19|
| duanyu| 20|
| xuzhu| 21|
+--------+---------+
scala>- RDD <=> DF
如果需要RDD与DF或者DS之间操作，那么都需要引入 import spark.implicits._ （spark不是包名，而是sparkSession对象的名称，所以必须先创建SparkSession对象再导入. implicits是一个内部object）
scala> import spark.implicits._
import spark.implicits._
scala> val peopleRDD = sc.textFile("/spark/data/sql/people.txt")
peopleRDD: org.apache.spark.rdd.RDD[String] = /spark/data/sql/people.txt MapPartitionsRDD[26] at textFile at <console>:27
scala> peopleRDD.map{line=>{val fields=line.split(",");(fields(0).trim.toInt,fields(1))}}.toDF("age","name").show
+---+--------+
|age| name|
+---+--------+
| 18|qiaofeng|
| 19| duanyu|
| 20| xuzhu|
+---+--------+
scala>scala> val df = spark.read.json("/spark/data/sql/people.json")
df: org.apache.spark.sql.DataFrame = [age: bigint, name: string]
scala> df.rdd.collect
res12: Array[org.apache.spark.sql.Row] = Array([18,qiaofeng], [19,duanyu], [20,xuzhu])
scala>2.2 DataSet
DataSet是具有强类型的数据集合，需要提供对应的类型信息。
- RDD => DS
scala> val peopleRDD = sc.textFile("/spark/data/sql/people.txt")
peopleRDD: org.apache.spark.rdd.RDD[String] = /spark/data/sql/people.txt MapPartitionsRDD[39] at textFile at <console>:27
scala> case class Person(age:Int, name:String)
defined class Person
scala> peopleRDD.map{line=>{val fields=line.split(",");Person(fields(0).trim.toInt,fields(1))}}.toDS
res13: org.apache.spark.sql.Dataset[Person] = [age: int, name: string]
scala> res13.show
+---+--------+
|age| name|
+---+--------+
| 18|qiaofeng|
| 19| duanyu|
| 20| xuzhu|
+---+--------+
scala>- DS => RDD
scala> val DS = Seq(Person(32,"zhangsan")).toDS()
DS: org.apache.spark.sql.Dataset[Person] = [age: int, name: string]
scala> DS.rdd
res15: org.apache.spark.rdd.RDD[Person] = MapPartitionsRDD[47] at rdd at <console>:31
scala>2.3 DF <=> DS
在使用一些特殊的操作时，一定要加上 import spark.implicits._ 不然toDF、toDS无法使用。
- DF => DS
scala> val df = spark.read.json("/spark/data/sql/people.json")
df: org.apache.spark.sql.DataFrame = [age: bigint, name: string]
scala> case class Person(age:Long, name:String)
defined class Person
scala> df.as[Person]
res18: org.apache.spark.sql.Dataset[Person] = [age: bigint, name: string]
scala>- DF <= DS
scala> case class Person(name: String,age: Long)
defined class Person
scala> val ds = Seq(Person("zhangwuji",32)).toDS()
ds: org.apache.spark.sql.Dataset[Person] = [name: string, age: bigint]
scala> var df = ds.toDF
df: org.apache.spark.sql.DataFrame = [name: string, age: bigint]
scala> df.show
+---------+---+
| name|age|
+---------+---+
|zhangwuji| 32|
+---------+---+
scala>2.4 RDD/DF/DS 关系
RDD (Spark1.0) —> Dataframe(Spark1.3) —> Dataset(Spark1.6)，在后期的Spark版本中，DataSet有可能会逐步取代RDD和DataFrame成为唯一的API接口。
- 共性
- RDD、DataFrame、DataSet全都是spark平台下的分布式弹性数据集，为处理超大型数据提供便利;
- 三者都有惰性机制，在进行创建、转换，如map方法时，不会立即执行，只有在遇到Action如foreach时，三者才会开始遍历运算;
- 三者有许多共同的函数，如filter，排序等;
- 在对DataFrame和Dataset进行操作许多操作都需要这个包:import spark.implicits._（在创建好SparkSession对象后尽量直接导入）
- 三者都会根据 Spark 的内存情况自动缓存运算，这样即使数据量很大，也不用担心会内存溢出
- 三者都有partition的概念
- DataFrame和Dataset均可使用模式匹配获取各个字段的值和类型
- 异性
- RDD：RDD一般和Spark MLib同时使用
- DataFrame
2.1 DataFrame每一行的类型固定为Row，每一列的值没法直接访问，只有通过解析才能获取各个字段的值
2.2 DataFrame与DataSet均支持 SparkSQL 的操作，比如select，groupby之类，还能注册临时表/视窗，进行 sql 语句操作
2.3 DataFrame与DataSet支持一些特别方便的保存方式，比如保存成csv，可以带上表头，这样每一列的字段名一目了然 - DataSet
3.1 Dataset和DataFrame拥有完全相同的成员函数，区别只是每一行的数据类型不同。 DataFrame其实就是DataSet的一个特例 type DataFrame = Dataset[Row]
3.2 DataFrame也可以叫Dataset[Row],每一行的类型是Row，不解析，每一行究竟有哪些字段，各个字段又是什么类型都无从得知，只能用上面提到的getAS方法或者共性中的第七条提到的模式匹配拿出特定字段。而Dataset中，每一行是什么类型是不一定的，在自定义了case class之后可以很自由的获得每一行的信息
- 相互间转换
2.5 IDEA创建SparkSQL程序
<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-sql_2.11</artifactId>
<version>2.1.1</version>
</dependency>object SparkSQL01_Demo {
def main(args: Array[String]): Unit = {
//创建上下文环境配置对象
val conf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("SparkSQL01_Demo")
//创建SparkSession对象
val spark: SparkSession = SparkSession.builder().config(conf).getOrCreate()
//RDD=>DataFrame=>DataSet转换需要引入隐式转换规则，否则无法转换
//spark不是包名，是上下文环境对象名
import spark.implicits._
//读取json文件 创建DataFrame {"username": "lisi","age": 18}
val df: DataFrame = spark.read.json("D:\\dev\\workspace\\spark-bak\\spark-bak-00\\input\\test.json")
//df.show()
//SQL风格语法
df.createOrReplaceTempView("user")
//spark.sql("select avg(age) from user").show
//DSL风格语法
//df.select("username","age").show()
//*****RDD=>DataFrame=>DataSet*****
//RDD
val rdd1: RDD[(Int, String, Int)] = spark.sparkContext.makeRDD(List((1,"qiaofeng",30),(2,"xuzhu",28),(3,"duanyu",20)))
//DataFrame
val df1: DataFrame = rdd1.toDF("id","name","age")
//df1.show()
//DateSet
val ds1: Dataset[User] = df1.as[User]
//ds1.show()
//*****DataSet=>DataFrame=>RDD*****
//DataFrame
val df2: DataFrame = ds1.toDF()
//RDD 返回的RDD类型为Row，里面提供的getXXX方法可以获取字段值，类似jdbc处理结果集，但是索引从0开始
val rdd2: RDD[Row] = df2.rdd
//rdd2.foreach(a=>println(a.getString(1)))
//*****RDD=>DataSe*****
rdd1.map{
case (id,name,age)=>User(id,name,age)
}.toDS()
//*****DataSet=>=>RDD*****
ds1.rdd
//释放资源
spark.stop()
}
}
case class User(id:Int,name:String,age:Int)2.6 用户自定义函数
- UDF
输入一行，输出一行。
scala> val df = spark.read.json("/spark/data/sql/people.json")
df: org.apache.spark.sql.DataFrame = [age: bigint, name: string]
scala> spark.udf.register("addName",(x:String)=> "Name:"+x)
res20: org.apache.spark.sql.expressions.UserDefinedFunction = UserDefinedFunction(<function1>,StringType,Some(List(StringType)))
scala> df.createOrReplaceTempView("people")
scala> spark.sql("Select addName(name),age from people").show()
+-----------------+---+
|UDF:addName(name)|age|
+-----------------+---+
| Name:qiaofeng| 18|
| Name:duanyu| 19|
| Name:xuzhu| 20|
+-----------------+---+
scala>- UDAF
输入一行，输出多行。通过继承UserDefinedAggregateFunction来实现用户自定义聚合函数。
需求：实现求平均年龄
- RDD算子方式实现
object Spark00_TestAgeAvg {
def main(args: Array[String]): Unit = {
//1.创建SparkConf并设置App名称
val conf: SparkConf = new SparkConf().setAppName("SparkCoreTest").setMaster("local[*]")
//2.创建SparkContext，该对象是提交Spark App的入口
val sc: SparkContext = new SparkContext(conf)
val res: (Int, Int) = sc.makeRDD(List(("zhangsan", 20), ("lisi", 30), ("wangw", 40))).map {
case (name, age) => {
(age, 1)
}
}.reduce {
(t1, t2) => {
(t1._1 + t2._1, t1._2 + t2._2)
}
}
println(res._1/res._2)
// 关闭连接
sc.stop()
}
}- 自定义累加器方式实现（减少Shuffle）提高效率（模仿LongAccumulator累加器）
object Spark01_TestSer {
def main(args: Array[String]): Unit = {
//1.创建SparkConf并设置App名称
val conf: SparkConf = new SparkConf().setAppName("SparkCoreTest").setMaster("local[*]")
//2.创建SparkContext，该对象是提交Spark App的入口
val sc: SparkContext = new SparkContext(conf)
var sumAc = new MyAC
sc.register(sumAc)
sc.makeRDD(List(("zhangsan",20),("lisi",30),("wangw",40))).foreach{
case (name,age)=>{
sumAc.add(age)
}
}
println(sumAc.value)
// 关闭连接
sc.stop()
}
}
class MyAC extends AccumulatorV2[Int,Int]{
var sum:Int = 0
var count:Int = 0
override def isZero: Boolean = {
return sum ==0 && count == 0
}
override def copy(): AccumulatorV2[Int, Int] = {
val newMyAc = new MyAC
newMyAc.sum = this.sum
newMyAc.count = this.count
newMyAc
}
override def reset(): Unit = {
sum =0
count = 0
}
override def add(v: Int): Unit = {
sum += v
count += 1
}
override def merge(other: AccumulatorV2[Int, Int]): Unit = {
other match {
case o:MyAC=>{
sum += o.sum
count += o.count
}
case _=>
}
}
override def value: Int = sum/count
}- 自定义聚合函数实现-弱类型（应用于SparkSQL更方便）
object Spark00_TestAgeAvg {
def main(args: Array[String]): Unit = {
//创建上下文环境配置对象
val conf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("SparkSQL01_Demo")
//创建SparkSession对象
val spark: SparkSession = SparkSession.builder().config(conf).getOrCreate()
import spark.implicits._
//创建聚合函数
var myAverage = new MyAveragUDAF
//在spark中注册聚合函数
spark.udf.register("avgAge",myAverage)
//读取数据 {"username": "zhangsan","age": 20}
val df: DataFrame = spark.read.json("D:\\dev\\workspace\\spark-bak\\spark-bak-00\\input\\test.json")
//创建临时视图
df.createOrReplaceTempView("user")
//使用自定义函数查询
spark.sql("select avgAge(age) from user").show()
}
}
/*
定义类继承UserDefinedAggregateFunction，并重写其中方法
*/
class MyAveragUDAF extends UserDefinedAggregateFunction {
// 聚合函数输入参数的数据类型
def inputSchema: StructType = StructType(Array(StructField("age",IntegerType)))
// 聚合函数缓冲区中值的数据类型(age,count)
def bufferSchema: StructType = {
StructType(Array(StructField("sum",LongType),StructField("count",LongType)))
}
// 函数返回值的数据类型
def dataType: DataType = DoubleType
// 稳定性：对于相同的输入是否一直返回相同的输出。
def deterministic: Boolean = true
// 函数缓冲区初始化
def initialize(buffer: MutableAggregationBuffer): Unit = {
// 存年龄的总和
buffer(0) = 0L
// 存年龄的个数
buffer(1) = 0L
}
// 更新缓冲区中的数据
def update(buffer: MutableAggregationBuffer,input: Row): Unit = {
if (!input.isNullAt(0)) {
buffer(0) = buffer.getLong(0) + input.getInt(0)
buffer(1) = buffer.getLong(1) + 1
}
}
// 合并缓冲区
def merge(buffer1: MutableAggregationBuffer,buffer2: Row): Unit = {
buffer1(0) = buffer1.getLong(0) + buffer2.getLong(0)
buffer1(1) = buffer1.getLong(1) + buffer2.getLong(1)
}
// 计算最终结果
def evaluate(buffer: Row): Double = buffer.getLong(0).toDouble / buffer.getLong(1)
}- 自定义聚合函数实现-强类型（应用于DataSet的DSL更方便）
object Spark04_TestAgeAvg {
def main(args: Array[String]): Unit = {
//创建上下文环境配置对象
val conf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("SparkSQL01_Demo")
//创建SparkSession对象
val spark: SparkSession = SparkSession.builder().config(conf).getOrCreate()
import spark.implicits._
//读取数据 {"username": "zhangsan","age": 20}
val df: DataFrame = spark.read.json("D:\\dev\\workspace\\spark-bak\\spark-bak-00\\input\\test.json")
//封装为DataSet
val ds: Dataset[User01] = df.as[User01]
//创建聚合函数
var myAgeUdtf1 = new MyAveragUDAF1
//将聚合函数转换为查询的列
val col: TypedColumn[User01, Double] = myAgeUdtf1.toColumn
//查询
ds.select(col).show()
// 关闭连接
spark.stop()
}
}
//输入数据类型
case class User01(username:String,age:Long)
//缓存类型
case class AgeBuffer(var sum:Long,var count:Long)
/**
* 定义类继承org.apache.spark.sql.expressions.Aggregator
* 重写类中的方法
*/
class MyAveragUDAF1 extends Aggregator[User01,AgeBuffer,Double]{
override def zero: AgeBuffer = {
AgeBuffer(0L,0L)
}
override def reduce(b: AgeBuffer, a: User01): AgeBuffer = {
b.sum = b.sum + a.age
b.count = b.count + 1
b
}
override def merge(b1: AgeBuffer, b2: AgeBuffer): AgeBuffer = {
b1.sum = b1.sum + b2.sum
b1.count = b1.count + b2.count
b1
}
override def finish(buff: AgeBuffer): Double = {
buff.sum.toDouble/buff.count
}
//DataSet默认额编解码器，用于序列化，固定写法
//自定义类型就是produce 自带类型根据类型选择
override def bufferEncoder: Encoder[AgeBuffer] = {
Encoders.product
}
override def outputEncoder: Encoder[Double] = {
Encoders.scalaDouble
}
}三、数据的加载与保存
spark.read.load是加载数据的通用方法；df.write.save是保存数据的通用方法
3.1 通用加载保存方式
3.1.1 加载数据
- 直接加载Json数据
scala> spark.read.json("/opt/module/spark-local/people.json").show
+---+--------+
|age| name|
+---+--------+
| 18|qiaofeng|
| 19| duanyu|
| 20| xuzhu|- format指定加载数据类型
spark.read.format("…")[.option("…")].load("…")
- format("…")：指定加载的数据类型，包括"csv"、“jdbc”、“json”、“orc”、“parquet"和"textFile”
- load("…")：在"csv"、“jdbc”、“json”、“orc”、"parquet"和"textFile"格式下需要传入加载数据的路径
- option("…")：在"jdbc"格式下需要传入JDBC相应参数，url、user、password和dbtable
scala> spark.read.format("json").load ("/opt/module/spark-local/people.json").show
+---+--------+
|age| name|
+---+--------+
| 18|qiaofeng|
| 19| duanyu|
| 20| xuzhu|- 在文件上直接运行SQL
json表示文件的格式. 后面的文件具体路径需要用反引号括起来.
scala> spark.sql("select * from json.`/opt/module/spark-local/people.json`").show
+---+--------+
|age| name|
+---+--------+
| 18|qiaofeng|
| 19| duanyu|
| 20| xuzhu|
+---+--------+|3.1.2 保存数据
- write直接保存数据
//默认保存格式为parquet
scala> df.write.save("/opt/module/spark-local/output")
//可以指定为保存格式，直接保存，不需要再调用save了
scala> df.write.json("/opt/module/spark-local/output")- format 指定保存数据类型
df.write.format("…")[.option("…")].save("…")
- format("…")：指定保存的数据类型，包括"csv"、“jdbc”、“json”、“orc”、“parquet"和"textFile”。
- save ("…")：在"csv"、“orc”、"parquet"和"textFile"格式下需要传入保存数据的路径。
- option("…")：在"jdbc"格式下需要传入JDBC相应参数，url、user、password和dbtable
- 文件保存选项：
Scala/Java | Any Language | Meaning |
SaveMode.ErrorIfExists(default) | “error”(default) | 如果文件已经存在则抛出异常 |
SaveMode.Append | “append” | 如果文件已经存在则追加 |
SaveMode.Overwrite | “overwrite” | 如果文件已经存在则覆盖 |
SaveMode.Ignore | “ignore” | 如果文件已经存在则忽略 |
df.write.mode("append").json("/opt/module/spark-local/output")3.1.3 默认数据源
- 说明
- Spark SQL的默认数据源为Parquet格式。
- 数据源为Parquet文件时，Spark SQL可以方便的执行所有的操作，不需要使用format。
- 修改配置项spark.sql.sources.default，可修改默认数据源格式。
- 加载数据
val df = spark.read.load("/opt/module/spark-local/examples/src/main/resources/users.parquet").show
+------+--------------+----------------+
| name|favorite_color|favorite_numbers|
+------+--------------+----------------+
|Alyssa| null| [3, 9, 15, 20]|
| Ben| red| []|
+------+--------------+----------------+
df: Unit = ()- 保存数据
scala> var df = spark.read.json("/opt/module/spark-local/people.json")
//保存为parquet格式
scala> df.write.mode("append").save("/opt/module/spark-local/output")3.2 JSON
Spark SQL 能够自动推测 JSON数据集的结构，并将它加载为一个Dataset[Row]. 可以通过SparkSession.read.json()去加载一个 一个JSON 文件。
- 导入隐式转换
import spark.implicits._- 加载JSON文件
val path = "/opt/module/spark-local/people.json"
val peopleDF = spark.read.json(path)- 创建临时表
peopleDF.createOrReplaceTempView("people")- 数据查询
val teenagerNamesDF = spark.sql("SELECT name FROM people WHERE age BETWEEN 13 AND 19").show()
+------+
| name|
+------+
|Justin|
+------+3.3 MySQL
Spark SQL可以通过JDBC从关系型数据库中读取数据的方式创建DataFrame，通过对DataFrame一系列的计算后，还可以将数据再写回关系型数据库中。
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.27</version>
</dependency>- 读数据
object SparkSQL02_Datasource {
def main(args: Array[String]): Unit = {
//创建上下文环境配置对象
val conf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("SparkSQL01_Demo")
//创建SparkSession对象
val spark: SparkSession = SparkSession.builder().config(conf).getOrCreate()
import spark.implicits._
//方式1：通用的load方法读取
spark.read.format("jdbc")
.option("url", "jdbc:mysql://hadoop202:3306/test")
.option("driver", "com.mysql.jdbc.Driver")
.option("user", "root")
.option("password", "123456")
.option("dbtable", "user")
.load().show
//方式2:通用的load方法读取 参数另一种形式
spark.read.format("jdbc")
.options(Map("url"->"jdbc:mysql://hadoop202:3306/test?user=root&password=123456",
"dbtable"->"user","driver"->"com.mysql.jdbc.Driver")).load().show
//方式3:使用jdbc方法读取
val props: Properties = new Properties()
props.setProperty("user", "root")
props.setProperty("password", "123456")
val df: DataFrame = spark.read.jdbc("jdbc:mysql://hadoop202:3306/test", "user", props)
df.show
//释放资源
spark.stop()
}
}- 写数据
object SparkSQL03_Datasource {
def main(args: Array[String]): Unit = {
//创建上下文环境配置对象
val conf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("SparkSQL01_Demo")
//创建SparkSession对象
val spark: SparkSession = SparkSession.builder().config(conf).getOrCreate()
import spark.implicits._
val rdd: RDD[User2] = spark.sparkContext.makeRDD(List(User2("lisi", 20), User2("zs", 30)))
val ds: Dataset[User2] = rdd.toDS
//方式1：通用的方式 format指定写出类型
ds.write
.format("jdbc")
.option("url", "jdbc:mysql://hadoop202:3306/test")
.option("user", "root")
.option("password", "123456")
.option("dbtable", "user")
.mode(SaveMode.Append)
.save()
//方式2：通过jdbc方法
val props: Properties = new Properties()
props.setProperty("user", "root")
props.setProperty("password", "123456")
ds.write.mode(SaveMode.Append).jdbc("jdbc:mysql://hadoop202:3306/test", "user", props)
//释放资源
spark.stop()
}
}
case class User2(name: String, age: Long)3.4 Hive
- 包含 Hive 支持的 Spark SQL 可以支持 Hive 表访问、UDF (用户自定义函数)以及 Hive 查询语言(HiveQL/HQL)等。
- 若要把 Spark SQL 连接到一个部署好的 Hive 上，你必须把 hive-site.xml 复制到 Spark的配置文件目录中($SPARK_HOME/conf)。
- spark-shell默认是Hive支持的；代码中是默认不支持的，需要手动指定（加一个参数即可）。
- Hive in Spark-Shell
- 确定原有Hive是正常工作的
- 需要把hive-site.xml拷贝到spark的conf/目录下
- 如果以前hive-site.xml文件中，配置过Tez相关信息，注释掉
- 把Mysql的驱动copy到Spark的jars/目录下
- 需要提前启动hive服务，hive/bin/hiveservices.sh start
- 如果访问不到hdfs，则需把core-site.xml和hdfs-site.xml拷贝到conf/目录
[omm@bigdata01 module]$ cp hive/conf/hive-site.xml spark/conf/
[omm@bigdata01 module]$ cp /opt/soft/mysql-connector-java-5.1.48.jar spark/jars/
[omm@bigdata01 module]$ spark/bin/spark-shell
scala> spark.sql("show tables").show
+--------+--------------+-----------+
|database| tableName|isTemporary|
+--------+--------------+-----------+
| default| business| false|
| default| dept| false|
| default| emp| false|
| default| emp_sex| false|
| default| location| false|
| default| log_orc| false|
| default| log_text| false|
| default| movie_info| false|
| default| person_info| false|
| default| score| false|
| default| stu_buck| false|
| default| user_orc| false|
| default| user_ori| false|
| default|video_category| false|
| default| video_orc| false|
| default| video_ori| false|
+--------+--------------+-----------+
scala> spark.sql("select * from emp").show
+-----+--------+---------+----+----------+------+------+------+
|empno| ename| job| mgr| hiredate| sal| comm|deptno|
+-----+--------+---------+----+----------+------+------+------+
| 7335|zhangsan| null|null| null| null| null| 50|
| 7369| SMITH| CLERK|7902|1980-12-17| 800.0| null| 20|
| 7499| ALLEN| SALESMAN|7698| 1981-2-20|1600.0| 300.0| 30|
| 7521| WARD| SALESMAN|7698| 1981-2-22|1250.0| 500.0| 30|
| 7566| JONES| MANAGER|7839| 1981-4-2|2975.0| null| 20|
| 7654| MARTIN| SALESMAN|7698| 1981-9-28|1250.0|1400.0| 30|
| 7698| BLAKE| MANAGER|7839| 1981-5-1|2850.0| null| 30|
| 7782| CLARK| MANAGER|7839| 1981-6-9|2450.0| null| 10|
| 7788| SCOTT| ANALYST|7566| 1987-4-19|3000.0| null| 20|
| 7839| KING|PRESIDENT|null|1981-11-17|5000.0| null| 10|
| 7844| TURNER| SALESMAN|7698| 1981-9-8|1500.0| 0.0| 30|
| 7876| ADAMS| CLERK|7788| 1987-5-23|1100.0| null| 20|
| 7900| JAMES| CLERK|7698| 1981-12-3| 950.0| null| 30|
| 7902| FORD| ANALYST|7566| 1981-12-3|3000.0| null| 20|
| 7934| MILLER| CLERK|7782| 1982-1-23|1300.0| null| 10|
+-----+--------+---------+----+----------+------+------+------+
scala>- Hive in IDEA
- 添加依赖
<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-hive_2.11</artifactId>
<version>2.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>1.2.1</version>
</dependency>- 拷贝hive-site.xml到resources目录
- 编写代码
object SparkSQL08_Hive{
def main(args: Array[String]): Unit = {
//创建上下文环境配置对象
val conf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("SparkSQL01_Demo")
val spark: SparkSession = SparkSession
.builder()
.enableHiveSupport()
.master("local[*]")
.appName("SQLTest")
.getOrCreate()
spark.sql("show tables").show()
//释放资源
spark.stop()
}
}四、项目实战
4.1 需求简介
各区域热门商品Top3：这里的热门商品是从 点击量 的维度来看的，计算各个区域前三大热门商品，并备注上每个商品在主要城市中的分布比例，超过两个城市用其他显示。
地区 商品名称 | 点击次数 | 城市备注 |
华北 商品A | 100000 | 北京21.2%，天津13.2%，其他65.6% |
华北 商品P | 80200 | 北京63.0%，太原10%，其他27.0% |
华北 商品M | 40000 | 北京63.0%，太原10%，其他27.0% |
东北 商品J | 92000 | 大连28%，辽宁17.0%，其他 55.0% |
- 数据准备
- user_visit_action.txt
2019-07-17 95 26070e87-1ad7-49a3-8fb3-cc741facaddf 37 2019-07-17 00:00:02 手机 -1 -1 \N \N \N \N 3
2019-07-17 95 26070e87-1ad7-49a3-8fb3-cc741facaddf 48 2019-07-17 00:00:10 \N 16 98 \N \N \N \N 19
2019-07-17 95 26070e87-1ad7-49a3-8fb3-cc741facaddf 6 2019-07-17 00:00:17 \N 19 85 \N \N \N \N 7
2019-07-17 38 6502cdc9-cf95-4b08-8854-f03a25baa917 29 2019-07-17 00:00:19 \N 12 36 \N \N \N \N 5
2019-07-17 38 6502cdc9-cf95-4b08-8854-f03a25baa917 22 2019-07-17 00:00:28 \N -1 -1 \N \N 15,1,20,6,4 15,88,75 9- product_info.txt
1 商品_1 自营
2 商品_2 自营
3 商品_3 自营
4 商品_4 自营
5 商品_5 自营- city_info.txt
1 北京 华北
2 上海 华东
3 深圳 华南
4 广州 华南
5 武汉 华中- 建表语句
CREATE TABLE `user_visit_action`(
`date` string,
`user_id` bigint,
`session_id` string,
`page_id` bigint,
`action_time` string,
`search_keyword` string,
`click_category_id` bigint,
`click_product_id` bigint,
`order_category_ids` string,
`order_product_ids` string,
`pay_category_ids` string,
`pay_product_ids` string,
`city_id` bigint)
row format delimited fields terminated by '\t';
load data local inpath '/opt/module/data/user_visit_action.txt' into table user_visit_action;
CREATE TABLE `product_info`(
`product_id` bigint,
`product_name` string,
`extend_info` string)
row format delimited fields terminated by '\t';
load data local inpath '/opt/module/data/product_info.txt' into table product_info;
CREATE TABLE `city_info`(
`city_id` bigint,
`city_name` string,
`area` string)
row format delimited fields terminated by '\t';
load data local inpath '/opt/module/data/city_info.txt' into table city_info;4.2 Hive SQL 实现
- 从用户行为表中，查询所有点击记录，并和city_info/product_info进行连接
select
c.area,
p.product_name
from
user_visit_action a
join
city_info c
on
a.city_id = c.city_id
join
product_info p
on
a.click_product_id = p.product_id
where
a.click_product_id != -1
limit 10;
+---------+-----------------+
| c.area | p.product_name |
+---------+-----------------+
| 华北 | 商品_98 |
| 华北 | 商品_85 |
| 华中 | 商品_36 |
| 华南 | 商品_44 |
| 华东 | 商品_79 |
| 华南 | 商品_50 |
| 东北 | 商品_39 |
| 华南 | 商品_62 |
| 东北 | 商品_58 |
| 华东 | 商品_68 |
+---------+-----------------+
10 rows selected (25.726 seconds)
0: jdbc:hive2://bigdata01:10000>- 按照地区和商品的名称进行分组，统计出每个地区每个商品的点击总数
select
t1.area,
t1.product_name,
count(*) as product_click_count
from
(
select
c.area,
p.product_name
from
user_visit_action a
join
city_info c
on
a.city_id = c.city_id
join
product_info p
on
a.click_product_id = p.product_id
where
a.click_product_id != -1
) t1
group by t1.area,t1.product_name
limit 10;
+----------+------------------+----------------------+
| t1.area | t1.product_name | product_click_count |
+----------+------------------+----------------------+
| 东北 | 商品_1 | 130 |
| 东北 | 商品_10 | 146 |
| 东北 | 商品_100 | 131 |
| 东北 | 商品_11 | 137 |
| 东北 | 商品_12 | 152 |
| 东北 | 商品_13 | 158 |
| 东北 | 商品_14 | 143 |
| 东北 | 商品_15 | 147 |
| 东北 | 商品_16 | 139 |
| 东北 | 商品_17 | 136 |
+----------+------------------+----------------------+
10 rows selected (25.475 seconds)
0: jdbc:hive2://bigdata01:10000>- 针对每个地区，对商品点击数进行降序排序
select
t2.area,
t2.product_name,
t2.product_click_count,
row_number() over(partition by t2.area order by t2.product_click_count desc) cn
from
(
select
t1.area,
t1.product_name,
count(*) as product_click_count
from
(
select
c.area,
p.product_name
from
user_visit_action a
join
city_info c
on
a.city_id = c.city_id
join
product_info p
on
a.click_product_id = p.product_id
where
a.click_product_id != -1
) t1
group by t1.area,t1.product_name
) t2
limit 12;
+----------+------------------+-------------------------+-----+
| t2.area | t2.product_name | t2.product_click_count | cn |
+----------+------------------+-------------------------+-----+
| 东北 | 商品_41 | 169 | 1 |
| 东北 | 商品_91 | 165 | 2 |
| 东北 | 商品_93 | 159 | 3 |
| 东北 | 商品_58 | 159 | 4 |
| 东北 | 商品_83 | 158 | 5 |
| 东北 | 商品_13 | 158 | 6 |
| 东北 | 商品_37 | 155 | 7 |
| 东北 | 商品_72 | 154 | 8 |
| 东北 | 商品_27 | 154 | 9 |
| 东北 | 商品_32 | 154 | 10 |
| 东北 | 商品_2 | 152 | 11 |
| 东北 | 商品_86 | 152 | 12 |
+----------+------------------+-------------------------+-----+
12 rows selected (40.756 seconds)
0: jdbc:hive2://bigdata01:10000>- 各个地区的前三名
select
t3.area,
t3.product_name,
t3.product_click_count,
t3.cn
from
(
select
t2.area,
t2.product_name,
t2.product_click_count,
row_number() over(partition by t2.area order by t2.product_click_count desc) cn
from
(
select
t1.area,
t1.product_name,
count(*) as product_click_count
from
(
select
c.area,
p.product_name
from
user_visit_action a
join
city_info c
on
a.city_id = c.city_id
join
product_info p
on
a.click_product_id = p.product_id
where
a.click_product_id != -1
) t1
group by t1.area,t1.product_name
) t2
) t3
where t3.cn <= 3;
+----------+------------------+-------------------------+--------+
| t3.area | t3.product_name | t3.product_click_count | t3.cn |
+----------+------------------+-------------------------+--------+
| 东北 | 商品_41 | 169 | 1 |
| 东北 | 商品_91 | 165 | 2 |
| 东北 | 商品_93 | 159 | 3 |
| 华东 | 商品_86 | 371 | 1 |
| 华东 | 商品_75 | 366 | 2 |
| 华东 | 商品_47 | 366 | 3 |
| 华中 | 商品_62 | 117 | 1 |
| 华中 | 商品_4 | 113 | 2 |
| 华中 | 商品_29 | 111 | 3 |
| 华北 | 商品_42 | 264 | 1 |
| 华北 | 商品_99 | 264 | 2 |
| 华北 | 商品_19 | 260 | 3 |
| 华南 | 商品_23 | 224 | 1 |
| 华南 | 商品_65 | 222 | 2 |
| 华南 | 商品_50 | 212 | 3 |
| 西北 | 商品_15 | 116 | 1 |
| 西北 | 商品_2 | 114 | 2 |
| 西北 | 商品_22 | 113 | 3 |
| 西南 | 商品_1 | 176 | 1 |
| 西南 | 商品_44 | 169 | 2 |
| 西南 | 商品_60 | 163 | 3 |
+----------+------------------+-------------------------+--------+
21 rows selected (40.685 seconds)
0: jdbc:hive2://bigdata01:10000>4.3 Spark 代码实现
import org.apache.spark.SparkConf
import org.apache.spark.sql.SparkSession
object TopProduct3 {
def main(args: Array[String]): Unit = {
val conf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("TopProduct3");
val spark: SparkSession = SparkSession.builder().enableHiveSupport().config(conf).getOrCreate()
//--------------------------
//1. 从用户行为表中，查询所有点击记录，并和city_info/product_info进行连接
spark.sql(
"""
|select
| c.area,
| p.product_name
|from
| user_visit_action a
|join
| city_info c
|on
| a.city_id = c.city_id
|join
| product_info p
|on
| a.click_product_id = p.product_id
|where
| a.click_product_id != -1
""".stripMargin).createOrReplaceTempView("t1")
//2.按照地区和商品的名称进行分组，统计出每个地区每个商品的点击总数
spark.sql(
"""
|select
| t1.area,
| t1.product_name,
| count(*) as product_click_count
|from
| t1
|group by t1.area,t1.product_name
""".stripMargin).createOrReplaceTempView("t2")
//3. 针对每个地区，对商品点击数进行降序排序
spark.sql(
"""
|select
| t2.area,
| t2.product_name,
| t2.product_click_count,
| row_number() over(partition by t2.area order by t2.product_click_count desc) cn
|from
| t2
""".stripMargin).createOrReplaceTempView("t3")
//4.各个地区的前三名
spark.sql(
"""
|select
| t3.area,
| t3.product_name,
| t3.product_click_count,
| t3.cn
|from
| t3
|where t3.cn <= 3
""".stripMargin).show()
//--------------------------
spark.close()
}
}4.4 自定义函数实现城市备注
package com.simwor.bigdata.spark
import org.apache.spark.SparkConf
import org.apache.spark.sql.{Row, SparkSession}
import org.apache.spark.sql.expressions.{MutableAggregationBuffer, UserDefinedAggregateFunction}
import org.apache.spark.sql.types.{DataType, LongType, MapType, StringType, StructField, StructType}
import java.text.DecimalFormat
object TopProduct3 {
def main(args: Array[String]): Unit = {
val conf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("TopProduct3");
val spark: SparkSession = SparkSession.builder().enableHiveSupport().config(conf).getOrCreate()
//--------------------------
// 0 注册自定义聚合函数
spark.udf.register("city_remark", new AreaClickUDAF)
//1. 从用户行为表中，查询所有点击记录，并和city_info/product_info进行连接
spark.sql(
"""
|select
| c.*,
| p.product_name
|from
| user_visit_action a
|join
| city_info c
|on
| a.city_id = c.city_id
|join
| product_info p
|on
| a.click_product_id = p.product_id
|where
| a.click_product_id != -1
""".stripMargin).createOrReplaceTempView("t1")
//2.按照地区和商品的名称进行分组，统计出每个地区每个商品的点击总数
spark.sql(
"""
|select
| t1.area,
| t1.product_name,
| count(*) as product_click_count,
| city_remark(t1.city_name)
|from
| t1
|group by t1.area,t1.product_name
""".stripMargin).createOrReplaceTempView("t2")
//3. 针对每个地区，对商品点击数进行降序排序
spark.sql(
"""
|select
| *,
| row_number() over(partition by t2.area order by t2.product_click_count desc) cn
|from
| t2
""".stripMargin).createOrReplaceTempView("t3")
//4.各个地区的前三名
spark.sql(
"""
|select
| *
|from
| t3
|where t3.cn <= 3
""".stripMargin).show(false)
//--------------------------
spark.close()
}
}
class AreaClickUDAF extends UserDefinedAggregateFunction {
//输入的数据类型：北京 String
override def inputSchema: StructType = {
StructType(StructField("city_name", StringType) :: Nil)
}
//缓存的数据类型：北京->1000, 天津->5000 Map, 总的点击量 1000
override def bufferSchema: StructType = {
StructType(StructField("city_count", MapType(StringType, LongType)) :: StructField("total_count", LongType) :: Nil)
}
//输出的数据类型 "北京21.2%，天津13.2%，其他65.6%" String
override def dataType: DataType = StringType
//相同的输入是否返回相同的输出
override def deterministic: Boolean = true
//给缓存的数据初始化
override def initialize(buffer: MutableAggregationBuffer): Unit = {
//初始化map缓存
buffer(0) = Map[String, Long]()
//初始化总的点击量
buffer(1) = 0L
}
//分区内合并：Map[城市名, 点击量]
override def update(buffer: MutableAggregationBuffer, input: Row): Unit = {
//首先拿到城市名, 然后把城市名作为key去查看map中是否存在, 如果存在就把对应的值 +1, 如果不存在, 则直接0+1
val cityName = input.getString(0)
val map: Map[String, Long] = buffer.getAs[Map[String, Long]](0)
buffer(0) = map + (cityName -> (map.getOrElse(cityName, 0L) + 1L))
//碰到一个城市, 则总的点击量要+1
buffer(1) = buffer.getLong(1) + 1L
}
//分区间的合并
override def merge(buffer1: MutableAggregationBuffer, buffer2: Row): Unit = {
val map1 = buffer1.getAs[Map[String, Long]](0)
val map2 = buffer2.getAs[Map[String, Long]](0)
//把map1的键值对与map2中的累积, 最后赋值给buffer1
buffer1(0) = map1.foldLeft(map2) {
case(map, (k,v)) => map + (k -> (map.getOrElse(k, 0L) + v))
}
buffer1(1) = buffer1.getLong(1) + buffer2.getLong(1)
}
// 最终的输出. "北京21.2%，天津13.2%，其他65.6%"
override def evaluate(buffer: Row): Any = {
val cityCountMap = buffer.getAs[Map[String, Long]](0)
val totalCount = buffer.getLong(1)
var citysRatio: List[CityRemark] = cityCountMap.toList.sortBy(-_._2).take(2).map {
case (cityName, count) => {
CityRemark(cityName, count.toDouble / totalCount)
}
}
// 如果城市的个数超过2才显示其他
if (cityCountMap.size > 2) {
citysRatio = citysRatio :+ CityRemark("其他", citysRatio.foldLeft(1D)(_ - _.cityRatio))
}
citysRatio.mkString(", ")
}
}
case class CityRemark(cityName: String, cityRatio: Double) {
val formatter = new DecimalFormat("0.00%")
override def toString: String = s"$cityName:${formatter.format(cityRatio)}"
}+----+------------+-------------------+--------------------------------+---+
|area|product_name|product_click_count|areaclickudaf(city_name) |cn |
+----+------------+-------------------+--------------------------------+---+
|华东 |商品_86 |371 |上海:16.44%, 无锡:15.90%, 其他:67.65% |1 |
|华东 |商品_47 |366 |杭州:15.85%, 青岛:15.57%, 其他:68.58% |2 |
|华东 |商品_75 |366 |上海:17.49%, 无锡:15.57%, 其他:66.94% |3 |
|西北 |商品_15 |116 |西安:54.31%, 银川:45.69% |1 |
|西北 |商品_2 |114 |银川:53.51%, 西安:46.49% |2 |
|西北 |商品_22 |113 |西安:54.87%, 银川:45.13% |3 |
|华南 |商品_23 |224 |厦门:29.02%, 深圳:24.55%, 其他:46.43% |1 |
|华南 |商品_65 |222 |深圳:27.93%, 厦门:26.58%, 其他:45.50% |2 |
|华南 |商品_50 |212 |福州:27.36%, 深圳:25.94%, 其他:46.70% |3 |
|华北 |商品_42 |264 |保定:25.00%, 郑州:25.00%, 其他:50.00% |1 |
|华北 |商品_99 |264 |北京:24.24%, 郑州:23.48%, 其他:52.27% |2 |
|华北 |商品_19 |260 |郑州:23.46%, 保定:20.38%, 其他:56.15% |3 |
|东北 |商品_41 |169 |哈尔滨:35.50%, 大连:34.91%, 其他:29.59%|1 |
|东北 |商品_91 |165 |哈尔滨:35.76%, 大连:32.73%, 其他:31.52%|2 |
|东北 |商品_58 |159 |沈阳:37.74%, 大连:32.08%, 其他:30.19% |3 |
|华中 |商品_62 |117 |武汉:51.28%, 长沙:48.72% |1 |
|华中 |商品_4 |113 |长沙:53.10%, 武汉:46.90% |2 |
|华中 |商品_57 |111 |武汉:54.95%, 长沙:45.05% |3 |
|西南 |商品_1 |176 |贵阳:35.80%, 成都:35.80%, 其他:28.41% |1 |
|西南 |商品_44 |169 |贵阳:37.28%, 成都:34.32%, 其他:28.40% |2 |
+----+------------+-------------------+--------------------------------+---+
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
