比较全面的spark_sql语法运用（直接看代码，速学速过）

最新推荐文章于 2024-10-10 09:56:47 发布
wan_qin6
最新推荐文章于 2024-10-10 09:56:47 发布
阅读量239
点赞数 3
分类专栏： sql 文章标签： javascript ecmascript sql spark
本文链接：https://blog.csdn.net/wan_qin6/article/details/140210184
版权
sql 专栏收录该内容
1 篇文章 0 订阅
订阅专栏
//c：SparkSQL应用实例
//c:SparkSQL是Spark提供的用于处理结构化数据的工具，它可以将结构化的数据文件（如CSV、JSON、Parquet等）转换为关系型数据库表，并提供SQL查询接口。
import org.apache.spark.sql.Sparksession
/**
 * 创建SparkSession实例:
 * .getOrCreate():这一行代码使用getOrCreate方法来获取一个已经存在的SparkSession对象，如果不存在则创建一个新的。
 */
val sparkSession=SparkSession.bulider().appeName("SparkSQ应用实例").config("spark.some.config.option","some-value").getOrCreate()
import sparkSession.implicits._;
//创建DATAFrame对象：
val df=SpakSession.read.json("file:///path/to/json/file")
//查看数据结构：
df.printSchema()
//查看数据内容：
df.show()
//创建临时视图：
df.createOrReplaceTempView("my_table")
//执行SQL查询：
val result=sparkSession.sql("SELECT * FROM my_table WHERE age > 30")
//查看结果：
result.show()
//关闭SparkSession实例：
sparkSession.close()
//查看数据集中指定列
df.select("name","age").show()
//查看数据集中指定列的统计信息
df.describe("age").show()
//查看数据集中指定列的最大值
df.select(max("age")).show()
//查看数据集中指定列的最小值
df.select(min("age")).show()
//查看数据集中指定列的平均值
df.select(avg("age")).show()
//查看数据集中指定列的总和
df.select(sum("age")).show()
//查看数据集中指定列的方差
df.select(stddev("age")).show()
//查看数据集中指定列的分位数
df.select(approx_quantile("age",Array(0.25,0.5,0.75))).show()
//查看数据集中指定列的频率分布
df.select(freqItems("age",10)).show()    //10表示展示频率最高的10个元素
//查看数据集中指定列的直方图
df.select(col("age").cast("int"),col("age")).groupBy("age").count().orderBy("age").show()

//查看数据集中指定列的饼图
val age_Frequency=df.groupBy("age").count().orderBy("age")
ageFrequency.Write.csv("path/to/json/file/age_Frequency.csv")
/*python:
import matplotlib.pyplot as plt
import pandas as pd
age_Frequency_pd=pd.read_csv("path/to/json/file/age_Frequency.csv")
plt.pie(age_Frequency_pd["count"],labels=age_Frequency_pd["age"],autopct='%1.1f%%')
plt.title("Age Frequency")
plt.show()
//
import plotly.express as px
import pandas as pd
// 读取 Spark 输出的 CSV 文件
df = pd.read_csv("path/to/output/age_frequency.csv")
//生成饼图
fig = px.pie(df, values='count', names='age', title='Age Distribution')
fig.show()
//
 */
import spark.implicits._
//创建DATAFrame对象：
val df = spark.read.json("file:///path/to/json/file")
df.createOrReplaceTempView("my_table")
//在 Scala 和 Spark 中，$ 符号用于引用 DataFrame 中的列名。
// 这是因为在 Scala 中，列名通常是字符串，而使用 $ 符号可以将这些字符串转换为 Column 对象，从而可以在 DataFrame 操作中使用。
df.select($"name",$"age"+1,$"weight").where($"height" > 175).orderBy($"age".asc).show()
df.groupBy("age").count().show()
//注册为临时表：
df.createOrReplaceTempView("students")
val sqlDF = sparkSession.sql("SELECT * FROM students WHERE age > 30")
sqlDF.show()
//全局临时表：global_temp.students
df.createGlobalTempView("students")
sparkSession.sql("SELECT name,age FROM global_temp.students WHERE age > 30")
sparkSession.newSession().sql("SELECT name,age FROM global_temp.students WHERE age > 30").show()
//查看全局临时表：
sparkSession.catalog.listTables("global_temp")
//创建Datqset[person]
case class Person(name: String, age: Int, weight: Double, height: Double)
val data = Seq(Person("Alice", 20, 60.0, 175.0), Person("Bob", 25, 70.0, 180.0), Person("Charlie", 30, 80.0, 165.0))
val df = sparkSession.createDataset(data)
df.show()
//UDF
import org.apache.spark.sql.functions.udf
import org.apache.spark.sql.expressions.UserDefinedFunction
import org.apache.spark.sql.expressions.MutableAggregationBuffer
import org.apache.spark.sql.types._
//无类型聚合函数：
object MyAverage extends UserDefinedFunction {
    def bufferSchema: StructType = StructType(Seq(StructField("sum", DoubleType), StructField("count", LongType)))
    def deterministic: Boolean = true
    def initialize(buffer: MutableAggregationBuffer): Unit = {
        buffer(0) = 0.0
        buffer(1) = 0L
    }
    def update(buffer: MutableAggregationBuffer, input: Double): Unit = {
        if (!input.isNullAt(0))
    {
        buffer(0) = buffer.getDouble(0) + input
        buffer(1) = buffer.getLong(1) + 1
    }
    }
    def merge(buffer1: MutableAggregationBuffer, buffer2: MutableAggregationBuffer): Unit = {
        buffer1(0) = buffer1.getDouble(0) + buffer2.getDouble(0)
        buffer1(1) = buffer1.getLong(1) + buffer2.getLong(1)
    }
    def evaluate(buffer: Row): Double = buffer.getDouble(0) / buffer.getLong(1)
}
//    注册UDF：
sparkSession.udf.register("myAverage", MyAverage)
val df = sparkSession.read.json("file:///path/to/json/file/s.json")
df.createOrReplaceTempView("employees")
df.show()
val result=sparkSession.sql("SELECT myAverage(weight) as avg_weight FROM employees")
result.show()
//用户自定义的强类型聚合函数：需要继承Aggregator抽象类，并实现3个方法：
import org.apache.spark.sql.expressions.Aggregator
import org.apache.spark.sql.Encoder
import org.apache.spark.sql.Encoders
import org.apache.spark.sql.SparkSession
case class Employee(name: String, age: Int, weight: Double, height: Double)
case class Averager(var sum: Double, var count: Long)
object MyAverage extends Aggregator[Employee, Averager, Double] {
    def zero:Averager = Averager(0.0, 0L)
    def reduce(b: Averager, a: Employee): Averager = {
        if (!a.weight.isNaN) {
            b.sum += a.weight
            b.count += 1
        }
        b
    def merge(b1: Averager, b2: Averager): Averager = {
            b1.sum += b2.sum
            b1.count += b2.count
            b1
        }
    def finish(reduction:

Averager): Double = {
        if (reduction.count == 0) {
            0.0
        } else {
            reduction.sum / reduction.count
        }
    }
    def bufferEncoder: Encoder[Averager] = Encoders.product
    def outputEncoder: Encoder[Double] = Encoders.scalaDouble}
val df = sparkSession.read.json("file:///path/to/json/file/s.json")
df.show()
val averageSalary=MyAverage.toColumn.name("avg_weight")
val result=df.select(averageSalary).show()

/*
SparkSQL DATAFRAME操作大全
*/
//JSON文件生成所需的DATAFRAME
import org.apache.spark.sql.Row
import org.apache.spark,sql.types._
import spark.implicits._
val jsonData = sparkSession.read.json("file:///path/to/json/file")
jsonData.printSchema()
df.show()#//默认20行，隐藏只显示20个字节
df.show(10)
//全显示每个字节，不隐藏
df.show(100,false)
df.show()
//查看数据集中指定列
df.select("name","age").show()
//获取所有数据到数组中:不适合大数据集
df.collect()
df.collectAsMap()
df.collectAsList()
df.describe("age").show()//包含：count、mean、stddev、min、max、approx_quantiles、freqItems
//获取行数据
df.first()
df.head()
df.take()//指定行数
df.takeAsList(10)//指定行数:返回List[Row]
df.select("name","age").where("age>30").orderBy("age").show()
df.where("$age>30").show()//使用$符号引用列名,需要引入相应包：import spark.implicits._
df.filter("name='M' or age=20")//.filter()过滤行:filter("age>30"):根据字段筛选
//查看指定列
df.select("name","age").show()
df.select($"colA",$"colB"+1)//使用$符号引用列名,需要引入相应包：import spark.implicits._
df.selectExpr("name","age+1 as new_age","round(age)").show()//可以对指定字段进行特殊处理：可以对指定字段调用UDF函数或者是指定别名。
df.select(expr("name"),expr("age+1 as new_age"),expr("round(age))"))//可以对指定字段进行特殊处理：可以对指定字段调用UDF函数或者是指定别名。)/.show()
//获取列标签：
df.col("name")//获取指定列的标签
df.columns//获取所有列的标签
df.apply("name")//获取指定列的标签
df.drop("name")//删除指定列
df.drop(df("name"))
df.dropDuplicates()//删除重复行
df.na.drop()//删除空值
df.na.fill(0) //填充空值
df.limit(10)//限制行数:返回的是新转化生成的DataFrame对象
//orderBy和sort只能输入column对象，不能输入字符串，需要使用df("age").asc/desc来指定排序规则
df.orderBy(df("age").desc)/ /排序:返回的是新转化生成的DataFrame对象"age".asc排序:返回的是新转化生成的DataFrame对象
df.orderBy( df("age").asc) 排序:返回的是新转化生成的DataFrame对象".desc)//排序:返回的是新转化生成的DataFrame对象
df.sort(df("age").asc,df("age").desc)
df.GroupBy("age")//分组:返回的是GroupBy对象
df.GroupBy(df("age"))//聚合:返回的是新转化生成的DataFrame对象
//之后可以在连接.count()、.sum()、.avg()、.min()、.max()、.mean()、.std()、.approxQuantile()、.freqItems()等方法进行聚合操作
df.distinct()//去重:返回的是新转化生成的DataFrame对象
//聚合:返回的是新转化生成的DataFrame对象,输入的是对于聚合操作的表达，可同事对多列进行聚合。
df.agg("agg"->"Mean","phone"->"max").show()//
df.limit(5).union(df2.limit(5))//合并:返回的是新转化生成的DataFrame对象
df.intersect(df2)//交集:返回的是新转化生成的DataFrame对象
df.except(df2)//差集:返回的是新转化生成的DataFrame对象
df.join(df1,"name").show(5)
df.join(joinDF2,Seq("name","age"))
df.join(df1,Seq("name","age"),"inner")//inner:内连接:返回的是新转化生成的DataFrame对象
df.join(df1,Seq("name","age"),"outer")//outer:外连接:返回的是新转化生成的DataFrame对象
df.join(df1,Seq("name","age"),"left_outer")//left_outer:左外连接:返回的是新转化生成的DataFrame对象
df.join(df1,Seq("name","age"),"right_outer")//right_outer:右外连接:返回的是新转化生成的DataFrame对象
df.join(df1,Seq("name","age"),"full_outer")//full_outer:全连接:返回的是新转化生成的DataFrame对象
df.join(df1,Seq("name","age"),"semi_join")//semi_join:返回的是新转化生成的DataFrame对象
df.join(df1,Seq("name","age"),"anti_join")//anti_join:返回的是新转化生成的DataFrame对象
df.join(df1,Seq("name","age"),"leftsemi_join")//leftsemi_join:返回的是新转化生成的DataFrame对象
df.join(df1,Seq("name","age"),"leftanti_join")//leftanti_join:返回的是新转化生成的DataFrame对象
df.join(df1,Seq("name","age"),"cross_join")//cross_join:返回的是新转化生成的DataFrame对象
df.crossJoin(df2)//笛卡尔积:返回的是新转化生成的DataFrame对象

//获取指定字段统计信息：.stat
df.stat.freqItems(Array("age","height"),0.3)//频率分布
df.state.freqItems(Seq("age","height"),0.3)//频率分布//第三个参数可以控制展示频率最高的元素个数
df.stat.corr("age","weight")//相关系数
df.stat.cov("age","weight")//协方差
df.stat.crosstab("age","weight")//交叉表
#//获取指定字段统计信息：.stat
df.stat.mean("age")//平均值
df.stat.sum("age")//求和
df.stat.min("age")//最小值
df.stat.max("age")//最大值
df.stat.variance("age")//方差
df.stat.kurtosis("age")//峰度
df.stat.skewness("age")//偏度
df.stat.approxQuantile("age",Array(0.25,0.5,0.75))//分位数
df.stat.countDistinct("age")//不同值个数
df.stat.count()//总行数
df.stat.describe("age")//描述性统计
df.stat.approxCountDistinct("age")//近似不同值个数
//操作字段名
df.withColumnRenamed("age","new_age")//重命名列
df.withColumn("new_age",df("age")*2)//增加列
df.dropDuplicates()//删除重复行
//处理空值
df.na.drop()//删除空值
df.na.fill(0) //填充空值
df.na.fill(Map(('age',12),('institute','cailiao')))

//分区：
df.repartition(10) //重新分区//.repartition()重新分区:repartition(10)
df.coalesce(10) //.coalesce()合并分区:coalesce(10)
//持久or缓存
df.persist(StorageLevel.MEMORY_AND_DISK) //.persist()持久化数据:persist(StorageLevel.MEMORY_AND_DISK)
df.unpersist() //.unpersist()取消缓存或持久化:unpersist()
df.cache() //.cache()缓存数据:cache()