Spark应用-常用功能汇总

读写 Excel

使用的是 crealytics 的 spark-excel （CSDN 的国内镜像仓库）解决 Excel 的读写问题，可以直接读取为 DataFrame ，常用的表头、读取 sheet 以及读取位置等参数都可以配置，写入的话支持写入到单个 Excel 文件而不是目录，常用的参数有写入表头、写入位置以及写入模式都参数配置，还支持同一个Excel文件多次写入。

导入 Maven 依赖：

<dependency>
  <groupId>com.crealytics</groupId>
  <artifactId>spark-excel_2.12</artifactId>
  <version>0.13.7</version>
</dependency>

读取 Excel 文件

import org.apache.spark.sql._
import org.apache.spark.sql.types._

val spark: SparkSession = ???
val peopleSchema = StructType(Array(
    StructField("Name", StringType, nullable = false),
    StructField("Age", DoubleType, nullable = false),
    StructField("Occupation", StringType, nullable = false),
    StructField("Date of birth", StringType, nullable = false)))

val df = spark.read
    .format("com.crealytics.spark.excel")
    .option("dataAddress", "'My Sheet'!B3:C35") // 默认："A1"，必须是sheet和位置一起指定
    .option("header", "true") // 是否包含表头
    .option("treatEmptyValuesAsNulls", "false") // 默认: true，空单元格是否转为null
    .option("setErrorCellsToFallbackValues", "true") // 默认: false, 错误将被转为空，若为true则转为当前列类型的零值
    .option("usePlainNumberFormat", "false") // 默认: false, 如果为真，则不使用舍入和科学符号格式化单元格
    .option("inferSchema", "false") // 默认: false
    .option("addColorColumns", "true") // 默认: false
    .option("timestampFormat", "MM-dd-yyyy HH:mm:ss") // 默认: yyyy-mm-dd hh:mm:ss[.fffffffff]
    .option("maxRowsInMemory", 20) // 默认：None，如果设置将使用流式读取Excel数据（xls文件不支持）
    .option("excerptSize", 10) // 默认: 10，指定推断schema的行数
    .option("workbookPassword", "pass") // 默认：None
    .schema(peopleSchema) // 默认会自己推断，也可以传入自定义schema
    .load("Worktime.xlsx")

写入 Excel 文件

import org.apache.spark.sql._

val df: DataFrame = ???
df.write
  .format("com.crealytics.spark.excel")
  .option("dataAddress", "'My Sheet'!B3:C35") // 可以指定不同sheet多次写入，mode需设为append
  .option("header", "true") // 是否包含表头
  .option("dateFormat", "yy-mmm-d") // 默认: yy-m-d h:mm
  .option("timestampFormat", "mm-dd-yyyy hh:mm:ss") // 默认: yyyy-mm-dd hh:mm:ss.000
  .mode("append") // 默认: overwrite.
  .save("Worktime2.xlsx")

读写 MySQL

Spark 读 MySQL

Spark 可以使用 read 和 write 方法直接连接 MySQL，只需传入指定的 option 即可。

def con(): String = {
  val host: String = "127.0.0.1"
  val port: String = "3306"
  val database: String = "database"
  val user: String = "root"
  val password: String = "123456"
  
  s"jdbc:mysql://${host}:${port}/${database}?user=${user}&password=${password}&zeroDateTimeBehavior=CONVERT_TO_NULL&useSSL=true"
}

这里需要注意一个参数配置：zeroDateTimeBehavior=CONVERT_TO_NULL，Java 或 Scala 连接 MySQL 数据库，在操作值为0的 timestamp 类型时不能正确的处理，而是默认抛出一个异常，就是所见的：java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 3 to TIMESTAMP。官方文档给出的解决办法就是配置zeroDateTimeBehavior参数，该参数有三个属性值，分别是exception（抛出异常，默认）、convertToNull（转换为 null ）和 round （替换为最近的日期），常用的就是指定为convertToNull。

随后就是使用 JDBC 的方式查询并返回，这里使用 SparkSession 下的 read 方法：

/**
 * MySQL-根据SQL语言查询并返回DataFrame
 * @param spark-sparkSession连接
 * @param url-带用户名密码的数据库连接url
 * @param subTableQuery-上方数据库url对应的SQL查询语句
 */
def query(spark: SparkSession, url: String, subTableQuery: String): DataFrame = {
  spark.read.format("jdbc")
    .option("url", url)
    .option("useUnicode", "true")
    .option("characterEncoding", "UTF-8")
    .option("dbtable", s"(${subTableQuery}) as tmp")
    .load()
}

使用方法如下：

val spark: SparkSession = SparkSession
  .builder()
  .master("local[*]")
  .appName("Test")
  .getOrCreate()

query(spark, con(), "select * from test").show()

Spark 写 MySQL

Spark 将数据写入 MySQL 仍然使用读数据时的 url ，只需额外指定目标表和写入方式即可。

df.write
	.format("jdbc")
	.mode(SaveMode.Append)  // 写入模式，追加或覆盖
	.option("url", test())
	.option("dbtable", "test")  // 指定表，若不存在自动新建
	.save()

需要注意的有两点，第一点是写入模式，指定为SaveMode.Append时直接将数据追加到 MySQL 目标表内，但若是任务失败重试会导致数据重复，这点需要注意，指定为SaveMode.Overwrite会先删除表内数据再写入，任务失败重试并不会导致数据重复；第二点是dbtable参数指定目标表，Spark确实会在表不存在的时候自动创建，但数据格式并不会按我们所想的配置，例如 String 格式到 MySQL 中就是 text ，Int 格式到 MySQL 中就是 bigint ，会造成存储空间的浪费，所以还是先建表再写入比较好。

读写 ES

工作中也会遇到将数据从 ES 中读到 Spark 的场景，经过很长时间的尝试，数据是能很方便的取下来，问题在解析为 DataFrame 的时候遇到了一系列问题，重点在数据格式的转换，因为大多数方式会将数据读取为AnyRef的数据格式，在解析转为 Map 的时候十分不便，当然也可能是我技术能力有限，最后是选用了将读到的数据使用 GSON（com.google.gson）转换为 JSON 格式再取出指定的字段。

读 ES 无嵌套

若 ES 数据不存在嵌套，可以使用EsSparkSQL.esDF方法将数据直接转为 DataFrame ，首先是导入依赖：

<dependency>
  <groupId>org.elasticsearch</groupId>
  <artifactId>elasticsearch-spark-20_2.12</artifactId>
  <version>7.14.1</version>
</dependency>

其次是 ES 的配置信息，fields 是所需返回的字段，把所有需要的配置信息都可以塞进去，配置参数可以在ES官网查询：

  def con_es(fields: String = ""): Map[String, String] = {
    Map(
      "pushdown" -> "true",
      "es.nodes" -> "127.0.0.1",
      "es.port" -> "9200",
      "es.net.http.auth.user" -> "user",
      "es.net.http.auth.pass" -> "password",
      "es.mapping.date.rich" -> "false",  // 不解析ES中的日期类型，否则会报异常
      "es.field.read.empty.as.null"-> "no",  // 是否将空字段视为null，默认为yes，会报错
      "es.read.field.include" -> fields  // 没有嵌套的时候传入需要的字段
    )

接下来直接传入参数读取数据即可：

/**
 * ES-根据DSL查询语句直接返回DataFrame
 * @param spark sparkSession连接
 * @param con EsSparkSQL.esDF内需要传入的所有配置
 * @param index ES索引
 * @param query DSL查询语言
 */
def query_es(spark: SparkSession, con: Map[String, String], index: String, query: String): DataFrame = {
  EsSparkSQL.esDF(spark, index, query, con)  // 没有嵌套的时候使用，直接返回DataSet
}

下面是查询时的操作，直接传入 DSL 语句即可，_source属性在查询中是不起作用的：

val index: String = "test"
val query: String =
  """
    |{
    |  "query": {
    |    "term": {
    |      "age": {
    |        "value": 30
    |      }
    |    }
    |  }
    |}
    |""".stripMargin
EsSparkSQL.esDF(spark, index, query, con_es("id, name, age")).show(false)

读 ES 有嵌套

相对于未嵌套的场景嵌套时的场景就会麻烦很多，首先它不可以使用EsSparkSQL.esDF方法，取出嵌套内的字段时会报异常，按官网的说法指定es.read.field.as.array.include参数也取不出来。需要使用sparkContext.esJsonRDD方法将它读取为 JSON 字符串，随后再使用 GSON 解析并取出所需的字段，需要注意的是指定es.read.field.include参数时并不起作用，它会将符合条件的所有的字段都取出来，只能自己解析取出指定的字段。

假设 ES 的数据是这样的：

{
  "id": 1,
  "name": "王五",
  "product":[
    {
      "productName": "拯救者 Y9000x",
      "productUrl": "www.baidu.com"
    }
  ],
  "payment": 9999.00
}

使用SparkSession.sparkContext.esJsonRDD查询数据：

val index: String = "test"
val query: String =
  """
    |{
    |  "query": {
    |    "term": {
    |      "name": {
    |        "value": "王五"
    |      }
    |    }
    |  }
    |}
    |""".stripMargin

// 上方不变，使用SparkSession.sparkContext.esJsonRDD查询数据
spark.sparkContext.esJsonRDD(index, query, con)

使用 GSON 解析，这里要用样例类指定数据类型：

case class DataType(id: Int, name: String, productName: String, payment: Double)

def parse_data(spark: SparkSession): Dataset[DataType] = {
  import spark.implicits._
  val json = new JsonParser()
  val types: Array[DataType] = get_data(spark, tm).collect().map {
    kv => {  // 没找到好的解析方法，只能先取出来，否则会报未序列化异常
      val str: String = kv._2
      val jsonAll: JsonObject = json.parse(str).asInstanceOf[JsonObject].getAsJsonObject
      
      DataType(
        jsonAll.get("id").getAsInt,
        jsonAll.get("name").getAsString,
        jsonAll.get("product").getAsJsonArray.get(0).getAsJsonObject.get("productName").getAsString,
        jsonAll.get("payment").getAsDouble
      )
    }
  }
  spark.sparkContext.makeRDD(types).toDS
}

大概流程就是先将符合条件的所有数据collect出来，直接使用 GSON 解析会报未序列化异常，随后使用 map 对每一行数据进行解析再取出目标字段放入样例类中形成带属性的 RDD ，最后再将 RDD 转为 DataSet ，注意导入import spark.implicits._隐式转换。里面的 product 数组固定只有一个产品，若有多个并且不确定时，建议直接将 product 存储为 List ，转为 DataSet 后再做 flatten 处理，不过还没有实践过。

对于 ES 的写入因为接触不到所以未做研究，使用df.write.format("org.elasticsearch.spark.sql")...应该可以满足批量写入的需求。