2024年最新淘宝数据可视化大屏案例(Hadoop实验)_hadoop数据分析可视化案例，2024年最新我三年开发经验从字节跳动抖音离职后

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

需要这份系统化资料的朋友，可以戳这里获取

执行下面的命令：

cd ${HIVE_HOME}/hcatalog/share/hcatalog/

cp * ${FLUME_HOME}/lib/

启动hive

hive

创建数据库并使用

create database taobao;
use taobao;

建立表格

create table `taobao`.`taobao_data`  (
  `user_id` varchar(255) ,
  `item_id` varchar(255) ,
  `behavior_type` varchar(255) ,
  `user_geohash` varchar(255) ,
  `item_category` varchar(255) ,
  `date` varchar(10) ,
  `hour` varchar(3) 
) 
clustered by(user_id) into 3 buckets
row format delimited fields terminated by ','
stored as orc tblproperties('transactional'='true');

创建导出数据表

create table `taobao`.`taobao_result`  (
  `key` varchar(255) ,
  `value` varchar(255)) ;

4.导入数据

先启动hive --service metastore -p 9083
(这个端口号要配置到flume文件中,可用netstat -tulpn | grep 9083查看端口是否监听)

hive --service metastore -p 9083

再去启动flume

flume-ng agent --conf conf --conf-file file_hive.properties -name agent3 -Dflume.hadoop.logger=INFO,console

然后把文件数据导入到，之前创建的data文件夹里面就完成了自动导入

 mv /home/hadoop/12yue.csv /home/hadoop/taobao/data/

---

5.数据分析

5.1 把总访问量查询出来，导入到结果表

insert into taobao_result 
(select "PV", u. `总访问量`  FROM 
(select count(\*) AS `总访问量` FROM taobao_data) u);

INSERT INTO taobao_result 
(SELECT  " UV", u. `用户数量`  FROM
(SELECT COUNT(DISTINCT user_id) AS `用户数量` FROM taobao_data) u); 

可以查看一下

select \* from taobao_result;

---

5.2 – 浏览页跳失率：用户仅仅有pv行为，没有其它的收藏、加购、购买行为

INSERT INTO taobao_result 
(SELECT "跳失率", u. `总访问量`  FROM
(
SELECT b.`仅pv用户` / a.`总用户` AS `总访问量`  FROM
(SELECT count( DISTINCT user_id )  AS `总用户` FROM taobao_data) a ,
(SELECT 
count( DISTINCT user_id ) AS `仅pv用户`
FROM taobao_data 
WHERE 
user_id NOT IN ( SELECT DISTINCT user_id FROM taobao_data WHERE behavior_type = '2' ) AND 
user_id NOT IN ( SELECT DISTINCT user_id FROM taobao_data WHERE behavior_type = '3' ) AND 
user_id NOT IN ( SELECT DISTINCT user_id FROM taobao_data WHERE behavior_type = '4' )) b
) u);

注意在hive里面不可以使用in not in查询，所以这里要用连接查询解决这个问题

经过大量的测试，我自己写了一个sq语句，也可以达到以上的效果

首先把要插入的信息，查询出来

SELECT "跳失率", u.`总访问量` FROM
(
SELECT b.`仅pv用户` / a.`总用户` AS `总访问量`  FROM
(SELECT count( DISTINCT user_id ) AS `总用户` FROM taobao_data) a,
(SELECT count( DISTINCT user_id ) AS `仅pv用户` from (select \* from taobao_data) as c LEFT JOIN (SELECT  DISTINCT user_id as `id`  FROM taobao_data WHERE behavior_type = '2' or behavior_type='3' or behavior_type='4') as d on c.user_id=d.id WHERE d.id is NULL ) as b
) as u;

插入

INSERT INTO taobao_result
(SELECT "跳失率", u.`总访问量` FROM
(
SELECT b.`仅pv用户` / a.`总用户` AS `总访问量`  FROM
(SELECT count( DISTINCT user_id ) AS `总用户` FROM taobao_data) a,
(SELECT count( DISTINCT user_id ) AS `仅pv用户` from (select \* from taobao_data) as c LEFT JOIN (SELECT  DISTINCT user_id as `id`  FROM taobao_data WHERE behavior_type = '2' or behavior_type='3' or behavior_type='4') as d on c.user_id=d.id WHERE d.id is NULL ) as b
) as u);

优化代码类型（思路类似）

SELECT count(DISTINCT user_id) from datas left JOIN
(select DISTINCT user_id as t from datas WHERE behavior_type ='2' or behavior_type ='3' or behavior_type ='4') as a on user_id=a.t WHERE a.t is null

---

5.3
– 有购买行为的用户数量、用户的购物情况、复购率分别是多少？

SELECT COUNT( 1 ) FROM (SELECT u.user_id, SUM( CASE u.behavior_type WHEN "4" THEN 1 ELSE 0 END ) AS buy FROM taobao_data u GROUP BY u.user_id HAVING buy > 0 ) t;

SELECT COUNT(1) AS `总数`, SUM(CASE u.behavior_type WHEN "1" THEN 1 ELSE 0 END ) AS `点击行为`,SUM(CASE u.behavior_type WHEN "2" THEN 1 ELSE 0 END ) AS `收藏行为`,SUM(CASE u.behavior_type WHEN "3" THEN 1 ELSE 0 END ) AS `加购物车行为`,SUM(CASE u.behavior_type WHEN "4" THEN 1 ELSE 0 END ) AS `购买行为` FROM taobao_data u;

---

5.4 – 复购率 购买次数大于2的人占总的人数的比率

必须要输入下面代码

原因如下

set hive.mapred.mode=nonstrict;

注意这个sq语句里面的4，必须也要用单引号括起来

SELECT t2.repeat_buy/t1.total AS `复购率` FROM	
(SELECT COUNT(DISTINCT u1.user_id) AS total FROM taobao_data u1) t1 , 
(SELECT COUNT(1) AS repeat_buy FROM
(SELECT u.user_id, SUM(CASE u.behavior_type WHEN '4' THEN 1 ELSE 0 END ) AS buy FROM taobao_data u  GROUP BY u.user_id HAVING buy>1) t) t2;

5.5 分析用户在哪个时间段最为活跃，包括日期和时间

SELECT `date`,count(\*) as `t` from taobao_data GROUP BY `date` ORDER BY `t` DESC;

SELECT `hour`,count(\*) as `t` from taobao_data GROUP BY `hour` ORDER BY `t` DESC;

---

导出数据

去自己的Navicat里面执行去试试也可以

CREATE DATABASE taobao;


create table `taobao`.`taobao_result`  (
  `key` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `value` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

虚拟机执行也可以

show create table taobao_result

在终端界面运行

sqoop export --connect jdbc:mysql://localhost:3306/taobao --username root -P --table taobao_result --export-dir  /user/hive/warehouse/taobao.db/taobao_result  -m 1 --input-fields-terminated-by '\001'

查询一下数据是否导入成功

OK！

可视化展示——基于Python里面的pyecharts库

1.有不同的用户访问，按照IP地址来确定，对比之后用户点击率还是比较高的，和用户人数形成了极差，说明该电商还是比较吸引人，有大量的浏览量，说明网站还是比较吸引人，流量价值比较高，可以加大对广告的投入赚取利益。

---

2.只看不买不收藏的用户占比总人数约1/3，说明还是比较可观，可以加大对网站信息化的建设以及，吸引更多的人，同时减少跳失率

---

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

需要这份系统化资料的朋友，可以戳这里获取

or_FFFFFF,t_70#pic_center)

[外链图片转存中…(img-b9aQSmjL-1715311118658)]
[外链图片转存中…(img-6Wm1GCZy-1715311118658)]
[外链图片转存中…(img-NCENIoeP-1715311118659)]

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

需要这份系统化资料的朋友，可以戳这里获取