目录
一、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的数量