Spark SQL花式查询

Spark SQL花式查询

---

一、Spark SQL花式查询

1、需求:针对personDF中的数据使用SQL和DSL两种方式进行各种查询

scala代码：

package com.jiang.sparksql

import org.apache.spark.SparkContext
import org.apache.spark.rdd.RDD
import org.apache.spark.sql.{DataFrame, SparkSession}

/*
 * @param null
*
*  @Description: Spark SQL花式查询
*/
object Demo05_Query {

  def main(args: Array[String]): Unit = {

    //TODO 0.准备环境
    val spark: SparkSession = SparkSession.builder().appName("sparksql").master("local[*]").getOrCreate()
    val sc: SparkContext = spark.sparkContext
    sc.setLogLevel("WARN")

    //TODO 1.加载数据
    val lines: RDD[String] = sc.textFile("data/input/person.txt")


    //TODO 2.处理数据
    val personRDD: RDD[Person] = lines.map(line => {
      val arr: Array[String] = line.split(" ")
      Person(arr(0).toInt, arr(1), arr(2).toInt)
    })

    // RDD --> DF
    import spark.implicits._
    val personDF: DataFrame = personRDD.toDF()

    // TODO ===============SQL===================
    //注册表名
    //personDF.registerTempTable("")//过期的
    //personDF.createOrReplaceGlobalTempView("")//创建全局的,夸SparkSession也可以用,但是生命周期太长!
    personDF.createOrReplaceTempView("t_person")//创建临时的,当前SparkSession也可以用
    // 1.查看name字段的数据
    spark.sql("select name from t_person").show()
    // 2.查看 name 和age字段数据
    spark.sql("select name,age from t_person").show()
    // 3.查询所有的name和age，并将age+1
    spark.sql("select name,age,age+1 from t_person").show()
    // 4.过滤age大于等于25的
    spark.sql("select name,age from t_person where age >= 25").show()
    // 5.统计年龄大于25的人数
    spark.sql("select count(1) from t_person where age > 25").show()
    // 6.按年龄进行分组并统计相同年龄的人数
    spark.sql("select age,count(*) from t_person group by age").show()
    // 7.查询姓名=张三的
    spark.sql("select * from t_person where name='zhangsan'").show()

    // TODO ===============DSL:面向对象的SQL===================
    // 1.查看name字段的数据
    //personDF.select(personDF.col("name"))
    personDF.select("name").show()
    // 2.查看 name 和age字段数据
    personDF.select("name","age").show()
    // 3.查询所有的name和age，并将age+1
    //personDF.select("name","age","age+1").show()
    // 错误的:cannot resolve '`age+1`' given input columns: [age, id, name];;
    personDF.select($"name",$"age",$"age" + 1).show()
    //注意'是把列名转为了Column列对象
//    personDF.select('name,'age,'age + 1).show()
    // 4.过滤age大于等于25的
    personDF.filter("age >= 25")
//    personDF.filter($"age"  >= 25)
//    personDF.filter('age  >= 25)
    // 5.统计年龄大于等于30的人数
    val count: Long = personDF.where($"age" >= 30).count()
    println("年龄大于30的人数: " + count)
    // 6.按年龄进行分组并统计相同年龄的人数
    personDF.groupBy("age").count().show()
    // 7.查询姓名=张三的
    personDF.filter("name = 'zhangsan'").show()

    //TODO 3.输出结果


    //TODO 3.关闭资源
    spark.stop()
  }

  case class Person(id:Int,name:String,age:Int)

}

2、Spark SQL实现Wordcount

使用SparkSQL的SQL和DSL两种方式实现Wordcount统计

scala代码：

package com.jiang.sparksql

import org.apache.spark.SparkContext
import org.apache.spark.rdd.RDD
import org.apache.spark.sql.{DataFrame, Dataset, SparkSession}

/*
 * @param null
*
*  @Description: 使用SparkSQL的SQL和DSL两种方式实现Wordcount统计
*/
object Sql_WordCount {

  def main(args: Array[String]): Unit = {

    //TODO 0.准备环境
    val spark: SparkSession = SparkSession.builder().appName("sparksql").master("local[*]").getOrCreate()
    val sc: SparkContext = spark.sparkContext
    sc.setLogLevel("WARN")

    //TODO 1.加载数据
//    val df: DataFrame = spark.read.text("data/input/wc.txt")
    val ds: Dataset[String] = spark.read.textFile("data/input/wc.txt")
//    val rdd: RDD[String] = sc.textFile("data/input/wc.txt")
    //TODO 2.处理数据
    import spark.implicits._
//    df.flatMap(_.split(" ")) // DataFrame没有泛型，不能直接使用split
    val words: Dataset[String] = ds.flatMap(_.split(" "))
    words.printSchema()
    words.show()


    //TODO 3.输出结果

    // TODO =========SQL============
    words.createOrReplaceTempView("t_words")
    val sql:String =
      """
        |select value,count(*) as counts
        |from t_words
        |group by value
        |order by counts desc
      """.stripMargin
    spark.sql(sql).show()

    // TODO =========DSL============
    words.groupBy('value)
      .count()
      .orderBy('count.desc)
      .show()

    //TODO 3.关闭资源
    spark.stop()
  }

}

3、电影评分

scala代码：

package com.jiang.sparksql

import org.apache.spark.SparkContext
import org.apache.spark.sql.{DataFrame, Dataset, SparkSession}

/**
 * Author itcast
 * Desc 演示SparkSQL-完成电影数据分析
 */
object Demo07_MovieDataAnalysis {
  def main(args: Array[String]): Unit = {
    //TODO 0.准备环境
    val spark: SparkSession = SparkSession.builder().appName("sparksql").master("local[*]")
      .config("spark.sql.shuffle.partitions", "4")//本次测试时将分区数设置小一点,实际开发中可以根据集群规模调整大小,默认200
      .getOrCreate()
    val sc: SparkContext = spark.sparkContext
    sc.setLogLevel("WARN")
    import spark.implicits._

    //TODO 1.加载数据
    val ds: Dataset[String] = spark.read.textFile("data/input/rating_100k.data")

    //TODO 2.处理数据
    val movieDF: DataFrame = ds.map(line => {
      val arr: Array[String] = line.split("\t")
      (arr(1), arr(2).toInt)
    }).toDF("movieId", "score")
    movieDF.printSchema()
    movieDF.show()
    /*
   +-------+-----+
  |movieId|score|
  +-------+-----+
  |    242|    3|
  |    302|    3|
     */

    //需求:统计评分次数>200的电影平均分Top10

    //TODO ======SQL
    //注册表
    movieDF.createOrReplaceTempView("t_movies")
    val sql: String =
      """
        |select movieId,avg(score) as avgscore,count(*) as counts
        |from t_movies
        |group by movieId
        |having counts > 200
        |order by avgscore desc
        |limit 10
        |""".stripMargin
    spark.sql(sql).show()
    /*
    +-------+------------------+------+
    |movieId|          avgscore|counts|
    +-------+------------------+------+
    |    318| 4.466442953020135|   298|
    |    483|  4.45679012345679|   243|
    |     64| 4.445229681978798|   283|
    |    603|4.3875598086124405|   209|
  .....
     */


    //TODO ======DSL
    import org.apache.spark.sql.functions._
    movieDF.groupBy('movieId)
      .agg(
        avg('score) as "avgscore",
        count("movieId") as "counts"
      ).filter('counts > 200)
      .orderBy('avgscore.desc)
      .limit(10)
      .show()

    //TODO 3.输出结果
    //TODO 4.关闭资源
    spark.stop()
  }
}