-
电商数仓笔记7_数据仓库系统（数仓搭建-DWD层，数仓搭建-DWS层，数仓搭建-DWT层，数仓搭建-ADS层）
电商数仓
一、数仓搭建-DWD层
- 1）对用户行为数据解析。
- 2）对业务数据采用维度模型重新建模。
1、 DWD层（用户行为日志）
（1）日志解析思路
1）日志结构回顾
（1）页面埋点日志
（2）启动日志
2）日志解析思路
（2）get_json_object函数使用
1）数据
[{"name":"大郎","sex":"男","age":"25"},{"name":"西门庆","sex":"男","age":"47"}]- 1
2）取出第一个json对象
hive (gmall)> select get_json_object('[{"name":"大郎","sex":"男","age":"25"},{"name":"西门庆","sex":"男","age":"47"}]','$[0]'); 结果是：{"name":"大郎","sex":"男","age":"25"}- 1
- 2
- 3
3）取出第一个json的age字段的值
hive (gmall)> SELECT get_json_object('[{"name":"大郎","sex":"男","age":"25"},{"name":"西门庆","sex":"男","age":"47"}]',"$[0].age"); 结果是：25- 1
- 2
- 3
（3）启动日志表
- 启动日志解析思路：启动日志表中每行数据对应一个启动记录，一个启动记录应该包含日志中的公共信息和启动信息。先将所有包含start字段的日志过滤出来，然后使用get_json_object函数解析每个字段。
1）建表语句
DROP TABLE IF EXISTS dwd_start_log; CREATE EXTERNAL TABLE dwd_start_log( `area_code` STRING COMMENT '地区编码', `brand` STRING COMMENT '手机品牌', `channel` STRING COMMENT '渠道', `is_new` STRING COMMENT '是否首次启动', `model` STRING COMMENT '手机型号', `mid_id` STRING COMMENT '设备id', `os` STRING COMMENT '操作系统', `user_id` STRING COMMENT '会员id', `version_code` STRING COMMENT 'app版本号', `entry` STRING COMMENT 'icon手机图标 notice 通知 install 安装后启动', `loading_time` BIGINT COMMENT '启动加载时间', `open_ad_id` STRING COMMENT '广告页ID ', `open_ad_ms` BIGINT COMMENT '广告总共播放时间', `open_ad_skip_ms` BIGINT COMMENT '用户跳过广告时点', `ts` BIGINT COMMENT '时间' ) COMMENT '启动日志表' PARTITIONED BY (`dt` STRING) -- 按照时间创建分区 STORED AS PARQUET -- 采用parquet列式存储 LOCATION '/warehouse/gmall/dwd/dwd_start_log' -- 指定在HDFS上存储位置 TBLPROPERTIES('parquet.compression'='lzo') -- 采用LZO压缩 ;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
2）数据导入
hive (gmall)> insert overwrite table dwd_start_log partition(dt='2020-06-14') select get_json_object(line,'$.common.ar'), get_json_object(line,'$.common.ba'), get_json_object(line,'$.common.ch'), get_json_object(line,'$.common.is_new'), 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 ods_log where dt='2020-06-14' and get_json_object(line,'$.start') is not null;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
3）查看数据
hive (gmall)> select * from dwd_start_log where dt='2020-06-14' limit 2;- 1
- 2
（4）页面日志表
- 页面日志解析思路：页面日志表中每行数据对应一个页面访问记录，一个页面访问记录应该包含日志中的公共信息和页面信息。先将所有包含page字段的日志过滤出来，然后使用get_json_object函数解析每个字段。
1）建表语句
DROP TABLE IF EXISTS dwd_page_log; CREATE EXTERNAL TABLE dwd_page_log( `area_code` STRING COMMENT '地区编码', `brand` STRING COMMENT '手机品牌', `channel` STRING COMMENT '渠道', `is_new` STRING COMMENT '是否首次启动', `model` STRING COMMENT '手机型号', `mid_id` STRING COMMENT '设备id', `os` STRING COMMENT '操作系统', `user_id` STRING COMMENT '会员id', `version_code` STRING COMMENT 'app版本号', `during_time` BIGINT COMMENT '持续时间毫秒', `page_item` STRING COMMENT '目标id ', `page_item_type` STRING COMMENT '目标类型', `last_page_id` STRING COMMENT '上页类型', `page_id` STRING COMMENT '页面ID ', `source_type` STRING COMMENT '来源类型', `ts` bigint ) COMMENT '页面日志表' PARTITIONED BY (`dt` STRING) STORED AS PARQUET LOCATION '/warehouse/gmall/dwd/dwd_page_log' TBLPROPERTIES('parquet.compression'='lzo');- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
2）数据导入
hive (gmall)> insert overwrite table dwd_page_log partition(dt='2020-06-14') select get_json_object(line,'$.common.ar'), get_json_object(line,'$.common.ba'), get_json_object(line,'$.common.ch'), get_json_object(line,'$.common.is_new'), 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.source_type'), get_json_object(line,'$.ts') from ods_log where dt='2020-06-14' and get_json_object(line,'$.page') is not null;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
3）查看数据
hive (gmall)> select * from dwd_page_log where dt='2020-06-14' limit 2;- 1
- 2
（5）动作日志表
- 动作日志解析思路：动作日志表中每行数据对应用户的一个动作记录，一个动作记录应当包含公共信息、页面信息以及动作信息。先将包含action字段的日志过滤出来，然后通过UDTF函数，将action数组“炸开”（类似于explode函数的效果），然后使用get_json_object函数解析每个字段。
1）建表语句
DROP TABLE IF EXISTS dwd_action_log; CREATE EXTERNAL TABLE dwd_action_log( `area_code` STRING COMMENT '地区编码', `brand` STRING COMMENT '手机品牌', `channel` STRING COMMENT '渠道', `is_new` STRING COMMENT '是否首次启动', `model` STRING COMMENT '手机型号', `mid_id` STRING COMMENT '设备id', `os` STRING COMMENT '操作系统', `user_id` STRING COMMENT '会员id', `version_code` STRING COMMENT 'app版本号', `during_time` BIGINT COMMENT '持续时间毫秒', `page_item` STRING COMMENT '目标id ', `page_item_type` STRING COMMENT '目标类型', `last_page_id` STRING COMMENT '上页类型', `page_id` STRING COMMENT '页面id ', `source_type` STRING COMMENT '来源类型', `action_id` STRING COMMENT '动作id', `item` STRING COMMENT '目标id ', `item_type` STRING COMMENT '目标类型', `ts` BIGINT COMMENT '时间' ) COMMENT '动作日志表' PARTITIONED BY (`dt` STRING) STORED AS PARQUET LOCATION '/warehouse/gmall/dwd/dwd_action_log' TBLPROPERTIES('parquet.compression'='lzo');- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
2）创建UDTF函数——设计思路
3）创建UDTF函数——编写代码
（1）创建一个maven工程：hivefunction
（2）创建包名：com.lyh.hive.udtf
（3）引入如下依赖<dependencies> <!--添加hive依赖--> <dependency> <groupId>org.apache.hive</groupId> <artifactId>hive-exec</artifactId> <version>3.1.2</version> </dependency> </dependencies>- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
（4）编码
package com.lyh.hive.udtf; import org.apache.hadoop.hive.ql.exec.UDFArgumentException; import org.apache.hadoop.hive.ql.metadata.HiveException; import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF; import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector; import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory; import org.apache.hadoop.hive.serde2.objectinspector.PrimitiveObjectInspector; import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector; import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory; import org.json.JSONArray; import java.util.ArrayList; import java.util.List; public class ExplodeJSONArray extends GenericUDTF { @Override public StructObjectInspector initialize(ObjectInspector[] argOIs) throws UDFArgumentException { // 1 参数合法性检查 if (argOIs.length != 1) { throw new UDFArgumentException("explode_json_array 只需要一个参数"); } // 2 第一个参数必须为string //判断参数是否为基础数据类型 if (argOIs[0].getCategory() != ObjectInspector.Category.PRIMITIVE) { throw new UDFArgumentException("explode_json_array 只接受基础类型参数"); } //将参数对象检查器强转为基础类型对象检查器 PrimitiveObjectInspector argumentOI = (PrimitiveObjectInspector) argOIs[0]; //判断参数是否为String类型 if (argumentOI.getPrimitiveCategory() != PrimitiveObjectInspector.PrimitiveCategory.STRING) { throw new UDFArgumentException("explode_json_array 只接受string类型的参数"); } // 3 定义返回值名称和类型 List<String> fieldNames = new ArrayList<String>(); List<ObjectInspector> fieldOIs = new ArrayList<ObjectInspector>(); fieldNames.add("items"); fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector); return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames, fieldOIs); } public void process(Object[] objects) throws HiveException { // 1 获取传入的数据 String jsonArray = objects[0].toString(); // 2 将string转换为json数组 JSONArray actions = new JSONArray(jsonArray); // 3 循环一次，取出数组中的一个json，并写出 for (int i = 0; i < actions.length(); i++) { String[] result = new String[1]; result[0] = actions.getString(i); forward(result); } } public void close() throws HiveException { } }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
4）创建函数
（1）打包
（2）将hivefunction-1.0-SNAPSHOT.jar上传到hadoop102的/opt/module，然后再将该jar包上传到HDFS的/user/hive/jars路径下[lyh@hadoop102 module]$ hadoop fs -mkdir -p /user/hive/jars [lyh@hadoop102 module]$ hadoop fs -put hivefunction-1.0-SNAPSHOT.jar /user/hive/jars- 1
- 2
（3）在datagrip中创建永久函数与开发好的java class关联
create function explode_json_array as 'com.lyh.gmall.hive.udtf.ExplodeJSONArray' using jar 'hdfs://hadoop102:8020/user/hive/jars/gmall-udtf-1.0-SNAPSHOT.jar';- 1
（4）注意：如果修改了自定义函数重新生成jar包怎么处理？只需要替换HDFS路径上的旧jar包，然后重启Hive客户端即可。
5）数据导入insert overwrite table dwd_action_log partition(dt='2020-06-14') select get_json_object(line,'$.common.ar'), get_json_object(line,'$.common.ba'), get_json_object(line,'$.common.ch'), get_json_object(line,'$.common.is_new'), 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.source_type'), get_json_object(action,'$.action_id'), get_json_object(action,'$.item'), get_json_object(action,'$.item_type'), get_json_object(action,'$.ts') from ods_log lateral view explode_json_array(get_json_object(line,'$.actions')) tmp as action where dt='2020-06-14' and get_json_object(line,'$.actions') is not null;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
3）查看数据
select * from dwd_action_log where dt='2020-06-14' limit 2;- 1
（6）曝光日志表
- 曝光日志解析思路：曝光日志表中每行数据对应一个曝光记录，一个曝光记录应当包含公共信息、页面信息以及曝光信息。先将包含display字段的日志过滤出来，然后通过UDTF函数，将display数组“炸开”（类似于explode函数的效果），然后使用get_json_object函数解析每个字段。
1）建表语句
DROP TABLE IF EXISTS dwd_display_log; CREATE EXTERNAL TABLE dwd_display_log( `area_code` STRING COMMENT '地区编码', `brand` STRING COMMENT '手机品牌', `channel` STRING COMMENT '渠道', `is_new` STRING COMMENT '是否首次启动', `model` STRING COMMENT '手机型号', `mid_id` STRING COMMENT '设备id', `os` STRING COMMENT '操作系统', `user_id` STRING COMMENT '会员id', `version_code` STRING COMMENT 'app版本号', `during_time` BIGINT COMMENT 'app版本号', `page_item` STRING COMMENT '目标id ', `page_item_type` STRING COMMENT '目标类型', `last_page_id` STRING COMMENT '上页类型', `page_id` STRING COMMENT '页面ID ', `source_type` STRING COMMENT '来源类型', `ts` BIGINT COMMENT 'app版本号', `display_type` STRING COMMENT '曝光类型', `item` STRING COMMENT '曝光对象id ', `item_type` STRING COMMENT 'app版本号', `order` BIGINT COMMENT '曝光顺序', `pos_id` BIGINT COMMENT '曝光位置' ) COMMENT '曝光日志表' PARTITIONED BY (`dt` STRING) STORED AS PARQUET LOCATION '/warehouse/gmall/dwd/dwd_display_log' TBLPROPERTIES('parquet.compression'='lzo');- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
2）数据导入
insert overwrite table dwd_display_log partition(dt='2020-06-14') select get_json_object(line,'$.common.ar'), get_json_object(line,'$.common.ba'), get_json_object(line,'$.common.ch'), get_json_object(line,'$.common.is_new'), 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.source_type'), get_json_object(line,'$.ts'), get_json_object(display,'$.display_type'), get_json_object(display,'$.item'), get_json_object(display,'$.item_type'), get_json_object(display,'$.order'), get_json_object(display,'$.pos_id') from ods_log lateral view explode_json_array(get_json_object(line,'$.displays')) tmp as display where dt='2020-06-14' and get_json_object(line,'$.displays') is not null;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
3）查看数据
select * from dwd_display_log where dt='2020-06-14' limit 2;- 1
（7）错误日志表
- 错误日志解析思路：错误日志表中每行数据对应一个错误记录，为方便定位错误，一个错误记录应当包含与之对应的公共信息、页面信息、曝光信息、动作信息、启动信息以及错误信息。先将包含err字段的日志过滤出来，然后使用get_json_object函数解析所有字段
1）建表语句
DROP TABLE IF EXISTS dwd_error_log; CREATE EXTERNAL TABLE dwd_error_log( `area_code` STRING COMMENT '地区编码', `brand` STRING COMMENT '手机品牌', `channel` STRING COMMENT '渠道', `is_new` STRING COMMENT '是否首次启动', `model` STRING COMMENT '手机型号', `mid_id` STRING COMMENT '设备id', `os` STRING COMMENT '操作系统', `user_id` STRING COMMENT '会员id', `version_code` STRING COMMENT 'app版本号', `page_item` STRING COMMENT '目标id ', `page_item_type` STRING COMMENT '目标类型', `last_page_id` STRING COMMENT '上页类型', `page_id` STRING COMMENT '页面ID ', `source_type` STRING COMMENT '来源类型', `entry` STRING COMMENT ' icon手机图标 notice 通知 install 安装后启动', `loading_time` STRING COMMENT '启动加载时间', `open_ad_id` STRING COMMENT '广告页ID ', `open_ad_ms` STRING COMMENT '广告总共播放时间', `open_ad_skip_ms` STRING COMMENT '用户跳过广告时点', `actions` STRING COMMENT '动作', `displays` STRING COMMENT '曝光', `ts` STRING COMMENT '时间', `error_code` STRING COMMENT '错误码', `msg` STRING COMMENT '错误信息' ) COMMENT '错误日志表' PARTITIONED BY (`dt` STRING) STORED AS PARQUET LOCATION '/warehouse/gmall/dwd/dwd_error_log' TBLPROPERTIES('parquet.compression'='lzo');- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
说明：此处为对动作数组和曝光数组做处理，如需分析错误与单个动作或曝光的关联，可先使用explode_json_array函数将数组“炸开”，再使用get_json_object函数获取具体字段。
4）数据导入insert overwrite table dwd_error_log partition(dt='2020-06-14') select get_json_object(line,'$.common.ar'), get_json_object(line,'$.common.ba'), get_json_object(line,'$.common.ch'), get_json_object(line,'$.common.is_new'), 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.source_type'), 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 ods_log where dt='2020-06-14' and get_json_object(line,'$.err') is not null;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
5）查看数据
hive (gmall)> select * from dwd_error_log where dt='2020-06-14' limit 2;- 1
- 2
（8）DWD层用户行为数据加载脚本
1）编写脚本
（1）在hadoop102的/home/lyh/bin目录下创建脚本[lyh@hadoop102 bin]$ vim ods_to_dwd_log.sh- 1
在脚本中编写如下内容- 1
#!/bin/bash APP=gmall # 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天 if [ -n "$2" ] ;then do_date=$2 else do_date=`date -d "-1 day" +%F` fi dwd_start_log=" set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; 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.is_new'), 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;" dwd_page_log=" set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; 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.is_new'), 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.source_type'), get_json_object(line,'$.ts') from ${APP}.ods_log where dt='$do_date' and get_json_object(line,'$.page') is not null;" dwd_action_log=" set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; 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.is_new'), 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.source_type'), 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;" dwd_display_log=" set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; 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.is_new'), 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.source_type'), get_json_object(line,'$.ts'), get_json_object(display,'$.display_type'), get_json_object(display,'$.item'), get_json_object(display,'$.item_type'), get_json_object(display,'$.order'), get_json_object(display,'$.pos_id') 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;" dwd_error_log=" set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; 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.is_new'), 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.source_type'), 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;" case $1 in dwd_start_log ) hive -e "$dwd_start_log" ;; dwd_page_log ) hive -e "$dwd_page_log" ;; dwd_action_log ) hive -e "$dwd_action_log" ;; dwd_display_log ) hive -e "$dwd_display_log" ;; dwd_error_log ) hive -e "$dwd_error_log" ;; all ) hive -e "$dwd_start_log$dwd_page_log$dwd_action_log$dwd_display_log$dwd_error_log" ;; esac- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
（2）增加脚本执行权限
[lyh@hadoop102 bin]$ chmod 777 ods_to_dwd_log.sh- 1
2）脚本使用
（1）执行脚本[lyh@hadoop102 module]$ ods_to_dwd_log.sh all 2020-06-14- 1
（2）查询导入结果
2、DWD层（业务数据）
- 业务数据方面DWD层的搭建主要注意点在于维度建模。
（1）DWD层业务数据首日装载脚本
1）编写脚本
（1）在/home/lyh/bin目录下创建脚本ods_to_dwd_db_init.sh[lyh@hadoop102 bin]$ vim ods_to_dwd_db_init.sh- 1
在脚本中填写如下内容
#!/bin/bash APP=gmall if [ -n "$2" ] ;then do_date=$2 else echo "请传入日期参数" exit fi dwd_order_info=" set hive.exec.dynamic.partition.mode=nonstrict; set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; insert overwrite table ${APP}.dwd_order_info partition(dt) select oi.id, oi.order_status, oi.user_id, oi.province_id, oi.payment_way, oi.delivery_address, oi.out_trade_no, oi.tracking_no, oi.create_time, times.ts['1002'] payment_time, times.ts['1003'] cancel_time, times.ts['1004'] finish_time, times.ts['1005'] refund_time, times.ts['1006'] refund_finish_time, oi.expire_time, feight_fee, feight_fee_reduce, activity_reduce_amount, coupon_reduce_amount, original_amount, final_amount, case when times.ts['1003'] is not null then date_format(times.ts['1003'],'yyyy-MM-dd') when times.ts['1004'] is not null and date_add(date_format(times.ts['1004'],'yyyy-MM-dd'),7)<='$do_date' and times.ts['1005'] is null then date_add(date_format(times.ts['1004'],'yyyy-MM-dd'),7) when times.ts['1006'] is not null then date_format(times.ts['1006'],'yyyy-MM-dd') when oi.expire_time is not null then date_format(oi.expire_time,'yyyy-MM-dd') else '9999-99-99' end from ( select * from ${APP}.ods_order_info where dt='$do_date' )oi left join ( select order_id, str_to_map(concat_ws(',',collect_set(concat(order_status,'=',operate_time))),',','=') ts from ${APP}.ods_order_status_log where dt='$do_date' group by order_id )times on oi.id=times.order_id;" dwd_order_detail=" set hive.exec.dynamic.partition.mode=nonstrict; set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; insert overwrite table ${APP}.dwd_order_detail partition(dt) select od.id, od.order_id, oi.user_id, od.sku_id, oi.province_id, oda.activity_id, oda.activity_rule_id, odc.coupon_id, od.create_time, od.source_type, od.source_id, od.sku_num, od.order_price*od.sku_num, od.split_activity_amount, od.split_coupon_amount, od.split_final_amount, date_format(create_time,'yyyy-MM-dd') from ( select * from ${APP}.ods_order_detail where dt='$do_date' )od left join ( select id, user_id, province_id from ${APP}.ods_order_info where dt='$do_date' )oi on od.order_id=oi.id left join ( select order_detail_id, activity_id, activity_rule_id from ${APP}.ods_order_detail_activity where dt='$do_date' )oda on od.id=oda.order_detail_id left join ( select order_detail_id, coupon_id from ${APP}.ods_order_detail_coupon where dt='$do_date' )odc on od.id=odc.order_detail_id;" dwd_payment_info=" set hive.exec.dynamic.partition.mode=nonstrict; set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; insert overwrite table ${APP}.dwd_payment_info partition(dt) select pi.id, pi.order_id, pi.user_id, oi.province_id, pi.trade_no, pi.out_trade_no, pi.payment_type, pi.payment_amount, pi.payment_status, pi.create_time, pi.callback_time, nvl(date_format(pi.callback_time,'yyyy-MM-dd'),'9999-99-99') from ( select * from ${APP}.ods_payment_info where dt='$do_date' )pi left join ( select id,province_id from ${APP}.ods_order_info where dt='$do_date' )oi on pi.order_id=oi.id;" dwd_cart_info=" set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; insert overwrite table ${APP}.dwd_cart_info partition(dt='$do_date') select id, user_id, sku_id, source_type, source_id, cart_price, is_ordered, create_time, operate_time, order_time, sku_num from ${APP}.ods_cart_info where dt='$do_date';" dwd_comment_info=" set hive.exec.dynamic.partition.mode=nonstrict; set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; insert overwrite table ${APP}.dwd_comment_info partition(dt) select id, user_id, sku_id, spu_id, order_id, appraise, create_time, date_format(create_time,'yyyy-MM-dd') from ${APP}.ods_comment_info where dt='$do_date'; " dwd_favor_info=" set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; insert overwrite table ${APP}.dwd_favor_info partition(dt='$do_date') select id, user_id, sku_id, spu_id, is_cancel, create_time, cancel_time from ${APP}.ods_favor_info where dt='$do_date';" dwd_coupon_use=" set hive.exec.dynamic.partition.mode=nonstrict; set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; insert overwrite table ${APP}.dwd_coupon_use partition(dt) select id, coupon_id, user_id, order_id, coupon_status, get_time, using_time, used_time, expire_time, coalesce(date_format(used_time,'yyyy-MM-dd'),date_format(expire_time,'yyyy-MM-dd'),'9999-99-99') from ${APP}.ods_coupon_use where dt='$do_date';" dwd_order_refund_info=" set hive.exec.dynamic.partition.mode=nonstrict; set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; insert overwrite table ${APP}.dwd_order_refund_info partition(dt) select ri.id, ri.user_id, ri.order_id, ri.sku_id, oi.province_id, ri.refund_type, ri.refund_num, ri.refund_amount, ri.refund_reason_type, ri.create_time, date_format(ri.create_time,'yyyy-MM-dd') from ( select * from ${APP}.ods_order_refund_info where dt='$do_date' )ri left join ( select id,province_id from ${APP}.ods_order_info where dt='$do_date' )oi on ri.order_id=oi.id;" dwd_refund_payment=" set hive.exec.dynamic.partition.mode=nonstrict; set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; insert overwrite table ${APP}.dwd_refund_payment partition(dt) select rp.id, user_id, order_id, sku_id, province_id, trade_no, out_trade_no, payment_type, refund_amount, refund_status, create_time, callback_time, nvl(date_format(callback_time,'yyyy-MM-dd'),'9999-99-99') from ( select id, out_trade_no, order_id, sku_id, payment_type, trade_no, refund_amount, refund_status, create_time, callback_time from ${APP}.ods_refund_payment where dt='$do_date' )rp left join ( select id, user_id, province_id from ${APP}.ods_order_info where dt='$do_date' )oi on rp.order_id=oi.id;" case $1 in dwd_order_info ) hive -e "$dwd_order_info" ;; dwd_order_detail ) hive -e "$dwd_order_detail" ;; dwd_payment_info ) hive -e "$dwd_payment_info" ;; dwd_cart_info ) hive -e "$dwd_cart_info" ;; dwd_comment_info ) hive -e "$dwd_comment_info" ;; dwd_favor_info ) hive -e "$dwd_favor_info" ;; dwd_coupon_use ) hive -e "$dwd_coupon_use" ;; dwd_order_refund_info ) hive -e "$dwd_order_refund_info" ;; dwd_refund_payment ) hive -e "$dwd_refund_payment" ;; all ) hive -e "$dwd_order_info$dwd_order_detail$dwd_payment_info$dwd_cart_info$dwd_comment_info$dwd_favor_info$dwd_coupon_use$dwd_order_refund_info$dwd_refund_payment" ;; esac- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
- 229
- 230
- 231
- 232
- 233
- 234
- 235
- 236
- 237
- 238
- 239
- 240
- 241
- 242
- 243
- 244
- 245
- 246
- 247
- 248
- 249
- 250
- 251
- 252
- 253
- 254
- 255
- 256
- 257
- 258
- 259
- 260
- 261
- 262
- 263
- 264
- 265
- 266
- 267
- 268
- 269
- 270
- 271
- 272
- 273
- 274
- 275
- 276
- 277
- 278
- 279
- 280
- 281
- 282
- 283
- 284
- 285
- 286
- 287
- 288
- 289
- 290
- 291
- 292
- 293
- 294
- 295
- 296
- 297
- 298
- 299
- 300
- 301
- 302
- 303
- 304
- 305
- 306
- 307
- 308
- 309
- 310
- 311
- 312
- 313
- 314
- 315
- 316
- 317
（2）增加执行权限
[lyh@hadoop102 bin]$ chmod +x ods_to_dwd_db_init.sh- 1
2）脚本使用
（1）执行脚本[lyh@hadoop102 bin]$ ods_to_dwd_db_init.sh all 2020-06-14- 1
（2）查看数据是否导入成功
（2）DWD层业务数据每日装载脚本
1）编写脚本
（1）在/home/lyh/bin目录下创建脚本ods_to_dwd_db.sh[lyh@hadoop102 bin]$ vim ods_to_dwd_db.sh- 1
在脚本中填写如下内容
#!/bin/bash APP=gmall # 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天 if [ -n "$2" ] ;then do_date=$2 else do_date=`date -d "-1 day" +%F` fi # 假设某累积型快照事实表，某天所有的业务记录全部完成，则会导致9999-99-99分区的数据未被覆盖，从而导致数据重复，该函数根据9999-99-99分区的数据的末次修改时间判断其是否被覆盖了，如果未被覆盖，就手动清理 clear_data(){ current_date=`date +%F` current_date_timestamp=`date -d "$current_date" +%s` last_modified_date=`hadoop fs -ls /warehouse/gmall/dwd/$1 | grep '9999-99-99' | awk '{print $6}'` last_modified_date_timestamp=`date -d "$last_modified_date" +%s` if [[ $last_modified_date_timestamp -lt $current_date_timestamp ]]; then echo "clear table $1 partition(dt=9999-99-99)" hadoop fs -rm -r -f /warehouse/gmall/dwd/$1/dt=9999-99-99/* fi } dwd_order_info=" set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; set hive.exec.dynamic.partition.mode=nonstrict; insert overwrite table ${APP}.dwd_order_info partition(dt) select nvl(new.id,old.id), nvl(new.order_status,old.order_status), nvl(new.user_id,old.user_id), nvl(new.province_id,old.province_id), nvl(new.payment_way,old.payment_way), nvl(new.delivery_address,old.delivery_address), nvl(new.out_trade_no,old.out_trade_no), nvl(new.tracking_no,old.tracking_no), nvl(new.create_time,old.create_time), nvl(new.payment_time,old.payment_time), nvl(new.cancel_time,old.cancel_time), nvl(new.finish_time,old.finish_time), nvl(new.refund_time,old.refund_time), nvl(new.refund_finish_time,old.refund_finish_time), nvl(new.expire_time,old.expire_time), nvl(new.feight_fee,old.feight_fee), nvl(new.feight_fee_reduce,old.feight_fee_reduce), nvl(new.activity_reduce_amount,old.activity_reduce_amount), nvl(new.coupon_reduce_amount,old.coupon_reduce_amount), nvl(new.original_amount,old.original_amount), nvl(new.final_amount,old.final_amount), case when new.cancel_time is not null then date_format(new.cancel_time,'yyyy-MM-dd') when new.finish_time is not null and date_add(date_format(new.finish_time,'yyyy-MM-dd'),7)='$do_date' and new.refund_time is null then '$do_date' when new.refund_finish_time is not null then date_format(new.refund_finish_time,'yyyy-MM-dd') when new.expire_time is not null then date_format(new.expire_time,'yyyy-MM-dd') else '9999-99-99' end from ( select id, order_status, user_id, province_id, payment_way, delivery_address, out_trade_no, tracking_no, create_time, payment_time, cancel_time, finish_time, refund_time, refund_finish_time, expire_time, feight_fee, feight_fee_reduce, activity_reduce_amount, coupon_reduce_amount, original_amount, final_amount from ${APP}.dwd_order_info where dt='9999-99-99' )old full outer join ( select oi.id, oi.order_status, oi.user_id, oi.province_id, oi.payment_way, oi.delivery_address, oi.out_trade_no, oi.tracking_no, oi.create_time, times.ts['1002'] payment_time, times.ts['1003'] cancel_time, times.ts['1004'] finish_time, times.ts['1005'] refund_time, times.ts['1006'] refund_finish_time, oi.expire_time, feight_fee, feight_fee_reduce, activity_reduce_amount, coupon_reduce_amount, original_amount, final_amount from ( select * from ${APP}.ods_order_info where dt='$do_date' )oi left join ( select order_id, str_to_map(concat_ws(',',collect_set(concat(order_status,'=',operate_time))),',','=') ts from ${APP}.ods_order_status_log where dt='$do_date' group by order_id )times on oi.id=times.order_id )new on old.id=new.id;" dwd_order_detail=" set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; insert overwrite table ${APP}.dwd_order_detail partition(dt='$do_date') select od.id, od.order_id, oi.user_id, od.sku_id, oi.province_id, oda.activity_id, oda.activity_rule_id, odc.coupon_id, od.create_time, od.source_type, od.source_id, od.sku_num, od.order_price*od.sku_num, od.split_activity_amount, od.split_coupon_amount, od.split_final_amount from ( select * from ${APP}.ods_order_detail where dt='$do_date' )od left join ( select id, user_id, province_id from ${APP}.ods_order_info where dt='$do_date' )oi on od.order_id=oi.id left join ( select order_detail_id, activity_id, activity_rule_id from ${APP}.ods_order_detail_activity where dt='$do_date' )oda on od.id=oda.order_detail_id left join ( select order_detail_id, coupon_id from ${APP}.ods_order_detail_coupon where dt='$do_date' )odc on od.id=odc.order_detail_id;" dwd_payment_info=" set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; set hive.exec.dynamic.partition.mode=nonstrict; insert overwrite table ${APP}.dwd_payment_info partition(dt) select nvl(new.id,old.id), nvl(new.order_id,old.order_id), nvl(new.user_id,old.user_id), nvl(new.province_id,old.province_id), nvl(new.trade_no,old.trade_no), nvl(new.out_trade_no,old.out_trade_no), nvl(new.payment_type,old.payment_type), nvl(new.payment_amount,old.payment_amount), nvl(new.payment_status,old.payment_status), nvl(new.create_time,old.create_time), nvl(new.callback_time,old.callback_time), nvl(date_format(nvl(new.callback_time,old.callback_time),'yyyy-MM-dd'),'9999-99-99') from ( select id, order_id, user_id, province_id, trade_no, out_trade_no, payment_type, payment_amount, payment_status, create_time, callback_time from ${APP}.dwd_payment_info where dt = '9999-99-99' )old full outer join ( select pi.id, pi.out_trade_no, pi.order_id, pi.user_id, oi.province_id, pi.payment_type, pi.trade_no, pi.payment_amount, pi.payment_status, pi.create_time, pi.callback_time from ( select * from ${APP}.ods_payment_info where dt='$do_date' )pi left join ( select id,province_id from ${APP}.ods_order_info where dt='$do_date' )oi on pi.order_id=oi.id )new on old.id=new.id;" dwd_cart_info=" set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; insert overwrite table ${APP}.dwd_cart_info partition(dt='$do_date') select id, user_id, sku_id, source_type, source_id, cart_price, is_ordered, create_time, operate_time, order_time, sku_num from ${APP}.ods_cart_info where dt='$do_date';" dwd_comment_info=" set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; insert overwrite table ${APP}.dwd_comment_info partition(dt='$do_date') select id, user_id, sku_id, spu_id, order_id, appraise, create_time from ${APP}.ods_comment_info where dt='$do_date';" dwd_favor_info=" set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; insert overwrite table ${APP}.dwd_favor_info partition(dt='$do_date') select id, user_id, sku_id, spu_id, is_cancel, create_time, cancel_time from ${APP}.ods_favor_info where dt='$do_date';" dwd_coupon_use=" set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; set hive.exec.dynamic.partition.mode=nonstrict; insert overwrite table ${APP}.dwd_coupon_use partition(dt) select nvl(new.id,old.id), nvl(new.coupon_id,old.coupon_id), nvl(new.user_id,old.user_id), nvl(new.order_id,old.order_id), nvl(new.coupon_status,old.coupon_status), nvl(new.get_time,old.get_time), nvl(new.using_time,old.using_time), nvl(new.used_time,old.used_time), nvl(new.expire_time,old.expire_time), coalesce(date_format(nvl(new.used_time,old.used_time),'yyyy-MM-dd'),date_format(nvl(new.expire_time,old.expire_time),'yyyy-MM-dd'),'9999-99-99') from ( select id, coupon_id, user_id, order_id, coupon_status, get_time, using_time, used_time, expire_time from ${APP}.dwd_coupon_use where dt='9999-99-99' )old full outer join ( select id, coupon_id, user_id, order_id, coupon_status, get_time, using_time, used_time, expire_time from ${APP}.ods_coupon_use where dt='$do_date' )new on old.id=new.id;" dwd_order_refund_info=" set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; insert overwrite table ${APP}.dwd_order_refund_info partition(dt='$do_date') select ri.id, ri.user_id, ri.order_id, ri.sku_id, oi.province_id, ri.refund_type, ri.refund_num, ri.refund_amount, ri.refund_reason_type, ri.create_time from ( select * from ${APP}.ods_order_refund_info where dt='$do_date' )ri left join ( select id,province_id from ${APP}.ods_order_info where dt='$do_date' )oi on ri.order_id=oi.id;" dwd_refund_payment=" set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; set hive.exec.dynamic.partition.mode=nonstrict; insert overwrite table ${APP}.dwd_refund_payment partition(dt) select nvl(new.id,old.id), nvl(new.user_id,old.user_id), nvl(new.order_id,old.order_id), nvl(new.sku_id,old.sku_id), nvl(new.province_id,old.province_id), nvl(new.trade_no,old.trade_no), nvl(new.out_trade_no,old.out_trade_no), nvl(new.payment_type,old.payment_type), nvl(new.refund_amount,old.refund_amount), nvl(new.refund_status,old.refund_status), nvl(new.create_time,old.create_time), nvl(new.callback_time,old.callback_time), nvl(date_format(nvl(new.callback_time,old.callback_time),'yyyy-MM-dd'),'9999-99-99') from ( select id, user_id, order_id, sku_id, province_id, trade_no, out_trade_no, payment_type, refund_amount, refund_status, create_time, callback_time from ${APP}.dwd_refund_payment where dt='9999-99-99' )old full outer join ( select rp.id, user_id, order_id, sku_id, province_id, trade_no, out_trade_no, payment_type, refund_amount, refund_status, create_time, callback_time from ( select id, out_trade_no, order_id, sku_id, payment_type, trade_no, refund_amount, refund_status, create_time, callback_time from ${APP}.ods_refund_payment where dt='$do_date' )rp left join ( select id, user_id, province_id from ${APP}.ods_order_info where dt='$do_date' )oi on rp.order_id=oi.id )new on old.id=new.id;" case $1 in dwd_order_info ) hive -e "$dwd_order_info" clear_data dwd_order_info ;; dwd_order_detail ) hive -e "$dwd_order_detail" ;; dwd_payment_info ) hive -e "$dwd_payment_info" clear_data dwd_payment_info ;; dwd_cart_info ) hive -e "$dwd_cart_info" ;; dwd_comment_info ) hive -e "$dwd_comment_info" ;; dwd_favor_info ) hive -e "$dwd_favor_info" ;; dwd_coupon_use ) hive -e "$dwd_coupon_use" clear_data dwd_coupon_use ;; dwd_order_refund_info ) hive -e "$dwd_order_refund_info" ;; dwd_refund_payment ) hive -e "$dwd_refund_payment" clear_data dwd_refund_payment ;; all ) hive -e "$dwd_order_info$dwd_order_detail$dwd_payment_info$dwd_cart_info$dwd_comment_info$dwd_favor_info$dwd_coupon_use$dwd_order_refund_info$dwd_refund_payment" clear_data dwd_order_info clear_data dwd_payment_info clear_data dwd_coupon_use clear_data dwd_refund_payment ;; esac- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
- 229
- 230
- 231
- 232
- 233
- 234
- 235
- 236
- 237
- 238
- 239
- 240
- 241
- 242
- 243
- 244
- 245
- 246
- 247
- 248
- 249
- 250
- 251
- 252
- 253
- 254
- 255
- 256
- 257
- 258
- 259
- 260
- 261
- 262
- 263
- 264
- 265
- 266
- 267
- 268
- 269
- 270
- 271
- 272
- 273
- 274
- 275
- 276
- 277
- 278
- 279
- 280
- 281
- 282
- 283
- 284
- 285
- 286
- 287
- 288
- 289
- 290
- 291
- 292
- 293
- 294
- 295
- 296
- 297
- 298
- 299
- 300
- 301
- 302
- 303
- 304
- 305
- 306
- 307
- 308
- 309
- 310
- 311
- 312
- 313
- 314
- 315
- 316
- 317
- 318
- 319
- 320
- 321
- 322
- 323
- 324
- 325
- 326
- 327
- 328
- 329
- 330
- 331
- 332
- 333
- 334
- 335
- 336
- 337
- 338
- 339
- 340
- 341
- 342
- 343
- 344
- 345
- 346
- 347
- 348
- 349
- 350
- 351
- 352
- 353
- 354
- 355
- 356
- 357
- 358
- 359
- 360
- 361
- 362
- 363
- 364
- 365
- 366
- 367
- 368
- 369
- 370
- 371
- 372
- 373
- 374
- 375
- 376
- 377
- 378
- 379
- 380
- 381
- 382
- 383
- 384
- 385
- 386
- 387
- 388
- 389
- 390
- 391
- 392
- 393
- 394
- 395
- 396
- 397
- 398
- 399
- 400
- 401
- 402
- 403
- 404
- 405
- 406
- 407
- 408
- 409
- 410
- 411
- 412
- 413
- 414
- 415
- 416
- 417
- 418
- 419
- 420
- 421
- 422
- 423
- 424
- 425
- 426
- 427
- 428
- 429
- 430
- 431
- 432
- 433
- 434
- 435
- 436
- 437
- 438
- 439
- 440
- 441
- 442
- 443
- 444
- 445
- 446
- 447
- 448
- 449
- 450
- 451
- 452
- 453
- 454
- 455
- 456
- 457
- 458
- 459
- 460
- 461
- 462
- 463
- 464
- 465
- 466
- 467
- 468
- 469
- 470
- 471
- 472
- 473
- 474
- 475
- 476
- 477
- 478
- 479
- 480
- 481
- 482
- 483
- 484
- 485
- 486
（2）增加脚本执行权限
[lyh@hadoop102 bin]$ chmod 777 ods_to_dwd_db.sh- 1
2）脚本使用
（1）执行脚本[lyh@hadoop102 bin]$ ods_to_dwd_db.sh all 2020-06-14- 1
（2）查看数据是否导入成功
二、数仓搭建-DWS层
1、系统函数
（1）nvl函数
1）基本语法
- NVL（表达式1，表达式2）
- 如果表达式1为空值，NVL返回值为表达式2的值，否则返回表达式1的值。
- 该函数的目的是把一个空值（null）转换成一个实际的值。其表达式的值可以是数字型、字符型和日期型。但是表达式1和表达式2的数据类型必须为同一个类型。
2）案例实操
hive (gmall)> select nvl(1,0); 1 hive (gmall)> select nvl(null,"hello"); hello- 1
- 2
- 3
- 4
（2）日期处理函数
1）date_format函数（根据格式整理日期）
hive (gmall)> select date_format('2020-06-14','yyyy-MM'); 2020-06- 1
- 2
2）date_add函数（加减日期）
hive (gmall)> select date_add('2020-06-14',-1); 2020-06-13 hive (gmall)> select date_add('2020-06-14',1); 2020-06-15- 1
- 2
- 3
- 4
3）next_day函数
（1）取当前天的下一个周一hive (gmall)> select next_day('2020-06-14','MO'); 2020-06-15 说明：星期一到星期日的英文（Monday，Tuesday、Wednesday、Thursday、Friday、Saturday、Sunday）- 1
- 2
- 3
（2）取当前周的周一
hive (gmall)> select date_add(next_day('2020-06-14','MO'),-7); 2020-06-8- 1
- 2
4）last_day函数（求当月最后一天日期）
hive (gmall)> select last_day('2020-06-14'); 2020-06-30- 1
- 2
（3）复杂数据类型定义
1）map结构数据定义
map<string,string>- 1
2）array结构数据定义
array<string>- 1
3）struct结构数据定义
struct<id:int,name:string,age:int>- 1
4）struct和array嵌套定义
array<struct<id:int,name:string,age:int>>- 1
2、DWS层
（1）DWS层首日数据装载脚本
1）编写脚本
（1）在/home/lyh/bin目录下创建脚本dwd_to_dws_init.sh#!/bin/bash APP=gmall if [ -n "$2" ] ;then do_date=$2 else echo "请传入日期参数" exit fi dws_visitor_action_daycount=" insert overwrite table ${APP}.dws_visitor_action_daycount partition(dt='$do_date') select t1.mid_id, t1.brand, t1.model, t1.is_new, t1.channel, t1.os, t1.area_code, t1.version_code, t1.visit_count, t3.page_stats from ( select mid_id, brand, model, if(array_contains(collect_set(is_new),'0'),'0','1') is_new,--ods_page_log中，同一天内，同一设备的is_new字段，可能全部为1，可能全部为0，也可能部分为0，部分为1(卸载重装),故做该处理 collect_set(channel) channel, collect_set(os) os, collect_set(area_code) area_code, collect_set(version_code) version_code, sum(if(last_page_id is null,1,0)) visit_count from ${APP}.dwd_page_log where dt='$do_date' and last_page_id is null group by mid_id,model,brand )t1 join ( select mid_id, brand, model, collect_set(named_struct('page_id',page_id,'page_count',page_count,'during_time',during_time)) page_stats from ( select mid_id, brand, model, page_id, count(*) page_count, sum(during_time) during_time from ${APP}.dwd_page_log where dt='$do_date' group by mid_id,model,brand,page_id )t2 group by mid_id,model,brand )t3 on t1.mid_id=t3.mid_id and t1.brand=t3.brand and t1.model=t3.model; " dws_area_stats_daycount=" set hive.exec.dynamic.partition.mode=nonstrict; with tmp_vu as ( select dt, id province_id, visit_count, login_count, visitor_count, user_count from ( select dt, area_code, count(*) visit_count,--访客访问次数 count(user_id) login_count,--用户访问次数,等价于sum(if(user_id is not null,1,0)) count(distinct(mid_id)) visitor_count,--访客人数 count(distinct(user_id)) user_count--用户人数 from ${APP}.dwd_page_log where last_page_id is null group by dt,area_code )tmp left join ${APP}.dim_base_province area on tmp.area_code=area.area_code ), tmp_order as ( select date_format(create_time,'yyyy-MM-dd') dt, province_id, count(*) order_count, sum(original_amount) order_original_amount, sum(final_amount) order_final_amount from ${APP}.dwd_order_info group by date_format(create_time,'yyyy-MM-dd'),province_id ), tmp_pay as ( select date_format(callback_time,'yyyy-MM-dd') dt, province_id, count(*) payment_count, sum(payment_amount) payment_amount from ${APP}.dwd_payment_info group by date_format(callback_time,'yyyy-MM-dd'),province_id ), tmp_ro as ( select date_format(create_time,'yyyy-MM-dd') dt, province_id, count(*) refund_order_count, sum(refund_amount) refund_order_amount from ${APP}.dwd_order_refund_info group by date_format(create_time,'yyyy-MM-dd'),province_id ), tmp_rp as ( select date_format(callback_time,'yyyy-MM-dd') dt, province_id, count(*) refund_payment_count, sum(refund_amount) refund_payment_amount from ${APP}.dwd_refund_payment group by date_format(callback_time,'yyyy-MM-dd'),province_id ) insert overwrite table ${APP}.dws_area_stats_daycount partition(dt) select province_id, sum(visit_count), sum(login_count), sum(visitor_count), sum(user_count), sum(order_count), sum(order_original_amount), sum(order_final_amount), sum(payment_count), sum(payment_amount), sum(refund_order_count), sum(refund_order_amount), sum(refund_payment_count), sum(refund_payment_amount), dt from ( select dt, province_id, visit_count, login_count, visitor_count, user_count, 0 order_count, 0 order_original_amount, 0 order_final_amount, 0 payment_count, 0 payment_amount, 0 refund_order_count, 0 refund_order_amount, 0 refund_payment_count, 0 refund_payment_amount from tmp_vu union all select dt, province_id, 0 visit_count, 0 login_count, 0 visitor_count, 0 user_count, order_count, order_original_amount, order_final_amount, 0 payment_count, 0 payment_amount, 0 refund_order_count, 0 refund_order_amount, 0 refund_payment_count, 0 refund_payment_amount from tmp_order union all select dt, province_id, 0 visit_count, 0 login_count, 0 visitor_count, 0 user_count, 0 order_count, 0 order_original_amount, 0 order_final_amount, payment_count, payment_amount, 0 refund_order_count, 0 refund_order_amount, 0 refund_payment_count, 0 refund_payment_amount from tmp_pay union all select dt, province_id, 0 visit_count, 0 login_count, 0 visitor_count, 0 user_count, 0 order_count, 0 order_original_amount, 0 order_final_amount, 0 payment_count, 0 payment_amount, refund_order_count, refund_order_amount, 0 refund_payment_count, 0 refund_payment_amount from tmp_ro union all select dt, province_id, 0 visit_count, 0 login_count, 0 visitor_count, 0 user_count, 0 order_count, 0 order_original_amount, 0 order_final_amount, 0 payment_count, 0 payment_amount, 0 refund_order_count, 0 refund_order_amount, refund_payment_count, refund_payment_amount from tmp_rp )t1 group by dt,province_id; " dws_user_action_daycount=" set hive.exec.dynamic.partition.mode=nonstrict; with tmp_login as ( select dt, user_id, count(*) login_count from ${APP}.dwd_page_log where user_id is not null and last_page_id is null group by dt,user_id ), tmp_cf as ( select dt, user_id, sum(if(action_id='cart_add',1,0)) cart_count, sum(if(action_id='favor_add',1,0)) favor_count from ${APP}.dwd_action_log where user_id is not null and action_id in ('cart_add','favor_add') group by dt,user_id ), tmp_order as ( select date_format(create_time,'yyyy-MM-dd') dt, user_id, count(*) order_count, sum(if(activity_reduce_amount>0,1,0)) order_activity_count, sum(if(coupon_reduce_amount>0,1,0)) order_coupon_count, sum(activity_reduce_amount) order_activity_reduce_amount, sum(coupon_reduce_amount) order_coupon_reduce_amount, sum(original_amount) order_original_amount, sum(final_amount) order_final_amount from ${APP}.dwd_order_info group by date_format(create_time,'yyyy-MM-dd'),user_id ), tmp_pay as ( select date_format(callback_time,'yyyy-MM-dd') dt, user_id, count(*) payment_count, sum(payment_amount) payment_amount from ${APP}.dwd_payment_info group by date_format(callback_time,'yyyy-MM-dd'),user_id ), tmp_ri as ( select date_format(create_time,'yyyy-MM-dd') dt, user_id, count(*) refund_order_count, sum(refund_num) refund_order_num, sum(refund_amount) refund_order_amount from ${APP}.dwd_order_refund_info group by date_format(create_time,'yyyy-MM-dd'),user_id ), tmp_rp as ( select date_format(callback_time,'yyyy-MM-dd') dt, rp.user_id, count(*) refund_payment_count, sum(ri.refund_num) refund_payment_num, sum(rp.refund_amount) refund_payment_amount from ( select user_id, order_id, sku_id, refund_amount, callback_time from ${APP}.dwd_refund_payment )rp left join ( select user_id, order_id, sku_id, refund_num from ${APP}.dwd_order_refund_info )ri on rp.order_id=ri.order_id and rp.sku_id=rp.sku_id group by date_format(callback_time,'yyyy-MM-dd'),rp.user_id ), tmp_coupon as ( select coalesce(coupon_get.dt,coupon_using.dt,coupon_used.dt) dt, coalesce(coupon_get.user_id,coupon_using.user_id,coupon_used.user_id) user_id, nvl(coupon_get_count,0) coupon_get_count, nvl(coupon_using_count,0) coupon_using_count, nvl(coupon_used_count,0) coupon_used_count from ( select date_format(get_time,'yyyy-MM-dd') dt, user_id, count(*) coupon_get_count from ${APP}.dwd_coupon_use where get_time is not null group by user_id,date_format(get_time,'yyyy-MM-dd') )coupon_get full outer join ( select date_format(using_time,'yyyy-MM-dd') dt, user_id, count(*) coupon_using_count from ${APP}.dwd_coupon_use where using_time is not null group by user_id,date_format(using_time,'yyyy-MM-dd') )coupon_using on coupon_get.dt=coupon_using.dt and coupon_get.user_id=coupon_using.user_id full outer join ( select date_format(used_time,'yyyy-MM-dd') dt, user_id, count(*) coupon_used_count from ${APP}.dwd_coupon_use where used_time is not null group by user_id,date_format(used_time,'yyyy-MM-dd') )coupon_used on nvl(coupon_get.dt,coupon_using.dt)=coupon_used.dt and nvl(coupon_get.user_id,coupon_using.user_id)=coupon_used.user_id ), tmp_comment as ( select date_format(create_time,'yyyy-MM-dd') dt, user_id, sum(if(appraise='1201',1,0)) appraise_good_count, sum(if(appraise='1202',1,0)) appraise_mid_count, sum(if(appraise='1203',1,0)) appraise_bad_count, sum(if(appraise='1204',1,0)) appraise_default_count from ${APP}.dwd_comment_info group by date_format(create_time,'yyyy-MM-dd'),user_id ), tmp_od as ( select dt, user_id, collect_set(named_struct('sku_id',sku_id,'sku_num',sku_num,'order_count',order_count,'activity_reduce_amount',activity_reduce_amount,'coupon_reduce_amount',coupon_reduce_amount,'original_amount',original_amount,'final_amount',final_amount)) order_detail_stats from ( select date_format(create_time,'yyyy-MM-dd') dt, user_id, sku_id, sum(sku_num) sku_num, count(*) order_count, cast(sum(split_activity_amount) as decimal(16,2)) activity_reduce_amount, cast(sum(split_coupon_amount) as decimal(16,2)) coupon_reduce_amount, cast(sum(original_amount) as decimal(16,2)) original_amount, cast(sum(split_final_amount) as decimal(16,2)) final_amount from ${APP}.dwd_order_detail group by date_format(create_time,'yyyy-MM-dd'),user_id,sku_id )t1 group by dt,user_id ) insert overwrite table ${APP}.dws_user_action_daycount partition(dt) select coalesce(tmp_login.user_id,tmp_cf.user_id,tmp_order.user_id,tmp_pay.user_id,tmp_ri.user_id,tmp_rp.user_id,tmp_comment.user_id,tmp_coupon.user_id,tmp_od.user_id), nvl(login_count,0), nvl(cart_count,0), nvl(favor_count,0), nvl(order_count,0), nvl(order_activity_count,0), nvl(order_activity_reduce_amount,0), nvl(order_coupon_count,0), nvl(order_coupon_reduce_amount,0), nvl(order_original_amount,0), nvl(order_final_amount,0), nvl(payment_count,0), nvl(payment_amount,0), nvl(refund_order_count,0), nvl(refund_order_num,0), nvl(refund_order_amount,0), nvl(refund_payment_count,0), nvl(refund_payment_num,0), nvl(refund_payment_amount,0), nvl(coupon_get_count,0), nvl(coupon_using_count,0), nvl(coupon_used_count,0), nvl(appraise_good_count,0), nvl(appraise_mid_count,0), nvl(appraise_bad_count,0), nvl(appraise_default_count,0), order_detail_stats, coalesce(tmp_login.dt,tmp_cf.dt,tmp_order.dt,tmp_pay.dt,tmp_ri.dt,tmp_rp.dt,tmp_comment.dt,tmp_coupon.dt,tmp_od.dt) from tmp_login full outer join tmp_cf on tmp_login.user_id=tmp_cf.user_id and tmp_login.dt=tmp_cf.dt full outer join tmp_order on coalesce(tmp_login.user_id,tmp_cf.user_id)=tmp_order.user_id and coalesce(tmp_login.dt,tmp_cf.dt)=tmp_order.dt full outer join tmp_pay on coalesce(tmp_login.user_id,tmp_cf.user_id,tmp_order.user_id)=tmp_pay.user_id and coalesce(tmp_login.dt,tmp_cf.dt,tmp_order.dt)=tmp_pay.dt full outer join tmp_ri on coalesce(tmp_login.user_id,tmp_cf.user_id,tmp_order.user_id,tmp_pay.user_id)=tmp_ri.user_id and coalesce(tmp_login.dt,tmp_cf.dt,tmp_order.dt,tmp_pay.dt)=tmp_ri.dt full outer join tmp_rp on coalesce(tmp_login.user_id,tmp_cf.user_id,tmp_order.user_id,tmp_pay.user_id,tmp_ri.user_id)=tmp_rp.user_id and coalesce(tmp_login.dt,tmp_cf.dt,tmp_order.dt,tmp_pay.dt,tmp_ri.dt)=tmp_rp.dt full outer join tmp_comment on coalesce(tmp_login.user_id,tmp_cf.user_id,tmp_order.user_id,tmp_pay.user_id,tmp_ri.user_id,tmp_rp.user_id)=tmp_comment.user_id and coalesce(tmp_login.dt,tmp_cf.dt,tmp_order.dt,tmp_pay.dt,tmp_ri.dt,tmp_rp.dt)=tmp_comment.dt full outer join tmp_coupon on coalesce(tmp_login.user_id,tmp_cf.user_id,tmp_order.user_id,tmp_pay.user_id,tmp_ri.user_id,tmp_rp.user_id,tmp_comment.user_id)=tmp_coupon.user_id and coalesce(tmp_login.dt,tmp_cf.dt,tmp_order.dt,tmp_pay.dt,tmp_ri.dt,tmp_rp.dt,tmp_comment.dt)=tmp_coupon.dt full outer join tmp_od on coalesce(tmp_login.user_id,tmp_cf.user_id,tmp_order.user_id,tmp_pay.user_id,tmp_ri.user_id,tmp_rp.user_id,tmp_comment.user_id,tmp_coupon.user_id)=tmp_od.user_id and coalesce(tmp_login.dt,tmp_cf.dt,tmp_order.dt,tmp_pay.dt,tmp_ri.dt,tmp_rp.dt,tmp_comment.dt,tmp_coupon.dt)=tmp_od.dt; " dws_activity_info_daycount=" set hive.exec.dynamic.partition.mode=nonstrict; with tmp_order as ( select date_format(create_time,'yyyy-MM-dd') dt, activity_rule_id, activity_id, count(*) order_count, sum(split_activity_amount) order_reduce_amount, sum(original_amount) order_original_amount, sum(split_final_amount) order_final_amount from ${APP}.dwd_order_detail where activity_id is not null group by date_format(create_time,'yyyy-MM-dd'),activity_rule_id,activity_id ), tmp_pay as ( select date_format(callback_time,'yyyy-MM-dd') dt, activity_rule_id, activity_id, count(*) payment_count, sum(split_activity_amount) payment_reduce_amount, sum(split_final_amount) payment_amount from ( select activity_rule_id, activity_id, order_id, split_activity_amount, split_final_amount from ${APP}.dwd_order_detail where activity_id is not null )od join ( select order_id, callback_time from ${APP}.dwd_payment_info )pi on od.order_id=pi.order_id group by date_format(callback_time,'yyyy-MM-dd'),activity_rule_id,activity_id ) insert overwrite table ${APP}.dws_activity_info_daycount partition(dt) select activity_rule_id, activity_id, sum(order_count), sum(order_reduce_amount), sum(order_original_amount), sum(order_final_amount), sum(payment_count), sum(payment_reduce_amount), sum(payment_amount), dt from ( select dt, activity_rule_id, activity_id, order_count, order_reduce_amount, order_original_amount, order_final_amount, 0 payment_count, 0 payment_reduce_amount, 0 payment_amount from tmp_order union all select dt, activity_rule_id, activity_id, 0 order_count, 0 order_reduce_amount, 0 order_original_amount, 0 order_final_amount, payment_count, payment_reduce_amount, payment_amount from tmp_pay )t1 group by dt,activity_rule_id,activity_id;" dws_sku_action_daycount=" set hive.exec.dynamic.partition.mode=nonstrict; with tmp_order as ( select date_format(create_time,'yyyy-MM-dd') dt, sku_id, count(*) order_count, sum(sku_num) order_num, sum(if(split_activity_amount>0,1,0)) order_activity_count, sum(if(split_coupon_amount>0,1,0)) order_coupon_count, sum(split_activity_amount) order_activity_reduce_amount, sum(split_coupon_amount) order_coupon_reduce_amount, sum(original_amount) order_original_amount, sum(split_final_amount) order_final_amount from ${APP}.dwd_order_detail group by date_format(create_time,'yyyy-MM-dd'),sku_id ), tmp_pay as ( select date_format(callback_time,'yyyy-MM-dd') dt, sku_id, count(*) payment_count, sum(sku_num) payment_num, sum(split_final_amount) payment_amount from ${APP}.dwd_order_detail od join ( select order_id, callback_time from ${APP}.dwd_payment_info where callback_time is not null )pi on pi.order_id=od.order_id group by date_format(callback_time,'yyyy-MM-dd'),sku_id ), tmp_ri as ( select date_format(create_time,'yyyy-MM-dd') dt, sku_id, count(*) refund_order_count, sum(refund_num) refund_order_num, sum(refund_amount) refund_order_amount from ${APP}.dwd_order_refund_info group by date_format(create_time,'yyyy-MM-dd'),sku_id ), tmp_rp as ( select date_format(callback_time,'yyyy-MM-dd') dt, rp.sku_id, count(*) refund_payment_count, sum(ri.refund_num) refund_payment_num, sum(refund_amount) refund_payment_amount from ( select order_id, sku_id, refund_amount, callback_time from ${APP}.dwd_refund_payment )rp left join ( select order_id, sku_id, refund_num from ${APP}.dwd_order_refund_info )ri on rp.order_id=ri.order_id and rp.sku_id=ri.sku_id group by date_format(callback_time,'yyyy-MM-dd'),rp.sku_id ), tmp_cf as ( select dt, item sku_id, sum(if(action_id='cart_add',1,0)) cart_count, sum(if(action_id='favor_add',1,0)) favor_count from ${APP}.dwd_action_log where action_id in ('cart_add','favor_add') group by dt,item ), tmp_comment as ( select date_format(create_time,'yyyy-MM-dd') dt, sku_id, sum(if(appraise='1201',1,0)) appraise_good_count, sum(if(appraise='1202',1,0)) appraise_mid_count, sum(if(appraise='1203',1,0)) appraise_bad_count, sum(if(appraise='1204',1,0)) appraise_default_count from ${APP}.dwd_comment_info group by date_format(create_time,'yyyy-MM-dd'),sku_id ) insert overwrite table ${APP}.dws_sku_action_daycount partition(dt) select sku_id, sum(order_count), sum(order_num), sum(order_activity_count), sum(order_coupon_count), sum(order_activity_reduce_amount), sum(order_coupon_reduce_amount), sum(order_original_amount), sum(order_final_amount), sum(payment_count), sum(payment_num), sum(payment_amount), sum(refund_order_count), sum(refund_order_num), sum(refund_order_amount), sum(refund_payment_count), sum(refund_payment_num), sum(refund_payment_amount), sum(cart_count), sum(favor_count), sum(appraise_good_count), sum(appraise_mid_count), sum(appraise_bad_count), sum(appraise_default_count), dt from ( select dt, sku_id, order_count, order_num, order_activity_count, order_coupon_count, order_activity_reduce_amount, order_coupon_reduce_amount, order_original_amount, order_final_amount, 0 payment_count, 0 payment_num, 0 payment_amount, 0 refund_order_count, 0 refund_order_num, 0 refund_order_amount, 0 refund_payment_count, 0 refund_payment_num, 0 refund_payment_amount, 0 cart_count, 0 favor_count, 0 appraise_good_count, 0 appraise_mid_count, 0 appraise_bad_count, 0 appraise_default_count from tmp_order union all select dt, sku_id, 0 order_count, 0 order_num, 0 order_activity_count, 0 order_coupon_count, 0 order_activity_reduce_amount, 0 order_coupon_reduce_amount, 0 order_original_amount, 0 order_final_amount, payment_count, payment_num, payment_amount, 0 refund_order_count, 0 refund_order_num, 0 refund_order_amount, 0 refund_payment_count, 0 refund_payment_num, 0 refund_payment_amount, 0 cart_count, 0 favor_count, 0 appraise_good_count, 0 appraise_mid_count, 0 appraise_bad_count, 0 appraise_default_count from tmp_pay union all select dt, sku_id, 0 order_count, 0 order_num, 0 order_activity_count, 0 order_coupon_count, 0 order_activity_reduce_amount, 0 order_coupon_reduce_amount, 0 order_original_amount, 0 order_final_amount, 0 payment_count, 0 payment_num, 0 payment_amount, refund_order_count, refund_order_num, refund_order_amount, 0 refund_payment_count, 0 refund_payment_num, 0 refund_payment_amount, 0 cart_count, 0 favor_count, 0 appraise_good_count, 0 appraise_mid_count, 0 appraise_bad_count, 0 appraise_default_count from tmp_ri union all select dt, sku_id, 0 order_count, 0 order_num, 0 order_activity_count, 0 order_coupon_count, 0 order_activity_reduce_amount, 0 order_coupon_reduce_amount, 0 order_original_amount, 0 order_final_amount, 0 payment_count, 0 payment_num, 0 payment_amount, 0 refund_order_count, 0 refund_order_num, 0 refund_order_amount, refund_payment_count, refund_payment_num, refund_payment_amount, 0 cart_count, 0 favor_count, 0 appraise_good_count, 0 appraise_mid_count, 0 appraise_bad_count, 0 appraise_default_count from tmp_rp union all select dt, sku_id, 0 order_count, 0 order_num, 0 order_activity_count, 0 order_coupon_count, 0 order_activity_reduce_amount, 0 order_coupon_reduce_amount, 0 order_original_amount, 0 order_final_amount, 0 payment_count, 0 payment_num, 0 payment_amount, 0 refund_order_count, 0 refund_order_num, 0 refund_order_amount, 0 refund_payment_count, 0 refund_payment_num, 0 refund_payment_amount, cart_count, favor_count, 0 appraise_good_count, 0 appraise_mid_count, 0 appraise_bad_count, 0 appraise_default_count from tmp_cf union all select dt, sku_id, 0 order_count, 0 order_num, 0 order_activity_count, 0 order_coupon_count, 0 order_activity_reduce_amount, 0 order_coupon_reduce_amount, 0 order_original_amount, 0 order_final_amount, 0 payment_count, 0 payment_num, 0 payment_amount, 0 refund_order_count, 0 refund_order_num, 0 refund_order_amount, 0 refund_payment_count, 0 refund_payment_num, 0 refund_payment_amount, 0 cart_count, 0 favor_count, appraise_good_count, appraise_mid_count, appraise_bad_count, appraise_default_count from tmp_comment )t1 group by dt,sku_id;" dws_coupon_info_daycount=" set hive.exec.dynamic.partition.mode=nonstrict; with tmp_cu as ( select coalesce(coupon_get.dt,coupon_using.dt,coupon_used.dt,coupon_exprie.dt) dt, coalesce(coupon_get.coupon_id,coupon_using.coupon_id,coupon_used.coupon_id,coupon_exprie.coupon_id) coupon_id, nvl(get_count,0) get_count, nvl(order_count,0) order_count, nvl(payment_count,0) payment_count, nvl(expire_count,0) expire_count from ( select date_format(get_time,'yyyy-MM-dd') dt, coupon_id, count(*) get_count from ${APP}.dwd_coupon_use group by date_format(get_time,'yyyy-MM-dd'),coupon_id )coupon_get full outer join ( select date_format(using_time,'yyyy-MM-dd') dt, coupon_id, count(*) order_count from ${APP}.dwd_coupon_use where using_time is not null group by date_format(using_time,'yyyy-MM-dd'),coupon_id )coupon_using on coupon_get.dt=coupon_using.dt and coupon_get.coupon_id=coupon_using.coupon_id full outer join ( select date_format(used_time,'yyyy-MM-dd') dt, coupon_id, count(*) payment_count from ${APP}.dwd_coupon_use where used_time is not null group by date_format(used_time,'yyyy-MM-dd'),coupon_id )coupon_used on nvl(coupon_get.dt,coupon_using.dt)=coupon_used.dt and nvl(coupon_get.coupon_id,coupon_using.coupon_id)=coupon_used.coupon_id full outer join ( select date_format(expire_time,'yyyy-MM-dd') dt, coupon_id, count(*) expire_count from ${APP}.dwd_coupon_use where expire_time is not null group by date_format(expire_time,'yyyy-MM-dd'),coupon_id )coupon_exprie on coalesce(coupon_get.dt,coupon_using.dt,coupon_used.dt)=coupon_exprie.dt and coalesce(coupon_get.coupon_id,coupon_using.coupon_id,coupon_used.coupon_id)=coupon_exprie.coupon_id ), tmp_order as ( select date_format(create_time,'yyyy-MM-dd') dt, coupon_id, sum(split_coupon_amount) order_reduce_amount, sum(original_amount) order_original_amount, sum(split_final_amount) order_final_amount from ${APP}.dwd_order_detail where coupon_id is not null group by date_format(create_time,'yyyy-MM-dd'),coupon_id ), tmp_pay as ( select date_format(callback_time,'yyyy-MM-dd') dt, coupon_id, sum(split_coupon_amount) payment_reduce_amount, sum(split_final_amount) payment_amount from ( select order_id, coupon_id, split_coupon_amount, split_final_amount from ${APP}.dwd_order_detail where coupon_id is not null )od join ( select order_id, callback_time from ${APP}.dwd_payment_info )pi on od.order_id=pi.order_id group by date_format(callback_time,'yyyy-MM-dd'),coupon_id ) insert overwrite table ${APP}.dws_coupon_info_daycount partition(dt) select coupon_id, sum(get_count), sum(order_count), sum(order_reduce_amount), sum(order_original_amount), sum(order_final_amount), sum(payment_count), sum(payment_reduce_amount), sum(payment_amount), sum(expire_count), dt from ( select dt, coupon_id, get_count, order_count, 0 order_reduce_amount, 0 order_original_amount, 0 order_final_amount, payment_count, 0 payment_reduce_amount, 0 payment_amount, expire_count from tmp_cu union all select dt, coupon_id, 0 get_count, 0 order_count, order_reduce_amount, order_original_amount, order_final_amount, 0 payment_count, 0 payment_reduce_amount, 0 payment_amount, 0 expire_count from tmp_order union all select dt, coupon_id, 0 get_count, 0 order_count, 0 order_reduce_amount, 0 order_original_amount, 0 order_final_amount, 0 payment_count, payment_reduce_amount, payment_amount, 0 expire_count from tmp_pay )t1 group by dt,coupon_id; " case $1 in "dws_visitor_action_daycount" ) hive -e "$dws_visitor_action_daycount" ;; "dws_user_action_daycount" ) hive -e "$dws_user_action_daycount" ;; "dws_activity_info_daycount" ) hive -e "$dws_activity_info_daycount" ;; "dws_area_stats_daycount" ) hive -e "$dws_area_stats_daycount" ;; "dws_sku_action_daycount" ) hive -e "$dws_sku_action_daycount" ;; "dws_coupon_info_daycount" ) hive -e "$dws_coupon_info_daycount" ;; "all" ) hive -e "$dws_visitor_action_daycount$dws_user_action_daycount$dws_activity_info_daycount$dws_area_stats_daycount$dws_sku_action_daycount$dws_coupon_info_daycount" ;; esac- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
- 229
- 230
- 231
- 232
- 233
- 234
- 235
- 236
- 237
- 238
- 239
- 240
- 241
- 242
- 243
- 244
- 245
- 246
- 247
- 248
- 249
- 250
- 251
- 252
- 253
- 254
- 255
- 256
- 257
- 258
- 259
- 260
- 261
- 262
- 263
- 264
- 265
- 266
- 267
- 268
- 269
- 270
- 271
- 272
- 273
- 274
- 275
- 276
- 277
- 278
- 279
- 280
- 281
- 282
- 283
- 284
- 285
- 286
- 287
- 288
- 289
- 290
- 291
- 292
- 293
- 294
- 295
- 296
- 297
- 298
- 299
- 300
- 301
- 302
- 303
- 304
- 305
- 306
- 307
- 308
- 309
- 310
- 311
- 312
- 313
- 314
- 315
- 316
- 317
- 318
- 319
- 320
- 321
- 322
- 323
- 324
- 325
- 326
- 327
- 328
- 329
- 330
- 331
- 332
- 333
- 334
- 335
- 336
- 337
- 338
- 339
- 340
- 341
- 342
- 343
- 344
- 345
- 346
- 347
- 348
- 349
- 350
- 351
- 352
- 353
- 354
- 355
- 356
- 357
- 358
- 359
- 360
- 361
- 362
- 363
- 364
- 365
- 366
- 367
- 368
- 369
- 370
- 371
- 372
- 373
- 374
- 375
- 376
- 377
- 378
- 379
- 380
- 381
- 382
- 383
- 384
- 385
- 386
- 387
- 388
- 389
- 390
- 391
- 392
- 393
- 394
- 395
- 396
- 397
- 398
- 399
- 400
- 401
- 402
- 403
- 404
- 405
- 406
- 407
- 408
- 409
- 410
- 411
- 412
- 413
- 414
- 415
- 416
- 417
- 418
- 419
- 420
- 421
- 422
- 423
- 424
- 425
- 426
- 427
- 428
- 429
- 430
- 431
- 432
- 433
- 434
- 435
- 436
- 437
- 438
- 439
- 440
- 441
- 442
- 443
- 444
- 445
- 446
- 447
- 448
- 449
- 450
- 451
- 452
- 453
- 454
- 455
- 456
- 457
- 458
- 459
- 460
- 461
- 462
- 463
- 464
- 465
- 466
- 467
- 468
- 469
- 470
- 471
- 472
- 473
- 474
- 475
- 476
- 477
- 478
- 479
- 480
- 481
- 482
- 483
- 484
- 485
- 486
- 487
- 488
- 489
- 490
- 491
- 492
- 493
- 494
- 495
- 496
- 497
- 498
- 499
- 500
- 501
- 502
- 503
- 504
- 505
- 506
- 507
- 508
- 509
- 510
- 511
- 512
- 513
- 514
- 515
- 516
- 517
- 518
- 519
- 520
- 521
- 522
- 523
- 524
- 525
- 526
- 527
- 528
- 529
- 530
- 531
- 532
- 533
- 534
- 535
- 536
- 537
- 538
- 539
- 540
- 541
- 542
- 543
- 544
- 545
- 546
- 547
- 548
- 549
- 550
- 551
- 552
- 553
- 554
- 555
- 556
- 557
- 558
- 559
- 560
- 561
- 562
- 563
- 564
- 565
- 566
- 567
- 568
- 569
- 570
- 571
- 572
- 573
- 574
- 575
- 576
- 577
- 578
- 579
- 580
- 581
- 582
- 583
- 584
- 585
- 586
- 587
- 588
- 589
- 590
- 591
- 592
- 593
- 594
- 595
- 596
- 597
- 598
- 599
- 600
- 601
- 602
- 603
- 604
- 605
- 606
- 607
- 608
- 609
- 610
- 611
- 612
- 613
- 614
- 615
- 616
- 617
- 618
- 619
- 620
- 621
- 622
- 623
- 624
- 625
- 626
- 627
- 628
- 629
- 630
- 631
- 632
- 633
- 634
- 635
- 636
- 637
- 638
- 639
- 640
- 641
- 642
- 643
- 644
- 645
- 646
- 647
- 648
- 649
- 650
- 651
- 652
- 653
- 654
- 655
- 656
- 657
- 658
- 659
- 660
- 661
- 662
- 663
- 664
- 665
- 666
- 667
- 668
- 669
- 670
- 671
- 672
- 673
- 674
- 675
- 676
- 677
- 678
- 679
- 680
- 681
- 682
- 683
- 684
- 685
- 686
- 687
- 688
- 689
- 690
- 691
- 692
- 693
- 694
- 695
- 696
- 697
- 698
- 699
- 700
- 701
- 702
- 703
- 704
- 705
- 706
- 707
- 708
- 709
- 710
- 711
- 712
- 713
- 714
- 715
- 716
- 717
- 718
- 719
- 720
- 721
- 722
- 723
- 724
- 725
- 726
- 727
- 728
- 729
- 730
- 731
- 732
- 733
- 734
- 735
- 736
- 737
- 738
- 739
- 740
- 741
- 742
- 743
- 744
- 745
- 746
- 747
- 748
- 749
- 750
- 751
- 752
- 753
- 754
- 755
- 756
- 757
- 758
- 759
- 760
- 761
- 762
- 763
- 764
- 765
- 766
- 767
- 768
- 769
- 770
- 771
- 772
- 773
- 774
- 775
- 776
- 777
- 778
- 779
- 780
- 781
- 782
- 783
- 784
- 785
- 786
- 787
- 788
- 789
- 790
- 791
- 792
- 793
- 794
- 795
- 796
- 797
- 798
- 799
- 800
- 801
- 802
- 803
- 804
- 805
- 806
- 807
- 808
- 809
- 810
- 811
- 812
- 813
- 814
- 815
- 816
- 817
- 818
- 819
- 820
- 821
- 822
- 823
- 824
- 825
- 826
- 827
- 828
- 829
- 830
- 831
- 832
- 833
- 834
- 835
- 836
- 837
- 838
- 839
- 840
- 841
- 842
- 843
- 844
- 845
- 846
- 847
- 848
- 849
- 850
- 851
- 852
- 853
- 854
- 855
- 856
- 857
- 858
- 859
- 860
- 861
- 862
- 863
- 864
- 865
- 866
- 867
- 868
- 869
- 870
- 871
- 872
- 873
- 874
- 875
- 876
- 877
- 878
- 879
- 880
- 881
- 882
- 883
- 884
- 885
- 886
- 887
- 888
- 889
- 890
- 891
- 892
- 893
- 894
- 895
- 896
- 897
- 898
- 899
- 900
- 901
- 902
- 903
- 904
- 905
- 906
- 907
- 908
- 909
- 910
- 911
- 912
- 913
- 914
- 915
- 916
- 917
- 918
- 919
- 920
- 921
- 922
- 923
- 924
- 925
- 926
- 927
- 928
- 929
- 930
- 931
- 932
- 933
- 934
- 935
- 936
- 937
- 938
- 939
- 940
- 941
- 942
- 943
- 944
- 945
- 946
- 947
- 948
- 949
- 950
- 951
- 952
- 953
- 954
- 955
- 956
- 957
- 958
- 959
- 960
- 961
- 962
- 963
- 964
- 965
- 966
- 967
- 968
- 969
- 970
- 971
- 972
- 973
- 974
- 975
- 976
- 977
- 978
- 979
- 980
- 981
- 982
- 983
- 984
- 985
- 986
- 987
- 988
- 989
- 990
- 991
- 992
- 993
- 994
- 995
- 996
- 997
- 998
- 999
- 1000
- 1001
- 1002
- 1003
- 1004
- 1005
- 1006
- 1007
- 1008
- 1009
- 1010
- 1011
- 1012
- 1013
- 1014
- 1015
- 1016
- 1017
- 1018
- 1019
- 1020
- 1021
- 1022
- 1023
- 1024
- 1025
- 1026
- 1027
- 1028
- 1029
- 1030
- 1031
- 1032
- 1033
- 1034
- 1035
- 1036
- 1037
- 1038
- 1039
- 1040
- 1041
- 1042
- 1043
- 1044
- 1045
- 1046
（2）增加执行权限
[lyh@hadoop102 bin]$ chmod +x dwd_to_dws_init.sh- 1
2）脚本使用
（1）执行脚本[lyh@hadoop102 bin]$ dwd_to_dws_init.sh all 2020-06-14- 1
（2）查看数据是否导入成功
（2）DWS层每日数据装载脚本
1）编写脚本
（1）在/home/lyh/bin目录下创建脚本dwd_to_dws.sh#!/bin/bash APP=gmall # 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天 if [ -n "$2" ] ;then do_date=$2 else do_date=`date -d "-1 day" +%F` fi dws_visitor_action_daycount="insert overwrite table ${APP}.dws_visitor_action_daycount partition(dt='$do_date') select t1.mid_id, t1.brand, t1.model, t1.is_new, t1.channel, t1.os, t1.area_code, t1.version_code, t1.visit_count, t3.page_stats from ( select mid_id, brand, model, if(array_contains(collect_set(is_new),'0'),'0','1') is_new,--ods_page_log中，同一天内，同一设备的is_new字段，可能全部为1，可能全部为0，也可能部分为0，部分为1(卸载重装),故做该处理 collect_set(channel) channel, collect_set(os) os, collect_set(area_code) area_code, collect_set(version_code) version_code, sum(if(last_page_id is null,1,0)) visit_count from ${APP}.dwd_page_log where dt='$do_date' and last_page_id is null group by mid_id,model,brand )t1 join ( select mid_id, brand, model, collect_set(named_struct('page_id',page_id,'page_count',page_count,'during_time',during_time)) page_stats from ( select mid_id, brand, model, page_id, count(*) page_count, sum(during_time) during_time from ${APP}.dwd_page_log where dt='$do_date' group by mid_id,model,brand,page_id )t2 group by mid_id,model,brand )t3 on t1.mid_id=t3.mid_id and t1.brand=t3.brand and t1.model=t3.model;" dws_user_action_daycount=" with tmp_login as ( select user_id, count(*) login_count from ${APP}.dwd_page_log where dt='$do_date' and user_id is not null and last_page_id is null group by user_id ), tmp_cf as ( select user_id, sum(if(action_id='cart_add',1,0)) cart_count, sum(if(action_id='favor_add',1,0)) favor_count from ${APP}.dwd_action_log where dt='$do_date' and user_id is not null and action_id in ('cart_add','favor_add') group by user_id ), tmp_order as ( select user_id, count(*) order_count, sum(if(activity_reduce_amount>0,1,0)) order_activity_count, sum(if(coupon_reduce_amount>0,1,0)) order_coupon_count, sum(activity_reduce_amount) order_activity_reduce_amount, sum(coupon_reduce_amount) order_coupon_reduce_amount, sum(original_amount) order_original_amount, sum(final_amount) order_final_amount from ${APP}.dwd_order_info where (dt='$do_date' or dt='9999-99-99') and date_format(create_time,'yyyy-MM-dd')='$do_date' group by user_id ), tmp_pay as ( select user_id, count(*) payment_count, sum(payment_amount) payment_amount from ${APP}.dwd_payment_info where dt='$do_date' group by user_id ), tmp_ri as ( select user_id, count(*) refund_order_count, sum(refund_num) refund_order_num, sum(refund_amount) refund_order_amount from ${APP}.dwd_order_refund_info where dt='$do_date' group by user_id ), tmp_rp as ( select rp.user_id, count(*) refund_payment_count, sum(ri.refund_num) refund_payment_num, sum(rp.refund_amount) refund_payment_amount from ( select user_id, order_id, sku_id, refund_amount from ${APP}.dwd_refund_payment where dt='$do_date' )rp left join ( select user_id, order_id, sku_id, refund_num from ${APP}.dwd_order_refund_info where dt>=date_add('$do_date',-15) )ri on rp.order_id=ri.order_id and rp.sku_id=rp.sku_id group by rp.user_id ), tmp_coupon as ( select user_id, sum(if(date_format(get_time,'yyyy-MM-dd')='$do_date',1,0)) coupon_get_count, sum(if(date_format(using_time,'yyyy-MM-dd')='$do_date',1,0)) coupon_using_count, sum(if(date_format(used_time,'yyyy-MM-dd')='$do_date',1,0)) coupon_used_count from ${APP}.dwd_coupon_use where (dt='$do_date' or dt='9999-99-99') and (date_format(get_time, 'yyyy-MM-dd') = '$do_date' or date_format(using_time,'yyyy-MM-dd')='$do_date' or date_format(used_time,'yyyy-MM-dd')='$do_date') group by user_id ), tmp_comment as ( select user_id, sum(if(appraise='1201',1,0)) appraise_good_count, sum(if(appraise='1202',1,0)) appraise_mid_count, sum(if(appraise='1203',1,0)) appraise_bad_count, sum(if(appraise='1204',1,0)) appraise_default_count from ${APP}.dwd_comment_info where dt='$do_date' group by user_id ), tmp_od as ( select user_id, collect_set(named_struct('sku_id',sku_id,'sku_num',sku_num,'order_count',order_count,'activity_reduce_amount',activity_reduce_amount,'coupon_reduce_amount',coupon_reduce_amount,'original_amount',original_amount,'final_amount',final_amount)) order_detail_stats from ( select user_id, sku_id, sum(sku_num) sku_num, count(*) order_count, cast(sum(split_activity_amount) as decimal(16,2)) activity_reduce_amount, cast(sum(split_coupon_amount) as decimal(16,2)) coupon_reduce_amount, cast(sum(original_amount) as decimal(16,2)) original_amount, cast(sum(split_final_amount) as decimal(16,2)) final_amount from ${APP}.dwd_order_detail where dt='$do_date' group by user_id,sku_id )t1 group by user_id ) insert overwrite table ${APP}.dws_user_action_daycount partition(dt='$do_date') select coalesce(tmp_login.user_id,tmp_cf.user_id,tmp_order.user_id,tmp_pay.user_id,tmp_ri.user_id,tmp_rp.user_id,tmp_comment.user_id,tmp_coupon.user_id,tmp_od.user_id), nvl(login_count,0), nvl(cart_count,0), nvl(favor_count,0), nvl(order_count,0), nvl(order_activity_count,0), nvl(order_activity_reduce_amount,0), nvl(order_coupon_count,0), nvl(order_coupon_reduce_amount,0), nvl(order_original_amount,0), nvl(order_final_amount,0), nvl(payment_count,0), nvl(payment_amount,0), nvl(refund_order_count,0), nvl(refund_order_num,0), nvl(refund_order_amount,0), nvl(refund_payment_count,0), nvl(refund_payment_num,0), nvl(refund_payment_amount,0), nvl(coupon_get_count,0), nvl(coupon_using_count,0), nvl(coupon_used_count,0), nvl(appraise_good_count,0), nvl(appraise_mid_count,0), nvl(appraise_bad_count,0), nvl(appraise_default_count,0), order_detail_stats from tmp_login full outer join tmp_cf on tmp_login.user_id=tmp_cf.user_id full outer join tmp_order on coalesce(tmp_login.user_id,tmp_cf.user_id)=tmp_order.user_id full outer join tmp_pay on coalesce(tmp_login.user_id,tmp_cf.user_id,tmp_order.user_id)=tmp_pay.user_id full outer join tmp_ri on coalesce(tmp_login.user_id,tmp_cf.user_id,tmp_order.user_id,tmp_pay.user_id)=tmp_ri.user_id full outer join tmp_rp on coalesce(tmp_login.user_id,tmp_cf.user_id,tmp_order.user_id,tmp_pay.user_id,tmp_ri.user_id)=tmp_rp.user_id full outer join tmp_comment on coalesce(tmp_login.user_id,tmp_cf.user_id,tmp_order.user_id,tmp_pay.user_id,tmp_ri.user_id,tmp_rp.user_id)=tmp_comment.user_id full outer join tmp_coupon on coalesce(tmp_login.user_id,tmp_cf.user_id,tmp_order.user_id,tmp_pay.user_id,tmp_ri.user_id,tmp_rp.user_id,tmp_comment.user_id)=tmp_coupon.user_id full outer join tmp_od on coalesce(tmp_login.user_id,tmp_cf.user_id,tmp_order.user_id,tmp_pay.user_id,tmp_ri.user_id,tmp_rp.user_id,tmp_comment.user_id,tmp_coupon.user_id)=tmp_od.user_id; " dws_activity_info_daycount=" with tmp_order as ( select activity_rule_id, activity_id, count(*) order_count, sum(split_activity_amount) order_reduce_amount, sum(original_amount) order_original_amount, sum(split_final_amount) order_final_amount from ${APP}.dwd_order_detail where dt='$do_date' and activity_id is not null group by activity_rule_id,activity_id ), tmp_pay as ( select activity_rule_id, activity_id, count(*) payment_count, sum(split_activity_amount) payment_reduce_amount, sum(split_final_amount) payment_amount from ${APP}.dwd_order_detail where (dt='$do_date' or dt=date_add('$do_date',-1)) and activity_id is not null and order_id in ( select order_id from ${APP}.dwd_payment_info where dt='$do_date' ) group by activity_rule_id,activity_id ) insert overwrite table ${APP}.dws_activity_info_daycount partition(dt='$do_date') select activity_rule_id, activity_id, sum(order_count), sum(order_reduce_amount), sum(order_original_amount), sum(order_final_amount), sum(payment_count), sum(payment_reduce_amount), sum(payment_amount) from ( select activity_rule_id, activity_id, order_count, order_reduce_amount, order_original_amount, order_final_amount, 0 payment_count, 0 payment_reduce_amount, 0 payment_amount from tmp_order union all select activity_rule_id, activity_id, 0 order_count, 0 order_reduce_amount, 0 order_original_amount, 0 order_final_amount, payment_count, payment_reduce_amount, payment_amount from tmp_pay )t1 group by activity_rule_id,activity_id;" dws_sku_action_daycount=" with tmp_order as ( select sku_id, count(*) order_count, sum(sku_num) order_num, sum(if(split_activity_amount>0,1,0)) order_activity_count, sum(if(split_coupon_amount>0,1,0)) order_coupon_count, sum(split_activity_amount) order_activity_reduce_amount, sum(split_coupon_amount) order_coupon_reduce_amount, sum(original_amount) order_original_amount, sum(split_final_amount) order_final_amount from ${APP}.dwd_order_detail where dt='$do_date' group by sku_id ), tmp_pay as ( select sku_id, count(*) payment_count, sum(sku_num) payment_num, sum(split_final_amount) payment_amount from ${APP}.dwd_order_detail where (dt='$do_date' or dt=date_add('$do_date',-1)) and order_id in ( select order_id from ${APP}.dwd_payment_info where dt='$do_date' ) group by sku_id ), tmp_ri as ( select sku_id, count(*) refund_order_count, sum(refund_num) refund_order_num, sum(refund_amount) refund_order_amount from ${APP}.dwd_order_refund_info where dt='$do_date' group by sku_id ), tmp_rp as ( select rp.sku_id, count(*) refund_payment_count, sum(ri.refund_num) refund_payment_num, sum(refund_amount) refund_payment_amount from ( select order_id, sku_id, refund_amount from ${APP}.dwd_refund_payment where dt='$do_date' )rp left join ( select order_id, sku_id, refund_num from ${APP}.dwd_order_refund_info where dt>=date_add('$do_date',-15) )ri on rp.order_id=ri.order_id and rp.sku_id=ri.sku_id group by rp.sku_id ), tmp_cf as ( select item sku_id, sum(if(action_id='cart_add',1,0)) cart_count, sum(if(action_id='favor_add',1,0)) favor_count from ${APP}.dwd_action_log where dt='$do_date' and action_id in ('cart_add','favor_add') group by item ), tmp_comment as ( select sku_id, sum(if(appraise='1201',1,0)) appraise_good_count, sum(if(appraise='1202',1,0)) appraise_mid_count, sum(if(appraise='1203',1,0)) appraise_bad_count, sum(if(appraise='1204',1,0)) appraise_default_count from ${APP}.dwd_comment_info where dt='$do_date' group by sku_id ) insert overwrite table ${APP}.dws_sku_action_daycount partition(dt='$do_date') select sku_id, sum(order_count), sum(order_num), sum(order_activity_count), sum(order_coupon_count), sum(order_activity_reduce_amount), sum(order_coupon_reduce_amount), sum(order_original_amount), sum(order_final_amount), sum(payment_count), sum(payment_num), sum(payment_amount), sum(refund_order_count), sum(refund_order_num), sum(refund_order_amount), sum(refund_payment_count), sum(refund_payment_num), sum(refund_payment_amount), sum(cart_count), sum(favor_count), sum(appraise_good_count), sum(appraise_mid_count), sum(appraise_bad_count), sum(appraise_default_count) from ( select sku_id, order_count, order_num, order_activity_count, order_coupon_count, order_activity_reduce_amount, order_coupon_reduce_amount, order_original_amount, order_final_amount, 0 payment_count, 0 payment_num, 0 payment_amount, 0 refund_order_count, 0 refund_order_num, 0 refund_order_amount, 0 refund_payment_count, 0 refund_payment_num, 0 refund_payment_amount, 0 cart_count, 0 favor_count, 0 appraise_good_count, 0 appraise_mid_count, 0 appraise_bad_count, 0 appraise_default_count from tmp_order union all select sku_id, 0 order_count, 0 order_num, 0 order_activity_count, 0 order_coupon_count, 0 order_activity_reduce_amount, 0 order_coupon_reduce_amount, 0 order_original_amount, 0 order_final_amount, payment_count, payment_num, payment_amount, 0 refund_order_count, 0 refund_order_num, 0 refund_order_amount, 0 refund_payment_count, 0 refund_payment_num, 0 refund_payment_amount, 0 cart_count, 0 favor_count, 0 appraise_good_count, 0 appraise_mid_count, 0 appraise_bad_count, 0 appraise_default_count from tmp_pay union all select sku_id, 0 order_count, 0 order_num, 0 order_activity_count, 0 order_coupon_count, 0 order_activity_reduce_amount, 0 order_coupon_reduce_amount, 0 order_original_amount, 0 order_final_amount, 0 payment_count, 0 payment_num, 0 payment_amount, refund_order_count, refund_order_num, refund_order_amount, 0 refund_payment_count, 0 refund_payment_num, 0 refund_payment_amount, 0 cart_count, 0 favor_count, 0 appraise_good_count, 0 appraise_mid_count, 0 appraise_bad_count, 0 appraise_default_count from tmp_ri union all select sku_id, 0 order_count, 0 order_num, 0 order_activity_count, 0 order_coupon_count, 0 order_activity_reduce_amount, 0 order_coupon_reduce_amount, 0 order_original_amount, 0 order_final_amount, 0 payment_count, 0 payment_num, 0 payment_amount, 0 refund_order_count, 0 refund_order_num, 0 refund_order_amount, refund_payment_count, refund_payment_num, refund_payment_amount, 0 cart_count, 0 favor_count, 0 appraise_good_count, 0 appraise_mid_count, 0 appraise_bad_count, 0 appraise_default_count from tmp_rp union all select sku_id, 0 order_count, 0 order_num, 0 order_activity_count, 0 order_coupon_count, 0 order_activity_reduce_amount, 0 order_coupon_reduce_amount, 0 order_original_amount, 0 order_final_amount, 0 payment_count, 0 payment_num, 0 payment_amount, 0 refund_order_count, 0 refund_order_num, 0 refund_order_amount, 0 refund_payment_count, 0 refund_payment_num, 0 refund_payment_amount, cart_count, favor_count, 0 appraise_good_count, 0 appraise_mid_count, 0 appraise_bad_count, 0 appraise_default_count from tmp_cf union all select sku_id, 0 order_count, 0 order_num, 0 order_activity_count, 0 order_coupon_count, 0 order_activity_reduce_amount, 0 order_coupon_reduce_amount, 0 order_original_amount, 0 order_final_amount, 0 payment_count, 0 payment_num, 0 payment_amount, 0 refund_order_count, 0 refund_order_num, 0 refund_order_amount, 0 refund_payment_count, 0 refund_payment_num, 0 refund_payment_amount, 0 cart_count, 0 favor_count, appraise_good_count, appraise_mid_count, appraise_bad_count, appraise_default_count from tmp_comment )t1 group by sku_id;" dws_coupon_info_daycount=" with tmp_cu as ( select coupon_id, sum(if(date_format(get_time,'yyyy-MM-dd')='$do_date',1,0)) get_count, sum(if(date_format(using_time,'yyyy-MM-dd')='$do_date',1,0)) order_count, sum(if(date_format(used_time,'yyyy-MM-dd')='$do_date',1,0)) payment_count, sum(if(date_format(expire_time,'yyyy-MM-dd')='$do_date',1,0)) expire_count from ${APP}.dwd_coupon_use where dt='9999-99-99' or dt='$do_date' group by coupon_id ), tmp_order as ( select coupon_id, sum(split_coupon_amount) order_reduce_amount, sum(original_amount) order_original_amount, sum(split_final_amount) order_final_amount from ${APP}.dwd_order_detail where dt='$do_date' and coupon_id is not null group by coupon_id ), tmp_pay as ( select coupon_id, sum(split_coupon_amount) payment_reduce_amount, sum(split_final_amount) payment_amount from ${APP}.dwd_order_detail where (dt='$do_date' or dt=date_add('$do_date',-1)) and coupon_id is not null and order_id in ( select order_id from ${APP}.dwd_payment_info where dt='$do_date' ) group by coupon_id ) insert overwrite table ${APP}.dws_coupon_info_daycount partition(dt='$do_date') select coupon_id, sum(get_count), sum(order_count), sum(order_reduce_amount), sum(order_original_amount), sum(order_final_amount), sum(payment_count), sum(payment_reduce_amount), sum(payment_amount), sum(expire_count) from ( select coupon_id, get_count, order_count, 0 order_reduce_amount, 0 order_original_amount, 0 order_final_amount, payment_count, 0 payment_reduce_amount, 0 payment_amount, expire_count from tmp_cu union all select coupon_id, 0 get_count, 0 order_count, order_reduce_amount, order_original_amount, order_final_amount, 0 payment_count, 0 payment_reduce_amount, 0 payment_amount, 0 expire_count from tmp_order union all select coupon_id, 0 get_count, 0 order_count, 0 order_reduce_amount, 0 order_original_amount, 0 order_final_amount, 0 payment_count, payment_reduce_amount, payment_amount, 0 expire_count from tmp_pay )t1 group by coupon_id;" dws_area_stats_daycount=" with tmp_vu as ( select id province_id, visit_count, login_count, visitor_count, user_count from ( select area_code, count(*) visit_count,--访客访问次数 count(user_id) login_count,--用户访问次数,等价于sum(if(user_id is not null,1,0)) count(distinct(mid_id)) visitor_count,--访客人数 count(distinct(user_id)) user_count--用户人数 from ${APP}.dwd_page_log where dt='$do_date' and last_page_id is null group by area_code )tmp left join ${APP}.dim_base_province area on tmp.area_code=area.area_code ), tmp_order as ( select province_id, count(*) order_count, sum(original_amount) order_original_amount, sum(final_amount) order_final_amount from ${APP}.dwd_order_info where dt='$do_date' or dt='9999-99-99' and date_format(create_time,'yyyy-MM-dd')='$do_date' group by province_id ), tmp_pay as ( select province_id, count(*) payment_count, sum(payment_amount) payment_amount from ${APP}.dwd_payment_info where dt='$do_date' group by province_id ), tmp_ro as ( select province_id, count(*) refund_order_count, sum(refund_amount) refund_order_amount from ${APP}.dwd_order_refund_info where dt='$do_date' group by province_id ), tmp_rp as ( select province_id, count(*) refund_payment_count, sum(refund_amount) refund_payment_amount from ${APP}.dwd_refund_payment where dt='$do_date' group by province_id ) insert overwrite table ${APP}.dws_area_stats_daycount partition(dt='$do_date') select province_id, sum(visit_count), sum(login_count), sum(visitor_count), sum(user_count), sum(order_count), sum(order_original_amount), sum(order_final_amount), sum(payment_count), sum(payment_amount), sum(refund_order_count), sum(refund_order_amount), sum(refund_payment_count), sum(refund_payment_amount) from ( select province_id, visit_count, login_count, visitor_count, user_count, 0 order_count, 0 order_original_amount, 0 order_final_amount, 0 payment_count, 0 payment_amount, 0 refund_order_count, 0 refund_order_amount, 0 refund_payment_count, 0 refund_payment_amount from tmp_vu union all select province_id, 0 visit_count, 0 login_count, 0 visitor_count, 0 user_count, order_count, order_original_amount, order_final_amount, 0 payment_count, 0 payment_amount, 0 refund_order_count, 0 refund_order_amount, 0 refund_payment_count, 0 refund_payment_amount from tmp_order union all select province_id, 0 visit_count, 0 login_count, 0 visitor_count, 0 user_count, 0 order_count, 0 order_original_amount, 0 order_final_amount, payment_count, payment_amount, 0 refund_order_count, 0 refund_order_amount, 0 refund_payment_count, 0 refund_payment_amount from tmp_pay union all select province_id, 0 visit_count, 0 login_count, 0 visitor_count, 0 user_count, 0 order_count, 0 order_original_amount, 0 order_final_amount, 0 payment_count, 0 payment_amount, refund_order_count, refund_order_amount, 0 refund_payment_count, 0 refund_payment_amount from tmp_ro union all select province_id, 0 visit_count, 0 login_count, 0 visitor_count, 0 user_count, 0 order_count, 0 order_original_amount, 0 order_final_amount, 0 payment_count, 0 payment_amount, 0 refund_order_count, 0 refund_order_amount, refund_payment_count, refund_payment_amount from tmp_rp )t1 group by province_id;" case $1 in "dws_visitor_action_daycount" ) hive -e "$dws_visitor_action_daycount" ;; "dws_user_action_daycount" ) hive -e "$dws_user_action_daycount" ;; "dws_activity_info_daycount" ) hive -e "$dws_activity_info_daycount" ;; "dws_area_stats_daycount" ) hive -e "$dws_area_stats_daycount" ;; "dws_sku_action_daycount" ) hive -e "$dws_sku_action_daycount" ;; "dws_coupon_info_daycount" ) hive -e "$dws_coupon_info_daycount" ;; "all" ) hive -e "$dws_visitor_action_daycount$dws_user_action_daycount$dws_activity_info_daycount$dws_area_stats_daycount$dws_sku_action_daycount$dws_coupon_info_daycount" ;; esac- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
- 229
- 230
- 231
- 232
- 233
- 234
- 235
- 236
- 237
- 238
- 239
- 240
- 241
- 242
- 243
- 244
- 245
- 246
- 247
- 248
- 249
- 250
- 251
- 252
- 253
- 254
- 255
- 256
- 257
- 258
- 259
- 260
- 261
- 262
- 263
- 264
- 265
- 266
- 267
- 268
- 269
- 270
- 271
- 272
- 273
- 274
- 275
- 276
- 277
- 278
- 279
- 280
- 281
- 282
- 283
- 284
- 285
- 286
- 287
- 288
- 289
- 290
- 291
- 292
- 293
- 294
- 295
- 296
- 297
- 298
- 299
- 300
- 301
- 302
- 303
- 304
- 305
- 306
- 307
- 308
- 309
- 310
- 311
- 312
- 313
- 314
- 315
- 316
- 317
- 318
- 319
- 320
- 321
- 322
- 323
- 324
- 325
- 326
- 327
- 328
- 329
- 330
- 331
- 332
- 333
- 334
- 335
- 336
- 337
- 338
- 339
- 340
- 341
- 342
- 343
- 344
- 345
- 346
- 347
- 348
- 349
- 350
- 351
- 352
- 353
- 354
- 355
- 356
- 357
- 358
- 359
- 360
- 361
- 362
- 363
- 364
- 365
- 366
- 367
- 368
- 369
- 370
- 371
- 372
- 373
- 374
- 375
- 376
- 377
- 378
- 379
- 380
- 381
- 382
- 383
- 384
- 385
- 386
- 387
- 388
- 389
- 390
- 391
- 392
- 393
- 394
- 395
- 396
- 397
- 398
- 399
- 400
- 401
- 402
- 403
- 404
- 405
- 406
- 407
- 408
- 409
- 410
- 411
- 412
- 413
- 414
- 415
- 416
- 417
- 418
- 419
- 420
- 421
- 422
- 423
- 424
- 425
- 426
- 427
- 428
- 429
- 430
- 431
- 432
- 433
- 434
- 435
- 436
- 437
- 438
- 439
- 440
- 441
- 442
- 443
- 444
- 445
- 446
- 447
- 448
- 449
- 450
- 451
- 452
- 453
- 454
- 455
- 456
- 457
- 458
- 459
- 460
- 461
- 462
- 463
- 464
- 465
- 466
- 467
- 468
- 469
- 470
- 471
- 472
- 473
- 474
- 475
- 476
- 477
- 478
- 479
- 480
- 481
- 482
- 483
- 484
- 485
- 486
- 487
- 488
- 489
- 490
- 491
- 492
- 493
- 494
- 495
- 496
- 497
- 498
- 499
- 500
- 501
- 502
- 503
- 504
- 505
- 506
- 507
- 508
- 509
- 510
- 511
- 512
- 513
- 514
- 515
- 516
- 517
- 518
- 519
- 520
- 521
- 522
- 523
- 524
- 525
- 526
- 527
- 528
- 529
- 530
- 531
- 532
- 533
- 534
- 535
- 536
- 537
- 538
- 539
- 540
- 541
- 542
- 543
- 544
- 545
- 546
- 547
- 548
- 549
- 550
- 551
- 552
- 553
- 554
- 555
- 556
- 557
- 558
- 559
- 560
- 561
- 562
- 563
- 564
- 565
- 566
- 567
- 568
- 569
- 570
- 571
- 572
- 573
- 574
- 575
- 576
- 577
- 578
- 579
- 580
- 581
- 582
- 583
- 584
- 585
- 586
- 587
- 588
- 589
- 590
- 591
- 592
- 593
- 594
- 595
- 596
- 597
- 598
- 599
- 600
- 601
- 602
- 603
- 604
- 605
- 606
- 607
- 608
- 609
- 610
- 611
- 612
- 613
- 614
- 615
- 616
- 617
- 618
- 619
- 620
- 621
- 622
- 623
- 624
- 625
- 626
- 627
- 628
- 629
- 630
- 631
- 632
- 633
- 634
- 635
- 636
- 637
- 638
- 639
- 640
- 641
- 642
- 643
- 644
- 645
- 646
- 647
- 648
- 649
- 650
- 651
- 652
- 653
- 654
- 655
- 656
- 657
- 658
- 659
- 660
- 661
- 662
- 663
- 664
- 665
- 666
- 667
- 668
- 669
- 670
- 671
- 672
- 673
- 674
- 675
- 676
- 677
- 678
- 679
- 680
- 681
- 682
- 683
- 684
- 685
- 686
- 687
- 688
- 689
- 690
- 691
- 692
- 693
- 694
- 695
- 696
- 697
- 698
- 699
- 700
- 701
- 702
- 703
- 704
- 705
- 706
- 707
- 708
- 709
- 710
- 711
- 712
- 713
- 714
- 715
- 716
- 717
- 718
- 719
- 720
- 721
- 722
- 723
- 724
- 725
- 726
- 727
- 728
- 729
- 730
- 731
- 732
- 733
- 734
- 735
- 736
- 737
- 738
- 739
- 740
- 741
- 742
- 743
- 744
- 745
- 746
- 747
- 748
- 749
- 750
- 751
- 752
- 753
- 754
- 755
- 756
- 757
- 758
- 759
- 760
- 761
- 762
- 763
- 764
- 765
- 766
- 767
- 768
- 769
- 770
- 771
- 772
- 773
- 774
- 775
- 776
- 777
- 778
- 779
- 780
- 781
- 782
- 783
- 784
- 785
- 786
- 787
- 788
- 789
- 790
- 791
- 792
- 793
- 794
- 795
- 796
- 797
- 798
- 799
- 800
- 801
- 802
- 803
- 804
- 805
- 806
- 807
- 808
- 809
- 810
- 811
- 812
- 813
- 814
- 815
- 816
- 817
- 818
- 819
- 820
- 821
- 822
- 823
- 824
- 825
- 826
- 827
- 828
- 829
- 830
- 831
- 832
- 833
- 834
- 835
- 836
- 837
- 838
- 839
- 840
- 841
- 842
- 843
- 844
- 845
- 846
- 847
- 848
- 849
- 850
- 851
- 852
- 853
- 854
- 855
- 856
- 857
- 858
- 859
- 860
- 861
- 862
- 863
- 864
- 865
- 866
- 867
- 868
- 869
- 870
- 871
- 872
- 873
- 874
- 875
- 876
- 877
- 878
- 879
- 880
- 881
- 882
- 883
- 884
- 885
- 886
- 887
- 888
- 889
- 890
- 891
- 892
- 893
- 894
- 895
- 896
- 897
- 898
- 899
- 900
- 901
- 902
- 903
- 904
- 905
- 906
- 907
（2）增加执行权限
[lyh@hadoop102 bin]$ chmod +x dwd_to_dws.sh- 1
2）脚本使用
（1）执行脚本[lyh@hadoop102 bin]$ dwd_to_dws.sh all 2020-06-14- 1
（2）查看数据是否导入成功
三、数仓搭建-DWT层
（1）DWT层首日数据导入脚本
1）编写脚本
（1）在/home/lyh/bin目录下创建脚本dws_to_dwt_init.sh[lyh@hadoop102 bin]$ vim dws_to_dwt_init.sh- 1
在脚本中填写如下内容
#!/bin/bash APP=gmall if [ -n "$2" ] ;then do_date=$2 else echo "请传入日期参数" exit fi dwt_visitor_topic=" insert overwrite table ${APP}.dwt_visitor_topic partition(dt='$do_date') select nvl(1d_ago.mid_id,old.mid_id), nvl(1d_ago.brand,old.brand), nvl(1d_ago.model,old.model), nvl(1d_ago.channel,old.channel), nvl(1d_ago.os,old.os), nvl(1d_ago.area_code,old.area_code), nvl(1d_ago.version_code,old.version_code), case when old.mid_id is null and 1d_ago.is_new=1 then '$do_date' when old.mid_id is null and 1d_ago.is_new=0 then '2020-06-13'--无法获取准确的首次登录日期，给定一个数仓搭建日之前的日期 else old.visit_date_first end, if(1d_ago.mid_id is not null,'$do_date',old.visit_date_last), nvl(1d_ago.visit_count,0), if(1d_ago.mid_id is null,0,1), nvl(old.visit_last_7d_count,0)+nvl(1d_ago.visit_count,0)- nvl(7d_ago.visit_count,0), nvl(old.visit_last_7d_day_count,0)+if(1d_ago.mid_id is null,0,1)- if(7d_ago.mid_id is null,0,1), nvl(old.visit_last_30d_count,0)+nvl(1d_ago.visit_count,0)- nvl(30d_ago.visit_count,0), nvl(old.visit_last_30d_day_count,0)+if(1d_ago.mid_id is null,0,1)- if(30d_ago.mid_id is null,0,1), nvl(old.visit_count,0)+nvl(1d_ago.visit_count,0), nvl(old.visit_day_count,0)+if(1d_ago.mid_id is null,0,1) from ( select mid_id, brand, model, channel, os, area_code, version_code, visit_date_first, visit_date_last, visit_last_1d_count, visit_last_1d_day_count, visit_last_7d_count, visit_last_7d_day_count, visit_last_30d_count, visit_last_30d_day_count, visit_count, visit_day_count from ${APP}.dwt_visitor_topic where dt=date_add('$do_date',-1) )old full outer join ( select mid_id, brand, model, is_new, channel, os, area_code, version_code, visit_count from ${APP}.dws_visitor_action_daycount where dt='$do_date' )1d_ago on old.mid_id=1d_ago.mid_id left join ( select mid_id, brand, model, is_new, channel, os, area_code, version_code, visit_count from ${APP}.dws_visitor_action_daycount where dt=date_add('$do_date',-7) )7d_ago on old.mid_id=7d_ago.mid_id left join ( select mid_id, brand, model, is_new, channel, os, area_code, version_code, visit_count from ${APP}.dws_visitor_action_daycount where dt=date_add('$do_date',-30) )30d_ago on old.mid_id=30d_ago.mid_id; " dwt_user_topic=" insert overwrite table ${APP}.dwt_user_topic partition(dt='$do_date') select id, login_date_first,--以用户的创建日期作为首次登录日期 nvl(login_date_last,date_add('$do_date',-1)),--若有历史登录记录，则根据历史记录获取末次登录日期，否则统一指定一个日期 nvl(login_last_1d_count,0), nvl(login_last_1d_day_count,0), nvl(login_last_7d_count,0), nvl(login_last_7d_day_count,0), nvl(login_last_30d_count,0), nvl(login_last_30d_day_count,0), nvl(login_count,0), nvl(login_day_count,0), order_date_first, order_date_last, nvl(order_last_1d_count,0), nvl(order_activity_last_1d_count,0), nvl(order_activity_reduce_last_1d_amount,0), nvl(order_coupon_last_1d_count,0), nvl(order_coupon_reduce_last_1d_amount,0), nvl(order_last_1d_original_amount,0), nvl(order_last_1d_final_amount,0), nvl(order_last_7d_count,0), nvl(order_activity_last_7d_count,0), nvl(order_activity_reduce_last_7d_amount,0), nvl(order_coupon_last_7d_count,0), nvl(order_coupon_reduce_last_7d_amount,0), nvl(order_last_7d_original_amount,0), nvl(order_last_7d_final_amount,0), nvl(order_last_30d_count,0), nvl(order_activity_last_30d_count,0), nvl(order_activity_reduce_last_30d_amount,0), nvl(order_coupon_last_30d_count,0), nvl(order_coupon_reduce_last_30d_amount,0), nvl(order_last_30d_original_amount,0), nvl(order_last_30d_final_amount,0), nvl(order_count,0), nvl(order_activity_count,0), nvl(order_activity_reduce_amount,0), nvl(order_coupon_count,0), nvl(order_coupon_reduce_amount,0), nvl(order_original_amount,0), nvl(order_final_amount,0), payment_date_first, payment_date_last, nvl(payment_last_1d_count,0), nvl(payment_last_1d_amount,0), nvl(payment_last_7d_count,0), nvl(payment_last_7d_amount,0), nvl(payment_last_30d_count,0), nvl(payment_last_30d_amount,0), nvl(payment_count,0), nvl(payment_amount,0), nvl(refund_order_last_1d_count,0), nvl(refund_order_last_1d_num,0), nvl(refund_order_last_1d_amount,0), nvl(refund_order_last_7d_count,0), nvl(refund_order_last_7d_num,0), nvl(refund_order_last_7d_amount,0), nvl(refund_order_last_30d_count,0), nvl(refund_order_last_30d_num,0), nvl(refund_order_last_30d_amount,0), nvl(refund_order_count,0), nvl(refund_order_num,0), nvl(refund_order_amount,0), nvl(refund_payment_last_1d_count,0), nvl(refund_payment_last_1d_num,0), nvl(refund_payment_last_1d_amount,0), nvl(refund_payment_last_7d_count,0), nvl(refund_payment_last_7d_num,0), nvl(refund_payment_last_7d_amount,0), nvl(refund_payment_last_30d_count,0), nvl(refund_payment_last_30d_num,0), nvl(refund_payment_last_30d_amount,0), nvl(refund_payment_count,0), nvl(refund_payment_num,0), nvl(refund_payment_amount,0), nvl(cart_last_1d_count,0), nvl(cart_last_7d_count,0), nvl(cart_last_30d_count,0), nvl(cart_count,0), nvl(favor_last_1d_count,0), nvl(favor_last_7d_count,0), nvl(favor_last_30d_count,0), nvl(favor_count,0), nvl(coupon_last_1d_get_count,0), nvl(coupon_last_1d_using_count,0), nvl(coupon_last_1d_used_count,0), nvl(coupon_last_7d_get_count,0), nvl(coupon_last_7d_using_count,0), nvl(coupon_last_7d_used_count,0), nvl(coupon_last_30d_get_count,0), nvl(coupon_last_30d_using_count,0), nvl(coupon_last_30d_used_count,0), nvl(coupon_get_count,0), nvl(coupon_using_count,0), nvl(coupon_used_count,0), nvl(appraise_last_1d_good_count,0), nvl(appraise_last_1d_mid_count,0), nvl(appraise_last_1d_bad_count,0), nvl(appraise_last_1d_default_count,0), nvl(appraise_last_7d_good_count,0), nvl(appraise_last_7d_mid_count,0), nvl(appraise_last_7d_bad_count,0), nvl(appraise_last_7d_default_count,0), nvl(appraise_last_30d_good_count,0), nvl(appraise_last_30d_mid_count,0), nvl(appraise_last_30d_bad_count,0), nvl(appraise_last_30d_default_count,0), nvl(appraise_good_count,0), nvl(appraise_mid_count,0), nvl(appraise_bad_count,0), nvl(appraise_default_count,0) from ( select id, date_format(create_time,'yyyy-MM-dd') login_date_first from ${APP}.dim_user_info where dt='9999-99-99' )t1 left join ( select user_id user_id, max(dt) login_date_last, sum(if(dt='$do_date',login_count,0)) login_last_1d_count, sum(if(dt='$do_date' and login_count>0,1,0)) login_last_1d_day_count, sum(if(dt>=date_add('$do_date',-6),login_count,0)) login_last_7d_count, sum(if(dt>=date_add('$do_date',-6) and login_count>0,1,0)) login_last_7d_day_count, sum(if(dt>=date_add('$do_date',-29),login_count,0)) login_last_30d_count, sum(if(dt>=date_add('$do_date',-29) and login_count>0,1,0)) login_last_30d_day_count, sum(login_count) login_count, sum(if(login_count>0,1,0)) login_day_count, min(if(order_count>0,dt,null)) order_date_first, max(if(order_count>0,dt,null)) order_date_last, sum(if(dt='$do_date',order_count,0)) order_last_1d_count, sum(if(dt='$do_date',order_activity_count,0)) order_activity_last_1d_count, sum(if(dt='$do_date',order_activity_reduce_amount,0)) order_activity_reduce_last_1d_amount, sum(if(dt='$do_date',order_coupon_count,0)) order_coupon_last_1d_count, sum(if(dt='$do_date',order_coupon_reduce_amount,0)) order_coupon_reduce_last_1d_amount, sum(if(dt='$do_date',order_original_amount,0)) order_last_1d_original_amount, sum(if(dt='$do_date',order_final_amount,0)) order_last_1d_final_amount, sum(if(dt>=date_add('$do_date',-6),order_count,0)) order_last_7d_count, sum(if(dt>=date_add('$do_date',-6),order_activity_count,0)) order_activity_last_7d_count, sum(if(dt>=date_add('$do_date',-6),order_activity_reduce_amount,0)) order_activity_reduce_last_7d_amount, sum(if(dt>=date_add('$do_date',-6),order_coupon_count,0)) order_coupon_last_7d_count, sum(if(dt>=date_add('$do_date',-6),order_coupon_reduce_amount,0)) order_coupon_reduce_last_7d_amount, sum(if(dt>=date_add('$do_date',-6),order_original_amount,0)) order_last_7d_original_amount, sum(if(dt>=date_add('$do_date',-6),order_final_amount,0)) order_last_7d_final_amount, sum(if(dt>=date_add('$do_date',-29),order_count,0)) order_last_30d_count, sum(if(dt>=date_add('$do_date',-29),order_activity_count,0)) order_activity_last_30d_count, sum(if(dt>=date_add('$do_date',-29),order_activity_reduce_amount,0)) order_activity_reduce_last_30d_amount, sum(if(dt>=date_add('$do_date',-29),order_coupon_count,0)) order_coupon_last_30d_count, sum(if(dt>=date_add('$do_date',-29),order_coupon_reduce_amount,0)) order_coupon_reduce_last_30d_amount, sum(if(dt>=date_add('$do_date',-29),order_original_amount,0)) order_last_30d_original_amount, sum(if(dt>=date_add('$do_date',-29),order_final_amount,0)) order_last_30d_final_amount, sum(order_count) order_count, sum(order_activity_count) order_activity_count, sum(order_activity_reduce_amount) order_activity_reduce_amount, sum(order_coupon_count) order_coupon_count, sum(order_coupon_reduce_amount) order_coupon_reduce_amount, sum(order_original_amount) order_original_amount, sum(order_final_amount) order_final_amount, min(if(payment_count>0,dt,null)) payment_date_first, max(if(payment_count>0,dt,null)) payment_date_last, sum(if(dt='$do_date',payment_count,0)) payment_last_1d_count, sum(if(dt='$do_date',payment_amount,0)) payment_last_1d_amount, sum(if(dt>=date_add('$do_date',-6),payment_count,0)) payment_last_7d_count, sum(if(dt>=date_add('$do_date',-6),payment_amount,0)) payment_last_7d_amount, sum(if(dt>=date_add('$do_date',-29),payment_count,0)) payment_last_30d_count, sum(if(dt>=date_add('$do_date',-29),payment_amount,0)) payment_last_30d_amount, sum(payment_count) payment_count, sum(payment_amount) payment_amount, sum(if(dt='$do_date',refund_order_count,0)) refund_order_last_1d_count, sum(if(dt='$do_date',refund_order_num,0)) refund_order_last_1d_num, sum(if(dt='$do_date',refund_order_amount,0)) refund_order_last_1d_amount, sum(if(dt>=date_add('$do_date',-6),refund_order_count,0)) refund_order_last_7d_count, sum(if(dt>=date_add('$do_date',-6),refund_order_num,0)) refund_order_last_7d_num, sum(if(dt>=date_add('$do_date',-6),refund_order_amount,0)) refund_order_last_7d_amount, sum(if(dt>=date_add('$do_date',-29),refund_order_count,0)) refund_order_last_30d_count, sum(if(dt>=date_add('$do_date',-29),refund_order_num,0)) refund_order_last_30d_num, sum(if(dt>=date_add('$do_date',-29),refund_order_amount,0)) refund_order_last_30d_amount, sum(refund_order_count) refund_order_count, sum(refund_order_num) refund_order_num, sum(refund_order_amount) refund_order_amount, sum(if(dt='$do_date',refund_payment_count,0)) refund_payment_last_1d_count, sum(if(dt='$do_date',refund_payment_num,0)) refund_payment_last_1d_num, sum(if(dt='$do_date',refund_payment_amount,0)) refund_payment_last_1d_amount, sum(if(dt>=date_add('$do_date',-6),refund_payment_count,0)) refund_payment_last_7d_count, sum(if(dt>=date_add('$do_date',-6),refund_payment_num,0)) refund_payment_last_7d_num, sum(if(dt>=date_add('$do_date',-6),refund_payment_amount,0)) refund_payment_last_7d_amount, sum(if(dt>=date_add('$do_date',-29),refund_payment_count,0)) refund_payment_last_30d_count, sum(if(dt>=date_add('$do_date',-29),refund_payment_num,0)) refund_payment_last_30d_num, sum(if(dt>=date_add('$do_date',-29),refund_payment_amount,0)) refund_payment_last_30d_amount, sum(refund_payment_count) refund_payment_count, sum(refund_payment_num) refund_payment_num, sum(refund_payment_amount) refund_payment_amount, sum(if(dt='$do_date',cart_count,0)) cart_last_1d_count, sum(if(dt>=date_add('$do_date',-6),cart_count,0)) cart_last_7d_count, sum(if(dt>=date_add('$do_date',-29),cart_count,0)) cart_last_30d_count, sum(cart_count) cart_count, sum(if(dt='$do_date',favor_count,0)) favor_last_1d_count, sum(if(dt>=date_add('$do_date',-6),favor_count,0)) favor_last_7d_count, sum(if(dt>=date_add('$do_date',-29),favor_count,0)) favor_last_30d_count, sum(favor_count) favor_count, sum(if(dt='$do_date',coupon_get_count,0)) coupon_last_1d_get_count, sum(if(dt='$do_date',coupon_using_count,0)) coupon_last_1d_using_count, sum(if(dt='$do_date',coupon_used_count,0)) coupon_last_1d_used_count, sum(if(dt>=date_add('$do_date',-6),coupon_get_count,0)) coupon_last_7d_get_count, sum(if(dt>=date_add('$do_date',-6),coupon_using_count,0)) coupon_last_7d_using_count, sum(if(dt>=date_add('$do_date',-6),coupon_used_count,0)) coupon_last_7d_used_count, sum(if(dt>=date_add('$do_date',-29),coupon_get_count,0)) coupon_last_30d_get_count, sum(if(dt>=date_add('$do_date',-29),coupon_using_count,0)) coupon_last_30d_using_count, sum(if(dt>=date_add('$do_date',-29),coupon_used_count,0)) coupon_last_30d_used_count, sum(coupon_get_count) coupon_get_count, sum(coupon_using_count) coupon_using_count, sum(coupon_used_count) coupon_used_count, sum(if(dt='$do_date',appraise_good_count,0)) appraise_last_1d_good_count, sum(if(dt='$do_date',appraise_mid_count,0)) appraise_last_1d_mid_count, sum(if(dt='$do_date',appraise_bad_count,0)) appraise_last_1d_bad_count, sum(if(dt='$do_date',appraise_default_count,0)) appraise_last_1d_default_count, sum(if(dt>=date_add('$do_date',-6),appraise_good_count,0)) appraise_last_7d_good_count, sum(if(dt>=date_add('$do_date',-6),appraise_mid_count,0)) appraise_last_7d_mid_count, sum(if(dt>=date_add('$do_date',-6),appraise_bad_count,0)) appraise_last_7d_bad_count, sum(if(dt>=date_add('$do_date',-6),appraise_default_count,0)) appraise_last_7d_default_count, sum(if(dt>=date_add('$do_date',-29),appraise_good_count,0)) appraise_last_30d_good_count, sum(if(dt>=date_add('$do_date',-29),appraise_mid_count,0)) appraise_last_30d_mid_count, sum(if(dt>=date_add('$do_date',-29),appraise_bad_count,0)) appraise_last_30d_bad_count, sum(if(dt>=date_add('$do_date',-29),appraise_default_count,0)) appraise_last_30d_default_count, sum(appraise_good_count) appraise_good_count, sum(appraise_mid_count) appraise_mid_count, sum(appraise_bad_count) appraise_bad_count, sum(appraise_default_count) appraise_default_count from ${APP}.dws_user_action_daycount group by user_id )t2 on t1.id=t2.user_id; " dwt_sku_topic=" insert overwrite table ${APP}.dwt_sku_topic partition(dt='$do_date') select id, nvl(order_last_1d_count,0), nvl(order_last_1d_num,0), nvl(order_activity_last_1d_count,0), nvl(order_coupon_last_1d_count,0), nvl(order_activity_reduce_last_1d_amount,0), nvl(order_coupon_reduce_last_1d_amount,0), nvl(order_last_1d_original_amount,0), nvl(order_last_1d_final_amount,0), nvl(order_last_7d_count,0), nvl(order_last_7d_num,0), nvl(order_activity_last_7d_count,0), nvl(order_coupon_last_7d_count,0), nvl(order_activity_reduce_last_7d_amount,0), nvl(order_coupon_reduce_last_7d_amount,0), nvl(order_last_7d_original_amount,0), nvl(order_last_7d_final_amount,0), nvl(order_last_30d_count,0), nvl(order_last_30d_num,0), nvl(order_activity_last_30d_count,0), nvl(order_coupon_last_30d_count,0), nvl(order_activity_reduce_last_30d_amount,0), nvl(order_coupon_reduce_last_30d_amount,0), nvl(order_last_30d_original_amount,0), nvl(order_last_30d_final_amount,0), nvl(order_count,0), nvl(order_num,0), nvl(order_activity_count,0), nvl(order_coupon_count,0), nvl(order_activity_reduce_amount,0), nvl(order_coupon_reduce_amount,0), nvl(order_original_amount,0), nvl(order_final_amount,0), nvl(payment_last_1d_count,0), nvl(payment_last_1d_num,0), nvl(payment_last_1d_amount,0), nvl(payment_last_7d_count,0), nvl(payment_last_7d_num,0), nvl(payment_last_7d_amount,0), nvl(payment_last_30d_count,0), nvl(payment_last_30d_num,0), nvl(payment_last_30d_amount,0), nvl(payment_count,0), nvl(payment_num,0), nvl(payment_amount,0), nvl(refund_order_last_1d_count,0), nvl(refund_order_last_1d_num,0), nvl(refund_order_last_1d_amount,0), nvl(refund_order_last_7d_count,0), nvl(refund_order_last_7d_num,0), nvl(refund_order_last_7d_amount,0), nvl(refund_order_last_30d_count,0), nvl(refund_order_last_30d_num,0), nvl(refund_order_last_30d_amount,0), nvl(refund_order_count,0), nvl(refund_order_num,0), nvl(refund_order_amount,0), nvl(refund_payment_last_1d_count,0), nvl(refund_payment_last_1d_num,0), nvl(refund_payment_last_1d_amount,0), nvl(refund_payment_last_7d_count,0), nvl(refund_payment_last_7d_num,0), nvl(refund_payment_last_7d_amount,0), nvl(refund_payment_last_30d_count,0), nvl(refund_payment_last_30d_num,0), nvl(refund_payment_last_30d_amount,0), nvl(refund_payment_count,0), nvl(refund_payment_num,0), nvl(refund_payment_amount,0), nvl(cart_last_1d_count,0), nvl(cart_last_7d_count,0), nvl(cart_last_30d_count,0), nvl(cart_count,0), nvl(favor_last_1d_count,0), nvl(favor_last_7d_count,0), nvl(favor_last_30d_count,0), nvl(favor_count,0), nvl(appraise_last_1d_good_count,0), nvl(appraise_last_1d_mid_count,0), nvl(appraise_last_1d_bad_count,0), nvl(appraise_last_1d_default_count,0), nvl(appraise_last_7d_good_count,0), nvl(appraise_last_7d_mid_count,0), nvl(appraise_last_7d_bad_count,0), nvl(appraise_last_7d_default_count,0), nvl(appraise_last_30d_good_count,0), nvl(appraise_last_30d_mid_count,0), nvl(appraise_last_30d_bad_count,0), nvl(appraise_last_30d_default_count,0), nvl(appraise_good_count,0), nvl(appraise_mid_count,0), nvl(appraise_bad_count,0), nvl(appraise_default_count,0) from ( select id from ${APP}.dim_sku_info where dt='$do_date' )t1 left join ( select sku_id, sum(if(dt='$do_date',order_count,0)) order_last_1d_count, sum(if(dt='$do_date',order_num,0)) order_last_1d_num, sum(if(dt='$do_date',order_activity_count,0)) order_activity_last_1d_count, sum(if(dt='$do_date',order_coupon_count,0)) order_coupon_last_1d_count, sum(if(dt='$do_date',order_activity_reduce_amount,0)) order_activity_reduce_last_1d_amount, sum(if(dt='$do_date',order_coupon_reduce_amount,0)) order_coupon_reduce_last_1d_amount, sum(if(dt='$do_date',order_original_amount,0)) order_last_1d_original_amount, sum(if(dt='$do_date',order_final_amount,0)) order_last_1d_final_amount, sum(if(dt>=date_add('$do_date',-6),order_count,0)) order_last_7d_count, sum(if(dt>=date_add('$do_date',-6),order_num,0)) order_last_7d_num, sum(if(dt>=date_add('$do_date',-6),order_activity_count,0)) order_activity_last_7d_count, sum(if(dt>=date_add('$do_date',-6),order_coupon_count,0)) order_coupon_last_7d_count, sum(if(dt>=date_add('$do_date',-6),order_activity_reduce_amount,0)) order_activity_reduce_last_7d_amount, sum(if(dt>=date_add('$do_date',-6),order_coupon_reduce_amount,0)) order_coupon_reduce_last_7d_amount, sum(if(dt>=date_add('$do_date',-6),order_original_amount,0)) order_last_7d_original_amount, sum(if(dt>=date_add('$do_date',-6),order_final_amount,0)) order_last_7d_final_amount, sum(if(dt>=date_add('$do_date',-29),order_count,0)) order_last_30d_count, sum(if(dt>=date_add('$do_date',-29),order_num,0)) order_last_30d_num, sum(if(dt>=date_add('$do_date',-29),order_activity_count,0)) order_activity_last_30d_count, sum(if(dt>=date_add('$do_date',-29),order_coupon_count,0)) order_coupon_last_30d_count, sum(if(dt>=date_add('$do_date',-29),order_activity_reduce_amount,0)) order_activity_reduce_last_30d_amount, sum(if(dt>=date_add('$do_date',-29),order_coupon_reduce_amount,0)) order_coupon_reduce_last_30d_amount, sum(if(dt>=date_add('$do_date',-29),order_original_amount,0)) order_last_30d_original_amount, sum(if(dt>=date_add('$do_date',-29),order_final_amount,0)) order_last_30d_final_amount, sum(order_count) order_count, sum(order_num) order_num, sum(order_activity_count) order_activity_count, sum(order_coupon_count) order_coupon_count, sum(order_activity_reduce_amount) order_activity_reduce_amount, sum(order_coupon_reduce_amount) order_coupon_reduce_amount, sum(order_original_amount) order_original_amount, sum(order_final_amount) order_final_amount, sum(if(dt='$do_date',payment_count,0)) payment_last_1d_count, sum(if(dt='$do_date',payment_num,0)) payment_last_1d_num, sum(if(dt='$do_date',payment_amount,0)) payment_last_1d_amount, sum(if(dt>=date_add('$do_date',-6),payment_count,0)) payment_last_7d_count, sum(if(dt>=date_add('$do_date',-6),payment_num,0)) payment_last_7d_num, sum(if(dt>=date_add('$do_date',-6),payment_amount,0)) payment_last_7d_amount, sum(if(dt>=date_add('$do_date',-29),payment_count,0)) payment_last_30d_count, sum(if(dt>=date_add('$do_date',-29),payment_num,0)) payment_last_30d_num, sum(if(dt>=date_add('$do_date',-29),payment_amount,0)) payment_last_30d_amount, sum(payment_count) payment_count, sum(payment_num) payment_num, sum(payment_amount) payment_amount, sum(if(dt='$do_date',refund_order_count,0)) refund_order_last_1d_count, sum(if(dt='$do_date',refund_order_num,0)) refund_order_last_1d_num, sum(if(dt='$do_date',refund_order_amount,0)) refund_order_last_1d_amount, sum(if(dt>=date_add('$do_date',-6),refund_order_count,0)) refund_order_last_7d_count, sum(if(dt>=date_add('$do_date',-6),refund_order_num,0)) refund_order_last_7d_num, sum(if(dt>=date_add('$do_date',-6),refund_order_amount,0)) refund_order_last_7d_amount, sum(if(dt>=date_add('$do_date',-29),refund_order_count,0)) refund_order_last_30d_count, sum(if(dt>=date_add('$do_date',-29),refund_order_num,0)) refund_order_last_30d_num, sum(if(dt>=date_add('$do_date',-29),refund_order_amount,0)) refund_order_last_30d_amount, sum(refund_order_count) refund_order_count, sum(refund_order_num) refund_order_num, sum(refund_order_amount) refund_order_amount, sum(if(dt='$do_date',refund_payment_count,0)) refund_payment_last_1d_count, sum(if(dt='$do_date',refund_payment_num,0)) refund_payment_last_1d_num, sum(if(dt='$do_date',refund_payment_amount,0)) refund_payment_last_1d_amount, sum(if(dt>=date_add('$do_date',-6),refund_payment_count,0)) refund_payment_last_7d_count, sum(if(dt>=date_add('$do_date',-6),refund_payment_num,0)) refund_payment_last_7d_num, sum(if(dt>=date_add('$do_date',-6),refund_payment_amount,0)) refund_payment_last_7d_amount, sum(if(dt>=date_add('$do_date',-29),refund_payment_count,0)) refund_payment_last_30d_count, sum(if(dt>=date_add('$do_date',-29),refund_payment_num,0)) refund_payment_last_30d_num, sum(if(dt>=date_add('$do_date',-29),refund_payment_amount,0)) refund_payment_last_30d_amount, sum(refund_payment_count) refund_payment_count, sum(refund_payment_num) refund_payment_num, sum(refund_payment_amount) refund_payment_amount, sum(if(dt='$do_date',cart_count,0)) cart_last_1d_count, sum(if(dt>=date_add('$do_date',-6),cart_count,0)) cart_last_7d_count, sum(if(dt>=date_add('$do_date',-29),cart_count,0)) cart_last_30d_count, sum(cart_count) cart_count, sum(if(dt='$do_date',favor_count,0)) favor_last_1d_count, sum(if(dt>=date_add('$do_date',-6),favor_count,0)) favor_last_7d_count, sum(if(dt>=date_add('$do_date',-29),favor_count,0)) favor_last_30d_count, sum(favor_count) favor_count, sum(if(dt='$do_date',appraise_good_count,0)) appraise_last_1d_good_count, sum(if(dt='$do_date',appraise_mid_count,0)) appraise_last_1d_mid_count, sum(if(dt='$do_date',appraise_bad_count,0)) appraise_last_1d_bad_count, sum(if(dt='$do_date',appraise_default_count,0)) appraise_last_1d_default_count, sum(if(dt>=date_add('$do_date',-6),appraise_good_count,0)) appraise_last_7d_good_count, sum(if(dt>=date_add('$do_date',-6),appraise_mid_count,0)) appraise_last_7d_mid_count, sum(if(dt>=date_add('$do_date',-6),appraise_bad_count,0)) appraise_last_7d_bad_count, sum(if(dt>=date_add('$do_date',-6),appraise_default_count,0)) appraise_last_7d_default_count, sum(if(dt>=date_add('$do_date',-29),appraise_good_count,0)) appraise_last_30d_good_count, sum(if(dt>=date_add('$do_date',-29),appraise_mid_count,0)) appraise_last_30d_mid_count, sum(if(dt>=date_add('$do_date',-29),appraise_bad_count,0)) appraise_last_30d_bad_count, sum(if(dt>=date_add('$do_date',-29),appraise_default_count,0)) appraise_last_30d_default_count, sum(appraise_good_count) appraise_good_count, sum(appraise_mid_count) appraise_mid_count, sum(appraise_bad_count) appraise_bad_count, sum(appraise_default_count) appraise_default_count from ${APP}.dws_sku_action_daycount group by sku_id )t2 on t1.id=t2.sku_id; " dwt_coupon_topic=" insert overwrite table ${APP}.dwt_coupon_topic partition(dt='$do_date') select id, nvl(get_last_1d_count,0), nvl(get_last_7d_count,0), nvl(get_last_30d_count,0), nvl(get_count,0), nvl(order_last_1d_count,0), nvl(order_last_1d_reduce_amount,0), nvl(order_last_1d_original_amount,0), nvl(order_last_1d_final_amount,0), nvl(order_last_7d_count,0), nvl(order_last_7d_reduce_amount,0), nvl(order_last_7d_original_amount,0), nvl(order_last_7d_final_amount,0), nvl(order_last_30d_count,0), nvl(order_last_30d_reduce_amount,0), nvl(order_last_30d_original_amount,0), nvl(order_last_30d_final_amount,0), nvl(order_count,0), nvl(order_reduce_amount,0), nvl(order_original_amount,0), nvl(order_final_amount,0), nvl(payment_last_1d_count,0), nvl(payment_last_1d_reduce_amount,0), nvl(payment_last_1d_amount,0), nvl(payment_last_7d_count,0), nvl(payment_last_7d_reduce_amount,0), nvl(payment_last_7d_amount,0), nvl(payment_last_30d_count,0), nvl(payment_last_30d_reduce_amount,0), nvl(payment_last_30d_amount,0), nvl(payment_count,0), nvl(payment_reduce_amount,0), nvl(payment_amount,0), nvl(expire_last_1d_count,0), nvl(expire_last_7d_count,0), nvl(expire_last_30d_count,0), nvl(expire_count,0) from ( select id from ${APP}.dim_coupon_info where dt='$do_date' )t1 left join ( select coupon_id coupon_id, sum(if(dt='$do_date',get_count,0)) get_last_1d_count, sum(if(dt>=date_add('$do_date',-6),get_count,0)) get_last_7d_count, sum(if(dt>=date_add('$do_date',-29),get_count,0)) get_last_30d_count, sum(get_count) get_count, sum(if(dt='$do_date',order_count,0)) order_last_1d_count, sum(if(dt='$do_date',order_reduce_amount,0)) order_last_1d_reduce_amount, sum(if(dt='$do_date',order_original_amount,0)) order_last_1d_original_amount, sum(if(dt='$do_date',order_final_amount,0)) order_last_1d_final_amount, sum(if(dt>=date_add('$do_date',-6),order_count,0)) order_last_7d_count, sum(if(dt>=date_add('$do_date',-6),order_reduce_amount,0)) order_last_7d_reduce_amount, sum(if(dt>=date_add('$do_date',-6),order_original_amount,0)) order_last_7d_original_amount, sum(if(dt>=date_add('$do_date',-6),order_final_amount,0)) order_last_7d_final_amount, sum(if(dt>=date_add('$do_date',-29),order_count,0)) order_last_30d_count, sum(if(dt>=date_add('$do_date',-29),order_reduce_amount,0)) order_last_30d_reduce_amount, sum(if(dt>=date_add('$do_date',-29),order_original_amount,0)) order_last_30d_original_amount, sum(if(dt>=date_add('$do_date',-29),order_final_amount,0)) order_last_30d_final_amount, sum(order_count) order_count, sum(order_reduce_amount) order_reduce_amount, sum(order_original_amount) order_original_amount, sum(order_final_amount) order_final_amount, sum(if(dt='$do_date',payment_count,0)) payment_last_1d_count, sum(if(dt='$do_date',payment_reduce_amount,0)) payment_last_1d_reduce_amount, sum(if(dt='$do_date',payment_amount,0)) payment_last_1d_amount, sum(if(dt>=date_add('$do_date',-6),payment_count,0)) payment_last_7d_count, sum(if(dt>=date_add('$do_date',-6),payment_reduce_amount,0)) payment_last_7d_reduce_amount, sum(if(dt>=date_add('$do_date',-6),payment_amount,0)) payment_last_7d_amount, sum(if(dt>=date_add('$do_date',-29),payment_count,0)) payment_last_30d_count, sum(if(dt>=date_add('$do_date',-29),payment_reduce_amount,0)) payment_last_30d_reduce_amount, sum(if(dt>=date_add('$do_date',-29),payment_amount,0)) payment_last_30d_amount, sum(payment_count) payment_count, sum(payment_reduce_amount) payment_reduce_amount, sum(payment_amount) payment_amount, sum(if(dt='$do_date',expire_count,0)) expire_last_1d_count, sum(if(dt>=date_add('$do_date',-6),expire_count,0)) expire_last_7d_count, sum(if(dt>=date_add('$do_date',-29),expire_count,0)) expire_last_30d_count, sum(expire_count) expire_count from ${APP}.dws_coupon_info_daycount group by coupon_id )t2 on t1.id=t2.coupon_id; " dwt_activity_topic=" insert overwrite table ${APP}.dwt_activity_topic partition(dt='$do_date') select t1.activity_rule_id, t1.activity_id, nvl(order_last_1d_count,0), nvl(order_last_1d_reduce_amount,0), nvl(order_last_1d_original_amount,0), nvl(order_last_1d_final_amount,0), nvl(order_count,0), nvl(order_reduce_amount,0), nvl(order_original_amount,0), nvl(order_final_amount,0), nvl(payment_last_1d_count,0), nvl(payment_last_1d_reduce_amount,0), nvl(payment_last_1d_amount,0), nvl(payment_count,0), nvl(payment_reduce_amount,0), nvl(payment_amount,0) from ( select activity_rule_id, activity_id from ${APP}.dim_activity_rule_info where dt='$do_date' )t1 left join ( select activity_rule_id, activity_id, sum(if(dt='$do_date',order_count,0)) order_last_1d_count, sum(if(dt='$do_date',order_reduce_amount,0)) order_last_1d_reduce_amount, sum(if(dt='$do_date',order_original_amount,0)) order_last_1d_original_amount, sum(if(dt='$do_date',order_final_amount,0)) order_last_1d_final_amount, sum(order_count) order_count, sum(order_reduce_amount) order_reduce_amount, sum(order_original_amount) order_original_amount, sum(order_final_amount) order_final_amount, sum(if(dt='$do_date',payment_count,0)) payment_last_1d_count, sum(if(dt='$do_date',payment_reduce_amount,0)) payment_last_1d_reduce_amount, sum(if(dt='$do_date',payment_amount,0)) payment_last_1d_amount, sum(payment_count) payment_count, sum(payment_reduce_amount) payment_reduce_amount, sum(payment_amount) payment_amount from ${APP}.dws_activity_info_daycount group by activity_rule_id,activity_id )t2 on t1.activity_rule_id=t2.activity_rule_id and t1.activity_id=t2.activity_id; " dwt_area_topic=" insert overwrite table ${APP}.dwt_area_topic partition(dt='$do_date') select id, nvl(visit_last_1d_count,0), nvl(login_last_1d_count,0), nvl(visit_last_7d_count,0), nvl(login_last_7d_count,0), nvl(visit_last_30d_count,0), nvl(login_last_30d_count,0), nvl(visit_count,0), nvl(login_count,0), nvl(order_last_1d_count,0), nvl(order_last_1d_original_amount,0), nvl(order_last_1d_final_amount,0), nvl(order_last_7d_count,0), nvl(order_last_7d_original_amount,0), nvl(order_last_7d_final_amount,0), nvl(order_last_30d_count,0), nvl(order_last_30d_original_amount,0), nvl(order_last_30d_final_amount,0), nvl(order_count,0), nvl(order_original_amount,0), nvl(order_final_amount,0), nvl(payment_last_1d_count,0), nvl(payment_last_1d_amount,0), nvl(payment_last_7d_count,0), nvl(payment_last_7d_amount,0), nvl(payment_last_30d_count,0), nvl(payment_last_30d_amount,0), nvl(payment_count,0), nvl(payment_amount,0), nvl(refund_order_last_1d_count,0), nvl(refund_order_last_1d_amount,0), nvl(refund_order_last_7d_count,0), nvl(refund_order_last_7d_amount,0), nvl(refund_order_last_30d_count,0), nvl(refund_order_last_30d_amount,0), nvl(refund_order_count,0), nvl(refund_order_amount,0), nvl(refund_payment_last_1d_count,0), nvl(refund_payment_last_1d_amount,0), nvl(refund_payment_last_7d_count,0), nvl(refund_payment_last_7d_amount,0), nvl(refund_payment_last_30d_count,0), nvl(refund_payment_last_30d_amount,0), nvl(refund_payment_count,0), nvl(refund_payment_amount,0) from ( select id from ${APP}.dim_base_province )t1 left join ( select province_id province_id, sum(if(dt='$do_date',visit_count,0)) visit_last_1d_count, sum(if(dt='$do_date',login_count,0)) login_last_1d_count, sum(if(dt>=date_add('$do_date',-6),visit_count,0)) visit_last_7d_count, sum(if(dt>=date_add('$do_date',-6),login_count,0)) login_last_7d_count, sum(if(dt>=date_add('$do_date',-29),visit_count,0)) visit_last_30d_count, sum(if(dt>=date_add('$do_date',-29),login_count,0)) login_last_30d_count, sum(visit_count) visit_count, sum(login_count) login_count, sum(if(dt='$do_date',order_count,0)) order_last_1d_count, sum(if(dt='$do_date',order_original_amount,0)) order_last_1d_original_amount, sum(if(dt='$do_date',order_final_amount,0)) order_last_1d_final_amount, sum(if(dt>=date_add('$do_date',-6),order_count,0)) order_last_7d_count, sum(if(dt>=date_add('$do_date',-6),order_original_amount,0)) order_last_7d_original_amount, sum(if(dt>=date_add('$do_date',-6),order_final_amount,0)) order_last_7d_final_amount, sum(if(dt>=date_add('$do_date',-29),order_count,0)) order_last_30d_count, sum(if(dt>=date_add('$do_date',-29),order_original_amount,0)) order_last_30d_original_amount, sum(if(dt>=date_add('$do_date',-29),order_final_amount,0)) order_last_30d_final_amount, sum(order_count) order_count, sum(order_original_amount) order_original_amount, sum(order_final_amount) order_final_amount, sum(if(dt='$do_date',payment_count,0)) payment_last_1d_count, sum(if(dt='$do_date',payment_amount,0)) payment_last_1d_amount, sum(if(dt>=date_add('$do_date',-6),payment_count,0)) payment_last_7d_count, sum(if(dt>=date_add('$do_date',-6),payment_amount,0)) payment_last_7d_amount, sum(if(dt>=date_add('$do_date',-29),payment_count,0)) payment_last_30d_count, sum(if(dt>=date_add('$do_date',-29),payment_amount,0)) payment_last_30d_amount, sum(payment_count) payment_count, sum(payment_amount) payment_amount, sum(if(dt='$do_date',refund_order_count,0)) refund_order_last_1d_count, sum(if(dt='$do_date',refund_order_amount,0)) refund_order_last_1d_amount, sum(if(dt>=date_add('$do_date',-6),refund_order_count,0)) refund_order_last_7d_count, sum(if(dt>=date_add('$do_date',-6),refund_order_amount,0)) refund_order_last_7d_amount, sum(if(dt>=date_add('$do_date',-29),refund_order_count,0)) refund_order_last_30d_count, sum(if(dt>=date_add('$do_date',-29),refund_order_amount,0)) refund_order_last_30d_amount, sum(refund_order_count) refund_order_count, sum(refund_order_amount) refund_order_amount, sum(if(dt='$do_date',refund_payment_count,0)) refund_payment_last_1d_count, sum(if(dt='$do_date',refund_payment_amount,0)) refund_payment_last_1d_amount, sum(if(dt>=date_add('$do_date',-6),refund_payment_count,0)) refund_payment_last_7d_count, sum(if(dt>=date_add('$do_date',-6),refund_payment_amount,0)) refund_payment_last_7d_amount, sum(if(dt>=date_add('$do_date',-29),refund_payment_count,0)) refund_payment_last_30d_count, sum(if(dt>=date_add('$do_date',-29),refund_payment_amount,0)) refund_payment_last_30d_amount, sum(refund_payment_count) refund_payment_count, sum(refund_payment_amount) refund_payment_amount from ${APP}.dws_area_stats_daycount group by province_id )t2 on t1.id=t2.province_id; " case $1 in "dwt_visitor_topic" ) hive -e "$dwt_visitor_topic" ;; "dwt_user_topic" ) hive -e "$dwt_user_topic" ;; "dwt_sku_topic" ) hive -e "$dwt_sku_topic" ;; "dwt_activity_topic" ) hive -e "$dwt_activity_topic" ;; "dwt_coupon_topic" ) hive -e "$dwt_coupon_topic" ;; "dwt_area_topic" ) hive -e "$dwt_area_topic" ;; "all" ) hive -e "$dwt_visitor_topic$dwt_user_topic$dwt_sku_topic$dwt_activity_topic$dwt_coupon_topic$dwt_area_topic" ;; esac- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
- 229
- 230
- 231
- 232
- 233
- 234
- 235
- 236
- 237
- 238
- 239
- 240
- 241
- 242
- 243
- 244
- 245
- 246
- 247
- 248
- 249
- 250
- 251
- 252
- 253
- 254
- 255
- 256
- 257
- 258
- 259
- 260
- 261
- 262
- 263
- 264
- 265
- 266
- 267
- 268
- 269
- 270
- 271
- 272
- 273
- 274
- 275
- 276
- 277
- 278
- 279
- 280
- 281
- 282
- 283
- 284
- 285
- 286
- 287
- 288
- 289
- 290
- 291
- 292
- 293
- 294
- 295
- 296
- 297
- 298
- 299
- 300
- 301
- 302
- 303
- 304
- 305
- 306
- 307
- 308
- 309
- 310
- 311
- 312
- 313
- 314
- 315
- 316
- 317
- 318
- 319
- 320
- 321
- 322
- 323
- 324
- 325
- 326
- 327
- 328
- 329
- 330
- 331
- 332
- 333
- 334
- 335
- 336
- 337
- 338
- 339
- 340
- 341
- 342
- 343
- 344
- 345
- 346
- 347
- 348
- 349
- 350
- 351
- 352
- 353
- 354
- 355
- 356
- 357
- 358
- 359
- 360
- 361
- 362
- 363
- 364
- 365
- 366
- 367
- 368
- 369
- 370
- 371
- 372
- 373
- 374
- 375
- 376
- 377
- 378
- 379
- 380
- 381
- 382
- 383
- 384
- 385
- 386
- 387
- 388
- 389
- 390
- 391
- 392
- 393
- 394
- 395
- 396
- 397
- 398
- 399
- 400
- 401
- 402
- 403
- 404
- 405
- 406
- 407
- 408
- 409
- 410
- 411
- 412
- 413
- 414
- 415
- 416
- 417
- 418
- 419
- 420
- 421
- 422
- 423
- 424
- 425
- 426
- 427
- 428
- 429
- 430
- 431
- 432
- 433
- 434
- 435
- 436
- 437
- 438
- 439
- 440
- 441
- 442
- 443
- 444
- 445
- 446
- 447
- 448
- 449
- 450
- 451
- 452
- 453
- 454
- 455
- 456
- 457
- 458
- 459
- 460
- 461
- 462
- 463
- 464
- 465
- 466
- 467
- 468
- 469
- 470
- 471
- 472
- 473
- 474
- 475
- 476
- 477
- 478
- 479
- 480
- 481
- 482
- 483
- 484
- 485
- 486
- 487
- 488
- 489
- 490
- 491
- 492
- 493
- 494
- 495
- 496
- 497
- 498
- 499
- 500
- 501
- 502
- 503
- 504
- 505
- 506
- 507
- 508
- 509
- 510
- 511
- 512
- 513
- 514
- 515
- 516
- 517
- 518
- 519
- 520
- 521
- 522
- 523
- 524
- 525
- 526
- 527
- 528
- 529
- 530
- 531
- 532
- 533
- 534
- 535
- 536
- 537
- 538
- 539
- 540
- 541
- 542
- 543
- 544
- 545
- 546
- 547
- 548
- 549
- 550
- 551
- 552
- 553
- 554
- 555
- 556
- 557
- 558
- 559
- 560
- 561
- 562
- 563
- 564
- 565
- 566
- 567
- 568
- 569
- 570
- 571
- 572
- 573
- 574
- 575
- 576
- 577
- 578
- 579
- 580
- 581
- 582
- 583
- 584
- 585
- 586
- 587
- 588
- 589
- 590
- 591
- 592
- 593
- 594
- 595
- 596
- 597
- 598
- 599
- 600
- 601
- 602
- 603
- 604
- 605
- 606
- 607
- 608
- 609
- 610
- 611
- 612
- 613
- 614
- 615
- 616
- 617
- 618
- 619
- 620
- 621
- 622
- 623
- 624
- 625
- 626
- 627
- 628
- 629
- 630
- 631
- 632
- 633
- 634
- 635
- 636
- 637
- 638
- 639
- 640
- 641
- 642
- 643
- 644
- 645
- 646
- 647
- 648
- 649
- 650
- 651
- 652
- 653
- 654
- 655
- 656
- 657
- 658
- 659
- 660
- 661
- 662
- 663
- 664
- 665
- 666
- 667
- 668
- 669
- 670
- 671
- 672
- 673
- 674
- 675
- 676
- 677
- 678
- 679
- 680
- 681
- 682
- 683
- 684
- 685
- 686
- 687
- 688
- 689
- 690
- 691
- 692
- 693
- 694
- 695
- 696
- 697
- 698
- 699
- 700
- 701
- 702
- 703
- 704
- 705
- 706
- 707
- 708
- 709
- 710
- 711
- 712
- 713
- 714
- 715
- 716
- 717
- 718
- 719
- 720
- 721
- 722
- 723
- 724
- 725
- 726
- 727
- 728
- 729
- 730
- 731
- 732
- 733
- 734
- 735
- 736
- 737
- 738
- 739
- 740
- 741
- 742
- 743
- 744
- 745
- 746
- 747
- 748
- 749
- 750
- 751
- 752
- 753
- 754
- 755
- 756
- 757
- 758
- 759
- 760
- 761
- 762
- 763
- 764
- 765
- 766
- 767
- 768
- 769
- 770
- 771
- 772
- 773
- 774
- 775
- 776
- 777
- 778
- 779
- 780
- 781
- 782
- 783
- 784
- 785
- 786
- 787
- 788
- 789
- 790
- 791
- 792
- 793
- 794
- 795
- 796
- 797
- 798
- 799
- 800
- 801
- 802
- 803
- 804
- 805
- 806
- 807
- 808
- 809
- 810
- 811
- 812
- 813
- 814
- 815
- 816
- 817
- 818
- 819
- 820
- 821
- 822
- 823
- 824
- 825
- 826
- 827
- 828
- 829
- 830
（2）增加执行权限
[lyh@hadoop102 bin]$ chmod +x dws_to_dwt_init.sh- 1
2）脚本使用
（1）执行脚本[lyh@hadoop102 bin]$ dws_to_dwt_init.sh all 2020-06-14- 1
（2）查看数据是否导入成功
（2）DWT层每日数据导入脚本
1）编写脚本
（1）在/home/lyh/bin目录下创建脚本dws_to_dwt.sh[lyh@hadoop102 bin]$ vim dws_to_dwt.sh- 1
在脚本中填写如下内容
#!/bin/bash APP=gmall # 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天 if [ -n "$2" ] ;then do_date=$2 else do_date=`date -d "-1 day" +%F` fi clear_date=`date -d "$do_date -2 day" +%F` dwt_visitor_topic=" insert overwrite table ${APP}.dwt_visitor_topic partition(dt='$do_date') select nvl(1d_ago.mid_id,old.mid_id), nvl(1d_ago.brand,old.brand), nvl(1d_ago.model,old.model), nvl(1d_ago.channel,old.channel), nvl(1d_ago.os,old.os), nvl(1d_ago.area_code,old.area_code), nvl(1d_ago.version_code,old.version_code), case when old.mid_id is null and 1d_ago.is_new=1 then '$do_date' when old.mid_id is null and 1d_ago.is_new=0 then '2020-06-13'--无法获取准确的首次登录日期，给定一个数仓搭建日之前的日期 else old.visit_date_first end, if(1d_ago.mid_id is not null,'$do_date',old.visit_date_last), nvl(1d_ago.visit_count,0), if(1d_ago.mid_id is null,0,1), nvl(old.visit_last_7d_count,0)+nvl(1d_ago.visit_count,0)- nvl(7d_ago.visit_count,0), nvl(old.visit_last_7d_day_count,0)+if(1d_ago.mid_id is null,0,1)- if(7d_ago.mid_id is null,0,1), nvl(old.visit_last_30d_count,0)+nvl(1d_ago.visit_count,0)- nvl(30d_ago.visit_count,0), nvl(old.visit_last_30d_day_count,0)+if(1d_ago.mid_id is null,0,1)- if(30d_ago.mid_id is null,0,1), nvl(old.visit_count,0)+nvl(1d_ago.visit_count,0), nvl(old.visit_day_count,0)+if(1d_ago.mid_id is null,0,1) from ( select mid_id, brand, model, channel, os, area_code, version_code, visit_date_first, visit_date_last, visit_last_1d_count, visit_last_1d_day_count, visit_last_7d_count, visit_last_7d_day_count, visit_last_30d_count, visit_last_30d_day_count, visit_count, visit_day_count from ${APP}.dwt_visitor_topic where dt=date_add('$do_date',-1) )old full outer join ( select mid_id, brand, model, is_new, channel, os, area_code, version_code, visit_count from ${APP}.dws_visitor_action_daycount where dt='$do_date' )1d_ago on old.mid_id=1d_ago.mid_id left join ( select mid_id, brand, model, is_new, channel, os, area_code, version_code, visit_count from ${APP}.dws_visitor_action_daycount where dt=date_add('$do_date',-7) )7d_ago on old.mid_id=7d_ago.mid_id left join ( select mid_id, brand, model, is_new, channel, os, area_code, version_code, visit_count from ${APP}.dws_visitor_action_daycount where dt=date_add('$do_date',-30) )30d_ago on old.mid_id=30d_ago.mid_id; alter table ${APP}.dwt_visitor_topic drop partition(dt='$clear_date'); " dwt_user_topic=" insert overwrite table ${APP}.dwt_user_topic partition(dt='$do_date') select nvl(1d_ago.user_id,old.user_id), nvl(old.login_date_first,'$do_date'), if(1d_ago.user_id is not null,'$do_date',old.login_date_last), nvl(1d_ago.login_count,0), if(1d_ago.user_id is not null,1,0), nvl(old.login_last_7d_count,0)+nvl(1d_ago.login_count,0)- nvl(7d_ago.login_count,0), nvl(old.login_last_7d_day_count,0)+if(1d_ago.user_id is null,0,1)- if(7d_ago.user_id is null,0,1), nvl(old.login_last_30d_count,0)+nvl(1d_ago.login_count,0)- nvl(30d_ago.login_count,0), nvl(old.login_last_30d_day_count,0)+if(1d_ago.user_id is null,0,1)- if(30d_ago.user_id is null,0,1), nvl(old.login_count,0)+nvl(1d_ago.login_count,0), nvl(old.login_day_count,0)+if(1d_ago.user_id is not null,1,0), if(old.order_date_first is null and 1d_ago.order_count>0, '$do_date', old.order_date_first), if(1d_ago.order_count>0,'$do_date',old.order_date_last), nvl(1d_ago.order_count,0), nvl(1d_ago.order_activity_count,0), nvl(1d_ago.order_activity_reduce_amount,0.0), nvl(1d_ago.order_coupon_count,0), nvl(1d_ago.order_coupon_reduce_amount,0.0), nvl(1d_ago.order_original_amount,0.0), nvl(1d_ago.order_final_amount,0.0), nvl(old.order_last_7d_count,0)+nvl(1d_ago.order_count,0)- nvl(7d_ago.order_count,0), nvl(old.order_activity_last_7d_count,0)+nvl(1d_ago.order_activity_count,0)- nvl(7d_ago.order_activity_count,0), nvl(old.order_activity_reduce_last_7d_amount,0.0)+nvl(1d_ago.order_activity_reduce_amount,0.0)- nvl(7d_ago.order_activity_reduce_amount,0.0), nvl(old.order_coupon_last_7d_count,0)+nvl(1d_ago.order_coupon_count,0)- nvl(7d_ago.order_coupon_count,0), nvl(old.order_coupon_reduce_last_7d_amount,0.0)+nvl(1d_ago.order_coupon_reduce_amount,0.0)- nvl(7d_ago.order_coupon_reduce_amount,0.0), nvl(old.order_last_7d_original_amount,0.0)+nvl(1d_ago.order_original_amount,0.0)- nvl(7d_ago.order_original_amount,0.0), nvl(old.order_last_7d_final_amount,0.0)+nvl(1d_ago.order_final_amount,0.0)- nvl(7d_ago.order_final_amount,0.0), nvl(old.order_last_30d_count,0)+nvl(1d_ago.order_count,0)- nvl(30d_ago.order_count,0), nvl(old.order_activity_last_30d_count,0)+nvl(1d_ago.order_activity_count,0)- nvl(30d_ago.order_activity_count,0), nvl(old.order_activity_reduce_last_30d_amount,0.0)+nvl(1d_ago.order_activity_reduce_amount,0.0)- nvl(30d_ago.order_activity_reduce_amount,0.0), nvl(old.order_coupon_last_30d_count,0)+nvl(1d_ago.order_coupon_count,0)- nvl(30d_ago.order_coupon_count,0), nvl(old.order_coupon_reduce_last_30d_amount,0.0)+nvl(1d_ago.order_coupon_reduce_amount,0.0)- nvl(30d_ago.order_coupon_reduce_amount,0.0), nvl(old.order_last_30d_original_amount,0.0)+nvl(1d_ago.order_original_amount,0.0)- nvl(30d_ago.order_original_amount,0.0), nvl(old.order_last_30d_final_amount,0.0)+nvl(1d_ago.order_final_amount,0.0)- nvl(30d_ago.order_final_amount,0.0), nvl(old.order_count,0)+nvl(1d_ago.order_count,0), nvl(old.order_activity_count,0)+nvl(1d_ago.order_activity_count,0), nvl(old.order_activity_reduce_amount,0.0)+nvl(1d_ago.order_activity_reduce_amount,0.0), nvl(old.order_coupon_count,0)+nvl(1d_ago.order_coupon_count,0), nvl(old.order_coupon_reduce_amount,0.0)+nvl(1d_ago.order_coupon_reduce_amount,0.0), nvl(old.order_original_amount,0.0)+nvl(1d_ago.order_original_amount,0.0), nvl(old.order_final_amount,0.0)+nvl(1d_ago.order_final_amount,0.0), if(old.payment_date_first is null and 1d_ago.payment_count>0, '$do_date', old.payment_date_first), if(1d_ago.payment_count>0,'$do_date',old.payment_date_last), nvl(1d_ago.payment_count,0), nvl(1d_ago.payment_amount,0.0), nvl(old.payment_last_7d_count,0)+nvl(1d_ago.payment_count,0)-nvl(7d_ago.payment_count,0), nvl(old.payment_last_7d_amount,0.0)+nvl(1d_ago.payment_amount,0.0)-nvl(7d_ago.payment_amount,0.0), nvl(old.payment_last_30d_count,0)+nvl(1d_ago.payment_count,0)-nvl(30d_ago.payment_count,0), nvl(old.payment_last_30d_amount,0.0)+nvl(1d_ago.payment_amount,0.0)- nvl(30d_ago.payment_amount,0.0), nvl(old.payment_count,0)+nvl(1d_ago.payment_count,0), nvl(old.payment_amount,0.0)+nvl(1d_ago.payment_amount,0.0), nvl(1d_ago.refund_order_count,0), nvl(1d_ago.refund_order_num,0), nvl(1d_ago.refund_order_amount,0.0), nvl(old.refund_order_last_7d_count,0)+nvl(1d_ago.refund_order_count,0)- nvl(7d_ago.refund_order_count,0), nvl(old.refund_order_last_7d_num,0)+nvl(1d_ago.refund_order_num, 0)- nvl(7d_ago.refund_order_num,0), nvl(old.refund_order_last_7d_amount,0.0)+ nvl(1d_ago.refund_order_amount,0.0)- nvl(7d_ago.refund_order_amount,0.0), nvl(old.refund_order_last_30d_count,0)+nvl(1d_ago.refund_order_count,0)- nvl(30d_ago.refund_order_count,0), nvl(old.refund_order_last_30d_num,0)+nvl(1d_ago.refund_order_num, 0)- nvl(30d_ago.refund_order_num,0), nvl(old.refund_order_last_30d_amount,0.0)+ nvl(1d_ago.refund_order_amount,0.0)- nvl(30d_ago.refund_order_amount,0.0), nvl(old.refund_order_count,0)+nvl(1d_ago.refund_order_count,0), nvl(old.refund_order_num,0)+nvl(1d_ago.refund_order_num,0), nvl(old.refund_order_amount,0.0)+ nvl(1d_ago.refund_order_amount,0.0), nvl(1d_ago.refund_payment_count,0), nvl(1d_ago.refund_payment_num,0), nvl(1d_ago.refund_payment_amount,0.0), nvl(old.refund_payment_last_7d_count,0)+nvl(1d_ago.refund_payment_count,0)-nvl(7d_ago.refund_payment_count,0), nvl(old.refund_payment_last_7d_num,0)+nvl(1d_ago.refund_payment_num,0)- nvl(7d_ago.refund_payment_num,0), nvl(old.refund_payment_last_7d_amount,0.0)+ nvl(1d_ago.refund_payment_amount,0.0)- nvl(7d_ago.refund_payment_amount,0.0), nvl(old.refund_payment_last_30d_count,0)+nvl(1d_ago.refund_payment_count,0)-nvl(30d_ago.refund_payment_count,0), nvl(old.refund_payment_last_30d_num,0)+nvl(1d_ago.refund_payment_num,0)- nvl(30d_ago.refund_payment_num,0), nvl(old.refund_payment_last_30d_amount,0.0)+ nvl(1d_ago.refund_payment_amount,0.0)- nvl(30d_ago.refund_payment_amount,0.0), nvl(old.refund_payment_count,0)+nvl(1d_ago.refund_payment_count,0), nvl(old.refund_payment_num,0)+nvl(1d_ago.refund_payment_num,0), nvl(old.refund_payment_amount,0.0)+nvl(1d_ago.refund_payment_amount,0.0), nvl(1d_ago.cart_count,0), nvl(old.cart_last_7d_count,0)+nvl(1d_ago.cart_count,0)-nvl(7d_ago.cart_count,0), nvl(old.cart_last_30d_count,0)+nvl(1d_ago.cart_count,0)-nvl(30d_ago.cart_count,0), nvl(old.cart_count,0)+nvl(1d_ago.cart_count,0), nvl(1d_ago.favor_count,0), nvl(old.favor_last_7d_count,0)+nvl(1d_ago.favor_count,0)- nvl(7d_ago.favor_count,0), nvl(old.favor_last_30d_count,0)+nvl(1d_ago.favor_count,0)- nvl(30d_ago.favor_count,0), nvl(old.favor_count,0)+nvl(1d_ago.favor_count,0), nvl(1d_ago.coupon_get_count,0), nvl(1d_ago.coupon_using_count,0), nvl(1d_ago.coupon_used_count,0), nvl(old.coupon_last_7d_get_count,0)+nvl(1d_ago.coupon_get_count,0)- nvl(7d_ago.coupon_get_count,0), nvl(old.coupon_last_7d_using_count,0)+nvl(1d_ago.coupon_using_count,0)- nvl(7d_ago.coupon_using_count,0), nvl(old.coupon_last_7d_used_count,0)+ nvl(1d_ago.coupon_used_count,0)- nvl(7d_ago.coupon_used_count,0), nvl(old.coupon_last_30d_get_count,0)+nvl(1d_ago.coupon_get_count,0)- nvl(30d_ago.coupon_get_count,0), nvl(old.coupon_last_30d_using_count,0)+nvl(1d_ago.coupon_using_count,0)- nvl(30d_ago.coupon_using_count,0), nvl(old.coupon_last_30d_used_count,0)+ nvl(1d_ago.coupon_used_count,0)- nvl(30d_ago.coupon_used_count,0), nvl(old.coupon_get_count,0)+nvl(1d_ago.coupon_get_count,0), nvl(old.coupon_using_count,0)+nvl(1d_ago.coupon_using_count,0), nvl(old.coupon_used_count,0)+nvl(1d_ago.coupon_used_count,0), nvl(1d_ago.appraise_good_count,0), nvl(1d_ago.appraise_mid_count,0), nvl(1d_ago.appraise_bad_count,0), nvl(old.appraise_last_7d_default_count,0)+nvl(1d_ago.appraise_default_count,0)-nvl(7d_ago.appraise_default_count,0), nvl(old.appraise_last_7d_good_count,0)+nvl(1d_ago.appraise_good_count,0)- nvl(7d_ago.appraise_good_count,0), nvl(old.appraise_last_7d_mid_count,0)+nvl(1d_ago.appraise_mid_count,0)-nvl(7d_ago.appraise_mid_count,0), nvl(old.appraise_last_7d_bad_count,0)+nvl(1d_ago.appraise_bad_count,0)-nvl(7d_ago.appraise_bad_count,0), nvl(old.appraise_last_7d_default_count,0)+nvl(1d_ago.appraise_default_count,0)-nvl(7d_ago.appraise_default_count,0), nvl(old.appraise_last_30d_good_count,0)+nvl(1d_ago.appraise_good_count,0)- nvl(30d_ago.appraise_good_count,0), nvl(old.appraise_last_30d_mid_count,0)+nvl(1d_ago.appraise_mid_count,0)-nvl(30d_ago.appraise_mid_count,0), nvl(old.appraise_last_30d_bad_count,0)+nvl(1d_ago.appraise_bad_count,0)-nvl(30d_ago.appraise_bad_count,0), nvl(old.appraise_last_30d_default_count,0)+nvl(1d_ago.appraise_default_count,0)-nvl(30d_ago.appraise_default_count,0), nvl(old.appraise_good_count,0)+nvl(1d_ago.appraise_good_count,0), nvl(old.appraise_mid_count,0)+nvl(1d_ago.appraise_mid_count, 0), nvl(old.appraise_bad_count,0)+nvl(1d_ago.appraise_bad_count,0), nvl(old.appraise_default_count,0)+nvl(1d_ago.appraise_default_count,0) from ( select user_id, login_date_first, login_date_last, login_date_1d_count, login_last_1d_day_count, login_last_7d_count, login_last_7d_day_count, login_last_30d_count, login_last_30d_day_count, login_count, login_day_count, order_date_first, order_date_last, order_last_1d_count, order_activity_last_1d_count, order_activity_reduce_last_1d_amount, order_coupon_last_1d_count, order_coupon_reduce_last_1d_amount, order_last_1d_original_amount, order_last_1d_final_amount, order_last_7d_count, order_activity_last_7d_count, order_activity_reduce_last_7d_amount, order_coupon_last_7d_count, order_coupon_reduce_last_7d_amount, order_last_7d_original_amount, order_last_7d_final_amount, order_last_30d_count, order_activity_last_30d_count, order_activity_reduce_last_30d_amount, order_coupon_last_30d_count, order_coupon_reduce_last_30d_amount, order_last_30d_original_amount, order_last_30d_final_amount, order_count, order_activity_count, order_activity_reduce_amount, order_coupon_count, order_coupon_reduce_amount, order_original_amount, order_final_amount, payment_date_first, payment_date_last, payment_last_1d_count, payment_last_1d_amount, payment_last_7d_count, payment_last_7d_amount, payment_last_30d_count, payment_last_30d_amount, payment_count, payment_amount, refund_order_last_1d_count, refund_order_last_1d_num, refund_order_last_1d_amount, refund_order_last_7d_count, refund_order_last_7d_num, refund_order_last_7d_amount, refund_order_last_30d_count, refund_order_last_30d_num, refund_order_last_30d_amount, refund_order_count, refund_order_num, refund_order_amount, refund_payment_last_1d_count, refund_payment_last_1d_num, refund_payment_last_1d_amount, refund_payment_last_7d_count, refund_payment_last_7d_num, refund_payment_last_7d_amount, refund_payment_last_30d_count, refund_payment_last_30d_num, refund_payment_last_30d_amount, refund_payment_count, refund_payment_num, refund_payment_amount, cart_last_1d_count, cart_last_7d_count, cart_last_30d_count, cart_count, favor_last_1d_count, favor_last_7d_count, favor_last_30d_count, favor_count, coupon_last_1d_get_count, coupon_last_1d_using_count, coupon_last_1d_used_count, coupon_last_7d_get_count, coupon_last_7d_using_count, coupon_last_7d_used_count, coupon_last_30d_get_count, coupon_last_30d_using_count, coupon_last_30d_used_count, coupon_get_count, coupon_using_count, coupon_used_count, appraise_last_1d_good_count, appraise_last_1d_mid_count, appraise_last_1d_bad_count, appraise_last_1d_default_count, appraise_last_7d_good_count, appraise_last_7d_mid_count, appraise_last_7d_bad_count, appraise_last_7d_default_count, appraise_last_30d_good_count, appraise_last_30d_mid_count, appraise_last_30d_bad_count, appraise_last_30d_default_count, appraise_good_count, appraise_mid_count, appraise_bad_count, appraise_default_count from ${APP}.dwt_user_topic where dt=date_add('$do_date',-1) )old full outer join ( select user_id, login_count, cart_count, favor_count, order_count, order_activity_count, order_activity_reduce_amount, order_coupon_count, order_coupon_reduce_amount, order_original_amount, order_final_amount, payment_count, payment_amount, refund_order_count, refund_order_num, refund_order_amount, refund_payment_count, refund_payment_num, refund_payment_amount, coupon_get_count, coupon_using_count, coupon_used_count, appraise_good_count, appraise_mid_count, appraise_bad_count, appraise_default_count from ${APP}.dws_user_action_daycount where dt='$do_date' )1d_ago on old.user_id=1d_ago.user_id left join ( select user_id, login_count, cart_count, favor_count, order_count, order_activity_count, order_activity_reduce_amount, order_coupon_count, order_coupon_reduce_amount, order_original_amount, order_final_amount, payment_count, payment_amount, refund_order_count, refund_order_num, refund_order_amount, refund_payment_count, refund_payment_num, refund_payment_amount, coupon_get_count, coupon_using_count, coupon_used_count, appraise_good_count, appraise_mid_count, appraise_bad_count, appraise_default_count from ${APP}.dws_user_action_daycount where dt=date_add('$do_date',-7) )7d_ago on old.user_id=7d_ago.user_id left join ( select user_id, login_count, cart_count, favor_count, order_count, order_activity_count, order_activity_reduce_amount, order_coupon_count, order_coupon_reduce_amount, order_original_amount, order_final_amount, payment_count, payment_amount, refund_order_count, refund_order_num, refund_order_amount, refund_payment_count, refund_payment_num, refund_payment_amount, coupon_get_count, coupon_using_count, coupon_used_count, appraise_good_count, appraise_mid_count, appraise_bad_count, appraise_default_count from ${APP}.dws_user_action_daycount where dt=date_add('$do_date',-30) )30d_ago on old.user_id=30d_ago.user_id; alter table ${APP}.dwt_user_topic drop partition(dt='$clear_date'); " dwt_sku_topic=" insert overwrite table ${APP}.dwt_sku_topic partition(dt='$do_date') select nvl(1d_ago.sku_id,old.sku_id), nvl(1d_ago.order_count,0), nvl(1d_ago.order_num,0), nvl(1d_ago.order_activity_count,0), nvl(1d_ago.order_coupon_count,0), nvl(1d_ago.order_activity_reduce_amount,0.0), nvl(1d_ago.order_coupon_reduce_amount,0.0), nvl(1d_ago.order_original_amount,0.0), nvl(1d_ago.order_final_amount,0.0), nvl(old.order_last_7d_count,0)+nvl(1d_ago.order_count,0)- nvl(7d_ago.order_count,0), nvl(old.order_last_7d_num,0)+nvl(1d_ago.order_num,0)- nvl(7d_ago.order_num,0), nvl(old.order_activity_last_7d_count,0)+nvl(1d_ago.order_activity_count,0)- nvl(7d_ago.order_activity_count,0), nvl(old.order_coupon_last_7d_count,0)+nvl(1d_ago.order_coupon_count,0)- nvl(7d_ago.order_coupon_count,0), nvl(old.order_activity_reduce_last_7d_amount,0.0)+nvl(1d_ago.order_activity_reduce_amount,0.0)- nvl(7d_ago.order_activity_reduce_amount,0.0), nvl(old.order_coupon_reduce_last_7d_amount,0.0)+nvl(1d_ago.order_coupon_reduce_amount,0.0)- nvl(7d_ago.order_coupon_reduce_amount,0.0), nvl(old.order_last_7d_original_amount,0.0)+nvl(1d_ago.order_original_amount,0.0)- nvl(7d_ago.order_original_amount,0.0), nvl(old.order_last_7d_final_amount,0.0)+nvl(1d_ago.order_final_amount,0.0)- nvl(7d_ago.order_final_amount,0.0), nvl(old.order_last_30d_count,0)+nvl(1d_ago.order_count,0)- nvl(30d_ago.order_count,0), nvl(old.order_last_30d_num,0)+nvl(1d_ago.order_num,0)- nvl(30d_ago.order_num,0), nvl(old.order_activity_last_30d_count,0)+nvl(1d_ago.order_activity_count,0)- nvl(30d_ago.order_activity_count,0), nvl(old.order_coupon_last_30d_count,0)+nvl(1d_ago.order_coupon_count,0)- nvl(30d_ago.order_coupon_count,0), nvl(old.order_activity_reduce_last_30d_amount,0.0)+nvl(1d_ago.order_activity_reduce_amount,0.0)- nvl(30d_ago.order_activity_reduce_amount,0.0), nvl(old.order_coupon_reduce_last_30d_amount,0.0)+nvl(1d_ago.order_coupon_reduce_amount,0.0)- nvl(30d_ago.order_coupon_reduce_amount,0.0), nvl(old.order_last_30d_original_amount,0.0)+nvl(1d_ago.order_original_amount,0.0)- nvl(30d_ago.order_original_amount,0.0), nvl(old.order_last_30d_final_amount,0.0)+nvl(1d_ago.order_final_amount,0.0)- nvl(30d_ago.order_final_amount,0.0), nvl(old.order_count,0)+nvl(1d_ago.order_count,0), nvl(old.order_num,0)+nvl(1d_ago.order_num,0), nvl(old.order_activity_count,0)+nvl(1d_ago.order_activity_count,0), nvl(old.order_coupon_count,0)+nvl(1d_ago.order_coupon_count,0), nvl(old.order_activity_reduce_amount,0.0)+nvl(1d_ago.order_activity_reduce_amount,0.0), nvl(old.order_coupon_reduce_amount,0.0)+nvl(1d_ago.order_coupon_reduce_amount,0.0), nvl(old.order_original_amount,0.0)+nvl(1d_ago.order_original_amount,0.0), nvl(old.order_final_amount,0.0)+nvl(1d_ago.order_final_amount,0.0), nvl(1d_ago.payment_count,0), nvl(1d_ago.payment_num,0), nvl(1d_ago.payment_amount,0.0), nvl(old.payment_last_7d_count,0)+nvl(1d_ago.payment_count,0)- nvl(7d_ago.payment_count,0), nvl(old.payment_last_7d_num,0)+nvl(1d_ago.payment_num,0)- nvl(7d_ago.payment_num,0), nvl(old.payment_last_7d_amount,0.0)+nvl(1d_ago.payment_amount,0.0)- nvl(7d_ago.payment_amount,0.0), nvl(old.payment_last_30d_count,0)+nvl(1d_ago.payment_count,0)- nvl(30d_ago.payment_count,0), nvl(old.payment_last_30d_num,0)+nvl(1d_ago.payment_num,0)- nvl(30d_ago.payment_num,0), nvl(old.payment_last_30d_amount,0.0)+nvl(1d_ago.payment_amount,0.0)- nvl(30d_ago.payment_amount,0.0), nvl(old.payment_count,0)+nvl(1d_ago.payment_count,0), nvl(old.payment_num,0)+nvl(1d_ago.payment_num,0), nvl(old.payment_amount,0.0)+nvl(1d_ago.payment_amount,0.0), nvl(old.refund_order_last_1d_count,0)+nvl(1d_ago.refund_order_count,0)- nvl(1d_ago.refund_order_count,0), nvl(old.refund_order_last_1d_num,0)+nvl(1d_ago.refund_order_num,0)- nvl(1d_ago.refund_order_num,0), nvl(old.refund_order_last_1d_amount,0.0)+nvl(1d_ago.refund_order_amount,0.0)- nvl(1d_ago.refund_order_amount,0.0), nvl(old.refund_order_last_7d_count,0)+nvl(1d_ago.refund_order_count,0)- nvl(7d_ago.refund_order_count,0), nvl(old.refund_order_last_7d_num,0)+nvl(1d_ago.refund_order_num,0)- nvl(7d_ago.refund_order_num,0), nvl(old.refund_order_last_7d_amount,0.0)+nvl(1d_ago.refund_order_amount,0.0)- nvl(7d_ago.refund_order_amount,0.0), nvl(old.refund_order_last_30d_count,0)+nvl(1d_ago.refund_order_count,0)- nvl(30d_ago.refund_order_count,0), nvl(old.refund_order_last_30d_num,0)+nvl(1d_ago.refund_order_num,0)- nvl(30d_ago.refund_order_num,0), nvl(old.refund_order_last_30d_amount,0.0)+nvl(1d_ago.refund_order_amount,0.0)- nvl(30d_ago.refund_order_amount,0.0), nvl(old.refund_order_count,0)+nvl(1d_ago.refund_order_count,0), nvl(old.refund_order_num,0)+nvl(1d_ago.refund_order_num,0), nvl(old.refund_order_amount,0.0)+nvl(1d_ago.refund_order_amount,0.0), nvl(1d_ago.refund_payment_count,0), nvl(1d_ago.refund_payment_num,0), nvl(1d_ago.refund_payment_amount,0.0), nvl(old.refund_payment_last_7d_count,0)+nvl(1d_ago.refund_payment_count,0)- nvl(7d_ago.refund_payment_count,0), nvl(old.refund_payment_last_7d_num,0)+nvl(1d_ago.refund_payment_num,0)- nvl(7d_ago.refund_payment_num,0), nvl(old.refund_payment_last_7d_amount,0.0)+nvl(1d_ago.refund_payment_amount,0.0)- nvl(7d_ago.refund_payment_amount,0.0), nvl(old.refund_payment_last_30d_count,0)+nvl(1d_ago.refund_payment_count,0)- nvl(30d_ago.refund_payment_count,0), nvl(old.refund_payment_last_30d_num,0)+nvl(1d_ago.refund_payment_num,0)- nvl(30d_ago.refund_payment_num,0), nvl(old.refund_payment_last_30d_amount,0.0)+nvl(1d_ago.refund_payment_amount,0.0)- nvl(30d_ago.refund_payment_amount,0.0), nvl(old.refund_payment_count,0)+nvl(1d_ago.refund_payment_count,0), nvl(old.refund_payment_num,0)+nvl(1d_ago.refund_payment_num,0), nvl(old.refund_payment_amount,0.0)+nvl(1d_ago.refund_payment_amount,0.0), nvl(1d_ago.cart_count,0), nvl(old.cart_last_7d_count,0)+nvl(1d_ago.cart_count,0)- nvl(7d_ago.cart_count,0), nvl(old.cart_last_30d_count,0)+nvl(1d_ago.cart_count,0)- nvl(30d_ago.cart_count,0), nvl(old.cart_count,0)+nvl(1d_ago.cart_count,0), nvl(1d_ago.favor_count,0), nvl(old.favor_last_7d_count,0)+nvl(1d_ago.favor_count,0)- nvl(7d_ago.favor_count,0), nvl(old.favor_last_30d_count,0)+nvl(1d_ago.favor_count,0)- nvl(30d_ago.favor_count,0), nvl(old.favor_count,0)+nvl(1d_ago.favor_count,0), nvl(1d_ago.appraise_good_count,0), nvl(1d_ago.appraise_mid_count,0), nvl(1d_ago.appraise_bad_count,0), nvl(1d_ago.appraise_default_count,0), nvl(old.appraise_last_7d_good_count,0)+nvl(1d_ago.appraise_good_count,0)- nvl(7d_ago.appraise_good_count,0), nvl(old.appraise_last_7d_mid_count,0)+nvl(1d_ago.appraise_mid_count,0)- nvl(7d_ago.appraise_mid_count,0), nvl(old.appraise_last_7d_bad_count,0)+nvl(1d_ago.appraise_bad_count,0)- nvl(7d_ago.appraise_bad_count,0), nvl(old.appraise_last_7d_default_count,0)+nvl(1d_ago.appraise_default_count,0)- nvl(7d_ago.appraise_default_count,0), nvl(old.appraise_last_30d_good_count,0)+nvl(1d_ago.appraise_good_count,0)- nvl(30d_ago.appraise_good_count,0), nvl(old.appraise_last_30d_mid_count,0)+nvl(1d_ago.appraise_mid_count,0)- nvl(30d_ago.appraise_mid_count,0), nvl(old.appraise_last_30d_bad_count,0)+nvl(1d_ago.appraise_bad_count,0)- nvl(30d_ago.appraise_bad_count,0), nvl(old.appraise_last_30d_default_count,0)+nvl(1d_ago.appraise_default_count,0)- nvl(30d_ago.appraise_default_count,0), nvl(old.appraise_good_count,0)+nvl(1d_ago.appraise_good_count,0), nvl(old.appraise_mid_count,0)+nvl(1d_ago.appraise_mid_count,0), nvl(old.appraise_bad_count,0)+nvl(1d_ago.appraise_bad_count,0), nvl(old.appraise_default_count,0)+nvl(1d_ago.appraise_default_count,0) from ( select sku_id, order_last_1d_count, order_last_1d_num, order_activity_last_1d_count, order_coupon_last_1d_count, order_activity_reduce_last_1d_amount, order_coupon_reduce_last_1d_amount, order_last_1d_original_amount, order_last_1d_final_amount, order_last_7d_count, order_last_7d_num, order_activity_last_7d_count, order_coupon_last_7d_count, order_activity_reduce_last_7d_amount, order_coupon_reduce_last_7d_amount, order_last_7d_original_amount, order_last_7d_final_amount, order_last_30d_count, order_last_30d_num, order_activity_last_30d_count, order_coupon_last_30d_count, order_activity_reduce_last_30d_amount, order_coupon_reduce_last_30d_amount, order_last_30d_original_amount, order_last_30d_final_amount, order_count, order_num, order_activity_count, order_coupon_count, order_activity_reduce_amount, order_coupon_reduce_amount, order_original_amount, order_final_amount, payment_last_1d_count, payment_last_1d_num, payment_last_1d_amount, payment_last_7d_count, payment_last_7d_num, payment_last_7d_amount, payment_last_30d_count, payment_last_30d_num, payment_last_30d_amount, payment_count, payment_num, payment_amount, refund_order_last_1d_count, refund_order_last_1d_num, refund_order_last_1d_amount, refund_order_last_7d_count, refund_order_last_7d_num, refund_order_last_7d_amount, refund_order_last_30d_count, refund_order_last_30d_num, refund_order_last_30d_amount, refund_order_count, refund_order_num, refund_order_amount, refund_payment_last_1d_count, refund_payment_last_1d_num, refund_payment_last_1d_amount, refund_payment_last_7d_count, refund_payment_last_7d_num, refund_payment_last_7d_amount, refund_payment_last_30d_count, refund_payment_last_30d_num, refund_payment_last_30d_amount, refund_payment_count, refund_payment_num, refund_payment_amount, cart_last_1d_count, cart_last_7d_count, cart_last_30d_count, cart_count, favor_last_1d_count, favor_last_7d_count, favor_last_30d_count, favor_count, appraise_last_1d_good_count, appraise_last_1d_mid_count, appraise_last_1d_bad_count, appraise_last_1d_default_count, appraise_last_7d_good_count, appraise_last_7d_mid_count, appraise_last_7d_bad_count, appraise_last_7d_default_count, appraise_last_30d_good_count, appraise_last_30d_mid_count, appraise_last_30d_bad_count, appraise_last_30d_default_count, appraise_good_count, appraise_mid_count, appraise_bad_count, appraise_default_count from ${APP}.dwt_sku_topic where dt=date_add('$do_date',-1) )old full outer join ( select sku_id, order_count, order_num, order_activity_count, order_coupon_count, order_activity_reduce_amount, order_coupon_reduce_amount, order_original_amount, order_final_amount, payment_count, payment_num, payment_amount, refund_order_count, refund_order_num, refund_order_amount, refund_payment_count, refund_payment_num, refund_payment_amount, cart_count, favor_count, appraise_good_count, appraise_mid_count, appraise_bad_count, appraise_default_count from ${APP}.dws_sku_action_daycount where dt='$do_date' )1d_ago on old.sku_id=1d_ago.sku_id left join ( select sku_id, order_count, order_num, order_activity_count, order_coupon_count, order_activity_reduce_amount, order_coupon_reduce_amount, order_original_amount, order_final_amount, payment_count, payment_num, payment_amount, refund_order_count, refund_order_num, refund_order_amount, refund_payment_count, refund_payment_num, refund_payment_amount, cart_count, favor_count, appraise_good_count, appraise_mid_count, appraise_bad_count, appraise_default_count from ${APP}.dws_sku_action_daycount where dt=date_add('$do_date',-7) )7d_ago on old.sku_id=7d_ago.sku_id left join ( select sku_id, order_count, order_num, order_activity_count, order_coupon_count, order_activity_reduce_amount, order_coupon_reduce_amount, order_original_amount, order_final_amount, payment_count, payment_num, payment_amount, refund_order_count, refund_order_num, refund_order_amount, refund_payment_count, refund_payment_num, refund_payment_amount, cart_count, favor_count, appraise_good_count, appraise_mid_count, appraise_bad_count, appraise_default_count from ${APP}.dws_sku_action_daycount where dt=date_add('$do_date',-30) )30d_ago on old.sku_id=30d_ago.sku_id; alter table ${APP}.dwt_sku_topic drop partition(dt='$clear_date'); " dwt_activity_topic=" insert overwrite table ${APP}.dwt_activity_topic partition(dt='$do_date') select nvl(1d_ago.activity_rule_id,old.activity_rule_id), nvl(1d_ago.activity_id,old.activity_id), nvl(1d_ago.order_count,0), nvl(1d_ago.order_reduce_amount,0.0), nvl(1d_ago.order_original_amount,0.0), nvl(1d_ago.order_final_amount,0.0), nvl(old.order_count,0)+nvl(1d_ago.order_count,0), nvl(old.order_reduce_amount,0.0)+nvl(1d_ago.order_reduce_amount,0.0), nvl(old.order_original_amount,0.0)+nvl(1d_ago.order_original_amount,0.0), nvl(old.order_final_amount,0.0)+nvl(1d_ago.order_final_amount,0.0), nvl(1d_ago.payment_count,0), nvl(1d_ago.payment_reduce_amount,0.0), nvl(1d_ago.payment_amount,0.0), nvl(old.payment_count,0)+nvl(1d_ago.payment_count,0), nvl(old.payment_reduce_amount,0.0)+nvl(1d_ago.payment_reduce_amount,0.0), nvl(old.payment_amount,0.0)+nvl(1d_ago.payment_amount,0.0) from ( select activity_rule_id, activity_id, order_count, order_reduce_amount, order_original_amount, order_final_amount, payment_count, payment_reduce_amount, payment_amount from ${APP}.dwt_activity_topic where dt=date_add('$do_date',-1) )old full outer join ( select activity_rule_id, activity_id, order_count, order_reduce_amount, order_original_amount, order_final_amount, payment_count, payment_reduce_amount, payment_amount from ${APP}.dws_activity_info_daycount where dt='$do_date' )1d_ago on old.activity_rule_id=1d_ago.activity_rule_id; alter table ${APP}.dwt_activity_topic drop partition(dt='$clear_date'); " dwt_coupon_topic=" insert overwrite table ${APP}.dwt_coupon_topic partition(dt='$do_date') select nvl(1d_ago.coupon_id,old.coupon_id), nvl(1d_ago.get_count,0), nvl(old.get_last_7d_count,0)+nvl(1d_ago.get_count,0)- nvl(7d_ago.get_count,0), nvl(old.get_last_30d_count,0)+nvl(1d_ago.get_count,0)- nvl(30d_ago.get_count,0), nvl(old.get_count,0)+nvl(1d_ago.get_count,0), nvl(1d_ago.order_count,0), nvl(1d_ago.order_reduce_amount,0.0), nvl(1d_ago.order_original_amount,0.0), nvl(1d_ago.order_final_amount,0.0), nvl(old.order_last_7d_count,0)+nvl(1d_ago.order_count,0)- nvl(7d_ago.order_count,0), nvl(old.order_last_7d_reduce_amount,0.0)+nvl(1d_ago.order_reduce_amount,0.0)- nvl(7d_ago.order_reduce_amount,0.0), nvl(old.order_last_7d_original_amount,0.0)+nvl(1d_ago.order_original_amount,0.0)- nvl(7d_ago.order_original_amount,0.0), nvl(old.order_last_7d_final_amount,0.0)+nvl(1d_ago.order_final_amount,0.0)- nvl(7d_ago.order_final_amount,0.0), nvl(old.order_last_30d_count,0)+nvl(1d_ago.order_count,0)- nvl(30d_ago.order_count,0), nvl(old.order_last_30d_reduce_amount,0.0)+nvl(1d_ago.order_reduce_amount,0.0)- nvl(30d_ago.order_reduce_amount,0.0), nvl(old.order_last_30d_original_amount,0.0)+nvl(1d_ago.order_original_amount,0.0)- nvl(30d_ago.order_original_amount,0.0), nvl(old.order_last_30d_final_amount,0.0)+nvl(1d_ago.order_final_amount,0.0)- nvl(30d_ago.order_final_amount,0.0), nvl(old.order_count,0)+nvl(1d_ago.order_count,0), nvl(old.order_reduce_amount,0.0)+nvl(1d_ago.order_reduce_amount,0.0), nvl(old.order_original_amount,0.0)+nvl(1d_ago.order_original_amount,0.0), nvl(old.order_final_amount,0.0)+nvl(1d_ago.order_final_amount,0.0), nvl(old.payment_last_1d_count,0)+nvl(1d_ago.payment_count,0)- nvl(1d_ago.payment_count,0), nvl(old.payment_last_1d_reduce_amount,0.0)+nvl(1d_ago.payment_reduce_amount,0.0)- nvl(1d_ago.payment_reduce_amount,0.0), nvl(old.payment_last_1d_amount,0.0)+nvl(1d_ago.payment_amount,0.0)- nvl(1d_ago.payment_amount,0.0), nvl(old.payment_last_7d_count,0)+nvl(1d_ago.payment_count,0)- nvl(7d_ago.payment_count,0), nvl(old.payment_last_7d_reduce_amount,0.0)+nvl(1d_ago.payment_reduce_amount,0.0)- nvl(7d_ago.payment_reduce_amount,0.0), nvl(old.payment_last_7d_amount,0.0)+nvl(1d_ago.payment_amount,0.0)- nvl(7d_ago.payment_amount,0.0), nvl(old.payment_last_30d_count,0)+nvl(1d_ago.payment_count,0)- nvl(30d_ago.payment_count,0), nvl(old.payment_last_30d_reduce_amount,0.0)+nvl(1d_ago.payment_reduce_amount,0.0)- nvl(30d_ago.payment_reduce_amount,0.0), nvl(old.payment_last_30d_amount,0.0)+nvl(1d_ago.payment_amount,0.0)- nvl(30d_ago.payment_amount,0.0), nvl(old.payment_count,0)+nvl(1d_ago.payment_count,0), nvl(old.payment_reduce_amount,0.0)+nvl(1d_ago.payment_reduce_amount,0.0), nvl(old.payment_amount,0.0)+nvl(1d_ago.payment_amount,0.0), nvl(1d_ago.expire_count,0), nvl(old.expire_last_7d_count,0)+nvl(1d_ago.expire_count,0)- nvl(7d_ago.expire_count,0), nvl(old.expire_last_30d_count,0)+nvl(1d_ago.expire_count,0)- nvl(30d_ago.expire_count,0), nvl(old.expire_count,0)+nvl(1d_ago.expire_count,0) from ( select coupon_id, get_last_1d_count, get_last_7d_count, get_last_30d_count, get_count, order_last_1d_count, order_last_1d_reduce_amount, order_last_1d_original_amount, order_last_1d_final_amount, order_last_7d_count, order_last_7d_reduce_amount, order_last_7d_original_amount, order_last_7d_final_amount, order_last_30d_count, order_last_30d_reduce_amount, order_last_30d_original_amount, order_last_30d_final_amount, order_count, order_reduce_amount, order_original_amount, order_final_amount, payment_last_1d_count, payment_last_1d_reduce_amount, payment_last_1d_amount, payment_last_7d_count, payment_last_7d_reduce_amount, payment_last_7d_amount, payment_last_30d_count, payment_last_30d_reduce_amount, payment_last_30d_amount, payment_count, payment_reduce_amount, payment_amount, expire_last_1d_count, expire_last_7d_count, expire_last_30d_count, expire_count from ${APP}.dwt_coupon_topic where dt=date_add('$do_date',-1) )old full outer join ( select coupon_id, get_count, order_count, order_reduce_amount, order_original_amount, order_final_amount, payment_count, payment_reduce_amount, payment_amount, expire_count from ${APP}.dws_coupon_info_daycount where dt='$do_date' )1d_ago on old.coupon_id=1d_ago.coupon_id left join ( select coupon_id, get_count, order_count, order_reduce_amount, order_original_amount, order_final_amount, payment_count, payment_reduce_amount, payment_amount, expire_count from ${APP}.dws_coupon_info_daycount where dt=date_add('$do_date',-7) )7d_ago on old.coupon_id=7d_ago.coupon_id left join ( select coupon_id, get_count, order_count, order_reduce_amount, order_original_amount, order_final_amount, payment_count, payment_reduce_amount, payment_amount, expire_count from ${APP}.dws_coupon_info_daycount where dt=date_add('$do_date',-30) )30d_ago on old.coupon_id=30d_ago.coupon_id; alter table ${APP}.dwt_coupon_topic drop partition(dt='$clear_date'); " dwt_area_topic=" insert overwrite table ${APP}.dwt_area_topic partition(dt='$do_date') select nvl(old.province_id, 1d_ago.province_id), nvl(1d_ago.visit_count,0), nvl(1d_ago.login_count,0), nvl(old.visit_last_7d_count,0)+nvl(1d_ago.visit_count,0)- nvl(7d_ago.visit_count,0), nvl(old.login_last_7d_count,0)+nvl(1d_ago.login_count,0)- nvl(7d_ago.login_count,0), nvl(old.visit_last_30d_count,0)+nvl(1d_ago.visit_count,0)- nvl(30d_ago.visit_count,0), nvl(old.login_last_30d_count,0)+nvl(1d_ago.login_count,0)- nvl(30d_ago.login_count,0), nvl(old.visit_count,0)+nvl(1d_ago.visit_count,0), nvl(old.login_count,0)+nvl(1d_ago.login_count,0), nvl(1d_ago.order_count,0), nvl(1d_ago.order_original_amount,0.0), nvl(1d_ago.order_final_amount,0.0), nvl(old.order_last_7d_count,0)+nvl(1d_ago.order_count,0)- nvl(7d_ago.order_count,0), nvl(old.order_last_7d_original_amount,0.0)+nvl(1d_ago.order_original_amount,0.0)- nvl(7d_ago.order_original_amount,0.0), nvl(old.order_last_7d_final_amount,0.0)+nvl(1d_ago.order_final_amount,0.0)- nvl(7d_ago.order_final_amount,0.0), nvl(old.order_last_30d_count,0)+nvl(1d_ago.order_count,0)- nvl(30d_ago.order_count,0), nvl(old.order_last_30d_original_amount,0.0)+nvl(1d_ago.order_original_amount,0.0)- nvl(30d_ago.order_original_amount,0.0), nvl(old.order_last_30d_final_amount,0.0)+nvl(1d_ago.order_final_amount,0.0)- nvl(30d_ago.order_final_amount,0.0), nvl(old.order_count,0)+nvl(1d_ago.order_count,0), nvl(old.order_original_amount,0.0)+nvl(1d_ago.order_original_amount,0.0), nvl(old.order_final_amount,0.0)+nvl(1d_ago.order_final_amount,0.0), nvl(1d_ago.payment_count,0), nvl(1d_ago.payment_amount,0.0), nvl(old.payment_last_7d_count,0)+nvl(1d_ago.payment_count,0)- nvl(7d_ago.payment_count,0), nvl(old.payment_last_7d_amount,0.0)+nvl(1d_ago.payment_amount,0.0)- nvl(7d_ago.payment_amount,0.0), nvl(old.payment_last_30d_count,0)+nvl(1d_ago.payment_count,0)- nvl(30d_ago.payment_count,0), nvl(old.payment_last_30d_amount,0.0)+nvl(1d_ago.payment_amount,0.0)- nvl(30d_ago.payment_amount,0.0), nvl(old.payment_count,0)+nvl(1d_ago.payment_count,0), nvl(old.payment_amount,0.0)+nvl(1d_ago.payment_amount,0.0), nvl(1d_ago.refund_order_count,0), nvl(1d_ago.refund_order_amount,0.0), nvl(old.refund_order_last_7d_count,0)+nvl(1d_ago.refund_order_count,0)- nvl(7d_ago.refund_order_count,0), nvl(old.refund_order_last_7d_amount,0.0)+nvl(1d_ago.refund_order_amount,0.0)- nvl(7d_ago.refund_order_amount,0.0), nvl(old.refund_order_last_30d_count,0)+nvl(1d_ago.refund_order_count,0)- nvl(30d_ago.refund_order_count,0), nvl(old.refund_order_last_30d_amount,0.0)+nvl(1d_ago.refund_order_amount,0.0)- nvl(30d_ago.refund_order_amount,0.0), nvl(old.refund_order_count,0)+nvl(1d_ago.refund_order_count,0), nvl(old.refund_order_amount,0.0)+nvl(1d_ago.refund_order_amount,0.0), nvl(1d_ago.refund_payment_count,0), nvl(1d_ago.refund_payment_amount,0.0), nvl(old.refund_payment_last_7d_count,0)+nvl(1d_ago.refund_payment_count,0)- nvl(7d_ago.refund_payment_count,0), nvl(old.refund_payment_last_7d_amount,0.0)+nvl(1d_ago.refund_payment_amount,0.0)- nvl(7d_ago.refund_payment_amount,0.0), nvl(old.refund_payment_last_30d_count,0)+nvl(1d_ago.refund_payment_count,0)- nvl(30d_ago.refund_payment_count,0), nvl(old.refund_payment_last_30d_amount,0.0)+nvl(1d_ago.refund_payment_amount,0.0)- nvl(30d_ago.refund_payment_amount,0.0), nvl(old.refund_payment_count,0)+nvl(1d_ago.refund_payment_count,0), nvl(old.refund_payment_amount,0.0)+nvl(1d_ago.refund_payment_amount,0.0) from ( select province_id, visit_last_1d_count, login_last_1d_count, visit_last_7d_count, login_last_7d_count, visit_last_30d_count, login_last_30d_count, visit_count, login_count, order_last_1d_count, order_last_1d_original_amount, order_last_1d_final_amount, order_last_7d_count, order_last_7d_original_amount, order_last_7d_final_amount, order_last_30d_count, order_last_30d_original_amount, order_last_30d_final_amount, order_count, order_original_amount, order_final_amount, payment_last_1d_count, payment_last_1d_amount, payment_last_7d_count, payment_last_7d_amount, payment_last_30d_count, payment_last_30d_amount, payment_count, payment_amount, refund_order_last_1d_count, refund_order_last_1d_amount, refund_order_last_7d_count, refund_order_last_7d_amount, refund_order_last_30d_count, refund_order_last_30d_amount, refund_order_count, refund_order_amount, refund_payment_last_1d_count, refund_payment_last_1d_amount, refund_payment_last_7d_count, refund_payment_last_7d_amount, refund_payment_last_30d_count, refund_payment_last_30d_amount, refund_payment_count, refund_payment_amount from ${APP}.dwt_area_topic where dt=date_add('$do_date',-1) )old full outer join ( select province_id, visit_count, login_count, order_count, order_original_amount, order_final_amount, payment_count, payment_amount, refund_order_count, refund_order_amount, refund_payment_count, refund_payment_amount from ${APP}.dws_area_stats_daycount where dt='$do_date' )1d_ago on old.province_id=1d_ago.province_id left join ( select province_id, visit_count, login_count, order_count, order_original_amount, order_final_amount, payment_count, payment_amount, refund_order_count, refund_order_amount, refund_payment_count, refund_payment_amount from ${APP}.dws_area_stats_daycount where dt=date_add('$do_date',-7) )7d_ago on old.province_id= 7d_ago.province_id left join ( select province_id, visit_count, login_count, order_count, order_original_amount, order_final_amount, payment_count, payment_amount, refund_order_count, refund_order_amount, refund_payment_count, refund_payment_amount from ${APP}.dws_area_stats_daycount where dt=date_add('$do_date',-30) )30d_ago on old.province_id= 30d_ago.province_id; alter table ${APP}.dwt_area_topic drop partition(dt='$clear_date'); " case $1 in "dwt_visitor_topic" ) hive -e "$dwt_visitor_topic" hadoop fs -rm -r -f /warehouse/gmall/dwt/dwt_visitor_topic/dt=$clear_date ;; "dwt_user_topic" ) hive -e "$dwt_user_topic" hadoop fs -rm -r -f /warehouse/gmall/dwt/dwt_user_topic/dt=$clear_date ;; "dwt_sku_topic" ) hive -e "$dwt_sku_topic" hadoop fs -rm -r -f /warehouse/gmall/dwt/dwt_sku_topic/dt=$clear_date ;; "dwt_activity_topic" ) hive -e "$dwt_activity_topic" hadoop fs -rm -r -f /warehouse/gmall/dwt/dwt_activity_topic/dt=$clear_date ;; "dwt_coupon_topic" ) hive -e "$dwt_coupon_topic" hadoop fs -rm -r -f /warehouse/gmall/dwt/dwt_coupon_topic/dt=$clear_date ;; "dwt_area_topic" ) hive -e "$dwt_area_topic" hadoop fs -rm -r -f /warehouse/gmall/dwt/dwt_area_topic/dt=$clear_date ;; "all" ) hive -e "$dwt_visitor_topic$dwt_user_topic$dwt_sku_topic$dwt_activity_topic$dwt_coupon_topic$dwt_area_topic" hadoop fs -rm -r -f /warehouse/gmall/dwt/dwt_visitor_topic/dt=$clear_date hadoop fs -rm -r -f /warehouse/gmall/dwt/dwt_user_topic/dt=$clear_date hadoop fs -rm -r -f /warehouse/gmall/dwt/dwt_sku_topic/dt=$clear_date hadoop fs -rm -r -f /warehouse/gmall/dwt/dwt_activity_topic/dt=$clear_date hadoop fs -rm -r -f /warehouse/gmall/dwt/dwt_coupon_topic/dt=$clear_date hadoop fs -rm -r -f /warehouse/gmall/dwt/dwt_area_topic/dt=$clear_date ;; esac- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
- 229
- 230
- 231
- 232
- 233
- 234
- 235
- 236
- 237
- 238
- 239
- 240
- 241
- 242
- 243
- 244
- 245
- 246
- 247
- 248
- 249
- 250
- 251
- 252
- 253
- 254
- 255
- 256
- 257
- 258
- 259
- 260
- 261
- 262
- 263
- 264
- 265
- 266
- 267
- 268
- 269
- 270
- 271
- 272
- 273
- 274
- 275
- 276
- 277
- 278
- 279
- 280
- 281
- 282
- 283
- 284
- 285
- 286
- 287
- 288
- 289
- 290
- 291
- 292
- 293
- 294
- 295
- 296
- 297
- 298
- 299
- 300
- 301
- 302
- 303
- 304
- 305
- 306
- 307
- 308
- 309
- 310
- 311
- 312
- 313
- 314
- 315
- 316
- 317
- 318
- 319
- 320
- 321
- 322
- 323
- 324
- 325
- 326
- 327
- 328
- 329
- 330
- 331
- 332
- 333
- 334
- 335
- 336
- 337
- 338
- 339
- 340
- 341
- 342
- 343
- 344
- 345
- 346
- 347
- 348
- 349
- 350
- 351
- 352
- 353
- 354
- 355
- 356
- 357
- 358
- 359
- 360
- 361
- 362
- 363
- 364
- 365
- 366
- 367
- 368
- 369
- 370
- 371
- 372
- 373
- 374
- 375
- 376
- 377
- 378
- 379
- 380
- 381
- 382
- 383
- 384
- 385
- 386
- 387
- 388
- 389
- 390
- 391
- 392
- 393
- 394
- 395
- 396
- 397
- 398
- 399
- 400
- 401
- 402
- 403
- 404
- 405
- 406
- 407
- 408
- 409
- 410
- 411
- 412
- 413
- 414
- 415
- 416
- 417
- 418
- 419
- 420
- 421
- 422
- 423
- 424
- 425
- 426
- 427
- 428
- 429
- 430
- 431
- 432
- 433
- 434
- 435
- 436
- 437
- 438
- 439
- 440
- 441
- 442
- 443
- 444
- 445
- 446
- 447
- 448
- 449
- 450
- 451
- 452
- 453
- 454
- 455
- 456
- 457
- 458
- 459
- 460
- 461
- 462
- 463
- 464
- 465
- 466
- 467
- 468
- 469
- 470
- 471
- 472
- 473
- 474
- 475
- 476
- 477
- 478
- 479
- 480
- 481
- 482
- 483
- 484
- 485
- 486
- 487
- 488
- 489
- 490
- 491
- 492
- 493
- 494
- 495
- 496
- 497
- 498
- 499
- 500
- 501
- 502
- 503
- 504
- 505
- 506
- 507
- 508
- 509
- 510
- 511
- 512
- 513
- 514
- 515
- 516
- 517
- 518
- 519
- 520
- 521
- 522
- 523
- 524
- 525
- 526
- 527
- 528
- 529
- 530
- 531
- 532
- 533
- 534
- 535
- 536
- 537
- 538
- 539
- 540
- 541
- 542
- 543
- 544
- 545
- 546
- 547
- 548
- 549
- 550
- 551
- 552
- 553
- 554
- 555
- 556
- 557
- 558
- 559
- 560
- 561
- 562
- 563
- 564
- 565
- 566
- 567
- 568
- 569
- 570
- 571
- 572
- 573
- 574
- 575
- 576
- 577
- 578
- 579
- 580
- 581
- 582
- 583
- 584
- 585
- 586
- 587
- 588
- 589
- 590
- 591
- 592
- 593
- 594
- 595
- 596
- 597
- 598
- 599
- 600
- 601
- 602
- 603
- 604
- 605
- 606
- 607
- 608
- 609
- 610
- 611
- 612
- 613
- 614
- 615
- 616
- 617
- 618
- 619
- 620
- 621
- 622
- 623
- 624
- 625
- 626
- 627
- 628
- 629
- 630
- 631
- 632
- 633
- 634
- 635
- 636
- 637
- 638
- 639
- 640
- 641
- 642
- 643
- 644
- 645
- 646
- 647
- 648
- 649
- 650
- 651
- 652
- 653
- 654
- 655
- 656
- 657
- 658
- 659
- 660
- 661
- 662
- 663
- 664
- 665
- 666
- 667
- 668
- 669
- 670
- 671
- 672
- 673
- 674
- 675
- 676
- 677
- 678
- 679
- 680
- 681
- 682
- 683
- 684
- 685
- 686
- 687
- 688
- 689
- 690
- 691
- 692
- 693
- 694
- 695
- 696
- 697
- 698
- 699
- 700
- 701
- 702
- 703
- 704
- 705
- 706
- 707
- 708
- 709
- 710
- 711
- 712
- 713
- 714
- 715
- 716
- 717
- 718
- 719
- 720
- 721
- 722
- 723
- 724
- 725
- 726
- 727
- 728
- 729
- 730
- 731
- 732
- 733
- 734
- 735
- 736
- 737
- 738
- 739
- 740
- 741
- 742
- 743
- 744
- 745
- 746
- 747
- 748
- 749
- 750
- 751
- 752
- 753
- 754
- 755
- 756
- 757
- 758
- 759
- 760
- 761
- 762
- 763
- 764
- 765
- 766
- 767
- 768
- 769
- 770
- 771
- 772
- 773
- 774
- 775
- 776
- 777
- 778
- 779
- 780
- 781
- 782
- 783
- 784
- 785
- 786
- 787
- 788
- 789
- 790
- 791
- 792
- 793
- 794
- 795
- 796
- 797
- 798
- 799
- 800
- 801
- 802
- 803
- 804
- 805
- 806
- 807
- 808
- 809
- 810
- 811
- 812
- 813
- 814
- 815
- 816
- 817
- 818
- 819
- 820
- 821
- 822
- 823
- 824
- 825
- 826
- 827
- 828
- 829
- 830
- 831
- 832
- 833
- 834
- 835
- 836
- 837
- 838
- 839
- 840
- 841
- 842
- 843
- 844
- 845
- 846
- 847
- 848
- 849
- 850
- 851
- 852
- 853
- 854
- 855
- 856
- 857
- 858
- 859
- 860
- 861
- 862
- 863
- 864
- 865
- 866
- 867
- 868
- 869
- 870
- 871
- 872
- 873
- 874
- 875
- 876
- 877
- 878
- 879
- 880
- 881
- 882
- 883
- 884
- 885
- 886
- 887
- 888
- 889
- 890
- 891
- 892
- 893
- 894
- 895
- 896
- 897
- 898
- 899
- 900
- 901
- 902
- 903
- 904
- 905
- 906
- 907
- 908
- 909
- 910
- 911
- 912
- 913
- 914
- 915
- 916
- 917
- 918
- 919
- 920
- 921
- 922
- 923
- 924
- 925
- 926
- 927
- 928
- 929
- 930
- 931
- 932
- 933
- 934
- 935
- 936
- 937
- 938
- 939
- 940
- 941
- 942
- 943
- 944
- 945
- 946
- 947
- 948
- 949
- 950
- 951
- 952
- 953
- 954
- 955
- 956
- 957
- 958
- 959
- 960
- 961
- 962
- 963
- 964
- 965
- 966
- 967
- 968
- 969
- 970
- 971
- 972
- 973
- 974
- 975
- 976
- 977
- 978
- 979
- 980
- 981
- 982
- 983
- 984
- 985
- 986
- 987
- 988
- 989
- 990
- 991
- 992
- 993
- 994
- 995
- 996
- 997
- 998
- 999
- 1000
- 1001
- 1002
- 1003
- 1004
- 1005
- 1006
- 1007
- 1008
- 1009
- 1010
- 1011
- 1012
- 1013
- 1014
- 1015
- 1016
- 1017
- 1018
- 1019
- 1020
- 1021
- 1022
- 1023
- 1024
- 1025
- 1026
- 1027
- 1028
- 1029
- 1030
- 1031
- 1032
- 1033
- 1034
- 1035
- 1036
- 1037
- 1038
- 1039
- 1040
- 1041
- 1042
- 1043
- 1044
- 1045
- 1046
- 1047
- 1048
- 1049
- 1050
- 1051
- 1052
- 1053
- 1054
- 1055
- 1056
- 1057
- 1058
- 1059
- 1060
- 1061
- 1062
- 1063
- 1064
- 1065
- 1066
- 1067
- 1068
- 1069
- 1070
- 1071
- 1072
- 1073
- 1074
- 1075
- 1076
- 1077
- 1078
- 1079
- 1080
- 1081
- 1082
- 1083
- 1084
- 1085
- 1086
- 1087
- 1088
- 1089
- 1090
- 1091
- 1092
- 1093
- 1094
- 1095
- 1096
- 1097
- 1098
- 1099
- 1100
- 1101
- 1102
- 1103
- 1104
- 1105
- 1106
- 1107
- 1108
- 1109
- 1110
- 1111
- 1112
- 1113
- 1114
- 1115
- 1116
- 1117
- 1118
（2）增加脚本执行权限
[lyh@hadoop102 bin]$ chmod 777 dws_to_dwt.sh- 1
2）脚本使用
（1）执行脚本[lyh@hadoop102 bin]$ dws_to_dwt.sh 2020-06-14- 1
（2）查看导入数据
四、数仓搭建-ADS层
1、ADS层业务数据导入脚本
1）编写脚本
（1）在/home/lyh/bin目录下创建脚本dwt_to_ads.sh[lyh@hadoop102 bin]$ vim dwt_to_ads.sh- 1
在脚本中填写如下内容
#!/bin/bash APP=gmall # 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天 if [ -n "$2" ] ;then do_date=$2 else do_date=`date -d "-1 day" +%F` fi ads_activity_stats=" insert overwrite table ${APP}.ads_activity_stats select * from ${APP}.ads_activity_stats union select '$do_date' dt, t4.activity_id, activity_name, start_date, order_count, order_original_amount, order_final_amount, reduce_amount, reduce_rate from ( select activity_id, activity_name, date_format(start_time,'yyyy-MM-dd') start_date from ${APP}.dim_activity_rule_info where dt='$do_date' and date_format(start_time,'yyyy-MM-dd')>=date_add('$do_date',-29) group by activity_id,activity_name,start_time )t4 left join ( select activity_id, sum(order_count) order_count, sum(order_original_amount) order_original_amount, sum(order_final_amount) order_final_amount, sum(order_reduce_amount) reduce_amount, cast(sum(order_reduce_amount)/sum(order_original_amount)*100 as decimal(16,2)) reduce_rate from ${APP}.dwt_activity_topic where dt='$do_date' group by activity_id )t5 on t4.activity_id=t5.activity_id; " ads_coupon_stats=" insert overwrite table ${APP}.ads_coupon_stats select * from ${APP}.ads_coupon_stats union select '$do_date' dt, t1.id, coupon_name, start_date, rule_name, get_count, order_count, expire_count, order_original_amount, order_final_amount, reduce_amount, reduce_rate from ( select id, coupon_name, date_format(start_time,'yyyy-MM-dd') start_date, case when coupon_type='3201' then concat('满',condition_amount,'元减',benefit_amount,'元') when coupon_type='3202' then concat('满',condition_num,'件打', (1-benefit_discount)*10,'折') when coupon_type='3203' then concat('减',benefit_amount,'元') end rule_name from ${APP}.dim_coupon_info where dt='$do_date' and date_format(start_time,'yyyy-MM-dd')>=date_add('$do_date',-29) )t1 left join ( select coupon_id, get_count, order_count, expire_count, order_original_amount, order_final_amount, order_reduce_amount reduce_amount, cast(order_reduce_amount/order_original_amount as decimal(16,2)) reduce_rate from ${APP}.dwt_coupon_topic where dt='$do_date' )t2 on t1.id=t2.coupon_id; " ads_order_by_province=" insert overwrite table ${APP}.ads_order_by_province select * from ${APP}.ads_order_by_province union select dt, recent_days, province_id, province_name, area_code, iso_code, iso_3166_2, order_count, order_amount from ( select '$do_date' dt, recent_days, province_id, sum(order_count) order_count, sum(order_amount) order_amount from ( select recent_days, province_id, case when recent_days=1 then order_last_1d_count when recent_days=7 then order_last_7d_count when recent_days=30 then order_last_30d_count end order_count, case when recent_days=1 then order_last_1d_final_amount when recent_days=7 then order_last_7d_final_amount when recent_days=30 then order_last_30d_final_amount end order_amount from ${APP}.dwt_area_topic lateral view explode(Array(1,7,30)) tmp as recent_days where dt='$do_date' )t1 group by recent_days,province_id )t2 join ${APP}.dim_base_province t3 on t2.province_id=t3.id; " ads_order_spu_stats=" insert overwrite table ${APP}.ads_order_spu_stats select * from ${APP}.ads_order_spu_stats union select '$do_date' dt, recent_days, spu_id, spu_name, tm_id, tm_name, category3_id, category3_name, category2_id, category2_name, category1_id, category1_name, sum(order_count), sum(order_amount) from ( select recent_days, sku_id, case when recent_days=1 then order_last_1d_count when recent_days=7 then order_last_7d_count when recent_days=30 then order_last_30d_count end order_count, case when recent_days=1 then order_last_1d_final_amount when recent_days=7 then order_last_7d_final_amount when recent_days=30 then order_last_30d_final_amount end order_amount from ${APP}.dwt_sku_topic lateral view explode(Array(1,7,30)) tmp as recent_days where dt='$do_date' )t1 left join ( select id, spu_id, spu_name, tm_id, tm_name, category3_id, category3_name, category2_id, category2_name, category1_id, category1_name from ${APP}.dim_sku_info where dt='$do_date' )t2 on t1.sku_id=t2.id group by recent_days,spu_id,spu_name,tm_id,tm_name,category3_id,category3_name,category2_id,category2_name,category1_id,category1_name; " ads_order_total=" insert overwrite table ${APP}.ads_order_total select * from ${APP}.ads_order_total union select '$do_date', recent_days, sum(order_count), sum(order_final_amount) order_final_amount, sum(if(order_final_amount>0,1,0)) order_user_count from ( select recent_days, user_id, case when recent_days=0 then order_count when recent_days=1 then order_last_1d_count when recent_days=7 then order_last_7d_count when recent_days=30 then order_last_30d_count end order_count, case when recent_days=0 then order_final_amount when recent_days=1 then order_last_1d_final_amount when recent_days=7 then order_last_7d_final_amount when recent_days=30 then order_last_30d_final_amount end order_final_amount from ${APP}.dwt_user_topic lateral view explode(Array(1,7,30)) tmp as recent_days where dt='$do_date' )t1 group by recent_days; " ads_page_path=" insert overwrite table ${APP}.ads_page_path select * from ${APP}.ads_page_path union select '$do_date', recent_days, source, target, count(*) from ( select recent_days, concat('step-',step,':',source) source, concat('step-',step+1,':',target) target from ( select recent_days, page_id source, lead(page_id,1,null) over (partition by recent_days,session_id order by ts) target, row_number() over (partition by recent_days,session_id order by ts) step from ( select recent_days, last_page_id, page_id, ts, concat(mid_id,'-',last_value(if(last_page_id is null,ts,null),true) over (partition by mid_id,recent_days order by ts)) session_id from ${APP}.dwd_page_log lateral view explode(Array(1,7,30)) tmp as recent_days where dt>=date_add('$do_date',-30) and dt>=date_add('$do_date',-recent_days+1) )t2 )t3 )t4 group by recent_days,source,target; " ads_repeat_purchase=" insert overwrite table ${APP}.ads_repeat_purchase select * from ${APP}.ads_repeat_purchase union select '$do_date' dt, recent_days, tm_id, tm_name, cast(sum(if(order_count>=2,1,0))/sum(if(order_count>=1,1,0))*100 as decimal(16,2)) from ( select recent_days, user_id, tm_id, tm_name, sum(order_count) order_count from ( select recent_days, user_id, sku_id, count(*) order_count from ${APP}.dwd_order_detail lateral view explode(Array(1,7,30)) tmp as recent_days where dt>=date_add('$do_date',-29) and dt>=date_add('$do_date',-recent_days+1) group by recent_days, user_id,sku_id )t1 left join ( select id, tm_id, tm_name from ${APP}.dim_sku_info where dt='$do_date' )t2 on t1.sku_id=t2.id group by recent_days,user_id,tm_id,tm_name )t3 group by recent_days,tm_id,tm_name; " ads_user_action=" with tmp_page as ( select '$do_date' dt, recent_days, sum(if(array_contains(pages,'home'),1,0)) home_count, sum(if(array_contains(pages,'good_detail'),1,0)) good_detail_count from ( select recent_days, mid_id, collect_set(page_id) pages from ( select dt, mid_id, page.page_id from ${APP}.dws_visitor_action_daycount lateral view explode(page_stats) tmp as page where dt>=date_add('$do_date',-29) and page.page_id in('home','good_detail') )t1 lateral view explode(Array(1,7,30)) tmp as recent_days where dt>=date_add('$do_date',-recent_days+1) group by recent_days,mid_id )t2 group by recent_days ), tmp_cop as ( select '$do_date' dt, recent_days, sum(if(cart_count>0,1,0)) cart_count, sum(if(order_count>0,1,0)) order_count, sum(if(payment_count>0,1,0)) payment_count from ( select recent_days, user_id, case when recent_days=1 then cart_last_1d_count when recent_days=7 then cart_last_7d_count when recent_days=30 then cart_last_30d_count end cart_count, case when recent_days=1 then order_last_1d_count when recent_days=7 then order_last_7d_count when recent_days=30 then order_last_30d_count end order_count, case when recent_days=1 then payment_last_1d_count when recent_days=7 then payment_last_7d_count when recent_days=30 then payment_last_30d_count end payment_count from ${APP}.dwt_user_topic lateral view explode(Array(1,7,30)) tmp as recent_days where dt='$do_date' )t1 group by recent_days ) insert overwrite table ${APP}.ads_user_action select * from ${APP}.ads_user_action union select tmp_page.dt, tmp_page.recent_days, home_count, good_detail_count, cart_count, order_count, payment_count from tmp_page join tmp_cop on tmp_page.recent_days=tmp_cop.recent_days; " ads_user_change=" insert overwrite table ${APP}.ads_user_change select * from ${APP}.ads_user_change union select churn.dt, user_churn_count, user_back_count from ( select '$do_date' dt, count(*) user_churn_count from ${APP}.dwt_user_topic where dt='$do_date' and login_date_last=date_add('$do_date',-7) )churn join ( select '$do_date' dt, count(*) user_back_count from ( select user_id, login_date_last from ${APP}.dwt_user_topic where dt='$do_date' and login_date_last='$do_date' )t1 join ( select user_id, login_date_last login_date_previous from ${APP}.dwt_user_topic where dt=date_add('$do_date',-1) )t2 on t1.user_id=t2.user_id where datediff(login_date_last,login_date_previous)>=8 )back on churn.dt=back.dt; " ads_user_retention=" insert overwrite table ${APP}.ads_user_retention select * from ${APP}.ads_user_retention union select '$do_date', login_date_first create_date, datediff('$do_date',login_date_first) retention_day, sum(if(login_date_last='$do_date',1,0)) retention_count, count(*) new_user_count, cast(sum(if(login_date_last='$do_date',1,0))/count(*)*100 as decimal(16,2)) retention_rate from ${APP}.dwt_user_topic where dt='$do_date' and login_date_first>=date_add('$do_date',-7) and login_date_first<'$do_date' group by login_date_first; " ads_user_total=" insert overwrite table ${APP}.ads_user_total select * from ${APP}.ads_user_total union select '$do_date', recent_days, sum(if(login_date_first>=recent_days_ago,1,0)) new_user_count, sum(if(order_date_first>=recent_days_ago,1,0)) new_order_user_count, sum(order_final_amount) order_final_amount, sum(if(order_final_amount>0,1,0)) order_user_count, sum(if(login_date_last>=recent_days_ago and order_final_amount=0,1,0)) no_order_user_count from ( select recent_days, user_id, login_date_first, login_date_last, order_date_first, case when recent_days=0 then order_final_amount when recent_days=1 then order_last_1d_final_amount when recent_days=7 then order_last_7d_final_amount when recent_days=30 then order_last_30d_final_amount end order_final_amount, if(recent_days=0,'1970-01-01',date_add('$do_date',-recent_days+1)) recent_days_ago from ${APP}.dwt_user_topic lateral view explode(Array(0,1,7,30)) tmp as recent_days where dt='$do_date' )t1 group by recent_days; " ads_visit_stats=" insert overwrite table ${APP}.ads_visit_stats select * from ${APP}.ads_visit_stats union select '$do_date' dt, is_new, recent_days, channel, count(distinct(mid_id)) uv_count, cast(sum(duration)/1000 as bigint) duration_sec, cast(avg(duration)/1000 as bigint) avg_duration_sec, sum(page_count) page_count, cast(avg(page_count) as bigint) avg_page_count, count(*) sv_count, sum(if(page_count=1,1,0)) bounce_count, cast(sum(if(page_count=1,1,0))/count(*)*100 as decimal(16,2)) bounce_rate from ( select session_id, mid_id, is_new, recent_days, channel, count(*) page_count, sum(during_time) duration from ( select mid_id, channel, recent_days, is_new, last_page_id, page_id, during_time, concat(mid_id,'-',last_value(if(last_page_id is null,ts,null),true) over (partition by recent_days,mid_id order by ts)) session_id from ( select mid_id, channel, last_page_id, page_id, during_time, ts, recent_days, if(visit_date_first>=date_add('$do_date',-recent_days+1),'1','0') is_new from ( select t1.mid_id, t1.channel, t1.last_page_id, t1.page_id, t1.during_time, t1.dt, t1.ts, t2.visit_date_first from ( select mid_id, channel, last_page_id, page_id, during_time, dt, ts from ${APP}.dwd_page_log where dt>=date_add('$do_date',-30) )t1 left join ( select mid_id, visit_date_first from ${APP}.dwt_visitor_topic where dt='$do_date' )t2 on t1.mid_id=t2.mid_id )t3 lateral view explode(Array(1,7,30)) tmp as recent_days where dt>=date_add('$do_date',-recent_days+1) )t4 )t5 group by session_id,mid_id,is_new,recent_days,channel )t6 group by is_new,recent_days,channel; " case $1 in "ads_activity_stats" ) hive -e "$ads_activity_stats" ;; "ads_coupon_stats" ) hive -e "$ads_coupon_stats" ;; "ads_order_by_province" ) hive -e "$ads_order_by_province" ;; "ads_order_spu_stats" ) hive -e "$ads_order_spu_stats" ;; "ads_order_total" ) hive -e "$ads_order_total" ;; "ads_page_path" ) hive -e "$ads_page_path" ;; "ads_repeat_purchase" ) hive -e "$ads_repeat_purchase" ;; "ads_user_action" ) hive -e "$ads_user_action" ;; "ads_user_change" ) hive -e "$ads_user_change" ;; "ads_user_retention" ) hive -e "$ads_user_retention" ;; "ads_user_total" ) hive -e "$ads_user_total" ;; "ads_visit_stats" ) hive -e "$ads_visit_stats" ;; "all" ) hive -e "$ads_activity_stats$ads_coupon_stats$ads_order_by_province$ads_order_spu_stats$ads_order_total$ads_page_path$ads_repeat_purchase$ads_user_action$ads_user_change$ads_user_retention$ads_user_total$ads_visit_stats" ;; esac- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
- 229
- 230
- 231
- 232
- 233
- 234
- 235
- 236
- 237
- 238
- 239
- 240
- 241
- 242
- 243
- 244
- 245
- 246
- 247
- 248
- 249
- 250
- 251
- 252
- 253
- 254
- 255
- 256
- 257
- 258
- 259
- 260
- 261
- 262
- 263
- 264
- 265
- 266
- 267
- 268
- 269
- 270
- 271
- 272
- 273
- 274
- 275
- 276
- 277
- 278
- 279
- 280
- 281
- 282
- 283
- 284
- 285
- 286
- 287
- 288
- 289
- 290
- 291
- 292
- 293
- 294
- 295
- 296
- 297
- 298
- 299
- 300
- 301
- 302
- 303
- 304
- 305
- 306
- 307
- 308
- 309
- 310
- 311
- 312
- 313
- 314
- 315
- 316
- 317
- 318
- 319
- 320
- 321
- 322
- 323
- 324
- 325
- 326
- 327
- 328
- 329
- 330
- 331
- 332
- 333
- 334
- 335
- 336
- 337
- 338
- 339
- 340
- 341
- 342
- 343
- 344
- 345
- 346
- 347
- 348
- 349
- 350
- 351
- 352
- 353
- 354
- 355
- 356
- 357
- 358
- 359
- 360
- 361
- 362
- 363
- 364
- 365
- 366
- 367
- 368
- 369
- 370
- 371
- 372
- 373
- 374
- 375
- 376
- 377
- 378
- 379
- 380
- 381
- 382
- 383
- 384
- 385
- 386
- 387
- 388
- 389
- 390
- 391
- 392
- 393
- 394
- 395
- 396
- 397
- 398
- 399
- 400
- 401
- 402
- 403
- 404
- 405
- 406
- 407
- 408
- 409
- 410
- 411
- 412
- 413
- 414
- 415
- 416
- 417
- 418
- 419
- 420
- 421
- 422
- 423
- 424
- 425
- 426
- 427
- 428
- 429
- 430
- 431
- 432
- 433
- 434
- 435
- 436
- 437
- 438
- 439
- 440
- 441
- 442
- 443
- 444
- 445
- 446
- 447
- 448
- 449
- 450
- 451
- 452
- 453
- 454
- 455
- 456
- 457
- 458
- 459
- 460
- 461
- 462
- 463
- 464
- 465
- 466
- 467
- 468
- 469
- 470
- 471
- 472
- 473
- 474
- 475
- 476
- 477
- 478
- 479
- 480
- 481
- 482
- 483
- 484
- 485
- 486
- 487
- 488
- 489
- 490
- 491
- 492
- 493
- 494
- 495
- 496
- 497
- 498
- 499
- 500
- 501
- 502
- 503
- 504
- 505
- 506
- 507
- 508
- 509
- 510
- 511
- 512
- 513
- 514
- 515
- 516
- 517
- 518
- 519
- 520
- 521
- 522
- 523
- 524
- 525
- 526
- 527
- 528
- 529
- 530
- 531
- 532
- 533
- 534
- 535
- 536
- 537
- 538
- 539
- 540
- 541
- 542
- 543
- 544
- 545
- 546
- 547
- 548
- 549
- 550
- 551
- 552
- 553
- 554
- 555
- 556
- 557
- 558
- 559
- 560
- 561
- 562
- 563
- 564
- 565
- 566
- 567
- 568
- 569
- 570
- 571
- 572
- 573
- 574
- 575
- 576
- 577
- 578
- 579
- 580
- 581
- 582
- 583
- 584
- 585
- 586
- 587
- 588
- 589
- 590
- 591
- 592
- 593
- 594
- 595
- 596
- 597
- 598
- 599
- 600
- 601
- 602
- 603
- 604
- 605
- 606
- 607
- 608
- 609
- 610
- 611
- 612
- 613
- 614
- 615
- 616
- 617
- 618
- 619
- 620
- 621
- 622
- 623
- 624
- 625
- 626
（2）增加脚本执行权限
[lyh@hadoop102 bin]$ chmod 777 dwt_to_ads.sh- 1
2）脚本使用
（1）执行脚本[lyh@hadoop102 bin]$ dwt_to_ads.sh all 2020-06-14- 1
（2）查看数据是否导入
-
相关阅读:
webpack学习
Vue项目初始化搭建JDK+Node.js+npm+vue-cli+创建Vue项目
【Kafka面试】面试题一
linux文本排序统计、快捷键和通配符及引号
CentOS 7 搭建 LVS集群 NAT模式
SqlBulkCopy - 批量写入数据库
MVC使用的设计模式
ubuntu20开启NTP服务器操作步骤
你不知道的 CSS 之包含块
基于Python的网络爬虫开发与实现
- 原文地址：https://blog.csdn.net/weixin_60440795/article/details/126683281
