SparkSQL代码案例

1、创建DataFrame的方式

package com.netcloud.bigdata.sparksql

import java.util.Properties

import org.apache.spark.sql.{SaveMode, SparkSession}

/**
  * DataFrame的创建
  * 从已经存在的RDD生成，从hive表、或者其他数据源（本地或者HDFS）
  * 1）在创建一个DataFrame时候，首先要用创建一个sparksession（sparksql程序的入口）
 *  2）通过读取其他数据源的方式 创建DataFrame
  *   a) 读取csv文件创建DataFrame
  *   b) 读取json文件创建DataFrame
  *   c) 读取txt文件创建DataFrame
  *   d) 读取parquet文件创建DataFrame
  *   e) 读取JDBC文件创建DataFrame
  * 3) 通过hive读取数据 创建DataFrame
  *   spark.sql("select * from user")
  * @author yangshaojun
  * #date  2019/3/8 16:59
  * @version 1.0
  */
object SparkSql_000_createDataFrame {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession
      .builder()
      .appName("Spark SQL basic example")
      .master("local[2]")
      .config("hadoop.home.dir", "/user/hive/warehouse")//开启对hive的支持
      .enableHiveSupport()
      .getOrCreate()

    //1、通过读取csv文件的方式 创建DataFrame; option("header",true)表示csv文件第一行是字段名(scheme信息)；
    //   读取csv文件的方式 如下几种
    val csvDF1=spark.read.option("header",true).csv("data/sparksql/bank_full.csv")// 本地文件
    //val csvDF2=spark.read.option("header",true).csv("hdfs:///ns1/data/bank_full.csv")//HDFS文件

    val csvDF3=spark.read.format("csv").option("header",true).load("data/sparksql/bank_full.csv")// 使用format指定读取的文件格式

    //2、通过读取json文件方式 创建DataFrame
    val jsonDF=spark.read.json("data/sparksql/people.json")
    //jsonDF.show()

    //3、读取parquet文件的方式 创建DataFrame
    val parquetDF=spark.read.parquet("data/parquet/*")
    //parquetDF.show()

    //4、读取text 文件创建DataFrame
    val textDF=spark.read.text("data/sparkcore/wordcount.txt")
    //textDF.show()

    //5、读取JDBC 文件创建DataFrame 然后保存到HDFS
    val url="jdbc:mysql://106.12.219.51:3306/maximai"
    val tabName="sys_user"
    val prop=new Properties()
    prop.setProperty("user","maximai")
    prop.setProperty("password","maximai")
    val jdbcDF=spark.read.jdbc(url,tabName,prop)
    jdbcDF.show()
    //保存模式 为覆盖 文件格式是csv
    //jdbcDF.write.mode(SaveMode.Overwrite).format("com.databricks.spark.csv").save("data/mysqlToHDFS/sys_user") //将mysql中的数据导入到HDFS

    //6、从Hive里面读取数据 开启对hive的支持
    spark.sql("select * from user").show()

  }
}

package com.netcloud.bigdata.sparksql

import org.apache.spark.sql.SparkSession

/**
  * DataSet的创建
  * @author yangshaojun
  * #date  2019/3/8 17:21
  * @version 1.0
  */
object SparkSql_001_createDataset {
  case class Person(name: String, age: Long)

  def main(args: Array[String]): Unit = {
    val spark = SparkSession
      .builder()
      .appName("Spark SQL basic example")
      .master("local[2]")
      .getOrCreate()
    import  spark.implicits._

    val path="data/sparksql/people.json"
    val peopleDS = spark.read.json(path).as[Person]
    peopleDS.show()
  }
}

2、DataFrame的常用操作

java版本

package com.netcloud.spark.sparksql;

import org.apache.spark.sql.*;

/**
 /**
 * java版本的DataFrame的常用操作。
 * 通常将DataFrame 存储在一张临时表中，后面通过执行sql的方式执行操作。
 *
 * @author yangshaojun
 * #date  2019/4/3 9:03
 * @version 1.0
 */
public class DataFrameNormalOperation {
    public static void main(String[] args) {

        SparkSession spark = SparkSession
                .builder()
                .appName("DataFrameNormalOperation")
                .master("local")
                .getOrCreate();
        // 等同于 SQLContext对象 sc.read().json("../.json")以及 sc.read().load(../.json)
        Dataset<Row> rowDataset = spark.read().json("data/sparksql/people.json");

        // 1、打印DataFrame数据信息
        rowDataset.show();
        // 2、打印schema信息
        rowDataset.printSchema();
        // 3、打印某列信息
        rowDataset.select("name").show();//等同于rowDataset.select(rowDataset.col("name")).show();
        // 4、查询多列信息；并且对列使用表达式操作 (df.col(columnName))
        rowDataset.select(rowDataset.col("name"), rowDataset.col("age").plus(1)).show();
        // 5、过滤出年龄大于25的people
        rowDataset.filter(rowDataset.col("age").gt(25)).show();
        rowDataset.select(rowDataset.col("name"), rowDataset.col("age"), rowDataset.col("age").gt(25)).show();
        // 6、跟根据某个字段分组 并统计总数
        rowDataset.groupBy("age").count().show();
        // 将DataFrame注册到临时表
        rowDataset.registerTempTable("people");
        // sparksession对象调用sql方法 方法参数书写sql表达式
        spark.sql("select * from people").show();

        // 将DataFrame注册全局表
        try {
            rowDataset.createGlobalTempView("peoples");
        } catch (AnalysisException e) {
            e.printStackTrace();
        }
        // sparksession对象调用sql方法 方法参数书写sql表达式
        // 全局临时视图绑定到系统保留的数据库`global_temp`
        spark.sql("select * from global_temp.peoples").show();



    }
}

 

scala版本

package com.netcloud.bigdata.sparksql

import org.apache.spark.sql.SparkSession

/**
  * scala版本的DataFrame的常用操作。
  * 通常将DataFrame 存储在一张临时表中，后面通过执行sql的方式执行操作。
  * @author yangshaojun
  * #date  2019/4/3 9:29
  * @version 1.0
  */
object SparkSql_002_DataFrameNormalOperation {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession
      .builder()
      .master("local")
      .appName("SparkSql_003_DataFrameNormalOperation")
      .getOrCreate()

    import spark.implicits._

    // 等同于 SQLContext对象 sc.read().json("../.json")以及 sc.read().load(../.json)
    val df = spark.read.json("data/sparksql/people.json")

    // 1、打印DataFrame数据信息
    df.show()

    // 2、打印schema信息
    df.printSchema()

    // 3、打印某列信息
    df.select("name")

    // 4、查询多列信息；并且对列使用表达式操作 (df.col(columnName))
    df.select(df.col("name"), df.col("age") + 1, df.col("age").plus(2)).show()
    df.select($"name",$"age".plus(3),$"age"+4).show() // $columnName 的语法需要引入spark.implicits._

    // 5、过滤出年龄大于25的people
    df.filter($"age".gt(25)).show()

    // 6、跟根据某个字段分组 并统计总数
    df.groupBy("age").count().show()

    // 将DataFrame注册到临时表
    df.registerTempTable("people")
    // sparksession对象调用sql方法 方法参数书写sql表达式
    spark.sql("select * from people").show()

    // 将DataFrame注册全局表
    df.createOrReplaceGlobalTempView("people")
    // sparksession对象调用sql方法 方法参数书写sql表达式
    // 全局临时视图绑定到系统保留的数据库`global_temp`
    spark.sql("select * from global_temp.people").show()

  }
}

3、RDD转为DataFrame的方法

java版本

package com.netcloud.spark.sparksql;

import java.io.Serializable;

/**
 * 创建Person类 并实现序列化
 * Person类中仅支持简单数据类型，不能有复杂的数据类型 像List Array 或者自定类型
 * 对于scala的样例类时可以支持Array seq等类型的
 * @author yangshaojun
 * #date  2019/4/3 11:40
 * @version 1.0
 */
public class People implements Serializable {

    private static final long serialVersionUID = 555241500840980177L;
    private String name;
    private int age;
    public People() {

    }
    public People(String name,int age) {
        this.name = name;
        this.age=age;
    }

    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;
    }

    @Override
    public String toString() {
        return "name:"+name+" "+"age:"+age;
    }
}



================================================================================

package com.netcloud.spark.sparksql;

import org.apache.spark.SparkConf;
import org.apache.spark.api.java.JavaRDD;
import org.apache.spark.sql.types.StructField;
import org.apache.spark.sql.types.StructType;
import org.apache.spark.api.java.JavaSparkContext;
import org.apache.spark.api.java.function.Function;
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.RowFactory;
import org.apache.spark.sql.SQLContext;
import org.apache.spark.sql.types.DataTypes;
import java.util.ArrayList;

import java.util.List;

/**
 * java版本使用反射和动态转换的方式将RDD转为DataFrame
 * @author yangshaojun
 * #date  2019/4/3 11:41
 * @version 1.0
 */
public class RDD2DataFrame {
    public static void main(String[] args) {

        JavaSparkContext sc = new JavaSparkContext(new SparkConf()
                .setMaster("local")
                .setAppName("RDD2DataFrame"));
        SQLContext sqlContext = new SQLContext(sc);
        JavaRDD<String> stringRDD = sc.textFile("data/sparksql/people.txt", 3);
        JavaRDD<People> peoples = stringRDD.map(new Function<String, People>() {

            @Override
            public People call(String str) throws Exception {
                String[] arrs = str.split(",");
                People people = new People(arrs[0], Integer.valueOf(arrs[1].trim()));
                return people;
            }
        });
        // 使用反射机制 将RDD转为DataFrame
        // People类必须实现序列化接口
        Dataset<Row> dataFrame = sqlContext.createDataFrame(peoples, People.class);
        // 将DataFrame注册为一个临时表；然后针对其中的数据执行SQL语句。
        dataFrame.registerTempTable("people");
        // 针对临时表执行SQL
        Dataset<Row> peopleDF = sqlContext.sql("select * from people where age>25");
        peopleDF.show();
        // 将DataFrame再次转为RDD
        JavaRDD<Row> peopleRDD = peopleDF.javaRDD();
        JavaRDD<People> res = peopleRDD.map(new Function<Row, People>() {
            @Override
            public People call(Row row) throws Exception {

                People people = new People();
                people.setAge(row.getInt(0));
                people.setName(row.getString(1));
                return people;
            }
        });
        List<People> retvalue = res.collect();
        for (People people : retvalue) {
            System.out.println(people);
        }

        /**
         *编码的方式 将RDD转为DataFrame
         */
        // 1、将 JavaRDD<String> 转为 JavaRDD<Row> 使用RowFactory.create()返回Row对象
        JavaRDD<Row> rowRDD = stringRDD.map(new Function<String, Row>() {

            @Override
            public Row call(String str) throws Exception {
                String[] arrs = str.split(",");
                String name = arrs[0];
                System.out.println(name);
                int age = Integer.valueOf(arrs[1].trim());

                return RowFactory.create(name, age);
            }
        });
        // 2、创建schema信息
        List<StructField> fields = new ArrayList<StructField>();
        StructField field = null;
        field = DataTypes.createStructField("name", DataTypes.StringType, true);
        fields.add(field);
        field = DataTypes.createStructField("age", DataTypes.IntegerType, true);
        fields.add(field);
        StructType schema = DataTypes.createStructType(fields);

        // 3 、将RDD和schema关联
        Dataset<Row> retsDF = sqlContext.createDataFrame(rowRDD, schema);
        retsDF.show();
    }
}

 

scala版本

package com.netcloud.bigdata.sparksql

import org.apache.spark.{SparkConf, SparkContext}
import org.apache.spark.sql.{Row, SparkSession}
import org.apache.spark.sql.types.{StringType, StructField, StructType}

/** scala版本的方式将RDD转为DataFrame
  * Spark SQL 支持两种不同的方法用于转换已存在的 RDD 成为 Dataset
  * 1）java 反射
  *  a)定义一个样例类；样例类定义了表的 Schema ；Case class 的参数名使用反射读取并且成为了列名
  *  b)使用toDF函数将RDD转为DataFrame
  * 2）以编程的方式指定 Schema
  *  a) 将从文本文件中创建的原始RDD使用map函数转为ROW类型的RDD。
  *  b) Step 1 被创建后，创建 Schema 表示一个 StructType(StructField(name,StringType,true),...) 匹配 RDD 中的 Rows（行）的结构。
  *  c) 通过 SparkSession 提供的 createDataFrame 方法应用 Schema 到 RDD 的 RowS（行）。
  * @author yangshaojun
  * #date  2019/3/8 17:33
  * @version 1.0
  */
object SparkSql_003_RDDtoDataFrame {
  case class Person(name:String,age:Int)
    def main(args: Array[String]): Unit = {
      val spark = SparkSession
        .builder()
        .master("local")
        .appName("SparkSql_003_DataFrameNormalOperation")
        .getOrCreate()
      import  spark.implicits._

      val rdd = spark.sparkContext.textFile("data/sparksql/people.txt")
      val peopleDF = rdd.map(_.split(",")).map(ar => Person(ar(0), ar(1).trim.toInt)).toDF()
      // 将DataFrame注册为一个view
      peopleDF.createOrReplaceTempView("people")
      val teenagersDF = spark.sql("SELECT name, age FROM people WHERE age BETWEEN 13 AND 19")
      teenagersDF.map(teenager => "Name: " + teenager(0)).show()
      // 获取某个列的值
      teenagersDF.map(teenager => "Name: " + teenager.getAs("name")).show()
      /**
        * 编码的方式将RDD转为DataFrame
        */
      val schemeString = "name age"
      // 1、使用StructType 创建schema信息
      val fields = schemeString.split(" ").map(fieldName => StructField(fieldName, StringType, nullable = true))
      val schema = StructType(fields)

      // 2、将原始的RDD[String] 转为 RDD[Row]
      val rowRDD = rdd.map(_.split(",")).map(attributes => Row(attributes(0), attributes(1).trim))
      // 3、使用createDataFrame方法将RDD和schema关联来创建DataFrame。
      val peopleDF2 = spark.createDataFrame(rowRDD, schema)
      peopleDF2.show()

    }

}

4、编程的方式加载parquet文件

object SparkSql_004_ParquetFile {
  def main(args: Array[String]): Unit = {
    val  spark=SparkSession
      .builder()
      .appName("SparkSql_004_ParquetFile")
      .master("local")
      .getOrCreate()
    // 读取parquet文件 创建一个DataFrame
    val parquetDF=spark.read.parquet("data/sparksql/people.parquet")
     // 将DataFrame注册为临时表，然后使用SQL查询需要的数据
    parquetDF.registerTempTable("people")

    // 对查询出来的DataFrame进行transform操作，处理数据，然后进行打印
    val resultDF=spark.sql("select * from people")
    resultDF.show()
    /**
      * +----+-------+
      * | age|   name|
      * +----+-------+
      * |null|Michael|
      * |  30|   Andy|
      * |  19| Justin|
      * +----+-------+
      */
    // 将DataFrame转为RDD ;然后遍历打印 name字段信息
    val result=resultDF.rdd.map(row => row(1)).collect().foreach(name => println(name))

  }
}

5、Parquet数据源自动分区推断

/**
  * parquet分区发现
  *
  * @author yangshaojun
  * #date  2019/4/8 13:15
  * @version 1.0
  */
object SparkSql_005_ParquetPartitionDiscovery {
  def main(args: Array[String]): Unit = {
    val  spark=SparkSession
      .builder()
      .appName("SparkSql_005_ParquetPartitionDiscovery")
      .master("local")
      .getOrCreate()
    // 读取parquet文件 创建一个DataFrame
    val parquetDF=spark.read.parquet("hdfs://netcloud01:9000/data/sparksql/users/gender=man/country=US/users.parquet")
    parquetDF.printSchema()
    parquetDF.show()
  }
}

6、合并 schema 

package com.netcloud.bigdata.sparksql

import org.apache.spark.sql.{SaveMode, SparkSession}

/**
  * @author yangshaojun
  * #date  2019/4/8 13:29
  * @version 1.0
  */
object SparkSql_006_ParquetMergeSchema {
  def main(args: Array[String]): Unit = {
    val  spark=SparkSession
      .builder()
      .appName("SparkSql_006_ParquetMergeSchema")
      .master("local")
      .getOrCreate()
    import spark.implicits._
    val basicData=Seq(("Andy",25),("Tome",26),("Jack",27))
    // 创建一个DataFrame 作为学生的基本信息 并写入到 parquet文件中
    val basicDF=spark.sparkContext.parallelize(basicData,2).toDF("name","age")
    basicDF.write.format("parquet").save("data/spark/sparksql/students")

    // 创建第二个DataFrame 作为学生的成绩信息 并写入到 同一个parquet文件中
    val scoreData=Seq(("ZhangSan",100),("Lisi",99),("Andy",150))
    val scoreDF=spark.sparkContext.parallelize(scoreData,2).toDF("name","score")
    scoreDF.write.mode(SaveMode.Append).format("parquet").save("data/spark/sparksql/students")

    // 首先，第一个DataFrame和第二个DataFrame的元数据肯定是不一样的
    // 一个是包含了 name和age两个列，一个包含了name 和score两个列
    // 所以这里期望的是，读取出来表的数据，自动合并两个文件的元数据，出现三个列 name 、age 、score
    // 用merageSchema的方式，读取数据进行元数据的合并。
    val mergeData= spark.sqlContext.read.option("mergeSchema","true").parquet("data/spark/sparksql/students")
    mergeData.printSchema()
    mergeData.show()
    /**
      * +--------+-----+----+
      * |    name|score| age|
      * +--------+-----+----+
      * |ZhangSan|  100|null|
      * |    Lisi|   99|null|
      * |    Andy|  150|null|
      * |    Tome| null|  26|
      * |    Jack| null|  27|
      * |    Andy| null|  25|
      * +--------+-----+----+
      */
  }
}

7、Hive数据源 

java版本

/**
 * @author yangshaojun
 * #date  2019/4/8 15:14
 * @version 1.0
 */
public class HiveDataSource {
    public static void main(String[] args) {
        SparkConf conf=new SparkConf();
        conf.setAppName("HiveDataSource");
        JavaSparkContext javaSparkContext=new JavaSparkContext(conf);
        HiveContext hiveContext=new HiveContext(javaSparkContext.sc());

        // 1、第一个功能:使用hiveContext 的sql或者hql方法；可以执行hive中能够执行的hqlQL语句

        // 判断是否存在students_info 表，如果存在就删除
        hiveContext.sql("DROP TABLE IF EXISTS  students_info");
        // 如果不存在，则创建这张表
        hiveContext.sql("CREATE TABLE IF NOT EXISTS  students_info (name STRING,age INT)");
        // 将学生基本信息导入 students_info 表中
        hiveContext.sql("LOAD DATA LOCAL INPATH '/data/sparksql/students.txt' INTO TABLE students_info");

        // 用同样的方式给 scores 导入数据
        hiveContext.sql("DROP TABLE IF EXISTS  students_score");
        // 如果不存在，则创建这张表
        hiveContext.sql("CREATE TABLE IF NOT EXISTS  students_score (name STRING,score INT)");
        // 将分数信息导入 students_score 表中
        hiveContext.sql("LOAD DATA LOCAL INPATH '/data/sparksql/scores.txt' INTO TABLE students_score");

        // 2、 第二个功能 执行sql返回一个DataFrame 用于查询。
        // 执行sql查询 关联两张表。查询出学生成绩大于80分的学生
        Dataset<Row> goodStudentDF = hiveContext.sql("select t1.name;t1.age,t2.score  " +
                "from students_info t1 join " +
                "students_score t2 on t1.name=t2.name " +
                "where score > =80 ");

        // 3、第三个功能 使用saveAsTable()方法 将DataFrame中的数据保存到hive表中。
        // 将好学生的信息保存到 goodstudent_info表中。
        hiveContext.sql("DROP TABLE IF EXISTS  goodstudent_info");
        goodStudentDF.write().saveAsTable("goodstudent_info");

        //4、第四个功能  可以使用table() 方法 针对hive表 直接创建DataFrame
        // 然后针对goodstudent_info表直接创建 DataFrame
        hiveContext.table("goodstudent_info");

    }
}

 

scala版本

/**
  * Hive 数据源
  * @author yangshaojun
  * #date  2019/4/8 15:01
  * @version 1.0
  */
object SparkSql_007_HiveDataSource {
  def main(args: Array[String]): Unit = {
    val  spark=SparkSession
      .builder()
      .appName("SparkSql_007_HiveDataSource")
      .getOrCreate()
    // 通过sparkContext 创建 HiveContext对象
    val hiveContext=new HiveContext(spark.sparkContext)

    // 1、第一个功能:使用hiveContext 的sql或者hql方法；可以执行hive中能够执行的hqlQL语句
    // 判断是否存在students_info 表，如果存在就删除
    hiveContext.sql("DROP TABLE IF EXISTS  students_info")
    // 如果不存在，则创建这张表
    hiveContext.sql("CREATE TABLE IF NOT EXISTS  students_info (name STRING,age INT)")
    // 将学生基本信息导入 students_info 表中
    hiveContext.sql("LOAD DATA LOCAL INPATH '/data/sparksql/students.txt' INTO TABLE students_info")

    // 用同样的方式给 scores 导入数据
    hiveContext.sql("DROP TABLE IF EXISTS  students_score")

    hiveContext.sql("CREATE TABLE IF NOT EXISTS  students_score (name STRING,score INT)")
    // 将分数信息导入 students_score 表中
    hiveContext.sql("LOAD DATA LOCAL INPATH '/data/sparksql/scores.txt' INTO TABLE students_score")

    // 2、 第二个功能 执行sql返回一个DataFrame 用于查询。
    // 执行sql查询 关联两张表。查询出学生成绩大于80分的学生
    val goodStudentDF = hiveContext.sql("select t1.name;t1.age,t2.score  " + "from students_info t1 join " + "students_score t2 on t1.name=t2.name " + "where score > =80 ")

    // 3、第三个功能 将DataFrame中的数据保存到hive表中。
    // 将好学生的信息保存到 goodstudent_info表中。
    hiveContext.sql("DROP TABLE IF EXISTS  goodstudent_info")
    goodStudentDF.write.saveAsTable("goodstudent_info")

    //4、第四个功能  可以使用table方法 针对hive表 直接创建DataFrame
    // 然后针对goodstudent_info表直接创建 DataFrame
    val retDF=hiveContext.table("goodstudent_info");
    for( ret <- retDF){
      println(ret)
    }
  }
}

8、JDBC 数据源 

package com.netcloud.spark.sparksql;

import org.apache.spark.SparkConf;
import org.apache.spark.api.java.JavaSparkContext;
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SQLContext;
import org.apache.spark.sql.SparkSession;
import org.apache.spark.sql.hive.HiveContext;

import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

/**
 * java 版本 使用JDBC数据源 创建DataFrame
 * @author yangshaojun
 * #date  2019/4/8 17:22
 * @version 1.0
 */
public class JDBCDataSource {
    public static void main(String[] args) {
        SparkConf conf=new SparkConf();
        conf.setAppName("JDBCDataSource").setMaster("local");
        JavaSparkContext javaSparkContext=new JavaSparkContext(conf);
        SQLContext sqlContext=new SQLContext(javaSparkContext);

        // 方式一: 将配置文件放在Map中。
        Map<String,String> options=new HashMap<String,String>();
        options.put("url","jdbc:mysql://106.12.219.66:3306/test");
        options.put("dbtable","test");
        options.put("user","test");
        options.put("password","test");
        Dataset<Row> jdbcDF = sqlContext.read().format("jdbc").options(options).load();
        jdbcDF.show();

        // 等同于 如下的方式
        // 方式二: 将配置文件放在Properties中。
        String url = "jdbc:mysql://106.12.219.66:3306/test";
        String tabName = "test";
        Properties prop = new Properties();
        prop.setProperty("user", "test");
        prop.setProperty("password", "test");
        Dataset<Row> jdbcDFOther = sqlContext.read().jdbc(url, tabName, prop);
        jdbcDFOther.show();


    }
}
=========================================================================================
package com.netcloud.bigdata.sparksql

import java.util.Properties

import org.apache.spark.sql.SparkSession

/**
  * scala版本 JDBC 数据源
  *
  * @author yangshaojun
  * #date  2019/4/8 17:02
  * @version 1.0
  */
object SparkSql_008_JDBCDataSource {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession
      .builder()
      .master("local")
      .appName("SparkSql_008_JDBCDataSource")
      .getOrCreate()

    // 读取JDBC 文件创建DataFrame 然后保存到HDFS
    val url = "jdbc:mysql://106.12.219.66:3306/test"
    val tabName = "test"
    val prop = new Properties()
    prop.setProperty("user", "test")
    prop.setProperty("password", "test")
    val jdbcDF = spark.read.jdbc(url, tabName, prop)
    jdbcDF.show()

    // 等同于下面的加载方式
    val jdbcDFother =spark.read.format("jdbc")
      .options(Map(
        "url" -> "jdbc:mysql://106.12.219.66:3306/test",
        "dbtable" -> "test",
        "user" -> "test",
        "password" -> "test"
      )).load()
    jdbcDFother.select("username").show()

  }

}

10、SparkSQL内置函数

package com.netcloud.bigdata.sparksql

import org.apache.spark.sql.types._
import org.apache.spark.sql.{Row, SparkSession}
import org.apache.spark.sql.functions._
/**
  * 根据用户每天的访问和购买日志，统计每天的uv和销售额。
  * @author yangshaojun
  * #date  2019/4/8 17:57
  * @version 1.0
  */
object SparkSql_009_DailyUV {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession
      .builder()
      .master("local")
      .appName("SparkSql_009_DailyUV")
      .getOrCreate()

    // 要使用SparkSQL的内置函数，就必须导入SQLContext的隐式转换
    import spark.sqlContext.implicits._

    // 构造用户访问日志 并创建DataFrame
    // 日志用逗号隔开 第一列 日期;第二列 用户id ;第三列 销售额 sale
    val userAccessLog=Array(
      "2019-10-01,1122,23.15",
      "2019-10-01,1123,34.5",
      "2019-10-01,1122,45.6",
      "2019-10-02,1122,44,5",
      "2019-10-02,1122,56,5",
      "2019-10-01,1122,66.6",
      "2019-10-02,1123,12,4",
      "2019-10-01,1123,77.6",
      "2019-10-02,1124,23.8"
    )
    // 将模拟出来的用户访问日志RDD 转为DataFrame
    // 首先将普通的RDD 转为元素为Row的RDD
    val userAccessRDD=spark.sparkContext.parallelize(userAccessLog,2)
    val userAccessRowRDD=userAccessRDD.map(log => Row(log.split(",")(0),log.split(",")(1).toInt))

    // 构造DataFrame的元数据
    val structType=StructType(Array(StructField("date",StringType,true),StructField("userid",IntegerType,true)))
   val  userAccessRowDF=spark.createDataFrame(userAccessRowRDD,structType);

    // 接下来使用SparkSQL中的内置函数
    /**
      * 内置函数的用法
      * 1）对DataFrame调用groupBy() 对某一列进行分组
      * 2）然后调用 agg()方法 第一个参数是必须的 即:传入之前在groupBy()方法中出现的字段
      * 3）第二个参数 传入countDistinct 、sum、first等spark内置的函数。
      * 内置函数中，传入的参数，也是用单引号作为前缀的。
      */
    userAccessRowDF.groupBy("date").agg('date, countDistinct('userid)).rdd
      .map(row => Row(row(1), row(2)))
      .collect()
      .foreach(print)


    /**
      * 统计每天的销售额
      */
    val userAccessRowSaleRDD=userAccessRDD.map(log => Row(log.split(",")(0),log.split(",")(2).toDouble))
    // 构造DataFrame的元数据
    val schema=StructType(Array(StructField("date",StringType,true),StructField("sale",DoubleType,true)))
    val  userAccessRowSaleDF=spark.createDataFrame(userAccessRowSaleRDD,schema);

    // 接下来使用SparkSQL中的内置函数
    /**
      * 内置函数的用法
      * 1）对DataFrame调用groupBy() 对某一列进行分组
      * 2）然后调用 agg()方法 第一个参数是必须的 即:传入之前在groupBy()方法中出现的字段
      * 3）第二个参数 传入countDistinct 、sum、first等spark内置的函数。
      * 内置函数中，传入的参数，也是用单引号作为前缀的。
      */
    userAccessRowSaleDF.groupBy("date").agg('date, sum('sale)).rdd
      .map(row => Row(row(1), row(2)))
      .collect()
      .foreach(print)
  }
}

开窗函数：

package com.netcloud.spark.sparksql;

import org.apache.spark.SparkConf;
import org.apache.spark.api.java.JavaSparkContext;
import org.apache.spark.sql.hive.HiveContext;

/**
 *
 * @author yangshaojun
 * #date  2019/4/8 21:38
 * @version 1.0
 */
public class RowNumberWindowFunction {
    public static void main(String[] args) {

        SparkConf conf = new SparkConf().setAppName("RowNumberWindowFunction");
        JavaSparkContext javaSparkContext = new JavaSparkContext(conf);
        HiveContext hiveContext = new HiveContext(javaSparkContext.sc());
        // 删除销售额表, sales表
        hiveContext.sql("DROP TABLE IF EXISTS sales");
        hiveContext.sql("CREATE TABLE IF NOT EXISTS sales (product String,category String,revenue BIGINT )");
        hiveContext.sql("LOAD DATA LOCAL INPAT '/usr/local/sales.txt INTO TABLE sales'");

        // 开始编写统计逻辑：使用 row_number() 开窗函数
        // row_number()函数的作用: 给每个分组的数据，按照其顺序排序，然后打上一个分组的行号
        hiveContext.sql("SELECT product,category,revenue"
                + "FROM ("
                    + "SELECT "
                        + "product,"
                        + "category,"
                        + "revenue,"
                        + "row_number() OVER (PARTITION BY category ORDER BY revenue DESC) rank"
                +")"
                + " WHERE rank<=3");

    }
}
======================================================================================
package com.netcloud.bigdata.sparksql


import org.apache.spark.sql.SparkSession
import org.apache.spark.sql.hive.HiveContext

/**
  * @author yangshaojun
  * #date  2019/4/8 21:39
  * @version 1.0
  */
object SparkSql_010_RowNumberWindowFunction {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession
      .builder()
      .master("local")
      .appName("SparkSql_010_RowNumberWindowFunction")
      .getOrCreate()

    val hiveContext = new HiveContext(spark.sparkContext);
    // 删除销售额表, sales表
    hiveContext.sql("DROP TABLE IF EXISTS sales")
    hiveContext.sql("CREATE TABLE IF NOT EXISTS sales (product String,category String,revenue BIGINT )")
    hiveContext.sql("LOAD DATA LOCAL INPAT '/usr/local/sales.txt INTO TABLE sales'")

    // 开始编写统计逻辑：使用 row_number() 开窗函数
    // row_number()函数的作用: 给每个分组的数据，按照其顺序排序，然后打上一个分组的行号
    hiveContext.sql("SELECT product,category,revenue" +
      "FROM ("
      + "SELECT "
      + "product,"
      + "category,"
      + "revenue,"
      + "row_number() OVER (PARTITION BY category ORDER BY revenue DESC) rank"
      + ")"
      + " WHERE rank<=3").w


  }

}

11、SparkSQL UDF 自定义函数的使用

package com.netcloud.bigdata.sparksql

import org.apache.spark.sql.types.{StringType, StructField, StructType}
import org.apache.spark.sql.{Row, SparkSession}

/**
  * 用户自定义函数
  * @author yangshaojun
  * #date  2019/4/9 16:50
  * @version 1.0
  */
object SparkSql_012_UDF {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession
      .builder()
      .master("local")
      .appName("SparkSql_012_UDF")
      .getOrCreate()
    // 构造模拟数据
    val arr=Array("Tom","Jerry","PeiQi","Andy")
    val strRDD=spark.sparkContext.parallelize(arr,2)
    // 将 RDD(String) 转为 RDD(Row)
    val rowRDD=strRDD.map(name => Row(name))

    // 动态转换的方式创建 schema 将RDD转为 DataFrame
    val schema=StructType(Array(StructField("name",StringType,true)))
    // 将RowRDD 转为DataFrame 然后将其注册到临时表中
    spark.createDataFrame(rowRDD,schema).registerTempTable("names")

    // 定义和注册自定义函数
    // 定义函数: 自己写匿名函数
    // 注册函数： SQLContext.udf.register()
    spark.udf.register("strLen",(str:String) => str.length)

    // 使用自定义函数
    spark.sql("select name , strLen(name) from names")
      .collect()
      .foreach(println)

  }

}

12、SparkSQL UDAF 自定义聚合函数的使用

package com.netcloud.bigdata.sparksql

import org.apache.spark.sql.{Row, SparkSession}
import org.apache.spark.sql.expressions.{MutableAggregationBuffer, UserDefinedAggregateFunction}
import org.apache.spark.sql.types._

/**
  * 用户自定义聚合函数
  *
  * @author yangshaojun
  * #date  2019/4/9 17:21
  * @version 1.0
  */
object SparkSql_013_UDAFStringCount {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession
      .builder()
      .master("local")
      .appName("SparkSql_012_UDF")
      .getOrCreate()
    // 构造模拟数据
    val arr=Array("Tom","Jerry","PeiQi","Andy","Tom","Andy","Tom")
    val strRDD=spark.sparkContext.parallelize(arr,2)
    // 将 RDD(String) 转为 RDD(Row)
    val rowRDD=strRDD.map(name => Row(name))

    // 动态转换的方式创建 schema 将RDD转为 DataFrame
    val schema=StructType(Array(StructField("name",StringType,true)))
    // 将RowRDD 转为DataFrame 然后将其注册到临时表中
    spark.createDataFrame(rowRDD,schema).registerTempTable("names")

    // 注册自定义聚合函数
    // 参数一：自定义聚合函数名称
    // 参数二： 继承UserDefinedAggregateFunction类的子类实例
    spark.udf.register("strCount",new StringCount)

    // 使用自定义函数
    spark.sql("select name,strCount(name)  from names group by name")
      .collect()
      .foreach(println)

    spark.stop()
  }

}

// 用户自定义聚合函数
class StringCount extends UserDefinedAggregateFunction {

  // inputSchema 指的是 输入数据的类型
  override def inputSchema: StructType = {
    StructType(Array(StructField("str", StringType, true)))
  }

  // bufferSchema 指的是 中间进行聚合时 所处理的数据类型
  override def bufferSchema: StructType = {
    StructType(Array(StructField("count", IntegerType, true)))
  }

  // dataType 指的是 函数返回值的数据类型
  override def dataType: DataType = {
    IntegerType
  }

  override def deterministic: Boolean = {
    true
  }

  // 为每个分组的数据进行初始化操作
  override def initialize(buffer: MutableAggregationBuffer): Unit = {
    buffer(0) = 0
  }

  // 指的是 每个分组有新的值进来的时候，如何进行分组对应的聚合值的计算
  override def update(buffer: MutableAggregationBuffer, input: Row): Unit = {
    buffer(0) = buffer.getAs[Int](0) + 1
  }

  // 由于spark是分布式的，所以一个分组的数据，可能在不同的节点上进行局部的聚合
  // 但是最后一个分组 在各个节点上的聚合值要进行Merage 也就是合并。
  override def merge(buffer1: MutableAggregationBuffer, buffer2: Row): Unit = {
    buffer1(0) = buffer1.getAs[Int](0) + buffer2.getAs[Int](0)
  }

  // 最后 一个分组的聚合值，如何通过中间的缓存聚合值，最后返回一个最终的聚合值
  override def evaluate(buffer: Row): Any = {
    buffer.getAs[Int](0)
  }
}

13、SparkSQL实战

package com.netcloud.spark.sparksql;

import com.netcloud.spark.utils.PropertiesUtil;
import org.apache.spark.SparkConf;
import org.apache.spark.api.java.JavaPairRDD;
import org.apache.spark.api.java.JavaRDD;
import org.apache.spark.api.java.JavaSparkContext;
import org.apache.spark.api.java.function.Function;
import org.apache.spark.api.java.function.PairFunction;
import org.apache.spark.broadcast.Broadcast;
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.RowFactory;
import org.apache.spark.sql.SQLContext;
import org.apache.spark.sql.types.*;
import scala.Tuple2;

import java.util.*;

/**
 * @author yangshaojun
 * #date  2019/4/10 13:32
 * @version 1.0
 */
public class ProjectPractice {
    public static void main(String[] args) {
        SparkConf conf =new SparkConf().setAppName("ProjectPractice");
        PropertiesUtil p = new PropertiesUtil("config.properties");
        String isLocal = p.readProperty("isLocal");

        JavaSparkContext sc=null;
        JavaRDD<String> rawRDD=null;

        // 判断是否在本地运行
        if(isLocal!=null && isLocal.equals("true")){
            conf.setMaster("local");
            sc=new JavaSparkContext(conf);
            rawRDD= sc.textFile("data/sparksql/keyword.txt");
        }else{
            sc=new JavaSparkContext(conf); 针对hdfs上的日志，创建一个RDD
            rawRDD= sc.textFile("hdfs://netcloud01:9000/data/sparksql/keyword.txt");

        }

        // 伪造一份数据 作为查询条件
        // 在实际的项目开发中，很可能这个查询条件是从J2EE平台插入到某个Mysql表中的
        // 然后呢 通常会用Spring框架和orm框架（mybatis） 去提取Mysql表的查询条件。
        Map<String,List<String>> queryParamMap=new HashMap<String,List<String>>();
        queryParamMap.put("city",Arrays.asList("beijing"));
        queryParamMap.put("platform",Arrays.asList("android"));
        queryParamMap.put("version",Arrays.asList("1.0","1.2","1.5","2.0"));

        // 根据实现思路的分析，这里最适合的方式就是，将查询参数map封装为一个broadcast广播变量
        // 这样可以进行优化，每个work节点，就拷贝一份 而不是每个task副本只有一份
        Broadcast<Map<String,List<String>>> queryParamMapBroadcast=sc.broadcast(queryParamMap);

        // 数据筛选 使用查询参数广播变量，进行筛选
        JavaRDD<String> filterRDD = rawRDD.filter(new Function<String, Boolean>() {
            @Override
            public Boolean call(String lineLog) throws Exception {

               String[] logSplited= lineLog.split("\t");//将每行的日志信息 按照制表符分割
                String city=logSplited[3];
                String platform=logSplited[4];
                String version=logSplited[5];
                // 与查询条件进行比对
                Map<String, List<String>> queryParamMap = queryParamMapBroadcast.value();//获取广播变量的值
                List<String> cities = queryParamMap.get("city");

                if(cities.size()>0 && ! cities.contains(city)){
                 return false;
                }
                List<String> platforms = queryParamMap.get("platform");
                if(platforms.size()>0 && ! platforms.contains(platform)){
                    return false;
                }
                List<String> versions = queryParamMap.get("version");
                if(versions.size()>0 && ! versions.contains(version)){
                    return false;
                }

                return true;
            }
        });

        // 将原始过滤出来的RDD 映射为 （日期_搜索词，用户）格式
        JavaPairRDD<String, String> datekeyWordUserRDD = filterRDD.mapToPair(new PairFunction<String, String, String>() {

            @Override
            public Tuple2<String, String> call(String lineLog) throws Exception {
                String[] splits = lineLog.split("\t");
                String date = splits[0];
                String user = splits[1];
                String keyWord = splits[2];
                return new Tuple2<String, String>(date + "_" + keyWord, user);
            }
        });

        // 进行分组，获取每天每个搜索词，由哪些用户搜索了(没有去重)
        JavaPairRDD<String, Iterable<String>> datekeyWordUsersRDD = datekeyWordUserRDD.groupByKey();
        // 对每天每个搜索词的搜索用户，执行去重操作，获得uv
        JavaPairRDD<String, Long> dateKeyWordUVRDD = datekeyWordUsersRDD.mapToPair(new PairFunction<Tuple2<String, Iterable<String>>, String, Long>() {

            @Override
            public Tuple2<String, Long> call(Tuple2<String, Iterable<String>> dateKeyWordsUsers) throws Exception {

                String dateKeyWord=dateKeyWordsUsers._1;
                Iterator<String> users = dateKeyWordsUsers._2.iterator();
                // 对用户进行去重，并统计去重后的数据
                List<String> distinctUsers=new ArrayList<String>();
                while(users.hasNext()){
                    String user=users.next();
                    if(!distinctUsers.contains(user)){
                        distinctUsers.add(user);
                    }
                }
                // 获取去重后的uv
                long uv=distinctUsers.size();
                return new Tuple2<String,Long>(dateKeyWord,uv);
            }
        });

        // 将每天每个搜索词的uv，转为DataFrame
        SQLContext sqlContext=new SQLContext(sc);
        JavaRDD<Row> madateKeyWordUVRowRDD= dateKeyWordUVRDD.map(new Function<Tuple2<String, Long>, Row>() {

            @Override
            public Row call(Tuple2<String, Long> dataKeyWordUv) throws Exception {
                String date = dataKeyWordUv._1.trim().split("_")[0];
                String keyword = dataKeyWordUv._1.trim().split("_")[1];
                long uv = dataKeyWordUv._2;
                return RowFactory.create(date, keyword, uv);
            }
        });

        List<StructField> structFields=Arrays.asList(
                DataTypes.createStructField("date",DataTypes.StringType,true),
                DataTypes.createStructField("keyword",DataTypes.StringType,true),
                DataTypes.createStructField("uv",DataTypes.LongType,true));
        StructType schema=DataTypes.createStructType(structFields);
        Dataset<Row> datekeyWordUvDF = sqlContext.createDataFrame(madateKeyWordUVRowRDD, schema);

        // 使用sparkSQL的开窗函数 统计每天搜索uv排名前3的热点搜索词
        datekeyWordUvDF.registerTempTable("daily_keyword_uv");
        Dataset<Row> dailyTop3KeyWordDF = sqlContext.sql("select date,keyword,uv " +
                "from (" +
                "select date,keyword,uv,row_number() OVER (PARTITION BY date ORDER BY uv DESC)  rank  " +
                "       from daily_keyword_uv " +
                ") " +
                "tmp  where rank<=3");

        dailyTop3KeyWordDF.show();
        sc.stop();
    }
}