Spark SQL学习

Spark SQL学习

1.SparkSQL概述

SparkSQL是能够操作结构化数据的spark中的module模块。

1.1.特点

1.1.1.Integrated（可集成的）

SparkSQL可以和Spark的程序混合在一起使用。

1.1.2.Uniform Data Access（统一的数据访问方式）

Connect to any data source the same way.
DataFrames and SQL provide a common way to access a variety of data sources, including Hive, Avro, Parquet, ORC, JSON, and JDBC. You can even join data across these sources.
SparkSQL能够对各种各样的数据提供一个统一的数据访问方式。

1.1.3.Hive Integration(可集成Hive)

Run SQL or HiveQL queries on existing warehouses.
Spark SQL supports the HiveQL syntax as well as Hive SerDes and UDFs, allowing you to access existing Hive warehouses.

1.1.4.Standard Connectivity（标准连接方式）

Connect through JDBC or ODBC.
A server mode provides industry standard JDBC and ODBC connectivity for business intelligence tools.
能够提供标准的jdbc和odbc作为客户端的连接方式，类似于mysql-server/hiveserver2.
一句话总结：
SparkSQL就是构建在SparkCore基础之上的能够使用SQL去操作结构化数据的Spark的module模块。

2.SparkSQL初体验

Spark-shell来体验sparksql

2.1.SparkSession

统一的编程入口

2.2.基本体验操作

3.编程模型

SparkSQL的主要编程模型，就是SQL、DataFrame以及Dataset。
我们把RDD称为Spark的第一代编程模型，DataFrame称为第二代编程模型，Dataset称为第三代编程模型。

3.1.DataFrame

DataFrame相比较于RDD，就比RDD多了一行schema描述信息，这个schema可以理解为表头等元数据信息。
官方解释：

一句话总结：DataFrame就是结构化数据库中的一张二维表。

3.2.Dataset

Dataset是RDD和DataFrame的集大成者，集成二者优点(RDD中的强类型推断，强大的lambda表达式和DataFrame中的执行引擎的优化的能力)
官方解释：

一句话：Dataset=RDD+DataFrame
总结：相比 DataFrame，Dataset 提供了编译时类型检查，对于分布式程序来讲，提交一次作业太费劲了（要编译、打包、上传、运行），到提交到集群运行时才发现错误，影响开发进度，这也是引入 Dataset 的一个重要原因。

4.SparkSQL编程

4.1.加载依赖

<!-- sparksql-->
<dependency>
  <groupId>org.apache.spark</groupId>
  <artifactId>spark-sql_2.11</artifactId>
  <version>${spark.version}</version>
</dependency>
<!--sparksql-hive-->
<dependency>
  <groupId>org.apache.spark</groupId>
  <artifactId>spark-hive_2.11</artifactId>
  <version>${spark.version}</version>
</dependency>

4.2.程序入口——SparkSession

Spark2.x以前，sparkSQL的程序入口——SQLContext，如果要想操作Hive，就使用HiveContext，Spark2.x之后，SparkSQL的入口就统一切换到SparkSession中来了。

4.3.DataFrame基本操作

4.4.SQL基本操作

5.RDD/list和DataFrame的转换

5.1.使用反射方式进行转化

5.1.1.List-2-dataframe

5.1.2.Rdd-2-dataframe

5.2.使用动态编程的方式进行转化

5.2.1.List-2-dataframe

5.2.2.Rdd-2-dataframe

5.3.Dataframe-2-rdd

6.List/RDD和Dataset的转化

6.1.1.List-2-dataset

修改正确之后：

6.1.2.Rdd-2-dataset

6.1.3.List/RDD-2-dataset

但是必须要引入对应的spark的隐士转化

6.1.4.其它转化

7.SparkSQL数据源和落地方式

7.1.各种各样的数据源

使用默认的方式加载数据

默认加载的文件格式为parquet===》

读取指定格式的文件

7.2.各种各样的落地方式

===>jdbc

8.SparkSQL和Hive的整合

/*
* SparkSQL和Hive的整合操作 
* 需求：
*     在hive数据库db-1808中有两张表
*         teacher_info
*             tname, height
*             zhangsan,175
*         teacher_basic
*             tname,age,married,children
*             zhangsan,23,false,0
*     需要完成的操作是：
*         1、使用sparksql创建数据db_1808
*         2、在db_1808中创建两张表teacher_info和teacher_basic，
*             并加载对应的数据
*         3、对这两张表进行关联查询，将最终结果存储到hive中的上述第一步创建的数据库中，表名为teacher
*          create table if not exists teacher as
*          select
*             b.tname,
*             b.age,
*             b.married,
*             b.children,
*             i.height
*          from teacher_info i
*          left join teacher_basic b on i.tname = b.tname;
*
*          insert <into|overwrite> table teacher
*          select
*             b.tname,
*             b.age,
*             b.married,
*             b.children,
*             i.height
*          from teacher_info i
*          left join teacher_basic b on i.tname = b.tname;
* /

第一步：构建Spark和Hive的整合，就需要在SparkSession中添加支持Hive.

第二步：便可展开hive的操作，就想在hive终端执行操作一样。

// 1、使用sparksql创建数据db_1808
spark.sql("create database `db_1808`")
spark.sql("use `db_1808`")
// 2 在db_1808中创建两张表teacher_info和teacher_basic，并加载对应的数据
spark.sql(
    """
      |create table if not exists `db_1808`.`teacher_info` (
      |  tname string,
      |  height double
      |) row format delimited
      |fields terminated by ','
    """.stripMargin)
spark.sql("load data inpath 'hdfs://ns1/data/teacher_info.txt' into table `db_1808`.`teacher_info`")
spark.sql(
    """
      |create table if not exists `db_1808`.`teacher_basic` (
      |  tname string,
      |  age int,
      |  married boolean,
      |  children int
      |) row format delimited
      |fields terminated by ','
    """.stripMargin)
spark.sql("load data inpath 'hdfs://ns1/data/teacher_basic.txt' into table `db_1808`.`teacher_basic`")
//3 对这两张表进行关联查询，将最终结果存储到hive中的上述第一步创建的数据库中，表名为teacher
val sql =
    """
      |select
      |   b.tname,
      |   b.age,
      |   b.married,
      |   b.children,
      |   i.height
      |from teacher_info i
      |left join teacher_basic b on i.tname = b.tname
    """.stripMargin
val retDF = spark.sql(sql)
retDF.write.mode(SaveMode.Overwrite).saveAsTable("`db_1808`.`teacher`")

//最后在spark安装实例中添加hive的元数据信息：

1、将hive/conf目录下面的hive-site.xml配置文件拷贝到spark的conf目录下面
2、将mysql的依赖jar拷贝到spark的jars目录下面
Spark-submit.sh

9.SparkSQL之函数操作

什么是函数？
函数就是为了完成某一个比较复杂，或者常用功能，对一些列代码的封装。

9.1.常用函数

9.1.1.按照功能做分类

数学函数：sin(),abs(),cos(),tan()
统计函数：sum(),avg(),count(),max()
日期函数:date_sub(date1, date2),year(),hour(),
字符串函数:length,substr
开窗函数：row_number() over(),sum() over()

9.1.2.按照参数分类

• UDF（User Definition Function）
  用户自定义函数
  一路输入，一路输出
  sin,year
• UDAF(User Definition Aggregation Function)
  用户自定义聚合函数
  多路输入，一路输出
  max,sum,…,
• UDTF(User Definition Table Function)
  用户自定义表函数
  一路输入，多路输出
  explode

这里面的“路”，指的就是一行中的某一列。

9.1.3.练习

数据源：person

Us_population

加载数据的工具类：

统计函数操作：

日期操作：

val pDF: DataFrame = loadTable(spark, "person")
pDF.createOrReplaceTempView("person")
pDF.printSchema()
pDF.show()
/*
    1、统计每年出生的人数
    2、计算李涛和钟宁静相差多少天
    3、计算129天之后的钟宁静同学芳龄几何
 */
var sql =
    """
      |select
      |     year(birthday) year,
      |     count(1) num
      |from person
      |group by year(birthday)
    """.stripMargin
spark.sql(sql).show()

sql =
    """
      |SELECT
      |   DATEDIFF(p1.birthday, p2.birthday) birthday_diff
      |FROM person p1 CROSS JOIN person p2
      |WHERE p1.name='李涛'
      |AND p2.name='钟宁静'
    """.stripMargin
spark.sql(sql).show()

sql =
    """
      |select
      |  datediff(b1, b2)
      |from (
          |select
          |(SELECT
          |   birthday
          |from person where name ='李涛') b1,
          |(SELECT
          |   birthday b1
          |from person where name ='钟宁静') b2
      |) t
    """.stripMargin
spark.sql(sql).show()

/**
  * 计算129天之后的钟宁静同学芳龄几何
  * 年龄=当前的年份-出生的年份
  *     这里的当前的年份就是129天之后的年份
  *         date_add(current_date(), 129) 129天之后的日期
  *         year(date_add(current_date(), 129)) 129天之后的年份
  *     出生的年份就是birthday中的year
  */
sql =
    """
      |select
      | name,
      | birthday,
      | date_add(current_date(), 129) date_129_later,
      | year(date_add(current_date(), 129)) year,
      | (year(date_add(current_date(), 129)) - year(birthday)) age
      |from person
      |where name ='钟宁静'
    """.stripMargin
spark.sql(sql).show()

9.2.自定义函数

当系统函数，满足不了业务需要的时候，就需要自定义函数，完成自定义函数，有如下三个步骤。
1、实现某个类，复写其中的方法
2、注册到环境中
3、直接使用

9.2.1.自定义UDF

/*
 *        自己模拟字符串长度的函数length
 *        1、编写一个方法即可
 *        2、注册
 *        3、使用
 * /
def main(args: Array[String]): Unit = {
    Logger.getLogger("org.apache.hadoop").setLevel(Level.WARN)
Logger.getLogger("org.apache.spark").setLevel(Level.WARN)  Logger.getLogger("org.spark-project").setLevel(Level.WARN)
    val spark = SparkSession.builder()
        .appName("_02SparkSQLUDFOps")
        .master("local[*]")
        .getOrCreate()
    // 2、注册(注册之后的函数名，函数的引用)
    spark.udf.register[Int, String]("strLen", str => myStrLen(str))
    val jdbcDF = loadTable(spark, "us_population")
    jdbcDF.createOrReplaceTempView("us_population")
    jdbcDF.printSchema()
    jdbcDF.show()
    //3 使用
    val sql =
        """
          |select
          | city,
          | length(city) city_len,
          | strLen(city) city_len
          |from us_population
        """.stripMargin
    spark.sql(sql).show()

    spark.stop()
}
// 1、编写一个方法即可
private def myStrLen(str:String) = str.length

private def loadTable(spark: SparkSession, table:String) = {
    val url = "jdbc:mysql://localhost:3306/test"
    val properties = new Properties()
    properties.put("user", "root")
    properties.put("password", "sorry")
    val jdbcDF = spark.read.jdbc(url, table, properties)
    jdbcDF
}

9.2.2.自定义UDAF

1、实现某个类，复写其中的方法

/**
  * 学习这个UserDefinedAggregateFunction类，大家一定要参考在SparkCore中学习过的
  * combineByKey和aggregateByKey
  */
class MySum extends UserDefinedAggregateFunction {
    //该udaf的输入参数的schema类型
    override def inputSchema: StructType = {
        StructType(
            List(StructField("population", DataTypes.IntegerType, false))
        )
    }

    /*
        当前UDAF返回值的数据类型
     */
    override def dataType: DataType = DataTypes.IntegerType

     /*
      * 聚合过程中的缓冲区域的数据类型Schema
      */
    override def bufferSchema: StructType = {
        StructType(
            List(StructField("population", DataTypes.IntegerType, false))
        )
    }
    //输入输出确定性判断，比如，输入输出类型是一致或者确定，返回为true
    override def deterministic: Boolean = true
    /*
        初始化的操作
        相当于aggregateByKey中的createCombiner方法
     */
    override def initialize(buffer: MutableAggregationBuffer): Unit = {
        buffer.update(0, 0)
    }

    /**
      *  分区内的合并操作
      * @param buffer   initialize构建的临时缓冲区，用于记录合并结果
      * @param input    就是每次新输入的要聚合的值
      * partition-1
      * var sum1 = 0 //initialize
      * for(i <- 0 until 10) { //i 就是每次新输入的要聚合的值
      *     sum1 += i //update
      * }
      *
      *partition-2
      * var sum2 = 0 //initialize
      * for(i <- 0 until 10) {
      *     sum2 = sum2 + i //update
      * }
      */
    override def update(buffer: MutableAggregationBuffer, input: Row): Unit = {
        val oldValue= buffer.getInt(0)
        val newValue = input.getInt(0)
        val mergeValue = oldValue + newValue
        buffer.update(0, mergeValue)
    }

    /**
      * 分区间的合并操作
      * @param buffer1 分区一中聚合得到的临时结果1
      * @param buffer2 分区二中聚合得到的临时结果2
      * partition-1
      * var sum1 = 0 //initialize
      * for(i <- 0 until 10) { //i 就是每次新输入的要聚合的值
      *     sum1 += i //update
      * }
      *
      *partition-2
      * var sum2 = 0 //initialize
      * for(i <- 0 until 10) {
      *     sum2 = sum2 + i //update
      * }
      *
      * sum1+sum2
      */
    override def merge(buffer1: MutableAggregationBuffer, buffer2: Row): Unit = {
        val old = buffer1.getInt(0)
        val newV = buffer2.getInt(0)
        val totalV = old + newV
        buffer1.update(0, totalV)
    }

    //获取当前聚合操作的最终结果值
    override def evaluate(buffer: Row): Any = buffer.getInt(0)
}

2、注册到环境中
3、直接使用

object _03SparkSQLUDAFOps {
    def main(args: Array[String]): Unit = {
        Logger.getLogger("org.apache.hadoop").setLevel(Level.WARN)
        Logger.getLogger("org.apache.spark").setLevel(Level.WARN)
        Logger.getLogger("org.spark-project").setLevel(Level.WARN)
        val spark = SparkSession.builder()
            .appName("_03SparkSQLUDAFOps")
            .master("local[*]")
            .getOrCreate()
        //2 注册
        spark.udf.register("mySum", new MySum)
        val jdbcDF = loadTable(spark, "us_population")
        jdbcDF.createOrReplaceTempView("us_population")
        jdbcDF.printSchema()
        jdbcDF.show()
        //3 使用
        val sql =
            """
              |select
              | state,
              | sum(population) total_population,
              | mySum(population) m_total_population
              |from us_population
              |group by state
            """.stripMargin
        spark.sql(sql).show()

        spark.stop()
    }

    private def loadTable(spark: SparkSession, table:String) = {
        val url = "jdbc:mysql://localhost:3306/test"
        val properties = new Properties()
        properties.put("user", "root")
        properties.put("password", "sorry")
        val jdbcDF = spark.read.jdbc(url, table, properties)
        jdbcDF
    }
}

9.3.特殊函数

开窗函数row_number()\sum() over()\max() over(),好处，直接在字段上进行统计，这样就不需要在表后面再用group by的。
Row_number

**
  *  http://www.bejson.com/otherformat/sql/
  * SparkSQL之开窗函数操作
  * row_number() over()
  *     分组topn
  *     course  name    score
  *     chinese ls 91
        english ww 56
        chinese zs 90
        chinese zl 76
        english zq 88
        chinese wb 95
        chinese sj 74
        english ts 87
        english ys 67
        english mz 77
        chinese yj 98
        english gk 96
    求取各科目成绩的前三名

  * sum() over()
  */
object _04SparkSQLWindowOps {
    def main(args: Array[String]): Unit = {
        Logger.getLogger("org.apache.hadoop").setLevel(Level.WARN)
        Logger.getLogger("org.apache.spark").setLevel(Level.WARN)
        Logger.getLogger("org.spark-project").setLevel(Level.WARN)
        val spark = SparkSession.builder()
            .appName("_04SparkSQLWindowOps")
            .master("local[*]")
            .getOrCreate()

        val linesDF = spark.read.text("file:///E:/data/spark/topn.txt")
        linesDF.printSchema()
        linesDF.show()
        import spark.implicits._
        val stuDS:Dataset[Student] = linesDF.map(row => {
            val line = row.getString(0)
            val fields = line.split("\\s+")
            val course = fields(0)
            val name = fields(1)
            val score = fields(2).trim.toInt
            Student(course, name, score)
        })

        stuDS.show()
        stuDS.createOrReplaceTempView("student")//注册成为临时表
        var sql =
            """
              |select
              |    course,
              |    name,
              |    score,
              |    row_number() over(partition by course order by score desc) rank
              |from student
              |having rank < 4
            """.stripMargin
        spark.sql(sql).show()

        //建议大家使用子查询代替having操作
        println("建议大家使用子查询代替having操作")
        sql =
            """
              |select
              | tmp.*
              |from(
              |select
              |    course,
              |    name,
              |    score,
              |    row_number() over(partition by course order by score desc) rank
              |from student) tmp
              |where tmp.rank < 4
            """.stripMargin
        spark.sql(sql).show()
        spark.stop()
    }
}
case class Student(course:String, name:String, score:Int)

Sum() over()
/**
  *  http://www.bejson.com/otherformat/sql/
  * SparkSQL之开窗函数操作
  * row_number() over()
  * sum()/max()/min/avg over()
  *     可以实现累计求和的过程
  */
object _05SparkSQLWindowOps {
    def main(args: Array[String]): Unit = {
        Logger.getLogger("org.apache.hadoop").setLevel(Level.WARN)
        Logger.getLogger("org.apache.spark").setLevel(Level.WARN)
        Logger.getLogger("org.spark-project").setLevel(Level.WARN)
        val spark = SparkSession.builder()
            .appName("_05SparkSQLWindowOps")
            .master("local[*]")
            .getOrCreate()

        val jsonDF = spark.read.json("file:///E:/data/spark/sql/product_info.json")
        jsonDF.printSchema()
        jsonDF.show()
        jsonDF.createOrReplaceTempView("product")
        val sql =
            """
              |select
              |   product_code,
              |   event_date,
              |   duration,
              |   sum(duration) over(partition by product_code order by event_date) `group_sum`,
              |   sum(duration) over(order by event_date) `sum`
              |from product
            """.stripMargin
        spark.sql(sql).show()
        spark.stop()
    }
}

10.SparkSQL和SparkCore整合案例

/**
  * SparkSQL+SparkCore的整合案例
  *  基础数据：
  *     date        name    keyword province  client  searchType
  *     2018-11-13 tom    china  beijing    pc web
  * 2018-11-13 tom    news   tianjing   pc web
  *  需求：
  *     统计每天用户检索关键字的Top3，要求：每个用户在每一天对同一个关键字的多次检索，只能统计一次。
  *
  *  分析思路：
  *     根据题意，其实就是分组求topN，这里的分组，按照什么分组？按照date进行分组，每一天有若干个
  *     单词的统计，如果统计关键字？
  *      为了避免关键字跨天被重复统计，那么就需要使用日期+关键字作为符合key，来求出每一天的关键字出现的次数。
  *      所以在统计过程中，要对同一个用户检索的关键字进行去重
  *      1、将该用户在某一天的所有的关键字都拉取过来，然后去重，
  *      2、以date+keyword+user作为rdd内容(distinct)，第二步，数据转化为<date+keyword, 1> 统计
  *      便可计算出某一天的某一个关键字的检索量。
  *
  *      检索出每一天每个关键字的量，之后便分组topN(row_number搞定即可)
  *
  */
object _06SparkSQLAndCoreTest {
    def main(args: Array[String]): Unit = {
            Logger.getLogger("org.apache.hadoop").setLevel(Level.WARN)
            Logger.getLogger("org.apache.spark").setLevel(Level.WARN)
            Logger.getLogger("org.spark-project").setLevel(Level.WARN)
            val spark = SparkSession.builder()
                .appName("_06SparkSQLAndCoreTest")
                .master("local[*]")
                .getOrCreate()

            import spark.implicits._
            val lines = spark.sparkContext.textFile("file:///E:/data/spark/sql/dailykey.txt")

            // date        name    keyword province  client  searchType
//            *     2018-11-13 tom    china  beijing    pc web
            //date+keyword+user
            val baseRDD:RDD[String] = lines.map(line => {
                    val fields = line.split("\\s+")
                    val date = fields(0)
                    val user = fields(1)
                    val keyword = fields(2)
                    s"${date}|${keyword}|${user}"
            })
            println("========>原始数据经过转化之后的内容")
            baseRDD.foreach(println)
            println("=========>去重之后的结果=====================")
            val distinctRDD:RDD[String] = baseRDD.distinct()
            distinctRDD.foreach(println)
            println("=========>统计每天每个关键字被检索的次数=====================")
            val keyCount:RDD[(String, Int)] = distinctRDD.map(fhkey => {
                    val key = fhkey.substring(0, fhkey.lastIndexOf("|"))
                    (key, 1)
            }).reduceByKey(_ + _)
            keyCount.foreach(println)
            println("=========>每天关键字检索Top3=====================")
            val finalRDD = keyCount.map { case (key, count) => {
                    val fields = key.split("\\|")
                    val date = fields(0)
                    val keyword = fields(1)
                    MyRow(date, keyword, count)
            }}

            val ds:Dataset[MyRow] = finalRDD.toDS()
            ds.createOrReplaceTempView("daily_keyword_tmp")
            ds.printSchema()
            val sql =
                """
                  |select
                  | tmp.*
                  |from (
                  |select
                  |  `date`,
                  |  keyword,
                  |  `count`,
                  |  row_number() over(partition by `date` order by `count` desc) rank
                  |from daily_keyword_tmp
                  |) tmp
                  |where tmp.rank < 4
                """.stripMargin


            spark.sql(sql).show()
            spark.stop()
    }
}
case class MyRow(date:String, keyword:String, count:Int)

11.SparkSQL以及wordcount的数据倾斜处理

11.1.SparkSQL编写wordcount

/**
  * 使用SparkSQL来统计wordcount
  */
object _07SparkSQLWordCountOps {
    def main(args: Array[String]): Unit = {
        Logger.getLogger("org.apache.hadoop").setLevel(Level.WARN)
        Logger.getLogger("org.apache.spark").setLevel(Level.WARN)
        Logger.getLogger("org.spark-project").setLevel(Level.WARN)
        val spark = SparkSession.builder()
            .appName("_07SparkSQLWordCountOps")
            .master("local[*]")
            .getOrCreate()
        val linesDF = spark.read.text("file:///E:/data/spark/core/hello.txt").toDF("line")
        linesDF.createOrReplaceTempView("test")
        linesDF.printSchema()
        linesDF.show()

        //求wordcount
        //step 1、将每一行的数据进行拆分
        println(">>>>step 1、将每一行的数据进行拆分")
        val flatMapSQL =
            """
              |select
              | split(line, '\\s+') words
              |from test
            """.stripMargin
        spark.sql(flatMapSQL).show()
        //step 2、强一个数组转化为为多行，使用explode函数
        println(">>>>step 2、强一个数组转化为为多行，使用explode函数")
        val explodeSQL =
            """
              |select
              | explode(split(line, '\\s+')) word
              |from test
            """.stripMargin
        spark.sql(explodeSQL).show()
        //step 3、分组统计
        val groupSQL =
            """
              |select
              |  tmp.word,
              |  count(tmp.word) as countz
              |from (
              | select
              |     explode(split(line, '\\s+')) word
              | from test
              |) tmp
              |group by tmp.word
              |order by countz desc
            """.stripMargin
        spark.sql(groupSQL).show()
        spark.stop()
    }
}

11.2.解决groupBy产生的数据倾斜

/**
  * 使用SparkSQL来解决group by操作的数据倾斜
  * 在SparkCore中，使用两阶段聚合来解决dataskew，
  *     局部聚合+全局聚合
  *     对key拆分打散(添加随机前缀)，在此基础之上做group by的统计---->局部聚合
  *     在局部聚合的基础之上，去掉对应的随机前缀，再做group by的统计--->全局聚合
  *  双重group-by
  */
object _08SparkSQLWordCountDataSkewOps {
    def main(args: Array[String]): Unit = {
        Logger.getLogger("org.apache.hadoop").setLevel(Level.WARN)
        Logger.getLogger("org.apache.spark").setLevel(Level.WARN)
        Logger.getLogger("org.spark-project").setLevel(Level.WARN)
        val spark = SparkSession.builder()
            .appName("_08SparkSQLWordCountDataSkewOps")
            .master("local[*]")
            .getOrCreate()
        val linesDF = spark.read.text("file:///E:/data/spark/core/hello.txt").toDF("line")
        linesDF.createOrReplaceTempView("test")
        linesDF.printSchema()
        linesDF.show()

        //求wordcount
        //step 1、将每一行的数据进行拆分
        println(">>>>step 1、将每一行的数据进行拆分")
        val flatMapSQL =
            """
              |select
              | split(line, '\\s+') words
              |from test
            """.stripMargin
        spark.sql(flatMapSQL).show()
        //step 2、强一个数组转化为为多行，使用explode函数
        println(">>>>step 2、强一个数组转化为为多行，使用explode函数")
        val explodeSQL =
            """
              |select
              |   explode(split(line, '\\s+')) word
              |from test
            """.stripMargin
        spark.sql(explodeSQL).show()
        //对拆分出来的每一个单词添加随机前缀，添加4以内的随机前缀
        println("step 3 对拆分出来的每一个单词添加随机前缀，添加4以内的随机前缀")
        val prefixSQL =
            """
              |select
              |  concat_ws("_", cast(floor(rand(10) * 2) as string), t1.word) as prefix_word
              |from (
              |  select
              |    explode(split(line, '\\s+')) word
              |  from test
              |) t1
            """.stripMargin
        spark.sql(prefixSQL).show()
        println(">>>>>step 4在添加好前缀之后做局部聚合")
        val partAggrSQL =
            """
              |select
              | t2.prefix_word,
              | count(t2.prefix_word) as prefix_count
              |from (
              | select
              |  concat_ws("_", cast(floor(rand(10) * 2) as string), t1.word) as prefix_word
              | from (
              |  select
              |    explode(split(line, '\\s+')) word
              |  from test
              | ) t1
              |) t2
              |group by t2.prefix_word
            """.stripMargin
        spark.sql(partAggrSQL).show()
        println(">>>在局部聚合的基础至少去除前缀")
        /*
            去除前缀的两种方式：
                1、select substr("1_baidu", instr("1_baidu", "_") + 1);索引法
                2、select split("1_baidu", "_")[1]; 切割法
         */
        val removePrefixSQL =
            """
              |select
              | t2.prefix_word,
              | substr(t2.prefix_word, instr(t2.prefix_word, "_") + 1) index_m,
              | split(t2.prefix_word, "_")[1] split_m,
              | count(t2.prefix_word) as prefix_count
              |from (
              | select
              |  concat_ws("_", cast(floor(rand(10) * 2) as string), t1.word) as prefix_word
              | from (
              |  select
              |    explode(split(line, '\\s+')) word
              |  from test
              | ) t1
              |) t2
              |group by t2.prefix_word
            """.stripMargin
        spark.sql(removePrefixSQL).show()
        println(">>>>全局聚合统计")
        val fullAggrSQL =
            """
              |select
              |  t3.index_m,
              |  sum(t3.prefix_count) as countz
              |from (
              |select
              | t2.prefix_word,
              | substr(t2.prefix_word, instr(t2.prefix_word, "_") + 1) index_m,
              | split(t2.prefix_word, "_")[1] split_m,
              | count(t2.prefix_word) as prefix_count
              |from (
              | select
              |  concat_ws("_", cast(floor(rand(10) * 2) as string), t1.word) as prefix_word
              | from (
              |  select
              |    explode(split(line, '\\s+')) word
              |  from test
              | ) t1
              |) t2
              |group by t2.prefix_word)
              |t3
              |group by t3.index_m
            """.stripMargin

        spark.sql(fullAggrSQL).show()
        spark.stop()
    }

    private def addPrefix(str:String) = {
        val random = new Random()
        random.nextInt(2) + "_" + str
    }

    private def removePrefix(str:String) = {
        str.substring(str.indexOf("_") + 1)
    }
}