hint sparksql 语法 spark.sql

Spark（三）— Spark SQL

• 一.简介
• 二.DataFrame和DataSet

• 2.1 搭建IDEA环境
• 2.2 概念
• 2.3 基本SQL操作
• 2.4 DSL语法
• 2.5. RDD、DF、DS转换

• 三.常用函数

• 3.1 UDF函数
• 3.2 UDAF

• 四.数据读取和保存

• 4.1 基本读取和保存
• 4.2 MySQL

一.简介

Spark SQL是Spark 用于结构化数据处理的Spark 模块，Spark SQL 简化RDD的开发， 提高开发效率，提供了 2 个编程抽象（DataFrame和DataSet，下文具体讲）

关于SparkSession

Spark Core 中，如果想要执行应用程序，需要首先构建上下文环境对象 SparkContext

SparkSession是Spark最新的SQL查询起始点，SparkSession内部封装了SparkContext，所以计算实际上是由 sparkContext完成的

当我们使用spark-shell的时候，spark框架会自动的创建一个名称叫做spark的SparkSession对象，就像我们以前可以自动获取到一个sc来表示 SparkContext对象一样

总结：Spark SQL中SparkSession对象类比与Spark Core中的SparkContext，它是我们Spark SQL查询的起始点

特点

• 易整合
  无缝的整合了 SQL 查询和 Spark 编程
• 统一的数据访问
  使用相同的方式连接不同的数据源
• 兼容 Hive
  在已有的仓库上直接运行 SQL 或者 HiveQL
• 标准数据连接
  通过 JDBC 或者 ODBC 来连接

二.DataFrame和DataSet

2.1 搭建IDEA环境

使用IDEA环境测试
导入相关依赖

导入相关依赖

```xml
<properties>
    <encoding>UTF-8</encoding>
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
    <scala.version>2.12.11</scala.version>
    <spark.version>3.0.1</spark.version>
    <hadoop.version>2.7.5</hadoop.version>
</properties>



<dependencies>
   <!--依赖Scala语言-->
   <dependency>
       <groupId>org.scala-lang</groupId>
       <artifactId>scala-library</artifactId>
       <version>${scala.version}</version>
   </dependency>

   <!--SparkCore依赖-->
   <dependency>
       <groupId>org.apache.spark</groupId>
       <artifactId>spark-core_2.12</artifactId>
       <version>${spark.version}</version>
   </dependency>

   <!-- spark-streaming-->
   <dependency>
       <groupId>org.apache.spark</groupId>
       <artifactId>spark-streaming_2.12</artifactId>
       <version>${spark.version}</version>
   </dependency>

   <!--SparkSQL依赖-->
   <dependency>
       <groupId>org.apache.spark</groupId>
       <artifactId>spark-sql_2.12</artifactId>
       <version>${spark.version}</version>
   </dependency>

   <!-- SparkMlLib机器学习模块,里面有ALS推荐算法-->
   <dependency>
       <groupId>org.apache.spark</groupId>
       <artifactId>spark-mllib_2.12</artifactId>
       <version>${spark.version}</version>
   </dependency>

   <dependency>
       <groupId>org.apache.hadoop</groupId>
       <artifactId>hadoop-client</artifactId>
       <version>${hadoop.version}</version>
   </dependency>

</dependencies>

获取SparkSession的上下文

val conf: SparkConf = new SparkConf().setAppName("SQL").setMaster("local[*]")
val spark: SparkSession = SparkSession.builder().config(conf).getOrCreate()

....

spark.close()

2.2 概念

在 Spar 中，DataFrame 是一种以RDD 为基础的分布式数据集，类似于传统数据库中的二维表格。DataFrame 与 RDD 的主要区别在于，前者带有schema元信息，即 DataFrame所表示的二维表数据集的每一列都带有名称和类型

左侧的 RDD[Person]虽然以 Person 为类型参数，但 Spark 框架本身不了解Person 类的内部结构。而右侧的DataFrame 却提供了详细的结构信息，使得 Spark SQL 可以清楚地知道该数据集中包含哪些列，每列的名称和类型各是什么

DataFrame 是为数据提供了 Schema 的视图。可以把它当做数据库中的一张表来对待

总结：DF包含了数据的元信息，即数据的名称和类型，同时提供了一种视图的机制让我们知道具体的数据

2.3 基本SQL操作

查看 Spark 支持创建文件的数据源格式

输入spark.read. + Tab键，我们发现支持多种数据源

①读取数据源的数据我们以json文件的读取为例

val df: DataFrame = spark.read.json("data/user.json")
// 查看df的元信息
df.printSchema()
// df的数据
df.show()

可以看到，DataFrame带有schema元信息，即DataFrame 所表示的二维表数据集的每一列都带有名称和类型

②创建视图

现在可以看到，我们获得的DF里面是实际的数据，那我们应该怎么操作这些数据呢？

创建一个作用域在当前连接的视图

df.createOrReplaceTempView("user")

val user: Unit = df.createOrReplaceTempView("user")

然后我们就可以用saprk.sql("sql")来执行操作了

spark.sql("select * from user where age > 20").show

③创建全局表

val emp: Unit = df.createGlobalTempView("emp")

新会话查询

注意：global_temp关键字不可省略

spark.newSession().sql("SELECT * FROM global_temp.emp").show()

ETiBA5oOz5YW75Liq5aSn6LGh,size_20,color_FFFFFF,t_70,g_se,x_16)

④查看当前已经生成的视图

spark.catalog.listTables().show()

2.4 DSL语法

DataFrame提供一个特定领域语言(domain-specific language, DSL)去管理结构化的数据。可以在 Scala, Java, Python 和 R 中使用 DSL，使用 DSL 语法风格不必去创建临时视图了

①查看某一列

df.select("age").show()

②涉及字段运算

涉及到运算的时候, 每列都必须使用$, 或者采用引号表达式：单引号+字段名，并且要导入spark下的转换包

import spark.implicits._
df.select('age + 1,'name,'address).show()

③RDD算子的使用

import spark.implicits._
df.filter('age > 20).show()

2.5. RDD、DF、DS转换

DataSet 是分布式数据集合。DataSet是Spark 1.6中添加的一个新抽象，是DataFram

的一个扩展。它提供了RDD的优势（强类型，使用强大的 lambda 函数的能力）以及Spark

SQL优化执行引擎的优点。DataSet 也可以使用功能性的转换，操作 map，flatMap，filter

DataFrame就是特定泛型的DataSet，看图就明白了

// RDD <=> DF
val rdd: RDD[(Int, String, Int)] = sc.makeRDD(List((1, "zhang", 18), (2, "liu", 36)))
val df: DataFrame = rdd.toDF("id", "name", "age")
val rdd1: RDD[Row] = df.rdd

// DF <=> DS
val ds: Dataset[User] = df.as[User]
val df1: DataFrame = ds.toDF()

// RDD <=> DS
val ds1: Dataset[User] = rdd.map {
  case (id, name, age) => {
    User(id, name, age)
  }
}.toDS()

val rdd2: RDD[Row] = df1.rdd


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

三.常用函数

3.1 UDF函数

UDF:User-Defined Functions，主要用来对列进行一些简单操作

spark.udf.register(“函数名”,(参数:参数类型) =>{
返回
})

val df: DataFrame = spark.read.json("data/user.json")

spark.udf.register("udfName",(str:String) =>{
  "udf  " + str
})

val user: Unit = df.createOrReplaceTempView("user")

spark.sql("select age,udfName(name) from user").show()

3.2 UDAF

自定义聚合函数

1.先继承Aggregator，需要三个泛型

• IN 输入的数据类型
• BUF 缓冲区数据类型（一般用样例类来作为参数）
• OUT 输出的数据类型
  2.重写方法
  3.使用udf声明方法
  4.使用

具体看两个示例，SUM和AVG聚合函数的书写！

object UDAF {
  def main(args: Array[String]): Unit = {
    val conf: SparkConf = new SparkConf().setAppName("SQL").setMaster("local[*]")
    val spark: SparkSession = SparkSession.builder().config(conf).getOrCreate()
    import spark.implicits._


     val df: DataFrame = spark.read.json("data/user.json")
    

     val user: Unit = df.createOrReplaceTempView("user")
    // 聚合函数AVG
    spark.udf.register("avg",functions.udaf(new MyAvg()))
    spark.sql("select avg(age) from user").show
    // 聚合函数SUM
    spark.udf.register("sum",functions.udaf(new MySUM()))
    spark.sql("select sum(age) from user").show




    spark.close()
  }

  /**
   * IN 输入的数据类型
   * BUF 缓冲区数据类型
   * OUT 输出的数据类型
   * AVG 聚合函数
   */
  case class Buff(var total:Long,var count :Long)
  class MyAvg extends Aggregator[Long,Buff,Long]{
    // 缓冲区初始化
    override def zero: Buff = {
      Buff(0L,0L)
    }
    // 根据输入的数据更新缓冲区的数据
    override def reduce(buff: Buff, in: Long): Buff = {
      buff.total = buff.total + in
      buff.count = buff.count + 1
      buff
    }
    // 合并缓冲区
    override def merge(b1: Buff, b2: Buff): Buff = {
      b1.total = b1.total + b2.total
      b1.count = b1.count + b2.count
      b1
    }
    // 计算
    override def finish(reduction: Buff): Long ={
      reduction.total / reduction.count
    }
    // 固定 若是自定义的类，则是Encoders.product
    override def bufferEncoder: sql.Encoder[Buff] = Encoders.product

    override def outputEncoder: sql.Encoder[Long] = Encoders.scalaLong
  }

  // 聚合函数 SUM
  case class Buff2(var total:Long)
  class MySUM extends Aggregator[Long,Buff2,Long] {
    override def zero: Buff2 = {
      Buff2(0L)
    }

    override def reduce(b: Buff2, a: Long): Buff2 = {
      b.total = b.total + a
      b
    }

    override def merge(b1: Buff2, b2: Buff2): Buff2 = {
      b1.total = b1.total + b2.total
      b1
    }

    override def finish(reduction: Buff2): Long = {
      reduction.total
    }

    override def bufferEncoder: sql.Encoder[Buff2] = Encoders.product

    override def outputEncoder: sql.Encoder[Long] = Encoders.scalaLong
  }
  
}

四.数据读取和保存

4.1 基本读取和保存

读取

spark有很多种文件读取方式，SparkSQL默认读取和保存的文件格式为 parquet

load是加载数据的通用方法

一般是这样用的：

spark.read.format(“…”)[.option(“…”)].load(“…”)

format指定加载的数据类型，包括csv、jdbc、json、orc、parquet和
textFile

但是前面也提到了，如果读json文件

直接spark.read.json(path) 更加简洁

Spark 读取的JSON 文件不是传统的JSON 文件，每一行都应该是一个 SON串

保存

df.write.format(“…”)[.option(“…”)].save(“…”)

format(“…”)：指定保存的数据类型，包括csv、jdbc、json、orc、parquet和
textFile

save (“…”)：在csv、orc、parquet和textFile格式下需要传入保存数据的路径

option(“…”)：在jdbc格式下需要传入 JDBC 相应参数，url、user、password 和 dbtable

val conf: SparkConf = new SparkConf().setAppName("SQL").setMaster("local[*]")
val spark: SparkSession = SparkSession.builder().config(conf).getOrCreate()
import spark.implicits._

val frame: DataFrame = spark.read.json("data/user.json")

frame.show()

frame.write.format("json").save("output")

保存操作可以使用SaveMode, 用来指明如何处理数据，使用mode()方法来设置

frame.write.format(“json”).mode(X).save(“output”)

X可以是append、overwrite、ignore

若文件存在，append会再写一个文件。overwrite是覆盖、ignore忽略

4.2 MySQL

导入MySQL依赖

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

object MySQLTest {
  def main(args: Array[String]): Unit = {
    val conf: SparkConf = new SparkConf().setAppName("SQL").setMaster("local[*]")
    val spark: SparkSession = SparkSession.builder().config(conf).getOrCreate()
    import spark.implicits._


    val df: DataFrame = spark.read.format("jdbc")
      .option("url", "jdbc:mysql://localhost:3306/hadoop?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8")
      .option("driver", "com.mysql.jdbc.Driver")
      .option("user", "root")
      .option("password", "zks123456")
      .option("dbtable", "student")
      .load()

    df.show()


    df.write
      .format("jdbc")
      .option("url", "jdbc:mysql://localhost:3306/hadoop?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8")
      .option("user", "root")
      .option("password", "zks123456")
      .option("dbtable", "student_spark")
      .mode(SaveMode.Append)
      .save()


    spark.close()
  }

}