4.3.1 商品点击表
1)建表语句
hive (gmall)>
drop table if exists dwd_display_log;
CREATE EXTERNAL TABLE dwd_display_log(
mid_id
string,
user_id
string,
version_code
string,
version_name
string,
lang
string,
source
string,
os
string,
area
string,
model
string,
brand
string,
sdk_version
string,
gmail
string,
height_width
string,
app_time
string,
network
string,
lng
string,
lat
string,
action
string,
goodsid
string,
place
string,
extend1
string,
category
string,
server_time
string
)
PARTITIONED BY (dt string)
location ‘/warehouse/gmall/dwd/dwd_display_log/’;
2)导入数据
hive (gmall)>
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_display_log
PARTITION (dt=‘2019-02-10’)
select
mid_id,
user_id,
version_code,
version_name,
lang,
source,
os,
area,
model,
brand,
sdk_version,
gmail,
height_width,
app_time,
network,
lng,
lat,
get_json_object(event_json,’
.
k
v
.
a
c
t
i
o
n
′
)
a
c
t
i
o
n
,
g
e
t
j
s
o
n
o
b
j
e
c
t
(
e
v
e
n
t
j
s
o
n
,
′
.kv.action') action, get_json_object(event_json,'
.kv.action′)action,getjsonobject(eventjson,′.kv.goodsid’) goodsid,
get_json_object(event_json,’
.
k
v
.
p
l
a
c
e
′
)
p
l
a
c
e
,
g
e
t
j
s
o
n
o
b
j
e
c
t
(
e
v
e
n
t
j
s
o
n
,
′
.kv.place') place, get_json_object(event_json,'
.kv.place′)place,getjsonobject(eventjson,′.kv.extend1’) extend1,
get_json_object(event_json,’$.kv.category’) category,
server_time
from dwd_base_event_log
where dt=‘2019-02-10’ and event_name=‘display’;
3)测试
hive (gmall)> select * from dwd_display_log limit 2;
4.3.2 商品详情页表
1)建表语句
hive (gmall)>
drop table if exists dwd_newsdetail_log;
CREATE EXTERNAL TABLE dwd_newsdetail_log(
mid_id
string,
user_id
string,
version_code
string,
version_name
string,
lang
string,
source
string,
os
string,
area
string,
model
string,
brand
string,
sdk_version
string,
gmail
string,
height_width
string,
app_time
string,
network
string,
lng
string,
lat
string,
entry
string,
action
string,
goodsid
string,
showtype
string,
news_staytime
string,
loading_time
string,
type1
string,
category
string,
server_time
string)
PARTITIONED BY (dt string)
location ‘/warehouse/gmall/dwd/dwd_newsdetail_log/’;
2)导入数据
hive (gmall)>
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_newsdetail_log
PARTITION (dt=‘2019-02-10’)
select
mid_id,
user_id,
version_code,
version_name,
lang,
source,
os,
area,
model,
brand,
sdk_version,
gmail,
height_width,
app_time,
network,
lng,
lat,
get_json_object(event_json,’
.
k
v
.
e
n
t
r
y
′
)
e
n
t
r
y
,
g
e
t
j
s
o
n
o
b
j
e
c
t
(
e
v
e
n
t
j
s
o
n
,
′
.kv.entry') entry, get_json_object(event_json,'
.kv.entry′)entry,getjsonobject(eventjson,′.kv.action’) action,
get_json_object(event_json,’
.
k
v
.
g
o
o
d
s
i
d
′
)
g
o
o
d
s
i
d
,
g
e
t
j
s
o
n
o
b
j
e
c
t
(
e
v
e
n
t
j
s
o
n
,
′
.kv.goodsid') goodsid, get_json_object(event_json,'
.kv.goodsid′)goodsid,getjsonobject(eventjson,′.kv.showtype’) showtype,
get_json_object(event_json,’
.
k
v
.
n
e
w
s
s
t
a
y
t
i
m
e
′
)
n
e
w
s
s
t
a
y
t
i
m
e
,
g
e
t
j
s
o
n
o
b
j
e
c
t
(
e
v
e
n
t
j
s
o
n
,
′
.kv.news_staytime') news_staytime, get_json_object(event_json,'
.kv.newsstaytime′)newsstaytime,getjsonobject(eventjson,′.kv.loading_time’) loading_time,
get_json_object(event_json,’
.
k
v
.
t
y
p
e
1
′
)
t
y
p
e
1
,
g
e
t
j
s
o
n
o
b
j
e
c
t
(
e
v
e
n
t
j
s
o
n
,
′
.kv.type1') type1, get_json_object(event_json,'
.kv.type1′)type1,getjsonobject(eventjson,′.kv.category’) category,
server_time
from dwd_base_event_log
where dt=‘2019-02-10’ and event_name=‘newsdetail’;
3)测试
hive (gmall)> select * from dwd_newsdetail_log limit 2;
4.3.3 商品列表页表
1)建表语句
hive (gmall)>
drop table if exists dwd_loading_log;
CREATE EXTERNAL TABLE dwd_loading_log(
mid_id
string,
user_id
string,
version_code
string,
version_name
string,
lang
string,
source
string,
os
string,
area
string,
model
string,
brand
string,
sdk_version
string,
gmail
string,
height_width
string,
app_time
string,
network
string,
lng
string,
lat
string,
action
string,
loading_time
string,
loading_way
string,
extend1
string,
extend2
string,
type
string,
type1
string,
server_time
string)
PARTITIONED BY (dt string)
location ‘/warehouse/gmall/dwd/dwd_loading_log/’;
2)导入数据
hive (gmall)>
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_loading_log
PARTITION (dt=‘2019-02-10’)
select
mid_id,
user_id,
version_code,
version_name,
lang,
source,
os,
area,
model,
brand,
sdk_version,
gmail,
height_width,
app_time,
network,
lng,
lat,
get_json_object(event_json,’
.
k
v
.
a
c
t
i
o
n
′
)
a
c
t
i
o
n
,
g
e
t
j
s
o
n
o
b
j
e
c
t
(
e
v
e
n
t
j
s
o
n
,
′
.kv.action') action, get_json_object(event_json,'
.kv.action′)action,getjsonobject(eventjson,′.kv.loading_time’) loading_time,
get_json_object(event_json,’
.
k
v
.
l
o
a
d
i
n
g
w
a
y
′
)
l
o
a
d
i
n
g
w
a
y
,
g
e
t
j
s
o
n
o
b
j
e
c
t
(
e
v
e
n
t
j
s
o
n
,
′
.kv.loading_way') loading_way, get_json_object(event_json,'
.kv.loadingway′)loadingway,getjsonobject(eventjson,′.kv.extend1’) extend1,
get_json_object(event_json,’
.
k
v
.
e
x
t
e
n
d
2
′
)
e
x
t
e
n
d
2
,
g
e
t
j
s
o
n
o
b
j
e
c
t
(
e
v
e
n
t
j
s
o
n
,
′
.kv.extend2') extend2, get_json_object(event_json,'
.kv.extend2′)extend2,getjsonobject(eventjson,′.kv.type’) type,
get_json_object(event_json,’$.kv.type1’) type1,
server_time
from dwd_base_event_log
where dt=‘2019-02-10’ and event_name=‘loading’;
3)测试
hive (gmall)> select * from dwd_loading_log limit 2;
4.3.4 广告表
1)建表语句
hive (gmall)>
drop table if exists dwd_ad_log;
CREATE EXTERNAL TABLE dwd_ad_log(
mid_id
string,
user_id
string,
version_code
string,
version_name
string,
lang
string,
source
string,
os
string,
area
string,
model
string,
brand
string,
sdk_version
string,
gmail
string,
height_width
string,
app_time
string,
network
string,
lng
string,
lat
string,
entry
string,
action
string,
content
string,
detail
string,
ad_source
string,
behavior
string,
newstype
string,
show_style
string,
server_time
string)
PARTITIONED BY (dt string)
location ‘/warehouse/gmall/dwd/dwd_ad_log/’;
2)导入数据
hive (gmall)>
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_ad_log
PARTITION (dt=‘2019-02-10’)
select
mid_id,
user_id,
version_code,
version_name,
lang,
source,
os,
area,
model,
brand,
sdk_version,
gmail,
height_width,
app_time,
network,
lng,
lat,
get_json_object(event_json,’
.
k
v
.
e
n
t
r
y
′
)
e
n
t
r
y
,
g
e
t
j
s
o
n
o
b
j
e
c
t
(
e
v
e
n
t
j
s
o
n
,
′
.kv.entry') entry, get_json_object(event_json,'
.kv.entry′)entry,getjsonobject(eventjson,′.kv.action’) action,
get_json_object(event_json,’
.
k
v
.
c
o
n
t
e
n
t
′
)
c
o
n
t
e
n
t
,
g
e
t
j
s
o
n
o
b
j
e
c
t
(
e
v
e
n
t
j
s
o
n
,
′
.kv.content') content, get_json_object(event_json,'
.kv.content′)content,getjsonobject(eventjson,′.kv.detail’) detail,
get_json_object(event_json,’
.
k
v
.
s
o
u
r
c
e
′
)
a
d
s
o
u
r
c
e
,
g
e
t
j
s
o
n
o
b
j
e
c
t
(
e
v
e
n
t
j
s
o
n
,
′
.kv.source') ad_source, get_json_object(event_json,'
.kv.source′)adsource,getjsonobject(eventjson,′.kv.behavior’) behavior,
get_json_object(event_json,’
.
k
v
.
n
e
w
s
t
y
p
e
′
)
n
e
w
s
t
y
p
e
,
g
e
t
j
s
o
n
o
b
j
e
c
t
(
e
v
e
n
t
j
s
o
n
,
′
.kv.newstype') newstype, get_json_object(event_json,'
.kv.newstype′)newstype,getjsonobject(eventjson,′.kv.show_style’) show_style,
server_time
from dwd_base_event_log
where dt=‘2019-02-10’ and event_name=‘ad’;
3)测试
hive (gmall)> select * from dwd_ad_log limit 2;
4.3.5 消息通知表
1)建表语句
hive (gmall)>
drop table if exists dwd_notification_log;
CREATE EXTERNAL TABLE dwd_notification_log(
mid_id
string,
user_id
string,
version_code
string,
version_name
string,
lang
string,
source
string,
os
string,
area
string,
model
string,
brand
string,
sdk_version
string,
gmail
string,
height_width
string,
app_time
string,
network
string,
lng
string,
lat
string,
action
string,
noti_type
string,
ap_time
string,
content
string,
server_time
string
)
PARTITIONED BY (dt string)
location ‘/warehouse/gmall/dwd/dwd_notification_log/’;
2)导入数据
hive (gmall)>
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_notification_log
PARTITION (dt=‘2019-02-10’)
select
mid_id,
user_id,
version_code,
version_name,
lang,
source,
os,
area,
model,
brand,
sdk_version,
gmail,
height_width,
app_time,
network,
lng,
lat,
get_json_object(event_json,’
.
k
v
.
a
c
t
i
o
n
′
)
a
c
t
i
o
n
,
g
e
t
j
s
o
n
o
b
j
e
c
t
(
e
v
e
n
t
j
s
o
n
,
′
.kv.action') action, get_json_object(event_json,'
.kv.action′)action,getjsonobject(eventjson,′.kv.noti_type’) noti_type,
get_json_object(event_json,’
.
k
v
.
a
p
t
i
m
e
′
)
a
p
t
i
m
e
,
g
e
t
j
s
o
n
o
b
j
e
c
t
(
e
v
e
n
t
j
s
o
n
,
′
.kv.ap_time') ap_time, get_json_object(event_json,'
.kv.aptime′)aptime,getjsonobject(eventjson,′.kv.content’) content,
server_time
from dwd_base_event_log
where dt=‘2019-02-10’ and event_name=‘notification’;
3)测试
hive (gmall)> select * from dwd_notification_log limit 2;
4.3.6 用户前台活跃表
1)建表语句
hive (gmall)>
drop table if exists dwd_active_foreground_log;
CREATE EXTERNAL TABLE dwd_active_foreground_log(
mid_id
string,
user_id
string,
version_code
string,
version_name
string,
lang
string,
source
string,
os
string,
area
string,
model
string,
brand
string,
sdk_version
string,
gmail
string,
height_width
string,
app_time
string,
network
string,
lng
string,
lat
string,
push_id
string,
access
string,
server_time
string)
PARTITIONED BY (dt string)
location ‘/warehouse/gmall/dwd/dwd_foreground_log/’;
2)导入数据
hive (gmall)>
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_active_foreground_log
PARTITION (dt=‘2019-02-10’)
select
mid_id,
user_id,
version_code,
version_name,
lang,
source,
os,
area,
model,
brand,
sdk_version,
gmail,
height_width,
app_time,
network,
lng,
lat,
get_json_object(event_json,’
.
k
v
.
p
u
s
h
i
d
′
)
p
u
s
h
i
d
,
g
e
t
j
s
o
n
o
b
j
e
c
t
(
e
v
e
n
t
j
s
o
n
,
′
.kv.push_id') push_id, get_json_object(event_json,'
.kv.pushid′)pushid,getjsonobject(eventjson,′.kv.access’) access,
server_time
from dwd_base_event_log
where dt=‘2019-02-10’ and event_name=‘active_foreground’;
3)测试
hive (gmall)> select * from dwd_active_foreground_log limit 2;
4.3.7 用户后台活跃表
1)建表语句
hive (gmall)>
drop table if exists dwd_active_background_log;
CREATE EXTERNAL TABLE dwd_active_background_log(
mid_id
string,
user_id
string,
version_code
string,
version_name
string,
lang
string,
source
string,
os
string,
area
string,
model
string,
brand
string,
sdk_version
string,
gmail
string,
height_width
string,
app_time
string,
network
string,
lng
string,
lat
string,
active_source
string,
server_time
string
)
PARTITIONED BY (dt string)
location ‘/warehouse/gmall/dwd/dwd_background_log/’;
2)导入数据
hive (gmall)>
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_active_background_log
PARTITION (dt=‘2019-02-10’)
select
mid_id,
user_id,
version_code,
version_name,
lang,
source,
os,
area,
model,
brand,
sdk_version,
gmail,
height_width,
app_time,
network,
lng,
lat,
get_json_object(event_json,’$.kv.active_source’) active_source,
server_time
from dwd_base_event_log
where dt=‘2019-02-10’ and event_name=‘active_background’;
3)测试
hive (gmall)> select * from dwd_active_background_log limit 2;
4.3.8 评论表
1)建表语句
hive (gmall)>
drop table if exists dwd_comment_log;
CREATE EXTERNAL TABLE dwd_comment_log(
mid_id
string,
user_id
string,
version_code
string,
version_name
string,
lang
string,
source
string,
os
string,
area
string,
model
string,
brand
string,
sdk_version
string,
gmail
string,
height_width
string,
app_time
string,
network
string,
lng
string,
lat
string,
comment_id
int,
userid
int,
p_comment_id
int,
content
string,
addtime
string,
other_id
int,
praise_count
int,
reply_count
int,
server_time
string
)
PARTITIONED BY (dt string)
location ‘/warehouse/gmall/dwd/dwd_comment_log/’;
2)导入数据
hive (gmall)>
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_comment_log
PARTITION (dt=‘2019-02-10’)
select
mid_id,
user_id,
version_code,
version_name,
lang,
source,
os,
area,
model,
brand,
sdk_version,
gmail,
height_width,
app_time,
network,
lng,
lat,
get_json_object(event_json,’
.
k
v
.
c
o
m
m
e
n
t
i
d
′
)
c
o
m
m
e
n
t
i
d
,
g
e
t
j
s
o
n
o
b
j
e
c
t
(
e
v
e
n
t
j
s
o
n
,
′
.kv.comment_id') comment_id, get_json_object(event_json,'
.kv.commentid′)commentid,getjsonobject(eventjson,′.kv.userid’) userid,
get_json_object(event_json,’
.
k
v
.
p
c
o
m
m
e
n
t
i
d
′
)
p
c
o
m
m
e
n
t
i
d
,
g
e
t
j
s
o
n
o
b
j
e
c
t
(
e
v
e
n
t
j
s
o
n
,
′
.kv.p_comment_id') p_comment_id, get_json_object(event_json,'
.kv.pcommentid′)pcommentid,getjsonobject(eventjson,′.kv.content’) content,
get_json_object(event_json,’
.
k
v
.
a
d
d
t
i
m
e
′
)
a
d
d
t
i
m
e
,
g
e
t
j
s
o
n
o
b
j
e
c
t
(
e
v
e
n
t
j
s
o
n
,
′
.kv.addtime') addtime, get_json_object(event_json,'
.kv.addtime′)addtime,getjsonobject(eventjson,′.kv.other_id’) other_id,
get_json_object(event_json,’
.
k
v
.
p
r
a
i
s
e
c
o
u
n
t
′
)
p
r
a
i
s
e
c
o
u
n
t
,
g
e
t
j
s
o
n
o
b
j
e
c
t
(
e
v
e
n
t
j
s
o
n
,
′
.kv.praise_count') praise_count, get_json_object(event_json,'
.kv.praisecount′)praisecount,getjsonobject(eventjson,′.kv.reply_count’) reply_count,
server_time
from dwd_base_event_log
where dt=‘2019-02-10’ and event_name=‘comment’;
3)测试
hive (gmall)> select * from dwd_comment_log limit 2;
4.3.9 收藏表
1)建表语句
hive (gmall)>
drop table if exists dwd_favorites_log;
CREATE EXTERNAL TABLE dwd_favorites_log(
mid_id
string,
user_id
string,
version_code
string,
version_name
string,
lang
string,
source
string,
os
string,
area
string,
model
string,
brand
string,
sdk_version
string,
gmail
string,
height_width
string,
app_time
string,
network
string,
lng
string,
lat
string,
id
int,
course_id
int,
userid
int,
add_time
string,
server_time
string
)
PARTITIONED BY (dt string)
location ‘/warehouse/gmall/dwd/dwd_favorites_log/’;
2)导入数据
hive (gmall)>
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_favorites_log
PARTITION (dt=‘2019-02-10’)
select
mid_id,
user_id,
version_code,
version_name,
lang,
source,
os,
area,
model,
brand,
sdk_version,
gmail,
height_width,
app_time,
network,
lng,
lat,
get_json_object(event_json,’
.
k
v
.
i
d
′
)
i
d
,
g
e
t
j
s
o
n
o
b
j
e
c
t
(
e
v
e
n
t
j
s
o
n
,
′
.kv.id') id, get_json_object(event_json,'
.kv.id′)id,getjsonobject(eventjson,′.kv.course_id’) course_id,
get_json_object(event_json,’
.
k
v
.
u
s
e
r
i
d
′
)
u
s
e
r
i
d
,
g
e
t
j
s
o
n
o
b
j
e
c
t
(
e
v
e
n
t
j
s
o
n
,
′
.kv.userid') userid, get_json_object(event_json,'
.kv.userid′)userid,getjsonobject(eventjson,′.kv.add_time’) add_time,
server_time
from dwd_base_event_log
where dt=‘2019-02-10’ and event_name=‘favorites’;
3)测试
hive (gmall)> select * from dwd_favorites_log limit 2;
4.3.10 点赞表
1)建表语句
hive (gmall)>
drop table if exists dwd_praise_log;
CREATE EXTERNAL TABLE dwd_praise_log(
mid_id
string,
user_id
string,
version_code
string,
version_name
string,
lang
string,
source
string,
os
string,
area
string,
model
string,
brand
string,
sdk_version
string,
gmail
string,
height_width
string,
app_time
string,
network
string,
lng
string,
lat
string,
id
string,
userid
string,
target_id
string,
type
string,
add_time
string,
server_time
string
)
PARTITIONED BY (dt string)
location ‘/warehouse/gmall/dwd/dwd_praise_log/’;
2)导入数据
hive (gmall)>
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_praise_log
PARTITION (dt=‘2019-02-10’)
select
mid_id,
user_id,
version_code,
version_name,
lang,
source,
os,
area,
model,
brand,
sdk_version,
gmail,
height_width,
app_time,
network,
lng,
lat,
get_json_object(event_json,’
.
k
v
.
i
d
′
)
i
d
,
g
e
t
j
s
o
n
o
b
j
e
c
t
(
e
v
e
n
t
j
s
o
n
,
′
.kv.id') id, get_json_object(event_json,'
.kv.id′)id,getjsonobject(eventjson,′.kv.userid’) userid,
get_json_object(event_json,’
.
k
v
.
t
a
r
g
e
t
i
d
′
)
t
a
r
g
e
t
i
d
,
g
e
t
j
s
o
n
o
b
j
e
c
t
(
e
v
e
n
t
j
s
o
n
,
′
.kv.target_id') target_id, get_json_object(event_json,'
.kv.targetid′)targetid,getjsonobject(eventjson,′.kv.type’) type,
get_json_object(event_json,’$.kv.add_time’) add_time,
server_time
from dwd_base_event_log
where dt=‘2019-02-10’ and event_name=‘praise’;
3)测试
hive (gmall)> select * from dwd_praise_log limit 2;
4.3.11 错误日志表
1)建表语句
hive (gmall)>
drop table if exists dwd_error_log;
CREATE EXTERNAL TABLE dwd_error_log(
mid_id
string,
user_id
string,
version_code
string,
version_name
string,
lang
string,
source
string,
os
string,
area
string,
model
string,
brand
string,
sdk_version
string,
gmail
string,
height_width
string,
app_time
string,
network
string,
lng
string,
lat
string,
errorBrief
string,
errorDetail
string,
server_time
string)
PARTITIONED BY (dt string)
location ‘/warehouse/gmall/dwd/dwd_error_log/’;
2)导入数据
hive (gmall)>
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_error_log
PARTITION (dt=‘2019-02-10’)
select
mid_id,
user_id,
version_code,
version_name,
lang,
source,
os,
area,
model,
brand,
sdk_version,
gmail,
height_width,
app_time,
network,
lng,
lat,
get_json_object(event_json,’
.
k
v
.
e
r
r
o
r
B
r
i
e
f
′
)
e
r
r
o
r
B
r
i
e
f
,
g
e
t
j
s
o
n
o
b
j
e
c
t
(
e
v
e
n
t
j
s
o
n
,
′
.kv.errorBrief') errorBrief, get_json_object(event_json,'
.kv.errorBrief′)errorBrief,getjsonobject(eventjson,′.kv.errorDetail’) errorDetail,
server_time
from dwd_base_event_log
where dt=‘2019-02-10’ and event_name=‘error’;
3)测试
hive (gmall)> select * from dwd_error_log limit 2;