案例任务
 
对淘宝双十一数据进行预处理、存储、查询和可视化分析。
 
项目步骤
 
0. 安装环境
 
Ubuntu18.04
 Java 1.8
 MySQL 5.7.31
 Hadoop 3.1.3
 Hive 3.1.2
 Spark 2.4.0
 Sqoop 1.4.7
 Eclipse 4.14
 Pycharm 2020.2
 
1. 预处理和存储
 
a. 将原始数据集进行预处理
 
b. 把文本文件的数据集导入数据仓库Hive中
 
1. 数据集下载与查看
 2. 数据集预处理
 3. 导入HDFS
 4. 在Hive上创建数据库 
 
2. Hive数据分析
 
3. 导入MySQL
 
1. hive创建内表
 2. MySQL建表
 3. 使用Sqoop将数据从Hive导入MySQL
 
4. 利用Spark预测回头客
 
1. 预处理训练集和测试集
 2. 预测
 
 

 

  一、预处理
 
 
 
 
训练技能：
 
 
HDFS基本操作
Hive基本操作之创建数据库和建表
基本数据预处理方法
 
 
任务清单：
 
 
将原始数据集进行预处理
把文本文件的数据集导入数据仓库Hive中
 
 

 

  1. 数据集下载与查看
 
 
数据集下载。该数据集是淘宝2015年双11前6个月(包含双11)的交易数据(交易数据有偏移，但是不影响实验的结果)，里面包含3个文件，分别是用户行为日志文件user_log.csv、回头客训练集train.csv、回头客测试集test.csv.
 下面列出这3个文件的数据格式定义：
 user_log.csv，日志中的字段定义如下：
 
user_id 买家id
item_id 商品id
cat_id 商品类别id
merchant_id 卖家id
brand_id 品牌id
month 交易时间:月
day 交易事件:日
action 行为,取值范围{0,1,2,3},0表示点击，1表示加入购物车，2表示购买，3表示关注商品
age_range 买家年龄分段：1表示年龄<18,2表示年龄在[18,24]，3表示年龄在[25,29]，4表示年龄在[30,34]，
 5表示年龄在[35,39]，6表示年龄在[40,49]，7和8表示年龄>=50,0和NULL则表示未知
gender 性别:0表示女性，1表示男性，2和NULL表示未知
province 收货地址省份
 
train.csv,test.csv，训练集和测试集拥有相同的字段，字段定义如下：
 
user_id 买家id
age_range 买家年龄分段
gender 性别
merchant_id 商家id
label 是否是回头客，0值表示不是回头客，1值表示回头客，-1值表示该用户已经超出我们所需要考虑的预测范围。
 NULL值只存在测试集，在测试集中表示需要预测的值。
 
$ head -5 user_log.csv
 
 
 
user_id,item_id,cat_id,merchant_id,brand_id,month,day,action,age_range,gender,province
 328862,323294,833,2882,2661,08,29,0,0,1,内蒙古
 328862,844400,1271,2882,2661,08,29,0,1,1,山西
 328862,575153,1271,2882,2661,08,29,0,2,1,山西
 328862,996875,1271,2882,2661,08,29,0,1,1,内蒙古
 
 

 

  2. 数据集预处理
 
 
删除字段名称：数据仓库Hive不需要第一行字段名称
 
$ sed -i '1d' user_log.csv
 
1d表示删除第1行，nd表示删除第n行。
 

 

  3. 导入HDFS
 
 
把user_log.csv上传到HDFS中，启动hadoop
 
$ hdfs dfs -mkdir -p /taobao/dataset/user_log
$ hdfs dfs -put user_log.csv /taobao/dataset/user_log
 
可在http://localhost:9870上看到上传成功的数据集。
 
 

 

  4. 在Hive上创建数据库
 
 
1. 启动mysql和hive
 
$ service mysql start
$ hive
 
2. 在hive上创建数据库
 
数据库名：taobao
 
hive> create database taobao;
hive> use taobao;
 
3. 创建表
 
 
 
hive内表和外表的区别：
 1、在导入数据到外部表，数据并没有移动到自己的数据仓库目录下，也就是说外部表中的数据并不是由它自己来管理。
 2、在删除表的时候，Hive将会把属于表的元数据和数据全部删掉；而删除外部表的时候，Hive仅仅删除外部表的元数据，数据是不会删除的！
 那么，应该如何选择使用哪种表呢？在大多数情况没有太多的区别，因此选择只是个人喜好的问题。
 但是作为一个经验，如果所有处理都需要由Hive完成，那么应该创建表，否则使用外部表。
 
 
内表例子：
 
hive> create table test(id int,
    > name string,
    > age int,
    > tel string)
    > row format delimited
    > fields terminated by '\t'
    > stored as textfile;
 
外部表例子：
 
hive> create external table exter_table(
    > id int,
    > name string,
    > age int,
    > tel string)
    > row format delimited
    > fields terminated by '\t'
    > stored as textfile location '/home/qnstar/external';	#HDFS地址
 
 
表名：user_log. 类型：外部表，包含字段：
 
user_id int 买家id
item_id int 商品id
cat_id int 商品类别id
merchant_id int 卖家id
brand_id int 品牌id
month string 交易时间:月
day string 交易事件:日
action int 行为,取值范围{0,1,2,3},0表示点击，1表示加入购物车，2表示购买，3表示关注商品
age_range int 买家年龄分段：1表示年龄<18,2表示年龄在[18,24]，3表示年龄在[25,29]，4表示年龄在[30,34]，
 5表示年龄在[35,39]，6表示年龄在[40,49]，7和8表示年龄>=50,0和NULL则表示未知
gender int 性别:0表示女性，1表示男性，2和NULL表示未知
province string 收货地址省份
 
hive> create external table user_log(
    > user_id int,
    > item_id int,
    > cat_id int,
    > merchant_id int,
    > brand_id int,
    > month string,
    > day string,
    > action int,
    > age_range int,
    > gender int,
    > province string)
    > comment 'create taobao.user_log'
    > row format delimited
    > fields terminated by ','
    > stored as textfile location '/taobao/dataset/user_log';
 
hive> select *from user_log limit 10;
OK
328862	323294	833	2882	2661	08	29	0	0	1	内蒙古
328862	844400	1271	2882	2661	08	29	0	1	1	山西
328862	575153	1271	2882	2661	08	29	0	2	1	山西
328862	996875	1271	2882	2661	08	29	0	1	1	内蒙古
328862	1086186	1271	1253	1049	08	29	0	0	2	浙江
328862	623866	1271	2882	2661	08	29	0	0	2	黑龙江
328862	542871	1467	2882	2661	08	29	0	5	2	四川
328862	536347	1095	883	1647	08	29	0	7	1	吉林
328862	364513	1271	2882	2661	08	29	0	1	2	贵州
328862	575153	1271	2882	2661	08	29	0	0	0	陕西
Time taken: 2.493 seconds, Fetched: 10 row(s)
 

 

  二、Hive数据分析
 
 
 
 
训练技能：
 
 
Hive基本操作之SQL查询分析
MySQL的基本操作
Sqoop的使用方法
 
 
任务清单：
 
 
对数据进行查询分析
 
 
1. 查看hive的表
 
启动hadoop，hive
 
hive> use taobao;
hive> show create table user_log;	#查看表的各种属性
hive> desc user_log					#查看表的简单结构
 
2. 简单查询分析
 
hive> select brand_id from user_log limit 10;		#查看日志前10个交易日志的商品品牌
 
3. 查询条数统计分析
 
（1）count()
 
hive> select count(*) from user_log;	#计算表内有多少条行数据
 

 （2）distinct 过滤，去重，不重复user_id的数据有多少条
 
select count(distinct user_id) from user_log;
 
（3）排除掉重复信息
 WHERE关键字在使用集合函数时不能使用，所以在集合函数中加上了HAVING来起到测试查询结果是否符合条件的作用。
 
hive> select count(*) from (select user_id,item_id,cat_id,merchant_id,brand_id,month,day,action from user_log group by user_id,item_id,cat_id,merchant_id,brand_id,month,day,action having count(*)=1)a;
 
4. 关键字条件查询分析
 
（1）查询双11那天有多少人购买了商品
 
hive> select count(distinct user_id) from user_log where action='2' and month=11 and day=11;
 
（2）取给定时间和给定品牌，求当天购买的此品牌商品的数量
 
hive> select count(distinct user_id) from user_log where action='2' and month=11 and day=11 and brand_id=2661;
 
5. 用户实时查询分析
 
不同的品牌的浏览次数
 
hive> create table scan(brand_id INT,scan INT) COMMENT 'This is the search of bigdatataobao' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS TEXTFILE; -- 创建新的数据表进行存储
hive> insert overwrite table scan select brand_id,count(action) from user_log where action='2' group by brand_id; --导入数据
hive> select * from scan; -- 显示结果
 

 

  三、导入MySQL
 
 
 
 
任务清单
 
 
Hive预操作
使用Sqoop把Hive的数据导入MySQL中
 
 

 

  1. hive创建内表
 
 
hive> create table inner_user_log(user_id INT,item_id INT,cat_id INT,merchant_id INT,brand_id INT,month STRING,day STRING,action INT,age_range INT,gender INT,province STRING) COMMENT 'Welcome to XMU dblab! Now create inner table inner_user_log ' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;
 
#插入数据
hive>  insert overwrite table inner_user_log select * from user_log;
 

 

  2. MySQL建表
 
 
MySql中用一对反引号"`"来标注SQL语句中的标识，如数据库名、表名、字段名等。zhi反dao引号是非必须的，在确保不会导致歧义发生的情况下可以省略掉。
 
mysql> create table `user_log` (
    -> `user_id` varchar(20),
    -> `item_id` varchar(20),
    -> `cat_id` varchar(20),
    -> `merchant_id` varchar(20),
    -> `brand_id` varchar(20),
    -> `month` varchar(6),
    -> `day` varchar(6),
    -> `action` varchar(6),
    -> `age_range` varchar(6),
    -> `gender` varchar(6),
    -> `province` varchar(10))
    -> engine=InnoDB
    -> default charset=utf8;
#sqoop抓数据的时候会把类型转为string类型，所以mysql设计字段的时候，设置为varchar。
 

 

  3. 使用Sqoop将数据从Hive导入MySQL
 
 
cd /usr/local/sqoop
bin/sqoop export --connect jdbc:mysql://localhost:3306/taobao --username root --password root --table user_log --export-dir '/user/hive/warehouse/taobao.db/inner_user_log' --fields-terminated-by ',';
 
 

 

  四、利用Spark预测回头客
 
 
 
 
任务清单
 
 
预处理训练集和测试集
预测回头客
 
 

 

  1. 预处理训练集和测试集
 
 
（1）把test.csv数据集里label字段表示-1值剔除掉,保留需要预测的数据.并假设需要预测的数据中label字段均为1.
 
import pandas as pd
df=pd.read_csv('/home/qnstar/Documents/datasets/data_format/test.csv')
df['label']=df['label'].astype(str)		#将类型转换为string才能替换
df1=df[~df['label'].isin([-1])]
df['label']=df['label'].replace('nan','1')
with open('/home/qnstar/Documents/datasets/data_format/test.txt','a+',encoding='utf-8') as f:
    for line in df.values:
        f.write((str(line[0])+','+str(line[1])+','+str(line[2])+','+str(line[3])+','+str(line[4])+'\n'))

 
（2）剔除掉train.csv中字段值部分字段值为空的数据
 
import pandas as pd
df=pd.read_csv('/home/qnstar/Documents/datasets/data_format/train.csv')
df1=df.dropna(axis = 0)
df1['age_range']=df1['age_range'].astype(int)
df1['gender']=df1['gender'].astype(int)
df1=df1[~df1['label'].isin([-1])]
print(df1.dtypes)
with open('/home/qnstar/Documents/datasets/data_format/train.txt','a+',encoding='utf-8') as f:
    for line in df1.values:
        f.write((str(line[0])+','+str(line[1])+','+str(line[2])+','+str(line[3])+','+str(line[4])+'\n'))
 
（3）删除train.csv字段名
 
sed -i '1d' train.csv
 

 

  2. 预测
 
 
用支持向量机进行预测
 
from pyspark.ml.classification import LinearSVC
from pyspark.ml.linalg import Vectors
from pyspark.sql import SparkSession,Row
from pyspark.ml.evaluation import MulticlassClassificationEvaluator
#把文件拆分为标签列和特征列
def splitDF(x):
    rel={}
    rel['features']=Vectors.dense(int(x[0]),int(x[1]),int(x[2]),int(x[3]))
    rel['label']=int(x[4])
    return rel

spark=SparkSession.builder.master("local").appName("double11").getOrCreate()
# 1.加载数据    封装为row对象，转换为dataframe类型，第一列为特征，第二列为标签
training = spark.sparkContext.textFile("hdfs://localhost:9000/taobao/dataset/train.txt").map(lambda line:line.split(',')).map(lambda p:Row(**splitDF(p))).toDF()
testing = spark.sparkContext.textFile("hdfs://localhost:9000/taobao/dataset/test.txt").map(lambda line:line.split(',')).map(lambda p:Row(**splitDF(p))).toDF()
#2.构建模型
dsvc = LinearSVC(maxIter=10, regParam=0.1).setFeaturesCol("label").setFeaturesCol('features')
#3.训练模型
dsvcModel = dsvc.fit(training)
dsvcPredictions=dsvcModel.transform(testing)
#4.输出预测结果
preRel=dsvcPredictions.select("prediction","label","features").collect()
for item in preRel:
    print(str(item['label'])+','+str(item['features'])+',prediction'+str(item['prediction']))
#准确率
evaluator=MulticlassClassificationEvaluator().setLabelCol("label").setPredictionCol("prediction")
dAccuracy=evaluator.evaluate(dsvcPredictions)
print(dAccuracy)

根据历年双十一数据进行多项式回归：分别用R、Spss、python进行了实现，发现Spss结果更好解释，拟合度达到0.99，将时间数据进行转为1，2，...
 
 
 
python的多项式回归结果还没太看懂，因为与R运行结果的回归系数不一样，希望看到的小伙伴能帮忙解释下；plt.scatter(x,y):圆形点；plt.plot(x,y,'*'):星形点
 
 
import matplotlib.pyplot as plt
import numpy as np
from pylab import *
mpl.rcParams['font.sans-serif'] = ['SimHei']
matplotlib.rcParams['axes.unicode_minus']=False


x=np.array([year for year in range(2009,2019)])
y=np.array([0.5,9.36,52,191,352,571,912,1207,1682.69,2135])

z1=np.polyfit(x,y,3)#用三次多项式拟合
p1=np.poly1d(z1)   # 生成多项式对象

yvals=p1(x)    #拟合值

plt.scatter(x,y,label='实际销售额')  
plt.plot(x,yvals,'r',label='拟合销售额')
plt.xlabel('年份')
plt.ylabel('销售额')
plt.legend(loc=4)  #指定legend的位置
plt.title('2009-2018淘宝双十一销售额拟合曲线')
plt.show()

print('拟合多项式:\n',p1)  #打印拟合多项式

print("-"*40)
print('2019年预测值：\n',p1(2019))


# x=2019
# y=0.159*(x**3) - 930.2*(x**2) + (1.813e+06)*x - (1.176e+09)
# print(y)
 
输出结果：
 
拟合多项式:
         3         2
 0.159 x - 930.2 x + 1.813e+06 x - 1.176e+09
 ----------------------------------------
 2019年预测值：
  2689.013335466385
 

Spss进行二次、三次多项式回归结果：
 
 
 
 

 
 

 
双十一狂欢刚刚落下帷幕，天猫累计成交额2684亿元。
 
正当外界庆祝再次刷新纪录的成交额之际，有一个发布于今年4月的观点被挖出，称“从天猫双十一的全天销售额来看，实际生产数据几乎完美地分布在三次回归曲线上，拟合度均超过99.94%...每一年数据都这么高度拟合，数据过于完美，属于小概率事件。”
 
因此，该观点断定：阿里对销售额数据进行了人工修饰，存在造假事实。
 
为此，一位大数据分析师对观察者网提出了不同的观点。他指出，双十一交易数据数造成本太高，阿里不敢也没必要造假。他补充道，阿里或许在事前沙盘推演时就定下目标，并按照该目标去营运和推动，“与其说预测‘牛X’还不如说阿里的营运‘牛X’”。
 

 
@尹立庆微博截图
 
质疑的观点称，双十一自发起到现在积累了10年数据，但每一年的数据拟合度均超过99.94%，几乎完美分布在三次回归曲线上，销售额与年份的增长趋势仿佛按预期设定的线性公式发展，是在现实生活中基本不可能发生的小概率事件。
 
该观点由此判断，历年双十一的销售数据存在造假，并且从一开始就在造假，甚至直呼“马云是个大骗子”。
 
一位预测过历年双十一交易额的大数据分析师对“造假”的说法持否定态度，他对观察者网表示，双十一交易数据数造成本太高，阿里不敢也没必要造假。
 
他提到，销售量是一定范围内可控的，可通过促销节奏、流量分配、客户压货、员工买单等方式进行过程控制，从而带来必然的结果把控。“商业预测讲究存在即合理，在商业场景的内在规律没有大变化的情况下，预测的数值和实际数值偏差在±5%以内都可以视为比较准确”。
 
而对于2019年天猫双十一的成交额，这位人士在双十一前使用Excel自带的趋势回归预测模型计算出的预测区间是2566-2676亿元。
 
关于双十一增长趋势的质疑，该业内人士指出，虽然双十一不可能一直保持增长是共识，“但今年一定不是增长曲线的拐点”。
 
对此，天猫公关总监彭美在朋友圈称这种预测看起来很“唬人”。她反问，“按照这位网友的逻辑，符合统计趋势的就是假的，那么世界经济总量也是能被预测的，经济发展也是假的吗”？
 

 
天猫公关总监朋友圈截图
 
同时，有网友认为，数据造假可能性不大，但销售额数据拟合确实存在，他推测阿里通过预测群体喜好控制流量推送来控制销售额，实际销售额跟目标越接近，说明模型越成功。
 

 
还有网友称，很多参与讨论的所谓大V的生活环境里造假是常态化或默认的，以己推人而已。“作为一个电商从业者，盘子越大，预测准确度越高”。
 

 
也有网友表示怀疑，在京东和拼多多加入战团的情况下，还能保持如此准确的线性增长值得怀疑。
 

 
有网友称很多合作的品牌有刷单指标，“数据肯定造假”。
 

 
有意思的是，认证为“原阿里集团安全研究实验室总监”的微博用户“安全_云舒”，也表示阿里控制交易额很容易，根本没必要伪造数据或刷单。
 

 
那你觉得数据是真还是假呢？
 
来说说你的观点吧~~

 

 
完
 

 
“有点意思~”
 

双11已经结束，按照天猫官方公布的最终数据看，今年的双11成交额为2684亿元，成功刷新了自己创下的商业纪录。然而，早在2019年4月就有网友指出，从天猫双十一的全天销售额来看，实际生产数据几乎完美地分布在三次回归曲线上，拟合度均超过99.94%，几乎为1，而且生产数据有10年之久，每一年的数据都这么高度拟合，数据过于完美。与此同时，这名网友还预测，“2019年淘宝双11当天的销售额为2675.37亿或2689.00亿。”，而这一预测，也因为贴合天猫今年的双十一最终交易数据，而被认为是神预测。
 针对这一内容，天猫相关负责人此前也回应称，“按照网友的逻辑，符合统计趋势的就是假的。那么，世界经济总量也是能被预测的，经济发展也是假的吗？自己YY下满足自嗨就算了，由此得出天猫双11数据造假，就是造谣了哦，要负法律责任的！”
 咱们且不论天猫数据有没有造假，今天我们通过这个案例中数据，利用python进行多项式回归的训练，学习一下多项式回归，顺便看看天猫的数据到底有多么‘完美’。
 
一.首先看一下天猫数据的‘庐山真面目’
 
# 1  用 Pandas 加载数据集
import pandas as pd
from matplotlib import pyplot as plt

df = pd.read_excel("E:\\job\\tmall.xlsx",header=0)
#df
# 2  绘制趋势图像
# 从原数据集中分离出需要的数据集（DataFrame）
x = df['year']
y = df['total']
# 绘图
plt.plot(x, y, 'r')
plt.scatter(x, y)
plt.title('tmall_trand')
 

 
 
二.二次多项式回归预测
 
# 3  训练集和测试集划分
# 将2009-2018年的数据定义为训练集，2010-2019年的数据定义为测试集
train_df = df[:int(len(df)*0.95)]
test_df = df[int(len(df)*0.05):]

# 定义训练和测试使用的自变量和因变量
train_x = train_df['year'].values
train_y = train_df['total'].values

test_x = test_df['year'].values
test_y = test_df['total'].values
# 4. 二次多项式预测
from sklearn.preprocessing import PolynomialFeatures

# 二次多项式回归特征矩阵
poly_features_2 = PolynomialFeatures(degree=2, include_bias=False)
poly_train_x_2 = poly_features_2.fit_transform(train_x.reshape(len(train_x),1))
poly_test_x_2 = poly_features_2.fit_transform(test_x.reshape(len(test_x),1))

# 二次多项式回归模型训练与预测
model = LinearRegression()
model.fit(poly_train_x_2, train_y.reshape(len(train_x),1)) # 训练模型

results_2 = model.predict(poly_test_x_2) # 预测结果

results_2.flatten() # 打印扁平化后的预测结果

print("二次多项式2019年预测值为: ", results_2[-1])
print("二次多项式回归平均绝对误差: ", mean_absolute_error(test_y, results_2.flatten()))
print("二次多项式均方根误差: ", mean_squared_error(test_y, results_2.flatten()))
 

# 绘出已知数据散点图    
#plt.scatter(x,y,color = 'blue') 
plt.plot(x,y,color = 'blue',linewidth = 2)
# 绘出预测线    
plt.plot(test_x,results_2,color = 'red',linewidth = 2)     
plt.title('Predict the tmall total')    
plt.xlabel('year')    
plt.ylabel('total')    
plt.show()
 

 可见，天猫双十一销售总额用二次多项式预测的结果为2675.56亿，与‘质疑淘宝数据造假’的网友的结论基本一致。而且可以看出淘宝的数据确实和二次多项式函数值极度吻合（上图中蓝色线为实际成交额曲线和红色线为预测的二次多项式曲线，两者基本完全重合）。这样的经营数据确实是可遇而不可求啊！
 
三.多项式预测的次数到底如何选择
 
在选择模型中的次数方面，可以通过设置程序，循环计算各个次数下预测误差，然后再根据结果反选参数。
 
#  多项式回归预测次数选择
# 计算 m 次多项式回归预测结果的 MSE 评价指标并绘图
from sklearn.pipeline import make_pipeline

train_df = df[:int(len(df)*0.95)]
test_df = df[int(len(df)*0.95):]

# 定义训练和测试使用的自变量和因变量
train_x = train_df['year'].values
train_y = train_df['total'].values

test_x = test_df['year'].values
test_y = test_df['total'].values

train_x = train_x.reshape(len(train_x),1)
test_x = test_x.reshape(len(test_x),1)
train_y = train_y.reshape(len(train_y),1)

mse = [] # 用于存储各最高次多项式 MSE 值
m = 1 # 初始 m 值
m_max = 10 # 设定最高次数
while m <= m_max:
    model = make_pipeline(PolynomialFeatures(m, include_bias=False), LinearRegression())
    model.fit(train_x, train_y) # 训练模型
    pre_y = model.predict(test_x) # 测试模型
    mse.append(mean_squared_error(test_y, pre_y.flatten())) # 计算 MSE
    m = m + 1

print("MSE 计算结果: ", mse)
# 绘图
plt.plot([i for i in range(1, m_max + 1)], mse, 'r')
plt.scatter([i for i in range(1, m_max + 1)], mse)

# 绘制图名称等
plt.title("MSE of m degree of polynomial regression")
plt.xlabel("m")
plt.ylabel("MSE")
 

 从误差结果可以看到，次数取二及以上误差基本稳定，没有明显的减少了，考虑到模型的泛化能力，避免出现过拟合，这里就可以选择 二次多项式为最优回归预测模型。
 
All things are difficult before they are easy.