八、Spark SQL

一、基本概念

• Spark SQL提供了一种特殊的RDD，叫做SchemaRDD。
• SchemaRDD是存放Row对象的RDD，每个Row对象代表一行记录。
• SchemaRDD支持RDD上所没有的一些新操作，比如运行SQL查询。
• SchemaRDD可以从外部数据源创建，也可以从查询结果或普通RDD中创建。

二、连接Spark SQL

1.Spark SQL依赖等信息

• 跟spark其他程序库一样，需要在应用中引入Spark SQL需要添加一些额外的依赖。这种分离机制使得spark内核编译无需依赖大量额外的包。
• Apache Hive是Hadoop上的SQL引擎，Spark SQL编译时可以包含Hive支持，也可以不包含。
• 包含Hive支持的Spark SQL可以支持Hive表访问、UDF(用户自定义函数)、SerDe(序列化格式和反序列化格式)，以及Hive查询语言(HiveSql/HQL)。
• 需要强调的一点，如果要在Spark SQL中包含Hive库，并不需要事先安装Hive。
• 如果下载的二进制版本的Spark，它已经在编译时添加了Hive支持。如果从代码编译Spark，应该使用sbt/sbt -Phive assembly编译，以打开Hive支持。
• 带有Hive支持的Spark SQL的Maven索引

groupId = org.apache.spark
artifactId = spark-hive_2.10
version = 1.2.0

如果你不能引入Hive依赖，那就应该使用工件spark-sql_2.10代替spark-hive_2.10

2.Spark SQL编程入口

• 当使用Spark SQL进行编程时，根据是否使用Hive支持，有两个不同入口。
• 推荐使用的入口是HiveContext,它可以提供HiveQL以及其他依赖于Hive的功能的支持。
• 更为基础的SQLContext则支持Spark SQL功能的一个子集，子集中去掉了需要依赖于Hive的功能。
• 这种分离主要是为了那些可能会因为引入Hive的全部依赖而陷入依赖冲突的用户设计的。
• 使用HiveContext不需要事先部署好Hive。

3.Spark SQL连接Hive

• 若要把Spark SQL连接到一个部署好的Hive上，需要把hive-site.xml复制到Spark的配置文件目录中($SPARK_HOmE)。即使没有部署好Hive，Spark SQL也可以运行。
• 如果没有部署好Hive，Spark SQL会在当前的工作目录中创建出自己的Hive元数据仓库，叫做metastore_db。此外，如果你尝试使用HiveQL中的CREATE TABLE(CREATE EXTERNAL TABLE)语句来创建表，这些表会被放在你默认的文件系统中的/user/hive/warehouse目录中(如果你的classpath中有配好的hdfs-site.xml,默认的文件系统就是HDFS，否则就是本地文件系统)。
• hive-site.xml文件

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
  <!-- WARNING!!! This file is auto generated for documentation purposes ONLY! -->
  <!-- WARNING!!! Any changes you make to this file will be ignored by Hive.   -->
  <!-- WARNING!!! You must make your changes in hive-site.xml instead.         -->
  <!-- Metastore property -->
  <property>
    <name>hive.metastore.warehouse.dir</name>
    <value>/user/map_navi_spark/hive/warehouse</value>
    <description>location of default database for the warehouse</description>
  </property>
  <property>
    <name>hive.metastore.uris</name>
    <value>thrift://10.153.53.249:9083</value>
  </property> 
  <property>
    <name>hive.exec.scratchdir</name>
    <value>/user/map_navi_spark/hive/data-scratchdir</value>
    <description>Scratch space for Hive jobs</description>
  </property>


   <property>
	<name>hive.exec.stagingdir</name>
    <value>/user/map_navi_spark/hive/data-stagingdir</value>
   </property>
   <property>
    <name>hive.metastore.client.socket.timeout</name>
    <value>60</value>
   </property>

   <property>
    <name>hive.cache.expr.evaluation</name>
    <value>false</value>
    <description>cache evaluation will make sogou common-lib udfs not work, so disable it.</description>
   </property>
</configuration>

三、在应用中使用Spark SQL

• 初始化Spark SQL
• 基本查询示例

//    val sc = new SparkContext(conf)
//    val hiveCtx = new HiveContext(sc)
//    //"Use SparkSession.builder.enableHiveSupport instead", "2.0.0"
//    val input = hiveCtx.jsonFile("inputFile")
//      //注册输入的SchemaRDD
//    input.registerTempTable("tweets")
//    //依据retweetCount(转发计数)选出推文
//    val topTweets = hiveCtx.sql("SELECT text,retweetCount FROM tweets ORDER BY retweetCount LIMIT 10")


val spark: SparkSession = SparkSession.builder.
        appName("retweetCount").
        master("local[3]").
        enableHiveSupport().
        config(conf).
        getOrCreate
        
//    import spark.implicits._
//    import spark.sql
//    sql("SELECT text,retweetCount FROM tweets ORDER BY retweetCount LIMIT 10")
    //上下文对象
val sc: SparkContext = spark.sparkContext
//sqlContext
val hiveCtx: SQLContext = spark.sqlContext

val input = hiveCtx.read.json("inputFile")
//注册输入的SchemaRDD
input.createOrReplaceTempView("tweets")
//依据retweetCount(转发计数)选出推文
val topTweets = hiveCtx.sql("SELECT text,retweetCount FROM tweets ORDER BY retweetCount LIMIT 10")

1.SchemaRDD(DataFrame)

• 读取数据和执行查询都会返回SchemaRDD。SchemaRDD和传统数据库中的表的概念类似。
• 从内部机理来看，SchemaRDD是由Row对象组成的RDD，附带包含每列数据类型的结构信息。
• Row对象只是对基本数据类型（如整型和字符串等）的数组的封装。
• spark1.3后，SchemaRDD名字改为DataFrame
• SchemaRDD任然是RDD，所以可以对其应用已有的RDD转化操作，比如map()和filter()。最重要的是，你可以把任意SchemaRDD注册为临时表，这样就可以使用HiveContext.sql或SQLContext.sql来对它进行查询了。
• Spark SQL/HiveQL类型与Scala等类型对应表(参考146页)
• 使用Row对象。在Scala/Java中，Row对象有一系列getter方法，可以通过获得下表获取每个字段的值。

2.缓存

• 我们知道每列的类型信息，所以Spark可以更加高效地存储数据。
• 为了确保使用更节约内存的表示方式进行缓存而不是存储整个对象，应当使用专门的hiveCtx.cacheTable(“tableName”)方法。
• 这些缓存只会在驱动器程序的生命周期里保留在内存中。
• 你也可以使用HiveQL/SQL语句来缓存表。只需要运行CACHE TABLE tableName或UNCACHE TABLE tableName来缓存或者删除已有的缓存即可。

四、读取和存储数据

• 当你使用SQL查询Hive表、JSON和Parquet等这些数据源中的数据时，Spark SQL可以智能地扫描这些用到的字段，而不是像SparkContext.hadoopFile中那样简单粗暴地扫描全部数据。
• 你也可以在程序中通过指定结构信息，将常规的RDD转化为SchemaRDD。

1.Apache Hive

val spark: SparkSession = SparkSession.builder.
    appName("retweetCount").
    master("local[3]").
    enableHiveSupport().
    config(conf).
    getOrCreate
//上下文对象
val sc: SparkContext = spark.sparkContext
//sqlContext
val hiveCtx: SQLContext = spark.sqlContext
val rows = hiveCtx.sql("SELECT key,value FROM mytable")
val keys = rows.map(row => row.getInt(0))

2.Parquet

• Parquet是一种流行的列式存储结构，可以高效地存储具有嵌套字段的记录。
• Parquet格式经常在Hadoop生态圈中被使用，它也支持Spark SQL的全部数据类型。Spark SQL提供了直接读取和存储Parquet格式文件的方法。
• 读取Parquet数据

val spark: SparkSession = SparkSession.builder.
      appName("retweetCount").
      master("local[3]").
      enableHiveSupport().
      config(new SparkConf()).
      getOrCreate
                             
val df = spark.read.parquet("inputpath")                                  

• 存储Parquet数据

val passLinkDF = passLinkSaved.toDF()
passLinkDF.write.format("parquet").mode(SaveMode.Append).partitionBy("cdate").parquet("/user/map_navi_spark/stat/q4_all_passlink_parquet")

3.JSON

• 如果想从获得的数据中推断出来的结构信息，可以在生成的SchemaRDD上调用printSchema方法。
• 例子

root
 |-- tripID: string (nullable = true)
 |-- from: struct (nullable = true)
 |    |-- status: string (nullable = true)
 |    |-- tripID: string (nullable = true)
 |    |-- gpsTm: long (nullable = true)
 |    |-- originX: double (nullable = true)
 |    |-- originY: double (nullable = true)
 |    |-- prjX: double (nullable = true)
 |    |-- prjY: double (nullable = true)
 |    |-- link: struct (nullable = true)
 |    |    |-- id: integer (nullable = true)
 |    |    |-- direct: integer (nullable = true)
 |    |    |-- length: double (nullable = true)
 |    |    |-- trip_index: integer (nullable = true)
 |    |-- distError: double (nullable = true)
 |    |-- angleError: double (nullable = true)
 |-- to: struct (nullable = true)
 |    |-- status: string (nullable = true)
 |    |-- tripID: string (nullable = true)
 |    |-- gpsTm: long (nullable = true)
 |    |-- originX: double (nullable = true)
 |    |-- originY: double (nullable = true)
 |    |-- prjX: double (nullable = true)
 |    |-- prjY: double (nullable = true)
 |    |-- link: struct (nullable = true)
 |    |    |-- id: integer (nullable = true)
 |    |    |-- direct: integer (nullable = true)
 |    |    |-- length: double (nullable = true)
 |    |    |-- trip_index: integer (nullable = true)
 |    |-- distError: double (nullable = true)
 |    |-- angleError: double (nullable = true)
 |-- v_prop: double (nullable = true)
 |-- seqLink: array (nullable = true)
 |    |-- element: struct (containsNull = true)
 |    |    |-- id: integer (nullable = true)
 |    |    |-- direct: integer (nullable = true)
 |    |    |-- length: double (nullable = true)
 |    |    |-- trip_index: integer (nullable = true)
 |-- length: double (nullable = true)
 |-- passspeed: double (nullable = true)

4.基于RDD

• 除了读取数据，也可以基于RDD创建SchemaRDD。在Scala中，带有case class的RDD可以隐式转换成SchemaRDD。

case class HappyPerson(handle: String,favouriteBeverage: String)
val happyPersonRdd = sc.parallelize(List("holden","coffee"))
import spark.implicits._
val happyPersonDf = happyPersonRdd.toDF
happyPersonDf.createOrReplaceTempView("viewName")
//接下来就可以使用sql语句进行查询了

五、JDBC/ODBC服务器(理解还有些问题)

• Spark SQL的JDBC服务器与Hive中的HiveServer2相一致。由于使用了Thrift通信协议，它也被称为"Thrift server"。
• 注意，JDBC服务器支持需要Spark在打开Hive支持的选项下编译。
• 服务器可以通过Spark目录中的sbin/start-thiftserver.sh启动。
• beeline客户端

六、Spark SQL通过JDBC连接外部数据库

1.DBC connection properties

• 属性名称和含义

  • url：要连接的JDBC URL。列如：jdbc:mysql://ip:3306
  • dbtable:应该读取的JDBC表。可以使用括号中的子查询代替完整表。
  • driver:用于连接到此URL的JDBC驱动程序的类名,列如：com.mysql.jdbc.Driver

• partitionColumn, lowerBound, upperBound, numPartitions：

  • 这些options仅适用于read数据。这些options必须同时被指定。他们描述，如何从多个workers并行读取数据时，分割表。
  • partitionColumn：必须是表中的数字列。
  • lowerBound和upperBound仅用于决定分区的大小，而不是用于过滤表中的行。
    表中的所有行将被分割并返回。

• fetchsize：仅适用于read数据。JDBC提取大小，用于确定每次获取的行数。这可以帮助JDBC驱动程序调优性能，这些驱动程序默认具有较低的提取大小（例如，Oracle每次提取10行）。

• batchsize：仅适用于write数据。JDBC批量大小，用于确定每次insert的行数。
  这可以帮助JDBC驱动程序调优性能。默认为1000。

• isolationLevel：仅适用于write数据。事务隔离级别，适用于当前连接。它可以是一个NONE，READ_COMMITTED，READ_UNCOMMITTED，REPEATABLE_READ，或SERIALIZABLE，对应于由JDBC的连接对象定义，缺省值为标准事务隔离级别READ_UNCOMMITTED。请参阅文档java.sql.Connection。

• truncate：仅适用于write数据。当SaveMode.Overwrite启用时，此选项会truncate在MySQL中的表，而不是删除，再重建其现有的表。这可以更有效，并且防止表元数据（例如，索引）被去除。但是，在某些情况下，例如当新数据具有不同的模式时，它将无法工作。它默认为false。

• createTableOptions：仅适用于write数据。此选项允许在创建表（例如CREATE TABLE t (name string) ENGINE=InnoDB.）时设置特定的数据库表和分区选项。

2.spark jdbc read MySQL

• 方式一：使用DataFrameReader 类提供的load()方法从指定数据库读取数据

val jdbcDF1 = spark.read.format("jdbc")
      .option("driver", "com.mysql.jdbc.Driver")
      .option("url", "jdbc:mysql://ip:3306")
      .option("dbtable", "db.user_test")
      .option("user", "test")
      .option("password", "123456")
      .option("fetchsize", "3")
      .load()
jdbcDF1.show

val jdbcDF2 = spark.read.format("jdbc").options(
      Map(
        "driver" -> "com.mysql.jdbc.Driver",
        "url" -> "jdbc:mysql://ip:3306",
        "dbtable" -> "db.user_test",
        "user" -> "test",
        "password" -> "123456",
        "fetchsize" -> "3")).load()
jdbcDF2.show

• 方式二：使用DataFrameReader 类提供的jdbc()方法从指定数据库读取数据

jdbc(url: String, table: String, properties: Properties): DataFrame

import java.util.Properties

// jdbc(url: String, table: String, properties: Properties): DataFrame
val readConnProp1 = new Properties()
readConnProp1.put("driver", "com.mysql.jdbc.Driver")
readConnProp1.put("user", "test")
readConnProp1.put("password", "123456")
readConnProp1.put("fetchsize", "3")

val jdbcDF3 = spark.read.jdbc(
      "jdbc:mysql://ip:3306",
      "db.user_test",
      readConnProp1)
jdbcDF3.rdd.partitions.size //默认并行度为1
jdbcDF3.show

val jdbcDF4 = spark.read.jdbc(
      "jdbc:mysql://ip:3306",
      "(select * from db.user_test where gender=1) t",  // 注意括号和表别名，必须得有，这里可以过滤数据
      readConnProp1)
jdbcDF4.show()

• 方式三

jdbc(url: String, table: String,
     columnName: String, lowerBound: Long, upperBound: Long, numPartitions: Int,
     connectionProperties: Properties): DataFrame

import java.util.Properties

val readConnProp2 = new Properties()
readConnProp2.put("driver", "com.mysql.jdbc.Driver")
readConnProp2.put("user", "test")
readConnProp2.put("password", "123456")
readConnProp2.put("fetchsize", "2")

val columnName = "uid"
val lowerBound = 1
val upperBound = 6
val numPartitions = 3

val jdbcDF5 = spark.read.jdbc(
      "jdbc:mysql://ip:3306",
      "db.user_test",
      columnName,
      lowerBound, //lowerBound和upperBound仅用于决定分区的大小，而不是用于过滤表中的行。表中的所有行将被分割并返回。
      upperBound,
      numPartitions,
      readConnProp2)

jdbcDF5.rdd.partitions.size //并行度为3，对应于numPartitions
jdbcDF5.show

• 方式四

dbc(url: String, table: String, predicates: Array[String], connectionProperties: Properties): DataFrame
predicates: Condition in the WHERE clause for each partition.

import java.util.Properties

val readConnProp3 = new Properties()
readConnProp3.put("driver", "com.mysql.jdbc.Driver")
readConnProp3.put("user", "test")
readConnProp3.put("password", "123456")
readConnProp3.put("fetchsize", "2")

val arr = Array(
      (1, 50),
      (2, 60))

// 此处的条件，既可以分割数据用作并行度，也可以过滤数据
val predicates = arr.map {
      case (gender, age) =>
        s" gender = $gender " + s" AND age < $age "
}

val predicates1 =
      Array(
        "2017-05-01" -> "2017-05-20",
        "2017-06-01" -> "2017-06-05").map {
        case (start, end) =>
          s"cast(create_time as date) >= date '$start' " + s"AND cast(create_time as date) <= date '$end'"
}

val jdbcDF6 = spark.read.jdbc(
      "jdbc:mysql://ip:3306",
      "db.user_test",
      predicates,
      readConnProp3)

jdbcDF6.show

3.spark jdbc write MySQL

// For implicit conversions like converting RDDs to DataFrames
import spark.implicits._

val dataList: List[(Double, String, Double, Double, String, Double, Double, Double, Double)] = List(
      (0, "male", 37, 10, "no", 3, 18, 7, 4),
      (0, "female", 27, 4, "no", 4, 14, 6, 4),
      (0, "female", 32, 15, "yes", 1, 12, 1, 4),
      (0, "male", 57, 15, "yes", 5, 18, 6, 5),
      (0, "male", 22, 0.75, "no", 2, 17, 6, 3),
      (0, "female", 32, 1.5, "no", 2, 17, 5, 5),
      (0, "female", 22, 0.75, "no", 2, 12, 1, 3),
      (0, "male", 57, 15, "yes", 2, 14, 4, 4),
      (0, "female", 32, 15, "yes", 4, 16, 1, 2))

val colArray: Array[String] = Array("affairs", "gender", "age", "yearsmarried", "children", "religiousness", "education", "occupation", "rating")

val df7 = dataList.toDF(colArray: _*) //将colArray当做参数序列seq处理

df7.write.mode("overwrite").format("jdbc").options(
      Map(
        "driver" -> "com.mysql.jdbc.Driver",
        "url" -> "jdbc:mysql://ip:3306",
        "dbtable" -> "db.affairs",
        "user" -> "test",
        "password" -> "123456",
        "batchsize" -> "1000",
        "truncate" -> "true")).save()

七、用户自定义函数(后期研究)

八、Spark SQL性能

• 如果我们只需要在spark中读取某些特定的记录，标准的方法是读入整个数据集，然后在上面执行筛选条件。
• 在Spark SQL中，如果底层的数据存储支持只读取键值在一个范围内的记录，或是其他某些限制条件，Spark SQL就可以把查询语句中的筛选限制条件推到数据存储层，从而大大减少需要读取的数据。
• 性能调优选项(参考《Spark快速大数据分析》158页)
• 例：性能选项spark.sql.parquet.compression.codec

 val sparkSession = SparkSession.builder()
    .appName("metric_speed_sample")
    .config(conf)
    .config("spark.sql.parquet.compression.codec", "snappy")
    .getOrCreate()

• 例：性能选项spark.sql.codegen

在Scala中打开codegen选项
conf.set("spark.sql.codegen","true")

扫码关注公众号

• 后台回复“Spark学习资料”免费获取更多Spark学习教材及实战资料