1、大数据竞赛培训之数据分析2018讲师：赵利平 日期：2018年11月11日目录CONTENTS原理介绍1学习目标2前期准备3数据分析4原理介绍01原理介绍原理介绍 本次数据分析是采用Hive进行的， Hive是建立在 Hadoop 上的数据仓库基础构架。它提供了一系列的工具，可以用来进行数据提取转化加载（ETL），这是一种可以存储、查询和分析存储在Hadoop 中的大规模数据的机制。 Hive 定义了简单的类 SQL 查询语言，称为 HQL，它允许熟悉 SQL 的用户查询数据。同时，这个语言也允许熟悉 MapReduce 开发者的开发自定义的 mapper 和 reducer 来处理内建的 m
2、apper 和 reducer 无法完成的复杂的分析工作。Hive的Sql语句HDFS解释为mapreduce学习目标02掌握将本地文件上传至掌握将本地文件上传至hdfshdfs指定路指定路径技能径技能掌握创建掌握创建hivehive表，并将本地数据表，并将本地数据信息导入技能信息导入技能掌握创建表获取指定格式、指定掌握创建表获取指定格式、指定信息技能信息技能掌握转化率计算等函数掌握转化率计算等函数学习目标学习目标学习目标 前期准备033.1hive3.1hive基本操基本操作作 进入hive安装目录 cd /usr/hive/apache-hive-2.1.1-bin/启动hive clie
3、nt(master上) bin/hive查看数据库列表 show databases; 3.1hive3.1hive基本操基本操作作 建数据库 create database hongya; /创建数据库hongya show databases; /查看数据库，发现有库hongya use hongya; /使用hongya数据库3.23.2查看样例数查看样例数据据 user_idage_rangegendermerchant_idlabelactivity_log3417660944-1408895:1505:7370:1107:03417660412-117235:1604:4396:0
4、818:0#954723:1604:4396:0818:0#275437:1604:4396:0818:0#236488:1505:4396:1024:0原始数据表：train_format2.csv数据user_idage_rangegendermerchant_idlabelactivity_log3417660944-1408895:1505:7370:1107:03417660412-117235:1604:4396:0818:03417660412-1954723:1604:4396:0818:03417660412-1275437:1604:4396:0818:0341766041
5、2-1236488:1505:4396:1024:0中间表1：初步创建表将activity_log中以#分割的数据拆成多行数据user_idage_rangegendermerchant_idlabelitem_idcategory _idbrand_idtime_stamp action_type3417660944-140889515057370110703417660412-117235160443968180中间表2:将拆成行的数据activity_log中以：分割的数据拆分成元素3.3hive3.3hive操作操作-创建表创建表 创建比赛数据表match_data，要求表结构与提供的
6、数据结构一样，信息包含用户iduser_id、用户性别gender、商家唯一id merchant_id、购物者标签label，均为为int类型，用户与商家交互信息activity_log为varchar类型。create table match_data( user_id int, age_range int, gender int, merchant_id int, label int, activity_log varchar(1000) row format delimited fields terminated by ,; 3.4hive3.4hive基本操作基本操作导入数据导入数据
7、 将root下的train_format2.csv数据导入到创建的match_data表中 load data local inpath /root/train_format2.csv overwrite into table match_data;查看match_data数据select * from match_data limit 100; 数据分析044.14.1数据分析数据分析中间表中间表 中间表是数据库中专门存放中间计算结果的数据表。报表系统中的中间表是普遍存在的。分析接下来的四个问题：创建click表，写入商品点击次数top100数据创建add_to_cart表，写入商品被加入购
8、物车次数top100数据创建collect表，写入商品被收藏次数top100数据创建emption表，写入商品被购买次数top100数据4.14.1数据分析数据分析中间表中间表 CREATE TABLE RESULT AS /创建RESULT表并获取match_data的USER_ID, ITEM_ID, BRAND_ID, ATIION_TYPE SELECT USER_ID, SPLIT(LOG_SPLIT,:)0 AS ITEM_ID, /将拆成行的数据以：为分隔符筛选字符串第0位 SPLIT(LOG_SPLIT,:)2 AS BRAND_ID, /将拆成行的数据以：为分隔符筛选字符串第
9、2位 SPLIT(LOG_SPLIT,:)4 AS ATIION_TYPE /将拆成行的数据以：为分隔符筛选字符串第4位 FROM (SELECT USER_ID,LOG_SPLIT FROM match_data LATERAL VIEW EXPLODE(SPLIT(ACTIVITY_LOG,#) ACTIVITY_LOG AS LOG_SPLIT ) T1; /lateral view和split, explode一起使用，以#为分隔符将一列数据拆成多行数据4.14.1数据分析数据分析中间表中间表 select * from RESULT limit 100;/查看前100行数据查看表RE
10、SULT中前100行数据4.2.14.2.1数据分析数据分析创建点击量表创建点击量表 n 创建click表，写入商品点击次数top100数据分析：商品id、点击、top100表：商品id、点击量统计、100条数据CREATE TABLE CLICK AS /创建表click，代表点击量 SELECT ITEM_ID,COUNT(1) COUNT_1/对所有的行ITEM_ID相同的进行统计 FROM RESULT WHERE ATIION_TYPE = 0/限定条件ATIION_TYPE = 0 GROUP BY ITEM_ID/ group by操作表示按照ITEM_ID字段的值进行分组，有相
11、同的ITEM_ID值放到一起 ORDER BY COUNT_1 DESC/按照统计结果全局降序排序 LIMIT 100;/限制数据100行4.2.14.2.1数据分析数据分析创建点击量表创建点击量表 select * from click;/ 查看click表中所有数据查看click表中所有数据，第一列代表商品id，第二列代表点击量4.2.24.2.2数据分析数据分析创建加入购物车表创建加入购物车表 n 创建ADD_TO_CART表，写入商品点击次数top100数据分析：商品id、加入购物车、top100表：商品id、加入购物车量统计、100条数据CREATE TABLE ADD_TO_CAR
12、T AS /创建表ADD_TO_CART，代表加入购物车量 SELECT ITEM_ID,COUNT(1) COUNT_1/对所有的行ITEM_ID相同的进行统计 FROM RESULT WHERE ATIION_TYPE = 1 /限定条件ATIION_TYPE = 1 GROUP BY ITEM_ID / group by操作表示按照ITEM_ID字段的值进行分组，有相同的ITEM_ID值放到一起 ORDER BY COUNT_1 DESC /按照统计结果全局降序排序 LIMIT 100 ; /限制数据100行4.2.24.2.2数据分析数据分析创建加入购物车表创建加入购物车表 查看ADD
13、_TO_CART表中所有数据，第一列代表商品id，第二列代表统计量select * from ADD_TO_CART；4.2.34.2.3数据分析数据分析创建收藏量表创建收藏量表 n 创建click表，写入商品收藏次数top100数据分析：商品id、收藏、top100表：商品id、收藏量统计、100条数据CREATE TABLE COLLECT AS /创建表COLLECT，代表收藏量 SELECT ITEM_ID,COUNT(1) COUNT_1 /对所有的行ITEM_ID相同的进行统计 FROM RESULT WHERE ATIION_TYPE = 3 /限定条件ATIION_TYPE =
14、 3 GROUP BY ITEM_ID / group by操作表示按照ITEM_ID字段的值进行分组，有相同的ITEM_ID值放到一起 ORDER BY COUNT_1 DESC /按照统计结果全局降序排序 LIMIT 100; /限制数据100行4.2.44.2.4数据分析数据分析创建购买量表创建购买量表 n 创建EMPTION表，写入商品购买次数top100数据分析：商品id、购买、top100表：商品id、购买量统计、100条数据CREATE TABLE EMPTION AS /创建表EMPTION，代表购买量 SELECT ITEM_ID,COUNT(1) COUNT_1 /对所有的
15、行ITEM_ID相同的进行统计 FROM RESULT WHERE ATIION_TYPE = 2 /限定条件ATIION_TYPE = 2 GROUP BY ITEM_ID / group by操作表示按照ITEM_ID字段的值进行分组，有相同的ITEM_ID值放到一起 ORDER BY COUNT_1 DESC /按照统计结果全局降序排序 LIMIT 100; /限制数据100行4.2.44.2.4数据分析数据分析创建购买量表创建购买量表 查看表EMPTION中所有数据，第一列表示商品id，第二列代表购买量select * from EMPTION;4.3.14.3.1数据分析数据分析点击
16、购买转化率计算点击购买转化率计算 CREATE TABLE CLICK_EMP AS /创建CLICK_EMP表 SELECT ITEM_ID,/商品id SUM(IF(ATIION_TYPE = 0,1,0)/COUNT(1) CLICK_EMP_RATE/点击总和除以该ITEM_ID的购买总和 FROM RESULT T1 GROUP BY ITEM_ID / group by操作表示按照ITEM_ID字段的值进行分组，有相同的ITEM_ID值放到一起 ORDER BY CLICK_EMP_RATE DESC;/按照点击购买转化率降序排序n 创建商品点击购买转化率表，并按照降序排列分析：商
17、品id、点击购买转化率、降序排列表：商品id、转化率、降序4.3.14.3.1数据分析数据分析点击购买转化率计算点击购买转化率计算 查看表CLICK_EMP中前100行数据，第一列数据为item_id，第二列数据为点击购买转化率。select * from CLICK_EMP limit 1004.3.24.3.2数据分析数据分析加入购物车转化率计算加入购物车转化率计算 CREATE TABLE ADD_EMP AS /创建ADD_EMP P表 SELECT ITEM_ID,SUM(IF(ATIION_TYPE = 1,1,0)/COUNT(1) CLICK_EMP_RATE /加入购物车总和
18、除以该ITEM_ID的购买总和 FROM RESULT T1 GROUP BY ITEM_ID / group by操作表示按照ITEM_ID字段的值进行分组，有相同的ITEM_ID值放到一起 ORDER BY CLICK_EMP_RATE DESC; /按照点击购买转化率降序排序n 创建商品加入购物车购买转化率表，并按照降序排列分析：商品id、加入购物车购买转化率、降序排列表：商品id、转化率、降序4.3.24.3.2数据分析数据分析加入购物车转化率计算加入购物车转化率计算 查看表ADD_EMP中前100行数据， 第一列数据为item_id，第二列数据为加入购物车购买转化率。select *
19、 from ADD_EMP limit 100; 4.3.34.3.3数据分析数据分析收藏购买转化率计算收藏购买转化率计算 CREATE TABLE COLLECT_EMP AS /创建COLLECT_EMP表 SELECT ITEM_ID,SUM(IF(ATIION_TYPE = 1,1,0)/COUNT(1) CLICK_EMP_RATE /收藏总和除以该ITEM_ID的购买总和 FROM RESULT T1 GROUP BY ITEM_ID / group by操作表示按照ITEM_ID字段的值进行分组，有相同的ITEM_ID值放到一起ORDER BY CLICK_EMP_RATE DESC; /按照点击购买转化率降序排序n 创建商品收藏购买转化率表，并按照降序排列分析：商品id、收藏购买转化率、降序排列表：商品id、转化率、降序非常感谢您的观看2018
