spark-2-spark SQL

简介：

SparkSQL是Spark中处理结构化数据的一个模块。SparkSQL是sql解析引擎
优点：

• 易整合：使用SQL/DataFrame API，支持多种语言
• 统一的数据访问形式，不管什么数据库，都用同样的方式访问
• 兼容Hive：Hive on spark 将Hive SQL解析成Spark 任务，运行在Spark集群
• 标准的数据连接：使用BI工具连接数据库

Spark SQL API
Spark SQL的操作有两套api
Spark Core中：操作的是RDD
SparkSQL中：dataFrame DataSet是基于RDD更层次的抽象数据集
dataFrame ：spark1.3引入的Spark SQL Api
DataSet：spark1.6引入的Spark SQL Api
spark2.0，统一了这两个api。即：DataFrame = DataSet[Row]

DataFrame编程

DataFrame = RDD + Schema
与RDD相似，DataFrame也是一个不可变的分布式数据集合，但与RDD不同的是，数据都被组织到有名字的列中，就像关系型数据库中的表一样，除了数据之外，还记录着数据的结构信息，即schema。设计dataFrame的目的就是要让对大型数据集的操作变得更简单，它让开发者制定一个模式，进行更高层次的抽象。还提供了特定领域内专用的API来处理分布式数据。

spark-shell中操作：

scala> val rdd1 = sc.textFile("hdfs://hadoop000/person.txt")
scala> rdd1.collect.foreach(println)
zs 29 20000
fbb 18 6000
fcc 25 2000
ym 32 3000
scala> case class Person(name:String,age:Int,sal:Double)
scala> val rdd2 = rdd1.map(_.split(" "))
scala> val rdd3 = rdd2.map(t=>Person(t(0),t(1).toInt,t(2).toDouble))
scala> val pdf = rdd3.toDF()
scala> pdf.printSchema
root
 |-- name: string (nullable = true)
 |-- age: integer (nullable = false)
 |-- sal: double (nullable = false)

scala> pdf.select("name").show
+----+
|name|
+----+
|  zs|
| fbb|
| fcc|
|  ym|
+----+
scala> pdf.filter("age>20").show
+----+---+-------+
|name|age|    sal|
+----+---+-------+
|  zs| 29|20000.0|
| fcc| 25| 2000.0|
|  ym| 32| 3000.0|
+----+---+-------+

MAVEN

引入POM

<dependency>
    <groupId>org.apache.spark</groupId>
    <artifactId>spark-sql_2.11</artifactId>
    <version>${spark.version}</version>
</dependency>

在SparkCore中创建了sparkContext。在SparkSQL中就得创建SQLContext

获取dataFrame的方式：（旧API）

方式1

通过RDD[类] + case class 利用反射获取schema的信息，toDF生成DataFrame

/**
  * 获取dataFrame的方式：
  * 通过RDD[类] + case class 利用反射获取schema的信息
  */
object DataFrameDemo1 {
  def main(args: Array[String]): Unit = {
    val conf: SparkConf = new SparkConf().setMaster("local[*]").setAppName(this.getClass.getSimpleName)
    val sc = new SparkContext(conf)
    //创建SparkSQL实例
    val sqlContext = new SQLContext(sc)
    //导入隐式转换，不导入无法toDF()
    import sqlContext.implicits._
    val rdd1: RDD[String] = sc.textFile("person.txt")
    val pRDD: RDD[Person] = rdd1.map(t => {
      val split: Array[String] = t.split(" ")
      //利用Person 样例类封装数据
      Person(split(0), split(1).toInt, split(2).toDouble)
    })
    //缺少隐式转换爆红
    val pdf: DataFrame = pRDD.toDF()

    pdf.printSchema()
    //println(pdf.schema)

    //基于DataFrame执行操作：SQL / DSL
    //SQL   语法风格：若要使用就需要将DataFrame注册为一张临时表，再调用SQLContext的SQL方法，执行SQL
    pdf.registerTempTable("t_person")
    val sql: DataFrame = sqlContext.sql("select * from t_person order by sal desc")
    //默认展示20行
    sql.show()

    //DSL语法风格操作
    //DSL：领域特定语言    sql中的函数使用方法进行调用
    pdf.select("name","age").show()
    pdf.where("age > 30").show()


    //pdf.select("name").show()
    //pdf.filter("age>20").show()

    sc.stop()
  }
}
case class Person(name:String,age:Int,sal:Double)

方式2

通过自定义schema + RDD[Row]

/**
  * 通过自定义schema + RDD[Row] 实现DataFrame
  */
object DataFrameDemo2 {
  def main(args: Array[String]): Unit = {
    val conf: SparkConf = new SparkConf().setMaster("local[*]").setAppName(this.getClass.getSimpleName)
    val sc = new SparkContext(conf)
    val sqlContext = new SQLContext(sc)
    import sqlContext.implicits._

    val rdd1: RDD[String] = sc.textFile("person.txt")
    val rowRDD: RDD[Row] = rdd1.map(t => {
      val strs: Array[String] = t.split(" ")
      Row(strs(0), strs(1).toInt, strs(2).toDouble)
    })

    val structType = StructType(List(
      StructField("name", StringType, true),
      StructField("age", IntegerType, false),
      StructField("sal", DoubleType, false)
    ))
    
    val pdf: DataFrame = sqlContext.createDataFrame(rowRDD,structType)
    pdf.printSchema()
    pdf.select("name").show()
  }
}

dataFrame两种风格的wordcount

object DataFrameWordCount {
  def main(args: Array[String]): Unit = {
    val conf = new SparkConf().setMaster("local[*]").setAppName(this.getClass.getSimpleName)
    val sc = new SparkContext(conf)
    val sqlContext = new SQLContext(sc)
    import sqlContext.implicits._

    val file: RDD[String] = sc.textFile("wc.txt")

    //反射方式获取schema
    val wordsRDD: RDD[Word] = file.flatMap(_.split(" ")).map((Word(_)))
    val wdf: DataFrame = wordsRDD.toDF()

    //SQL风格
    wdf.registerTempTable("t_words")
    val sql: DataFrame = sqlContext.sql("select word,count(*) as cnt from t_words group by word order by cnt desc")
    sql.show()

    //通过自定义schema + RDD[Row] 实现DataFrame
    val rowRDD: RDD[Row] = file.flatMap(_.split(" ")).map(Row(_))
    val structType = StructType(List(StructField("word",StringType,true)))
    val dslFrame: DataFrame = sqlContext.createDataFrame(rowRDD,structType)
    
    //DSL风格
    dslFrame.select("word").groupBy("word").count().orderBy("count").show()
    
    sc.stop()
  }
}
case class Word(word:String)

dataFrame编程总结：

DataSet编程

DataSet也是一个分布式数据集合，并自带schema信息

高级API的两种风格

SparkSession 中已创建好了sparkContext和sqlContext

object DataSetDemo {
  def main(args: Array[String]): Unit = {
    //spark 2.x统一了访问api
    val spark: SparkSession = SparkSession.builder().appName(this.getClass.getSimpleName).master("local[*]").getOrCreate()
    //一定导入隐式转换
    import spark.implicits._
    val file: Dataset[String] = spark.read.textFile("person.txt")
    //dataSet自带schema
    file.printSchema()
    //root
    //|-- value: string (nullable = true)

    //数据处理：Dataset 元组的schema信息是根据 元组类型来确定的
    val splitDS: Dataset[(String, Int, Double)] = file.map(str => {
      val strs: Array[String] = str.split(" ")
      (strs(0), strs(1).toInt, strs(2).toDouble)
    })
    splitDS.printSchema()
    //看到，元组类型的取值只能用 _1._2._3    因此还要自定义schema
    //root
    //|-- _1: string (nullable = true)
    //|-- _2: integer (nullable = false)
    //|-- _3: double (nullable = false)

    //自定义schema
    val pdf: DataFrame = splitDS.toDF("name","age","sal")

    //SQL风格
    pdf.createTempView("v_person")
    val sql: DataFrame = spark.sql("select * from v_person where age > 30 limit 1")
    sql.show()

    //DSL风格：
    //select 选择
    pdf.select("name","age").show()
    //过滤  filter  where   where 调用了filter，是同一个api
    pdf.filter("age > 30").show()
    //order by     sort 是同一个api    默认升序
    pdf.orderBy($"age"desc).show()
    //分组groupBy
    val cnt: DataFrame = pdf.groupBy("age").count()
    cnt.show()
    val maxdf: DataFrame = pdf.groupBy("age").max("sal")
    maxdf.show()
    //agg聚合需要导入函数包
    import org.apache.spark.sql.functions._
    pdf.groupBy("age").agg(count("*") as "cnts").show()
    pdf.groupBy("age").agg(sum("sal") as "sumSal").show()
    maxdf.withColumnRenamed("max(sal)","maxSal").sort($"age"desc).limit(4).show()

    spark.close()
  }
}

dataSet，dataFrame转RDD

dataSet

val splitDS: Dataset[(String, Int, Double)] = file.map(str => {
  val strs: Array[String] = str.split(" ")
  (strs(0), strs(1).toInt, strs(2).toDouble)
})
val rdd: RDD[(String, Int, Double)] = splitDS.rdd

dataFrame需转换

val pdf: DataFrame = splitDS.toDF("name","age","sal")
val rdd: RDD[Row] = pdf.rdd
val rdd3: RDD[(String, Int, Double)] = rdd.map(row => {
  //row.get(0)
  val name: String = row.getAs[String](0)
  val age: Int = row.getAs[Int](1)
  val sal: Double = row.getDouble(2)
  (name, age, sal)
})
rdd3.foreach(println)

多表关联查询

object JoinDemo {
  def main(args: Array[String]): Unit = {
    val spark: SparkSession = SparkSession.builder().master("local[*]").appName(this.getClass.getSimpleName).getOrCreate()
    import spark.implicits._
    //用户信息表
    val ds1: Dataset[String] = spark.createDataset((List("ty 28 hunan","ruhua 40 xg","mr 30 shanxi","yixx 32 beijing","cangls 18 japan")))
    //数据预处理
    val processDS1: DataFrame = ds1.map(str => {
      val split: Array[String] = str.split(" ")
      (split(0), split(1).toInt, split(2))
    }).toDF("name", "age","proCode")

    val province: Dataset[String] = spark.createDataset((List("hunan,湖南省","xg,香港","shanxi,陕西省","beijing,北京","japan,日本省")))
    val processDS2: DataFrame = province.map(str => {
      val split: Array[String] = str.split(",")
      (split(0), split(1))
    }).toDF("proCode", "pName")

    //SQL:
    processDS1.createTempView("user")
    processDS2.createTempView("address")
    val sql1: DataFrame = spark.sql("select user.name as name,user.age,address.pName from user,address where user.proCode = address.proCode")
    val sql2: DataFrame = spark.sql("select u.age as age,u.name as name,a.pName as pName from user u join address a on a.proCode = u.proCode")
    sql1.show()
    sql2.show()

    //DSL
    val sql3: DataFrame = processDS1.join(processDS2,"proCode")
    //默认inner
    val sql4: Dataset[Row] = processDS1.join(processDS2,"left").where(processDS1("proCode") === processDS2("proCode")).orderBy(processDS1("age"))
    sql3.show()
    sql4.show()

    spark.stop()
  }
}

sparkSQL中自定义函数

UDF：user defined functions 使用频繁，输入输出一对一
UDAF：user defined aggregate functions 输入输出多对一
目的是不再做JOIN，单表查询

object UDFDemo {
  def main(args: Array[String]): Unit = {
    val spark: SparkSession = SparkSession.builder().appName(this.getClass.getSimpleName).master("local[*]").getOrCreate()
    import spark.implicits._

    val dataDF: DataFrame = spark.createDataset(List((10, 20, 30), (9, 16, 42), (12, 23, 35))).toDF("age1", "age2", "age3")
    dataDF.show()

    //注册udf函数
    spark.udf.register("operation",(x:Int)=>{
      x.toInt+100
    })
    //创建临时表
    dataDF.createTempView("xx")
    //使用函数查询
    val sql: DataFrame = spark.sql("select age2, operation(age1) as agg from xx")
    sql.show()
    
	//加载默认配置文件的顺序 application.conf--->application.json--->application.properties
	val config: Config = ConfigFactory.load()

	val url = config.getString("db.url")
	val conn = new Properties()
	conn.setProperty("user","root")
	conn.setProperty("password","123456")
	//在集群运行必须加Driver
	conn.setProperty("driver","com.mysql.jdbc.Driver")
	sql.write.mode(SaveMode.Append).jdbc(url,"tableName",conn)

    spark.close()
  }
}

保存数据库saveMode

sparkSQL任务提交到集群运行

从配置文件中加载各种配置
通过ConfigFactory加载配置文件
导入ConfigFactory所在的jar

<dependency>
    <groupId>com.typesafe</groupId>
    <artifactId>config</artifactId>
    <version>1.2.0</version>
</dependency>

在resource文件夹下new application.conf

//加载默认配置文件的顺序 application.conf--->application.json--->application.properties
val config: Config = ConfigFactory.load()
println(config.getString("db.url"))

config实例能被序列化传输的原因在于，具体的实现类SimpleConfig实现了序列化接口

sparkSQL数据源

指SparkSQL读入数据的格式：
普通文本，jdbc，json，csv，parquet
DataSourceFile

object DataSourceFile {
  def main(args: Array[String]): Unit = {
    val spark: SparkSession = SparkSession.builder().appName(this.getClass.getSimpleName).master("local[*]").getOrCreate()
    import spark.implicits._

    //同一个类型的文件一起读
    val text: Dataset[String] = spark.read.textFile("person.txt","wc.txt")
    val text1: DataFrame = spark.read.text("wc.txt")

    val file: Dataset[String] = spark.read.textFile("person.txt")
    val df2: Dataset[(String, String, String)] = file.map(str => {
      val split: Array[String] = str.split(" ")
      (split(0), split(1), split(2))
    })

    //text API只支持单列，String类型    //报错
    //df2.write.text("output1")
    //save API 数据格式是parquet
    file.write.save("output2")
    //将dataSet  转为  RDD  再写入文件
    df2.rdd.saveAsTextFile("output3")

    spark.stop()
  }
}

DataSourceJDBC

object DataSourceJDBC {
  def main(args: Array[String]): Unit = {
    val spark: SparkSession = SparkSession.builder().appName(this.getClass.getSimpleName).master("local[*]").getOrCreate()
    import spark.implicits._

    val config: Config = ConfigFactory.load()

    val conn = new Properties()
    conn.setProperty("user",config.getString("db.user"))
    conn.setProperty("password",config.getString("db.password"))
    conn.setProperty("driver",config.getString("db.driver"))

    //读取jdbc的API
    val jdbc: DataFrame = spark.read.jdbc(config.getString("db.url"),"bank_a",conn)
    spark.read.format("jdbc").jdbc(config.getString("db.url"),"bank_a",conn)
    jdbc.printSchema()

    val selectDF: DataFrame = jdbc.where("money < 200").select("user")
    selectDF.printSchema()

    selectDF.write.mode(SaveMode.Append).jdbc(config.getString("db.url"),"emp2",conn)
    
    spark.stop()
  }
}

JSON数据源

object JsonDataSource {
  def main(args: Array[String]): Unit = {
    val spark: SparkSession = SparkSession.builder().appName(this.getClass.getSimpleName).master("local[*]").getOrCreate()
    import spark.implicits._

    //数据格式：{"pname":"meinv", "price":200, "amount":10}
    val jsonDF: DataFrame = spark.read.json("log.json")
    jsonDF.printSchema()
    jsonDF.show()

    //数据格式：{"pname":"meinv", "price":200, "amount":10,"province":{"city":"bj"}}
    val json2: DataFrame = spark.read.json("log2.json")
    json2.printSchema()
    json2.show()
    ///DSL获得city
    json2.select("province.city").show()
    //SQL方式
    json2.createTempView("json")
    spark.sql("select province.city from json").show()
    //root
    //|-- amount: long (nullable = true)
    //|-- pname: string (nullable = true)
    //|-- price: double (nullable = true)

    //json格式的数据类型是按照字典顺序写的，注意顺序
    jsonDF.toDF("amount2","pname2","price2").show()
    jsonDF.withColumnRenamed("pname","names").show()

    val whereDS: Dataset[Row] = jsonDF.where("price > 100")
    whereDS.write.json("jsonout")

    spark.stop()
  }
}

CSV格式的数据

csv是python机器学习中非常常见的数据格式，默认，分割 没有schema ，可用execel打开

object CSVDataSource {
  def main(args: Array[String]): Unit = {
    val spark: SparkSession = SparkSession.builder().appName(this.getClass.getSimpleName).master("local[*]").getOrCreate()
    import spark.implicits._

    val csv: DataFrame = spark.read.csv("log.csv")
    csv.printSchema()
    //root
    //|-- _c0: string (nullable = true)
    //|-- _c1: string (nullable = true)
    //|-- _c2: string (nullable = true)
    csv.show()

    val csvDS: DataFrame = csv.map(str => {
      val name: String = str.getString(0)
      val age: Int = str.getString(1).toInt
      val fv: Double = str.getString(2).toDouble
      (name, age, fv)
    }).toDF("name", "age", "fv")

    val filter: Dataset[Row] = csvDS.where("age>30")
    filter.show()
    filter.write.csv("csvout")

    spark.stop()
  }
}

Parquet

Parquet：列式存储，使用时只需要读取需要的列，支持向量运算，能获得更好的扫描行。
压缩编码可降低磁盘存储空间，由于同一列的数据类型是相同的，可以使用不同的压缩编码
可以跳过不符合条件的数据，只读取需要的数据，降低io
通用的，适配多种计算框架，查询引擎（hive，impala，pig等）计算框架（MapReduce，spark等）数据模型（avro，thrift，json等）
压缩比：12：1
缺点，不支持update操作

//读取
val parquet = spark.read.parquet("xxx").show()
//写文件：
parquet.rdd.coalesce(1).saveAsTextFile("xxx")

println(parquet.count())

HiveOnSpark

将hive任务提交到spark
HiveOnSpark跟hive没有太大关系，就是使用了hive的标准（HQL，元数据库，UDF，序列化，反序列化机制）
hive原有的计算模型是MR，速度慢（将中间结果写进HDFS）
HiveOnSpark使用RDD(DataFrame),然后运行在spark集群
mysql存储hive的元数据（保存的是hive表的描述信息，描述了有哪些database，table，以及表有多少列，每一列是什么类型，描述表的数据存在hdfs的什么位置），真正数据存储在HDFS

Hive和MySQL的区别
Hive是一个数据仓库(存储并分析数据，数据量大，一般都是长时间分析)
MySQL是关系型数据库（CRUD低延迟）
hive的元数据库的功能：建立了一种映射关系，执行HQL时，先到mysql元数据库查找描述信息，然后根据描述信息生成任务，然后将任务下发到spark集群执行

HiveOnSpark 使用的仅仅是hive的标准和规范，不需要有hive数据库一样可行

Spark-SQL的交互式命令操作

进行sql的基本操作，创建表，插入的语句。默认的元数据在deby数据库。
真正的元数据存储在本地磁盘中，就在执行spark-sql所在路径下的spark-warehouse中

将deby -> mysql 本地磁盘数据 ->hdfs
利用hive的机制，将hive-site.xml文件放在spark安装目录conf下
放在一台机器（客户端下）（hadoop002）

解决方式:

spark-sql --jars /mytemp/mysql-connector-java-8.0.13.jar

在mysql中生成了hive数据库，但仍不能将数据写入hdfs，需修改DBS表中的配置：hdfs://hadoop000:8020/user/root/spark-warehouse/

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

val spark: SparkSession = SparkSession.builder().appName(this.getClass.getSimpleName).master("local[*]")
  .enableHiveSupport().getOrCreate()

将hive-site.xml放在resource文件夹下

总结

DataFrame和DataSet都可以通过 .rdd 转化为RDD
RDD的优缺点：
编译时类型安全：编译时就能检查出错误。
面向对象的编程风格：直接通过类名打点操作数据
缺点：
序列化和反序列化的性能开销，无论是集群中的操作还是IO，都需要对对象的结构和数据进行序列化和反序列化。
GC的性能开销：频繁的创建和销毁对象，势必增加GC

DataFrame优缺点：
DataFrame引入了schema和off-heap
schema：RDD每行的数据，结构都是一样的，这个结构就存储在schema中，Spark中通过schema就能读懂数据，因此在通信和IO时，就只需要序列化和反序列化数据，而结构的部分就能省略。
of-heap：意味着JVM的堆外内存，这些内存直接受操作系统管理（而非JVM）。Spark能够以二进制的形式序列化数据（不包括结构）到off-heap中，当要操作数据时，就直接操作off-heap内存，由于Spark理解schema，所以知道该如何操作。
优点：
通过schema和off-heap，DataFrame解决了RDD的缺点，DataFrame不受JVM的限制，没有GC困扰
缺点：
DataFrame不是类型安全的，API也不是面向对象风格的

DataSet的优缺点：
优点：DataSet结合了RDD和DataFrame的优点（类型安全，面向对象，不受JVM限制，无GC开销）

静态类型与运行时类型安全

如果你用的是Spark SQL的查询语句,要直到运行时才会发现有语法错误（代价大），而如果你使用了DataSet或DataFrame(dsl)，在编译时就可以捕获错误（节省开发者时间）。即当你在DataFrame中调用了API之外的函数时，编译器就可以发现这个错误。不过如果你使用了一个不存在的字段名，那就要到运行时才可发现错误。因为DataSetAPI都是用lambda函数和JVM类型对象表示的，所有不匹配的类型参数都可以在编译时发现。而且在使用DataSet时，你的分析错误也会在编译时会发现，这样就节省了开发者的时间和代价。

所有这些最终都被解释成关于类型安全的图谱，内容就是你的spark代码里的语法和错误分析。在图谱中，DataSet是最严格的一端，但对于开发者也是效率最高的。

方便易用的结构化API

虽然结构化可能会限制Spark程序对数据的控制，但它却提供了丰富的语义，和方便易用的特定领域内的操作，后者可以被表示为高级结构。事实上，用DataSet的高级API可以完成大多数的计算。比如，比用RDD数据行的数据字段进行agg,select,sum,avg,map,filter或groupBy等操作更简单。

性能与优化

在Scala和java中，一个DataFrame所代表的是一个多个Row（行）的DataSet（数据集合）。在ScalaAPI中，DataFrame仅仅是一个DataSet[Row]类型的别名。然而，在JavaAPI中，用户需要使用DataSet<Row>去代表一个DataFrame

SparkSQL梳理：

两套API：DataSet DataFrame基本操作
两套语法风格：SQL 和 DSL
关联查询：join 及新的解决方案：UDF函数
SparkSQL的数据源：csv， parquet （可能用的最多），jdbc ， json读取都是通用，混用的.
Hive On Spark