在线拍卖数据分析-基于华为云MapReduce、ModelArts-云计算与大数据综合实践

一、实验详情

本实验涉及数据预处理，数据存储，数据查询分析及可视化展示等大数据处理的全部操作流程。首先需配置部署在线拍卖数据分析系统所需要的环境，然后把数据集上传到 HDFS 分布式文件系统，利用 Hive 对在线拍卖数据进行分析处理，并利用 Python对分析结果进行可视化展示。这里使用华为云MR服务完成。最后选用逻辑回归、决策树、随机森林、XGBOOST、神经网络等多个模型来进行预测。可视化分析与结果预测均在华为云ModelArts上完成。

• 

二、数据集：

本实验使用 FTP 服务器上的 raw.tar.gz 压缩文件中的数据。raw.tar.gz 中包含 TestSet.csv、TestSubset.csv、TrainingSet.csv、TrainingSubset.csv，解压后选择 TrainingSet.csv 和 TestSet.csv 作为数据集，TrainingSet 中含有 40 万条的拍卖数据，TestSet 中有 3万条数据。raw.tar.gz 解压后包含以下 4 个 CSV 文件，本实验只使用其中的 TrainingSet.csv 和 TestSet.csv 文件。数据集具体含义这里不再赘叙。

三、实验步骤

(1)数据分析部分

1.购买弹性公网IP，使用华为云EIP进行购买，这里购买三个弹性公网IP以备后续使用2.配置MRS，根据华为云实验操作手册购买集群，并进行相关配置

3.开通MRS集群，等待十分钟左右运行成功

4.绑定弹性公网IP，选择master节点然后绑定前述中所购买的弹性公网IP

5.根据华为云实验操作手册进行安全配置，添加入方向规则，优先级设置为1，协议端口选择全部协议，其余保持默认

6.进入华为云CloudShell，使用如下命令创建相关文件夹

1. mkdir /home/zkpk  

2. mkdir /home/zkpk/raw  

7.安装MySQL，使用如下代码安装MySQL并解压

1. #安装  

2. wget  https://mirrors.tuna.tsinghua.edu.cn/mysql/downloads/MySQL-8.0/mysql-8.0.27-1.el8.aarch64.rpm-bundle.tar  

3. #解压  

4. tar -xvf mysql-8.0.27-1.el8.aarch64.rpm-bundle.tar  

解压后显示complete，完成

8.查看mysql运行状态并更改密码

1. systemctl start mysqld  

2. systemctl status mysqld  

使用如下命令查看随机默认密码

1. grep 'temporary password' /var/log/mysqld.log  

登录mysql后运行如下命令，修改密码为MyNewPass4!

1. # 创建远程连接用户  

2. create user 'zkpk'@'%' identified by 'MyNewPass4!';  

3. # 授予用户权限  

4. grant all on *.* to 'zkpk'@'%';  

5. # 更改加密方式  

6. ALTER USER 'zkpk'@'%' IDENTIFIED BY 'MyNewPass4!' PASSWORD EXPIRE NEVER;  

7. # 刷新权限  

8. flush privileges;  

9.使用WinSCP将相关文件上传到前述所创建的raw文件夹中

10.通过如下shell命令去除文件的首行字段

1. cd /home/zkpk/raw/  

2. sed -i '1d' TrainingSet.csv  

3. sed -i '1d' TestSet.csv  

11.使用如下命令将CSV文件上传到hadoop

1. hadoop fs -put TrainingSet.csv /zkpk/raw/  

2. hadoop fs -put TestSet.csv /zkpk/raw/  

3. hadoop fs -ls /zkpk/raw/  

12.进入hive，创建zkpk数据库

1. SHOW DATABASES;  

2. CREATE DATABASE zkpk;  

3. SHOW DATABASES;  

13.对训练集、测试集使用如下代码创建外部表

1. CREATE EXTERNAL TABLE trainingset_log(  

2. EbayID STRING,  

3. QuantitySold INT,  

4. Price FLOAT,  

5. PricePercent FLOAT,  

6. StartingBidPercent FLOAT,  

7. SellerName STRING,  

8. SellerClosePercent DOUBLE,  

9. Category INT,  

10. PersonID STRING,  

11. StartingBid FLOAT,  

12. AvgPrice FLOAT,  

13. EndDay STRING,  

14. HitCount INT,  

15. AuctionAvgHitCount INT,  

16. ItemAuctionSellPercent INT,  

17. SellerSaleAvgPriceRatio DOUBLE,  

18. SellerAvg DOUBLE,  

19. SellerItemAvg INT,  

20. AuctionHitCountAvgRatio INT,  

21. BestOffer DOUBLE,  

22. IsHOF INT,  

23. ItemListedCount INT,  

24. AuctionCount INT,  

25. AuctionSaleCount INT,  

26. SellerAuctionCount INT,  

27. SellerAuctionSaleCount INT,  

28. AuctionMedianPrice FLOAT)  

29. ROW FORMAT DELIMITED FIELDS TERMINATED BY ','  

30. STORED AS TEXTFILE;  

31.   

32.   

33. CREATE EXTERNAL TABLE testset_log(  

34. EbayID STRING,  

35. QuantitySold INT,  

36. Price FLOAT,  

37. PricePercent FLOAT,  

38. StartingBidPercent FLOAT,  

39. SellerName STRING,  

40. SellerClosePercent DOUBLE,  

41. Category INT,  

42. PersonID STRING,  

43. StartingBid FLOAT,  

44. AvgPrice FLOAT,  

45. EndDay STRING,  

46. HitCount INT,  

47. AuctionAvgHitCount INT,  

48. ItemAuctionSellPercent INT,  

49. SellerSaleAvgPriceRatio DOUBLE,  

50. SellerAvg DOUBLE,  

51. SellerItemAvg INT,  

52. AuctionHitCountAvgRatio INT,  

53. BestOffer DOUBLE,  

54. IsHOF INT,  

55. ItemListedCount INT,  

56. AuctionCount INT,  

57. AuctionSaleCount INT,  

58. SellerAuctionCount INT,  

59. SellerAuctionSaleCount INT,  

60. AuctionMedianPrice FLOAT)  

61. ROW FORMAT DELIMITED FIELDS TERMINATED BY ','  

62. STORED AS TEXTFILE;  

14.导入数据到相应的表

15.统计 TrainingSet 中拍买成功交易的平均成交价并保存

1. CREATE TABLE avg_price(avg_price FLOAT)  

2. ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;  

3.   

4. Insert OVERWRITE TABLE avg_price  

5. SELECT avg(Price) FROM trainingset_log WHERE QuantitySold=1; 

最终结果为33.03668

16.统计 TrainingSet 中金牌卖家的拍卖成功率，降序排列并保存

1. CREATE TABLE success_rate_temp(SellerName STRING,Rate DOUBLE)  

2. ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;  

3.   

4. INSERT OVERWRITE TABLE success_rate_temp   

5. SELECT SellerName,sum(QuantitySold)/count(QuantitySold)  

6. FROM trainingset_log WHERE IsHOF=1 GROUP BY SellerName;  

7.   

8. CREATE TABLE success_rate_desc(SellerName STRING,Rate DOUBLE)  

9. ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;  

10.   

11. INSERT OVERWRITE TABLE success_rate_desc   

12. SELECT * FROM success_rate_temp ORDER BY Rate DESC;  

13.   

14. drop table success_rate_temp;  

17.统计TrainingSet和TestSet中周一到周日，每天拍卖成功的数量及拍卖成功率并保存

1. CREATE TABLE train_day_rate(EndDay STRING,Success INT,Rate DOUBLE)   

2. ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;  

3.   

4. INSERT OVERWRITE TABLE train_day_rate   

5. SELECT EndDay,sum(QuantitySold),sum(QuantitySold)/count(QuantitySold)   

6. FROM trainingset_log GROUP BY EndDay;  

7.   

8.   

9. CREATE TABLE test_day_rate(EndDay STRING,Success INT,Rate DOUBLE)   

10. ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;  

11.   

12. INSERT OVERWRITE TABLE test_day_rate   

13. SELECT EndDay,sum(QuantitySold),sum(QuantitySold)/count(QuantitySold)   

14. FROM testset_log GROUP BY EndDay;  

18.筛选出TrainingSet和TestSet数据中的EbayID，Quantitiysold字段，保存为train_label文件和test_label文件

1. CREATE TABLE train_label(EbayID STRING, QuantitySold Int)  

2. ROW FORMAT DELIMITED FIELDS TERMINATED BY ','  

3. STORED AS TEXTFILE;  

4.   

5. INSERT OVERWRITE TABLE train_label SELECT  

6. EbayID, QuantitySold FROM trainingset_log;  

7.   

8.   

9. CREATE TABLE test_label(EbayID STRING, QuantitySold Int)  

10. ROW FORMAT DELIMITED FIELDS TERMINATED BY ','  

11. STORED AS TEXTFILE;  

12.   

13. INSERT OVERWRITE TABLE test_label SELECT  

14. EbayID, QuantitySold FROM testset_log;  

19.从TrainingSet和TestSet数据中删除的SellerName，QuantiySold，EndDay字段，并将数据导出保存为train_data文件和test_data文件

1. CREATE TABLE train_data (  

2. EbayID STRING,  

3. Price FLOAT,  

4. PricePercent FLOAT,  

5. StartingBidPercent FLOAT,  

6. SellerClosePercent DOUBLE,  

7. Category INT,  

8. PersonID STRING,  

9. StartingBid FLOAT,  

10. AvgPrice FLOAT,  

11. HitCount INT,  

12. AuctionAvgHitCount INT,  

13. ItemAuctionSellPercent INT,  

14. SellerSaleAvgPriceRatio DOUBLE,  

15. SellerAvg DOUBLE,  

16. SellerItemAvg INT,  

17. AuctionHitCountAvgRatio INT,  

18. BestOffer DOUBLE,  

19. IsHOF INT,  

20. ItemListedCount INT,  

21. AuctionCount INT,  

22. AuctionSaleCount INT,  

23. SellerAuctionCount INT,  

24. SellerAuctionSaleCount INT,  

25. AuctionMedianPrice FLOAT)  

26. ROW FORMAT DELIMITED FIELDS TERMINATED BY ','  

27. STORED AS TEXTFILE;  

28.   

29. INSERT OVERWRITE TABLE train_data SELECT   

30. EbayID,Price,PricePercent,StartingBidPercent,SellerClosePercent,Category,PersonID,StartingBid,  

31. AvgPrice,HitCount,AuctionAvgHitCount,ItemAuctionSellPercent,SellerSaleAvgPriceRatio,SellerAvg,  

32. SellerItemAvg,AuctionHitCountAvgRatio,BestOffer,IsHOF,ItemListedCount,AuctionCount,  

33. AuctionSaleCount,SellerAuctionCount,SellerAuctionSaleCount,AuctionMedianPrice   

34. FROM trainingset_log;  

35.   

36.   

37. CREATE TABLE test_data (  

38. EbayID STRING,  

39. Price FLOAT,  

40. PricePercent FLOAT,  

41. StartingBidPercent FLOAT,  

42. SellerClosePercent DOUBLE,  

43. Category INT,  

44. PersonID STRING,  

45. StartingBid FLOAT,  

46. AvgPrice FLOAT,  

47. HitCount INT,  

48. AuctionAvgHitCount INT,  

49. ItemAuctionSellPercent INT,  

50. SellerSaleAvgPriceRatio DOUBLE,  

51. SellerAvg DOUBLE,  

52. SellerItemAvg INT,  

53. AuctionHitCountAvgRatio INT,  

54. BestOffer DOUBLE,  

55. IsHOF INT,  

56. ItemListedCount INT,  

57. AuctionCount INT,  

58. AuctionSaleCount INT,  

59. SellerAuctionCount INT,  

60. SellerAuctionSaleCount INT,  

61. AuctionMedianPrice FLOAT)  

62. ROW FORMAT DELIMITED FIELDS TERMINATED BY ','  

63. STORED AS TEXTFILE;  

64.   

65. INSERT OVERWRITE TABLE test_data SELECT   

66. EbayID,Price,PricePercent,StartingBidPercent,SellerClosePercent,Category,PersonID,StartingBid,  

67. AvgPrice,HitCount,AuctionAvgHitCount,ItemAuctionSellPercent,SellerSaleAvgPriceRatio,SellerAvg,  

68. SellerItemAvg,AuctionHitCountAvgRatio,BestOffer,IsHOF,ItemListedCount,AuctionCount,  

69. AuctionSaleCount,SellerAuctionCount,SellerAuctionSaleCount,AuctionMedianPrice   

70. FROM testset_log;  

20.查看所有结果表

21.从HDFS导出数据到本地文件系统

首先查看表格在HDFS上的存储位置，为hdfs://hacluster/user/hive/warehouse/zkpk.db/

然后用hadoop fs -put命令导出到本地文件系统，即创建的/home/zkpk/result目录下

22.导入数据到MySQL数据库中

首先在MySQL中创建对应的表，如下分别导出 TrainingSet 和 TestSet 中周一到周日，每天拍卖成功的数量及拍卖成功率的统计结果，需要创建表train_day_rate,test_day_rate

1. CREATE TABLE train_day_rate(EndDay varchar(60),Success int,Rate double);  

2. CREATE TABLE test_day_rate(EndDay varchar(60),Success int,Rate double); 

然后使用如下命令，导入到mysql数据库成功

23.在mysql中查看结果

24.对其余表格进行同样操作，导入到MySQL数据库中，最终结果如下

(2)数据可视化部分

首先配置ModelArts环境，选择tensorflow2.1-cuda10.1-cudnn7-ubuntu18.04，CPU选择8核32GB

然后进入notebook开发环境，导入文件，使用pip命令安装相关包

最后编写代码，利用python 中 pandas 库的 dataframe 加载数据，再利用 matplotlib 绘制图形，代码如下

1. import numpy as np  

2. import matplotlib  

3. import pandas as pd  

4. import matplotlib.pyplot as plt  

5.   

6. matplotlib.rcParams['font.sans-serif'] = ['SimHei']  

7. matplotlib.rcParams['axes.unicode_minus'] = False  

8.   

9. df = pd.read_csv('../train_day_rate.csv', sep=',')  

10. print(df)  

11.   

12. plt.ylabel("拍卖成功数量")  

13. plt.xlabel("时间")  

14. p1 = plt.bar(df['endday'], df['success'], color='red')  

15. plt.bar_label(p1, labels=df['success'])  

16. plt.title('train_data-success', fontproperties='SimHei', fontsize=15)  

17. plt.savefig("train_data-success.png")  

18. plt.show()  

19.   

20. plt.ylabel("拍卖成功率")  

21. plt.xlabel("时间")  

22. p1 = plt.bar(df['endday'], df['rate'], color='blue')  

23. plt.title('train_data-rate', fontproperties='SimHei', fontsize=15)  

24. plt.savefig("train_data-rate.png")  

25. plt.show()  

Trainingset数据中每天拍卖成功数量和成功率柱形图(左为成功数量，右为成功率)：

TestSet 数据中每天拍卖成功数量和成功率柱形图(左为成功数量，右为成功率)：

综上所述，trainingset中Thursday拍卖成功率最高，Testset中Tuesday拍卖成功率最高

(3)数据预测部分

数据预处理后的数据展示：

逻辑回归、决策树、随机森林做预测的准确度如下(此处未进行算法调优)：

这三类预测模型得到的结果并不理想，但是相比于Xgboost和BP神经网络训练速度更快。

BPNN

设置模型参数hidden_layer_sizes=(100,),learning_rate_init=0.05，结果如下：

Xgboost

设置模型参数n_estimatores=50(迭代次数为50), learning_rate=0.1(学习率为0.1), max_depth=5(树最大深度为5)，得到结果较优，考虑到训练时间和资源的问题，这里没有用网格搜索调优参数。

项目代码：