Spark 操作hive实战练习

一、最终需求：预测，在test里面的用户，下一个订单会购买哪些商品

比如：
user3，已经有了5月5号的订单（不公开）来评测模型的
我们有的是5月4号之前的历史数据
我们需要预测，通过历史(5月4号之前订单数据)，预测这个用户5月5号买了什么

1、数据准备：

a、在hive中创建orders订单外部表
b、在hive中创建products商品外部表
c、在hive中创建priors(order_products__prior)订单商品关联外部表
查看hadoop上文件前几行数据

hadoop fs -cat /bigdata/orders/orders.csv | head -5

2、启动hadoop集群，hive，spark-shell 连接hive

a、启动hadoop集群

cd /opt/hadoop/sbin
./start-all.sh

b、连接hive

cd /opt/hive/bin
./hive

c、spark-shell 连接hive

./spark-shell --master local[2] --jars /opt/mysql-connector-java-5.1.22-bin.jar

3、上传源数据到hadoop，创建hive外部表

查看hadoop /bigdata/路径下文件

hadoop fs -ls /bigdata/

创建orders，products，priors文件夹

hadoop fs -mkdir -p /bigdata/orders
hadoop fs -mkdir -p /bigdata/products
hadoop fs -mkdir -p /bigdata/priors

上传数据源到hadoop

hadoop fs -put /opt/bigdatas/orders.csv /bigdata/orders/
hadoop fs -put /opt/bigdatas/products.csv /bigdata/products/
hadoop fs -put /opt/bigdatas/order_products__prior.csv /bigdata/priors/

创建orders订单外部表
external 关键字表示外部表

create  external table orders
(
order_id string,
user_id string,
eval_set string,
order_number string,
order_dow string,  
order_hour_of_day string,
days_since_prior_order string
)
row format delimited 
fields terminated by ',' 
lines terminated by '\n'
location '/bigdata/orders'
-- tblproperties ("skip.header.line.count"="1");   -- 由于csv文件第一行为表头，这里数据过滤掉第一行数据

创建products订单外部表

create  external table products
(
product_id string,
product_name string,
aisle_id string,
department_id string
)
row format delimited 
fields terminated by ',' 
lines terminated by '\n'
location '/bigdata/products';

创建订单商品关系表

create  external table priors
(
order_id string,
product_id string,
add_to_cart_order string,
reordered string
)
row format delimited 
fields terminated by ',' 
lines terminated by '\n'
location '/bigdata/priors'
-- tblproperties ("skip.header.line.count"="1"); 

使用hive查看新建的表数据

 show tables;

4、spark 连接hive,查看orders、products、priors数据

import spark.sql

查看orders表

val orders = sql("select * from orders")
orders.show()

查看products数据

val products= sql("select * from products")
products.show()

查看priors数据

val priors= sql("select * from priors")
priors.show()

5、统计product 被购买的数据量(销售量)

priors.groupBy("product_id").count().show()

将结果缓存到内存中：

val productCnt = priors.groupBy("product_id").count().cache
productCnt.show(10) 会执行
productCnt.show(10) 直接从内存中读取

从内存中去掉：

productCnt.unpersist

6.统计product 被(reordered)再次购买的数量（这个商品是不是消耗品）

selectExpr：可以写sql

priors.selectExpr("product_id","cast(reordered as int)").filter(col("reordered") ===1).groupBy("product_id").count()

等价于：
priors.selectExpr("product_id","cast(reordered as int)").groupBy("product_id").sum("reordered") 

过滤举例：

orders.filter(col("eval_set")==="test").show()
等价于
orders.where(col("eval_set")==="test").show()

agg用法： 可以同时求两个值,一般是搭配groupBy这种聚合函数使用，和单独使用sum的差别，是一次聚合中可以统计出来多个值

priors.selectExpr("product_id","cast(reordered as int)").groupBy("product_id").agg(sum("reordered"),avg("reordered")).show()

7、结合上面数量统计product购买的reordered的比率

val prodcutSumReorder = priors.selectExpr("product_id","cast(reordered as int)").groupBy("product_id").agg(sum("reordered"),avg("reordered")).
withColumnRenamed("sum(reordered)","sum_re").withColumnRenamed("avg(reordered)","avg_re")

val jproduct = productCnt.join(prodcutSumReorder,"product_id")

引入udf
import org.apache.spark.sql.functions._
val avg_udf =udf((sm:Long,cnt:Long)=>sm.toDouble/cnt.toDouble)

jproduct.withColumn("mean_re",avg_udf(col("sum_re"),col("count"))).show()

可以看到mean_re和avg_re的计算结果是一样的

二、user统计/特征

1、每个用户平均购买订单的间隔周期

orders.show(5)

1)、用户的第一个订单没有间隔天数，需要赋值为0

orders.selectExpr("*","if(days_since_prior_order='',0,days_since_prior_order) as dspo").show()

删掉旧行（days_since_prior_order）

orders.selectExpr("*","if(days_since_prior_order='',0,days_since_prior_order) as dspo").drop("days_since_prior_order").show()

val ordersNew = orders.selectExpr("*","if(days_since_prior_order='',0,days_since_prior_order) as dspo").drop("days_since_prior_order")

2）每个用户平均购买周期

ordersNew.selectExpr("user_id","cast(dspo as int)").groupBy("user_id").avg("dspo").show()

2.每个用户的总订单数量

orders.groupBy("user_id").count().show()

3.每个用户购买的product商品去重后的集合数据

orders订单表关联 priors关联表

val po = orders.join(priors,"order_id").select("user_id","product_id")
po.show()

DataFrame转RDD处理：
po.rdd.map(x=>(x(0).toString,x(1).toString)).groupByKey().mapValues(_.toSet.mkString(","))
xx.take(1)

RDD转DataFrame:
隐式转换：
import spark.implicits._

xx.toDF("user_id","product_records").show(5,false)

4.每个用户总商品数量以及去重后的商品数量

总商品数量：

orders.join(priors,"order_id").groupBy("user_id").count().show()

重后的商品数量:

xx.toDF("user_id","product_records").selectExpr("user_id","size(split(product_records,',')) as pro_dist_cnt").show()

5.每个用户购买的平均每个订单的商品数量

1)先求每个订单的商品数量【对订单做聚合count()】

val ordProCnt = priors.groupBy(“order_id”).count()

2)求每个用户订单中商品个数的平均值【对user做聚合，avg(商品个数)】

orders.join(ordProCnt ,"order_id").groupBy("user_id").avg("count")
改名字：
orders.join(ordProCnt ,"order_id").groupBy("user_id").avg("count").withColumnRenamed("avg(count)","avg_po_num").show()