Spark大数据-基于Hive和Spark的淘宝双11数据分析与预测
基于Hive和Spark的淘宝双11数据分析与预测
1.系统和环境要求（版本仅供参考）：
- Linux: centos7
- MySQL: 5.7.16
- Hadoop: 2.7.1
- Hive: 1.2.1
- Sqoop: 1.4.6
- Spark: 2.1.0
- Eclipse: 3.8
- ECharts: 3.4.0
2.数据上传到Hive（ Hive的安装配置 ）
2.1数据集格式内容
数据集压缩包为 data_format.zip
，该数据集压缩包是淘宝2015年双11前6个月(包含双11)的交易数据(交易数据有偏移，但是不影响实验的结果)，里面包含3个文件，分别是用户行为日志文件user_log.csv
、回头客训练集train.csv 、回头客测试集test.csv. 下面列出这3个文件的数据格式定义：
1.用户行为日志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| 收获地址省份
2.回头客训练集train.csv和回头客测试集test.csv，训练集和测试集拥有相同的字段，字段定义如下：
- 1.user_id | 买家id
- 2.age_range | 买家年龄分段：1表示年龄<18,2表示年龄在[18,24]，3表示年龄在[25,29]，4表示年龄在[30,34]，5表示年龄在[35,39]，6表示年龄在[40,49]，7和8表示年龄>=50,0和NULL则表示未知
- 3.gender | 性别:0表示女性，1表示男性，2和NULL表示未知
- 4.merchant_id | 商家id
- 5.label | 是否是回头客，0值表示不是回头客，1值表示回头客，-1值表示该用户已经超出我们所需要考虑的预测范围。NULL值只存在测试集，在测试集中表示需要预测的值。
3.创建文件根目录，并将数据集解压到该目录
cd /usr/local
ls
sudo mkdir dbtaobao
//这里会提示你输入当前用户（本教程是hadoop用户名）的密码
//下面给hadoop用户赋予针对dbtaobao目录的各种操作权限
sudo chown -R hadoop:hadoop ./dbtaobao
cd dbtaobao
//下面创建一个dataset目录，用于保存数据集
mkdir dataset
//下面就可以解压缩data_format.zip文件
cd ~ //表示进入hadoop用户的目录
cd 下载
ls
unzip data_format.zip -d /usr/local/dbtaobao/dataset
cd /usr/local/dbtaobao/dataset
ls
**4.dataset目录下有三个文件：test.csv、train.csv、user_log.csv
查看user_log.csv前面5条记录（[其他花里胡哨的查看方式] **
(https://blog.csdn.net/chenbengang/article/details/104015652)）：
head -5 user_log.csv
2.2数据预处理以及上传到数据库
1.删除文件第一行记录，即字段名称
cd /usr/local/dbtaobao/dataset
//下面删除user_log.csv中的第1行
sed -i '1d' user_log.csv //1d表示删除第1行，同理，3d表示删除第3行，nd表示删除第n行
//下面再用head命令去查看文件的前5行记录，就看不到字段名称这一行了
head -5 user_log.csv
2.获取数据集中双11的前100000条数据
由于数据太大，这里截取数据集中在双11的前10000条交易数据作为小数据集small_user_log.csv，建立一个脚本文件完成上面截取任务，请把这个脚本文件放在dataset目录下和数据集user_log.csv:
cd /usr/local/dbtaobao/dataset
vim predeal.sh
predeal.sh文件内容如下：
#!/bin/bash
#下面设置输入文件，把用户执行predeal.sh命令时提供的第一个参数作为输入文件名称
infile=$1
#下面设置输出文件，把用户执行predeal.sh命令时提供的第二个参数作为输出文件名称
outfile=$2
#注意！！最后的$infile > $outfile必须跟在}’这两个字符的后面
awk -F "," 'BEGIN{
id=0;
}
{
if($6==11 && $7==11){
id=id+1;
print $1","$2","$3","$4","$5","$6","$7","$8","$9","$10","$11
if(id==10000){
exit
}
}
}' $infile > $outfile
使用脚本截取数据：
chmod +x ./predeal.sh
./predeal.sh ./user_log.csv ./small_user_log.csv
3.将数据导入数据库
确保hadoop启动，jps查看状态，以下即可：
3765 NodeManager
3639 ResourceManager
3800 Jps
3261 DataNode
3134 NameNode
3471 SecondaryNameNode
hdfs中创建文件的根目录：
cd /usr/local/hadoop
./bin/hdfs dfs -mkdir -p /dbtaobao/dataset/user_log
本地文件上传到hdfs：
cd /usr/local/hadoop
./bin/hdfs dfs -put /usr/local/dbtaobao/dataset/small_user_log.csv /dbtaobao/dataset/user_log
启动mysql（ hive将元数据存储在mysql所以必须启动 ） 和hive：
service mysql start #可以在Linux的任何目录下执行该命令
cd /usr/local/hive
./bin/hive # 启动Hive
创建数据库和外部表（并且将hdfs文件系统的相应文件上传到hive数据库）
hive> create database dbtaobao;
hive> use dbtaobao;
hive> CREATE EXTERNAL TABLE dbtaobao.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 dbtaobao.user_log!' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE LOCATION '/dbtaobao/dataset/user_log';
hive中查询数据：
hive> select * from user_log limit 10;
3.基于Hive的数据分析
3.1启动MySQL、Hadoop和Hive，查看数据库dbtaobao格式
hive> use dbtaobao; -- 使用dbtaobao数据库
hive> show tables; -- 显示数据库中所有表。
hive> show create table user_log; -- 查看user_log表的各种属性；
3.2简单结构查询：
hive> desc user_log;
hive> select brand_id from user_log limit 10; -- 查看日志前10个交易日志的商品品牌
hive> select month,day,cat_id from user_log limit 20;
hive> select ul.at, ul.ci from (select action as at, cat_id as ci from user_log) as ul limit 20;
3.3查询条数统计分析
用聚合函数count()计算出表内有多少条行数据。加上distinct，查出uid不重复的数据有多少条
hive> select count(*) from user_log; -- 用聚合函数count()计算出表内有多少条行数据
hive> select count(distinct user_id) from user_log; -- 在函数内部加上distinct，查出user_id不重复的数据有多少条
查询不重复的数据有多少条(为了排除客户刷单情况) (嵌套语句最好取别名，就是上面的a，否则很容易报cannot recognize错误)
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;
3.4关键字条件查询分析
查询双11那天有多少人购买了商品
hive> select count(distinct user_id) from user_log where action='2';
取给定时间和给定品牌，求当天购买的此品牌商品的数量
hive> select count(*) from user_log where action='2' and brand_id=2661;
3.5．根据用户行为分析
查询一件商品在某天的购买比例或浏览比例
hive> select count(distinct user_id) from user_log where action='2'; -- 查询有多少用户在双11购买了商品
hive> select count(distinct user_id) from user_log; -- 查询有多少用户在双11点击了该店
查询双11那天，男女买家购买商品的比例
hive> select count(*) from user_log where gender=0; --查询双11那天女性购买商品的数量
hive> select count(*) from user_log where gender=1; --查询双11那天男性购买商品的数量
给定购买商品的数量范围，查询某一天在该网站的购买该数量商品的用户id
hive> select user_id from user_log where action='2' group by user_id having count(action='2')>5; -- 查询某一天在该网站购买商品超过5次的用户id
3.6.用户实时查询分析
不同的品牌的浏览次数
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; -- 显示结果
4.使用Sqoop将数据从Hive导入MySQL( [ sqoop安装教程
](http://dblab.xmu.edu.cn/blog/install-sqoop1/) )
4.1.hive预操作 （确保启动mysql、hadoop和hive）
- 创建临时表inner_user_log和inner_user_info。执行完以后，Hive会自动在HDFS文件系统中创建对应的数据文件“/user/hive/warehouse/dbtaobao.db/inner_user_log”。
hive> create table dbtaobao.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;
- 将user_log表中的数据插入到inner_user_log,
前面已经在Hive中的dbtaobao数据库中创建了一个外部表user_log，把dbtaobao.user_log数据插入到dbtaobao.inner_user_log表中，并查询是否上传成功：
hive> INSERT OVERWRITE TABLE dbtaobao.inner_user_log select * from dbtaobao.user_log;
hive> select * from inner_user_log limit 10;
4.2使用Sqoop将数据从Hive导入MySQL
- 1.登录mysql，创建数据库， 修改数据库编码格式 ，创建表格：
mysql –u root –p
mysql> show databases; #显示所有数据库
mysql> create database dbtaobao; #创建dbtaobao数据库
mysql> use dbtaobao; #使用数据库
mysql> show variables like "char%";
mysql> CREATE TABLE `dbtaobao`.`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。
- 2.导入数据：
cd /usr/local/sqoop
bin/sqoop export --connect jdbc:mysql://localhost:3306/dbtaobao --username root --password root --table user_log --export-dir '/user/hive/warehouse/dbtaobao.db/inner_user_log' --fields-terminated-by ',';
字段解释：
./bin/sqoop export ##表示数据从 hive 复制到 mysql 中
–connect jdbc:mysql://localhost:3306/dbtaobao
–username root #mysql登陆用户名
–password root #登录密码
–table user_log #mysql 中的表，即将被导入的表名称
–export-dir ‘/user/hive/warehouse/dbtaobao.db/user_log ‘ #hive 中被导出的文件，hdfs中存的
–fields-terminated-by ‘,’ #Hive 中被导出的文件字段的分隔符
- 3.查看mysql中的数据：
mysql -u root -p
mysql> use dbtaobao;
mysql> select * from user_log limit 10;
5.Spark预测回头客行为
5.1预处理test.csv和train.csv数据集
- 1.字段描述和定义
1.user_id | 买家id
2.age_range |
买家年龄分段：1表示年龄<18,2表示年龄在[18,24]，3表示年龄在[25,29]，4表示年龄在[30,34]，5表示年龄在[35,39]，6表示年龄在[40,49]，7和8表示年龄>=50,0和NULL则表示未知
3.gender | 性别:0表示女性，1表示男性，2和NULL表示未知
4.merchant_id | 商家id
5.label |
是否是回头客，0值表示不是回头客，1值表示回头客，-1值表示该用户已经超出我们所需要考虑的预测范围。NULL值只存在测试集，在测试集中表示需要预测的值。
- 2.需要预先处理test.csv数据集，把这test.csv数据集里label字段表示-1值剔除掉,保留需要预测的数据.并假设需要预测的数据中label字段均为1：
cd /usr/local/dbtaobao/dataset
vim predeal_test.sh
predeal_test.sh脚本文件：
#!/bin/bash
#下面设置输入文件，把用户执行predeal_test.sh命令时提供的第一个参数作为输入文件名称
infile=$1
#下面设置输出文件，把用户执行predeal_test.sh命令时提供的第二个参数作为输出文件名称
outfile=$2
#注意！！最后的$infile > $outfile必须跟在}’这两个字符的后面
awk -F "," 'BEGIN{
id=0;
}
{
if($1 && $2 && $3 && $4 && !$5){
id=id+1;
print $1","$2","$3","$4","1
if(id==10000){
exit
}
}
}' $infile > $outfile
chmod +x ./predeal_test.sh
./predeal_test.sh ./test.csv ./test_after.csv
- 3.train.csv的第一行都是字段名称，不需要第一行字段名称,这里在对train.csv做数据预处理时，删除第一行
sed -i '1d' train.csv
然后剔除掉train.csv中字段值部分字段值为空的数据
cd /usr/local/dbtaobao/dataset
vim predeal_train.sh
#!/bin/bash
#下面设置输入文件，把用户执行predeal_train.sh命令时提供的第一个参数作为输入文件名称
infile=$1
#下面设置输出文件，把用户执行predeal_train.sh命令时提供的第二个参数作为输出文件名称
outfile=$2
#注意！！最后的$infile > $outfile必须跟在}’这两个字符的后面
awk -F "," 'BEGIN{
id=0;
}
{
if($1 && $2 && $3 && $4 && ($5!=-1)){
id=id+1;
print $1","$2","$3","$4","$5
if(id==10000){
exit
}
}
}' $infile > $outfile
截取得到训练数据：
chmod +x ./predeal_train.sh
./predeal_train.sh ./train.csv ./train_after.csv
5.2预测回头客
- 1.启动hadoop，创建文件目录
bin/hadoop fs -mkdir -p /dbtaobao/dataset
bin/hadoop fs -put /usr/local/dbtaobao/dataset/train_after.csv /dbtaobao/dataset
bin/hadoop fs -put /usr/local/dbtaobao/dataset/test_after.csv /dbtaobao/dataset
- 2.启动mysql创建rebuy表
use dbtaobao;
create table rebuy (score varchar(40),label varchar(40));
- 3.使用spark连接mysql数据库，Spark支持通过JDBC方式连接到其他数据库获取数据生成DataFrame。 下载MySQL的JDBC驱动 （mysql-connector-java-5.1.40.zip）。unzip命令解压后将jar包拷贝到集群每个节点spark下的jars目录下，启动spark-submit
cd ~/下载/
unzip mysql-connector-java-5.1.40.zip -d /usr/local/spark/jars
cd /usr/local/spark
./bin/spark-shell --jars /usr/local/spark/jars/mysql-connector-java-5.1.40/mysql-connector-java-5.1.40-bin.jar --driver-class-path /usr/local/spark/jars/mysql-connector-java-5.1.40/mysql-connector-java-5.1.40-bin.jar
5.3支持向量机SVM分类器预测回头客
import org.apache.spark.SparkConf
import org.apache.spark.SparkContext
import org.apache.spark.mllib.regression.LabeledPoint
import org.apache.spark.mllib.linalg.{Vectors,Vector}
import org.apache.spark.mllib.classification.{SVMModel, SVMWithSGD}
import org.apache.spark.mllib.evaluation.BinaryClassificationMetrics
import java.util.Properties
import org.apache.spark.sql.types._
import org.apache.spark.sql.Row
// val conf = new SparkConf().setAppName("taobao").setMaster("local[2]")
// val sc = new SparkContext(conf)
// 加载数据
val train_data = sc.textFile("/opt/dbtaobao/dataset/train_after.csv")
val test_data = sc.textFile("/opt/dbtaobao/dataset/test_after.csv")
// 构建训练数据和测试数据
val train= train_data.map{line =>
val parts = line.split(',')
LabeledPoint(parts(4).toDouble,Vectors.dense(parts(1).toDouble,parts
(2).toDouble,parts(3).toDouble))
}
val test = test_data.map{line =>
val parts = line.split(',')
LabeledPoint(parts(4).toDouble,Vectors.dense(parts(1).toDouble,parts(2).toDouble,parts(3).toDouble))
}
// 构建模型
val numIterations = 1000
val model = SVMWithSGD.train(train, numIterations)
// 查看测试集的预测结果
model.clearThreshold()
val scoreAndLabels = test.map{point =>
val score = model.predict(point.features)
score+" "+point.label
}
scoreAndLabels.foreach(println)
model.setThreshold(0.0)
scoreAndLabels.foreach(println)
model.clearThreshold()
val scoreAndLabels = test.map{point =>
val score = model.predict(point.features)
score+" "+point.label
}
//设置回头客数据
val rebuyRDD = scoreAndLabels.map(_.split(" "))
//下面要设置模式信息
val schema = StructType(List(StructField("score", StringType, true),StructField("label", StringType, true)))
//下面创建Row对象，每个Row对象都是rowRDD中的一行
val rowRDD = rebuyRDD.map(p => Row(p(0).trim, p(1).trim))
//建立起Row对象和模式之间的对应关系，也就是把数据和模式对应起来
val rebuyDF = spark.createDataFrame(rowRDD, schema)
//下面创建一个prop变量用来保存JDBC连接参数
val prop = new Properties()
prop.put("user", "root") //表示用户名是root
prop.put("password", "hadoop") //表示密码是hadoop
prop.put("driver","com.mysql.jdbc.Driver") //表示驱动程序是com.mysql.jdbc.Driver
//下面就可以连接数据库，采用append模式，表示追加记录到数据库dbtaobao的rebuy表中
rebuyDF.write.mode("append").jdbc("jdbc:mysql://localhost:3306/dbtaobao", "dbtaobao.rebuy", prop)
6.ECharts进行数据可视化分析
**6.1.使用myeclipse+tomcat构建j2ee项目，连接mysql数据库。也可远程连接集群上的mysql数据库。连接并操作mysql数据库的后端代码，前端采用js（
可视化项目源代码下载 ）： **
package dbtaobao;
import java.sql.*;
import java.util.ArrayList;
public class connDb {
private static Connection con = null;
private static Statement stmt = null;
private static ResultSet rs = null;
//连接数据库方法
public static void startConn(){
try{
Class.forName("com.mysql.jdbc.Driver");
//连接数据库中间件
try{
con = DriverManager.getConnection("jdbc:MySQL://localhost:3306/dbtaobao","root","root");
}catch(SQLException e){
e.printStackTrace();
}
}catch(ClassNotFoundException e){
e.printStackTrace();
}
}
//关闭连接数据库方法
public static void endConn() throws SQLException{
if(con != null){
con.close();
con = null;
}
if(rs != null){
rs.close();
rs = null;
}
if(stmt != null){
stmt.close();
stmt = null;
}
}
//数据库双11 所有买家消费行为比例
public static ArrayList index() throws SQLException{
ArrayList<String[]> list = new ArrayList();
startConn();
stmt = con.createStatement();
rs = stmt.executeQuery("select action,count(*) num from user_log group by action desc");
while(rs.next()){
String[] temp={rs.getString("action"),rs.getString("num")};
list.add(temp);
}
endConn();
return list;
}
//男女买家交易对比
public static ArrayList index_1() throws SQLException{
ArrayList<String[]> list = new ArrayList();
startConn();
stmt = con.createStatement();
rs = stmt.executeQuery("select gender,count(*) num from user_log group by gender desc");
while(rs.next()){
String[] temp={rs.getString("gender"),rs.getString("num")};
list.add(temp);
}
endConn();
return list;
}
//男女买家各个年龄段交易对比
public static ArrayList index_2() throws SQLException{
ArrayList<String[]> list = new ArrayList();
startConn();
stmt = con.createStatement();
rs = stmt.executeQuery("select gender,age_range,count(*) num from user_log group by gender,age_range desc");
while(rs.next()){
String[] temp={rs.getString("gender"),rs.getString("age_range"),rs.getString("num")};
list.add(temp);
}
endConn();
return list;
}
//获取销量前五的商品类别
public static ArrayList index_3() throws SQLException{
ArrayList<String[]> list = new ArrayList();
startConn();
stmt = con.createStatement();
rs = stmt.executeQuery("select cat_id,count(*) num from user_log group by cat_id order by count(*) desc limit 5");
while(rs.next()){
String[] temp={rs.getString("cat_id"),rs.getString("num")};
list.add(temp);
}
endConn();
return list;
}
//各个省份的总成交量对比
public static ArrayList index_4() throws SQLException{
ArrayList<String[]> list = new ArrayList();
startConn();
stmt = con.createStatement();
rs = stmt.executeQuery("select province,count(*) num from user_log group by province order by count(*) desc");
while(rs.next()){
String[] temp={rs.getString("province"),rs.getString("num")};
list.add(temp);
}
endConn();
return list;
}
}
6.2可视化结果
给出的源代码中未解决最后一个可视化，自行下载china.js，获取省份位置以及数量进行可视化。
