（36）DWD 层用户行为数据加载脚本

（1 ）在 hadoop102 的 /home/atguigu/bin 目录下创建脚本

[atguigu@hadoop102 bin]$ vim ods_to_dwd_log.sh

在脚本中编写如下内容

#!/bin/bash

hive=/opt/module/hive/bin/hive

APP=gmall

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天

if [ -n "$1" ] ;then

do_date=$1

else

do_date=`date -d "-1 day" +%F`

fi

sql="

SET mapreduce.job.queuename=hive;

SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFo

rmat;

insert overwrite table ${APP}.dwd_start_log

partition(dt='$do_date')

select

get_json_object(line,'$.common.ar'),

get_json_object(line,'$.common.ba'),

get_json_object(line,'$.common.ch'),

get_json_object(line,'$.common.md'),

get_json_object(line,'$.common.mid'),

get_json_object(line,'$.common.os'),

get_json_object(line,'$.common.uid'),

get_json_object(line,'$.common.vc'),

get_json_object(line,'$.start.entry'),

get_json_object(line,'$.start.loading_time'),

get_json_object(line,'$.start.open_ad_id'),

get_json_object(line,'$.start.open_ad_ms'),

get_json_object(line,'$.start.open_ad_skip_ms'),

get_json_object(line,'$.ts')

from ${APP}.ods_log

where dt='$do_date'

and get_json_object(line,'$.start') is not null;

insert overwrite table ${APP}.dwd_action_log

partition(dt='$do_date')

select

get_json_object(line,'$.common.ar'),

get_json_object(line,'$.common.ba'),

get_json_object(line,'$.common.ch'),

get_json_object(line,'$.common.md'),

get_json_object(line,'$.common.mid'),

get_json_object(line,'$.common.os'),

get_json_object(line,'$.common.uid'),

get_json_object(line,'$.common.vc'),

get_json_object(line,'$.page.during_time'),

get_json_object(line,'$.page.item'),

get_json_object(line,'$.page.item_type'),

get_json_object(line,'$.page.last_page_id'),

get_json_object(line,'$.page.page_id'),

get_json_object(line,'$.page.sourceType'),

get_json_object(action,'$.action_id'),

get_json_object(action,'$.item'),

get_json_object(action,'$.item_type'),

get_json_object(action,'$.ts')

from ${APP}.ods_log lateral view

${APP}.explode_json_array(get_json_object(line,'$.actions'))

tmp as action

where dt='$do_date'

and get_json_object(line,'$.actions') is not null;

insert overwrite table ${APP}.dwd_display_log

partition(dt='$do_date')

select

get_json_object(line,'$.common.ar'),

get_json_object(line,'$.common.ba'),

get_json_object(line,'$.common.ch'),

get_json_object(line,'$.common.md'),

get_json_object(line,'$.common.mid'),

get_json_object(line,'$.common.os'),

get_json_object(line,'$.common.uid'),

get_json_object(line,'$.common.vc'),

get_json_object(line,'$.page.during_time'),

get_json_object(line,'$.page.item'),

get_json_object(line,'$.page.item_type'),

get_json_object(line,'$.page.last_page_id'),

get_json_object(line,'$.page.page_id'),

get_json_object(line,'$.page.sourceType'),

get_json_object(line,'$.ts'),

get_json_object(display,'$.displayType'),

get_json_object(display,'$.item'),

get_json_object(display,'$.item_type'),

get_json_object(display,'$.order')

from ${APP}.ods_log lateral view

${APP}.explode_json_array(get_json_object(line,'$.displays'))

tmp as display

where dt='$do_date'

and get_json_object(line,'$.displays') is not null;

insert overwrite table ${APP}.dwd_page_log

partition(dt='$do_date')

select

get_json_object(line,'$.common.ar'),

get_json_object(line,'$.common.ba'),

get_json_object(line,'$.common.ch'),

get_json_object(line,'$.common.md'),

get_json_object(line,'$.common.mid'),

get_json_object(line,'$.common.os'),

get_json_object(line,'$.common.uid'),

get_json_object(line,'$.common.vc'),

get_json_object(line,'$.page.during_time'),

get_json_object(line,'$.page.item'),

get_json_object(line,'$.page.item_type'),

get_json_object(line,'$.page.last_page_id'),

get_json_object(line,'$.page.page_id'),

get_json_object(line,'$.page.sourceType'),

get_json_object(line,'$.ts')

from ${APP}.ods_log

where dt='$do_date'

and get_json_object(line,'$.page') is not null;

insert overwrite table ${APP}.dwd_error_log

partition(dt='$do_date')

select

get_json_object(line,'$.common.ar'),

get_json_object(line,'$.common.ba'),

get_json_object(line,'$.common.ch'),

get_json_object(line,'$.common.md'),

get_json_object(line,'$.common.mid'),

get_json_object(line,'$.common.os'),

get_json_object(line,'$.common.uid'),

get_json_object(line,'$.common.vc'),

get_json_object(line,'$.page.item'),

get_json_object(line,'$.page.item_type'),

get_json_object(line,'$.page.last_page_id'),

get_json_object(line,'$.page.page_id'),

get_json_object(line,'$.page.sourceType'),

get_json_object(line,'$.start.entry'),

get_json_object(line,'$.start.loading_time'),

get_json_object(line,'$.start.open_ad_id'),

get_json_object(line,'$.start.open_ad_ms'),

get_json_object(line,'$.start.open_ad_skip_ms'),

get_json_object(line,'$.actions'),

get_json_object(line,'$.displays'),

get_json_object(line,'$.ts'),

get_json_object(line,'$.err.error_code'),

get_json_object(line,'$.err.msg')

from ${APP}.ods_log

where dt='$do_date'

and get_json_object(line,'$.err') is not null;

"

$hive -e "$sql"

2 ）增加脚本执行权限

[atguigu@hadoop102 bin]$ chmod 777 ods_to_dwd_log.sh

3 ）脚本使用

[atguigu@hadoop102 module]$ ods_to_dwd_log.sh 2020-06-15

4 ）查询导入结果

hive (gmall)>

select * from dwd_start_log where dt='2020-06-15' limit 2;

5 ）脚本执行时间

企业开发中一般在每日凌晨 30 分 ~1 点