Spark综合案例

需求分析

一个做零售相关业务的公司，旗下出品各类收银机
机器每次使用都会将售卖的商品数据上传到公司后台
老板现在想对 省份维度 的 销售情况 进行统计分析

4个需求

1. 各省 销售指标，每个省的销售额统计
2. Top3 销售省份中，有多少家店铺日均销售额 1000+
3. Top3 省份中，各省的平均单单价
4. Top3 省份中，各省的支付类型比例

2个操作

1. 将需求结果写出到 mysql
2. 将数据写入到 Spark On Hive中

数据格式

案例所用数据为 mini.json

问题记录

MySQL数据库写入 中文 时报错
当写入内容有中文时，即使在写入时设置了UTF8编码，但还是会报错，这是因为创建数据库时默认的数据库编码和排序规则都不是utf8，一般是latin1，手动修改为latin1的项为utf8即可（别忘了排序规则，只改编码格式不得行）

HDFS写入权限问题
Spark 向 Hive 中写入数据时，可能会报权限错误。hdfs 的操作权限归 hadoop 用户，但程序中操作时会以 root 用户操作

hadoop 在访问 hdfs 的时候会进行权限认证，root 用户就被拦截下来了
取用户名的过程是这样的：

读取 HADOOP_USER_NAME 系统环境变量，如果不为空，那么拿它作 username，如果为空，读取 HADOOP_USER_NAME 这个环境变量

因此解决方案为：在代码最开始将 hadoop 用户名设置到环境变量中

os.environ['HADOOP_USER_NAME'] = "hadoop"

代码

# -*- coding = utf-8 -*-
# @Time :2023/8/11 14:12
# @Author :ZJH 
# @Site :
# @File :main.py
# @Software :PyCharm
"""
需求1：各省份销售额统计
需求2：Top3销售额省份中，有多少家店铺日销售额 1000+
需求3：Top3省份中，各省的平均单单价
需求4：Top3省份中，各个省份的支付类型比例
"""
from pyspark.sql import SparkSession
from pyspark.sql import functions as F
from pyspark.sql.types import StringType
import os
from pyspark.storagelevel import StorageLevel

if __name__ == '__main__':
    os.environ['HADOOP_USER_NAME'] = "hadoop"
    spark = SparkSession.builder.\
        appName("Spark Example").\
        master("local[*]").\
        config("spark.sql.warehouse.dir", "hdfs://node1:8020/user/hive/warehouse").\
        config("hive.metastore.uris", "thrift://node1:9083").\
        enableHiveSupport().\
        getOrCreate()
    # TODO 1.读取数据
    df = spark.read.format('json').load('../../data/input/mini.json')    # Linux本地下的路径
    # df = spark.read.format('json').load('/input/mini.json')    # hdfs下的路径
    # 缺失值处理，storeProvince列至少得有一个有效值，即该列为空就删；storeProvince列不得为null
    # 订单金额超过10000的是测试数据，也要过滤掉
    # 列值裁剪（只用5个列，其余列可以裁掉，虽然SparkSQL会自动优化，但是可以自己写一遍，保险）
    df = df.dropna(thresh=1, subset=['storeProvince']).\
        filter('storeProvince != "null"').\
        filter('receivable < 10000').\
        select('storeProvince', 'storeID', 'receivable', 'dateTS', 'payType')
    # df.show()

    # TODO 需求1：各省份销售额统计。需要用到 receivable字段(订单金额)
    province_sale_df = df.groupBy('storeProvince').sum('receivable').\
        withColumnRenamed('sum(receivable)', 'money').\
        withColumn('money', F.round('money', 2)).\
        orderBy('money', ascending=False)
    province_sale_df.show()

    # 写出mysql
    province_sale_df.write.mode('overwrite').\
        format('jdbc').\
        option("url", "jdbc:mysql://node1:3306/bigdata?useSSL=false&useUnicode=true&characterEncoding=utf8").\
        option('dbtable', 'province_sale').\
        option('user', 'root').\
        option('password', '123456'). \
        option('encoding', 'utf-8'). \
        save()

    # 写出hive表(需要确保配置好了SparkOnHive)
    province_sale_df.write.mode('overwrite').\
        saveAsTable('default.province_sale', 'parquet')    # 指定表名和存储格式，表会自动创建

    # TODO 需求2：Top3销售额省份中，有多少家店铺日销售额 1000+。需要用到 storeProvince字段(店铺省份)、dateTS(订单日期)
    # 2.1. 先找到Top3 的省份
    # 修改下列名，因为后面join时两个表都有storeProvince字段，后续对joined表groupby时会报错，有两个同名列 ambiguous
    top3_province_df = province_sale_df.limit(3).select('storeProvince').withColumnRenamed('storeProvince', 'top3_province')

    # 2.2. 和原始数据进行内关联join，筛选出top3省份的数据
    top3_province_df_joined = df.join(top3_province_df, on=df['storeProvince'] == top3_province_df['top3_province'])
    top3_province_df_joined.persist(StorageLevel.MEMORY_AND_DISK)    # 由于这个df后面还要用到，因此可以先缓存一下，提升性能

    # 2.3. 分组聚合，需要三个粒度：省 店铺 日期
    # 原始数据中的日期是一个时间戳，可以通过F.from_unixtime()将时间戳转换为时间，但F的精度是秒级，时间戳为毫秒级，因此需要对时间戳进行裁剪
    # 广东省 店铺1 2021-01-01 99.8   ...
    # alias可以针对column对象进行改名，而如果要改一个表中的某列列名就需要用到withcolumnRename了
    # 同一个店铺可能有多天日销售额1000+，因此需要根据店铺ID去重
    province_hot_store_count_df = 
    	top3_province_df_joined.groupBy('storeProvince', 'storeID', F.from_unixtime(df['dateTS'].substr(0, 10), 'yyyy-MM-dd').alias('day')).\
        sum('receivable').\
        withColumnRenamed('sum(receivable)', 'money').\
        filter('money > 1000').\
        dropDuplicates(subset=['storeProvince', 'storeID']).\
        groupBy('storeProvince').count()
  
    # TODO 需求3：Top3省份中，各省的平均订单价格。需要用到 storeProvince字段(店铺省份)、dateTS(订单日期)
    top3_province_order_avg_df = top3_province_df_joined.\
        groupBy('storeProvince').\
        avg('receivable').\
        withColumnRenamed('avg(receivable)', 'money').\
        withColumn('money', F.round('money', 2)).\
        orderBy('money', ascending=False)
    top3_province_order_avg_df.show()

    # TODO 需求4：Top3省份中，各个省份的支付类型比例。需要用到 payType字段(支付类型)
    # 湖南 支付宝 33%
    # 湖南 现金 66%
    top3_province_df_joined.createTempView('province_pay')

    # 定义udf，用于转换百分比的表现形式
    def udf_function(percent):
        return str(round(percent * 100, 2)) + "%"
    # 注册udf
    my_udf = F.udf(udf_function, StringType())
    pay_type_df = spark.sql("""
        select storeProvince, payType, count(payType)/total as percent from
        (select storeProvince, payType, count(1) over(partition by storeProvince) as total from province_pay) as sub
        group by storeProvince, payType, total
    """).withColumn('percent', my_udf('percent'))
    pay_type_df.show()
    # 释放缓存
    top3_province_df_joined.unpersist()