淘宝数据可视化大屏案例 (Hadoop 实验)

start-all.shjps

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

#定义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 = taobaoagent3.sinks.sink3.hive.table = taobao_dataagent3.sinks.sink3.serializer = DELIMITEDagent3.sinks.sink3.serializer.delimiter = ","agent3.sinks.sink3.serializer.serdeSeparator = ','agent3.sinks.sink3.serializer.fieldnames = user_id,item_id,behavior_type,user_geohash,item_category,date,houragent3.sinks.sink3.batchSize = 90
#组装source、channel、sinkagent3.sources.source3.channels = channel3agent3.sinks.sink3.channel = channel3

vi file_hive.properties

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

把${HIVE_HOME}/hcatalog/share/hcatalog/下的所有包，拷贝入${FLUME_HOME}/lib

cd ${HIVE_HOME}/hcatalog/share/hcatalog/
cp * ${FLUME_HOME}/lib/

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 bucketsrow format delimited fields terminated by ','stored as orc tblproperties('transactional'='true');

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

hive --service metastore -p 9083

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

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

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;

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);

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

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;

set hive.mapred.mode=nonstrict;

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;

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;

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'