JSON数据源

JSON数据源

Spark SQL可以自动推断JSON文件的元数据，并且加载其数据，创建一个DataFrame。可以使用SQLContext.read.json()方法，针对一个元素类型为String的RDD，或者是一个JSON文件。

综合性复杂案例：查询成绩为80分以上的学生的基本信息与成绩信息

import java.util.Properties

import org.apache.spark.sql.functions._
import org.apache.spark.sql.types._
import org.apache.spark.sql.{Row, SQLContext, SaveMode}
import org.apache.spark.{SparkConf, SparkContext}

/**
  * Created by cuiyufei on 2018/3/6.
  * jdbc实战
  */
object sparkStudy {
  /**
    * 将学生基本信息和学生成绩信息合并，并找出优秀的学生（成绩大于85）
   */
  def main(args: Array[String]) {
    /**
      * 读取json数据源
      */
    val conf = new SparkConf().setAppName("JSON").setMaster("local")
    val sc = new SparkContext(conf)
    val sqlContext = new SQLContext(sc)
    import sqlContext.implicits._
    val studentScoresDF = sqlContext.read.json("f:/spark/students.json")
    studentScoresDF.createOrReplaceTempView("studentScore")
    val goodStudentScoresDF = sqlContext.sql("select name,score from studentScore where score>=80")
    val goodStudentNames = goodStudentScoresDF.rdd.map(row => row(0)).collect()

    // 创建学生基本信息DataFrame
    val studentInfoJSONs = Array("{\"name\":\"Leo\", \"age\":18}",
      "{\"name\":\"Marry\", \"age\":17}",
      "{\"name\":\"Jack\", \"age\":19}")
    val studentInfoJSONsRDD = sc.parallelize(studentInfoJSONs,3)
    val studentsInfoDF = sqlContext.read.json(studentInfoJSONsRDD)
    studentsInfoDF.createOrReplaceTempView("studentInfo")

    var sql = "select name,age from studentInfo where name in ("
    for(i <- 0 until goodStudentNames.length) {
      sql += "'" + goodStudentNames(i) + "'"
      if(i < goodStudentNames.length - 1) {
        sql += ","
      }
    }
    sql += ")"

    val goodStudentInfosDF = sqlContext.sql(sql)
    // 将分数大于80分的学生的成绩信息与基本信息进行join
    goodStudentInfosDF.join(goodStudentScoresDF,"name")
    val goodStudentsRDD =
      goodStudentScoresDF.rdd.map { row => (row.getAs[String]("name"), row.getAs[Long]("score")) }
        .join(goodStudentInfosDF.rdd.map { row => (row.getAs[String]("name"), row.getAs[Long]("age")) })

    // 将rdd转换为dataframe
    val goodStudentRowsRDD = goodStudentsRDD.map(
      info => Row(info._1, info._2._1.toInt, info._2._2.toInt))

    val structType = StructType(Array(
      StructField("name", StringType, true),
      StructField("score", IntegerType, true),
      StructField("age", IntegerType, true)))

    val goodStudentsDF = sqlContext.createDataFrame(goodStudentRowsRDD, structType)

    // 将dataframe中的数据保存到json中
    goodStudentsDF.write.format("json").save("f:/spark/good-students-scala")


  }

}