6.3 ADS层
6.3.1 建表语句
hive (gmall)>
drop table ads_sale_tm_category1_stat_mn;
create external table ads_sale_tm_category1_stat_mn
(
tm_id string comment ‘品牌id’,
category1_id string comment '1级品类id ',
category1_name string comment '1级品类名称 ',
buycount bigint comment ‘购买人数’,
buy_twice_last bigint comment ‘两次以上购买人数’,
buy_twice_last_ratio decimal(10,2) comment ‘单次复购率’,
buy_3times_last bigint comment ‘三次以上购买人数’,
buy_3times_last_ratio decimal(10,2) comment ‘多次复购率’,
stat_mn string comment ‘统计月份’,
stat_date string comment ‘统计日期’
) COMMENT ‘复购率统计’
row format delimited fields terminated by ‘\t’
location ‘/warehouse/gmall/ads/ads_sale_tm_category1_stat_mn/’
;
6.3.2 数据导入
1）数据导入
hive (gmall)>
insert into table ads_sale_tm_category1_stat_mn
select
mn.sku_tm_id,
mn.sku_category1_id,
mn.sku_category1_name,
sum(if(mn.order_count>=1,1,0)) buycount,
sum(if(mn.order_count>=2,1,0)) buyTwiceLast,
sum(if(mn.order_count>=2,1,0))/sum( if(mn.order_count>=1,1,0)) buyTwiceLastRatio,
sum(if(mn.order_count>=3,1,0)) buy3timeLast ,
sum(if(mn.order_count>=3,1,0))/sum( if(mn.order_count>=1,1,0)) buy3timeLastRatio ,
date_format(‘2019-02-10’ ,‘yyyy-MM’) stat_mn,
‘2019-02-10’ stat_date
from
(
select sd.sku_tm_id,
sd.sku_category1_id,
sd.sku_category1_name,
user_id,
sum(order_count) order_count
from dws_sale_detail_daycount sd
where
date_format(dt,‘yyyy-MM’)=date_format(‘2019-02-10’ ,‘yyyy-MM’)
group by
sd.sku_tm_id, sd.sku_category1_id, user_id, sd.sku_category1_name
) mn
group by mn.sku_tm_id, mn.sku_category1_id, mn.sku_category1_name
;
2）查询导入数据
hive (gmall)>select * from ads_sale_tm_category1_stat_mn;
6.3.3数据导入脚本
1）在/home/atguigu/bin目录下创建脚本ads_sale.sh
[atguigu@hadoop102 bin]$ vim ads_sale.sh
#!/bin/bash
定义变量方便修改
APP=gmall
hive=/opt/module/hive/bin/hive
如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n “$1” ] ;then
do_date=$1
else
do_date=date-d "-1 day"+%F
fi
sql="
set hive.exec.dynamic.partition.mode=nonstrict;
insert into table “
A
P
P
"
.
a
d
s
s
a
l
e
t
m
c
a
t
e
g
o
r
y
1
s
t
a
t
m
n
s
e
l
e
c
t
m
n
.
s
k
u
t
m
i
d
,
m
n
.
s
k
u
c
a
t
e
g
o
r
y
1
i
d
,
m
n
.
s
k
u
c
a
t
e
g
o
r
y
1
n
a
m
e
,
s
u
m
(
i
f
(
m
n
.
o
r
d
e
r
c
o
u
n
t
>
=
1
,
1
,
0
)
)
b
u
y
c
o
u
n
t
,
s
u
m
(
i
f
(
m
n
.
o
r
d
e
r
c
o
u
n
t
>
=
2
,
1
,
0
)
)
b
u
y
T
w
i
c
e
L
a
s
t
,
s
u
m
(
i
f
(
m
n
.
o
r
d
e
r
c
o
u
n
t
>
=
2
,
1
,
0
)
)
/
s
u
m
(
i
f
(
m
n
.
o
r
d
e
r
c
o
u
n
t
>
=
1
,
1
,
0
)
)
b
u
y
T
w
i
c
e
L
a
s
t
R
a
t
i
o
,
s
u
m
(
i
f
(
m
n
.
o
r
d
e
r
c
o
u
n
t
>
=
3
,
1
,
0
)
)
b
u
y
3
t
i
m
e
L
a
s
t
,
s
u
m
(
i
f
(
m
n
.
o
r
d
e
r
c
o
u
n
t
>
=
3
,
1
,
0
)
)
/
s
u
m
(
i
f
(
m
n
.
o
r
d
e
r
c
o
u
n
t
>
=
1
,
1
,
0
)
)
b
u
y
3
t
i
m
e
L
a
s
t
R
a
t
i
o
,
d
a
t
e
f
o
r
m
a
t
(
′
APP".ads_sale_tm_category1_stat_mn select mn.sku_tm_id, mn.sku_category1_id, mn.sku_category1_name, sum(if(mn.order_count>=1,1,0)) buycount, sum(if(mn.order_count>=2,1,0)) buyTwiceLast, sum(if(mn.order_count>=2,1,0))/sum( if(mn.order_count>=1,1,0)) buyTwiceLastRatio, sum(if(mn.order_count>=3,1,0)) buy3timeLast, sum(if(mn.order_count>=3,1,0))/sum( if(mn.order_count>=1,1,0)) buy3timeLastRatio , date_format('
APP".adssaletmcategory1statmnselectmn.skutmid,mn.skucategory1id,mn.skucategory1name,sum(if(mn.ordercount>=1,1,0))buycount,sum(if(mn.ordercount>=2,1,0))buyTwiceLast,sum(if(mn.ordercount>=2,1,0))/sum(if(mn.ordercount>=1,1,0))buyTwiceLastRatio,sum(if(mn.ordercount>=3,1,0))buy3timeLast,sum(if(mn.ordercount>=3,1,0))/sum(if(mn.ordercount>=1,1,0))buy3timeLastRatio,dateformat(′do_date’ ,‘yyyy-MM’) stat_mn,
'
d
o
d
a
t
e
′
s
t
a
t
d
a
t
e
f
r
o
m
(
s
e
l
e
c
t
o
d
.
s
k
u
t
m
i
d
,
o
d
.
s
k
u
c
a
t
e
g
o
r
y
1
i
d
,
o
d
.
s
k
u
c
a
t
e
g
o
r
y
1
n
a
m
e
,
u
s
e
r
i
d
,
s
u
m
(
o
r
d
e
r
c
o
u
n
t
)
o
r
d
e
r
c
o
u
n
t
f
r
o
m
"
do_date' stat_date from ( select od.sku_tm_id, od.sku_category1_id, od.sku_category1_name, user_id, sum(order_count) order_count from "
dodate′statdatefrom(selectod.skutmid,od.skucategory1id,od.skucategory1name,userid,sum(ordercount)ordercountfrom"APP”.dws_sale_detail_daycount od
where date_format(dt,‘yyyy-MM’)=date_format(’$do_date’ ,‘yyyy-MM’)
group by od.sku_tm_id, od.sku_category1_id, user_id, od.sku_category1_name
) mn
group by mn.sku_tm_id, mn.sku_category1_id, mn.sku_category1_name;
"
h
i
v
e
−
e
"
hive -e "
hive−e"sql"
2）增加脚本执行权限
[atguigu@hadoop102 bin]$ chmod 777 ads_sale.sh
3）执行脚本导入数据
[atguigu@hadoop102 bin]$ads_sale.sh 2019-02-11
4）查看导入数据
hive (gmall)>
select * from ads_sale_tm_category1_stat_mn limit 2;
扫一扫
628
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
