1）在hadoop102的/home/atguigu/bin目录下创建脚本
[atguigu@hadoop102 bin]$ vim dws_uv_log.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 overwrite table "
A
P
P
"
.
d
w
s
u
v
d
e
t
a
i
l
d
a
y
p
a
r
t
i
t
i
o
n
(
d
t
=
′
APP".dws_uv_detail_day partition(dt='
APP".dwsuvdetaildaypartition(dt=′do_date’)
select
mid_id,
concat_ws(’|’, collect_set(user_id)) user_id,
concat_ws(’|’, collect_set(version_code)) version_code,
concat_ws(’|’, collect_set(version_name)) version_name,
concat_ws(’|’, collect_set(lang)) lang,
concat_ws(’|’, collect_set(source)) source,
concat_ws(’|’, collect_set(os)) os,
concat_ws(’|’, collect_set(area)) area,
concat_ws(’|’, collect_set(model)) model,
concat_ws(’|’, collect_set(brand)) brand,
concat_ws(’|’, collect_set(sdk_version)) sdk_version,
concat_ws(’|’, collect_set(gmail)) gmail,
concat_ws(’|’, collect_set(height_width)) height_width,
concat_ws(’|’, collect_set(app_time)) app_time,
concat_ws(’|’, collect_set(network)) network,
concat_ws(’|’, collect_set(lng)) lng,
concat_ws(’|’, collect_set(lat)) lat
from "
A
P
P
"
.
d
w
d
s
t
a
r
t
l
o
g
w
h
e
r
e
d
t
=
′
APP".dwd_start_log where dt='
APP".dwdstartlogwheredt=′do_date’
group by mid_id;
insert overwrite table “
A
P
P
"
.
d
w
s
u
v
d
e
t
a
i
l
w
k
p
a
r
t
i
t
i
o
n
(
w
k
d
t
)
s
e
l
e
c
t
m
i
d
i
d
,
c
o
n
c
a
t
w
s
(
′
∣
′
,
c
o
l
l
e
c
t
s
e
t
(
u
s
e
r
i
d
)
)
u
s
e
r
i
d
,
c
o
n
c
a
t
w
s
(
′
∣
′
,
c
o
l
l
e
c
t
s
e
t
(
v
e
r
s
i
o
n
c
o
d
e
)
)
v
e
r
s
i
o
n
c
o
d
e
,
c
o
n
c
a
t
w
s
(
′
∣
′
,
c
o
l
l
e
c
t
s
e
t
(
v
e
r
s
i
o
n
n
a
m
e
)
)
v
e
r
s
i
o
n
n
a
m
e
,
c
o
n
c
a
t
w
s
(
′
∣
′
,
c
o
l
l
e
c
t
s
e
t
(
l
a
n
g
)
)
l
a
n
g
,
c
o
n
c
a
t
w
s
(
′
∣
′
,
c
o
l
l
e
c
t
s
e
t
(
s
o
u
r
c
e
)
)
s
o
u
r
c
e
,
c
o
n
c
a
t
w
s
(
′
∣
′
,
c
o
l
l
e
c
t
s
e
t
(
o
s
)
)
o
s
,
c
o
n
c
a
t
w
s
(
′
∣
′
,
c
o
l
l
e
c
t
s
e
t
(
a
r
e
a
)
)
a
r
e
a
,
c
o
n
c
a
t
w
s
(
′
∣
′
,
c
o
l
l
e
c
t
s
e
t
(
m
o
d
e
l
)
)
m
o
d
e
l
,
c
o
n
c
a
t
w
s
(
′
∣
′
,
c
o
l
l
e
c
t
s
e
t
(
b
r
a
n
d
)
)
b
r
a
n
d
,
c
o
n
c
a
t
w
s
(
′
∣
′
,
c
o
l
l
e
c
t
s
e
t
(
s
d
k
v
e
r
s
i
o
n
)
)
s
d
k
v
e
r
s
i
o
n
,
c
o
n
c
a
t
w
s
(
′
∣
′
,
c
o
l
l
e
c
t
s
e
t
(
g
m
a
i
l
)
)
g
m
a
i
l
,
c
o
n
c
a
t
w
s
(
′
∣
′
,
c
o
l
l
e
c
t
s
e
t
(
h
e
i
g
h
t
w
i
d
t
h
)
)
h
e
i
g
h
t
w
i
d
t
h
,
c
o
n
c
a
t
w
s
(
′
∣
′
,
c
o
l
l
e
c
t
s
e
t
(
a
p
p
t
i
m
e
)
)
a
p
p
t
i
m
e
,
c
o
n
c
a
t
w
s
(
′
∣
′
,
c
o
l
l
e
c
t
s
e
t
(
n
e
t
w
o
r
k
)
)
n
e
t
w
o
r
k
,
c
o
n
c
a
t
w
s
(
′
∣
′
,
c
o
l
l
e
c
t
s
e
t
(
l
n
g
)
)
l
n
g
,
c
o
n
c
a
t
w
s
(
′
∣
′
,
c
o
l
l
e
c
t
s
e
t
(
l
a
t
)
)
l
a
t
,
d
a
t
e
a
d
d
(
n
e
x
t
d
a
y
(
′
APP".dws_uv_detail_wk partition(wk_dt) select mid_id, concat_ws('|', collect_set(user_id)) user_id, concat_ws('|', collect_set(version_code)) version_code, concat_ws('|', collect_set(version_name)) version_name, concat_ws('|', collect_set(lang)) lang, concat_ws('|', collect_set(source)) source, concat_ws('|', collect_set(os)) os, concat_ws('|', collect_set(area)) area, concat_ws('|', collect_set(model)) model, concat_ws('|', collect_set(brand)) brand, concat_ws('|', collect_set(sdk_version)) sdk_version, concat_ws('|', collect_set(gmail)) gmail, concat_ws('|', collect_set(height_width)) height_width, concat_ws('|', collect_set(app_time)) app_time, concat_ws('|', collect_set(network)) network, concat_ws('|', collect_set(lng)) lng, concat_ws('|', collect_set(lat)) lat, date_add(next_day('
APP".dwsuvdetailwkpartition(wkdt)selectmidid,concatws(′∣′,collectset(userid))userid,concatws(′∣′,collectset(versioncode))versioncode,concatws(′∣′,collectset(versionname))versionname,concatws(′∣′,collectset(lang))lang,concatws(′∣′,collectset(source))source,concatws(′∣′,collectset(os))os,concatws(′∣′,collectset(area))area,concatws(′∣′,collectset(model))model,concatws(′∣′,collectset(brand))brand,concatws(′∣′,collectset(sdkversion))sdkversion,concatws(′∣′,collectset(gmail))gmail,concatws(′∣′,collectset(heightwidth))heightwidth,concatws(′∣′,collectset(apptime))apptime,concatws(′∣′,collectset(network))network,concatws(′∣′,collectset(lng))lng,concatws(′∣′,collectset(lat))lat,dateadd(nextday(′do_date’,‘MO’),-7),
date_add(next_day(‘
d
o
d
a
t
e
′
,
′
M
O
′
)
,
−
1
)
,
c
o
n
c
a
t
(
d
a
t
e
a
d
d
(
n
e
x
t
d
a
y
(
′
do_date','MO'),-1), concat(date_add( next_day('
dodate′,′MO′),−1),concat(dateadd(nextday(′do_date’,‘MO’),-7), ‘_’ , date_add(next_day('
d
o
d
a
t
e
′
,
′
M
O
′
)
,
−
1
)
)
f
r
o
m
"
do_date','MO'),-1) ) from "
dodate′,′MO′),−1))from"APP”.dws_uv_detail_day
where dt>=date_add(next_day(‘
d
o
d
a
t
e
′
,
′
M
O
′
)
,
−
7
)
a
n
d
d
t
<
=
d
a
t
e
a
d
d
(
n
e
x
t
d
a
y
(
′
do_date','MO'),-7) and dt<=date_add(next_day('
dodate′,′MO′),−7)anddt<=dateadd(nextday(′do_date’,‘MO’),-1)
group by mid_id;
insert overwrite table "
A
P
P
"
.
d
w
s
u
v
d
e
t
a
i
l
m
n
p
a
r
t
i
t
i
o
n
(
m
n
)
s
e
l
e
c
t
m
i
d
i
d
,
c
o
n
c
a
t
w
s
(
′
∣
′
,
c
o
l
l
e
c
t
s
e
t
(
u
s
e
r
i
d
)
)
u
s
e
r
i
d
,
c
o
n
c
a
t
w
s
(
′
∣
′
,
c
o
l
l
e
c
t
s
e
t
(
v
e
r
s
i
o
n
c
o
d
e
)
)
v
e
r
s
i
o
n
c
o
d
e
,
c
o
n
c
a
t
w
s
(
′
∣
′
,
c
o
l
l
e
c
t
s
e
t
(
v
e
r
s
i
o
n
n
a
m
e
)
)
v
e
r
s
i
o
n
n
a
m
e
,
c
o
n
c
a
t
w
s
(
′
∣
′
,
c
o
l
l
e
c
t
s
e
t
(
l
a
n
g
)
)
l
a
n
g
,
c
o
n
c
a
t
w
s
(
′
∣
′
,
c
o
l
l
e
c
t
s
e
t
(
s
o
u
r
c
e
)
)
s
o
u
r
c
e
,
c
o
n
c
a
t
w
s
(
′
∣
′
,
c
o
l
l
e
c
t
s
e
t
(
o
s
)
)
o
s
,
c
o
n
c
a
t
w
s
(
′
∣
′
,
c
o
l
l
e
c
t
s
e
t
(
a
r
e
a
)
)
a
r
e
a
,
c
o
n
c
a
t
w
s
(
′
∣
′
,
c
o
l
l
e
c
t
s
e
t
(
m
o
d
e
l
)
)
m
o
d
e
l
,
c
o
n
c
a
t
w
s
(
′
∣
′
,
c
o
l
l
e
c
t
s
e
t
(
b
r
a
n
d
)
)
b
r
a
n
d
,
c
o
n
c
a
t
w
s
(
′
∣
′
,
c
o
l
l
e
c
t
s
e
t
(
s
d
k
v
e
r
s
i
o
n
)
)
s
d
k
v
e
r
s
i
o
n
,
c
o
n
c
a
t
w
s
(
′
∣
′
,
c
o
l
l
e
c
t
s
e
t
(
g
m
a
i
l
)
)
g
m
a
i
l
,
c
o
n
c
a
t
w
s
(
′
∣
′
,
c
o
l
l
e
c
t
s
e
t
(
h
e
i
g
h
t
w
i
d
t
h
)
)
h
e
i
g
h
t
w
i
d
t
h
,
c
o
n
c
a
t
w
s
(
′
∣
′
,
c
o
l
l
e
c
t
s
e
t
(
a
p
p
t
i
m
e
)
)
a
p
p
t
i
m
e
,
c
o
n
c
a
t
w
s
(
′
∣
′
,
c
o
l
l
e
c
t
s
e
t
(
n
e
t
w
o
r
k
)
)
n
e
t
w
o
r
k
,
c
o
n
c
a
t
w
s
(
′
∣
′
,
c
o
l
l
e
c
t
s
e
t
(
l
n
g
)
)
l
n
g
,
c
o
n
c
a
t
w
s
(
′
∣
′
,
c
o
l
l
e
c
t
s
e
t
(
l
a
t
)
)
l
a
t
,
d
a
t
e
f
o
r
m
a
t
(
′
APP".dws_uv_detail_mn partition(mn) select mid_id, concat_ws('|', collect_set(user_id)) user_id, concat_ws('|', collect_set(version_code)) version_code, concat_ws('|', collect_set(version_name)) version_name, concat_ws('|', collect_set(lang))lang, concat_ws('|', collect_set(source)) source, concat_ws('|', collect_set(os)) os, concat_ws('|', collect_set(area)) area, concat_ws('|', collect_set(model)) model, concat_ws('|', collect_set(brand)) brand, concat_ws('|', collect_set(sdk_version)) sdk_version, concat_ws('|', collect_set(gmail)) gmail, concat_ws('|', collect_set(height_width)) height_width, concat_ws('|', collect_set(app_time)) app_time, concat_ws('|', collect_set(network)) network, concat_ws('|', collect_set(lng)) lng, concat_ws('|', collect_set(lat)) lat, date_format('
APP".dwsuvdetailmnpartition(mn)selectmidid,concatws(′∣′,collectset(userid))userid,concatws(′∣′,collectset(versioncode))versioncode,concatws(′∣′,collectset(versionname))versionname,concatws(′∣′,collectset(lang))lang,concatws(′∣′,collectset(source))source,concatws(′∣′,collectset(os))os,concatws(′∣′,collectset(area))area,concatws(′∣′,collectset(model))model,concatws(′∣′,collectset(brand))brand,concatws(′∣′,collectset(sdkversion))sdkversion,concatws(′∣′,collectset(gmail))gmail,concatws(′∣′,collectset(heightwidth))heightwidth,concatws(′∣′,collectset(apptime))apptime,concatws(′∣′,collectset(network))network,concatws(′∣′,collectset(lng))lng,concatws(′∣′,collectset(lat))lat,dateformat(′do_date’,‘yyyy-MM’)
from "
A
P
P
"
.
d
w
s
u
v
d
e
t
a
i
l
d
a
y
w
h
e
r
e
d
a
t
e
f
o
r
m
a
t
(
d
t
,
′
y
y
y
y
−
M
M
′
)
=
d
a
t
e
f
o
r
m
a
t
(
′
APP".dws_uv_detail_day where date_format(dt,'yyyy-MM') = date_format('
APP".dwsuvdetaildaywheredateformat(dt,′yyyy−MM′)=dateformat(′do_date’,‘yyyy-MM’)
group by mid_id;
"
h
i
v
e
−
e
"
hive -e "
hive−e"sql"
2）增加脚本执行权限
[atguigu@hadoop102 bin]$ chmod 777 dws_uv_log.sh
3）脚本使用
[atguigu@hadoop102 module]$ dws_uv_log.sh 2019-02-11
4）查询结果
hive (gmall)> select count() from dws_uv_detail_day where dt=‘2019-02-11’;
hive (gmall)> select count() from dws_uv_detail_wk;
hive (gmall)> select count(*) from dws_uv_detail_mn ;
5）脚本执行时间
企业开发中一般在每日凌晨30分~1点
扫一扫
549
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
