spark SQL

已于 2023-04-26 17:20:59 修改
阅读量347 收藏
点赞数
分类专栏： 大数据 # Spark 文章标签： spark sql 大数据
于 2023-03-13 10:41:28 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_49948651/article/details/128047529
版权

spark3--sql

1. SparkCore 与 SparkSQL 的区别

对比SparkCoreSparkSQL
处理场景离线离线、实时
处理类型结构化数据、非结构化结构化
数据来源文件、数据库、任何一种数据来源结构化文件、数据库表
开发语言代码：Python、Scala、Java、RSQL、DSL【代码】
驱动对象SparkContextSparkSession
数据抽象RDDDataFrame
抽象设计分布式数据集合【列表】分布式数据表
设计差异数据数据 + Schema【表的结构】

2. RDD与 计DataFrame 的区别

RDD是数据集合，每条数据就是一个元素，只能对元素进行处理；DataFrame是数据表，每条数据就是表中的一行，并且会给一行划分每一列，可以对每行或者每列的数据进行处理。
DataFrame 本质是基于RDD之上增加了Schema信息：DataFrame = RDD + Schema。
在这里插入图片描述
RDD： 没有Schema，对于RDD，每条数据就是一个Person信息，每个元素代表一个人但是RDD并不清楚每个人有什么信息。支持泛型，RDD[int]、RDD[tuple]、RDD[Person]。
DataFrame： 有Schema，对于DF，每条数据是一个Row对象信息，知道每个Row对象的数据中有哪几列，列名，类型。不支持泛型，现在使用的DataFrame，底层就是DataSet，DataSet既支持泛型。
Schema： 是一种元数据，用于描述DataFrame中数据的字段信息（字段名称，字段类型，字段是否允许为空），基于Schema来实现对DataFrame中的列进行处理。
Row： 存储DataFrame中的一行的数据，DataFrame = List，Row = Person，列 = Person类的属性

3. SparkSession中的开发模式：SQL和DSL

3.1 SQL开发

步骤：

  1. 将DataFrame注册成临时的视图表
    DataFrame.createOrReplaceTempView(“视图名称”)
  2. 使用SQL语句对临时视图进行处理
    RS_DF = spark.sql(“处理视图的SQL语句”)

3.2 DSL开发使用API函数

在这里插入图片描述

3.3 DSL开发使用SQL函数

在这里插入图片描述

3.4 SQL和DSL都能实现SparkSQL开发，那么哪种性能更好？

不论是SQL还是DSL，只要计算的逻辑是一样的，经过优化器，最终的实现也是一样的，所以相同执行逻辑的代码性能上没有区别。

4. SparkSession 模板

下面所有的代码都依照这个模板运行，只编写这一块的内容 # todo:2-数据处理：读取、转换、保存

from pyspark.sql import SparkSession
import os

if __name__ == '__main__':
# todo:0-设置系统环境变量
    # 配置JDK的路径，就是前面解压的那个路径
    os.environ['JAVA_HOME'] = '/export/server/jdk'
    # 配置Hadoop的路径，就是前面解压的那个路径
    os.environ['HADOOP_HOME'] = '/export/server/hadoop'
    # 配置base环境Python解析器的路径
    os.environ['PYSPARK_PYTHON'] = '/export/server/anaconda3/bin/python3'
    # 配置base环境Python解析器的路径
    os.environ['PYSPARK_DRIVER_PYTHON'] = '/export/server/anaconda3/bin/python3'
    
    # 若没有配置以下配置，则默认路径为 程序提交的当前位置(file:/root/pyspark_code_zimo/"当前项目名"/)
    # 以下配置，配置后，文件数据默认路径为 hdfs://node1.itcast.cn:8020/user/root

    # 配置Hadoop的配置路径（Yarn提交特有）
    # os.environ['HADOOP_CONF_DIR'] = '/export/server/hadoop/etc/hadoop/'
    # 配置Yarn的配置路径（Yarn提交特有）
    # os.environ['YARN_CONF_DIR'] = '/export/server/hadoop/etc/hadoop/'

    # 申明当前以root用户的身份来执行操作
    os.environ['HADOOP_USER_NAME'] = 'root'

    # todo:1-构建SparkSession
    ss = (
        SparkSession.builder
        .master("local[2]")     # 运行模式
        .appName("HiveAPP")  # 程序名字
        .config("spark.sql.warehouse.dir", 'hdfs://node1.itcast.cn:8020/user/hive/warehouse')
        .config('hive.metastore.uris', 'thrift://node1.itcast.cn:9083')
        .config("spark.sql.shuffle.partitions", 2)
        .enableHiveSupport()
        .getOrCreate()  # 判断是否创建过ss
    )
    ss.sparkContext.setLogLevel("WARN")
    sc=ss.sparkContext


    # todo:2-数据处理：使用SQL或者DSL实现对DF的转换
    # step1: 读取数据

    # step2: 处理数据


    # step3: 保存结果


    # todo:3-关闭SparkSession
    ss.stop()

5. RDD 与 DataFrame 的区别

5.1 rdd与df的区别（schema，printSchema，show，first）

rdd与df的区别：

算子名称算子功能
schema返回Schema对象
printSchema ()打印Scheme
show (n=20, truncate=True)展示df表，n表示展示几行，truncate表示是否截串(20字节)显示
first ()显示第一条ROW 对象
from pyspark.sql import SparkSession
import os
# todo:2-数据处理：使用SQL或者DSL实现对DF的转换
    # step1: 读取数据
    # 如果读取数据没有指定schema,df会把行数据作为列,字段名为value
    input_rdd=sc.textFile("./datas/resources/employees.json",minPartitions=2)
    input_df=spark.read.json("./datas/resources/employees.json")
    print("----- rdd -----")
    input_rdd.foreach(lambda x : print(x)) # {"name":"Michael", "salary":3000}
    print("----- df -----")
    print(input_df.schema)		# StructType(List(StructField(name,StringType,true),StructField(salary,LongType,true)))
    input_df.printSchema()      #  |-- name: string     |-- salary: long
    input_df.show(truncate=False)
    print(input_df.first())     # Row(name='Michael', salary=3000)
    
    # step2: 处理数据
    # step3: 保存结果

5.2 Schema与Row的理解（获取row中的值，StructField，StructType）

Schema与Row的理解：

算子名称算子功能
df[“列名”]获取row中的值 方式一
df.列名获取row中的值 方式二
StructField (name=表字段名,dataType=字段类型,nullable=是否为空)
StructType (fields=[StructField对象,…])

出现的其他函数：

算子名称算子功能
json (路径)将json对象转换成df
from pyspark.sql import SparkSession
import os
from pyspark.sql.types import StructField, StringType, DoubleType, BooleanType, StructType

# todo:2-数据处理：使用SQL或者DSL实现对DF的转换
    # step1: 读取数据

    print("----- Schema -----")
    input_df=spark.read.json("./datas/resources/employees.json")
    # step2: 处理数据
    input_df.printSchema()  # |-- name: string     |-- salary: long
    # 底层 schemea对象：StructType(以列表封装StructField列对象）
    print(input_df.schema)  # StructType(List(StructField(name,StringType,true),StructField(salary,LongType,true)))

    print("----- Row -----")
    first_row=input_df.first()
    print(first_row)
    # 获取row中的值 方式一
    name1=first_row["name"]
    salary1=first_row["salary"]
    print(f"name:{name1}\tsalary:{salary1}")
    # 获取row中的值 方式二
    name2=first_row.name
    salary2=first_row.salary
    print(f"name:{name2}\tsalary:{salary2}")

    print("----- 自定义创建Schema ------")
    # 定义4列：id  name  score  gender
    col_id=StructField(name="id",dataType=StringType(),nullable=False)
    col_name=StructField(name="name",dataType=StringType(),nullable=True)
    col_score=StructField(name="score",dataType=DoubleType(),nullable=True)
    col_gender=StructField(name="gender",dataType=BooleanType(),nullable=True)
    # 创建 Schema
    create_schema=StructType(fields=[col_id,col_name,col_score,col_gender])
    print(create_schema)

    # step3: 保存结果

5.3 rdd 转换 df【3种方式】（Row，createDataFrame，toDF）

rdd 转换 df【3种方式】：

算子名称算子功能
Row (字段名=值)构建Row对象
createDataFrame(RDD[Row])rdd 转换 df【方式1】
createDataFrame(RDD[list] or RDD[tuple]，StructType)rdd 转换 df【方式2】
toDF(字段tuple or 字段list)使用toDF方法的RDD 元素必须是tuple或者list
import re
from pyspark.sql import SparkSession
import os
from pyspark.sql.types import StructField, StringType, LongType, DoubleType, StructType
from pyspark.sql.types import Row
# todo:2-数据处理：使用SQL或者DSL实现对DF的转换
    # step1: 读取数据
    input_rdd=sc.textFile("./datas/movie/u.data")
    # step2: 处理数据
    
    # 方式1：
    movie_rdd_row1=(
        input_rdd.map(lambda line:re.split("\\s+",line))
            # 196	242 3	881250949
        .map(lambda item:Row(userid=item[0],movieid=item[1],
                             rate=float(item[2]),ts=int(item[3])))
    )
    movie_df1=ss.createDataFrame(movie_rdd_row1)
    print(movie_df1.schema,"\n")
    movie_df1.show()
    
    # 方式2：
    movie_rdd_tuple2 = (
        input_rdd.map(lambda line: re.split("\\s+", line))
            # 196	242 3	881250949
            .map(lambda item: (item[0], item[1], float(item[2]), int(item[3])))
    )
    # 定义列
    col_userid = StructField(name="userid", dataType=StringType(), nullable=True)
    col_movieid = StructField(name="movieid", dataType=StringType(), nullable=True)
    col_rate = StructField(name="rate", dataType=DoubleType(), nullable=True)
    col_ts = StructField(name="ts", dataType=LongType(), nullable=True)
    # 构建schema
    movie_schema = StructType(fields=[col_userid, col_movieid, col_rate, col_ts])
    movie_df = ss.createDataFrame(movie_rdd_tuple2, movie_schema)
    movie_df.printSchema()
    movie_df.show()
    
    # 方式3：
    movie_rdd_tuple3=(
        input_rdd.map(lambda line:re.split("\\s+",line))
        .map(lambda item:(item[0],item[1],float(item[2]),int(item[3])) )
    )
    # toDF(传入列表[])      列表类存字段名
    # 使用toDF方法的RDD 元素必须是tuple或者list
    movie_df3 = movie_rdd_tuple2.toDF(["userid","movieid","rate","ts"])
    movie_df3.printSchema()
    movie_df3.show()
    # step3: 保存结果

6. DataFrame的常用api

在这里插入图片描述

6.1 df常用api【上】（count，collect，take，first，head，tail，foreach，foreachPartition）

df常用api【上】：

算子名称算子功能
count ()计算df行数
collect()df转换成列表
take(n)取前n行内容
first()取第一行内容
head(NULL or n)当为空时，等价于first()；当为n时，等价于take(n)
tail(n)取最后n行内容
foreach(self , f : T -> None) -> None对DataFrame中每条数据进行处理，没有返回值
foreachPartition (self: RDD[T] , f: Iterable[T] -> None) -> None对DataFrame中每个分区数据进行处理，没有返回值
from pyspark import StorageLevel
from pyspark.sql import SparkSession
import os
# todo:2-数据处理：使用SQL或者DSL实现对DF的转换
    people_df = ss.read.json("./datas/resources/people.json")

    # 基本算子：count/collect/take/first/head/tail/foreach/foreachPartition/distinct/union/unionAll/coalesce/repartition
    count = people_df.count()
    print(f"总共有：{count}行")

    collect = people_df.collect()
    print(f"转换成列表以后的内容是：{collect}")

    take = people_df.take(3)
    print(f"前三行的内容是：{take}")

    first = people_df.first()
    print(f"第一行的内容是：{first}")

    head = people_df.head()
    print(f"第一行的内容是：{head}")

    tail = people_df.tail(2)
    print(f"最后二行的内容是：{tail}")

    # 下面这两个算子的区别是什么
    # 针对DF中的每个元素 也就是Row进行操作
    people_df.foreach(lambda row: print(row))
    # 针对DF中的每个分区操作 一次处理的是一个分区的所有Row
    people_df.foreachPartition(lambda part: print(*part))

6.2 df常用api【中】（union，unionAll，distinct，getNumPartitions，coalesce，repartition）

df常用api【中】：

算子名称算子功能
union(df)两个df的合并（去重）
unionAll(df)两个df的合并（不去重）
distinct()对数据进行去重处理
getNumPartitions()获取分区数
coalesce(n)调小df的分区数
repartition(n)调大df的分区数
from pyspark import StorageLevel
from pyspark.sql import SparkSession
import os
# todo:2-数据处理：使用SQL或者DSL实现对DF的转换
	people_df = ss.read.json("./datas/resources/people.json")
    people_df_other = ss.read.json("./datas/resources/people.json")
    print("union的结果")
    people_df.union(people_df_other).show()
    print("unionAll的结果")
    people_df.unionAll(people_df_other).show()
    print("distinct的结果")
    people_df.unionAll(people_df_other).distinct().show()

    print(f"原来的分区数：{people_df.rdd.getNumPartitions()}")
    print(f"减少后分区数：{people_df.coalesce(1).rdd.getNumPartitions()}")
    print(f"增大后分区数：{people_df.repartition(4).rdd.getNumPartitions()}")

6.3 df常用api【下】（cache，persist，unpersist，columns，schema，rdd）

df常用api【下】：

算子名称算子功能
cache()对df进行缓存(到内存)
persist(StorageLevel)按StorageLevel进行缓存
unpersist(blocking=True or False)取消df的缓存；blocking=True时：等释放完再继续下一步
columns返回df中的所有列名
schema返回df中Schema的信息
rdd返回df中的数据放入RDD中
from pyspark import StorageLevel
from pyspark.sql import SparkSession
import os
# todo:2-数据处理：使用SQL或者DSL实现对DF的转换
	people_df = ss.read.json("./datas/resources/people.json")
	
 	# 持久化算子
    people_df.cache()
    people_df.persist(StorageLevel.MEMORY_AND_DISK_2)
    people_df.unpersist(blocking=True)

    # 其他算子
    columns = people_df.columns
    print(f"所有列的名称：{columns}")

    schema = people_df.schema
    print(f"所有列的信息：{schema}")

    rdd = people_df.rdd
    rdd.foreach(lambda x: print(x))

    people_df.printSchema()

7. SparkSQL与SparkDSL

7.1 SparkSQL语法

SparkSQL语法：

算子名称算子功能
sql()SparkSession的sql处理
createOrReplaceTempView(“表名”)将df转换为临时表
import re
from pyspark.sql import SparkSession
import os

# todo:2-数据处理：使用SQL或者DSL实现对DF的转换
    # step1: 读取数据
    movie_input_rdd = sc.textFile("./datas/movie/movies.dat")
    rate_input_rdd=sc.textFile("./datas/movie/ratings.dat")
    rate_df=(
        rate_input_rdd.map(lambda line:re.split("::",line))
        .map(lambda item:(item[0],item[1],float(item[2]),int(item[3])))
        .toDF(["userid","movieid","rate","ts"])
    )
    # rate_df.show()
    movie_df=(
        movie_input_rdd.map(lambda line:re.split("::",line))
        .map(lambda item:(item[0],item[1],item[2]))
        .toDF(["movieid","title","genres"])
    )
    # movie_df.show()
    # step2: 处理数据
    ### 需求1：求点评超过2000人的影评分数TOP10
    rate_df.createOrReplaceTempView("rate_table_temp")
    rs_rate_df=ss.sql("""
        select 
            movieid,
            round(avg(rate),2) avg_rate,
            count(*) as cnt
        from rate_table_temp
        group by movieid
        having cnt>2000
        order by avg_rate desc,cnt desc
        limit 10
    """)
    # rs_rate_df.show(truncate=False)

    rs_rate_df.createOrReplaceTempView("rate_table")
    movie_df.createOrReplaceTempView("movie_table")
    rs_movie_df=ss.sql("""
        select
            a.movieid,genres,title,avg_rate,cnt
        from rate_table a left join movie_table b
        on a.movieid = b.movieid
    """)
    rs_movie_df.show(truncate=False)

7.2 SparkDSL语法

SparkDSL语法：

算子名称算子功能
col(“字段名”)获取该字段列的内容
agg(聚合函数 )聚合函数调用时要封装在agg里
alias(“别名”)取别名，用在SQL相关算子后

在这里插入图片描述

import re
from pyspark.sql import SparkSession
import os
import pyspark.sql.functions as sf

# todo:2-数据处理：使用SQL或者DSL实现对DF的转换
    # step1: 读取数据
    movie_input_rdd = sc.textFile("./datas/movie/movies.dat")
    rate_input_rdd = sc.textFile("./datas/movie/ratings.dat")
    rate_df = (
        rate_input_rdd.map(lambda line: re.split("::", line))
            .map(lambda item: (item[0], item[1], float(item[2]), int(item[3])))
            .toDF(["userid", "movieid", "rate", "ts"])
    )
    # rate_df.show()
    movie_df = (
        movie_input_rdd.map(lambda line: re.split("::", line))
            .map(lambda item: (item[0], item[1], item[2]))
            .toDF(["movieid", "title", "genres"])
    )
    # movie_df.show()
    # step2: 处理数据
    ### 需求1：求点评超过2000人的影评分数TOP10
    rs_rate_df=(
        rate_df.select(sf.col("movieid"),sf.col("rate"))
        .groupby(sf.col("movieid"))
        .agg(
            sf.round(sf.avg(sf.col("rate")),2).alias("avg_rate")
            ,sf.count(sf.col("movieid")).alias("cnt")
            )
        .where(sf.col("cnt")>2000)
        .orderBy(sf.col("avg_rate").desc(),sf.col("cnt").desc())
        .limit(10)
    )
    # rs_rate_df.printSchema()
    # rs_rate_df.show(truncate=False)

    rs_df=(
        rs_rate_df.join(other=movie_df,on=["movieid"],how="left")
        .select(sf.col("title"),sf.col("avg_rate"),sf.col("cnt"))
        .orderBy(sf.col("avg_rate").desc(),sf.col("cnt").desc())
    )
    rs_df.printSchema()
    rs_df.show(truncate=False)

8. SparkSQL读写数据

8.1 SparkSQL读写数据（text，json，Parquet ORC，CSV）

输出模式：Save Mode：

  • append: 追加模式，当数据存在时，继续追加
  • overwrite: 覆写模式，当数据存在时，覆写以前数据，存储当前最新数据；
  • error/errorifexists: 如果目标存在就报错，默认的模式
  • ignore: 忽略，数据存在时不做任何操作
from pyspark.sql import SparkSession
import os

# todo:2-数据处理：使用SQL或者DSL实现对DF的转换
    # step1: 读取数据

    # # ----- text ----- （文本格式）【下面三个都一样】
    # text_df1=ss.read.text("./datas/resources/people.txt")
    # text_df2=ss.read.format("text").load("./datas/resources/people.txt")
    # text_df3=ss.read.load("./datas/resources/people.txt",format("text"))

    # # ----- json ----- （json格式）【下面三个都一样】
    # json_df1 = ss.read.json("./datas/resources/people.json")
    # json_df2 = ss.read.format("json").load("./datas/resources/people.json")
    # json_df3 = ss.read.format("json").option("path","./datas/resources/people.json").load()

    # # ----- Parquet ORC ----- （列存储格式）【下面三个都一样】
    # # SparkSQL 默认存储格式 Parquet
    # parquet_df1 = ss.read.parquet("./datas/resources/users.parquet")
    # parquet_df2 = ss.read.format("parquet").load("./datas/resources/users.parquet")
    # parquet_df3 = ss.read.format("parquet").option("path", "./datas/resources/users.parquet").load()

    # # ----- CSV ----- （CSV格式）
    # # 默认以 ',' 为分隔符
    # parquet_df1 = ss.read.csv("./datas/resources/people.csv")   # 默认','分隔符
    # parquet_df2 = (
    #     ss.read.format("csv")
    #     .option("sep", ";")
    #     .option("header","true")
    #     .option("inferSchema","true")
    #     .option("path","./datas/resources/people.csv")
    #     .load()
    # )

    # step2: 处理数据
    # step3: 保存结果

    # # ----- text ----- （文本格式）
    # # mode保存模式有：【append , overwrite , error(默认模式) , ignore】
    # text_df3.write.mode("append").text("./output/text1")
    # text_df3.write.mode("overwrite").format("text").save("./output/text2")

    # # ----- json ----- （json格式）
    # json_df3.write.mode("append").json("./output/json1")
    # json_df3.write.mode("overwrite").format("json").save("./output/json2")
    # json_df3.write.mode("overwrite").format("json").option("path","./output/json3").save()

    # # ----- Parquet ORC ----- （列存储格式）
    # # 默认开启 Snappy压缩
    # parquet_df3.write.mode("append").parquet("./output/parquet1")
    # parquet_df3.write.mode("overwrite").format("parquet").save("./output/parquet2")
    # parquet_df3.write.mode("overwrite").format("parquet").option("path","./output/parquet3").save()

    # # ----- CSV ----- （CSV格式）
    # parquet_df2.write.mode("append").csv("./output/csv1")
    # parquet_df2.write.mode("overwrite").format("csv").save("./output/csv2")

9. SparkSQL与读写数据库

9.1 sparksql与mysql的集成

import time
from pyspark.sql import SparkSession
import os

# todo:2-数据处理：使用SQL或者DSL实现对DF的转换
    # step1: 读取数据
    # todo: 连接mysql
    # 方式1：直接jdbc读取
    url = "jdbc:mysql://node1.itcast.cn:3306/?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true"
    table="db_company.emp"
    props={"user":"root","password":"123456"}
    jdbc_df1=ss.read.jdbc(url=url,table=table,properties=props)
    jdbc_df1.show()

    # 方式2：指定format加载数据库读取
    jdbc_df2 = (
        ss.read
            .format("jdbc")
            .option("url","jdbc:mysql://node1.itcast.cn:3306/?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true")
            .option("dbtable", "db_company.emp")
            .option("user", "root")
            .option("password", "123456")
            .load()
    )
    jdbc_df2.show()

    # step2: 处理数据
    # step3: 保存结果
    props={"user":"root","password":"123456"}

    # # 方式1：直接jdbc写入
    # rs_df1=(
    #     jdbc_df2.select("empno","ename","job","sal","deptno")
    #     .write.jdbc(
    #         url="jdbc:mysql://node1.itcast.cn:3306/?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true",
    #         # 该表设置了主键(二次追加报错)
    #         table="db_company.emp_v2",
    #         properties=props,
    #         mode="append"
    #     )
    # )
    # rs_df2 = (
    #     jdbc_df2.select("empno", "ename", "job", "sal", "deptno")
    #         .write.jdbc(
    #         url="jdbc:mysql://node1.itcast.cn:3306/?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true",
    #         # 该表没有主键(可以重复追加)
    #         table="db_company.emp_v3",
    #         properties=props,
    #         mode="append"
    #     )
    # )

    # # 方式2：指定format加载数据库写入
    # rs_df3=(
    #     jdbc_df2.select('empno', 'ename', 'job', 'sal', 'deptno')
    #     .write.mode("append").format("jdbc")
    #     .option("url", "jdbc:mysql://node1.itcast.cn:3306/?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true")
    #     .option("dbtable", "db_company.emp_v3")
    #     .option("user", "root")
    #     .option("password", "123456")
    #     .save()
    # )

9.2 sparksql与hive的集成

from pyspark.sql import SparkSession
import pyspark.sql.functions as sf
import os

if __name__ == '__main__':
# todo:0-设置系统环境变量
    # 配置JDK的路径，就是前面解压的那个路径
    os.environ['JAVA_HOME'] = '/export/server/jdk'
    # 配置Hadoop的路径，就是前面解压的那个路径
    os.environ['HADOOP_HOME'] = '/export/server/hadoop'
    # 配置base环境Python解析器的路径
    os.environ['PYSPARK_PYTHON'] = '/export/server/anaconda3/bin/python3'
    # 配置base环境Python解析器的路径
    os.environ['PYSPARK_DRIVER_PYTHON'] = '/export/server/anaconda3/bin/python3'
    
    # 若没有配置以下配置，则默认路径为 程序提交的当前位置(file:/root/pyspark_code_zimo/"当前项目名"/)
    # 以下配置，配置后，文件数据默认路径为 hdfs://node1.itcast.cn:8020/user/root

    # 配置Hadoop的配置路径（Yarn提交特有）
    # os.environ['HADOOP_CONF_DIR'] = '/export/server/hadoop/etc/hadoop/'
    # 配置Yarn的配置路径（Yarn提交特有）
    # os.environ['YARN_CONF_DIR'] = '/export/server/hadoop/etc/hadoop/'

    # 申明当前以root用户的身份来执行操作
    os.environ['HADOOP_USER_NAME'] = 'root'
# todo:1-构建SparkSession
    ss = (
        SparkSession.builder
        .master("local[2]")     # 运行模式
        .appName("HiveAPP")  # 程序名字
        .config("spark.sql.warehouse.dir", 'hdfs://node1.itcast.cn:8020/user/hive/warehouse')
        .config('hive.metastore.uris', 'thrift://node1.itcast.cn:9083')
        .config("spark.sql.shuffle.partitions", 2)
        .enableHiveSupport()
        .getOrCreate()  # 判断是否创建过ss
    )
    ss.sparkContext.setLogLevel("WARN")
    sc=ss.sparkContext
# todo:2-数据处理：使用SQL或者DSL实现对DF的转换
    # step1: 读取数据

    # step2: 处理数据
    # SQL
    print("----- SQL -----")
    ss.sql("""
            select
            	d.deptno, round(avg(e.sal), 2) as avg_sal 
            from db_hive.emp e 
            join db_hive.dept d 
            on e.deptno = d.deptno
            group by d.deptno
            order by avg_sal desc
    """).show()

    # DSL
    print("----- DSL -----")
    hiveData = ss.read.table("db_hive.emp")
    rs_df = (
        hiveData
        .select("deptno", "sal")
        .groupBy("deptno")
        .agg(sf.round(sf.avg("sal"), 2).alias("avg_sal"))
    )
    rs_df.show()


    # step3: 保存结果
    print("----- save -----")
    rs_df.write.mode("overwrite").format("hive").saveAsTable("db_hive.test3")

10. SparkSQL的自定义函数UDF

在这里插入图片描述

10.1 自定义函数UDF（register）

UDF：

算子名称算子功能特点
udf(聚合函数 )funcname1=sf.udf( f =lambda x : f( x ), returnType=StringType())只有DSL都能使用。funcname1是DSL函数名
register(“字段名”)funcname1=ss.udf.register(name=“funcname2”, f =lambda x : f( x ))SQL和DSL都能使用。funcname1是DSL函数名，funcname2是SQL中的函数名
import re
from pyspark.sql import SparkSession
import pyspark.sql.functions as sf
import os
from pyspark.sql.types import StringType

# todo:2-数据处理：使用SQL或者DSL实现对DF的转换
    # step1: 读取数据
    input_df=(
        ss.read.format("csv")
        .option("sep", "\t")
        .load("./datas/udf/music.tsv")
        .withColumnRenamed(existing='_c0',new="id")
        .withColumnRenamed(existing='_c1',new="name")
        .withColumnRenamed(existing='_c2',new="info")
    )
    # input_df.show()

    # step2: 处理数据
    # 定义udf函数
    def to_newinfo(info):
        item=re.split("/",info)
        item[0]=round(float(item[0])/2,1)
        item[1]=item[1]+"cm"
        return str(item[0])+"kg  "+item[1]
    # UDF自定义函数
    ## 1. register(SQL和DSL都能使用)
    get_info1=ss.udf.register(name="func",f=lambda info:to_newinfo(info))
    ## 2. udf(只有DSL都能使用)
    get_info2=sf.udf(f=lambda info:to_newinfo(info),returnType=StringType())

    # SQL
    print("----- SQL -----")
    input_df.createOrReplaceTempView("music")
    ss.sql("""
        select id,name,func(info) as newinfo
        from music
    """).show()


    # DSL
    print("----- DSL -----")
    print("第一种方法")
    input_df.select(
        sf.col("id"),
        sf.col("name"),
        get_info1("info").alias("newinfo1")
    ).show()

    print("第二种方法")
    input_df.select(
        sf.col("id"),
        sf.col("name"),
        get_info2("info").alias("newinfo2")
    ).show()

    # step3: 保存结果

11. 案例练习

11.0 数据前瞻

{“discountRate”: 1, “dayOrderSeq”: 8, “storeDistrict”: “火星区”, “isSigned”: 0, “storeProvince”: “太阳系”, “origin”: 0, “storeGPSLongitude”: “113.01567856440359”, “discount”: 0, “storeID”: 4064, “productCount”: 4, “operatorName”: “OperatorName”, “operator”: “NameStr”, “storeStatus”: “open”, “storeOwnUserTel”: 12345678910, “corporator”: “hnzy”, “serverSaved”: true, “payType”: “alipay”, “discountType”: 2, “storeName”: “杨光食店”, “storeOwnUserName”: “OwnUserNameStr”, “dateTS”: 1563758583000, “smallChange”: 0, “storeGPSName”: “”, “erase”: 0, “product”: [{“count”: 1, “name”: “百事可口汽水”, “unitID”: 0, “barcode”: “6940149410029”, “pricePer”: 3, “retailPrice”: 3, “tradePrice”: 0, “categoryID”: 1}, {“count”: 1, “name”: “真鸡筋110g”, “unitID”: 0, “barcode”: “6951027300076”, “pricePer”: 2.5, “retailPrice”: 2.5, “tradePrice”: 0, “categoryID”: 1}, {“count”: 2, “name”: “火星锅巴”, “unitID”: 0, “barcode”: “6970362690000”, “pricePer”: 2.5, “retailPrice”: 2.5, “tradePrice”: 0, “categoryID”: 1}, {“count”: 1, “name”: “地球包装”, “unitID”: 0, “barcode”: “6901028300056”, “pricePer”: 12, “retailPrice”: 12, “tradePrice”: 0, “categoryID”: 1}], “storeGPSAddress”: “”, “orderID”: “156375858240940641230”, “moneyBeforeWholeDiscount”: 22.5, “storeCategory”: “normal”, “receivable”: 22.5, “faceID”: “”, “storeOwnUserId”: 4082, “paymentChannel”: 0, “paymentScenarios”: “PASV”, “storeAddress”: “StoreAddress”, “totalNoDiscount”: 22.5, “payedTotal”: 22.5, “storeGPSLatitude”: “28.121213726121993”, “storeCreateDateTS”: 15577143046000, “payStatus”: -1, “storeCity”: “火星市”, “memberID”: “0”}

11.1 代码实现

from pyspark import StorageLevel
from pyspark.sql import SparkSession
import os
import pyspark.sql.functions as sf
from pyspark.sql.types import DecimalType

if __name__ == '__main__':
    # todo:0-设置系统环境变量
    # 配置JDK的路径，就是前面解压的那个路径
    os.environ['JAVA_HOME'] = '/export/server/jdk'
    # 配置Hadoop的路径，就是前面解压的那个路径
    os.environ['HADOOP_HOME'] = '/export/server/hadoop'
    # 配置base环境Python解析器的路径
    os.environ['PYSPARK_PYTHON'] = '/export/server/anaconda3/bin/python3'
    # 配置base环境Python解析器的路径
    os.environ['PYSPARK_DRIVER_PYTHON'] = '/export/server/anaconda3/bin/python3'

    # 若没有配置以下配置，则默认路径为 程序提交的当前位置(file:/root/pyspark_code_zimo/"当前项目名"/)
    # 以下配置，配置后，文件数据默认路径为 hdfs://node1.itcast.cn:8020/user/root

    # 配置Hadoop的配置路径（Yarn提交特有）
    # os.environ['HADOOP_CONF_DIR'] = '/export/server/hadoop/etc/hadoop/'
    # 配置Yarn的配置路径（Yarn提交特有）
    # os.environ['YARN_CONF_DIR'] = '/export/server/hadoop/etc/hadoop/'

    # 申明当前以root用户的身份来执行操作
    os.environ['HADOOP_USER_NAME'] = 'root'

    # todo:1-构建SparkSession
    ss = (
        SparkSession.builder
            .master("local[2]")  # 运行模式
            .appName("HiveAPP")  # 程序名字
            .config("spark.sql.warehouse.dir", 'hdfs://node1.itcast.cn:8020/user/hive/warehouse')
            .config('hive.metastore.uris', 'thrift://node1.itcast.cn:9083')
            .config("spark.sql.shuffle.partitions", 2)
            .enableHiveSupport()
            .getOrCreate()  # 判断是否创建过ss
    )
    ss.sparkContext.setLogLevel("WARN")
    sc = ss.sparkContext

    # todo:2-数据处理：使用SQL或者DSL实现对DF的转换
    # step1: 读取数据
    input_df=ss.read.json("./datas/order/retail.json")
    # step2: 处理数据
    etl_df=(
        input_df.filter(
            (sf.col("receivable")<10000)
            & (sf.col("storeProvince").isNotNull())
            & (sf.col("storeProvince")!=("null" or "NULL"))
        )
        .select(
            sf.col("storeProvince").alias("store_province") # 省份
            ,sf.col("storeID").alias("store_id")    # 店铺ID
            ,sf.col("payType").alias("pay_type")    # 支付方式
            ,sf.col("receivable").cast(DecimalType(10,2)).alias("receivable_money")   # 订单金额
            ,sf.from_unixtime(sf.col("dateTS").substr(0,10),"yyyy-MM-dd").alias("daystr")    # 订单时间
            # ,sf.from_unixtime(sf.substring(sf.col("dateTS"), 0, 10),"yyyy-MM-dd").alias("daystr")  # 订单时间
        )
    )
    etl_df.persist(StorageLevel.MEMORY_AND_DISK)
    # etl_df.show()

    # 需求1：统计每个省份总销售额
    rs1_df=(
        etl_df.groupby(sf.col("store_province"))
        .agg(sf.sum("receivable_money").alias("sum_money"))
    )
    # rs1_df.show()

    # 中转数据
    temp_province=(
        rs1_df.orderBy(sf.col("sum_money").desc())
        .limit(3)
        .rdd
        .map(lambda row:row["store_province"])
        .collect()
    )
    top3_df=(
        etl_df.filter(sf.col("store_province").isin(temp_province))
    )
    top3_df.show()
    etl_df.unpersist()
    top3_df.persist(StorageLevel.MEMORY_AND_DISK)
    top3_df.createOrReplaceTempView("top3_province")
    ss.catalog.cacheTable("top3_province")      # 缓存视图

    # 需求2：统计销售额前三的省份中，每个省份单日销售额能超过1000的店铺的个数
    rs2_df=ss.sql("""
        with t1 as (
            select  store_province
                    ,daystr
                    ,store_id
                    ,sum(receivable_money) as summoney
            from top3_province
            group by store_province,daystr,store_id
            having summoney>1000
        ),
        t2 as (
            select  store_province
                    ,summoney
                    ,store_id
                    ,row_number() over (partition by store_id order by summoney) as rk
            from t1
        )
        select  store_province
                ,count(store_id) as cnt
        from t2
        where rk=1
        group by store_province
    """)
    # rs2_df.show()

    # 需求3：统计销售额前三省份的平均订单金额
    rs_df3=ss.sql("""
        select  store_province
                ,round(avg(receivable_money),2) as avg_money
        from top3_province
        group by store_province
    """)
    # rs_df3.show()

    # 需求4：统计销售额前三省份的订单支付方式占比
    rs_df4=ss.sql("""
        with t1 as (
            select  store_province
                    ,pay_type
                    ,count(1) as cnt
            from top3_province
            group by store_province,pay_type
        ),
        t2 as (
            select  t1.*
                    ,sum(cnt) over(partition by store_province) as all_cnt
            from t1
        )
        select  store_province
                ,pay_type
                , concat(cast(round(round(cnt / all_cnt, 4) * 100, 2) as STRING), "%") as rate

        from t2
    """)
    rs_df4.show()

    # step3: 保存结果
    top3_df.unpersist()
    ss.catalog.uncacheTable("top3_province")    # 释放缓存
    # todo:3-关闭SparkSession
    ss.stop()

11.2 窗口函数

    # todo：窗口函数
    # 需求：统计销售额前三省份的订单支付方式占比
    rs_df4=ss.sql("""
        with t1 as (
            select  store_province
                    ,pay_type
                    ,count(1) as cnt
            from top3_province
            group by store_province,pay_type
        ),
        t2 as (
            select  t1.*
                    ,sum(cnt) over(partition by store_province) as all_cnt
            from t1
        )
        select  store_province
                ,pay_type
                ,round(round(cnt/all_cnt,4)*100,2) as rate
        from t2
    """)
    rs_df4.printSchema()
    rs_df4.show()
    window = Window.partitionBy("store_province").orderBy("rate")
    window_df=rs_df4.withColumn("min", sf.min("rate").over(window))
    window_df.show()
DUT_子陌
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SparkSql数组操作的N种骚气用法
xiaoluobutou的专栏
02-22 2975
总结一下sparksql（基于branch3.3） 中 array操作相关的骚气用法，这恐怕是总结的最全的一篇了，哈哈~~
PySpark数据分析基础:核心数据类Row和Column原理及常用操作一文详解
master_hunter的博客
02-03 3234
如果之前不接触python的pandas我觉得上手pyspark会更快，原因在于pandas的dataframe操作API实在是好用，功能代码使用简便而且容易理解，相对于pyspark中的sql.dataframe就显得十分出色了。sql.dataframe数据类型的底层构造是完全和python中pandas完全不同的，而是强关联与spark的dataframe，二者有本质的区别，当然函数功能操作也是有很大的不同。
pyspark.sql.Row
qq_40176087的博客
08-26 1652
文章目录
pyspark入门系列 - 05 pyspark.sql.Row函数汇总
qq_34615112的博客
06-28 3622
from pyspark.sql import SparkSession spark = SparkSession.Builder().master('local').appName('pysparkSqlRow').getOrCreate() Row DataFrame中的一行，可以像属性和字典一样访问其中的字段 # 1. 通过Row创建 from pyspark.sql import Row row = Row(name='Tom', age=15) row Row(age=15, name='
【spark源码系列】pyspark.sql.Row介绍和使用示例
helloxiaozhe的博客
12-08 7125
0、Spark SQL和DataFrames重要的类有： pyspark.sql.SQLContext： DataFrame和SQL方法的主入口 pyspark.sql.DataFrame： 将分布式数据集分组到指定列名的数据框中 pyspark.sql.Column ：DataFrame中的列 pyspark.sql.Row： DataFrame数据的行 pyspark.sql.Hiv...
Spark Sql中时间字段少8个小时问题解决
02-28
### Spark SQL 中时间字段少8个小时问题解决 #### 问题背景 在进行数据处理时，尤其是在涉及时间戳字段转换的过程中，经常会遇到时区问题。本文将详细探讨在使用Spark SQL处理Hive表中的时间戳字段时，遇到的时间...
Atlas Spark SQL血缘分析，Hive Hook
最新发布
05-25
4. **执行Spark SQL操作**：当Spark SQL通过Hive接口执行DML（Data Manipulation Language）或DDL（Data Definition Language）操作时，Hive Hook会捕获这些操作的元数据变更。 5. **血缘信息收集和存储**：捕获的...
Learning Spark SQL epub
10-06
Learning Spark SQL 英文epub 本资源转载自网络，如有侵权，请联系上传者或csdn删除 本资源转载自网络，如有侵权，请联系上传者或csdn删除
Spark SQL操作大全.zip
03-24
《Spark SQL操作大全》 Spark SQL是Apache Spark项目的一个核心组件，它提供了处理结构化数据的强大功能，使得在大数据分析领域中，Spark SQL成为了一种不可或缺的工具。本资料主要涵盖了Spark SQL的基础概念、核心...
spark sql解析-源码分析
03-16
Spark SQL是Apache Spark项目的一部分，它是处理SQL查询和数据集成的强大工具。Spark SQL结合了DataFrame API和传统的SQL接口，使得开发人员可以灵活地在结构化和半结构化数据上进行高性能计算。在这个源码分析中，...
Spark Sql之union
南风知我意
08-01 2620
spark中data frame 有union和union all算子，均不去重 这点，不像hive中那样，hive sql中union all不去重，union去重
Spark之Row
热门推荐
SunnyRivers
10-30 1万+
前言 一直在说Dataframe是Dataset的特列，DataFrame=Dataset[Row]，可Row是什么东西呢？ 什么是Row 顾名思义：就是一行数据 Row是org.apache.spark.sql包下的一个特质 简单的理解： Row是一个类型，跟Car、Person这些的类型一样，所有的表结构信息都用Row来表示。 什么时候会用到这个Row呢？ 煮个例子 通过读取文件创建一个Dat...
Spark.sql Row
qq_47506661的博客
07-07 500
Row 使用 代码如下： from pyspark.sql import SparkSession from pysark.sql import Row row =Row(name="andi",age=26) >row[name] >andi >row[age] >29 >row2=Row('i love rui') >row2[0] ><Row('i love rui')> 写以上代码的原因主要是example/word_count.py下
Spark-sql-row
oblesslyy的专栏
05-21 5403
Spark-sql-row@(spark)[sql|row]Rowrow就是关系数据库中的一行。/**
spark.sql.Row
花木兰
03-04 2632
DF 的 一行
Spark DF:关于Row中的数值获取问题
anyan8397的博客
12-16 2104
//json文件 //{"page_id":"87","pay_category_ids":"99","pay_product_ids":"96","city_id":5} //{"pay_category_ids":"74","city_id":0} val df = sparkSession.read.json("./src/data/test") df.show() df.rdd...
pyspark.sql.Row 使用 dictionary 初始化的方法 “TypeError: sequence item 0: expected string, dict found”
CY_TEC的博客
09-03 3095
from pyspark.sql import Row row_dict = {'C0': -1.1990072635132698, 'C3': 0.12605772684660232, 'C4': 0.5760856026559944, 'C5': 0.1951877800894315, 'C6':...
pyspark.sql.DataFrame-统计
lily's world
09-03 2228
继上篇DF中agg介绍之后，继续极少DF methods 注册一个临时表： 1 createGlobalTempView :创建了一个全局的临时表 对应于dropGlobalTempView spark.catalog.dropGlobalTempView("table_name") 2.createOrReplaceTempView()&amp; createTempView()...
pyspark.sql.Column & pyspark.sql.Row
lily's world
09-05 864
method of Column Column 对象的创建 创建方法 1. DF[“col_name”] 2.expression(DF[‘col_name’]) column 的方法 between(lowerBound,upperBound) 判断值的范围 [lower,upper] df.select(df.name,df.age.between(2,4))...
spark sql
07-29
Spark SQL是Apache Spark中的一个模块，它提供了在分布式数据处理中进行结构化数据处理和分析的功能。它支持使用SQL查询、DataFrame和Dataset API进行数据操作。 Spark SQL可以用于读取和写入多种数据源，包括Parquet、Avro、JSON、CSV等。它还提供了对Hive表的支持，可以通过Hive元数据访问已存在的Hive表。 使用Spark SQL，你可以执行SQL查询来处理大规模的结构化数据。它还提供了优化器和执行引擎，可以针对查询进行优化，以提高查询性能。 除了SQL查询之外，Spark SQL还提供了DataFrame和Dataset API，这些API提供了更灵活的编程方式来处理和操作数据。DataFrame是一种以命名列组织的分布式数据集，类似于传统数据库中的表。Dataset是在Spark 1.6版本中引入的，是DataFrame的类型安全版本，通过编译时类型检查来避免运行时错误。 总之，Spark SQL为Spark生态系统提供了一种处理结构化数据的高性能和灵活的方式，使得开发者可以使用SQL查询或编程API来操作大规模的数据集。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值