Spark SQL

1 Spark SQL 概述

1.1 什么是 Spark SQL

• 结构化数据 不是NoSQL
• Spark SQL 是 Spark 用来处理结构化数据的一个模块，它提供了一个编程抽象叫做 DataFrame并且作为分布式 SQL 查询引擎的作用。
• Hive，它是将 Hive SQL 转换成 MapReduce 然后提交到集群上执行，大大简化了编写 MapReduce 的程序的复杂性，由于 MapReduce 这种计算模型执行效率比较慢。所有 SparkSQL 的应运而生，它是将 Spark SQL 转换成 RDD，然后提交到集群执行，执行效率非常快！
• Spark SQL的官网
• 

1. 易整合
2. 统一的数据访问方式
3. 兼容 Hive
   内部内置HIVE 可以与关系型数据库无缝对接
4. 标准的数据连接
   

• SparkSQL 可以看做是一个转换层，向下对接各种不同的结构化数据源，向上提供不同的数据访问方式。
• Spark SQL由 Spark Core衍生而来
• Tableau：可视化根据数据库数据结构生成图表报表

1.2 RDD vs DataFrames vs DataSet

• 在 SparkSQL 中 Spark 为我们提供了两个新的抽象，分别是 DataFrame 和 DataSet。 他们和RDD 有什么区别呢？首先从版本的产生上来看：
• RDD (Spark1.0) —> Dataframe(Spark1.3) —> Dataset(Spark1.6)
• 如果同样的数据都给到这三个数据结构，他们分别计算之后，都会给出相同的结果。不同是的他们的执行效率和执行方式。
• 在后期的 Spark 版本中， DataSet 会逐步取代 RDD 和 DataFrame 成为唯一的 API 接口。

1.2.1 RDD

• RDD 是一个懒执行的不可变的可以支持 Lambda 表达式的并行数据集合。
• RDD 的最大好处就是简单， API 的人性化程度很高。
• RDD 的劣势是性能限制，它是一个 JVM 驻内存对象， 这也就决定了存在 GC 的限制和数据增加时 Java 序列化成本的升高。
• RDD：弹性分布式数据集， Spark计算的基石，为用户屏蔽了底层对数据的复杂抽象和处理，为用户提供了一组方便的数据转换与求值方法。

1.2.2 Dataframe

• 提出了数据管理框架，比RDD执行性能高
• RDD + Schema => 一张表
• DataFrame也是懒执行的、不可变的。
• 性能上比RDD要高
• 与 RDD 类似， DataFrame 也是一个分布式数据容器。然而 DataFrame 更像传统数据库的二维表格，除了数据以外，还记录数据的结构信息，即 schema。同时，与 Hive 类似， DataFrame 也支持嵌套数据类型（struct、 array 和 map）。从 API 易用性的角度上看， DataFrame API 提供的是一套高层的关系操作，比函数式的 RDD API 要更加友好，门槛更低。由于与 R 和 Pandas 的DataFrame 类似， Spark DataFrame 很好地继承了传统单机数据分析的开发体验。
• 
• 上图直观地体现了 DataFrame 和 RDD 的区别。左侧的 RDD[Person]虽然以 Person 为类型参数，但 Spark 框架本身不了解 Person 类的内部结构。而右侧的 DataFrame 却提供了详细的结构信息，使得 Spark SQL 可以清楚地知道该数据集中包含哪些列，每列的名称和类型各是什么。DataFrame 多了数据的结构信息，即 schema。 RDD 是分布式的 Java 对象的集合。 DataFrame 是分布式的 Row 对象的集合。 DataFrame 除了提供了比 RDD 更丰富的算子以外，更重要的特点是提升执行效率、减少数据读取以及执行计划的优化，比如 filter 下推、裁剪等。
• DataFrame 是为数据提供了 Schema 的视图。可以把它当做数据库中的一张表来对待
• DataFrame 也是懒执行的。
• 性能上比 RDD 要高，主要有两方面原因：
  1. 定制化内存管理
  2. 数据以二进制的方式存在于非堆内存， 节省了大量空间之外， 还摆脱了 GC 的限制。
     定制化内存管理：
     
  • 优化的执行计划
  • 查询计划通过 Spark catalyst optimiser 进行优化。
    
    
• 为了说明查询优化，我们来看上图展示的人口数据分析的示例。图中构造了两个 DataFrame，将它们 join 之后又做了一次 filter 操作。如果原封不动地执行这个执行计划，最终的执行效率是不高的。因为 join 是一个代价较大的操作， 也可能会产生一个较大的数据集。如果我们能将 filter下推到 join 下方，先对 DataFrame 进行过滤，再 join 过滤后的较小的结果集，便可以有效缩短执行时间。而 Spark SQL 的查询优化器正是这样做的。简而言之，逻辑查询计划优化就是一个利用基于关系代数的等价变换，将高成本的操作替换为低成本操作的过程
• 得到的优化执行计划在转换成物 理执行计划的过程中，还可以根据具体的数据源的特性将过滤条件下推至数据源内。最右侧的物理执行计划中 Filter 之所以消失不见，就是因为溶入了用于执行最终的读取操作的表扫描节点内。
• 对于普通开发者而言，查询优化器的意义在于，即便是经验并不丰富的程序员写出的次优的查询，也可以被尽量转换为高效的形式予以执行
• Dataframe 的劣势在于在编译期缺少类型安全检查，导致运行时出错.
  • 只对数据有了结构化的规范，但是没有对数据类型严格校验。写不会报错，运行可能报错
  • Dataframe的劣势在于在编译期缺少类型安全检查，运行期检查
  • DataFrame -> List< Map >
    DataSet -> List< Bean >

  1.2.3 Dataset

• Dataframe API的一个扩展
• Spark最新的数据抽象
• 具有类型安全检查
• 具有Dataframe的查询优化特性
• Dataset支持编解码器样例类的使用
• DataFrame=Dataset[Row]
• DataSet是强类型的
• DataFrame只是知道字段，但是不知道字段的类型，所以在执行这些操作的时候是没办法在编译的时候检查是否类型失败的，比如你可以对一个String进行减法操作，在执行的时候才报错，而DataSet不仅仅知道字段，而且知道字段类型，所以有更严格的错误检查。

1. 是 Dataframe API 的一个扩展，是 Spark 最新的数据抽象
2. 用户友好的 API 风格，既具有类型安全检查也具有 Dataframe 的查询优化特性。
3. Dataset 支持编解码器， 当需要访问非堆上的数据时可以避免反序列化整个对象，提高了效率
4. 样例类被用来在 Dataset 中定义数据的结构信息，样例类中每个属性的名称直接映射到DataSet 中的字段名称。
5. Dataframe 是 Dataset 的特列，DataFrame=Dataset[Row] ，所以可以通过 as 方法将 Dataframe转换为 Dataset。 Row 是一个类型，跟 Car、 Person 这些的类型一样，所有的表结构信息我都用Row 来表示。
6. DataSet 是强类型的。比如可以有 Dataset[Car]， Dataset[Person].
7. DataFrame 只是知道字段，但是不知道字段的类型，所以在执行这些操作的时候是没办法在编译的时候检查是否类型失败的，比如你可以对一个 String 进行减法操作，在执行的时候才报错，而 DataSet 不仅仅知道字段，而且知道字段类型，所以有更严格的错误检查。 就跟 JSON 对象和类对象之间的类比。
8. RDD 让我们能够决定怎么做，而 DataFrame 和 DataSet 让我们决定做什么，控制的粒度不一样。

2 执行 SparkSQL 查询

• SparkSession=>SparkContext+HiveContext SparkSession是驱动
• SparkSQL处理结构化数据

RDD	Spark1.0出现的
DataFrame	Spark1.3出现的
DataSet	Spark1.6出现的

2.1 命令行查询流程

• 原始数据

/home/bduser/spark/examples/src/main/resources



[bduser@node102 resources]$ cat ~/spark/examples/src/main/resources/people.json
{"name":"Michael"}
{"name":"Andy", "age":30}
{"name":"Justin", "age":19}

• SQL风格

scala> df.createTempView("person")
scala> spark.sql("select * from person").show
+----+-------+
| age|   name|
+----+-------+
|null|Michael|
|  30|   Andy|
|  19| Justin|
+----+-------+

2.2 IDEA 创建 SparkSQL 程序

【session：会议】

• IDEA 中程序的打包和运行方式都和 SparkCore 类似， Maven 依赖中需要添加新的依赖项：

<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-sql_2.11</artifactId>
<version>2.1.3</version>
<scope>provided</scope>
</dependency>

• 程序如下：

 val conf = new SparkConf().setAppName("hello SparkSQL").setMaster("local[*]")

    val sparkSession = SparkSession.builder().config(conf).getOrCreate()

    val sparkContext = sparkSession.sparkContext


    val peopleDF = sparkSession.read
      .json("D:\\AboutMyWork\\IDEA-WorkSpace\\SparkDemos\\SparkSQLDemos\\src\\main\\resources\\datas\\people.json")

    //DF要选构建为临时表
    peopleDF.createTempView("aaa")

    sparkSession.sql("select * from aaa").show()

    sparkSession.close()
    sparkContext.stop()

| age|   name|
+----+-------+
|null|Michael|
|  30|   Andy|
|  19| Justin|
+----+-------+

• SparkSQL开发环境问题：以SparkSession对象驱动SQL执行

3 Spark SQL 解析

3.1 新的起始点 SparkSession

• 在老的版本中， SparkSQL 提供两种 SQL 查询起始点，一个叫 SQLContext，用于 Spark 自己提供的 SQL 查询，一个叫 HiveContext，用于连接 Hive 的查询， SparkSession 是 Spark 最新的 SQL查询起始点，实质上是 SQLContext 和 HiveContext 的组合，所以在 SQLContext 和 HiveContext上可用的 API 在 SparkSession 上同样是可以使用的。 SparkSession 内部封装了 sparkContext，所以计算实际上是由 sparkContext 完成的
• SparkSession.builder 用于创建一个 SparkSession。
• import spark.implicits._的引入是用于将 DataFrames 隐式转换成 RDD，使 df 能够使用 RDD 中的方法。
• 如果需要 Hive 支持，则需要以下创建语句：

import org.apache.spark.sql.SparkSession
val spark = SparkSession
.builder()
.appName("Spark SQL basic example")
.config("spark.some.config.option", "some-value")
.enableHiveSupport()
.getOrCreate()
// For implicit conversions like converting RDDs to DataFrames
import spark.implicits._

3.2 创建 DataFrames

• 在 Spark SQL 中 SparkSession 是创建 DataFrames 和执行 SQL 的入口，创建 DataFrames 有三种方式，一种是可以从一个存在的 RDD 进行转换，还可以从 Hive Table 进行查询返回，或者通过 Spark 的数据源进行创建
• 从 Spark 数据源进行创建：

val df = spark.read.json("examples/src/main/resources/people.json")
// Displays the content of the DataFrame to stdout
df.show()
// +----+-------+
// | age| name|
// +----+-------+
// |null|Michael|
// | 30| Andy|
// | 19| Justin|
// +----+-------+

• 从 RDD 进行转换：

/**
Michael, 29
Andy, 30
Justin, 19
**/
scala> val peopleRdd =
sc.textFile("examples/src/main/resources/people.txt")
peopleRdd: org.apache.spark.rdd.RDD[String] =
examples/src/main/resources/people.txt MapPartitionsRDD[18] at textFile
at <console>:24
scala> val peopleDF3 = peopleRdd.map(_.split(",")).map(paras =>
(paras(0),paras(1).trim().toInt)).toDF("name","age")
peopleDF3: org.apache.spark.sql.DataFrame = [name: string, age: int]
scala> peopleDF.show()
+-------+---+
| name|age|
+-------+---+
|Michael| 29|
| Andy| 30|
| Justin| 19|
+-------+---+

• Hive 在数据源章节介绍

3.3 DataFrame 常用操作

3.3.1 DSL 风格语法

• DSL 算子风格：

scala> val df = spark.read.json("/home/bduser/spark/examples/src/mainces/people.json")
df: org.apache.spark.sql.DataFrame = [age: bigint, name: string]

scala> df.select("age")
res1: org.apache.spark.sql.DataFrame = [age: bigint]

scala> df.select("age").show
+----+
| age|
+----+
|null|
|  30|
|  19|
+----+


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


scala> df.select($"age" + 1).show
+---------+
|(age + 1)|
+---------+
|     null|
|       31|
|       20|
+---------+


scala> df.select($"age" + 1,$"name").show
+---------+-------+
|(age + 1)|   name|
+---------+-------+
|     null|Michael|
|       31|   Andy|
|       20| Justin|
+---------+-------+


scala> df.select($"age" + 1,$"name").filter("age>20").show
+---------+----+
|(age + 1)|name|
+---------+----+
|       31|Andy|
+---------+----+

3.3.2 SQL 风格语法

• SQL风格

scala> df.createTempView("person")
scala> spark.sql("select * from person").show
+----+-------+
| age|   name|
+----+-------+
|null|Michael|
|  30|   Andy|
|  19| Justin|
+----+-------+

3.4 创建 DataSet

• Dataset 是具有强类型的数据集合，需要提供对应的类型信息。

3.5 Dataset 和 RDD 互操作

• Spark SQL 支持通过两种方式将存在的 RDD 转换为 Dataset，转换的过程中需要让 Dataset 获取 RDD 中的 Schema 信息，主要有两种方式，一种是通过反射来获取 RDD 中的 Schema 信息。这种方式适合于列名已知的情况下。第二种是通过编程接口的方式将 Schema 信息应用于 RDD，这种方式可以处理那种在运行时才能知道列的方式。

1. 常规转换

import sparkSession.implicits._
			RDD.toDF("columnName1", "columnName2"...)

3.5.1 通过反射获取 Scheam

• SparkSQL 能够自动将包含有 case 类的 RDD 转换成 DataFrame， case 类定义了 table 的结构，case 类属性通过反射变成了表的列名。 Case 类可以包含诸如 Seqs 或者 Array 等复杂的结构。

case class People(name:String, age:Int)
			RDD[People].toDF[Row[name,age]]

3.5.2 通过编程设置 Schema

• 如果 case 类不能够提前定义，可以通过下面三个步骤定义一个 DataFrame
• 创建一个多行结构的 RDD;
• 创建用 StructType 来表示的行结构信息。
• 通过 SparkSession 提供的 createDataFrame 方法来应用 Schema 。

val peopleRDD = fileRDD.map{
			      lines=>
				val strs = lines.split(",")
				Row(strs(0).trim, strs(1).trim.toInt)
			    }

			    val columnInfo = "name age" //从外部文件中动态读取
			    val fieldsArray = columnInfo.split(" ").map{
			      fieldName=>
				fieldName match {
				  case "age" => StructField(fieldName,IntegerType,nullable=true)
				  case _ => StructField(fieldName,StringType,nullable=true)
				}
			    }

			    val structType = StructType(fieldsArray)
			    val peopleDF = sparkSession.createDataFrame(peopleRDD, structType)

			    peopleDF.createTempView("person")
			    sparkSession.sql("select * from person").show()

3.6 类型之间的转换总结

RDD <-> DataFrame <-> DataSet

3.6.1 RDD->DataFrame：

3.6.2 DataFrame->RDD:

• DataFrame中对数据类型是不效验的

val prdd = peopleDF.rdd

DF[Row(name,age)].rdd[Row(name,age)]	
				Row的操作=> row.getAs[FieldType](FieldIndex)   row.getAs[String](FieldName)

3.6.3 RDD->DataSet

case class People(name:String, age:Int)
			RDD[People].toDS

3.6.4 DataSet -> RDD

case class People(name:String, age:Int)
			DS[People].rdd[People]

3.6.5 DataFrame->DataSet

case class People(name:String, age:Int)
			DF[Row(name,age)].as[People]

3.6.6 DataSet->DataFrame

case class People(name:String, age:Int)
			DS[People].toDF

• 总的测试代码：

    val sparkConf = new SparkConf().setAppName("RDD Trans").setMaster("local[*]")
    val sparkContext = new SparkContext(sparkConf)
    val sparkSession = SparkSession.builder().config(sparkConf).getOrCreate()



    //创建RDD
    val rdd = sparkContext.textFile("D:\\AboutMyWork\\IDEA-WorkSpace\\SparkDemos\\SparkSQLDemos\\src\\main\\resources\\datas\\people.txt")
    rdd.collect().foreach(println)

    val peopleRDD = rdd.map{
      line =>
        val strs = line.split(",")
       // (strs(0).trim,strs(1).trim.toInt)
        People(strs(0).trim,strs(1).trim.toInt)
    }

    // RDD -> DF
    import sparkSession.implicits._
    val peopleDF = peopleRDD.toDF("name","age")
    peopleDF.createTempView("ppp")
    sparkSession.sql("select * from ppp").show()

    // DF -> RDD
    val peoRDD = peopleDF.rdd
    peoRDD.map{
      row =>
        (row.getString(0),row.getInt(1))
    }.collect().foreach(println)

    //RDD -> DS
    val peopleDS = peopleRDD.toDS()
    peopleDS.createTempView("p2")
    sparkSession.sql("select * from p2").show()

    //DS -> RDD
    val pelRDD = peopleDS.rdd
    pelRDD.map{
      people=>
        (people.name,people.age)
    }.collect().foreach(println)

    //DF -> DS
    val pDS = peopleDF.as[People]
    pDS.createTempView("pDS")
    sparkSession.sql("select * from pDS").show()

    //DS -> DF
    val pDF = pDS.toDF()
    pDF.createTempView("pDF")
    sparkSession.sql("select * from pDF").show()





    sparkSession.close()
    sparkContext.stop()

                                                                                Michael, 29
Andy, 30
Justin, 19
+-------+---+
|   name|age|
+-------+---+
|Michael| 29|
|   Andy| 30|
| Justin| 19|
+-------+---+

(Michael,29)
(Andy,30)
(Justin,19)
+-------+---+
|   name|age|
+-------+---+
|Michael| 29|
|   Andy| 30|
| Justin| 19|
+-------+---+

(Michael,29)
(Andy,30)
(Justin,19)
+-------+---+
|   name|age|
+-------+---+
|Michael| 29|
|   Andy| 30|
| Justin| 19|
+-------+---+

+-------+---+
|   name|age|
+-------+---+
|Michael| 29|
|   Andy| 30|
| Justin| 19|
+-------+---+

3.7 用户自定义函数

• 通过 spark.udf 功能用户可以自定义函数。
• UDF->用户自定义函数(一进一出的函数)
• UDAF->(多进一出)
• UDTF->(一进多出)

3.7.1 用户自定义 UDF 函数

    val sparkConf = new SparkConf().setAppName("RDD Trans").setMaster("local[*]")
    //val sparkContext = new SparkContext(sparkConf)
    val sparkSession = SparkSession.builder().config(sparkConf).getOrCreate()

    val peopleDF = sparkSession.read.json("D:\\AboutMyWork\\IDEA-WorkSpace\\SparkDemos\\SparkSQLDemos\\src\\main\\resources\\datas\\people.json")

    peopleDF.createTempView("people")

	 //注册一个UDF函数
    sparkSession.udf.register("addHello",(x:String)=>"hello" + x)
    sparkSession.sql("select addHello(name) HelloName from people").show()

    sparkSession.close()

+------------+
|   HelloName|
+------------+
|helloMichael|
|   helloAndy|
| helloJustin|
+------------+

3.7.2 用户自定义聚合函数

• 强类型的 Dataset 和弱类型的 DataFrame 都提供了相关的聚合函数 ， 如 count()，countDistinct()， avg()， max()， min()。除此之外，用户可以设定自己的自定义聚合函数。
• 弱类型用户自定义聚合函数：通过继承 UserDefinedAggregateFunction 来实现用户自定义聚合函数。下面展示一个求平均年龄的自定义聚合函数。

case class MyAvgUDAF() extends UserDefinedAggregateFunction() {

  //输入数据的类型定义
  override def inputSchema: StructType = StructType(List(StructField("number",DoubleType,nullable=true)))

  //缓存数据的类型定义
  override def bufferSchema: StructType = StructType(StructField("sum",DoubleType,nullable=true) :: StructField("number",LongType,nullable=true) :: Nil )

  //返回数据的类型定义
  override def dataType: DataType = DoubleType

  //幂等性
  override def deterministic: Boolean = true

  //缓存的初始化
  override def initialize(buffer: MutableAggregationBuffer): Unit = {
    buffer(0) = 0.0 //总值
    buffer(1) = 0L //个数
  }

  override def update(buffer: MutableAggregationBuffer, input: Row): Unit = {
    buffer(0) = buffer.getDouble(0) + input.getDouble(0)
    buffer(1) = buffer.getLong(1) + 1L
  }

  override def merge(buffer1: MutableAggregationBuffer, buffer2: Row): Unit = {
    buffer1(0) = buffer1.getDouble(0) + buffer2.getDouble(0)
    buffer1(1) = buffer1.getLong(1) + buffer2.getLong(1)
  }

  override def evaluate(buffer: Row): Double = {
    buffer.getDouble(0) / buffer.getLong(1)
  }
}


object MyAvgUDAF{
  def main(args: Array[String]): Unit = {
    val sparkConf = new SparkConf().setAppName("SparkSQL UDAF").setMaster("local[*]")
    //val sparkContext = new SparkContext(sparkConf)
    val sparkSession = SparkSession.builder().config(sparkConf).getOrCreate()

    val peopleDF = sparkSession.read.json("D:\\AboutMyWork\\IDEA-WorkSpace\\SparkDemos\\SparkSQLDemos\\src\\main\\resources\\datas\\people.json")

    peopleDF.createTempView("people")

    //注册一个UDAF函数
    sparkSession.udf.register("myavg",new MyAvgUDAF)
    sparkSession.sql("select myavg(age) from people").show()

    sparkSession.close()
  }
}

+------------------------------+
|myavgudaf(CAST(age AS DOUBLE))|
+------------------------------+
|                          20.0|
+------------------------------+

• 强类型用户自定义聚合函数： 通过继承 Aggregator 来实现强类型自定义聚合函数，同样是求平均工资

4 Spark中的输入输出问题

4.1 输入

4.1.1 定制模式

• 通用处理模式

sparkSession.read.format("csv")[.option(driver,driverClass)].load()
			默认格式:parquet

4.1.2 简单模式

• 专用处理方式

parkSession.read.json("../people.json")
			.csv("路径")
			.parquet("路径")
			.table("路径")
			.orc("路径")
			.text("路径")
			.textFile("路径")
			.jdbc(driver,url,...)

4.2 输出

4.2.1 定制模式

• 通用处理模式

df.write.format("csv")[.option(driver,driverClass)].mode(SaveMode).save()
			默认格式:parquet

4.2.2 简单模式

• 专用处理方式

	df.write.json("../people.json")
		.csv("路径")
		.parquet("路径")
		.orc("路径")
		.text("路径")
		.jdbc(driver,url,...)

SaveMode

• Enum用法
  • • SaveMode.Overwrite: overwrite the existing data.
  • • SaveMode.Append: append the data.
  • • SaveMode.Ignore: ignore the operation (i.e. no-op).
  • • SaveMode.ErrorIfExists: default option, throw an exception at runtime.
• 字符串用法
  • • overwrite: overwrite the existing data.
  • • append: append the data.
  • • ignore: ignore the operation (i.e. no-op).
  • • error: default option, throw an exception at runtime.

4.3 JDBC示例

• Spark SQL 可以通过 JDBC 从关系型数据库中读取数据的方式创建 DataFrame，通过对DataFrame 一系列的计算后，还可以将数据再写回关系型数据库中。
• Spark SQL 也提供 JDBC 连接支持，这对于让商业智能(BI)工具连接到 Spark 集群上以 及在多用户间共享一个集群的场景都非常有用。 JDBC 服务器作为一个独立的 Spark 驱动 器程序运行，可以在多用户之间共享。任意一个客户端都可以在内存中缓存数据表，对表 进行查询。集群的资源以及缓存数据都在所有用户之间共享。
• Spark SQL 的 JDBC 服务器与 Hive 中的 HiveServer2 相一致。由于使用了 Thrift 通信协议，它也被称为“Thrift server”。

val sparkConf = new SparkConf().setAppName("sparkSQL UDF").setMaster("local[*]")
    //val sparkContext = new SparkContext(sparkConf)
    val sparkSession = SparkSession.builder().config(sparkConf).getOrCreate()

    val peopleDF = sparkSession.read.json("D:\\AboutMyWork\\IDEA-WorkSpace\\SparkDemos\\SparkSQLDemos\\src\\main\\resources\\datas\\people.json")

    peopleDF.write
      .format("jdbc")
      .option("url", "jdbc:mysql://localhost:3306/01_hivehomework")
      .option("dbtable", "spark_people")
      .option("user", "root")
      .option("password", "tiger")
      .save()

val sparkConf = new SparkConf().setAppName("sparkSQL UDF").setMaster("local[*]")
    //val sparkContext = new SparkContext(sparkConf)
    val sparkSession = SparkSession.builder().config(sparkConf).getOrCreate()

    //val peopleDF = sparkSession.read.json("D:\\AboutMyWork\\IDEA-WorkSpace\\SparkDemos\\SparkSQLDemos\\src\\main\\resources\\datas\\people.json")

    //从表里面读出
    val peopleDF =  sparkSession.read
      .format("jdbc")
        .option("url","jdbc:mysql://localhost:3306/01_hivehomework")
        .option("dbtable","spark_people")
        .option("user","root")
        .option("password","tiger")
        .load()


    //再写到另一个表
    peopleDF.write
      .format("jdbc")
      .option("url", "jdbc:mysql://localhost:3306/01_hivehomework")
      .option("dbtable", "spark_person")
      .option("user", "root")
      .option("password", "tiger")
      .mode(SaveMode.Overwrite)
      .save()

    //写到文件  -> 指定目录
    peopleDF.write.csv("D:\\Resourse\\16_Spark\\03_SparkSQL篇\\JDBC测试")

5 SparkSQL 与 Hive整合问题

5.1 Spark 内置Hive

• Spark 已经内置Hive应用。默认在本地文件系统中生成一个本地数据仓库。

• 内置Hive定位到HDFS上：

  1. 删除原来在本地文件系统中自动创建的元数据库和本地数据仓库
  2. 需要把HDFS中的文件系统配置整合到Spark中
     core-site.xml hdfs-site.xml
  3. 配置内置Hive的在HDFS上的目录地址
     启动Spark-shell时配置即可：--conf spark.sql.warehouse.dir=/user/spark/spark-warehouse

• 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，否则就是本地文件系统)。

• 注意：如果你使用的是内部的 Hive， 在 Spark2.0 之后， spark.sql.warehouse.dir 用于指定数据仓库的地址， 如果你需要是用 HDFS 作为路径， 那么需要将 core-site.xml和 hdfs-site.xml 加入到 Spark conf 目录， 否则只会创建 master 节点上的 warehouse 目录， 查询时会出现文件找不到的问题， 这是需要向使用 HDFS， 则需要将 metastore删除， 重启集群

• 打开Spark-shell

• 建Hive表，导入hdfs上的数据文件

spark.sql("create table spark_hive_test(id int,name string) row format delimited fields terminated by '\t'").show

spark.sql("load data inpath '/user/bduser/test/person.txt' into table spark_hive_test").show

spark.sql("select * from spark_hive_test").show

                                                                             +---+---------+
| id|     name|
+---+---------+
|  1|       li|
|  2|zhenshuai|
+---+---------+

• 一次配置，重启仍然可以查询得到

scala> :quit
20/03/18 22:03:43 ERROR LiveListenerBus: SparkListenerBus has already stopped! Dropping event SparkListenerExecutorMetricsUpdate(0,WrappedArray())
[bduser@node102 ~]$ spark-shell
Setting default log level to "WARN".
To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).
20/03/18 22:09:05 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
Spark context Web UI available at http://192.168.128.102:4040
Spark context available as 'sc' (master = local[*], app id = local-1584540557137).
Spark session available as 'spark'.
Welcome to
      ____              __
     / __/__  ___ _____/ /__
    _\ \/ _ \/ _ `/ __/  '_/
   /___/ .__/\_,_/_/ /_/\_\   version 2.1.3
      /_/

Using Scala version 2.11.8 (Java HotSpot(TM) 64-Bit Server VM, Java 1.8.0_172)
Type in expressions to have them evaluated.
Type :help for more information.

scala> spark.sql("select * from spark_hive_test").show
20/03/18 22:11:06 WARN ObjectStore: Failed to get database global_temp, returning NoSuchObjectException
+---+---------+
| id|     name|
+---+---------+
|  1|       li|
|  2|zhenshuai|
+---+---------+

• SparkSQL不能实现全部的Hive功能。

5.2 Spark接管外置Hive

• 把Hive配置目录中的hive-site.xml复制(或软链接)到spark的配置目录即可

[bduser@node102 datas]$ ln -s /opt/modules/hive-1.2.2/conf/hive-site.xml /opt/modules/spark-2.1.3/conf/hive-site.xml

[bduser@node102 lib]$ cp mysql-connector-java-5.1.20.jar /opt/modules/spark-2.1.3/jars/

• 打开spark-shell

scala> spark.sql("show tables").show
20/03/18 22:24:34 WARN ObjectStore: Failed to get database global_temp, returning NoSuchObjectException
+--------+---------+-----------+
|database|tableName|isTemporary|
+--------+---------+-----------+
+--------+---------+-----------+


scala> spark.sql("show databases").show
+------------+
|databaseName|
+------------+
|     db_hive|
|  db_youtube|
|     default|
|        test|
+------------+

• 直接输入spark-sql，显示一大堆日志后就类似Hive的客户端（Linux）。可以直接执行SQL语句，与数据仓库对话。
• 删除大量日志的显示，在/conf下配置

log4j.rootCategory=ERROR, console