大数据Spark08_SparkSql

从Shark说起

Shark除了基于Spark的特性外，Shark是完全兼容Hive的语法，表结构以及UDF函数等，由于SHark的整体设计架构对Hive的依赖性太强，难以支持其长远发展，比如不能和Spark的其他组件进行很好的集成，无法满足Spark的一栈式解决大数据处理的需求。

Spark SQL增加了SchemaRDD（即带有Schema信息的RDD），使用户可以在Spark SQL中执行SQL语句，数据既可以来自RDD，也可以来自Hive、HDFS、Cassandra等外部数据源，还可以是JSON格式的数据。Spark SQL目前支持Scala、Java、Python三种语言，支持SQL-92规范。从Spark1.2 升级到Spark1.3以后，Spark SQL中的SchemaRDD变为了DataFrame，DataFrame相对于SchemaRDD有了较大改变,同时提供了更多好用且方便的API，如图16-13所示。

图1-Spark-SQL支持的数据格式和编程语言

Spark SQL可以很好地支持SQL查询，一方面，可以编写Spark应用程序使用SQL语句进行数据查询，另一方面，也可以使用标准的数据库连接器（比如JDBC或ODBC）连接Spark进行SQL查询，这样，一些市场上现有的商业智能工具（比如Tableau）就可以很好地和Spark SQL组合起来使用，从而使得这些外部工具借助于Spark SQL也能获得大规模数据的处理分析能力。

DataFrame与RDD的区别

DataFrame的推出，让Spark具备了处理大规模结构化数据的能力，不仅比原有的RDD转化方式更加简单易用，而且获得了更高的计算性能。Spark能够轻松实现从MySQL到DataFrame的转化，并且支持SQL查询。

图 DataFrame与RDD的区别

从上面的图中可以看出DataFrame和RDD的区别。RDD是分布式的 Java对象的集合，比如，RDD[Person]是以Person为类型参数，但是，Person类的内部结构对于RDD而言却是不可知的。DataFrame是一种以RDD为基础的分布式数据集，也就是分布式的Row对象的集合（每个Row对象代表一行记录），提供了详细的结构信息，也就是我们经常说的模式（schema），Spark SQL可以清楚地知道该数据集中包含哪些列、每列的名称和类型。

和RDD一样，DataFrame的各种变换操作也采用惰性机制，只是记录了各种转换的逻辑转换路线图（是一个DAG图），不会发生真正的计算，这个DAG图相当于一个逻辑查询计划，最终，会被翻译成物理查询计划，生成RDD DAG，按照之前介绍的RDD DAG的执行方式去完成最终的计算得到结果。

DataFrame的创建

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

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

SparkSession是saprk整个应用程序功能的开始。SparkSession的创建只需要SparkSession.builder()。

这里我使用的是Spark1.6版本，仍继续使用SQLContext。

1、 SparkSQL的数据源

SparkSQL的数据源可以是JSON类型的字符串，JDBC,Parquent,Hive，HDFS等。

2、创建DataFrame的几种方式

1.   读取json格式的文件创建DataFrame

2.   通过json格式的RDD创建DataFrame

3.   非json格式的RDD创建DataFrame

  1) 通过反射的方式将非json格式的RDD转换成DataFrame（不建议使用）

  2) 动态创建Schema将非json格式的RDD转换成DataFrame

4.  读取parquet文件创建DataFrame

5.  读取JDBC中的数据创建DataFrame(MySql为例)

6.  读取Hive中的数据加载成DataFrame

读取json格式的文件创建DataFrame

注意：

--json文件中的json数据不能嵌套json格式数据。

--DataFrame是一个一个Row类型的RDD，df.rdd() DF转为RDD。

--可以两种方式读取json格式的文件。

--df.show()默认显示前20行数据。如果现实多行要指定多少行show(行数)

--DataFrame原生API可以操作DataFrame（不方便）。

--注册成临时表时，表中的列默认按ascii顺序显示列。

代码示例：

object CreateDF_FromJsonFile {
 def main(args: Array[String]): Unit = {
   val conf=new SparkConf()
   conf.setMaster("local").setAppName("CreateDF_FromJsonFile")
   val sc=new SparkContext(conf)
   val sqlContext = new SQLContext(sc)//spark 2.0后可以使用SparkSession作为应用程序功能实现的入口
   val df = sqlContext.read.json("./student_infos.json")
   //val df1 = sqlContext.read.format("json").load("./test.json")//读取Json文件的两种方式

  //df.show(30)//默认显示前20行数据 也可以指定显示的行数
    /**
     * dataFram自带的API 操作DataFrame
     */
    /**
     * 显示 DataFrame中的内容，默认显示前20行。如果现实多行要指定多少行show(行数)
     * 注意：当有多个列时，显示的列先后顺序是按列的ascii码先后显示。
     */
    df.show();
    /*
      +---+--------+
      |age|    name|
      +---+--------+
      | 18|zhangsan|
      | 19|    lisi|
      | 20|  wangwu|
      +---+--------+*/
   /**
    * DataFrame转换成RDD
    * DataFrame的底层是一个一个的RDD  RDD的泛型是Row类型。
    */
    val rdd=df.rdd // RDD<Row>
    // 1、打印模式信息:树形的形式显示schema信息
    df.printSchema()
    /*
     * root
     *  |-- age: long (nullable = true)
     *  |-- name: string (nullable = true)
     */
    //2、 选择多列
    df.select(df("name"),df("age")+1).show()
    /*
      +-------+---------+
      |   name|(age + 1)|
      +-------+---------+
      |zhangsan|      19|
      |    lisi|      20|
      |  wangwu|      21|
      +-------+---------+*/
    //3、 条件过滤
   df.filter(df("age") > 19 ).show()
    /*
      +-------+---------+
      |   name|      age|
      +-------+---------+
      |  wangwu|      21|
      +-------+---------+*/
   // 4、分组聚合
   val df2 = sqlContext.read.json("./student_infos2.json")
   df2.groupBy("age").count().show()
   /*
      +---+-----+
      |age|count|
      +---+-----+
      | 18|    2|
      | 19|    1|
      | 20|    1|
      +---+-----+*/
   // 5、排序
   df.sort(df("age").desc).show()
   /*
    * +---+--------+
      |age|    name|
      +---+--------+
      | 20|  wangwu|
      | 19|    lisi|
      | 18|zhangsan|
      +---+--------+
    * */
   //6、多列排序
   df.sort(df("age").desc, df("name").asc).show()
   /*
    * +---+--------+
      |age|    name|
      +---+--------+
      | 20|  wangwu|
      | 19|    lisi|
      | 18|zhangsan|
      +---+--------+
    * */
   //7、对列进行重命名
   df.select(df("name").as("username"),df("age")).show()
   /*
    * +--------+---+
      |username|age|
      +--------+---+
      |zhangsan| 18|
      |    lisi| 19|
      |  wangwu| 20|
      +--------+---+
    */
   //8、将DataFrame注册成临时的一张表，这张表临时注册到内存中，是逻辑上的表，不会雾化到磁盘
  df.registerTempTable("jtable");
  val results=sqlContext.sql("select * from jtable");
  results.show()
  sc.stop()
 }
}

通过json格式的RDD创建DataFrame

student_infos.json 文件

{"name":"zhangsan","age":18}
{"name":"lisi","age":19}
{"name":"wangwu","age":20}

student_scores.json 文件

{"name":"zhangsan","score":100}
{"name":"lisi","score":200}
{"name":"wangwu","score":300}

代码示例

object CreateDF_FromJsonRDD {
  def main(args: Array[String]): Unit = {
    val conf = new SparkConf()
    conf.setMaster("local").setAppName("jsonrdd")
    val sc = new SparkContext(conf)
    val sqlContext = new SQLContext(sc)
    val nameRDD = sc.textFile("./student_infos.json")
    val scoreRDD = sc.textFile("./student_scores.json")
    val nameDF = sqlContext.read.json(nameRDD)
    val scoreDF = sqlContext.read.json(scoreRDD)
    nameDF.registerTempTable("name")
    scoreDF.registerTempTable("score")
    val result = sqlContext.sql("select name.name,name.age,score.score from name,score where name.name = score.name")
    result.show()
  }
}

非json格式的RDD创建DataFrame

1）通过反射的方式将非json格式的RDD转换成DataFrame（不建议使用）

/**
 * 在利用反射机制推断RDD模式时，需要首先定义一个case class，因为，只有case class才能被Spark隐式地转换为DataFrame。
 */
object CreateDF_FromNotJsonRDD {
  case class Person(id:String,name:String,age:Int) //定义一个case class
  def main(args: Array[String]): Unit = {
  val conf = new SparkConf()
conf.setMaster("local").setAppName("rddreflect")
val sc = new SparkContext(conf)
val sqlContext = new SQLContext(sc)
val lineRDD = sc.textFile("./person.txt")
/**
* 将RDD隐式转换成DataFrame
*/
import sqlContext.implicits._//导入包，支持把一个RDD隐式转换为一个DataFrame
val personRDD = lineRDD.map { x => {
  val person = Person(x.split(",")(0),x.split(",")(1),Integer.valueOf(x.split(",")(2)))
  person
} }
val df = personRDD.toDF();
df.show()

/**
* 将DataFrame转换成PersonRDD
* 将DataFrame转换成RDD时获取字段两种方式,一种是df.getInt(0)下标获取（不推荐使用），另一种是df.getAs(“列名”)获取（推荐使用）
*/
val rdd = df.rdd
val result = rdd.map { x => {
  Person(x.getAs("id"),x.getAs("name"),x.getAs("age"))
} }
result.foreach { println}
sc.stop()
} 
  }

2）动态创建Schema将非json格式的RDD转换成DataFrame

/**
 * Row(split(0), split(1), Integer.valueOf(split(2))
 * 就会生成一个Row对象，这个对象里面包含了三个字段的值，这个Row对象就构成了rowRDD中的其中一个元素
 * 因为people有3行文本，所以，最终，rowRDD中会包含3个元素，每个元素都是org.apache.spark.sql.Row类型。
 * 实际上，Row对象只是对基本数据类型（比如整型或字符串）的数组的封装，本质就是一个定长的字段数组。
 */ 
object CreateDF_FromNotJsonRDDSchema {
  def main(args: Array[String]): Unit = {
    val conf = new SparkConf()
    conf.setMaster("local").setAppName("rddStruct")
    val sc = new SparkContext(conf)
    val sqlContext = new SQLContext(sc)
    val lineRDD = sc.textFile("./person.txt")
    val rowRDD = lineRDD.map { x =>
      {
        val split = x.split(",")
//        RowFactory.create(split(0), split(1), Integer.valueOf(split(2)))
        Row(split(0), split(1), Integer.valueOf(split(2)))
      }
    }
    val schema = StructType(List(
      StructField("id", StringType, true),
      StructField("name", StringType, true),
      StructField("age", IntegerType, true)))
    /**
     *建立rowRDD数据集和模式之间的对应关系，从而我们就知道对于rowRDD的每行记录，第一个字段的名称是schema中的“id”，
     * 第二个字段的名称是schema中的“name”,第三个字段的名称是schema中的“age”。
     */
    val df = sqlContext.createDataFrame(rowRDD, schema)
    df.show()
    df.printSchema()
    sc.stop()
  }
}

读取parquet文件创建DataFrame

object CreateDF_FromParquetFile {
  def main(args: Array[String]): Unit = {
     val conf = new SparkConf()
 conf.setMaster("local").setAppName("parquet")
 val sc = new SparkContext(conf)
 val sqlContext = new SQLContext(sc)
 val jsonRDD = sc.textFile("./test.json")
 val df = sqlContext.read.json(jsonRDD)
 df.show()
  /**
  * 将DF保存为parquet文件
  */
df.write.mode(SaveMode.Overwrite).format("parquet").save("./parquet")
// df.write.mode(SaveMode.Overwrite).parquet("./parquet")
 /**
  * 读取parquet文件
  */
 var result = sqlContext.read.parquet("./parquet")
// result = sqlContext.read.format("parquet").load("./parquet.parquet")
 result.show()
 sc.stop()
  }
}

读取JDBC中的数据创建DataFrame(MySql为例)

object CreateDF_FromJDBC {
  def main(args: Array[String]): Unit = {
    val conf = new SparkConf()
    conf.setMaster("local").setAppName("mysql")
    val sc = new SparkContext(conf)
    val sqlContext = new SQLContext(sc)
    /**
     * 第一种方式读取Mysql数据库表创建DF
     */
    val options = new HashMap[String, String]();
    options.put("url", "jdbc:mysql://192.168.8.18:3306/face")
    options.put("driver", "com.mysql.jdbc.Driver")
    options.put("user", "root")
    options.put("password", "root")
    options.put("dbtable", "person")
    val person = sqlContext.read.format("jdbc").options(options).load()
    person.show()
    person.registerTempTable("person")
    /**
     * 第二种方式读取Mysql数据库表创建DF
     */
    val reader = sqlContext.read.format("jdbc")
    reader.option("url", "jdbc:mysql://192.168.8.18:3306/face")
    reader.option("driver", "com.mysql.jdbc.Driver")
    reader.option("user", "root")
    reader.option("password", "root")
    reader.option("dbtable", "score")
    val score = reader.load()
    score.show()
    score.registerTempTable("score")
    val result = sqlContext.sql("select person.id,person.name,score.score from person,score where person.name = score.name")
    //result.show()
    /**
     * 将数据写入到Mysql表中
     */
    val properties = new Properties()
    properties.setProperty("user", "root")
    properties.setProperty("password", "123456")
    result.write.mode(SaveMode.Append).jdbc("jdbc:mysql://192.168.179.4:3306/spark", "result", properties)
    sc.stop()
  }
}

从Hive中读取数据创建DataFrame

1、Spark On Hive的配置

1）在Spark客户端安装包下spark-1.6.0/conf中创建文件hive-site.xml：

配置hive的metastore路径（hive的服务器端节点weekend11）

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

2）启动zookeeper集群（weekend11、weekend12、weekend13），启动HDFS集群（weekend08、weekend09、weekend10）。

3）启动Hive的metastore服务（weekend11）、启动hive客户端（weekend12）使用hive命令

hive --service metastore

4）启动SparkShell 读取Hive中的表总数，对比hive（客户端）中查询同一表查询总数测试时间。

./spark-shell

--master spark://node1:7077,node2:7077

 --executor-cores 1

--executor-memory 1g

--total-executor-cores 1

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

val hc = new HiveContext(sc)

hc.sql("show databases").show

hc.sql("user default").show

hc.sql("select count(*) from jizhan").show

储存DataFrame

1. 将DataFrame存储为parquet文件。

2. 将DataFrame存储到JDBC数据库。

3. 将DataFrame存储到Hive表。