【SparkSQL】数据的加载和保存、项目实战

一 数据的加载和保存

1 通用的加载和保存方式

SparkSQL提供了通用的保存数据和数据加载的方式。这里的通用指的是使用相同的API，根据不同的参数读取和保存不同格式的数据，SparkSQL默认读取和保存的文件格式为parquet

（1）加载数据

spark.read.load 是加载数据的通用方法

scala> spark.read.tab键
csv   format   jdbc   json   load   option   options   orc   parquet   schema   table   text   textFile

如果读取不同格式的数据，可以对不同的数据格式进行设定

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

scala> spark.read.format("json").load("data/user.json")
res4: org.apache.spark.sql.DataFrame = [age: bigint, username: string]

scala> res4.show
+---+--------+
|age|username|
+---+--------+
| 20|zhangsan|
| 30|    lisi|
| 10|  wangwu|
+---+--------+

前面都是使用read API 先把文件加载到 DataFrame然后再查询，其实，也可以直接在文件上进行查询: 文件格式.文件路径

scala>spark.sql("select * from json.`data/user.json`").show

（2） 保存数据

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

# 创建df
scala> var df = spark.read.json("data/user.json")
df: org.apache.spark.sql.DataFrame = [age: bigint, username: string]
# 保存，默认格式为parquet
scala> df.write.save("output")
# 保存为json格式，格式一
scala> df.write.json("output1")
# 保存为json格式，格式二
scala> df.write.format("json").save("output2")

保存操作可以使用 SaveMode, 用来指明如何处理数据，使用mode()方法来设置。

有一点很重要: 这些 SaveMode 都是没有加锁的, 也不是原子操作。

SaveMode是一个枚举类，其中的常量包括：

Scala/Java	Any Language	Meaning
SaveMode.ErrorIfExists(default)	“error”(default)	如果文件已经存在则抛出异常
SaveMode.Append	“append”	如果文件已经存在则追加
SaveMode.Overwrite	“overwrite”	如果文件已经存在则覆盖
SaveMode.Ignore	“ignore”	如果文件已经存在则忽略

scala> df.write.mode("overwrite").format("json").save("output2")
scala> df.write.mode("ignore").format("json").save("output2")
scala> df.write.mode("append").format("json").save("output2")

2 Parquet

Spark SQL的默认数据源为Parquet格式。Parquet是一种能够有效存储嵌套数据的列式存储格式。

数据源为Parquet文件时，Spark SQL可以方便的执行所有的操作，不需要使用format。修改配置项spark.sql.sources.default，可修改默认数据源格式。

# 加载数据
val df = spark.read.load("output/part-00000-da461a9c-ea5b-489f-bccc-2b97d7bc2910-c000.snappy.parquet").show
# 保存数据
scala> val df = spark.read.json("data/user.json")
df: org.apache.spark.sql.DataFrame = [age: bigint, username: string]
# parquet格式保存
scala> df.write.mode("append").save("output")
# json格式保存
scala> df.write.mode("append").format("json").save("output")

3 JSON

Spark SQL 能够自动推测JSON数据集的结构，并将它加载为一个Dataset[Row]. 可以通过SparkSession.read.json()去加载JSON 文件。

注意：Spark读取的JSON文件不是传统的JSON文件，每一行都应该是一个JSON串。格式如下：

{"age":20,"username":"zhangsan"}
{"age":30,"username":"lisi"}
{"age":10,"username":"wangwu"}

# 导入隐式转换
import spark.implicits._
# 加载JSON文件
val path = "data/user.json"
val userDF = spark.read.json(path)
# 创建临时表
userDF.createOrReplaceTempView("user")
# 数据查询
val userNamesDF = spark.sql("SELECT username FROM user WHERE age BETWEEN 9 AND 19")
userNamesDF.show()

+--------+
|username|
+--------+
|  wangwu|
+--------+

4 CSV

Spark SQL可以配置CSV文件的列表信息，读取CSV文件,CSV文件的第一行设置为数据列

spark.read.format("csv").option("sep", ";").option("inferSchema", "true").option("header", "true").load("data/user.csv")

scala> res27.show
+------------+
|username       age|
+------------+
| zhangsan      20|
|     lisi      40|
|   wangwu      50|
+------------+

5 MySQL

Spark SQL可以通过JDBC从关系型数据库中读取数据的方式创建DataFrame，通过对DataFrame一系列的计算后，还可以将数据再写回关系型数据库中。如果使用spark-shell操作，可在启动shell时指定相关的数据库驱动路径或者将相关的数据库驱动放到spark的类路径下。

bin/spark-shell --jars mysql-connector-java-5.1.27-bin.jar

这里只演示在Idea中通过JDBC对Mysql进行操作

（1）导入依赖

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.27</version>
</dependency>

（2）读取数据

//创建配置文件对象
val conf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("SparkSQL")

//创建SparkSession对象
val spark: SparkSession = SparkSession.builder().config(conf).getOrCreate()

import spark.implicits._

//从mysql数据库中读取数据
//方式1：通用的load方法读取
spark.read.format("jdbc")
  .option("url", "jdbc:mysql://hadoop101:3306/test")
  .option("driver", "com.mysql.jdbc.Driver")
  .option("user", "root")
  .option