[Spark]三、SparkSQL

墨尔本、晴

已于 2024-09-11 22:33:17 修改

阅读量797

点赞数 11

分类专栏：大数据 Spark 文章标签： spark 大数据分布式

于 2024-06-19 01:21:22 首次发布

本文链接：https://blog.csdn.net/weixin_44428807/article/details/139787187

版权

大数据同时被 2 个专栏收录

20 篇文章 0 订阅

订阅专栏

Spark

5 篇文章 0 订阅

订阅专栏

G:\Bigdata\4.spark\2024最新版Spark视频教程

第1章 Spark SQL概述

1.1 什么是Spark SQL

Spark SQL是用于结构化数据处理的Spark模块。与基本的Spark RDD API不同，Spark SQL提供的接口为Spark提供了有关数据结构和正在执行的计算的更多信息。在内部，Spark SQL使用这些额外的信息来执行额外的优化。与Spark SQL交互的方式有多种，包括SQL和Dataset API。计算结果时，使用相同的执行引擎，与您用于表达计算的API/语言无关。

SparkSQL演变的过程：

SparkSQL学习的重点：不是SQL，SQL是跟业务强相关的
   数据模型的封装；
   数据环境的封装；
   SQL不是万能的，它实现不了或不好实现的功能，是我们需要重点掌握的；
   数据源的变化。

1.2 为什么要有Spark SQL

1.3 SparkSQL的发展

1）发展历史

RDD（Spark1.0）=> Dataframe（Spark1.3）=> Dataset（Spark1.6）

如果同样的数据都给到这三个数据结构，他们分别计算之后，都会给出相同的结果。不同的是他们的执行效率和执行方式。在现在的版本中，dataSet性能最好，已经成为了唯一使用的接口。其中Dataframe已经在底层被看做是特殊泛型的DataSet<Row>。

2）三者的共性

（1）RDD、DataFrame、DataSet全都是Spark平台下的分布式弹性数据集，为处理超大型数据提供便利。

（2）三者都有惰性机制，在进行创建、转换，如map方法时，不会立即执行，只有在遇到Action行动算子如foreach时，三者才会开始遍历运算。

（3）三者有许多共同的函数，如filter，排序等。

（4）三者都会根据Spark的内存情况自动缓存运算。

（5）三者都有分区的概念。

1.4 Spark SQL的特点

1）易整合

无缝的整合了SQL查询和Spark编程。

2）统一的数据访问方式

使用相同的方式连接不同的数据源。

3）兼容Hive

在已有的仓库上直接运行SQL或者HQL。

4）标准的数据连接

通过JDBC或者ODBC来连接。

第2章 Spark SQL编程

2.1 SparkSession新的起始点

在老的版本中，SparkSQL提供两种SQL查询起始点：

一个叫SQLContext，用于Spark自己提供的SQL查询；

一个叫HiveContext，用于连接Hive的查询。

SparkSession是Spark最新的SQL查询起始点，实质上是SQLContext和HiveContext的组合，所以在SQLContext和HiveContext上可用的API在SparkSession上同样是可以使用的。

SparkSession内部封装了SparkContext，所以计算实际上是由SparkContext完成的。当我们使用spark-shell的时候，Spark框架会自动的创建一个名称叫做Spark的SparkSession，就像我们以前可以自动获取到一个sc来表示SparkContext。

[seven@hadoop102 spark-local]$ bin/spark-shell

20/09/12 11:16:35 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
Using Spark's default log4j profile: org/apache/spark/log4j-defaults.properties
Setting default log level to "WARN".
To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).
Spark context Web UI available at http://hadoop102:4040
Spark context available as 'sc' (master = local[*], app id = local-1599880621394).
Spark session available as 'spark'.
Welcome to
      ____              __

     / __/__  ___ _____/ /__

    _\ \/ _ \/ _ `/ __/  '_/

   /___/ .__/\_,_/_/ /_/\_\   version 3.3.1

      /_/

        
Using Scala version 2.12.10 (Java HotSpot(TM) 64-Bit Server VM, Java 1.8.0_212)
Type in expressions to have them evaluated.
Type :help for more information.

2.2 常用方式

2.2.1 方法调用

1）创建一个maven工程SparkSQL

2）创建包名为com.seven.sparksql

3）输入文件夹准备：在新建的SparkSQL项目名称上右键=》新建input文件夹=》在input文件夹上右键=》新建user.json。并输入如下内容：

{"age":20,"name":"qiaofeng"}
{"age":19,"name":"xuzhu"}
{"age":18,"name":"duanyu"}
{"age":22,"name":"qiaofeng"}
{"age":11,"name":"xuzhu"}
{"age":12,"name":"duanyu"}

5）在pom.xml文件中添加spark-sql的依赖

<dependencies>
    <dependency>
       <groupId>org.apache.spark</groupId>
       <artifactId>spark-sql_2.12</artifactId>
       <version>3.3.1</version>
    </dependency> 

    <dependency>
       <groupId>org.projectlombok</groupId>
       <artifactId>lombok</artifactId>
       <version>1.18.22</version>
    </dependency>
</dependencies>

6）代码实现

环境信息

import org.apache.spark.SparkConf;
import org.apache.spark.SparkContext;
import org.apache.spark.api.java.JavaSparkContext;
import org.apache.spark.sql.SparkSession;

public class SparkSQL01_Env {
    public static void main(String[] args) {

        // TODO 构建环境对象
        //      Spark在结构化数据的处理场景中对核心功能，环境进行了封装
        //new JavaSparkContext()
        SparkConf conf = new SparkConf();
        conf.setMaster("local[*]");
        conf.setAppName("SparkSQL");
        SparkContext sc = new SparkContext(conf);

//        final JavaSparkContext jsc = new JavaSparkContext(conf);

        final SparkSession sparkSession = new SparkSession(sc);

        // TODO 释放资源
        sparkSession.close();

    }
}

构造器设计模式：

import org.apache.spark.SparkConf;
import org.apache.spark.SparkContext;
import org.apache.spark.sql.SparkSession;

public class SparkSQL01_Env_1 {
    public static void main(String[] args) {

        // TODO 构建环境对象
        //      Spark在结构化数据的处理场景中对核心功能，环境进行了封装
        //      构建SparkSQL的环境对象时，一般采用构建器模式
        //      构建器模式： 构建对象
        final SparkSession sparkSession = SparkSession
                .builder()
                .master("local[*]")
                .appName("SparkSQL")
                .getOrCreate();

        // TODO 释放资源
        sparkSession.close();

    }
}

构建环境对象

import org.apache.spark.SparkConf;
import org.apache.spark.sql.SparkSession;

public class SparkSQL01_Env_2 {
    public static void main(String[] args) {

        // TODO 构建环境对象
        //      Spark在结构化数据的处理场景中对核心功能，环境进行了封装
        //      构建SparkSQL的环境对象时，一般采用构建器模式
        //      构建器模式： 构建对象
        SparkConf conf = new SparkConf().setAppName("sparksql").setMaster("local[*]");

        final SparkSession sparkSession = SparkSession
                .builder()
                .config(conf)
                .getOrCreate();

        // TODO 释放资源
        sparkSession.close();

    }
}

SparkSQL示例：

import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;

public class SparkSQL03_SQL {
    public static void main(String[] args) {

        // TODO 构建环境对象
        //      Spark在结构化数据的处理场景中对核心功能，环境进行了封装
        //      构建SparkSQL的环境对象时，一般采用构建器模式
        //      构建器模式： 构建对象
        final SparkSession sparkSession = SparkSession
                .builder()
                .master("local[*]")
                .appName("SparkSQL")
                .getOrCreate();

        // TODO Spark SQL中对数据模型也进行了封装 ： RDD -> Dataset
        //      对接文件数据源时，会将文件中的一行数据封装为Row对象
        final Dataset<Row> ds = sparkSession.read().json("data/user.json");
        //final RDD<Row> rdd = ds.rdd();

        // TODO 将数据模型转换成表，方便SQL的使用
        ds.createOrReplaceTempView("user");


        // TODO 使用SQL文的方式操作数据
        String sql = "select avg(age) from user";
        final Dataset<Row> sqlDS = sparkSession.sql(sql);

        // TODO 展示数据模型的效果
        sqlDS.show();


        // TODO 释放资源
        sparkSession.close();

    }
}

模型的封装：RDD=> DataSet

import org.apache.spark.rdd.RDD;
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;

public class SparkSQL02_Model {
    public static void main(String[] args) {

        // TODO 构建环境对象
        //      Spark在结构化数据的处理场景中对核心功能，环境进行了封装
        //      构建SparkSQL的环境对象时，一般采用构建器模式
        //      构建器模式： 构建对象
        final SparkSession sparkSession = SparkSession
                .builder()
                .master("local[*]")
                .appName("SparkSQL")
                .getOrCreate();

        // TODO Spark SQL中对数据模型也进行了封装 ： RDD -> Dataset
        //      对接文件数据源时，会将文件中的一行数据封装为Row对象
        final Dataset<Row> ds = sparkSession.read().json("data/user.json");
        //final RDD<Row> rdd = ds.rdd();

        // TODO 释放资源
        sparkSession.close();

    }
}

DataFrame 转换为用户自定义对象：

import org.apache.spark.rdd.RDD;
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Encoders;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;

import java.io.Serializable;

public class SparkSQL02_Model_1 {
    public static void main(String[] args) {

        // TODO 构建环境对象
        //      Spark在结构化数据的处理场景中对核心功能，环境进行了封装
        //      构建SparkSQL的环境对象时，一般采用构建器模式
        //      构建器模式： 构建对象
        final SparkSession sparkSession = SparkSession
                .builder()
                .master("local[*]")
                .appName("SparkSQL")
                .getOrCreate();

        // TODO Spark SQL中对数据模型也进行了封装 ： RDD -> Dataset
        //      对接文件数据源时，会将文件中的一行数据封装为Row对象
        final Dataset<Row> ds = sparkSession.read().json("data/user.json");
        //final RDD<Row> rdd = ds.rdd();

        // [1,2,3,4]
        // {name=zs,age=30}

        // TODO Dataframe
//        ds.foreach(
//                row -> {
//                    System.out.println(row.getInt(2));
//                }
//        );

        // TODO 将数据模型中的数据类型进行转换，将Row转换成其他对象进行处理
        final Dataset<User> userDS = ds.as(Encoders.bean(User.class));

        userDS.foreach(
                user -> {
                    System.out.println(user.getName());
                }
        );

        // TODO 释放资源
        sparkSession.close();
    }
}
class User implements Serializable {
    private int id;
    private int age;
    private String name;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

视图：

import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;

public class SparkSQL02_Model_2 {
    public static void main(String[] args) {

        final SparkSession sparkSession = SparkSession
                .builder()
                .master("local[*]")
                .appName("SparkSQL")
                .getOrCreate();
        final Dataset<Row> ds = sparkSession.read().json("data/user.json");

        // TODO 模型对象的访问
        //      将数据模型转换为二维的结构（行，列），可以通过SQL文进行访问
        //      视图：是表的查询结果集。表可以增加，修改，删除，查询。
        //           视图不能增加，不能修改，不能删除，只能查询
        ds.createOrReplaceTempView("user");

        // TODO 当前JDK版本不适合开发SQL
        /*
        "
           select
                *
            from a
            join b on a.id = b.id
            union
            select
                *
           from c
        "
         */
        sparkSession.sql("select * from user").show();

        // TODO 释放资源
        sparkSession.close();

    }
}

DSL语言：

import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;

public class SparkSQL02_Model_3 {
    public static void main(String[] args) {

        final SparkSession sparkSession = SparkSession
                .builder()
                .master("local[*]")
                .appName("SparkSQL")
                .getOrCreate();
        final Dataset<Row> ds = sparkSession.read().json("data/user.json");


        // TODO 采用DSL语法进行访问
        //      select * from user
        ds.select("*").show();

        // TODO 释放资源
        sparkSession.close();

    }
}

SparkSQL：UDAF

import org.apache.spark.sql.*;
import static org.apache.spark.sql.functions.*;
import org.apache.spark.sql.api.java.UDF1;
import org.apache.spark.sql.expressions.Aggregator;

import java.io.Serializable;

import static org.apache.spark.sql.types.DataTypes.StringType;

public class SparkSQL03_SQL_UDAF {
    public static void main(String[] args) {

        // TODO 构建环境对象
        //      Spark在结构化数据的处理场景中对核心功能，环境进行了封装
        //      构建SparkSQL的环境对象时，一般采用构建器模式
        //      构建器模式： 构建对象
        final SparkSession sparkSession = SparkSession
                .builder()
                .master("local[*]")
                .appName("SparkSQL")
                .getOrCreate();


        final Dataset<Row> ds = sparkSession.read().json("data/user.json");
        ds.createOrReplaceTempView("user");

        // TODO SparkSQL采用特殊的方式将UDAF转换成UDF使用
        //      UDAF使用时需要创建自定义聚合对象
        //        udaf方法需要传递2个参数
        //             第一个参数表示UDAF对象
        //             第二个参数表示UDAF对象
        sparkSession.udf().register("avgAge", udaf(
                new MyAvgAgeUDAF(), Encoders.LONG()
        ));

        String sql = "select avgAge(age) from user";
        final Dataset<Row> sqlDS = sparkSession.sql(sql);
        sqlDS.show();


        // TODO 释放资源
        sparkSession.close();

    }
}

import org.apache.spark.sql.Encoder;
import org.apache.spark.sql.Encoders;
import org.apache.spark.sql.expressions.Aggregator;

import java.io.Serializable;

// TODO 自定义UDAF函数，实现年龄的平均值
//      1. 创建自定义的【公共】类
//      2. 继承 org.apache.spark.sql.expressions.Aggregator
//      3. 设定泛型
//          IN : 输入数据类型
//          BUFF : 缓冲区的数据类型
//          OUT : 输出数据类型
//     4. 重写方法 （ 4（计算） + 2(状态)）
public class MyAvgAgeUDAF extends Aggregator<Long, AvgAgeBuffer, Long> {
    @Override
    // TODO 缓冲区的初始化操作
    public AvgAgeBuffer zero() {
        return new AvgAgeBuffer(0L, 0L);
    }

    @Override
    // TODO 将输入的年龄和缓冲区的数据进行聚合操作
    public AvgAgeBuffer reduce(AvgAgeBuffer buffer, Long in) {
        buffer.setTotal(buffer.getTotal() + in);
        buffer.setCnt(buffer.getCnt() + 1);
        return buffer;
    }

    @Override
    // TODO 合并缓冲区的数据
    public AvgAgeBuffer merge(AvgAgeBuffer b1, AvgAgeBuffer b2) {
        b1.setTotal(b1.getTotal() + b2.getTotal());
        b1.setCnt(b1.getCnt() + b2.getCnt());
        return b1;
    }

    @Override
    // TODO 计算最终结果
    public Long finish(AvgAgeBuffer buffer) {
        return buffer.getTotal() / buffer.getCnt();
    }

    @Override
    public Encoder<AvgAgeBuffer> bufferEncoder() {
        return Encoders.bean(AvgAgeBuffer.class);
    }

    @Override
    public Encoder<Long> outputEncoder() {
        return Encoders.LONG();
    }
}

import java.io.Serializable;

public class AvgAgeBuffer implements Serializable {
    private Long total;
    private Long cnt;

    public AvgAgeBuffer(Long total, Long cnt) {
        this.total = total;
        this.cnt = cnt;
    }

    public AvgAgeBuffer() {
    }

    public Long getTotal() {
        return total;
    }

    public void setTotal(Long total) {
        this.total = total;
    }

    public Long getCnt() {
        return cnt;
    }

    public void setCnt(Long cnt) {
        this.cnt = cnt;
    }
}

SparkSQL：UDF

import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;
import org.apache.spark.sql.api.java.UDF1;

import org.apache.spark.sql.types.DataTypes;
import org.apache.spark.sql.types.StringType$;

import static org.apache.spark.sql.types.DataTypes.StringType;

public class SparkSQL03_SQL_UDF {
    public static void main(String[] args) {

        // TODO 构建环境对象
        //      Spark在结构化数据的处理场景中对核心功能，环境进行了封装
        //      构建SparkSQL的环境对象时，一般采用构建器模式
        //      构建器模式： 构建对象
        final SparkSession sparkSession = SparkSession
                .builder()
                .master("local[*]")
                .appName("SparkSQL")
                .getOrCreate();


        final Dataset<Row> ds = sparkSession.read().json("data/user.json");
        ds.createOrReplaceTempView("user");

        // prefix + name
        //  Name: + zhangsan
        //  Name: + lisi
        //  Name: + wangwu
        //String sql = "select 'Name:' + name from user";
        //String sql = "select concat('Name:',name) from user";
        //String sql = "select 'Name:'||name from user";
        // mysql, oracle(||), db2, sqlserver
        // Shark => Spark On Hive => SparkSQL => Spark parse SQL
        //       => Hive On Spark => 数据仓库   => Hive parse SQL


        // TODO SparkSQL提供了一种特殊的方式，可以在SQL中增加自定义方法来实现复杂的逻辑

        // TODO 如果想要自定义的方法能够在SQL中使用，那么必须在SPark中进行声明和注册
        //      register方法需要传递3个参数
        //           第一个参数表示SQL中使用的方法名
        //           第二个参数表示逻辑 : IN => OUT
        //           第三个参数表示返回的数据类型 : DataType类型数据，需要使用scala语法操作，需要特殊的使用方式。
        //                 scala Object => Java
        //                 StringType$.MODULE$ => case object StringType
        //                 DataTypes.StringType
        sparkSession.udf().register("prefixName", new UDF1<String, String>() {
            @Override
            public String call(String name) throws Exception {
                return "Name:" + name;
            }
        }, StringType);

        String sql = "select prefixName(name) from user";
        final Dataset<Row> sqlDS = sparkSession.sql(sql);
        sqlDS.show();


        // TODO 释放资源
        sparkSession.close();

    }
}

SparkSQL：csv source

import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;
import org.apache.spark.sql.api.java.UDF1;

import static org.apache.spark.sql.types.DataTypes.StringType;

public class SparkSQL04_Source_CSV {
    public static void main(String[] args) {

        // TODO 构建环境对象
        //      Spark在结构化数据的处理场景中对核心功能，环境进行了封装
        //      构建SparkSQL的环境对象时，一般采用构建器模式
        //      构建器模式： 构建对象
        final SparkSession sparkSession = SparkSession
                .builder()
                .master("local[*]")
                .appName("SparkSQL")
                .getOrCreate();

        // TODO CSV文件就是将数据采用逗号分隔的数据文件
        final Dataset<Row> csv = sparkSession.read()
                .option("header", "true") // 配置
                .option("sep","_") // 配置：\t => tsv, csv
                .csv("data/user.csv");
        csv.show();

        // select avg(_c2) from user


        // TODO 释放资源
        sparkSession.close();

    }
}

SparkSQL：csv source


import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SaveMode;
import org.apache.spark.sql.SparkSession;

public class SparkSQL04_Source_CSV_1 {
    public static void main(String[] args) {

        // TODO 构建环境对象
        //      Spark在结构化数据的处理场景中对核心功能，环境进行了封装
        //      构建SparkSQL的环境对象时，一般采用构建器模式
        //      构建器模式： 构建对象
        final SparkSession sparkSession = SparkSession
                .builder()
                .master("local[*]")
                .appName("SparkSQL")
                .getOrCreate();

        // 32 UUID String
        // TODO CSV文件就是将数据采用逗号分隔的数据文件
        final Dataset<Row> csv = sparkSession.read()
                .option("header", "true") // 配置
                .option("sep","_") // 配置：\t => tsv, csv
                .csv("data/user.csv");

        // org.apache.spark.sql.AnalysisException : bigdata-bj-classes231226/output already exists.

        // TODO 如果输出目的地已经存在，那么SparkSQL默认会发生错误，如果不希望发生错误，那么就需要修改配置：保存模式
        //      append : 追加
        csv.write()
                .mode(SaveMode.Overwrite)
                .option("header", "true") // 配置
                .csv("output");

        // select avg(_c2) from user


        // TODO 释放资源
        sparkSession.close();

    }
}

SparkSQL：JSON Source

import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;

public class SparkSQL05_Source_JSON {
    public static void main(String[] args) {

        // TODO 构建环境对象
        //      Spark在结构化数据的处理场景中对核心功能，环境进行了封装
        //      构建SparkSQL的环境对象时，一般采用构建器模式
        //      构建器模式： 构建对象
        final SparkSession sparkSession = SparkSession
                .builder()
                .master("local[*]")
                .appName("SparkSQL")
                .getOrCreate();

        // org.apache.spark.sql.AnalysisException:
       //            Since Spark 2.3, the queries from raw JSON/CSV files are disallowed when the
       //            referenced columns only include the internal corrupt record column
       //                    (named _corrupt_record by default)

        // JSON : JavaScript Object Notation
        //        对象 ：{}
        //        数组 ：[]
        //        JSON文件：整个文件的数据格式符合JSON格式，不是一行数据符合JSON格式
        // TODO SparkSQL其实就是对Spark Core RDD的封装。RDD读取文件采用的是Hadoop，hadoop是按行读取。
        //      SparkSQL只需要保证JSON文件中一行数据符合JSON格式即可，无需整个文件符合JSON格式
        final Dataset<Row> json = sparkSession.read().json("data/user.json");

        json.write().json("output");
        json.show();


        // TODO 释放资源
        sparkSession.close();

    }
}

SparkSQL：JSON Source

import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;

public class SparkSQL05_Source_JSON_1 {
    public static void main(String[] args) {

        // TODO 构建环境对象
        //      Spark在结构化数据的处理场景中对核心功能，环境进行了封装
        //      构建SparkSQL的环境对象时，一般采用构建器模式
        //      构建器模式： 构建对象
        final SparkSession sparkSession = SparkSession
                .builder()
                .master("local[*]")
                .appName("SparkSQL")
                .getOrCreate();

        final Dataset<Row> csv = sparkSession.read().csv("data/user.csv");

        csv.write().json("output");
        csv.show();


        // TODO 释放资源
        sparkSession.close();

    }
}

SparkSQL：Parquet Source

import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;

public class SparkSQL06_Source_Parquet {
    public static void main(String[] args) {

        // TODO 构建环境对象
        //      Spark在结构化数据的处理场景中对核心功能，环境进行了封装
        //      构建SparkSQL的环境对象时，一般采用构建器模式
        //      构建器模式： 构建对象
        final SparkSession sparkSession = SparkSession
                .builder()
                .master("local[*]")
                .appName("SparkSQL")
                .getOrCreate();


        //final Dataset<Row> json = sparkSession.read().parquet("data/users.parquet");
        //json.show();
        final Dataset<Row> csv = sparkSession.read().json("data/user.json");

        csv.write().parquet("output");

        // TODO 释放资源
        sparkSession.close();

    }
}

MySQL Source：

import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;

import java.util.Properties;

public class SparkSQL07_Source_MySQL {
    public static void main(String[] args) {

        // TODO 构建环境对象
        //      Spark在结构化数据的处理场景中对核心功能，环境进行了封装
        //      构建SparkSQL的环境对象时，一般采用构建器模式
        //      构建器模式： 构建对象
        final SparkSession sparkSession = SparkSession
                .builder()
                .master("local[*]")
                .appName("SparkSQL")
                .getOrCreate();


        Properties properties = new Properties();
        properties.setProperty("user","root");
        properties.setProperty("password","000000");

//        json.write()
//                // 写出模式针对于表格追加覆盖
//                .mode(SaveMode.Append)
//                .jdbc("jdbc:mysql://hadoop102:3306","gmall.testInfo",properties);

        Dataset<Row> jdbc = sparkSession.read()
                .jdbc("jdbc:mysql://hadoop102:3306/gmall?useSSL=false&useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true", "activity_info", properties);

        jdbc.write()
            .jdbc("jdbc:mysql://hadoop102:3306/gmall?useSSL=false&useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true", "activity_info_test", properties);

        jdbc.show();

        // TODO 释放资源
        sparkSession.close();

    }
}

SparkSQL：Hive Source

import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;

import java.util.Properties;

public class SparkSQL08_Source_Hive {
    public static void main(String[] args) {

        // TODO 在编码前，设定Hadoop的访问用户
        System.setProperty("HADOOP_USER_NAME","atguigu");

        final SparkSession sparkSession = SparkSession
                .builder()
                .enableHiveSupport() // TODO 启用Hive的支持
                .master("local[*]")
                .appName("SparkSQL")
                .getOrCreate();

        sparkSession.sql("show tables").show();

        sparkSession.sql("create table user_info(name String,age bigint)");
        sparkSession.sql("insert into table user_info values('zhangsan',10)");
        sparkSession.sql("select * from user_info").show();

        // TODO 释放资源
        sparkSession.close();

    }
}

添加javaBean的User

package com.seven.sparksql.Bean;

import lombok.Data;
import java.io.Serializable; 

@Data
public class User implements Serializable {
    public Long age;
    public String name; 

    public User() {
    }
 
    public User(Long age, String name) {
        this.age = age;
        this.name = name;
    }
}

代码编写
package com.seven.sparksql;
import com.seven.sparksql.Bean.User;
import org.apache.spark.SparkConf;
import org.apache.spark.api.java.function.MapFunction;
import org.apache.spark.api.java.function.ReduceFunction;
import org.apache.spark.sql.*;
import scala.Tuple2; 

public class Test01_Method {
    public static void main(String[] args) {

        //1. 创建配置对象
        SparkConf conf = new SparkConf().setAppName("sparksql").setMaster("local[*]"); 

        //2. 获取sparkSession
        SparkSession spark = SparkSession.builder().config(conf).getOrCreate(); 

        //3. 编写代码
        // 按照行读取
        Dataset<Row> lineDS = spark.read().json("input/user.json");
        // 转换为类和对象
        Dataset<User> userDS = lineDS.as(Encoders.bean(User.class)); 

//        userDS.show(); 

        // 使用方法操作
        // 函数式的方法
        Dataset<User> userDataset = lineDS.map(new MapFunction<Row, User>() {
            @Override
            public User call(Row value) throws Exception {
                return new User(value.getLong(0), value.getString(1));
            }

        },
                // 使用kryo在底层会有部分算子无法使用
                Encoders.bean(User.class)); 

        // 常规方法
        Dataset<User> sortDS = userDataset.sort(new Column("age"));
        sortDS.show(); 

        // 区分
        RelationalGroupedDataset groupByDS = userDataset.groupBy("name"); 

        // 后续方法不同
        Dataset<Row> count = groupByDS.count(); 

        // 推荐使用函数式的方法  使用更灵活
        KeyValueGroupedDataset<String, User> groupedDataset = userDataset.groupByKey(new MapFunction<User, String>() {

            @Override
            public String call(User value) throws Exception {
                return value.name;
            }
        }, Encoders.STRING()); 

        // 聚合算子都是从groupByKey开始
        // 推荐使用reduceGroup
        Dataset<Tuple2<String, User>> result = groupedDataset.reduceGroups(new ReduceFunction<User>() {

            @Override
            public User call(User v1, User v2) throws Exception {
                // 取用户的大年龄
                return new User(Math.max(v1.age, v2.age), v1.name);
            }
        }); 

        result.show(); 

        //4. 关闭sparkSession
        spark.close();
    }
}

在sparkSql中DS直接支持的转换算子有：map（底层已经优化为mapPartition）、mapPartition、flatMap、groupByKey（聚合算子全部由groupByKey开始）、filter、distinct、coalesce、repartition、sort和orderBy（不是函数式的算子，不过不影响使用）。

2.2.2 SQL使用方式

package com.seven.sparksql; 

import org.apache.spark.SparkConf;
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession; 

public class Test02_SQL {
    public static void main(String[] args) { 

        //1. 创建配置对象
        SparkConf conf = new SparkConf().setAppName("sparksql").setMaster("local[*]");

        //2. 获取sparkSession
        SparkSession spark = SparkSession.builder().config(conf).getOrCreate(); 

        //3. 编写代码
        Dataset<Row> lineDS = spark.read().json("input/user.json"); 

        // 创建视图 => 转换为表格 填写表名
        // 临时视图的生命周期和当前的sparkSession绑定
        // orReplace表示覆盖之前相同名称的视图
        lineDS.createOrReplaceTempView("t1"); 
        // 支持所有的hive sql语法,并且会使用spark的又花钱
        Dataset<Row> result = spark.sql("select * from t1 where age > 18");

        result.show(); 

        //4. 关闭sparkSession
        spark.close();
    }
}}

2.2.3 DSL特殊语法（扩展）

环境对象的封装：

import org.apache.spark.SparkConf;
import org.apache.spark.SparkContext;
import org.apache.spark.api.java.JavaSparkContext;
import org.apache.spark.sql.SparkSession;

import java.util.Arrays;

public class SparkSQL01_Env_3 {
    public static void main(String[] args) {

        // TODO 构建环境对象
        //      Spark在结构化数据的处理场景中对核心功能，环境进行了封装
        //      构建SparkSQL的环境对象时，一般采用构建器模式
        //      构建器模式： 构建对象
        SparkConf conf = new SparkConf().setAppName("sparksql").setMaster("local[*]");

        final SparkSession sparkSession = SparkSession
                .builder()
                .config(conf)
                .getOrCreate();

        // TODO 环境之间的转换
        //      Core : SparkContext -> SQL : SparkSession
        //new SparkSession( new SparkContext(conf) );
        // TODO SQL  : SparkSession -> Core : SparkContext
        //final SparkContext sparkContext = sparkSession.sparkContext();
        //sparkContext.parallelize()
        // TODO SQL  : SparkSession -> Core : JavaSparkContext
        final SparkContext sparkContext = sparkSession.sparkContext();
        final JavaSparkContext jsc = new JavaSparkContext(sparkContext);
        jsc.parallelize(Arrays.asList(1,2,3,4));

        // TODO 释放资源
        sparkSession.close();

    }
}

package com.seven.sparksql; 

import org.apache.spark.SparkConf;
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession; 

import static org.apache.spark.sql.functions.col; 

public class Test03_DSL {
    public static void main(String[] args) {
        //1. 创建配置对象
        SparkConf conf = new SparkConf().setAppName("sparksql").setMaster("local[*]"); 

        //2. 获取sparkSession
        SparkSession spark = SparkSession.builder().config(conf).getOrCreate(); 

        //3. 编写代码
        // 导入特殊的依赖 import static org.apache.spark.sql.functions.col;
        Dataset<Row> lineRDD = spark.read().json("input/user.json");
        Dataset<Row> result = lineRDD.select(col("name").as("newName"),col("age").plus(1).as("newAge"))
                .filter(col("age").gt(18));
        result.show(); 

        //4. 关闭sparkSession
        spark.close();
    }
}

2.3 SQL语法的用户自定义函数

2.3.1 UDF

1）UDF：一行进入，一行出

2）代码实现

package com.seven.sparksql;

import org.apache.spark.SparkConf;
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;
import org.apache.spark.sql.api.java.UDF1;
import org.apache.spark.sql.expressions.UserDefinedFunction;
import org.apache.spark.sql.types.DataTypes;
import static org.apache.spark.sql.functions.udf; 

public class Test04_UDF {
    public static void main(String[] args) { 

        //1. 创建配置对象
        SparkConf conf = new SparkConf().setAppName("sparksql").setMaster("local[*]");

        //2. 获取sparkSession
        SparkSession spark = SparkSession.builder().config(conf).getOrCreate();

        //3. 编写代码
        Dataset<Row> lineRDD = spark.read().json("input/user.json");
        lineRDD.createOrReplaceTempView("user"); 

        // 定义一个函数
        // 需要首先导入依赖import static org.apache.spark.sql.functions.udf;
        UserDefinedFunction addName = udf(new UDF1<String, String>() {

            @Override
            public String call(String s) throws Exception {
                return s + " 大侠";
            }
        }, DataTypes.StringType); 

        spark.udf().register("addName",addName);
        spark.sql("select addName(name) newName from user")
                .show(); 

        // lambda表达式写法
        spark.udf().register("addName1",(UDF1<String,String>) name -> name + " 大侠",DataTypes.StringType);

        //4. 关闭sparkSession
        spark.close();
    }
}

2.3.2 UDAF

1）UDAF：输入多行，返回一行。通常和groupBy一起使用，如果直接使用UDAF函数，默认将所有的数据合并在一起。

2）Spark3.x推荐使用extends Aggregator自定义UDAF，属于强类型的Dataset方式。

3）Spark2.x使用extends UserDefinedAggregateFunction，属于弱类型的DataFrame

4）案例实操

需求：实现求平均年龄，自定义UDAF，MyAvg(age)

（1）自定义聚合函数实现-强类型

package com.seven.sparksql; 

import org.apache.spark.SparkConf;
import org.apache.spark.sql.Encoder;
import org.apache.spark.sql.Encoders;
import org.apache.spark.sql.SparkSession;
import org.apache.spark.sql.expressions.Aggregator; 

import java.io.Serializable;
import static org.apache.spark.sql.functions.udaf; 

public class Test05_UDAF {
    public static void main(String[] args) { 

        //1. 创建配置对象
        SparkConf conf = new SparkConf().setAppName("sparksql").setMaster("local[*]");

        //2. 获取sparkSession
        SparkSession spark = SparkSession.builder().config(conf).getOrCreate();

        //3. 编写代码
        spark.read().json("input/user.json").createOrReplaceTempView("user");

        // 注册需要导入依赖 import static org.apache.spark.sql.functions.udaf;
        spark.udf().register("avgAge",udaf(new MyAvg(),Encoders.LONG()));
        spark.sql("select avgAge(age) newAge from user").show(); 

        //4. 关闭sparkSession
        spark.close();
    } 

    public static class Buffer implements Serializable {
        private Long sum;
        private Long count; 

        public Buffer() {
        }

        public Buffer(Long sum, Long count) {
            this.sum = sum;
            this.count = count;
        }
        public Long getSum() {
            return sum;
        }
        public void setSum(Long sum) {
            this.sum = sum;
        }
        public Long getCount() {
            return count;
        }
        public void setCount(Long count) {
            this.count = count;
        }
    } 

    public static class MyAvg extends Aggregator<Long,Buffer,Double>{ 

        @Override
        public Buffer zero() {
            return new Buffer(0L,0L);
        } 

        @Override
        public Buffer reduce(Buffer b, Long a) {
            b.setSum(b.getSum() + a);
            b.setCount(b.getCount() + 1);
            return b;
        } 

        @Override
        public Buffer merge(Buffer b1, Buffer b2) {
            b1.setSum(b1.getSum() + b2.getSum());
            b1.setCount(b1.getCount() + b2.getCount());
            return b1;
        } 

        @Override
        public Double finish(Buffer reduction) {
            return reduction.getSum().doubleValue() / reduction.getCount();
        }

        @Override
        public Encoder<Buffer> bufferEncoder() {
            // 可以用kryo进行优化
            return Encoders.kryo(Buffer.class);
        } 

        @Override
        public Encoder<Double> outputEncoder() {
            return Encoders.DOUBLE();
        }
    }
}

2.3.3 UDTF（没有）

输入一行，返回多行（Hive）。

SparkSQL中没有UDTF，需要使用算子类型的flatMap先完成拆分。

第3章 SparkSQL数据的加载与保存

3.1 读取和保存文件

SparkSQL读取和保存的文件一般为三种，JSON文件、CSV文件和列式存储的文件，同时可以通过添加参数，来识别不同的存储和压缩格式。

3.1.1 CSV文件

1）代码实现

package com.seven.sparksql; 

import com.seven.sparksql.Bean.User;
import org.apache.spark.SparkConf;
import org.apache.spark.api.java.function.MapFunction;
import org.apache.spark.sql.*; 

public class Test06_CSV {
    public static void main(String[] args) throws ClassNotFoundException {

        //1. 创建配置对象
        SparkConf conf = new SparkConf().setAppName("sparksql").setMaster("local[*]");

        //2. 获取sparkSession
        SparkSession spark = SparkSession.builder().config(conf).getOrCreate(); 

        //3. 编写代码
        DataFrameReader reader = spark.read(); 

        // 添加参数  读取csv
        Dataset<Row> userDS = reader
                .option("header", "true")//默认为false 不读取列名
                .option("sep",",") // 默认为, 列的分割
                // 不需要写压缩格式  自适应
                .csv("input/user.csv");
        userDS.show(); 

        // 转换为user的ds
        // 直接转换类型会报错  csv读取的数据都是string
//        Dataset<User> userDS1 = userDS.as(Encoders.bean(User.class));
        userDS.printSchema(); 

        Dataset<User> userDS1 = userDS.map(new MapFunction<Row, User>() {
            @Override
            public User call(Row value) throws Exception {
                return new User(Long.valueOf(value.getString(0)), value.getString(1));
            }
        }, Encoders.bean(User.class)); 

        userDS1.show(); 

        // 写出为csv文件
        DataFrameWriter<User> writer = userDS1.write();
        writer.option("seq",";")
                .option("header","true")
//                .option("compression","gzip")// 压缩格式
                // 写出模式
                // append 追加
                // Ignore 忽略本次写出
                // Overwrite 覆盖写
                // ErrorIfExists 如果存在报错
                .mode(SaveMode.Append)
                .csv("output"); 

        //4. 关闭sparkSession
        spark.close();
    }
}

3.1.2 JSON文件

package com.seven.sparksql; 

import com.seven.sparksql.Bean.User;
import org.apache.spark.SparkConf;
import org.apache.spark.sql.*;
 
public class Test07_JSON {
    public static void main(String[] args) {
        //1. 创建配置对象
        SparkConf conf = new SparkConf().setAppName("sparksql").setMaster("local[*]"); 

        //2. 获取sparkSession
        SparkSession spark = SparkSession.builder().config(conf).getOrCreate(); 

        //3. 编写代码
        Dataset<Row> json = spark.read().json("input/user.json"); 

        // json数据可以读取数据的数据类型
        Dataset<User> userDS = json.as(Encoders.bean(User.class));
        userDS.show();    

        // 读取别的类型的数据也能写出为json
        DataFrameWriter<User> writer = userDS.write();
        writer.json("output1");

        //4. 关闭sparkSession
        spark.close();
    }
}

3.1.3 Parquet文件

列式存储的数据自带列分割。

package com.seven.sparksql; 

import org.apache.spark.SparkConf;
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession; 

public class Test08_Parquet {
    public static void main(String[] args) { 

        //1. 创建配置对象
        SparkConf conf = new SparkConf().setAppName("sparksql").setMaster("local[*]");

        //2. 获取sparkSession
        SparkSession spark = SparkSession.builder().config(conf).getOrCreate(); 

        //3. 编写代码
        Dataset<Row> json = spark.read().json("input/user.json"); 

        // 写出默认使用snappy压缩
//        json.write().parquet("output"); 

        // 读取parquet 自带解析  能够识别列名
        Dataset<Row> parquet = spark.read().parquet("output");
        parquet.printSchema(); 

        //4. 关闭sparkSession
        spark.close();
    }
}

3.2 与MySQL交互

1）导入依赖

<!-- 早期MySQL版本 -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.27</version>
</dependency>
<!-- 5.8(8.0)MySQL版本 -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.18</version>
</dependency>

2）从MySQL读数据

package com.seven.sparksql;

import org.apache.spark.SparkConf;
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession; 

import java.util.Properties; 

public class Test09_Table {
    public static void main(String[] args) { 

        //1. 创建配置对象
        SparkConf conf = new SparkConf().setAppName("sparksql").setMaster("local[*]");

        //2. 获取sparkSession
        SparkSession spark = SparkSession.builder().config(conf).getOrCreate();

        //3. 编写代码
        Dataset<Row> json = spark.read().json("input/user.json"); 

        // 添加参数
        Properties properties = new Properties();
        properties.setProperty("user","root");
        properties.setProperty("password","000000"); 
//        json.write()
//                // 写出模式针对于表格追加覆盖
//                .mode(SaveMode.Append)
//                .jdbc("jdbc:mysql://hadoop102:3306","gmall.testInfo",properties); 

        Dataset<Row> jdbc = spark.read().jdbc("jdbc:mysql://hadoop102:3306/gmall?useSSL=false&useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true", "test_info", properties);
        jdbc.show();
        //4. 关闭sparkSession
        spark.close();
    }
}

3.3 与Hive交互

SparkSQL可以采用内嵌Hive（spark开箱即用的hive），也可以采用外部Hive。企业开发中，通常采用外部Hive。

3.3.1 Linux中的交互

1）添加MySQL连接驱动到spark-yarn的jars目录

[seven@hadoop102 spark-yarn]$ cp /opt/software/mysql-connector-java-5.1.27-bin.jar /opt/module/spark-yarn/jars

2）添加hive-site.xml文件到spark-yarn的conf目录

[seven@hadoop102 spark-yarn]$ cp /opt/module/hive/conf/hive-site.xml /opt/module/spark-yarn/conf

3）启动spark-sql的客户端即可

[seven@hadoop102 spark-yarn]$ bin/spark-sql --master yarn

spark-sql (default)> show tables;

3.3.2 IDEA中的交互

1）添加依赖

<dependencies>
    <dependency>
       <groupId>org.apache.spark</groupId>
       <artifactId>spark-sql_2.12</artifactId>
       <version>3.3.1</version>
    </dependency> 

    <dependency>
       <groupId>mysql</groupId>
       <artifactId>mysql-connector-java</artifactId>
       <version>5.1.27</version>
    </dependency>

    <dependency>
       <groupId>org.apache.spark</groupId>
       <artifactId>spark-hive_2.12</artifactId>
       <version>3.3.1</version>
    </dependency> 

    <dependency>
       <groupId>org.projectlombok</groupId>
       <artifactId>lombok</artifactId>
       <version>1.18.22</version>
    </dependency>
</dependencies>

2）拷贝hive-site.xml到resources目录（如果需要操作Hadoop，需要拷贝hdfs-site.xml、core-site.xml、yarn-site.xml）

3）代码实现

package com.seven.sparksql;

import org.apache.spark.SparkConf;
import org.apache.spark.sql.SparkSession; 

public class Test10_Hive {
    public static void main(String[] args) {
        System.setProperty("HADOOP_USER_NAME","seven"); 

        //1. 创建配置对象
        SparkConf conf = new SparkConf().setAppName("sparksql").setMaster("local[*]");

        //2. 获取sparkSession
        SparkSession spark = SparkSession.builder()
                .enableHiveSupport()// 添加hive支持
                .config(conf).getOrCreate(); 

        //3. 编写代码
        spark.sql("show tables").show();
        spark.sql("create table user_info(name String,age bigint)");
        spark.sql("insert into table user_info values('zhangsan',10)");
        spark.sql("select * from user_info").show(); 

        //4. 关闭sparkSession
        spark.close();
    }
}

第4章 SparkSQL项目实战

4.1 准备数据

我们这次Spark-sql操作所有的数据均来自Hive，首先在Hive中创建表，并导入数据。一共有3张表：1张用户行为表，1张城市表，1张产品表。

1）将city_info.txt、product_info.txt、user_visit_action.txt上传到/opt/module/data

[seven@hadoop102 module]$ mkdir data

2）将创建对应的三张表

hive (default)>

CREATE TABLE `user_visit_action`(
  `date` string,
  `user_id` bigint,
  `session_id` string,
  `page_id` bigint,
  `action_time` string,
  `search_keyword` string,
  `click_category_id` bigint,
  `click_product_id` bigint, --点击商品id，没有商品用-1表示。
  `order_category_ids` string,
  `order_product_ids` string,
  `pay_category_ids` string,
  `pay_product_ids` string,
  `city_id` bigint --城市id
)
row format delimited fields terminated by '\t';

 
CREATE TABLE `city_info`(
  `city_id` bigint, --城市id
  `city_name` string, --城市名称
  `area` string --区域名称
)
row format delimited fields terminated by '\t';

 
CREATE TABLE `product_info`(
  `product_id` bigint, -- 商品id
  `product_name` string, --商品名称
  `extend_info` string
)
row format delimited fields terminated by '\t';

3）并加载数据

hive (default)>

load data local inpath '/opt/module/data/user_visit_action.txt' into table user_visit_action;
load data local inpath '/opt/module/data/product_info.txt' into table product_info;
load data local inpath '/opt/module/data/city_info.txt' into table city_info;

4）测试一下三张表数据是否正常

hive (default)>

select * from user_visit_action limit 5;
select * from product_info limit 5;
select * from city_info limit 5;

4.2 需求：各区域热门商品Top3

4.2.1 需求简介

这里的热门商品是从点击量的维度来看的，计算各个区域前三大热门商品，并备注上每个商品在主要城市中的分布比例，超过两个城市用其他显示。

例如：

地区	商品名称	点击次数	城市备注
华北	商品A	100000	北京21.2%，天津13.2%，其他65.6%
华北	商品P	80200	北京63.0%，太原10%，其他27.0%
华北	商品M	40000	北京63.0%，太原10%，其他27.0%
东北	商品J	92000	大连28%，辽宁17.0%，其他 55.0%

4.2.2 思路分析

使用Spark-SQL来完成复杂的需求，可以使用UDF或UDAF。

（1）查询出来所有的点击记录，并与city_info表连接，得到每个城市所在的地区，与 Product_info表连接得到商品名称。

（2）按照地区和商品名称分组，统计出每个商品在每个地区的总点击次数。

（3）每个地区内按照点击次数降序排列。

（4）只取前三名，并把结果保存在数据库中。

（5）城市备注需要自定义UDAF函数。

4.2.3 代码实现

package com.seven.sparksql.demo; 

import lombok.Data;
import org.apache.spark.SparkConf;
import org.apache.spark.sql.*;
import org.apache.spark.sql.expressions.Aggregator; 

import java.io.Serializable;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.TreeMap;
import java.util.function.BiConsumer;
import static org.apache.spark.sql.functions.udaf; 

public class Test01_Top3 {
    public static void main(String[] args) { 

        // 1. 创建sparkConf配置对象
        SparkConf conf = new SparkConf().setAppName("sql").setMaster("local[*]"); 

        // 2. 创建sparkSession连接对象
        SparkSession spark = SparkSession.builder().enableHiveSupport().config(conf).getOrCreate(); 

        // 3. 编写代码
        // 将3个表格数据join在一起
        Dataset<Row> t1DS = spark.sql("select \n" +
                "\tc.area,\n" +
                "\tc.city_name,\n" +
                "\tp.product_name\n" +
                "from\n" +
                "\tuser_visit_action u\n" +
                "join\n" +
                "\tcity_info c\n" +
                "on\n" +
                "\tu.city_id=c.city_id\n" +
                "join\n" +
                "\tproduct_info p\n" +
                "on\n" +
                "\tu.click_product_id=p.product_id");
        t1DS.createOrReplaceTempView("t1");
        spark.udf().register("cityMark",udaf(new CityMark(),Encoders.STRING()));

        // 将区域内的产品点击次数统计出来
        Dataset<Row> t2ds = spark.sql("select \n" +
                "\tarea,\n" +
                "\tproduct_name,\n" +
                "\tcityMark(city_name) mark,\n" +
                "\tcount(*) counts\n" +
                "from\t\n" +
                "\tt1\n" +
                "group by\n" +
                "\tarea,product_name"); 

//        t2ds.show(false);
        t2ds.createOrReplaceTempView("t2"); 

        // 对区域内产品点击的次数进行排序  找出区域内的top3
        spark.sql("select\n" +
                "\tarea,\n" +
                "\tproduct_name,\n" +
                "\tmark,\n" +
                "\trank() over (partition by area order by counts desc) rk\n" +
                "from \n" +
                "\tt2").createOrReplaceTempView("t3"); 

        // 使用过滤  取出区域内的top3
        spark.sql("select\n" +
                "\tarea,\n" +
                "\tproduct_name,\n" +
                "\tmark \n" +
                "from\n" +
                "\tt3\n" +
                "where \n" +
                "\trk < 4").show(50,false); 

        // 4. 关闭sparkSession
        spark.close();
    } 

    @Data
    public static class Buffer implements Serializable {
        private Long totalCount;
        private HashMap<String,Long> map;
        public Buffer() {
        }

        public Buffer(Long totalCount, HashMap<String, Long> map) {
            this.totalCount = totalCount;
            this.map = map;
        }
    } 

    public static class CityMark extends Aggregator<String, Buffer, String> {
        public static class CityCount {
            public String name;
            public Long count;

            public CityCount(String name, Long count) {
                this.name = name;
                this.count = count;
            }

            public CityCount() {
            }
        }

        public static class CompareCityCount implements Comparator<CityCount> {
            /**

             * 默认倒序
             * @param o1
             * @param o2
             * @return
             */

            @Override
            public int compare(CityCount o1, CityCount o2) {
                if (o1.count > o2.count) {
                    return -1;
                } else return o1.count.equals(o2.count) ? 0 : 1;
            }
        }

        @Override
        public Buffer zero() {
            return new Buffer(0L, new HashMap<String, Long>());
        }

        /**
         * 分区内的预聚合
         *
         * @param b map(城市,sum)
         * @param a 当前行表示的城市
         * @return
         */
        @Override
        public Buffer reduce(Buffer b, String a) {
            HashMap<String, Long> hashMap = b.getMap();
            // 如果map中已经有当前城市  次数+1
            // 如果map中没有当前城市    0+1
            hashMap.put(a, hashMap.getOrDefault(a, 0L) + 1);
            b.setTotalCount(b.getTotalCount() + 1L);
            return b;
        }

        /**
         * 合并多个分区间的数据
         *
         * @param b1 (北京,100),(上海,200)
         * @param b2 (天津,100),(上海,200)
         * @return
         */
        @Override
        public Buffer merge(Buffer b1, Buffer b2) {
            b1.setTotalCount(b1.getTotalCount() + b2.getTotalCount());
            HashMap<String, Long> map1 = b1.getMap();
            HashMap<String, Long> map2 = b2.getMap();
            // 将map2中的数据放入合并到map1
            map2.forEach(new BiConsumer<String, Long>() {
                @Override
                public void accept(String s, Long aLong) {
                    map1.put(s, aLong + map1.getOrDefault(s, 0L));
                }
            }); 

            return b1;
        } 

        /**
         * map => {(上海,200),(北京,100),(天津,300)}
         *
         * @param reduction
         * @return
         */
        @Override
        public String finish(Buffer reduction) {
            Long totalCount = reduction.getTotalCount();
            HashMap<String, Long> map = reduction.getMap();
            // 需要对map中的value次数进行排序
            ArrayList<CityCount> cityCounts = new ArrayList<>(); 

            // 将map中的数据放入到treeMap中 进行排序
            map.forEach(new BiConsumer<String, Long>() {
                @Override
                public void accept(String s, Long aLong) {
                    cityCounts.add(new CityCount(s, aLong));
                }
            }); 

            cityCounts.sort(new CompareCityCount());
            ArrayList<String> resultMark = new ArrayList<>();
            Double sum = 0.0; 

            // 当前没有更多的城市数据  或者  已经找到两个城市数据了  停止循环
            while (!(cityCounts.size() == 0) && resultMark.size() < 2) {
                CityCount cityCount = cityCounts.get(0);
                resultMark.add(cityCount.name + String.format("%.2f",cityCount.count.doubleValue() / totalCount * 100) + "%");
                cityCounts.remove(0);
            } 

            // 拼接其他城市
            if (cityCounts.size() > 0) {
                resultMark.add("其他" + String.format("%.2f", 100 - sum) + "%");
            } 

            StringBuilder cityMark = new StringBuilder();
            for (String s : resultMark) {
                cityMark.append(s).append(",");
            } 

            return cityMark.substring(0, cityMark.length() - 1);
        }

        @Override
        public Encoder<Buffer> bufferEncoder() {
            return Encoders.javaSerialization(Buffer.class);
        } 

        @Override
        public Encoder<String> outputEncoder() {
            return Encoders.STRING();
        }
    }
}

SparkSQL：案例

public class SparkSQL09_Source_Req {
    public static void main(String[] args) {

        // TODO 在编码前，设定Hadoop的访问用户
        System.setProperty("HADOOP_USER_NAME","atguigu");

        final SparkSession sparkSession = SparkSession
                .builder()
                .enableHiveSupport() // TODO 启用Hive的支持
                .master("local[*]")
                .appName("SparkSQL")
                .getOrCreate();

        //sparkSession.sql("show tables").show();

//        sparkSession.sql("CREATE TABLE `user_visit_action`(\n" +
//                "  `date` string,\n" +
//                "  `user_id` bigint,\n" +
//                "  `session_id` string,\n" +
//                "  `page_id` bigint,\n" +
//                "  `action_time` string,\n" +
//                "  `search_keyword` string,\n" +
//                "  `click_category_id` bigint,\n" +
//                "  `click_product_id` bigint, --点击商品id，没有商品用-1表示。\n" +
//                "  `order_category_ids` string,\n" +
//                "  `order_product_ids` string,\n" +
//                "  `pay_category_ids` string,\n" +
//                "  `pay_product_ids` string,\n" +
//                "  `city_id` bigint --城市id\n" +
//                ")\n" +
//                "row format delimited fields terminated by '\\t';");
//
//        sparkSession.sql("load data local inpath 'data/user_visit_action.txt' into table user_visit_action;");
//
//        sparkSession.sql("CREATE TABLE `city_info`(\n" +
//                "  `city_id` bigint, --城市id\n" +
//                "  `city_name` string, --城市名称\n" +
//                "  `area` string --区域名称\n" +
//                ")\n" +
//                "row format delimited fields terminated by '\\t';");



        sparkSession.sql("CREATE TABLE `product_info`(\n" +
                "  `product_id` bigint, -- 商品id\n" +
                "  `product_name` string, --商品名称\n" +
                "  `extend_info` string\n" +
                ")\n" +
                "row format delimited fields terminated by '\\t';");

        sparkSession.sql("load data local inpath 'data/city_info.txt' into table city_info;");
        sparkSession.sql("load data local inpath 'data/product_info.txt' into table product_info;");

        sparkSession.sql("select * from city_info limit 10").show();

        // TODO 需求：
        //    Spark Core :      热门品类Top10
        //    Spark SQL  : 各区域热门商品Top3

        // TODO 1. 如果需求中有【各个XXXX】描述
        //      表述的基本含义就是相同的数据分在一个组中 ：group by
        // TODO 2. 热门：只从点击量统计 => (商品ID, 点击数量)
        // TODO 3. Top3: （组内）排序后取前3名

        /*

        区域      商品      点击数量    排序号
        -----------------------------------
        华北      鞋       5000        1
        华北      鞋       5000        1
        华北      鞋       5000        1
        华北      鞋       5000        1
        华北      鞋       5000        1
        华北      衣服     3500        2
        华北      帽子     1500        3
        东北      鞋       6000        1
        东北      手机     5500        2
        东北      电脑     5200        3

        ---------------------------------------------------------------------
        区域      商品      城市      点击数量     总的点击数量     比率      顺写号
        ---------------------------------------------------------------------
        华北      鞋       北京      5000        14000         5/14      1
        华北      鞋       天津      4000        14000         4/14      2
        华北      鞋       保定      3000        14000         3/14      3
        华北      鞋       石家庄    2000        14000         2/14      4

        1. 行转列？
        2. 如何拼接结果

        SQL不能做或不好做的功能，可以采用自定义 UDF，UDAF 函数来实现

        需求的实现方式：SQL + UDAF


         */



        // TODO 释放资源
        sparkSession.close();

    }
}

import org.apache.spark.sql.SparkSession;

public class SparkSQL09_Source_Req_1 {
    public static void main(String[] args) {

        // TODO 在编码前，设定Hadoop的访问用户
        System.setProperty("HADOOP_USER_NAME","atguigu");

        final SparkSession sparkSession = SparkSession
                .builder()
                .enableHiveSupport() // TODO 启用Hive的支持
                .master("local[*]")
                .appName("SparkSQL")
                .getOrCreate();

        sparkSession.sql("select\n" +
                "\tarea,\n" +
                "\tproduct_name,\n" +
                "\tcount(*)\n" +
                "from (\n" +
                "\tselect\n" +
                "\t\tclick_product_id,\n" +
                "\t\tcity_id\n" +
                "\tfrom user_visit_action\n" +
                "\twhere click_product_id != -1\n" +
                ") a\n" +
                "join (\n" +
                "\tselect\n" +
                "\t\tproduct_id,\n" +
                "\t\tproduct_name\n" +
                "\tfrom product_info\n" +
                ") p on a.click_product_id = p.product_id\n" +
                "join (\n" +
                "    select\n" +
                "\t\tcity_id,\n" +
                "\t\tcity_name,\n" +
                "\t\tarea\n" +
                "\tfrom city_info\n" +
                ") c on a.city_id = c.city_id\n" +
                "group by area, product_id, product_name\n" +
                "limit 10").show();


        // TODO 释放资源
        sparkSession.close();

    }
}

package com.atguigu.bigdata.sparksql;

import org.apache.spark.sql.Encoders;
import org.apache.spark.sql.SparkSession;
import org.apache.spark.sql.functions;

public class SparkSQL09_Source_Req_2 {
    public static void main(String[] args) {

        // TODO 在编码前，设定Hadoop的访问用户
        System.setProperty("HADOOP_USER_NAME","atguigu");

        final SparkSession sparkSession = SparkSession
                .builder()
                .enableHiveSupport() // TODO 启用Hive的支持
                .master("local[*]")
                .appName("SparkSQL")
                .getOrCreate();

        sparkSession.udf().register("cityRemark", functions.udaf(
                new MyCityRemarkUDAF(), Encoders.STRING()
        ));

        sparkSession.sql("select\n" +
                "\t*\n" +
                "from (\n" +
                "\tselect\n" +
                "\t\t*,\n" +
                "\t\trank() over ( partition by area order by clickCnt desc ) rk\n" +
                "\tfrom (\n" +
                "\t\tselect\n" +
                "\t\t\tarea,\n" +
                "\t\t\tproduct_name,\n" +
                "\t\t\tcount(*) clickCnt,\n" +
                "\t\t\tcityRemark(city_name) cityremark\n" +
                "\t\tfrom (\n" +
                "\t\t\tselect\n" +
                "\t\t\t\tclick_product_id,\n" +
                "\t\t\t\tcity_id\n" +
                "\t\t\tfrom user_visit_action\n" +
                "\t\t\twhere click_product_id != -1\n" +
                "\t\t) a\n" +
                "\t\tjoin (\n" +
                "\t\t\tselect\n" +
                "\t\t\t\tproduct_id,\n" +
                "\t\t\t\tproduct_name\n" +
                "\t\t\tfrom product_info\n" +
                "\t\t) p on a.click_product_id = p.product_id\n" +
                "\t\tjoin (\n" +
                "\t\t\tselect\n" +
                "\t\t\t\tcity_id,\n" +
                "\t\t\t\tcity_name,\n" +
                "\t\t\t\tarea\n" +
                "\t\t\tfrom city_info\n" +
                "\t\t) c on a.city_id = c.city_id\n" +
                "\t\tgroup by area, product_id, product_name\n" +
                "\t) t\n" +
                ") t1 where rk <= 3").show();


        // TODO 释放资源
        sparkSession.close();

    }
}

import org.apache.spark.sql.Encoders;
import org.apache.spark.sql.SparkSession;
import org.apache.spark.sql.functions;

public class SparkSQL09_Source_Req_3 {
    public static void main(String[] args) {

        // TODO 在编码前，设定Hadoop的访问用户
        System.setProperty("HADOOP_USER_NAME","atguigu");

        final SparkSession sparkSession = SparkSession
                .builder()
                .enableHiveSupport() // TODO 启用Hive的支持
                .master("local[*]")
                .appName("SparkSQL")
                .getOrCreate();

        sparkSession.udf().register("cityRemark", functions.udaf(
                new MyCityRemarkUDAF(), Encoders.STRING()
        ));

        // TODO 补全数据
        /*

		select
			area,
		    product_id,
			product_name,
			city_name
		from (
			select
				click_product_id,
				city_id
			from user_visit_action
			where click_product_id != -1
		) a
		join (
			select
				product_id,
				product_name
			from product_info
		) p on a.click_product_id = p.product_id
		join (
			select
				city_id,
				city_name,
				area
			from city_info
		) c on a.city_id = c.city_id

         */
        sparkSession.sql("select\n" +
                "\tarea,\n" +
                "\tclick_product_id,\n" +
                "\tproduct_name,\n" +
                "\tcity_name\n" +
                "from (\n" +
                "\tselect\n" +
                "\t\tclick_product_id,\n" +
                "\t\tcity_id\n" +
                "\tfrom user_visit_action\n" +
                "\twhere click_product_id != -1\n" +
                ") a\n" +
                "join (\n" +
                "\tselect\n" +
                "\t\tproduct_id,\n" +
                "\t\tproduct_name\n" +
                "\tfrom product_info\n" +
                ") p on a.click_product_id = p.product_id\n" +
                "join (\n" +
                "\tselect\n" +
                "\t\tcity_id,\n" +
                "\t\tcity_name,\n" +
                "\t\tarea\n" +
                "\tfrom city_info\n" +
                ") c on a.city_id = c.city_id").createOrReplaceTempView("t1");

        // TODO 对补全的数据分组聚合
        /*

        select
            *,
            count(*) clickCnt,
            cityRemark(city_name) cityremark
        from t1
        group by area, product_id, product_name

         */
        sparkSession.sql("select\n" +
                "\t*,\n" +
                "\tcount(*) clickCnt,\n" +
                "\tcityRemark(city_name) cityremark\n" +
                "from t1\n" +
                "group by area, product_id, product_name").createOrReplaceTempView("t2");

        // TODO 给每一行数据增加排序号
        /*

         select
            *,
            rank() over ( partition by area order by clickCnt desc ) rk
         from t2

         */
        sparkSession.sql("select\n" +
                "\t*,\n" +
                "\trank() over ( partition by area order by clickCnt desc ) rk\n" +
                "from t2").createOrReplaceTempView("t3");

        // TODO 根据排序号保留组内前3名
        sparkSession.sql("select * from t3 where rk <= 3").show();


        // TODO 释放资源
        sparkSession.close();

    }
}