Spark SQL

一、SQL on Hadoop

SQL是一种传统的用来进行数据分析的标准

• Hive：原始的SQL-on-Hadoop解决方案
• Impala：和Hive一样，提供了一种可以针对已有Hadoop数据编写SQL查询的方法
• Presto：类似于Impala，未被主要供应商支持
• Shark：Spark SQL的前身，设计目标是作为Hive的补充
• Phoenix：基于HBase的开源SQL查询引擎

二、Spark SQL

Spark SQL的前身

Shark是spark sql的前身，它的设计初衷是让Hive运行在Spark上，其是对Hive的改造，但是继承了大量Hive代码，给优化和维护带来了大量的麻烦

Spark SQL架构

• Spark SQL是Spark的核心组件之一 - 能够直接访问现存的Hive数据
• 提供JDBC/ODBC接口供第三方工具借助Spark进行数据处理
• 提供了更高层级的接口方便地处理数据
• 支持多种操作方式：SQL、API编程
• 支持多种外部数据源：Parquet、JSON、RDBMS等

Spark SQL运行原理

Spark sql执行计划的生成过程

• ①接收 sql 语句，初步解析成 logical plan
• ②上步生成的 logical plan，生成验证后的 logical plan
• ③对分析过后的 logical plan，进行优化
• ④对优化过后的 logical plan，生成 physical plan
• ⑤根据 physical plan，生成 rdd 的程序，并且提交运行
  

Catalyst优化器

在Spark SQL执行计划中，②③④过程被称为Catalyst

• Catalyst优化器是Spark SQL的核心
• Catalyst优化器将逻辑计划转为物理计划

逻辑计划

以执行下段代码为例

select name from
(
	select id,name from student
) as s
where s.id=1

该段代码的逻辑执行计划如图

优化

• 1、在投影上面查询过滤器
• 2、检查过滤是否可下
  经上述优化，会将执行计划修改
  

物理计划

优化后生成物理计划

二、Spark SQL API

SparkSession

• SparkSession合并了SQLContext（Spark SQL的编程入口）与HiveContext（SQLContext的子集，包含更多功能）
• Spark SQL所有功能入口点，，并允许使用DataFrame和Dataset API对Spark进行编程
• 在spark-shell下，会自动创建将sc（SparkContext）和spark（SparkSession）

// API获取SparkSession
val spark = SparkSession.builder
	.master("local[*]")  // 设置模式,local为本地模式
	.appName("appName")  // 设置appName
	.getOrCreate()       // 获取或创建SparkSession对象

DataSet

DataSet是特定域对象中的强类型集合

• createDataSet()创建DataSet对象，参数可以Seq、Array、RDD

// Seq做参数
spark.createDataset(1 to 3).show  // 类型为DataSet[Int]
// Array做参数
spark.createDataset(List(("a",1),("b",2),("c",3))).show  // 类型为DataSet[(String,Int)]
// RDD做参数
spark.createDataset(sc.parallelize(List(("a",1,1),("b",2,2)))).show // 类型为DataSet[(String,Int,Int)]

• 使用样例类（case class 创建Dataset）

case class Student(id:Int,name:String,sex:String,age:Int)
object InnerFunctionDemo2 {
  def main(args: Array[String]): Unit = {
  	// 获取SparkSession
    val spark: SparkSession = SparkSession.builder()
      .appName("innerFunc")
      .master("local[*]")
      .getOrCreate()
    //此包用于隐式转换，如将RDDs转换为DataFrame。
    import spark.implicits._
    val stuDS: Dataset[Student] = Seq(
      Student(1001, "zhangsan", "F", 20),
      Student(1002, "lisi", "M", 16),
      Student(1003, "wangwu", "M", 21)
    ).toDS()
    stuDS.show()
}

DataFrame

• 当Dataset的类型为Row时，就是DataFrame，可以理解为DataFrame=Dataset[Row]
• 在RDD基础上加入了Schema（数据结构信息）
• DataFrame Schema支持嵌套数据类型：struct、map、Array

RDD与DataFrame对比

Student的属性为Name、Age、Height

创建DataFrame的方式

• 从Spark数据源进行创建

// 读取csv文件
// option("header","true")的作用将文件首行作为表头
val df: DataFrame = spark.read.option("header","true").csv("in/users.csv")

// 读取json文件
val df2: DataFrame = spark.read.json("in/users.json")

• 从RDD转换成DataFrame
  ① 方式一：rdd类型转换成样例类，通过toDF方法转换成DataFrame

case class Person(name:String,age:Int)
// people.txt的内容格式为：zhangsan,21
import spark.implicits._
val df = sc.textFile("in/people.txt")
  .map(x => x.split(",")) // 以逗号分割
  .map(x => Person(x(0), x(1).toInt)) // 转换成Person样例类类型
  .toDF() // 转换成DataFrame
df.printSchema()
df.show()

② Row类型的RDD+Schema

import org.apache.spark.sql.Row
import org.apache.spark.sql.types.StructType
import org.apache.spark.sql.types.StructField
import org.apache.spark.sql.types.StringType
import org.apache.spark.sql.types._

val people = sc.textFile("in/people.txt")
// 字段名称
val schemaString = "name age"
// 为每个字段设置类型
val schema = StructType(schemaString.split(" ")
  .map(fieldName =>{
	if (fieldName.equals("name"))
	  StructField(fieldName, StringType, true)
	else
	  StructField(fieldName, IntegerType, true)
   })
)
// 将Rdd类型转为Row类型
val rowRDD = people.map(_.split(",")).map(x=>Row(x(0),x(1).toInt))
// 创建DataFrame
val peopleDataFrame = spark.createDataFrame(rowRDD,schema)

peopleDataFrame.printSchema()
peopleDataFrame.show()

从DataFrame转成RDD只需要用.rdd方法即可

三、Spark SQL操作外部数据源

Spark SQL支持的外部数据源

Spark SQL支持很多数据源，如常用的JSON、CSV、Hive、RDBMS等

使用常用数据源方式

在DataSet中json、csv的格式的文件读取已经演示，这里就不在赘述

• Parquet文件
  ①写parquet文件

// 获取SparkSession对象
val spark: SparkSession = SparkSession.builder()
	.master("local[*]")
 	.appName("perDemo")
 	.getOrCreate()
// 获取SparkContext对象
val sc:SparkContext = spark.sparkContext
import spark.implicits._
val list = List(
  ("zhangsan", "red", Array(3, 4, 5)),
  ("lisi", "black", Array(13, 14, 15)),
  ("wangwu", "orange", Array(23, 24, 25)),
  ("zhaoliu", "red", Array(33, 34, 35))
)
// 获取RDD
val rdd1: RDD[(String, String, Array[Int])] = sc.parallelize(list)
// 定义数据的结构
val schema = StructType(
  Array(
    StructField("name", StringType),
    StructField("color", StringType),
    StructField("numbers", ArrayType(IntegerType))
  )
)
// 将RDD转为Row类型
val rowRDD: RDD[Row] = rdd1.map(x=>Row(x._1,x._2,x._3))
val df: DataFrame = spark.createDataFrame(rowRDD,schema)
df.show
// 写parquet文件
df.write.parquet("out/color")

②读parquet文件

val df: DataFrame = spark.read.parquet("out/color")
df.printSchema()
df.show()

连接Hive表

① 在idea中，Spark SQL连接Hive

• 开启hive的元数据服务
  命令：nohup hive --service metastore &
• 添加maven依赖

<dependency>
  <groupId>org.apache.spark</groupId>
  <artifactId>spark-hive_2.11</artifactId>
  <version>2.1.1</version>
</dependency>

• 连接hive

val spark: SparkSession = SparkSession.builder()
   .master("local[*]")
   .appName("sparksqlOnHiveDemo")
   .config("hive.metastore.uris","thrift://192.168.233.133:9083") // 配置远程连接的metastore的uri
   .enableHiveSupport()
   .getOrCreate()
spark.sql("show databases").collect.foreach(println)

// 可以对hive中的表进行操作
val df: DataFrame = spark.sql("select * from toronto")
df.show()

注意：默认连接的时候Hive Default库，若需要使用其他库中的表，可以用库名.表名的形式

② spark-shell环境下连接Hive

• 修改Hive的conf目录下的hive-site.xml文件
  添加如下内容

<property>
  <name>hive.metastore.uris</name>
  <value>thrift://192.168.233.133:9083</value>
</property>

• 拷贝hive-site.xml文件到spark的conf目录下
  命令：cp /opt/hive/conf/hive-site.xml /opt/spark/conf/
• 进入spark-shell命令行，集成了hive后可直接访问Hive表

scala> val df=spark.table("toronto")
scala> df.show

连接RDBMS（关系型数据库管理系统）

以mysql为例
①在idea中，Spark SQL连接mysql

• 添加maven依赖

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

• 连接mysql

val spark: SparkSession = SparkSession.builder()
	.master("local[*]")
	.appName("sqlDemo")
	.getOrCreate()
// mysql驱动
val driver = "com.mysql.jdbc.Driver"
// mysql地址
val url = "jdbc:mysql://192.168.233.133:3306/information_schema"
// 登录用户名
val user = "root"
// 登录密码
val password = "root"
// 配置
val prop = new Properties()
prop.setProperty("user",user)
prop.setProperty("password",password)
prop.setProperty("driver",driver)
// 连接mysql，第二个参数为要连接的表名
val df: DataFrame = spark.read.jdbc(url,"SCHEMATA",prop)
df.printSchema()

②在spark-shell下连接mysql

• 添加mysql的jar包到spark的jars路径下
• 进入spark-shell命令行

scala> val url="jdbc:mysql://192.168.233.133:3306/hive"
scala> val tbname="TBLS"
scala> val prop = new java.util.Properties
scala> prop.setProperty("username","root")
scala> prop.setProperty("password","root")
scala> prop.setProperty("driver","com.mysql.jdbc.Driver")
scala> val jdbcDF= spark.read.jdbc(url,tbname,prop)
scala> jdbcDF.printSchema

Spark SQL函数

内置函数

Spark SQL提供了许多内置函数，可以参考这位博主的博文：Spark SQL 函数全集
使用内置函数的方式有两种，一种是通过编程的方式的使用，另一种是通过SQL的方式使用

• 编程式使用

// 使用内置函数需要导入此包
import org.apache.spark.sql.functions._
// scoreTableDF根据sid分组,count用于计算结果，选出count=2的
scoreTableDF.groupBy("sid").count().where("count=2").show()

• 通过SQL的方式

scoreTableDF.createOrReplaceTempView("score")
studentTableDF.createOrReplaceTempView("student")
spark.sql("select sid,count(1) from score group by sid having count(1)=2).show()

自定义函数

除了内置函数，Spark SQL中还可以使用自定义函数，详细内容可以参照这篇博客：Spark SQL 中的UDF、UDAF、UDTF

Spark性能优化

• 序列化
  Spark默认使用的是Java的序列化
  正确使用Kryo序列化，比Java序列化快约10倍，但不支持所有可序列化类型

conf.set("spark.serializer", "org.apache.spark.serializer.KryoSerializer");
//向Kryo注册自定义类型
conf.registerKryoClasses(Array(classOf[MyClass1], classOf[MyClass2]));

如果没有注册需要序列化的class，Kyro依然可以照常工作，但会存储每个对象的全类名(full class name)，这样往往比默认的 Java serialization 更浪费空间

• 使用对象数组、原始类型代替Java、Scala集合类（如HashMap）
  避免嵌套结构
• 尽量使用数字作为Key，而非字符串
• 以较大的RDD使用MEMORY_ONLY_SER
• 加载CSV、JSON时，仅加载所需字段
• 仅在需要时持久化中间结果（RDD/DS/DF）
• 避免不必要的中间结果（RDD/DS/DF）的生成
• DF的执行速度比DS快约3倍
• 自定义RDD分区与spark.default.parallelism
  该参数用于设置每个stage的默认task数量
• 将小变量广播出去，而不是直接使用
• 尝试处理本地数据并最小化跨工作节点的数据传输
• 表连接（join操作）
  最大的表放在第一位
  广播最小的表
  最小化表join的数量