【Spark】SparkSQL编程实践

1 知识准备

1.1 Shark

Spark SQL是Spark生态系统中非常重要的组件，其前身为Shark。

Shark的设计导致了两个问题：

• 一是执行计划优化完全依赖于Hive，不方便添加新的优化策略；
• 二是因为Spark是线程级并行，而MapReduce是进程级并行。

因此，Spark在兼容Hive的实现上存在线程安全问题，导致Shark不得不使用另外一套独立维护的打了补丁的Hive源码分支。Shark的实现继承了大量的Hive代码，因而给优化和维护带来了大量的麻烦，特别是基于MapReduce设计的部分，成为整个项目的瓶颈。因此，在2014年的时候，Shark项目中止，并转向Spark SQL的开发。

1.2 Spark SQL设计

1、Spark-SQL架构

2、Spark-SQL支持的数据格式和编程语言

1.3 DataFrame与RDD的区别

2 DataFrame的创建

1、从Spark2.0以上版本开始，Spark使用全新的SparkSession接口替代Spark1.6中的SQLContext及HiveContext接口来实现其对数据加载、转换、处理等功能。SparkSession实现了SQLContext及HiveContext所有功能。

2、SparkSession支持从不同的数据源加载数据，并把数据转换成DataFrame，并且支持把DataFrame转换成SQLContext自身中的表，然后使用SQL语句来操作数据。SparkSession亦提供了HiveQL以及其他依赖于Hive的功能的支持。

2.1 从JSON文件中读取数据生成DataFrame

1、文件

{"name":"Michael"}
{"name":"Andy", "age":30}
{"name":"Justin", "age":19}

2、操作

scala> import org.apache.spark.sql.SparkSession

scala> val spark=SparkSession.builder().getOrCreate()

//使支持RDDs转换为DataFrames及后续sql操作
scala> import spark.implicits._

scala> val df = spark.read.json("file:///usr/local/spark/examples/src/main/resources/people.json")

scala> df.show()
+----+-------+
| age|   name|
+----+-------+
|null|Michael|
|  30|   Andy|
|  19| Justin|
+----+-------+

2、常用DataFrame操作

// 打印模式信息
scala> df.printSchema()
root
 |-- age: long (nullable = true)
 |-- name: string (nullable = true)
 
// 选择多列
scala> df.select(df("name"),df("age")+1).show()
+-------+---------+
|   name|(age + 1)|
+-------+---------+
|Michael|     null|
|   Andy|       31|
| Justin|       20|
+-------+---------+
 
// 条件过滤
scala> df.filter(df("age") > 20 ).show()
+---+----+
|age|name|
+---+----+
| 30|Andy|
+---+----+
 
// 分组聚合
scala> df.groupBy("age").count().show()
+----+-----+
| age|count|
+----+-----+
|  19|    1|
|null|    1|
|  30|    1|
+----+-----+
 
// 排序
scala> df.sort(df("age").desc).show()
+----+-------+
| age|   name|
+----+-------+
|  30|   Andy|
|  19| Justin|
|null|Michael|
+----+-------+
 
//多列排序
scala> df.sort(df("age").desc, df("name").asc).show()
+----+-------+
| age|   name|
+----+-------+
|  30|   Andy|
|  19|   Justin|
| null| Michael|
+----+-------+
 
//对列进行重命名
scala> df.select(df("name").as("username"),df("age")).show()
+--------+----+
|username| age|
+--------+----+
| Michael|null|
|  Andy|  30|
|  Justin|  19|
+--------+----+

2.1 从RDD转换创建DataFrame（txt文件）

方法：

• 第一种方法是利用反射来推断包含特定类型对象的RDD的schema，适用对已知数据结构的RDD转换；
• 第二种方法是使用编程接口，构造一个schema并将其应用在已知的RDD上。

2.1.1 反射机制

在利用反射机制推断RDD模式时，需要首先定义一个case class，因为只有case class才能被Spark隐式地转换为DataFrame。

1、导包

import org.apache.spark.sql.catalyst.encoders.ExpressionEncoder
import org.apache.spark.sql.Encoder
import org.apache.spark.sql.Encoder
import spark.implicits._  //导入包支持把一个RDD隐式转换为一个DataFrame

2、创建Person类

case class Person(name: String, age: Long)  //定义一个case class

val peopleDF = spark.sparkContext.textFile("file:///usr/local/spark/examples/src/main/resources/people.txt").
｜map(_.split(",")).
｜map(attributes => Person(attributes(0), attributes(1).trim.toInt)).
｜toDF()
 
//必须注册为临时表才能供下面的sql查询使用 
peopleDF.createOrReplaceTempView("people")  

//sql语句查询，最终生成一个DataFrame 
val personsDF = spark.sql("select name,age from people where age > 20")

//DataFrame中的每个元素都是一行记录，包含name和age两个字段，分别用t(0)和t(1)来获取值
personsDF.map(t => "Name:"+t(0)+","+"Age:"+t(1)).show()  
+------------------+
|             value|
+------------------+
|Name:Michael,Age:29|
|   Name:Andy,Age:30|
+------------------+

2.1.2 使用编程方式定义RDD模式

当无法提前定义case class时，就需要采用编程方式定义RDD模式

import org.apache.spark.sql.types._
import org.apache.spark.sql.Row

//生成 RDD
val peopleRDD = spark.sparkContext.textFile("file:///usr/local/spark/examples/src/main/resources/people.txt")

//制作“表头”
val schemaString = "name age"

val fields = schemaString.split(" ").map(fieldName => StructField(fieldName, StringType, nullable = true))

val schema = StructType(fields)

//制作“记录” 
val rowRDD = peopleRDD.map(_.split(",")).map(attributes => Row(attributes(0), attributes(1).trim))

//拼装记录和表头 
val peopleDF = spark.createDataFrame(rowRDD, schema)

//必须注册为临时表才能供下面查询使用
peopleDF.createOrReplaceTempView("people")

val results = spark.sql("SELECT name,age FROM people")
  
results.map(attributes => "name: " + attributes(0)+","+"age:"+attributes(1)).show()
+--------------------+
|               value|
+--------------------+
|name: Michael,age:29|
|   name: Andy,age:30|
| name: Justin,age:19|
+--------------------+

3 DataFrame数据保存

3.1 第一种保存方法

val peopleDF = spark.read.format("json").load("file:///usr/local/spark/examples/src/main/resources/people.json")

//select(“name”, “age”)确定要把哪些列进行保存，调用write.format(“csv”).save ()保存成csv文件 
peopleDF.select("name", "age").write.format("csv").save("file:///usr/local/spark/mycode/newpeople.csv")

另外，write.format()支持输出 json,parquet, jdbc, orc, libsvm, csv, text等格式文件，如果要输出文本文件，可以采用write.format(“text”)，但是，需要注意，只有select()中只存在一个列时，才允许保存成文本文件，如果存在两个列，比如select(“name”, “age”)，就不能保存成文本文件。

3.2 第二种保存方法

val peopleDF = spark.read.format("json").load("file:///usr/local/spark/examples/src/main/resources/people.json")

peopleDF.rdd.saveAsTextFile("file:///usr/local/spark/mycode/newpeople.txt")

把DataFrame转换成RDD，然后调用saveAsTextFile()保存成文本文件

4 Parquet文件与JDBC数据库读写

4.1 Parquet文件读写操作

scala> import spark.implicits._

scala> val parquetFileDF = spark.read.parquet("file:///usr/local/spark/examples/src/main/resources/users.parquet")
 
scala> parquetFileDF.createOrReplaceTempView("parquetFile")

scala> val namesDF = spark.sql("SELECT * FROM parquetFile")
 
scala> namesDF.foreach(attributes =>println("Name: " + attributes(0)+"  favorite color:"+attributes(1)))

import spark.implicits._

val peopleDF = spark.read.json("file:///usr/local/spark/examples/src/main/resources/people.json")
 
peopleDF.write.parquet("file:///usr/local/spark/mycode/newpeople.parquet")

4.2 JDBC数据库读写操作

4.2.1 准备

1、首先，请进入Linux系统（本教程统一使用hadoop用户名登录），打开火狐（FireFox）浏览器，下载一个MySQL的JDBC驱动（下载）。在火狐浏览器中下载时，一般默认保存在hadoop用户的当前工作目录的“Downloads”目录下，所以，可以打开一个终端界面，输入下面命令查看：

cd ~/Downloads

2、就可以看到刚才下载到的MySQL的JDBC驱动程序，文件名称为mysql-connector-java-5.1.49.tar.gz（你下载的版本可能和这个不同）。现在，使用下面命令，把该驱动程序拷贝到spark的安装目录下：

sudo tar -zxf ~/Downloads/mysql-connector-java-5.1.49.tar.gz -C /usr/local/spark/jars
cd /usr/local/spark/jars
ls

3、这时就可以在/usr/local/spark/jars目录下看到这个驱动程序文件所在的文件夹mysql-connector-java-5.1.40，进入这个文件夹，就可以看到驱动程序文件mysql-connector-java-5.1.40-bin.jar。请输入下面命令启动已经安装在Linux系统中的mysql数据库（如果前面已经启动了MySQL数据库，这里就不用重复启动了）

service mysql start

4、下面，我们要启动一个spark-shell，而且启动的时候，要附加一些参数。启动Spark Shell时，必须指定mysql连接驱动jar包

cd /usr/local/spark
./bin/spark-shell \
--jars /usr/local/spark/jars/mysql-connector-java-5.1.49/mysql-connector-java-5.1.49-bin.jar \
--driver-class-path /usr/local/spark/jars/mysql-connector-java-5.1.49/mysql-connector-java-5.1.49-bin.jar

上面的命令行中，在一行的末尾加入斜杠\，是为了告诉spark-shell，命令还没有结束。启动进入spark-shell以后，可以执行以下命令连接数据库，读取数据，并显示：

scala> val jdbcDF = spark.read.format("jdbc").option("url", "jdbc:mysql://mnode1:3306/spark").option("driver","com.mysql.jdbc.Driver").option("dbtable", "student").option("user", "root").option("password", "Hive@2020").load()

scala> jdbcDF.show()
| id|    name|gender|age|
+---+--------+------+---+
|  1| Zhangsan|     F| 23|
|  2| Lisi|     M| 24|
+---+--------+------+---+

4.2.2 写入数据

import java.util.Properties
import org.apache.spark.sql.types._
import org.apache.spark.sql.Row

//下面我们设置两条数据表示两个学生信息
val studentRDD = spark.sparkContext.parallelize(Array("3 Wangwu M 26","4 Zhaoliu M 27")).map(_.split(" "))

//下面要设置模式信息
val schema = StructType(List(StructField("id", IntegerType, true),StructField("name", StringType, true),StructField("gender", StringType, true),StructField("age", IntegerType, true)))

//下面创建Row对象，每个Row对象都是rowRDD中的一行
val rowRDD = studentRDD.map(p => Row(p(0).toInt, p(1).trim, p(2).trim, p(3).toInt))
 
//建立起Row对象和模式之间的对应关系，也就是把数据和模式对应起来
val studentDF = spark.createDataFrame(rowRDD, schema)

//下面创建一个prop变量用来保存JDBC连接参数
val prop = new Properties()
prop.put("user", "root") //表示用户名是root
prop.put("password", "Hive@2020") //表示密码是hadoop
prop.put("driver","com.mysql.jdbc.Driver") //表示驱动程序是com.mysql.jdbc.Driver

//下面就可以连接数据库，采用append模式，表示追加记录到数据库spark的student表中
studentDF.write.mode("append").jdbc("jdbc:mysql://mnode1:3306/spark", "spark.student", prop)

5 Hive数据仓库读写操作

5.1 让Spark包含Hive支持

scala> import org.apache.spark.sql.hive.HiveContext

如果返回错误信息，也就是spark无法识别org.apache.spark.sql.hive.HiveContext，这就说明你当前电脑上的Spark版本不包含Hive支持。

为了让Spark能够访问Hive，需要把Hive的配置文件hive-site.xml拷贝到Spark的conf目录下，请在Shell命令提示符状态下操作：

cd /usr/local/spark/conf
cp /usr/local/hive/conf/hive-site.xml .
ls

5.2 连接Hive读写数据

1、在进行编程之前，我们需要做一些准备工作，我们需要修改“/usr/local/spark/conf/spark-env.sh”这个配置文件：

cd /usr/local/spark/conf/
vim spark-env.sh

这样就使用vim编辑器打开了spark-env.sh这个文件，这文件里面以前可能包含一些配置信息，全部删除，然后输入下面内容：

export JAVA_HOME=/usr/lib/jvm/jdk1.8.0_271
export CLASSPATH=$CLASSPATH:/usr/local/hive/lib
export SCALA_HOME=/usr/local/scala
export HIVE_CONF_DIR=/usr/local/hive/conf

2、保存spark-env.sh这个文件，退出vim编辑器。之后我们配置spark-defaults.conf文件，具体命令如下：

cd /usr/local/spark/conf/
cp spark-defaults.conf.template spark-defaults.conf
vim spark-defaults.conf

这样就使用vim编辑器打开了spark-defaults.conf这个文件，然后输入下面内容：

spark.driver.extraClassPath=/usr/local/spark/jars/mysql-connector-java-5.1.49/*

5.2.1 从Hive中读取数据

import org.apache.spark.sql.Row
import org.apache.spark.sql.SparkSession
 
case class Record(key: Int, value: String)
 
// warehouseLocation points to the default location for managed databases and tables
val warehouseLocation = "spark-warehouse"
 
val spark = SparkSession.builder().appName("Spark Hive Example").config("spark.sql.warehouse.dir", warehouseLocation).enableHiveSupport().getOrCreate()
 
import spark.implicits._
import spark.sql
//下面是运行结果
scala> sql("SELECT * FROM sparktest.student").show()
+---+--------+------+---+
| id|    name|gender|age|
+---+--------+------+---+
|  1| Zhangsan|     F| 23|
|  2| Lisi |     M| 24|
+---+--------+------+---+

5.2.2 向Hive中写入数据

import java.util.Properties
import org.apache.spark.sql.types._
import org.apache.spark.sql.Row

//下面我们设置两条数据表示两个学生信息
val studentRDD = spark.sparkContext.parallelize(Array("3 Wangwu M 26","4 Zhaoliu M 27")).map(_.split(" "))
 
//下面要设置模式信息
val schema = StructType(List(StructField("id", IntegerType, true),StructField("name", StringType, true),StructField("gender", StringType, true),StructField("age", IntegerType, true)))
 
//下面创建Row对象，每个Row对象都是rowRDD中的一行
val rowRDD = studentRDD.map(p => Row(p(0).toInt, p(1).trim, p(2).trim, p(3).toInt))

//建立起Row对象和模式之间的对应关系，也就是把数据和模式对应起来
val studentDF = spark.createDataFrame(rowRDD, schema)
 
//查看studentDF
studentDF.show()
+---+---------+------+---+
| id|     name|gender|age|
+---+---------+------+---+
|  3| Wangwu|     M| 26|
|  4|  Zhaoliu|     M| 27|
+---+---------+------+---+
//下面注册临时表
studentDF.registerTempTable("tempTable") 
 
sql("insert into sparktest.student select * from tempTable")