淘宝大数据分析案例（百万数据集 Hadoop 项目）

start-all.sh

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

cp * ${FLUME_HOME}/lib/

cd ${FLUME_HOME}/lib/

ll

<?xml version="1.0" encoding="UTF-8" standalone="no"?><?xml-stylesheet type="text/xsl" href="configuration.xsl"?><configuration><property>   <name>javax.jdo.option.ConnectionURL</name>   <value>jdbc:mysql://localhost:3306/hive?useSSL=false&amp;allowPublicKeyRetrieval=true&amp;useUnicode=true&amp;characterEncoding=UTF-8</value></property><property>   <name>javax.jdo.option.ConnectionDriverName</name>   <value>com.mysql.jdbc.Driver</value></property>
<property>   <name>javax.jdo.option.ConnectionUserName</name>   <value>root</value></property>
<property>   <name>javax.jdo.option.ConnectionPassword</name>   <value>root</value></property>
<property>    <name>hive.support.concurrency</name>    <value>true</value></property><property>    <name>hive.exec.dynamic.partition.mode</name>    <value>nonstrict</value></property><property>    <name>hive.txn.manager</name>    <value>org.apache.hadoop.hive.ql.lockmgr.DbTxnManager</value></property><property>    <name>hive.compactor.initiator.on</name>    <value>true</value></property><property>    <name>hive.compactor.worker.threads</name>    <value>1</value>    <!--这里的线程数必须大于0 :理想状态和分桶数一致--></property><property>    <name>hive.enforce.bucketing</name>    <value>true</value></property>
</configuration>

mkdir -p /home/hadoop/taobao/datamkdir -p /home/hadoop/taobao/tmp/point

vi taobao.properties

#定义agent名， source、channel、sink的名称agent3.sources = source3agent3.channels = channel3agent3.sinks = sink3#具体定义sourceagent3.sources.source3.type = spooldiragent3.sources.source3.spoolDir = /home/hadoop/taobao/dataagent3.sources.source3.fileHeader=false

#设置channel类型为磁盘agent3.channels.channel3.type = file#file channle checkpoint文件的路径agent3.channels.channel3.checkpointDir=/home/hadoop/taobao/tmp/point# file channel data文件的路径agent3.channels.channel3.dataDirs=/home/hadoop/taobao/tmp
#具体定义sinkagent3.sinks.sink3.type = hiveagent3.sinks.sink3.hive.metastore = thrift://hadoop:9083agent3.sinks.sink3.hive.database = taobao_dataagent3.sinks.sink3.hive.table = dataagent3.sinks.sink3.serializer = DELIMITEDagent3.sinks.sink3.serializer.delimiter = ","agent3.sinks.sink3.serializer.serdeSeparator = ','agent3.sinks.sink3.serializer.fieldnames = id,user_id,age,gender,item_id,behavior_type,item_category,time,Provinceagent3.sinks.sink3.batchSize = 90
#组装source、channel、sinkagent3.sources.source3.channels = channel3agent3.sinks.sink3.channel = channel3

create database taobao_data;

use taobao_data;

create table `taobao_data`.`data`  (  `id` varchar(255) ,  `user_id` varchar(255) ,  `age` varchar(255) ,  `gender` varchar(255),  `item_id` varchar(255),  `behavior_type` varchar(255),  `item_category` varchar(255),  `time` varchar(255),  `Province` varchar(255)) clustered by(id) into 3 bucketsrow format delimited fields terminated by ','stored as orc tblproperties('transactional'='true');

首先运行该代码启动（终端命令行）
hive --service metastore -p 9083

运行下面的代码（终端命令行）
flume-ng agent --conf conf --conf-file taobao.properties -name agent3 -Dflume.hadoop.logger=INFO,console

create database taobao_data;use taobao_data;
create table `taobao_data`.`data`  (  `id` varchar(255) ,  `user_id` varchar(255) ,  `age` varchar(255) ,  `gender` varchar(255),  `item_id` varchar(255),  `behavior_type` varchar(255),  `item_category` varchar(255),  `time` varchar(255),  `Province` varchar(255)) row format delimited fields terminated by ','stored as textfile;

LOAD DATA LOCAL INPATH '/home/hadoop/data.txt' INTO TABLE data;

create table `xsxk`.`whw_2019443818_xsxk_result`  (  `key` varchar(255) ,  `value` varchar(255)) ;

CREATE DATABASE xsxk;

create table `taobao`.`data_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;

SELECT count( DISTINCT d.item_category)as `商品种类`,d.Province `所属地` from `data` as d GROUP BY d.Province ORDER BY `商品种类` DESC;

SELECT DISTINCT d.user_id as `男性用户`,d.Province from `data` as d where d.gender=0 GROUP BY d.Province ;

SELECT DISTINCT d.user_id as `女性用户`,d.Province from `data` as d where d.gender=1 GROUP BY d.Province ;

select 'PV', u.`总访问量`,u.`Province` as `省份` FROM (select count(*) AS `总访问量`,`Province` FROM `data` GROUP BY `Province`) u;

SELECT 'UV', u. `用户数量`,u.`Province` FROM(SELECT COUNT(DISTINCT user_id) AS `用户数量`,`Province` FROM `data` GROUP BY `Province`) u;

set hive.mapred.mode=nonstrict;

SELECT "跳失率", u.`总访问量` FROM(SELECT b.`仅pv用户` / a.`总用户` AS `总访问量`  FROM(SELECT count( DISTINCT user_id ) AS `总用户` FROM `data`) a,(SELECT count( DISTINCT user_id ) AS `仅pv用户` from (select * from `data`) as c LEFT JOIN (SELECT  DISTINCT user_id as `id`  FROM `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(*) FROM (SELECT u.user_id, SUM( CASE u.behavior_type WHEN '4 ' THEN 1 ELSE 0 END ) AS buy FROM `data` u GROUP BY u.user_id HAVING buy > 0 ) t;

SELECT COUNT(*) 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 `data` as u;

SELECT t2.repeat_buy/t1.total AS `复购率` FROM  (SELECT COUNT(DISTINCT u1.user_id) AS total FROM `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 `data` u  GROUP BY u.user_id HAVING buy>1) t) t2;

from pyecharts import options as optsfrom pyecharts.charts import Piea=["点击行为","收藏行为","加购物车行为","购买行为"]b=[2998564,60921,91654,31122]c = (    Pie()    .add(        "",         [list(z) for z in zip(a, b)]    )    .set_colors(["orange", "purple", "yellow", "red", "pink", "purple", "blue"])#颜色设置    .set_global_opts(title_opts=opts.TitleOpts(title="用户行为分析"))    .set_series_opts(label_opts=opts.LabelOpts(formatter="{b}: {c}"),    toolbox_opts=opts.ToolboxOpts()    )    .render("饼图.html"))