文章目录
- 需求分析
- 数据格式
- 问题记录
- 代码
需求分析
一个做零售相关业务的公司,旗下出品各类收银机
机器每次使用都会将售卖的商品数据上传到公司后台
老板现在想对 省份维度 的 销售情况 进行统计分析
4个需求
- 各省 销售指标,每个省的销售额统计
- Top3 销售省份中,有多少家店铺日均销售额 1000+
- Top3 省份中,各省的平均单单价
- Top3 省份中,各省的支付类型比例
2个操作
- 将需求结果写出到 mysql
- 将数据写入到 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()