案例来源：黑马程序员Spark全套视频教程教程 P138~P141
案例数据：mini.json（118.62M）
环境要求
# 虚拟机需要安装配置 hadoop、hive、spark
# 启动 hadoop
$HADOOP_HOME/sbin/start-dfs.sh
# 启动 hive 的 MetaStore 服务（元数据服务）
nohup /export/server/hive/bin/hive --service metastore 2>&1 >> /var/log/metastore.log &
# 启动 thriftserver
/export/server/spark/sbin/start-thriftserver.sh \
--hiveconf hive.server2.thrift.port=10000 \
--hiveconf hive.server2.thrift.bind.host=node1
--master local[2]
# 查看 hadoop是否启动
[root@node1 ~]# jps
129904 NameNode
1217 Jps
130310 DataNode
# 查看 MetaStore、thriftserver是否启动
[root@node1 ~]# netstat -anp | grep 9083
tcp6 0 0 :::9083 :::* LISTEN 2823/java
[root@node1 ~]# netstat -anp | grep 10000
tcp6 0 0 192.168.88.161:10000 :::* LISTEN 4673/java
本地编码在虚拟机上运行的其他配置
# Pycharm 通过 SSH 连接虚拟机的解释器
/root/anaconda3/envs/pyspark_env/bin/python
# Pycharm 远程连接到虚拟机，部署传输数据和代码，映射路径为
/tmp/pycharm_project_105/
读取数据
"""
需求1: 各省销售额的统计
需求2: TOP3销售省份中, 有多少店铺达到过日销售额1000+
需求3: TOP3省份中, 各省的平均单单价
需求4: TOP3省份中, 各个省份的支付类型比例
receivable: 订单金额
storeProvince: 店铺省份
dateTS: 订单的销售日期（时间戳）
payType: 支付类型
storeID: 店铺ID
"""
# 创建 SparkSession
spark = SparkSession.builder. \
appName("案例"). \
master("local[*]"). \
config("spark.sql.shuffle.partitions", 2). \
config("spark.sql.warehouse.dir", "hdfs://node1:8020/user/hive/warehouse"). \
config("hive.metastore.uris", "thrift://node1:9083"). \
enableHiveSupport(). \
getOrCreate()
# 读取数据
# 省份信息, 缺失值过滤, 同时省份信息中 会有"null" 字符串
# 订单的金额, 数据集中有的订单的金额是单笔超过10000的, 这些是测试数据
# 列值裁剪(SparkSQL会自动做这个优化)
file_path = "./data/input/mini.json"
df = spark.read.format("json").load(file_path). \
dropna(thresh=1, subset=["storeProvince"]). \
filter("storeProvince != 'null'"). \
filter("receivable < 10000"). \
select("storeProvince", "storeID", "dateTS", "receivable", "payType")
# +--------------+-------+-------------+----------+-------+
# | storeProvince|storeID| dateTS|receivable|payType|
# +--------------+-------+-------------+----------+-------+
# | 湖南省| 4064|1563758583000| 22.5| alipay|
# | 湖南省| 718|1546737450000| 7.0| alipay|
# | 湖南省| 1786|1546478081000| 10.0| cash|
# | 广东省| 3702|1559133703000| 10.5| wechat|
# |广西壮族自治区| 1156|1548594458000| 10.0| cash|
# +--------------+-------+-------------+----------+-------+
df.show(n=5)
需求1：各省销售额的统计
# TODO 需求1: 各省销售额统计
province_sale_df = df.groupBy("storeProvince").sum("receivable").\
withColumnRenamed("sum(receivable)", "money").\
withColumn("money", F.round("money", 2)).\
orderBy("money", ascending=False)
# +--------------+----------+
# |storeProvince |money |
# +--------------+----------+
# |广东省 |1713207.92|
# |湖南省 |1701303.53|
# |广西壮族自治区|37828.22 |
# |北京市 |10926.91 |
# |上海市 |7358.5 |
# |江苏省 |6357.9 |
# |浙江省 |4568.1 |
# |山东省 |664.0 |
# |江西省 |553.5 |
# +--------------+----------+
province_sale_df.show(truncate=False)
# 得到的数据，写入到 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表 saveAsTable 可以写出表 要求已经配置好Spark On Hive, 配置好后
# 会将表写入到Hive的数据仓库中
# 在 saprk 数据库中查到数据
province_sale_df.write.mode("overwrite").\
saveAsTable("province_sale","parquet")
需求2：TOP3销售省份中, 有多少店铺达到过日销售额1000+
# TODO 需求2: TOP3销售省份中, 有多少店铺达到过日销售额1000+
# 2.1 先找到TOP3的销售省份
top3_province_df = province_sale_df.select("storeProvince").\
limit(3).\
withColumnRenamed("storeProvince","top3_province")
# +--------------+
# | storeProvince|
# +--------------+
# | 广东省|
# | 湖南省|
# |广西壮族自治区|
# +--------------+
top3_province_df.show()
# 2.2 和 原始的DF进行内关联, 数据关联后, 就是全部都是TOP3省份的销售数据了
top3_province_df_joined = df.join(top3_province_df,on=(df["storeProvince"] == top3_province_df["top3_province"]))
# +--------------+-------+-------------+----------+-------+--------------+
# | storeProvince|storeID| dateTS|receivable|payType| storeProvince|
# +--------------+-------+-------------+----------+-------+--------------+
# | 湖南省| 4064|1563758583000| 22.5| alipay| 湖南省|
# | 湖南省| 718|1546737450000| 7.0| alipay| 湖南省|
# | 湖南省| 1786|1546478081000| 10.0| cash| 湖南省|
# | 广东省| 3702|1559133703000| 10.5| wechat| 广东省|
# |广西壮族自治区| 1156|1548594458000| 10.0| cash|广西壮族自治区|
# +--------------+-------+-------------+----------+-------+--------------+
top3_province_df_joined.show(n=5)
# 存档一下数据
top3_province_df_joined.persist(StorageLevel.MEMORY_AND_DISK)
# from_unixtime的精度是秒级, 数据的精度是毫秒级, 要对数据进行精度的裁剪
# 根据 省份、店铺ID、时间（年月日）排序，计算日销售额
province_hot_store_count_df = top3_province_df_joined.groupBy("storeProvince","storeID",
F.from_unixtime(df["dateTS"].substr(0,10),format="yyyy-MM-dd").alias("day")).\
sum("receivable").withColumnRenamed("sum(receivable)","money").\
filter("money > 1000").\
dropDuplicates(subset = ["storeID"]).\
groupBy("storeProvince").\
count()
# +--------------+-----+
# | storeProvince|count|
# +--------------+-----+
# | 广东省| 105|
# | 湖南省| 97|
# |广西壮族自治区| 3|
# +--------------+-----+
province_hot_store_count_df.show()
# 得到的数据，写入到 mysql
province_hot_store_count_df.write.mode("overwrite").\
format("jdbc").\
option("url","jdbc:mysql://node1:3306/bigdata?useSSL=false&useUnicode=true&characterEncoding=utf8").\
option("dbtable","province_hot_store_count").\
option("user","root").\
option("password","123456").\
option("encoding","utf-8").\
save()
# 写出Hive表 saveAsTable 可以写出表 要求已经配置好Spark On Hive, 配置好后
# 会将表写入到Hive的数据仓库中
# 在 saprk 数据库中查到数据
province_hot_store_count_df.write.mode("overwrite").\
saveAsTable("province_hot_store_count","parquet")
持久化存储 数据
# 将数据 top3_province_df_joined 持久化到内存和磁盘中
top3_province_df_joined.persist(StorageLevel.MEMORY_AND_DISK)
# 持久化是指将数据存储在内存中，以便后续计算时能够快速访问，而不是每次都从原始数据源重新计算
# persist() 可以避免重复计算，提高性能
# StorageLevel.MEMORY_AND_DISK 存储级别，将数据存储在内存中，如果内存不足，超出的部分将被存储到磁盘上
列名冲突
top3_province_df = province_sale_df.select("storeProvince").\
limit(3)
top3_province_df_joined = df.join(top3_province_df,on=(df["storeProvince"] == top3_province_df["storeProvince"]))
province_hot_store_count_df =
top3_province_df_joined.groupBy("storeProvince","storeID",
F.from_unixtime("dateTS",format="yyyy-MM-dd").alias("day")).\
sum("receivable").\
filter("sum(receivable) > 1000")
# 报错
# 在执行查询"top3_province_df_joined.groupBy("storeProvince"..."时
# storeProvince 这个字段存在于多个数据框中，导致 Spark 无法确定你指的是哪个字段。
pyspark.sql.utils.AnalysisException: Reference 'storeProvince' is ambiguous, could be: storeProvince, storeProvince.
# 解决方法
# 修改其中一个列名
top3_province_df = province_sale_df.select("storeProvince").\
limit(3).\
withColumnRenamed("storeProvince","top3_province")
时间戳转为 yyyy-MM-dd 格式
# 修改前
province_hot_store_count_df =
top3_province_df_joined.groupBy("storeProvince","storeID",
F.from_unixtime(df["dateTS"],format="yyyy-MM-dd").alias("day")).\
sum("receivable").\
filter("sum(receivable) > 1000")
# +-------------+-------+------------+---------------+
# |storeProvince|storeID| day|sum(receivable)|
# +-------------+-------+------------+---------------+
# | 湖南省| 2555|+50971-03-19| 3052.0|
# | 湖南省| 3116|+51094-08-30| 1376.0|
# | 广东省| 4111|+51361-12-26| 1150.0|
# | 广东省| 1475|+51132-03-18| 1279.0|
# | 广东省| 1865|+51051-04-06| 4113.99|
# +-------------+-------+------------+---------------+
province_hot_store_count_df.show(n=5)
# 修改后
province_hot_store_count_df =
top3_province_df_joined.groupBy("storeProvince","storeID",
F.from_unixtime(df["dateTS"].substr(0,10),format="yyyy-MM-dd").alias("day")).\
sum("receivable").\
filter("sum(receivable) > 1000")
# +-------------+-------+----------+---------------+
# | storeProvince | storeID | day | sum(receivable) |
# +-------------+-------+----------+---------------+
# | 湖南省 | 643 | 2018 - 12 - 29 | 1040.0 |
# | 广东省 | 2647 | 2019 - 03 - 14 | 3619.0 |
# | 湖南省 | 2555 | 2019 - 01 - 01 | 3052.0 |
# | 广东省 | 3695 | 2019 - 07 - 09 | 1093.0 |
# | 湖南省 | 627 | 2019 - 02 - 03 | 3040.0 |
# +-------------+-------+----------+---------------+
province_hot_store_count_df.show()
需求3：TOP3省份中, 各省的平均单价
# TODO 需求3: TOP3 省份中 各个省份的平均订单价格(单价)
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")
# +--------------+-----+
# | storeProvince|money|
# +--------------+-----+
# | 广东省|32.81|
# | 湖南省|36.86|
# |广西壮族自治区|40.03|
# +--------------+-----+
top3_province_order_avg_df.show()
# 得到的数据，写入到 mysql
top3_province_order_avg_df.write.mode("overwrite").\
format("jdbc").\
option("url","jdbc:mysql://node1:3306/bigdata?useSSL=false&useUnicode=true&characterEncoding=utf8").\
option("dbtable","top3_province_order_avg_df").\
option("user","root").\
option("password","123456").\
option("encoding","utf-8").\
save()
# 写出Hive表 saveAsTable 可以写出表 要求已经配置好Spark On Hive, 配置好后
# 会将表写入到Hive的数据仓库中
# 在 saprk 数据库中查到数据
top3_province_order_avg_df.write.mode("overwrite").\
saveAsTable("top3_province_order_avg_df","parquet")
需求4：TOP3省份中, 各个省份的支付类型比例
# TODO 需求4: TOP3 省份中, 各个省份支付类型的支付比例
# 创建临时表
top3_province_df_joined.createTempView("province_pay")
# 将 percent 修改为 % 2显示
def udf_func(percent):
return str(round(percent * 100,2)) + "%"
# 注册UDF
my_udf = F.udf(udf_func,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)
group by storeProvince,payType,total
""").withColumn("percent",my_udf("percent"))
# +--------------+--------+-------+
# | storeProvince| payType|percent|
# +--------------+--------+-------+
# | 广东省| wechat| 39.48%|
# | 广东省| cash| 52.91%|
# | 广东省|bankcard| 0.72%|
# | 广东省| alipay| 6.88%|
# |广西壮族自治区| cash| 73.23%|
# |广西壮族自治区| wechat| 21.59%|
# |广西壮族自治区| alipay| 4.23%|
# |广西壮族自治区|bankcard| 0.95%|
# | 湖南省| alipay| 4.24%|
# | 湖南省| cash| 70.81%|
# | 湖南省| wechat| 24.88%|
# | 湖南省|bankcard| 0.06%|
# +--------------+--------+-------+
pay_type_df.show()
# 得到的数据，写入到 mysql
pay_type_df.write.mode("overwrite").\
format("jdbc").\
option("url","jdbc:mysql://node1:3306/bigdata?useSSL=false&useUnicode=true&characterEncoding=utf8").\
option("dbtable","pay_type").\
option("user","root").\
option("password","123456").\
option("encoding","utf-8").\
save()
# 写出Hive表 saveAsTable 可以写出表 要求已经配置好Spark On Hive, 配置好后
# 会将表写入到Hive的数据仓库中
# 在 saprk 数据库中查到数据
pay_type_df.write.mode("overwrite").\
saveAsTable("default.pay_type","parquet")
###
