sparkSQL笔记

最新推荐文章于 2024-11-06 14:47:09 发布

99滴神

最新推荐文章于 2024-11-06 14:47:09 发布

阅读量206

点赞数

分类专栏： spark 文章标签： spark scala big data

本文链接：https://blog.csdn.net/li1351026473/article/details/120146642

版权

spark 专栏收录该内容

12 篇文章 1 订阅

订阅专栏

本文深入探讨了Spark SQL的DataFrame API，包括创建SparkSession、DataFrame的基本操作、数据加载与落地到HDFS或Hive、与Hive的整合。此外，还介绍了DataFrame的转换方法，如新增和修改列，以及DataFrame与RDD、DataSet之间的相互转换。最后，讨论了SparkSQL的函数和自定义函数的使用。

摘要由CSDN通过智能技术生成

SparkSQL笔记

一、DataFrame

DataFrame：理解了RDD，DataFrame就容易理解些，RDD是一个数据集，DataFrame在RDD的基础上加了Schema（描述数据的信息，可以认为是元数据，DataFrame曾经就有个名字叫SchemaRDD）。

1.1、 SparkSQL基本编程

1.1.1、SparkSession的创建

1、在spark-shell中会自动创建SparkContext和SparkSession

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-I6Jp8cqn-1630938702481)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\image-20210617193335318.png)]

2、在IDEA中创建SparkSession

步骤：

1、首先导入spark-sql依赖

<dependency>
     <groupId>org.apache.spark</groupId>
     <artifactId>spark-sql_2.11</artifactId>
     <version>2.2.0</version>
</dependency>

2、然后创建SparkSession

val spark = SparkSession.builder().appName("SparkSQLOps").master("local[*]").getOrCreate

1.1.2、注意点：

1、SparkSession依赖SparkContext,SparkContext只能创建一个，故SparkSession 只能有一个

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-F2E9Vivg-1630938702483)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\image-20210617193021336.png)]

String都能编程StringContext

新增一列，使用witchColumn函数，一般会导入import org.apache.spark.sql.functions._

修改现有列，withColumnRenamed函数

实际上DataFrame 本质上是DataSet[Row]的别名

Row对象

1.2、DataFrame的表头操作

1.2.1、总结：

1、spark推荐的文件格式是(.parquet)

2、实际上DataFrame 本质上是DataSet[Row]的别名

type DataFrame = Dataset[Row]

3、读取json和text的区别:

a):json会进行schema解析,而 text不会,知识将源文件整个一行作为odf的一行,schema只有一列"value"

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ElShoJEW-1630938702485)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\image-20210617195750867.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BZgnNsuO-1630938702487)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\image-20210617195707757.png)]

4、读取csv

读取csv默认是",“分隔,schema为”_c0…"

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3nrcTuhJ-1630938702489)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\image-20210617200037655.png)]

1.2.2、有关表头的操作

1.2.2.1、新增一列 (此时使用的是使用现有的列来增加)

$f i e l d N a m e 是 c o l u m n 类型，也就是说$ +字符串，其实就是column类型，但是呢一般需要导入隐式的转换配合 import spark.implicits._

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Xj56chyZ-1630938702490)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\image-20210617200726270.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nU7VfIZ5-1630938702491)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\image-20210617201138120.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WQRayLxu-1630938702492)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\image-20210617201155841.png)]

1.2.2.2、新增一列，并给默认的值1

使用的是lit方法

需要提前导入包 org.apache.spark.sql.functions.lit

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-We1wy6Sn-1630938702493)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\image-20210617202821020.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-R0cNSY8j-1630938702494)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\image-20210617202447903.png)]

1.2.2.3、新增一列-进阶操作

使用col、column,这两个方法是等价的，参数相同，返回的也都是column类型。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-W8LylGRy-1630938702494)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\image-20210617204114309.png)]

1.2.2.4、修改现有列

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aWm41MvY-1630938702495)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\image-20210617201657581.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UFQzGniy-1630938702496)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\image-20210617201641730.png)]

1.2.2.5、完整代码如下：

package day06

import org.apache.spark.sql.{DataFrame, SparkSession}
import utils.MyApp

object SparkSqlDemo01 extends MyApp{
  val spark = SparkSession.builder().appName("SparkSQLOps").master("local[*]").getOrCreate()
  //临时调整为本地文件系统来读取相对路径上的目录
  spark.sparkContext.hadoopConfiguration.set("fs.defaultFS","file:///")
  //加载数据
  val pdf:DataFrame = spark.read.json("src\\main\\data\\sparkSql\\people.json")
  pdf.printSchema() //二维表结构 也可以说是打印表头schema
  pdf.show()  //输出数据内容 select * from table  也就是输出数剧 默认20行

  println("pdf2=>")
  //txt读取
  private val pdf2: DataFrame = spark.read.text("src\\main\\data\\sparkSql\\people.txt")
  pdf2.printSchema()
  pdf2.show()

  println("pdf3=>")
  //csv读取
  private val pdf3: DataFrame = spark.read.csv("src\\main\\data\\sparkSql\\people.txt")
  pdf3.printSchema()
  pdf3.show()

//  pdf.select("name","age").show() //方式一  //具体的查询 select name, age from tbl
//
//  import spark.implicits._
//  pdf.select($"name",$"age").show()  //方式二
  
  println("pdf4=>")
  //新增一列 ---  通过 withColum 方法
  import spark.implicits._
  //$fieldName 是 column类型，也就是说$+字符串(最好是已有的一列)，其实就是column类型，但是呢一般需要导入隐式的转换配合 import spark.implicits._
  //$ 是一个方法，隐式类为String 增强了一个功能 $方法，另外String都能隐式的变成StringContext
  //col column 方法是等价的，返回的都是column类型 这两个的功能是一样的
  private val pdf4: DataFrame = pdf3.withColumn("name", $"_c0")
  pdf4.printSchema()
  pdf4.show()

  println("pdf5=>")
  //修改现有的列
  private val pdf5: DataFrame = pdf3.withColumnRenamed("_c0", "name")
    .withColumnRenamed("_c1","age")
  pdf5.printSchema()
  pdf5.show()

  println("pdf6=>")
  //新增加一列,给默认值(可以自己指定值)
  import org.apache.spark.sql.functions._
  //lit(0),给每一行赋一个常量,返回是column类型 表示一列
  private val pdf6: DataFrame = pdf5.withColumn("scores", lit(1))
  pdf6.printSchema()
  pdf6.show()

  println("pdf7=>")
  //新增一列
  //进阶操作
  //$ 是一个方法，隐式类为String 增强了一个功能 $方法，另外String都能隐式的变成StringContext
  //col column 方法是等价的，返回的都是column类型 $ col column这三个函数的功能是一样的
  private val pdf7: DataFrame = pdf3.withColumn("name", col("_c0"))
    .withColumn("age", column("_c1"))
    .withColumn("scores", pdf3.apply("_c1"))
  pdf7.printSchema()
  pdf7.show()

  //以下是DataFrame的简单查询
  // $ 返回的一个Column对象
  //查询年龄大于20的人
  pdf6.where("age>20").show()   //没有结果，因为age字段中的值前面有空格，故有了下面的改进
  pdf6.withColumn("age",trim($"age").cast("int")).where("age>20").show()

  //查询年龄大于20的人的姓名
  //gt方法等价于 >方法  lt方法等价于<方法
  pdf6.withColumn("age",trim($"age").cast("int")).where("age>20").select("name").show()
  pdf6.withColumn("age",trim($"age").cast("int")).where($"age".gt(20)).select($"name").show()

  //pdf中的数据按照province分组 查每个省份有多少人  select count(1) from tb group by province
  pdf.groupBy("province").count().show()

  //pdf.registerTempTable()//在spark2.0之后处于维护状态，使用createOrReplaceTempView
  /*
      从使用范围上说，分为global和非global
          global是当前SparkApplication中可用，非global只在当前SparkSession中可用
      从创建的角度上说，分为createOrReplace和不Replace
          createOrReplace会覆盖之前的数据
          create不Replace，如果视图存在，会报错
   */

  //纯SQL实现上面需求
//  pdf.registerTempTable("people")  //已过时
  pdf.createOrReplaceTempView("people") //在纯SQL环境中存在一个临时表,SparkSession退出即消失
  spark.sql(
    """
      |select province,count(1) as count
      |from people
      |group by province
      |""".stripMargin).show()
}

1.3、SparkSQL的数据加载和落地

1.3.1、数据加载

读取mysq中的数据

命令格式：

有两种方式，哪一种都可以

1、spark.read.xxx 
   默认加载的文件格式为parquet
2、spark.read.format("csv").load
  解释：根据所有DataSourceRegister的shortName和你输入format进行比较,返回相应的DataSource

package day06

import org.apache.spark.sql.{DataFrame, SparkSession}
import utils.MyApp

import java.util.Properties

object JDBCReaderDemo06 extends MyApp{

  val spark: SparkSession = SparkSession.builder().master("local[*]").appName("SparkSQL").getOrCreate()

  //本机上的mysql
  val url1 = "jdbc:mysql://localhost:3306/testdb?useSSL=false"
  val password1 = "123456"
  val table1 = "emp"
  //虚拟机中的mysql
  val url = "jdbc:mysql://192.168.1.101:3306/test1?useSSL=false"
  val username = "root"
  val password = "Root123#"
  val table = "sale"
  val properties = new Properties()
  properties.put("user",username)
  properties.put("password",password)

  val df: DataFrame = spark.read.jdbc(url, table, properties)
  df.printSchema()
  df.show()

}

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eYFjyoUu-1630938702497)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624066306325.png)]

1.3.2、数据落地

落地到本地或者mysql

val spark: SparkSession = SparkSession.builder().master("local[*]").appName("SparkSQL").getOrCreate()

  import spark.implicits._
  val pdf = Seq("a"->1,"b"->2).toDF("k","v")
  //暂时修改为从本地系统中读取
  spark.sparkContext.hadoopConfiguration.set("fs.defaultFS","file:///")
  pdf.write.save("DFData")  //若是DFData已经存在了，会给提示的，此时可以使用mode方法
  //默认加载的文件格式为parquet
  //若是想指定读取特定的文件格式,可以使用format
  private val pdf2: DataFrame = spark.read.load("DFData")

  //hive默认加载的文件格式是 orc
  //spark默认加载的文件格式是parquet
  pdf2.show()

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JCwtJKht-1630938702498)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624020964099.png)]

解决方法：使用mode方法，方法参数如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mk7l3t5X-1630938702499)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624021097787.png)]

修正后的代码如下：

package day06

import org.apache.spark.sql.{DataFrame, SaveMode, SparkSession}
import utils.MyApp

import java.util.Properties

object DataFrameWriter07 extends MyApp{
  val spark: SparkSession = SparkSession.builder().master("local[*]").appName("SparkSQL").getOrCreate()

  import spark.implicits._
  val pdf = Seq("a"->1,"b"->2).toDF("k","v")
  /**
   * save 到 本地系统
   */

  //暂时修改为从本地系统中读取
  spark.sparkContext.hadoopConfiguration.set("fs.defaultFS","file:///")
  pdf.write.mode(SaveMode.Overwrite).save("DFData")  //若是DFData已经存在了，会给提示的，此时可以使用mode方法
  //默认加载的文件格式为parquet
  //若是想指定读取特定的文件格式,可以使用format
  private val pdf2: DataFrame = spark.read.load("DFData")
  //hive默认加载的文件格式是 orc
  //spark默认加载的文件格式是parquet
  pdf2.show()
  /**
   * save 到 mysql
   */
  val url="jdbc:mysql://localhost:3306/testdb"
  val username="root"
  val passwd="123456"
  val table="kv"
  val properties=new Properties()
  properties.put("user",username)
  properties.put("password",passwd)
  pdf2.write.mode(SaveMode.Append).jdbc(url,table,properties)
}

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xbNqvXl2-1630938702500)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624066449795.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ORPvPRuZ-1630938702501)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624066491864.png)]

1.4、DataFrame的简单操作

1.4.1、查询年龄大于20的人

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-V9y4xSSX-1630938702501)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\image-20210617210030679.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4tw7yxhY-1630938702502)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\image-20210617210051305.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WRuyWVBR-1630938702504)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\image-20210617210121132.png)]

1.4.2、查询年龄大于20的人的姓名

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kpeMHuGs-1630938702505)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\image-20210617211559097.png)]

1.4.3、按照province分组，并查看每个省份有多少人

准备工作：

pdf对象的数据如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2Y5XFD1F-1630938702506)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\image-20210617211705710.png)]

1、DataFrame方式 – 其实也是DSL风格(使用的是RDD对象的方法)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YmaoW5Fs-1630938702507)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\image-20210617213926882.png)]

2、Spark纯SQL语句方式(使用的是SparkSession对象的方法)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-b6EUbqsg-1630938702509)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\image-20210617213801597.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SX19pKaT-1630938702510)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\image-20210617213857617.png)]

1.5、DataFrame的构建

1.5以上的DataFrame的对象是使用的内置的api构建的

查看schema的源码：

schema=》apply=》List[StructField]，这个是StructField的集合

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Fh3VsChe-1630938702511)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\image-20210617220352248.png)]

注意：

Row：代表的是二维表中的一行记录，或者就是一个Java对象

StructType：是该二维表的元数据信息，是StructField的集合

StructField：是该二维表中某一个字段/列的元数据信息（主要包括，列名，类型，是否可以为null）

方式一：通过动态编程的方式来构建(常见、常用)(更底层的方式)

RDD=>DataFrame

SparkSession对象调用createDataFrame方法来创建

package day06

import org.apache.spark.rdd.RDD
import org.apache.spark.sql.types.{DataTypes, StructField, StructType}
import org.apache.spark.sql.{DataFrame, Row, SparkSession}
import utils.MyApp

object DataFrameBuilderDemo extends MyApp {

  val spark = SparkSession.builder().appName("SparkSQLOps").master("local[*]").getOrCreate()
  spark.sparkContext.hadoopConfiguration.set("fs.defaultFS", "file:///")

  testRow
  def testRow()={
    //定义一个Row对象，DataFrame中的数据就是以Row对象描述
    val row = Row("1","xiaofeng",18)
    println(row.getString(0),row.getString(1),row.getInt(2))

  }
  /**
   * Row：代表的是二维表中的一行记录，或者就是一个Java对象
   * StructType：是该二维表的元数据信息，是StructField的集合
   * StructField：是该二维表中某一个字段/列的元数据信息（主要包括，列名，类型，是否可以为null）
   */
  val rows:RDD[Row] = spark.sparkContext.parallelize(List(
    Row(1, "李伟", 1, 180.0),
    Row(2, "汪松伟", 2, 179.0),
    Row(3, "常洪浩", 1, 183.0),
    Row(4, "麻宁娜", 0, 168.0)
  ))

  //定义Schema，即StructType
  val schema = StructType(List(
    StructField("id", DataTypes.IntegerType, false),  //StructField表示一个字段
    StructField("name", DataTypes.StringType, false),
    StructField("gender", DataTypes.IntegerType, false),
    StructField("height", DataTypes.DoubleType, false)
  ))
//  使用RDD[Row]+Schema，最原始的创建DataFrame的方式
  val pdf: DataFrame = spark.createDataFrame(rows, schema)
  pdf.printSchema()
  pdf.show()
}

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kj3QAkRb-1630938702512)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\image-20210617221222695.png)]

方式二：RDD=>DataFrame

//  RDD2DataFrame1()
  //常用方式:通过内存集合+隐式转换
  //RDD =》DataFrame
  def RDD2DataFrame1()={
    val rows = spark.sparkContext.parallelize(List(
      (1, "李伟", 1, 180.0),
      (2, "汪松伟", 2, 179.0),
      (3, "常洪浩", 1, 183.0),
      (4, "麻宁娜", 0, 168.0)
    ))
    import spark.implicits._
    val pdf: DataFrame = rows.toDF("id", "name", "gender", "height")
    pdf.printSchema()
    pdf.show()
  }

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-b5sMtcdu-1630938702514)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624008324888.png)]

方式三：集合=>DataFrame

//集合=》DataFrame
  def RDD2DataFrame2() = {
    import spark.implicits._
    val df=Seq(
      (1, "李伟", 1, 180.0),
      (2, "汪松伟", 2, 179.0),
      (3, "常洪浩", 1, 183.0),
      (4, "麻宁娜", 0, 168.0)).toDF("id","name","gender","height")
    df.printSchema()
    df.show()
  }

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6AzKkHjQ-1630938702515)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624008392622.png)]

1.6、DataSet的构建

Dataset是DataFrame的升级版，创建方式和DataFrame类似，但也有不同。

注意：

1、Dataset一般很少手动创建. 通常都是通过DataFrame转换而来
2、Dataset[Student]存储的Student必须得是case class,因为样例类有默认的encoder，同时需要通过import spark.implicts._来完成数据类型的编码。

方式一：类似于DataFrame动态编程构建（一般不常用，很少用）

方式二：一般使用通过RDD=》DataSet或者DataFrame=》DataSet(一般使用这样的方式)

1.7、RDD、DataFrame和DataSet之间的相互转换

1.7.1、RDD=》DataFrame

方法一：调用RDD对象的toDF方法

val rows = spark.sparkContext.parallelize(List(
      (1, "李伟", 1, 180.0),
      (2, "汪松伟", 2, 179.0),
      (3, "常洪浩", 1, 183.0),
      (4, "麻宁娜", 0, 168.0)
    ))
    val pdf: DataFrame = rows.toDF("id", "name", "gender", "height")
    pdf.printSchema()

方法二：通过动态编程的方式来构建DataFame

调用SparkSession对象的createDataFrame方法

val rows:RDD[Row] = spark.sparkContext.parallelize(List(
      Row(1, "李伟", 1, 180.0),
      Row(2, "汪松伟", 2, 179.0),
      Row(3, "常洪浩", 1, 183.0),
      Row(4, "麻宁娜", 0, 168.0)
    ))
    //定义Schema，即StructType
    val schema = StructType(List(
      StructField("id", DataTypes.IntegerType, false),  //StructField表示一个字段
      StructField("name", DataTypes.StringType, false),
      StructField("gender", DataTypes.IntegerType, false),
      StructField("height", DataTypes.DoubleType, false)
    ))
    //  使用RDD[Row]+Schema，最原始的创建DataFrame的方式
    val pdf: DataFrame = spark.createDataFrame(rows, schema)
    pdf.printSchema()
    pdf.show()

1.7.2、DataFrame=》RDD

需要调用RDD对象的rdd方法

  	val rows = spark.sparkContext.parallelize(List(
      (1, "李伟", 1, 180.0),
      (2, "汪松伟", 2, 179.0),
      (3, "常洪浩", 1, 183.0),
      (4, "麻宁娜", 0, 168.0)
    ))
    val pdf: DataFrame = rows.toDF("id", "name", "gender", "height")
    pdf.rdd.foreach(println(_))

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5ye9MtBM-1630938702515)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624010332320.png)]

1.7.3、RDD=》DataSet

类似于RDD=》DataFrame

调用RDD对象的toDS方法(一般都用这种方式创建DataSet，很常用)

    val rows = spark.sparkContext.parallelize(List(
      Student(1, "王盛芃", 1, 19),
      Student(2, "李金宝", 1, 49),
      Student(3, "张海波", 1, 39),
      Student(4, "张文悦", 0, 29)
    ))
  case class Student(id: Int, name: String, gender: Int, age: Double)
    val pdf = rows.toDS()
    pdf.printSchema()
    pdf.show()

1.7.4、DataSet=》RDD

类似于DataFrame=》RDD

同样调用rdd方法

	val rows = spark.sparkContext.parallelize(List(
      Student(1, "王盛芃", 1, 19),
      Student(2, "李金宝", 1, 49),
      Student(3, "张海波", 1, 39),
      Student(4, "张文悦", 0, 29)
    ))
    val pdf = rows.toDS()
    pdf.rdd.collect().foreach(println(_))

1.7.5、DataFrame=》DataSet

注意：无法直接将DataFrame转化为Dataset

需要借助as方法，在上面DataSet的构建中介绍过

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5wLV9sFt-1630938702516)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624016531891.png)]

as方法需要的是Encoder，由于样例类可以提供encoder，故必须用到case class

    case class Student1(id:Int,name:String,gender:Int,height:Double)
	val df=Seq(
      (1, "李伟", 1, 180.0),
      (2, "汪松伟", 2, 179.0),
      (3, "常洪浩", 1, 183.0),
      (4, "麻宁娜", 0, 168.0)).toDF("id","name","gender","height")
    import spark.implicits._
    val ds = df.as[Student1]
    ds.printSchema()
    ds.show()

注意：需要特别注意样例类 ,样例类最好放到外面，放到方法体外面，不要放到方法里面，如果在方法体里面，在分布式环境下，是无法直接访问此单例对象

1.7.6、DataSet=》DataFrame

思路：

1、集合 =》DataFrame =》DataSet

2、DataSet=》DataFrame

 	case class Student1(id:Int,name:String,gender:Int,height:Double)
    val df=Seq(
      (1, "李伟", 1, 180.0),
      (2, "汪松伟", 2, 179.0),
      (3, "常洪浩", 1, 183.0),
      (4, "麻宁娜", 0, 168.0)).toDF("id","name","gender","height")
    import spark.implicits._
    val ds = df.as[Student1]
    ds.toDF().foreach(println(_))

1.7.7、完整的代码如下：

package day06

import org.apache.spark.sql.SparkSession

object DataFrameConverterDemo extends App{
  val spark = SparkSession.builder().appName("SparkSQ").master("local[*]").getOrCreate
  val rdd=spark.sparkContext.makeRDD(List("a"->1,"b"->2))
  import spark.implicits._

  println("RDD->DataFrame")
  //RDD->DataFrame
  rdd.toDF("key","value").printSchema()
  rdd.toDF("key","value").show()

  println("DataFrame->RDD")
  //DataFrame->RDD
  rdd.toDF("key","value").rdd.collect().foreach(println(_))

  println("RDD->Dataset")
  //RDD->Dataset
  rdd.toDS().printSchema()
  rdd.toDS().show()

  println("Dataset->RDD")
  //Dataset->RDD
  rdd.toDS().rdd.collect().foreach(println(_))


  println("DataFrame->Dataset")
  //DataFrame->Dataset
  //无法直接将DataFrame转化为Dataset
  case class KV(key:String,value:String)
  rdd.toDF("key","value").as[KV].printSchema()
  rdd.toDF("key","value").as[KV].show()

  println("Dataset->DataFrame")
  //Dataset->DataFrame
  rdd.toDS().toDF("key","value").printSchema()
  rdd.toDS().toDF("key","value").show()

}

1.8、SparkSQL和Hive的整合

体验让sparkSQL访问hive元数据

SparkSQL和Hive的整合的实现手段:

a)此时使用的是通过IDEA实现SparkSQL对hive元数据的访问

**b)**spark-shell 中也可操作，需要修改以下hive-site.xml

**c)**spark-sql 也可操作，更方便

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zy9KP1FC-1630938702518)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624026875938.png)]

1.8.1、IDEA使用Spark内置的hive

1、Spark本身有自己的元数据 & warehouse

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DHQqwfvQ-1630938702518)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624022935118.png)]

创建表的部分代码如下：

package day06
import org.apache.spark.sql.SparkSession
import utils.MyApp

/**
 * 使用spark自身的内置hive
 */
object CreateTableDemo08 extends MyApp{
  val spark: SparkSession = SparkSession.builder()
      .master("local[*]")
      .appName("SparkSQL")
//      .enableHiveSupport()
      .getOrCreate()

  //创建一个物化表，也就是实际的表
  spark.sql( """
               |CREATE TABLE if NOT EXISTS dim_tb_user (
               |     user_id BIGINT COMMENT "用户id",
               |     create_time STRING COMMENT "注册时间,如：2019-01-01 08:00:00"
               |)
               |""".stripMargin).show()

}

运行以上代码出现以下异常

出现异常1：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WtjUVXnU-1630938702519)(G:\BigData2021资料\spark\day06\2.笔记\Spark SQL笔记.assets\image-20210617165344275.png)]

解决办法：开启hive机制

val spark: SparkSession = SparkSession.builder()
      .master("local[*]")
      .appName("SparkSQL")
      .enableHiveSupport()
      .getOrCreate()

异常2：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sXdLw71C-1630938702521)(G:\BigData2021资料\spark\day06\2.笔记\Spark SQL笔记.assets\image-20210617165257639.png)]

解决办法：导入 spark-hive依赖。

总结：

实际上hive的元数据存储在mysql，warehouse存储的实际的数据

而同样的在使用spark内置的hive时，也会产生相同的.

sparksql的元数据目录与实际表存储位置截图如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SLEoe2DQ-1630938702522)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624023676231.png)]

metastore_db是一个Derby数据库,查看方式如下:

Derby使用 
$ wget http://mirror.bit.edu.cn/apache//db/derby/db-derby-10.14.2.0/db-derby-10.14.2.0-bin.tar.gz 
$ tar -zxvf db-derby-10.14.2.0-bin.tar.gz 
$ bin/ij 

#使用嵌入式的derby，create为true表示自动创建数据库，数据库名为metastore_db 
ij>  CONNECT 'jdbc:derby:/Users/ly/IdeaProjects/hello-spark2101/metastore_db;create=true'; 
ij(CONNECTION1)> show tables; 
ij(CONNECTION1)> select * from tbls;

插入数据代码如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-c0GmZZ28-1630938702523)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624023940518.png)]

修正后：(成功插入数据)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cGk9vedg-1630938702525)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624024274661.png)]

查询代码如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SfYiYZqO-1630938702526)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624025766748.png)]

完整的代码如下：

使用spark自身的内置hive

package day06

import org.apache.spark.sql.SparkSession
import utils.MyApp
/**
 * 使用spark自身的内置hive
 */
object CreateTableDemo08 extends MyApp{
  val spark: SparkSession = SparkSession.builder()
      .master("local[*]")
      .appName("SparkSQL")
      .enableHiveSupport()
      .getOrCreate()

  //创建一个物化表，也就是实际的表
  def create = {
    spark.sql( """
                 |CREATE TABLE if NOT EXISTS dim_tb_user (
                 |     user_id BIGINT COMMENT "用户id",
                 |     create_time STRING COMMENT "注册时间,如：2019-01-01 08:00:00"
                 |)
                 |""".stripMargin).show()
  }
//  insert
  def insert={
    spark.sql("""
      |insert into dim_tb_user values(123456,'2018-07-01 08:00:01')
      |""".stripMargin).show()
    spark.sql("""
      |insert into dim_tb_user values(123457,'2018-08-01 08:00:01')
      |""".stripMargin).show()
    spark.sql("""
      |insert into dim_tb_user values(123458,'2018-09-01 08:00:01')
      |""".stripMargin).show()
  }
//  query
  def query={
    spark.sql(
      """
        |select * from dim_tb_user
        |""".stripMargin).show
  }
}

1.8.2、Hive回顾

1、metastore元数据服务启动
 hive --service metastore
 端口：9083
 webUI：thrift://192.168.1.101:9083
2、jdbc服务，供beeline使用的
 hive --service hiveserver2
 端口：10000
 webUI：jdbc:hive2://localhost:10000
 命令：beeline -u jdbc:hive2://localhost:10000

提示：
	不建议直接使用hive客户端，因为直接使用hive客户端会在hive进程中启动一个本地的metastore

1.8.3、sparkSQL集成hive(不是使用的内置hive)

目的：hive的元数据替换spark内置的元数据(前提是要有hadoop环境)(远程连接元数据库)

准备工作：

hive的元数据替换spark内置的元数据

步骤一：
 hive-site.xml
 1.使用IDEA时将之放在resources下
 2.使用spark-shell时将之放在$SPARK_HOME/conf下
步骤二：
 启动metastore    hive --service metastore
 启动hiveserver2  hive --service hiveserver2

其实hive-site.xml最重要的配置是：

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <property>
        <name>hive.metastore.uris</name>   			 <value>thift://192.168.1.101:9083</value>
    </property>
</configuration>

package day07

import org.apache.spark.sql.{DataFrame, SparkSession}
import utils.MyApp

/**
 * SparkSQL 有两种hive
 * 一种是Spark内置的hive
 * 一种是使用外置的hive
 *
 * hive的元数据替换spark内置的元数据
 * 步骤一：
 * hive-site.xml
 *1.使用IDEA时将之放在resources下,
 *2.使用spark-shell时将之放在$SPARK_HOME/conf下
 *
 * 步骤二：
 * 启动metastore    hive --service metastore
 * 启动hiveserver2  hive --service hiveserver2
 *
 */
object HiveReaderDemo01 extends MyApp{
  val spark = SparkSession.builder().appName("SparkSQL").master("local[*]")
    .enableHiveSupport()
    .getOrCreate()

  //从hive中读取数据
  spark.sql("show databases").show()
  spark.sql("show tables").show()
}

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-miQtZmw1-1630938702527)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624072695527.png)]

1.8.4、在spark-shell下集成Hive

也可以说成在生产环境下集成Hive，步骤类似

步骤：

将hive-site.xml放在$SPARK_HOME/conf下
或者将hive下的conf中的hive-site.xml放在$SPARK_HOME/conf下

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fwkdvoMd-1630938702528)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624069535360.png)]

纯SQL风格：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2WWSzv22-1630938702529)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624069825510.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cnVsrVQK-1630938702530)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624069875124.png)]

DSL风格：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KpFyLb3L-1630938702530)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624069932937.png)]

真正开发的时候是怎么做的呢?

其实是使用的DSL风格

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OPcMfa2S-1630938702531)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624070218172.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-C8yRPITj-1630938702532)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624070270847.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1AzRapFe-1630938702533)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624070396008.png)]

向hive中写入表

解释：将count>1的数据写入到wordcount1中

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oKi0c6r9-1630938702534)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624070443222.png)]

1.8.5、beeline连接Hive

Beeline其实就是类似于mysql连接工具，比如mysql、Navicat

两个beeline其实是一样的，但hiveserver2与thriftserver是不一样的

**准备工作：**使用beeline的前提是启动metastore和Hadoop环境

#1、启动metastore
#2、启动hiveserver2或者thfitserver服务
#3、在hive或spark目录下启动beeline

1、Hive内置Beeline

hive的jdbc server

#先启动hiveserver2服务
hive --service hiveserver2

#再建立新窗口使用beeline
$HIVE_HOME/bin/beeline -u jdbc:hive2://192.168.1.101:10000

第一步：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QOUtZo53-1630938702536)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624079480814.png)]

第二步：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-igsU8MTX-1630938702538)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624079546191.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dHQhuhHp-1630938702539)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624080336051.png)]

2、Spark内置Beeline

# Spark启用jdbc server

#先启动thriftserver服务

$SPARK_HOME/sbin/start-thriftserver.sh \
--hiveconf hive.metastore.uris=thrift://192.168.1.101:9083 \
--hiveconf hive.server2.thrift.port=10006 \
--hiveconf hive.server2.thrift.bind.host=192.168.1.101

#再建立新窗口使用beeline，也是可以连1000端口的
$SPARK_HOME/bin/beeline -u jdbc:hive2://192.168.1.101:10006

若是在启动thriftserver服务过程中启动不了，可能是$SPARK_HOME/bin下的hive-site.xml没有使用元数据，所以在命令中加上强制使用元数据。

以下是截图演示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eFIrP0QC-1630938702541)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624079776800.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-v4wKmSjT-1630938702542)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624080138396.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YEVtP5ko-1630938702543)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624080261028.png)]

3、总结的点：

1、hiveserver 2使用的是 MapReduce引擎
2、sparkthiftserver 使用的是 RDD引擎
访问hive元数据的两种方式，当然第二种肯定快

4、生产环境下怎么做呢？

一般在生产环境中也是会启动两个，因为一个负责写(hiveserver2)，一个负责读(sparkthriftserver)

第一条和第二条就是一个写一个读，在这里就不用演示了。

解释：

用hivesever2写是因为元数据的标准就是它定的，使用它兼容性更好。

用spark thriftserver读是因为速度快

5、hive查询方式总结：

1、直接使用hive客户端(少用)
2、beeline+hiveserver2
3、beeline+spark thriftserver(仅次于presto)(推荐使用)
4、其他：presto+metastore（效率最高） 还有 imapala+metastore

1.9、SparkSQL函数

Spark函数分为标量函数和聚合函数

标量函数 – (一行输入一行输出) 对应UDF

聚合函数 – (多行输入一行输出，并结合分组使用) 对应UDAF

应当重点练习：

array:返回数组

	collect_set：返回一个元素不重复的set集合

	collect_list：返回一个元素可重复的list集合

	split(str, regex):使用regex分隔符将str进行切割，返回一个字符串数组

	explode(array):将一个数组，转化为多行

  cast(type1 as type2)：将数据类型type1的数据转化为数据类型type2

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YOz21h0j-1630938702544)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624083668548.png)]

1.10、Spark自定义函数

Spark函数分为标量函数和聚合函数

标量函数 – (一行输入一行输出) 对应UDF

聚合函数 – (多行输入一行输出，并结合分组使用) 对应UDAF

1.10.1、UDF

两种定义方法如下：

spark.udf.register[Int, String](“myLen”, (str:String) => myStrLength(str)) 只能在sql方式中使用

//方式一：纯SQL
spark.udf.register[Int, String]("myLen", function)

spark.sql("select myLen('...')")

//方式二：DSL
val fun=org.apache.spark.sql.functions.udf(()=>{})
df.select(fun($"field"))

示例如下：

package day07

import org.apache.spark.sql.SparkSession
import org.apache.spark.sql.expressions.UserDefinedFunction
import utils.MyApp

/**
 * 注意点：
 * $ 返回column对象  -- 其实一般都在DSL风格中使用 在纯SQL下无法使用
 * 其实纯SQL下的UDF和DSL下的UDF还是有区别的：
 *  DSL下的UDF是对列中所有的数据进行运算,而纯SQL不是
 */
object UDFDemo02 extends MyApp{
  val spark = SparkSession.builder().appName("SparkSQL").master("local[*]")
    .enableHiveSupport()
    .getOrCreate

  println("纯SQL")
  //使用纯SQL的方式
  spark.udf.register("myLen",(s:String)=>s.length)
  spark.sql(
    """
      |select myLen("liqiqiqiqi")
      |""".stripMargin).show()

  println("DSL方式")
  //使用DSL方式,其实就是使用的RDD的api
  import spark.implicits._
  val pdf = Seq("aaa"->1,"bbbb"->2).toDF("name","id")
  import org.apache.spark.sql.functions._

  val myLength: UserDefinedFunction = udf((s: String) => s.length)
  pdf.select(myLength($"name")).show()
}

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-57Z39GKQ-1630938702545)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624086098452.png)]

1.10.2、UDAF

暂时不太明白

package day07

import org.apache.spark.sql.{Row, SparkSession}
import org.apache.spark.sql.expressions.{MutableAggregationBuffer, UserDefinedAggregateFunction}
import org.apache.spark.sql.types.{DataType, DataTypes, StructField, StructType}
import utils.MyApp

object UDAFDemo03 extends MyApp{
  val spark = SparkSession.builder().appName("SparkSQL").master("local[*]")
    .enableHiveSupport()
    .getOrCreate

  //纯SQL
  spark.udf.register("myMax",new MyMax)

  spark.sql(
    """
      |select myMax(id) from (select 1  as id union select 2  as id  union select 3 as id )
      |""".stripMargin).show()

}

class MyMax extends UserDefinedAggregateFunction{
  /*
       指定用户自定义udaf输入参数的元数据
       datediff(date1, date2)
  */
  override def inputSchema: StructType = StructType(List(StructField("num",DataTypes.IntegerType,false)))

  //udaf自定义函数求解过程中的临时变量的数据类型
  override def bufferSchema: StructType = StructType(List(StructField("max",DataTypes.IntegerType,false)))

  //udaf返回值的数据类型
  override def dataType: DataType = DataTypes.IntegerType

  //聚合函数是否幂等,相同输入是否总是得到相同输出
  override def deterministic: Boolean = true


  /*
      分区内的初始化操作
       说白了就是给sum和count赋初始值
   */
  override def initialize(buffer: MutableAggregationBuffer): Unit = buffer.update(0,0)

  /**
   * 分区内的更新操作
   * @param buffer 临时变量
   * @param input  自定义函数调用时传入的值
   */
  override def update(buffer: MutableAggregationBuffer, input: Row): Unit = {
    val i = Math.max(buffer.getInt(0),input.getInt(0))
    buffer.update(0,i)
  }

  //分区间的合并操作
  override def merge(buffer1: MutableAggregationBuffer, buffer2: Row): Unit = {
    val i = Math.max(buffer1.getInt(0),buffer2.getInt(0))
    buffer1.update(0,i)
  }

  //udaf聚合结果的返回值
  override def evaluate(buffer: Row): Any = buffer.getInt(0)
}

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZH9EMzM2-1630938702546)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624088141880.png)]

1.11、多维立方体分析函数

多维立方体：空间换时间

1.11.1、相关介绍

grouping sets 、rollup 、cube 是用来处理多维分析的函数：

grouping sets：对分组集中指定的组表达式的每个子集执行group by，group by A,B grouping sets(A,B)就等价于 group by A union group by B,其中A和B也可以是一个集合，比如group by A,B,C grouping sets((A,B),(A,C))。
rollup：在指定表达式的每个层次级别创建分组集。group by A,B,C with rollup首先会对(A、B、C)进行group by，然后对(A、B)进行group by，然后是(A)进行group by，最后对全表进行group by操作。
cube : 为指定表达式集的每个可能组合创建分组集。group by A,B,C with cube首先会对(A、B、C)进行group by，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，©，最后对全表进行group by操作。

1.11.2、示例如下：

package day07

import org.apache.spark.sql.SparkSession
import utils.MyApp

/**
 * 多维立方体模型一般在 数据仓库中使用
 */
object CubesDemo04 extends MyApp{

  val spark = SparkSession.builder().appName("SparkSQL").master("local[*]")
    .enableHiveSupport()
    .getOrCreate
  case class NumberInfo(pro:String,city:String,product:String,number:Int)

  val list = Seq(
    NumberInfo("广东","深圳","今日疫情治愈人数",1),
    NumberInfo("广东","东莞","今日疫情治愈人数",1),
    NumberInfo("广东","珠海","今日疫情治愈人数",1),
    NumberInfo("广东","深圳","昨日疫情治愈人数",1),
    NumberInfo("广东","东莞","昨日疫情治愈人数",1),
    NumberInfo("广东","珠海","昨日疫情治愈人数",1),
    NumberInfo("广东","深圳","今日疫情治愈人数",1),
    NumberInfo("广东","东莞","今日疫情治愈人数",1),
    NumberInfo("广东","珠海","今日疫情治愈人数",1),
    NumberInfo("广东","深圳","昨日疫情治愈人数",1),
    NumberInfo("广东","东莞","昨日疫情治愈人数",1),
    NumberInfo("广东","珠海","昨日疫情治愈人数",1),
    NumberInfo("广东","深圳","前日疫情治愈人数",1),
    NumberInfo("广东","东莞","前日疫情治愈人数",1),
    NumberInfo("广东","珠海","前日疫情治愈人数",1),
    NumberInfo("辽宁","锦州","今日疫情治愈人数",1),
    NumberInfo("辽宁","沈阳","今日疫情治愈人数",1),
    NumberInfo("辽宁","大连","今日疫情治愈人数",1),
    NumberInfo("辽宁","锦州","昨日疫情治愈人数",1),
    NumberInfo("辽宁","沈阳","昨日疫情治愈人数",1),
    NumberInfo("辽宁","大连","昨日疫情治愈人数",1),
    NumberInfo("辽宁","大连","今日疫情治愈人数",1),
    NumberInfo("辽宁","锦州","昨日疫情治愈人数",1),
    NumberInfo("辽宁","沈阳","昨日疫情治愈人数",1),
    NumberInfo("辽宁","大连","昨日疫情治愈人数",1)
  )

  import spark.implicits._
  val df = list.toDF()

  //创建临时表
  df.createTempView("log")

  // 按照省市情况进行分组聚合
  spark.sql(
    """
      |select pro,city,product,sum(number) from log group by pro,city,product
      |""".stripMargin).show()

  // grouping sets 是对每一个单独的维度求值
  spark.sql(
    """
      |select pro,city,product,sum(number)  from log group by pro,city,product grouping sets(pro,city,product)
      |""".stripMargin).show()
  // 如果我们使用这个函数，那么他会对每一个分组后的维度进行单独分组求和
  // 相当于用group by(每个字段) union group by (每个字段)....
  //上面等价于下面
  /*
  select pro,null as city,null as product,sum(number) from log
  group by pro

  union
  select null as pro,city,null as product sum(number) from log
  group by city

  union

  select null as pro,null as city product,sum(number) from log
  group by product
  */

  // rollup 上卷
  // 假如我们还是要求进行分组聚合求值，但是这个求值有变化，要求group by A B C  求ABC，AB，A，全表扫描所有数据
  spark.sql(
    """
      |select pro,city,product,sum(number)  from log group by pro,city,product with rollup order by pro,city,product
      |""".stripMargin).show()

  // cube 立方体
  // 如果需求发生改变，要求你将分组后的维度，所有维度的结果都考虑到，例如group by A B C ,AB,AC,BC,A,B,C,全表扫描
  spark.sql(
    """
      |select pro,city,product,sum(number)  from log group by pro,city,product with cube  order by pro,city,product
      |""".stripMargin).show()

}

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wUMVlDbo-1630938702548)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624089811364.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JNQbZCnl-1630938702549)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624089821001.png)]

1.12、DataFrame中的join算子

需要注意的点：

join和sql中的join相同
===比较的不是列而是列中每个元素都比较,并返回column对象

package day07

import org.apache.spark.sql.SparkSession
import utils.MyApp

/**
 * 注意：
 *  join和sql中的join相同
 *
 *  ===比较的不是列 而是列中每个元素都比较
 */
object JoinDemo05 extends MyApp{
  val spark = SparkSession.builder().appName("SparkSQLOps").master("local[*]")
    .enableHiveSupport()
    .getOrCreate
  import spark.implicits._
  val user=Seq((1,"jason",20),(2,"ammy",21)).toDF("id","name","age")
  val log=Seq((1,"index.html"),(2,"index2.html")).toDF("id","url")

  //期望得到(1,jason,index.html)

  //纯SQL
  user.createOrReplaceTempView("user")
  log.createOrReplaceTempView("log")
  println("纯SQL=>")
  spark.sql(
    """
      |select log.id,user.name,log.url from
      | user left join log
      | on log.id=user.id
      |""".stripMargin).show()

  //DSL
  println("DSL=>")
  user.join(log,user("id")===log("id"),"left").select(log("id"),$"name",$"url").show()
}

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3oOghzSm-1630938702550)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624092728717.png)]

二、案例

2.1、介绍

用户留存的概念 

昨天有100个人注册, 今天这100人中有50个登录,一日留存率是0.5

明天假设这100人中还有30人,二日留存率0.3

计算平台用户留存

用户留存是体现平台健康程度的重要数据指标之一，请用hive sql完成如下需求：

已知：

用户信息表结构：

CREATE TABLE if NOT EXISTS dim_tb_user (
     user_id BIGINT COMMENT "用户id",
     create_time STRING COMMENT "注册时间,如：2019-01-01 08:00:00"
)

COMMENT “用户信息表”;

埋点访问日志表结构

CREATE TABLE if NOT EXISTS fact_access_log (
     user_id BIGINT COMMENT "用户id",
     page_url STRING COMMENT "网站页面路径,用于标记不同页面",
     create_time STRING COMMENT "访问时间,如：2019-01-01 08:00:01"
)

COMMENT “用户访问日志表”

PARTITIONED by (dt STRING COMMENT “格式：yyyymmdd ,数值与create_time转化后的日期一致”;

dim_tb_user : 总量数百万行级别

user_id	create_time
123456	2018-09-01 08:00:01
123457	2019-05-24 09:21:42
123458	2019-08-04 21:02:12
…	…

fact_access_log ：每天新增访问日志记录数据量数亿行

user_id	page_url	create_time	dt
123456	index	2018-09-01 08:08:32	20180901
123457	hot	2019-05-24 09:21:46	20190524
123458	mine	2019-08-04 22:01:21	20190804
…	…	…	…

定义：（20分）

新用户：某一日新注册的用户
新用户次1日留存：T日成为新用户，T+1日回访平台网站任意页面（T+1即T日期的第二天）
新用户次3日留存：T日成为新用户，T+3日回访平台网站任意页面
新用户次n日留存：T日成为新用户，T+n日回访平台网站任意页面

1.假设平台2019年元旦节做了一次拉新活动，当日新增用户数约为平日的两倍，2019年1月9日，距离活动已经过去一周了，公司希望评估一下2019年1月1日当天的全部新增用户，在接下来一周的留存情况(即1月2日-1月8日每天的留存用户数)，要求交付的数据结果表如下，请用一个简洁的sql计算出如下数据：

新用户数	次1日留存用户数	次2日留存用户数	次3日留存用户数	次4日留存用户数	次5日留存用户数	次6日留存用户数	次7日留存用户数	dt（成为新用户日期）
61132	5054	4284	3492	3209	3202	2929	2116	20190101

2.2、纯SQL答案

select
count(distinct a.user_id) 活跃用户数,
count(distinct b.user_id) 一日留存用户数,
count(distinct c.user_id) 二日留存用户数,
count(distinct d.user_id) 三日留存用户数,
count(distinct e.user_id) 四日留存用户数,
count(distinct f.user_id) 五日留存用户数,
count(distinct g.user_id) 六日留存用户数,
count(distinct h.user_id) 七日留存用户数,
"20190101" 成为新用户日期
from (select
user_id
from
dim_tb_user WHERE split(create_time, ' ')[0]="2019-01-01") a
left join fact_access_log  b on a.user_id = b.user_id and split(b.create_time, ' ')[0]= "2019-01-02"
left join fact_access_log  c on a.user_id = c.user_id and split(c.create_time, ' ')[0]= "2019-01-03"
left join fact_access_log  d on a.user_id = d.user_id and split(d.create_time, ' ')[0]= "2019-01-04"
left join fact_access_log  e on a.user_id = e.user_id and split(e.create_time, ' ')[0]= "2019-01-05"
left join fact_access_log  f on a.user_id = f.user_id and split(f.create_time, ' ')[0]= "2019-01-06"
left join fact_access_log  g on a.user_id = g.user_id and split(g.create_time, ' ')[0]= "2019-01-07"
left join fact_access_log  h on a.user_id = h.user_id and split(h.create_time, ' ')[0]= "2019-01-08";

2.3、DSL方式

此处用的数据很少

package day07

import org.apache.spark.sql.{DataFrame, Dataset, Row, SparkSession}
import utils.MyApp

object RetentionDemo06 extends MyApp{
  val spark = SparkSession.builder().appName("SparkSQL").master("local[*]")
    .enableHiveSupport()
    .getOrCreate

  spark.sql(
    """
      |use test1
      |""".stripMargin)

  private val user: DataFrame = spark.table("dim_tb_user").distinct()
  private val log: DataFrame = spark.table("fact_access_log")



  //user join log join log...
  //起始日T注册人数
  import org.apache.spark.sql.functions._
  import spark.implicits._
  val startDate="2018-09-01"
  val newUsers=user.filter(split($"create_time"," ").getItem(0)===startDate)

  //T+1留存用户
  val log1: Dataset[Row] = log.filter(split($"create_time", " ").getItem(0) === "2018-09-02")
    .withColumnRenamed("user_id","t1")

  val r1=newUsers.join(log1,newUsers("user_id")===log1("t1"),"left")
    .select("user_id","t1")

  //T+2留存用户
  val log2=log.filter(split($"create_time"," ").getItem(0)==="2018-09-03")
    .withColumnRenamed("user_id","t2")

  val r2=r1.join(log2,r1("user_id")===log2("t2"),"left")
    .select("user_id","t1","t2")

  //T+3
  val log3=log.filter(split($"create_time"," ").getItem(0)==="2018-09-04")
    .withColumnRenamed("user_id","t3")
  val r3=r2.join(log3,r2("user_id")===log3("t3"),"left")
    .select("user_id","t1","t2","t3")
  //最终留存人数表就是r2
  //求留存率
  r3.select(countDistinct($"user_id").as("news"),
    countDistinct($"t1").as("r1"),
    countDistinct($"t2").as("r2"),
    countDistinct($"t3").as("r3"),
    lit(startDate)
  ).show()
  r3.select(countDistinct($"user_id").as("news"),
    countDistinct($"t1").as("r1"),
    countDistinct($"t2").as("r2"),
    countDistinct($"t3").as("r3"),
    lit(startDate)
  ).select($"r1"/$"news",$"r2"/$"news",$"r3"/$"news")
    .show()

}

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hJ6QIMdW-1630938702551)(C:\Users\等待\AppData\Roaming\Typora\typora-user-images\1624105514663.png)]

准备工作：建立表、插入数据等

package day06

import org.apache.spark.sql.SparkSession
import utils.MyApp
/**
 * 使用spark自身的内置hive
 */
object CreateTableDemo08 extends MyApp{
  //解决错误：org.apache.hadoop.security.AccessControlException
  System.setProperty("HADOOP_USER_NAME","root")
  val spark: SparkSession = SparkSession.builder()
      .master("local[*]")
      .appName("SparkSQL")
      .enableHiveSupport()
      .getOrCreate()

  //创建一个物化表，也就是实际的表
  def create = {
    spark.sql( """
                 |CREATE TABLE if NOT EXISTS dim_tb_user (
                 |     user_id BIGINT COMMENT "用户id",
                 |     create_time STRING COMMENT "注册时间,如：2019-01-01 08:00:00"
                 |)
                 |""".stripMargin).show()
  }

  def create2 = {
    spark.sql(
      """
        |CREATE TABLE if NOT EXISTS fact_access_log (
        |     user_id BIGINT COMMENT "用户id",
        |     page_url STRING COMMENT "网站页面路径,用于标记不同页面",
        |     create_time STRING COMMENT "访问时间,如：2019-01-01 08:00:01"
        |)
        |""".stripMargin).show()
  }

  def useDB = {
    spark.sql( """
                 |use test1
                 |""".stripMargin).show()
  }

  def insert={
    spark.sql("""
      |insert into dim_tb_user values(123456,'2018-09-01 08:00:01')
      |""".stripMargin).show()
    spark.sql("""
      |insert into dim_tb_user values(123457,'2018-09-01 08:00:01')
      |""".stripMargin).show()
    spark.sql("""
      |insert into dim_tb_user values(123458,'2018-09-01 08:00:01')
      |""".stripMargin).show()
  }

  def insert2={

    spark.sql(
      """
        |insert into fact_access_log values(123456,"index","2018-09-02 08:08:32")
        |""".stripMargin).show()
    spark.sql(
      """
        |insert into fact_access_log values(123457,"hot","2018-09-02 08:08:32")
        |""".stripMargin).show()
    spark.sql(
      """
        |insert into fact_access_log values(123458,"mine","2018-09-03 08:08:32")
        |""".stripMargin).show()

  }

  useDB
  create
  create2
  insert
  insert2

}

sparkSQL运行原理

eate()

//创建一个物化表，也就是实际的表
def create = {
spark.sql( “”"
|CREATE TABLE if NOT EXISTS dim_tb_user (
| user_id BIGINT COMMENT “用户id”,
| create_time STRING COMMENT “注册时间,如：2019-01-01 08:00:00”
|)
|""".stripMargin).show()
}

def create2 = {
spark.sql(
“”"
|CREATE TABLE if NOT EXISTS fact_access_log (
| user_id BIGINT COMMENT “用户id”,
| page_url STRING COMMENT “网站页面路径,用于标记不同页面”,
| create_time STRING COMMENT “访问时间,如：2019-01-01 08:00:01”
|)
|""".stripMargin).show()
}

def useDB = {
spark.sql( “”"
|use test1
|""".stripMargin).show()
}

def insert={
spark.sql("""
|insert into dim_tb_user values(123456,‘2018-09-01 08:00:01’)
|""".stripMargin).show()
spark.sql("""
|insert into dim_tb_user values(123457,‘2018-09-01 08:00:01’)
|""".stripMargin).show()
spark.sql("""
|insert into dim_tb_user values(123458,‘2018-09-01 08:00:01’)
|""".stripMargin).show()
}

def insert2={

spark.sql(
  """
    |insert into fact_access_log values(123456,"index","2018-09-02 08:08:32")
    |""".stripMargin).show()
spark.sql(
  """
    |insert into fact_access_log values(123457,"hot","2018-09-02 08:08:32")
    |""".stripMargin).show()
spark.sql(
  """
    |insert into fact_access_log values(123458,"mine","2018-09-03 08:08:32")
    |""".stripMargin).show()

}

useDB
create
create2
insert
insert2

}