淘宝大数据分析案例

<?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>

#定义agent名， source、channel、sink的名称
agent3.sources = source3
agent3.channels = channel3
agent3.sinks = sink3
#具体定义source
agent3.sources.source3.type = spooldir
agent3.sources.source3.spoolDir = /home/hadoop/taobao/data
agent3.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
#具体定义sink
agent3.sinks.sink3.type = hive
agent3.sinks.sink3.hive.metastore = thrift://hadoop:9083
agent3.sinks.sink3.hive.database = taobao_data
agent3.sinks.sink3.hive.table = data
agent3.sinks.sink3.serializer = DELIMITED
agent3.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,Province
agent3.sinks.sink3.batchSize = 90
#组装source、channel、sink
agent3.sources.source3.channels = channel3
agent3.sinks.sink3.channel = channel3

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 buckets
row 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 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=1 GROUP BY d.Province ;

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 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 opts
from pyecharts.charts import Pie
a=["点击行为","收藏行为","加购物车行为","购买行为"]
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")
)