【考试真题】2020年10月份机试试卷

一、环境要求

Hadoop+Hive+Spark+HBase 开发环境。

二、提交结果要求

1.必须提交源码或对应分析语句，如不提交则不得分。
2.带有分析结果的功能，请分析结果的截图与代码一同提交。

三、数据描述

UserBehavior 是阿里巴巴提供的一个淘宝用户行为数据集。本数据集包含了 2017-09-11 至 2017-12-03 之间有行为的约 5458 位随机用户的所有行为（行为包括点击、购买、加购、喜欢）。数据集的每一行表示一条用户行为，由用户 ID、商品 ID、商品类目 ID、行为类型和时间戳组成，并以逗号分隔。关于数据集中每一列的详细描述如下具体字段
说明如下：

注意到，用户行为类型共有四种，它们分别是

四、功能要求

1.数据准备（10 分）

① 请在 HDFS 中创建目录/data/userbehavior，并将 UserBehavior.csv 文件传到该目录。（5 分）

hdfs dfs -mkdir -p /data/userbehavior
hdfs dfs -put /opt/kb09File/UserBehavior.csv /data/userbehavior

② 通过 HDFS 命令查询出文档有多少行数据。（5 分）

hdfs dfs -cat /data/userbehavior/UserBehavior.csv | wc -l

2.数据清洗（40 分）

① 请在 Hive 中创建数据库 exam（5 分）

create database exam202010;
use exam202010;

② 请在 exam 数据库中创建外部表 userbehavior，并将 HDFS 数据映射到表中（5 分）

create external table if not exists ex_userbehavior(
	user_id string,
	item_id string,
	category_id string,
	behavior_type string,
	action_timestamp string)
row format delimited
fields terminated by ','
stored as textFile
location '/data/userbehavior/';

③ 请在 HBase 中创建命名空间 exam，并在命名空间 exam 创建 userbehavior 表，包含一个列簇 info（5 分）

create_namespace 'exam202010'
create 'exam202010:userbehavior','info'

④ 请在 Hive 中创建外部表 userbehavior_hbase，并映射到 HBase 中（5 分），并将数据加载到 HBase 中（5 分）

create external table if not exists hb_userbehavior(
	user_id string,
	item_id string,
	category_id string,
	behavior_type string,
	action_timestamp string)
stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
with serdeproperties ("hbase.columns.mapping"=":key,info:item_id,info:category_id,info:behavior_type,info:action_timestamp")
tblproperties("hbase.table.name"="exam202010:userbehavior");

加载数据：

insert into hb_userbehavior select * from ex_userbehavior;

⑤ 请在 exam 数据库中创建内部分区表 userbehavior_partitioned（按照日期进行分区），并通过查询 userbehavior 表将时间戳格式化为”年-月-日 时:分:秒”格式，将数据插入至 userbehavior_partitioned 表中，例如下图：（15 分）

create table if not exists userbehavior_partitioned(
	user_id string,
	item_id string,
	category_id string,
	behavior_type string,
	time string)
partitioned by (dt string)
row format delimited fields terminated by ',';

开启动态分区：

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

插入数据：

insert overwrite table userbehavior_partitioned PARTITION(dt)
select user_id,
item_id,
category_id,
behavior_type,
from_unixtime(CAST(action_timestamp as BIGINT),'yyyy-MM-dd HH:mm:ss') as time,
from_unixtime(CAST(action_timestamp as BIGINT),'yyyy-MM-dd')
from ex_userbehavior;

3.用户行为分析（20 分）

请使用 Spark，加载 HDFS 文件系统 UserBehavior.csv 文件，并分别使用 RDD 完成以下分析。

val fileRDD=sc.textFile("hdfs://HadoopY:9000/data/userbehavior/UserBehavior.csv")

① 统计 uv 值（一共有多少用户访问淘宝）（10 分）

fileRDD.map(_.split(",")).groupBy(_(0)).count()

② 分别统计浏览行为为点击，收藏，加入购物车，购买的总数量（10 分）

fileRDD.map(_.split(",")).map(x=>(x(3),1)).reduceByKey(_+_).collect.foreach(println)

4.找出有价值的用户（30 分）

① 使用 SparkSQL 统计用户最近购买时间。以 2017-12-03 为当前日期，计算时间范围为一个月，计算用户最近购买时间，时间的区间为 0-30 天，将其分为 5 档，0-6 天，7-12天，13-18 天，19-24 天，25-30 天分别对应评分 4 到 0（15 分）

import spark.implicits._
import org.apache.spark.sql._
import org.apache.spark.sql.types._

spark.sql("""
with cte as (
select user_id,
DATEDIFF('2017-12-03',MAX(time)) as ltime
from exam202010.userbehavior_partitioned
where dt between '2017-11-03' and '2017-12-03' and behavior_type='buy'
group by user_id)
select user_id,
(case when ltime between 0 and 6 then 4 
when ltime between 7 and 12 then 3
when ltime between 13 and 18 then 2 
when ltime between 19 and 24 then 1
when ltime between 25 and 30 then 0 
else null end) level 
from cte
""").show()

② 使用 SparkSQL 统计用户的消费频率。以 2017-12-03 为当前日期，计算时间范围为一个月，计算用户的消费次数，用户中消费次数从低到高为 1-161 次，将其分为 5档，1-32，33-64，65-96，97-128，129-161 分别对应评分 0 到 4（15 分）

spark.sql("""
WITH cte AS (
SELECT
user_id,
COUNT(item_id) as LEVEL
FROM exam202010.userbehavior_partitioned
where dt between '2017-11-03' and '2017-12-03'
and behavior_type='buy'
group by user_id
)
select user_id,CASE
when LEVEL between 0 and 32 THEN 0
when LEVEL between 33 and 64 THEN 1
when LEVEL between 65 and 96 THEN 2
when LEVEL between 97 and 128 THEN 3
when LEVEL between 129 and 161 THEN 4
ELSE null END AS PURCHASE_LEVEL
FROM cte
ORDER BY PURCHASE_LEVEL
""").show()