IDEA中开发SparkSQL
实际开发中,都是使用 IDEA 进行开发的。
添加依赖
<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-sql_2.12</artifactId>
<version>3.0.0</version>
</dependency>
object TestSQLBasic {
def main(args: Array[String]): Unit = {
// 创建SparkSQL的运行环境
val sparkConf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("SparkSQL")
val spark: SparkSession = SparkSession.builder().config(sparkConf).getOrCreate()
// 涉及到转换操作，需要引入转换规则
import spark.implicits._
// DataFrame
val df: DataFrame = spark.read.json("datas/spark-sql/user.json")
df.show()
// DataFrame => SQL
df.createOrReplaceTempView("user")
spark.sql("select * from user").show
spark.sql("select age, username from user").show
spark.sql("select avg(age) from user").show
// DataFrame => DSL
df.select("age", "username").show
df.select($"age" + 1).show()
df.select('age + 1).show()
// DataSet
val seq = Seq(1, 2, 3, 4)
val ds: Dataset[Int] = seq.toDS()
ds.show()
val rdd = spark.sparkContext.makeRDD(List((1, "caocao", 123), List(2, "liubei", 77), List(3, "sunquan", 88)))
// RDD <=> DataFrame
val rddDf: DataFrame = rdd.toDF("id", "name", "num")
val rdd1: RDD[Row] = rddDf.rdd
// DataFrame <=> DataSet
val dfDs: Dataset[User] = rddDf.as[User]
val dsDf: DataFrame = dfDs.toDF()
// RDD <=> DataSet
val rddDs = rdd.map {
case (id, name, age) => User(id, name, age)
}.toDS()
rddDs.rdd
// 关闭环境
spark.close()
}
case class User(id: Int, name: String, age: Int)
}
用户自定义函数
用户可以通过 spark.udf 功能添加自定义函数,实现自定义功能。
UDF
object TestUDF {
def main(args: Array[String]): Unit = {
// 创建SparkSQL的运行环境
val sparkConf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("SparkSQL")
val spark: SparkSession = SparkSession.builder().config(sparkConf).getOrCreate()
// 涉及到转换操作，需要引入转换规则
import spark.implicits._
val df: DataFrame = spark.read.json("datas/spark-sql/user.json")
df.createOrReplaceTempView("user")
spark.udf.register("prefixName", (name: String) => {
"Name: " + name
})
spark.sql("select age, prefixName(username) from user").show
// 关闭环境
spark.close()
}
}
UDAF
强类型的 Dataset 和弱类型的 DataFrame 都提供了相关的聚合函数, 如 count(), countDistinct(), avg(), max(), min()。除此之外,用户可以设定自己的自定义聚合函数。通过继承 UserDefinedAggregateFunction 来实现用户自定义弱类型聚合函数。
从 Spark3.0 版本后,UserDefinedAggregateFunction 已经不推荐使用了。可以统一采用强类型聚合函数Aggregator
UserDefinedAggregateFunction自定义聚合方法（弱类型聚合）
object TestUDAF {
def main(args: Array[String]): Unit = {
// 创建SparkSQL的运行环境
val sparkConf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("SparkSQL")
val spark: SparkSession = SparkSession.builder().config(sparkConf).getOrCreate()
// 涉及到转换操作，需要引入转换规则
val df: DataFrame = spark.read.json("datas/spark-sql/user.json")
df.createOrReplaceTempView("user")
spark.udf.register("ageAvg", new MyAvgUDAF)
spark.sql("select ageAvg(age) from user").show
// 关闭环境
spark.close()
}
/**
* 自定义聚合函数类：计算年龄的平均值
* 1、继承UserDefinedAggregateFunction
* 2、重写方法
*/
class MyAvgUDAF extends UserDefinedAggregateFunction {
// 输入数据的结构：In
override def inputSchema: StructType = {
StructType(
Array(
StructField("age", LongType)
)
)
}
// 缓冲区数据的结构：Buffer
override def bufferSchema: StructType = {
StructType(
Array(
StructField("total", LongType),
StructField("count", LongType)
)
)
}
// 函数计算结构的数据类型：Out
override def dataType: DataType = LongType
// 函数的稳定性
override def deterministic: Boolean = true
// 缓冲区初始化
override def initialize(buffer: MutableAggregationBuffer): Unit = {
// buffer(0) = 0L
// buffer(1) = 0L
buffer.update(0, 0L)
buffer.update(1, 0L)
}
// 根据输入的值更新缓冲区数据
override def update(buffer: MutableAggregationBuffer, input: Row): Unit = {
buffer.update(0, buffer.getLong(0) + input.getLong(0))
buffer.update(1, buffer.getLong(1) + 1)
}
// 缓冲区数据合并
override def merge(buffer1: MutableAggregationBuffer, buffer2: Row): Unit = {
buffer1.update(0, buffer1.getLong(0) + buffer2.getLong(0))
buffer1.update(1, buffer1.getLong(1) + buffer2.getLong(1))
}
// 计算平均值
override def evaluate(buffer: Row): Any = {
buffer.getLong(0) / buffer.getLong(1)
}
}
}
// 数据如下：
{"username": "caocao", "age": 100}
{"username": "liubei", "age": 56}
{"username": "sunquan", "age": 45}
// 结果如下：
+--------------+
|myavgudaf(age)|
+--------------+
| 67|
+--------------+
Aggregator自定义函数（强类型）
object TestUDAFAggregator {
def main(args: Array[String]): Unit = {
// 创建SparkSQL的运行环境
val sparkConf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("SparkSQL")
val spark: SparkSession = SparkSession.builder().config(sparkConf).getOrCreate()
// 涉及到转换操作，需要引入转换规则
val df: DataFrame = spark.read.json("datas/spark-sql/user.json")
df.createOrReplaceTempView("user")
spark.udf.register("ageAvg", functions.udaf(new MyAvgUDAF))
spark.sql("select ageAvg(age) from user").show
// 关闭环境
spark.close()
}
/**
* 自定义聚合函数类：计算年龄的平均值
* 1、继承org.apache.spark.sql.expressions.Aggregator，定义泛型
* IN：输入的数据类型 Long
* BUF： 缓冲区数据类型Buff
* OUT：输出的数据类型 Long
* 2、重写方法
*/
class MyAvgUDAF extends Aggregator [Long, Buff, Long] {
// z & zero：初始值，或零值
// 缓冲区的初始化
override def zero: Buff = {
Buff(0L, 0L)
}
// 根据输入的数据更新缓冲区的数据
override def reduce(buff: Buff, in: Long): Buff = {
buff.total = buff.total + in
buff.count += 1
buff
}
// 合并缓冲区
override def merge(b1: Buff, b2: Buff): Buff = {
b1.total += b2.total
b1.count += b2.count
b1
}
// 计算结果
override def finish(buff: Buff): Long = {
buff.total / buff.count
}
// 缓冲区编码操作
// 自定义的用product
override def bufferEncoder: Encoder[Buff] = Encoders.product
// 缓输出的编码操作
// scala自带的类型的有对应的类型
override def outputEncoder: Encoder[Long] = Encoders.scalaLong
}
case class Buff(var total: Long, var count: Long)
}
DSL方式使用Aggregator进行自定义函数
object TestUDAFAggregator2 {
def main(args: Array[String]): Unit = {
// 创建SparkSQL的运行环境
val sparkConf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("SparkSQL")
val spark: SparkSession = SparkSession.builder().config(sparkConf).getOrCreate()
// 涉及到转换操作，需要引入转换规则
import spark.implicits._
val df: DataFrame = spark.read.json("datas/spark-sql/user.json")
// 早期版本中，spark不能在sql中使用强类型的UDAF操作
// 早期的UDAF强类型聚合函数使用DSL语法操作
val ds: Dataset[User] = df.as[User]
// 将UDAF函数转换为查询的列对象
val udafCol: TypedColumn[User, Long] = new MyAvgUDAF().toColumn
ds.select(udafCol).show()
// 关闭环境
spark.close()
}
case class User(username: String, age: Long)
/**
* 自定义聚合函数类：计算年龄的平均值
* 1、继承org.apache.spark.sql.expressions.Aggregator，定义泛型
* IN：输入的数据类型 User
* BUF： 缓冲区数据类型Buff
* OUT：输出的数据类型 Long
* 2、重写方法
*/
class MyAvgUDAF extends Aggregator [User, Buff, Long] {
// z & zero：初始值，或零值
// 缓冲区的初始化
override def zero: Buff = {
Buff(0L, 0L)
}
// 根据输入的数据更新缓冲区的数据
override def reduce(buff: Buff, in: User): Buff = {
buff.total = buff.total + in.age
buff.count += 1
buff
}
// 合并缓冲区
override def merge(b1: Buff, b2: Buff): Buff = {
b1.total += b2.total
b1.count += b2.count
b1
}
// 计算结果
override def finish(buff: Buff): Long = {
buff.total / buff.count
}
// 缓冲区编码操作
// 自定义的用product
override def bufferEncoder: Encoder[Buff] = Encoders.product
// 缓输出的编码操作
// scala自带的类型的有对应的类型
override def outputEncoder: Encoder[Long] = Encoders.scalaLong
}
case class Buff(var total: Long, var count: Long)
}
数据的加载和保存
通用的加载和保存方式
SparkSQL 提供了通用的保存数据和数据加载的方式。这里的通用指的是使用相同的API,根据不同的参数读取和保存不同格式的数据,SparkSQL 默认读取和保存的文件格式为 parquet
加载数据
spark.read.load 是加载数据的通用方法
scala> spark.read.
csv format table text jdbc json textFile load option options orc parquet schema
如果读取不同格式的数据,可以对不同的数据格式进行设定
scala> 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我们前面都是使用 read API 先把文件加载到 DataFrame 然后再查询,其实,我们也可以直接在文件上进行查询: 文件格式.`文件路径`
scala>spark.sql("select * from json.`/opt/module/data/user.json`").show
保存数据
df.write.save 是保存数据的通用方法
scala>df.write.
csv jdbc json orc parquet textFile... ...
如果保存不同格式的数据,可以对不同的数据格式进行设定
scala>df.write.format("...")[.option("...")].save("...")
format("…"):指定保存的数据类型,包括"csv"、“jdbc”、“json”、“orc”、“parquet"和"textFile”
save ("…"):在"csv"、“orc”、"parquet"和"textFile"格式下需要传入保存数据的路径。
option("…"):在"jdbc"格式下需要传入 JDBC 相应参数,url、user、password 和 dbtable保存操作可以使用 SaveMode, 用来指明如何处理数据,使用 mode()方法来设置。有一点很重要: 这些 SaveMode 都是没有加锁的, 也不是原子操作。
SaveMode 是一个枚举类,其中的常量包括:
df.write.mode("append").json("/opt/module/data/output")
Parquet
Spark SQL 的默认数据源为 Parquet 格式。Parquet 是一种能够有效存储嵌套数据的列式存储格式。
数据源为 Parquet 文件时,Spark SQL 可以方便的执行所有的操作,不需要使用 format。修改配置项 **spark.sql.sources.default,**可修改默认数据源格式。
1)加载数据
scala> val df = spark.read.load("examples/src/main/resources/users.parquet")
scala> df.show
2)保存数据
scala> var df = spark.read.json("/opt/module/data/input/people.json")
// 保存为 parquet 格式
scala> df.write.mode("append").save("/opt/module/data/output")
JSON
Spark SQL 能够自动推测 JSON 数据集的结构,并将它加载为一个 Dataset[Row]. 可以通过 SparkSession.read.json()去加载 JSON 文件。
**注意:Spark 读取的 JSON 文件不是传统的 JSON 文件,每一行都应该是一个 JSON 串。**格式如下:
{"name":"Michael"}
{"name":"Andy", "age":30}
[{"name":"Justin", "age":19},{"name":"Justin", "age":19}]
1)导入隐式转换
import spark.implicits._
2)加载 JSON 文件
val path = "/opt/module/spark-local/people.json"
val peopleDF = spark.read.json(path)
3)创建临时表
peopleDF.createOrReplaceTempView("people")
4)数据查询
val teenagerNamesDF = spark.sql("SELECT name FROM people WHERE age BETWEEN 13AND 19")
teenagerNamesDF.show()
+------+
| name |
+------+
|Justin|
+------+
CSV
Spark SQL 可以配置 CSV 文件的列表信息,读取 CSV 文件,CSV 文件的第一行设置为数据列
spark.read.format("csv").option("sep", ";").option("inferSchema", "true").option("header", "true").load("data/user.csv")
MySQL
Spark SQL 可以通过 JDBC 从关系型数据库中读取数据的方式创建 DataFrame,通过对DataFrame 一系列的计算后,还可以将数据再写回关系型数据库中。如果使用 spark-shell 操作,可在启动 shell 时指定相关的数据库驱动路径或者将相关的数据库驱动放到 spark 的类路径下。
bin/spark-shell --jars mysql-connector-java-5.1.27-bin.jar
1)导入依赖
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.27</version>
</dependency>
2)读取数据
object TestMysql {
def main(args: Array[String]): Unit = {
// 创建SparkSQL的运行环境
val sparkConf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("SparkSQL")
val spark: SparkSession = SparkSession.builder().config(sparkConf).getOrCreate()
// 涉及到转换操作，需要引入转换规则
import spark.implicits._
// 读取Mysql数据
val df = spark.read.format("jdbc")
.option("url", "jdbc:mysql://127.0.0.1:13306/olt")
.option("driver", "com.mysql.jdbc.Driver")
.option("user", "root")
.option("password", "123456")
.option("dbtable", "t_monitor_alarm_defined")
.load()
df.show
// 保存数据
df.write.format("jdbc")
.option("url", "jdbc:mysql://127.0.0.1:13306/olt")
.option("driver", "com.mysql.jdbc.Driver")
.option("user", "root")
.option("password", "123456")
.option("dbtable", "t_monitor_alarm_defined2")
.save()
// 关闭环境
spark.close()
}
}
表数据
运行结果如下：
+---+--------------------+--------------------+----+---------------+
| id| code| content|rank| type|
+---+--------------------+--------------------+----+---------------+
| 1|SYSTEM_MONITOR_SI...|CPU usage exceeds...| 1| CPU|
| 2|SYSTEM_MONITOR_CP...|The total CPU usa...| 4| CPU|
| 3|SYSTEM_MONITOR_ME...|Memory usage exce...| 4| MEMORY|
| 4|SYSTEM_MONITOR_DI...|Root partition di...| 3| DISK|
| 5|SYSTEM_MONITOR_DI...|Total disk usage ...| 3| DISK|
| 6|SYSTEM_MONITOR_NE...|Average input flo...| 3|NETWORK_TRAFFIC|
| 7|SYSTEM_MONITOR_NE...|Average output fl...| 3|NETWORK_TRAFFIC|
| 8|SYSTEM_EXCEPTION_...|System program ex...| 1| EXCEPTION|
+---+--------------------+--------------------+----+---------------+
Hive
Apache Hive 是 Hadoop 上的 SQL 引擎,Spark SQL 编译时可以包含 Hive 支持,也可以不包含。包含 Hive 支持的 Spark SQL 可以支持 Hive 表访问、 UDF (用户自定义函数)以及 Hive 查询语言(HiveQL/HQL)等。需要强调的一点是,如果要在 Spark SQL 中包含Hive 的库,并不需要事先安装 Hive。一般来说,最好还是在编译 Spark SQL 时引入 Hive支持,这样就可以使用这些特性了。如果你下载的是二进制版本的 Spark,它应该已经在编译时添加了 Hive 支持。
若要把 Spark SQL 连接到一个部署好的 Hive 上,你必须把 hive-site.xml 复制到Spark 的配置文件目录中($SPARK_HOME/conf)。即使没有部署好 Hive,Spark SQL 也可以运行。 需要注意的是,如果你没有部署好 Hive,Spark SQL 会在当前的工作目录中创建出自己的 Hive 元数据仓库,叫作 metastore_db。此外,如果你尝试使用 HiveQL 中的CREATE TABLE (并非 CREATE EXTERNAL TABLE)语句来创建表,这些表会被放在你默认的文件系统中的 /user/hive/warehouse 目录中(如果你的 classpath 中有配好的hdfs-site.xml,默认的文件系统就是 HDFS,否则就是本地文件系统)。
spark-shell 默认是 Hive 支持的;代码中是默认不支持的,需要手动指定(加一个参数即可)。
内嵌的 HIVE
如果使用 Spark 内嵌的 Hive, 则什么都不用做, 直接使用即可.
Hive 的元数据存储在 derby 中, 默认仓库地址:$SPARK_HOME/spark-warehouse
scala> spark.sql("show tables").show
+--------+---------+-----------+
| database | tableName | isTemporary |
| -------- | --------- | ----------- |
| | | |
+--------+---------+-----------+
+--------+---------+-----------+
scala> spark.sql("create table aa(id int)")
scala> spark.sql("show tables").show
+--------+---------+-----------+
| database | tableName | isTemporary |
| -------- | --------- | ----------- |
| | | |
+--------+---------+-----------+
| default |aa |false |
+--------+---------+-----------+
向表加载本地数据
scala> spark.sql("load data local inpath 'input/ids.txt' into table aa")
scala> spark.sql("select * from aa").show
+---+
| id|
+---+
| 1|
| 2|
| 3|
| 4|
+---+
在实际使用中, 几乎没有任何人会使用内置的 Hive
外部的 HIVE
如果想连接外部已经部署好的 Hive,需要通过以下几个步骤:
1、Spark 要接管 Hive 需要把 hive-site.xml 拷贝到 conf/目录下
2、把 Mysql 的驱动 copy 到 jars/目录下
3、如果访问不到 hdfs,则需要把 core-site.xml 和 hdfs-site.xml 拷贝到 conf/目录下
4、重启 spark-shell
scala> spark.sql("show tables").show
20/04/25 22:05:14 WARN ObjectStore: Failed to get database global_temp, returning
NoSuchObjectException
+--------+--------------------+-----------+
|database| tableName|isTemporary|
+--------+--------------------+-----------+
| default|emp |false|
| default|hive_hbase_emp_table|false|
| default|relevance_hbase_emp |false|
| default|staff_hive |false|
| default|ttt |false|
| default|user_visit_action |false|
+--------+--------------------+-----------+
运行 Spark SQL CLI
Spark SQL CLI 可以很方便的在本地运行 Hive 元数据服务以及从命令行执行查询任务。在Spark 目录下执行如下命令启动 Spark SQL CLI,直接执行 SQL 语句,类似一 Hive 窗口
bin/spark-sql
运行 Spark beeline
Spark Thrift Server 是 Spark 社区基于 HiveServer2 实现的一个 Thrift 服务。旨在无缝兼容HiveServer2。因为 Spark Thrift Server 的接口和协议都和 HiveServer2 完全一致,因此我们部署好 Spark Thrift Server 后,可以直接使用 hive 的 beeline 访问 Spark Thrift Server 执行相关语句。Spark Thrift Server 的目的也只是取代 HiveServer2,因此它依旧可以和 Hive Metastore进行交互,获取到 hive 的元数据。
如果想连接 Thrift Server,需要通过以下几个步骤:
1、Spark 要接管 Hive 需要把 hive-site.xml 拷贝到 conf/目录下
2、把 Mysql 的驱动 copy 到 jars/目录下
3、如果访问不到 hdfs,则需要把 core-site.xml 和 hdfs-site.xml 拷贝到 conf/目录下
4、启动 Thrift Server
sbin/start-thriftserver.sh
5、使用 beeline 连接 Thrift Server
bin/beeline -u jdbc:hive2://linux1:10000 -n root
代码操作 Hive
1)导入依赖
<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-hive_2.12</artifactId>
<version>3.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>1.2.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.27</version>
</dependency>
2)将 hive-site.xml 文件拷贝到项目的 resources 目录中,代码实现
//创建 SparkSession
val spark: SparkSession = SparkSession
.builder()
.enableHiveSupport()
.master("local[*]")
.appName("sql")
.getOrCreate()
注意:在开发工具中创建数据库默认是在本地仓库,通过参数修改数据库仓库的地址:
config("spark.sql.warehouse.dir", "hdfs://linux1:8020/user/hive/warehouse")
如果在执行操作时,出现如下错误:
可以代码最前面增加如下代码解决:
System.setProperty("HADOOP_USER_NAME", "root")
此处的 root 改为自己的 hadoop 用户名称
扫一扫
2595
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
