案例需求
创建hive表
创建用户
create database db_pra;
创建表
create table if not exists db_log(
id string ,
url string ,
referer string ,
keyword string ,
type string ,
guid string ,
pageId string ,
moduleId string ,
linkId string ,
attachedInfo string ,
sessionId string ,
trackerU string ,
trackerType string ,
ip string ,
trackerSrc string ,
cookie string ,
orderCode string ,
trackTime string ,
endUserId string ,
firstLink string ,
sessionViewNo string ,
productId string ,
curMerchantId string ,
provinceId string ,
cityId string ,
fee string ,
edmActivity string ,
edmEmail string ,
edmJobId string ,
ieVersion string ,
platform string ,
internalKeyword string ,
resultSum string ,
currentPage string ,
linkPosition string ,
buttonPosition string
)
partitioned by (day string ,hour string)
row format delimited fields terminated by '\t';
加载数据
load data local inpath '/opt/datafile/2015082819' overwrite into table db_log partition(day ='20150828',hour ='18');
查看是否load成功
需求分析 :统计分析每日各时段的PV, UV
统计条目数量
select count(*) sum from db_pra.db_log where data='20150828';
分析PV,UV
PV:统计所有用户总的点击次数
UV:Unique Visitor,访问您网站的一台电脑客户端为一个访客
select data,hour,count(url) pv, count(distinct guid) uv from db_log where data='20150828' group by data,hour;
根据day,hour分组
查看结果
将数据放入hive表中
创建结果表
包括day,hour,pv,uv,四列
create table if not exists db_log_result(
day string ,
hour string,
pv string,
uv string
)
row format delimited fields terminated by '\t';
加载结果数据到hive表
insert into table db_log_result select data,hour,count(url) pv, count(distinct guid) uv from db_log where data='20150828' group by data,hour;
查看结果
导出数据到mysql
创建结果数据表
create table if not exists sqoop_test.db_log_result(
day varchar(255) not null,
hour varchar(255) default null,
pv varchar(255) default null,
uv varchar(255) default null,
primary key(day,hour)
)
使用sqoop将数据从hive导出到mysql
bin/sqoop export \
--connect jdbc:mysql://localhost:3306/sqoop_test \
--username root \
--password 123456 \
--table db_log_result \
--num-mappers 1 \
--export-dir /user/hive/warehouse/db_pra.db/db_log_result \
--input-fields-terminated-by "\t"
查看结果