1.本地数据集上传到数据堆栈Hive
1.1下令
- sudo mkdir -R /usr/local/bigdatacase/dataset //这里会提示你输入当前用户(本教程是hadoop用户名)的暗码 //下面给hadoop用户赋予针对bigdatacase目次的各种操作权限 cd /usr/local/sudo chown -R hadoop:hadoop ./bigdatacase cd bigdatacase //下面就可以解压缩user.zip文件 cd ~ //表现进入hadoop用户的目次 cd 下载 ls unzip user.zip -d /usr/local/bigdatacase/dataset cd /usr/local/bigdatacase/dataset ls head -5 raw_user.csv
复制代码 1.2数据集举行预处理惩罚
1.2.1删除首行
- cd /usr/local/bigdatacase/dataset //下面删除raw_user中的第1行 //1d表现删除第1行,同理,3d表现删除第3行,nd表现删除第n行sed -i '1d' raw_user.csv //下面删除small_user中的第1行 sed -i '1d' small_user.csv //下面再用head下令去检察文件的前5行记载,就看不到字段名称这一行了 head -5 raw_user.csv head -5 small_user.csv
复制代码 1.2.2 添加字段
添加随机省份字段
1.3 导入HDFS中
略
1.4 Hive创建数据库
- //这个为小数据集,下面的为大数据集hive> create database dblab;hive> create database dblab_big;//创建表并映射入HDFS中的文件hive> CREATE EXTERNAL TABLE dblab.bigdata_user(id INT,uid STRING,item_id STRING,behavior_type INT,item_category STRING,visit_date DATE,province STRING) COMMENT 'Welcome to xmu dblab!' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS TEXTFILE LOCATION '/bigdatacase/dataset';
复制代码 到现在为止就完成了数据的预处理惩罚,添加省份,创建Hive数据库,上传到HDFS,而且映射到Hive内里
2.Hive数据分析语句
Hive数据分析就是写SQL语句对刚刚映射的表举行查询、筛选、统计等等一系列操作。
2.1 检察表的属性
uiditem_idbehavior_typeitem_categoryvisit_dateprovince1用户ID商品ID用户行为商品分类产生时间用户地理位置 uiditem_idbehavior_typeitem_categoryvisit_dateprovince110001082285259775140762014-12-08广东2100010824368907155032014-12-08湖南310001082285259788440762014-12-08北京behaviour_type属性1234代表的意思
12341浏览收藏加购物车购买2.2 SQL语句
2.2.1 2014-12-11~12号有多少条购买商品的记载
- //首先查询表中的数据select * from bigdata_user limit 10;//然后查询10号之后select * from bigdata_user where visit_date >'2014-12-10' limit 10;//再添加限制 13号之前select * from bigdata_user where visit_date >'2014-12-10' and visit_date '2014-12-10' and visit_date 4 limit 10; select uid,count(*) from bigdata_user where behavior_type='4'and visit_date='2014-12-12' group by uid having count(behavior_type='4')>4 limit 10;
复制代码 2.2.4 查询商品的购买数量和购买用户的数量,排序购买数量
查询出每个商品的购买次数 和购买人数
- //查询所有购买商品聚集select * from bigdata_user where behavior_type='4' limit 10;//查询每个商品的购买次数select item_id,count(1) buy_number from bigdata_user where behavior_type='4' group by item_id order by buy_number desc limit 10;//查询每个商品购买的用户数量select item_id,count(distinct uid) user_number from bigdata_user where behavior_type='4' group by item_id order by user_number desc limit 10;//查询每个商品购买的总数量和购买的用户数量select item_id,count(1) buy_number,count(distinct uid) user_number from bigdata_user where behavior_type='4' group by item_id order by buy_number desc limit 10;
复制代码 2.2.5 查询广东每日的商品购买率(购买量/浏览量)
- //广东每日浏览商品数select visit_date, count(*) browse_number from bigdata_user where province='广东' and behavior_type='1' group by visit_date order by browse_number desc ;//广东每日购买览商品数select visit_date, count(*) buy_number from bigdata_user where province='广东' and behavior_type='4' group by visit_date order by buy_number desc ;//为以上两个创建单独的表出来//创建表,每日用户浏览数量create table browse_user as select visit_date, count(*) browse_number from bigdata_user where province='广东' and behavior_type='1' group by visit_date order by browse_number desc ;//创建表,每日用户购买数量create table buy_user as select visit_date, count(*) buy_number from bigdata_user where province='广东' and behavior_type='4' group by visit_date order by buy_number desc ;//每日浏览购买乐成率//通过左联查询出每日的浏览量和购买量,而且算出每日购买率select browse_user.visit_date, browse_number,buy_number,buy_number/browse_number buy_rate from browse_user left join buy_user on browse_user.visit_date=buy_user.visit_date;
复制代码 2.2.6查询2019-11-11每个省的浏览数量,购买数量和比例
- //查询2019-12-12每个省的浏览数量,购买数量和比例//a查询2014-12-12每个省的浏览数量select province,count(1) browse_number from bigdata_user where visit_date='2014-12-12' and behavior_type='1' group by province order by browse_number desc;//b查询2014-12-12每个省的购买数量select province,count(1) buy_number from bigdata_user where visit_date='2014-12-12' and behavior_type='4' group by province order by buy_number desc;//以下语句太长格式为select a.province,a.browse_number,b.buy_number,两个相除 from (表格a) a left join (表格b) b on a.province=b.province;//一条语句//直接显示每个省浏览数量、购买数量、购买率select a.province,a.browse_number,b.buy_number,b.buy_number/a.browse_number rate from ( select province,count(1) browse_number from bigdata_user where visit_date='2014-12-12' and behavior_type='1' group by province order by browse_number desc) a left join ( select province,count(1) buy_number from bigdata_user where visit_date='2014-12-12' and behavior_type='4' group by province order by buy_number desc ) b on a.province=b.province;
复制代码 2.2.7用户10001082在2014-12-12号当天生动度:该用户点击行为占该天所有点击行为的比例
- //该天所有点击行为数量select count(*) users from bigdata_user where visit_date='2014-12-12' and behavior_type='1';//该用户的点击量select count(*) user_number from bigdata_user where visit_date='2014-12-12' and behavior_type='1' and uid=10001082;//联合上面两个语句,显示用户点击量、当天点击量和比例select '10001082' uid,a.users,b.user_number,b.user_number/a.users rate from ( select count(*) users from bigdata_user where visit_date='2014-12-12' and behavior_type='1' ) a ,( select count(*) user_number from bigdata_user where visit_date='2014-12-12' and behavior_type='1' and uid=10001082 ) b;
复制代码 2.2.8查询出用户最喜欢的商品集(收藏)
- //查询出用户最喜欢的商品集select item_id,count(1) collect from bigdata_user where behavior_type='2' group by item_id order by collect desc limit 20 ;
复制代码 2.2.9某个地域的用户当天浏览网站的次数
- //查询出每个省浏览网站的次数select province,count(behavior_type) count from bigdata_user where behavior_type='1' group by province;//把效果存入表格create table browse_count as select province,count(behavior_type) count from bigdata_user where behavior_type='1' group by province;//检察效果select * from browse_count;
复制代码 2.2.10查询潜力最大的商品(购物车)
- //查询加入购物车最多的商品select item_id,count(behavior_type) cart_number from bigdata_user where behavior_type='3' group by item_id order by cart_number desc limit 20;
复制代码 来源:https://blog.csdn.net/Luckly_smile/article/details/111773580
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作! |