数据仓库（五）——ODS层
1）保持数据原貌不做任何修改，起到备份数据的作用。
2）数据采用LZO压缩，减少磁盘存储空间。100G数据可以压缩到10G以内。
3）创建分区表，防止后续的全表扫描，在企业开发中大量使用分区表。
4）创建外部表。在企业开发中，除了自己用的临时表，创建内部表外，绝大多数场景都是创建外部表。
第一章 用户行为数据
1.1 创建日志表ods_log
1）创建支持lzo压缩的分区表
（1）建表语句
hive (gmall)> drop table if exists ods_log; CREATE EXTERNAL TABLE ods_log (`line` string) PARTITIONED BY (`dt` string) -- 按照时间创建分区 STORED AS -- 指定存储方式，读数据采用LzoTextInputFormat； INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_log' -- 指定数据在hdfs上的存储位置 ;
说明Hive的LZO压缩：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+LZO
（2）分区规划
2）加载数据
hive (gmall)>
load data inpath '/origin_data/gmall/log/topic_log/2020-06-14' into table ods_log partition(dt='2020-06-14');
注意：时间格式都配置成YYYY-MM-DD格式，这是Hive默认支持的时间格式
3）为lzo压缩文件创建索引
[atguigu@hadoop102 bin]$ hadoop jar /opt/module/hadoop-3.1.3/share/hadoop/common/hadoop-lzo-0.4.20.jar com.hadoop.compression.lzo.DistributedLzoIndexer /warehouse/gmall/ods/ods_log/dt=2020-06-14
1.2 Shell中单引号和双引号区别
1）在/home/atguigu/bin创建一个test.sh文件
[atguigu@hadoop102 bin]$ vim test.sh
在文件中添加如下内容
#!/bin/bash do_date=$1 echo '$do_date' echo "$do_date" echo "'$do_date'" echo '"$do_date"' echo `date`
2）查看执行结果
[atguigu@hadoop102 bin]$ test.sh 2020-06-14 $do_date 2020-06-14 '2020-06-14' "$do_date"
2020年 06月 18日 星期四 21:02:08 CST
3）总结：
（1）单引号不取变量值
（2）双引号取变量值
（3）反引号`，执行引号中命令
（4）双引号内部嵌套单引号，取出变量值
（5）单引号内部嵌套双引号，不取出变量值
1.3 ODS层日志表加载数据脚本
1）编写脚本
（1）在hadoop102的/home/atguigu/bin目录下创建脚本
[atguigu@hadoop102 bin]$ vim hdfs_to_ods_log.sh
在脚本中编写如下内容
#!/bin/bash # 定义变量方便修改 APP=gmall # 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天 if [ -n "$1" ] ;then do_date=$1 else do_date=`date -d "-1 day" +%F` fi echo ================== 日志日期为 $do_date ================== sql=" load data inpath '/origin_data/$APP/log/topic_log/$do_date' into table ${APP}.ods_log partition(dt='$do_date'); " hive -e "$sql" hadoop jar /opt/module/hadoop-3.1.3/share/hadoop/common/hadoop-lzo-0.4.20.jar com.hadoop.compression.lzo.DistributedLzoIndexer /warehouse/$APP/ods/ods_log/dt=$do_date
（1）说明1：
[ -n 变量值 ] 判断变量的值，是否为空
-- 变量的值，非空，返回true
-- 变量的值，为空，返回false
注意：[ -n 变量值 ]不会解析数据，使用[ -n 变量值 ]时，需要对变量加上双引号(" ")
（2）说明2：
查看date命令的使用，date --help
（3）增加脚本执行权限
[atguigu@hadoop102 bin]$ chmod 777 hdfs_to_ods_log.sh
2）脚本使用
（1）执行脚本
[atguigu@hadoop102 module]$ hdfs_to_ods_log.sh 2020-06-14
（2）查看导入数据
第二章 业务数据
ODS层业务表分区规划如下
ODS层业务表数据装载思路如下
2.1 活动信息表
DROP TABLE IF EXISTS ods_activity_info; CREATE EXTERNAL TABLE ods_activity_info( `id` STRING COMMENT '编号', `activity_name` STRING COMMENT '活动名称', `activity_type` STRING COMMENT '活动类型', `start_time` STRING COMMENT '开始时间', `end_time` STRING COMMENT '结束时间', `create_time` STRING COMMENT '创建时间' ) COMMENT '活动信息表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_activity_info/';
2.2 活动规则表
DROP TABLE IF EXISTS ods_activity_rule; CREATE EXTERNAL TABLE ods_activity_rule( `id` STRING COMMENT '编号', `activity_id` STRING COMMENT '活动ID', `activity_type` STRING COMMENT '活动类型', `condition_amount` DECIMAL(16,2) COMMENT '满减金额', `condition_num` BIGINT COMMENT '满减件数', `benefit_amount` DECIMAL(16,2) COMMENT '优惠金额', `benefit_discount` DECIMAL(16,2) COMMENT '优惠折扣', `benefit_level` STRING COMMENT '优惠级别' ) COMMENT '活动规则表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_activity_rule/';
2.3 一级品类表
DROP TABLE IF EXISTS ods_base_category1; CREATE EXTERNAL TABLE ods_base_category1( `id` STRING COMMENT 'id', `name` STRING COMMENT '名称' ) COMMENT '商品一级分类表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_base_category1/';
2.4 二级品类表
DROP TABLE IF EXISTS ods_base_category2; CREATE EXTERNAL TABLE ods_base_category2( `id` STRING COMMENT ' id', `name` STRING COMMENT '名称', `category1_id` STRING COMMENT '一级品类id' ) COMMENT '商品二级分类表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_base_category2/';
2.5 三级品类表
DROP TABLE IF EXISTS ods_base_category3; CREATE EXTERNAL TABLE ods_base_category3( `id` STRING COMMENT ' id', `name` STRING COMMENT '名称', `category2_id` STRING COMMENT '二级品类id' ) COMMENT '商品三级分类表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_base_category3/';
2.6 编码字典表
DROP TABLE IF EXISTS ods_base_dic; CREATE EXTERNAL TABLE ods_base_dic( `dic_code` STRING COMMENT '编号', `dic_name` STRING COMMENT '编码名称', `parent_code` STRING COMMENT '父编码', `create_time` STRING COMMENT '创建日期', `operate_time` STRING COMMENT '操作日期' ) COMMENT '编码字典表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_base_dic/';
2.7 省份表
DROP TABLE IF EXISTS ods_base_province; CREATE EXTERNAL TABLE ods_base_province ( `id` STRING COMMENT '编号', `name` STRING COMMENT '省份名称', `region_id` STRING COMMENT '地区ID', `area_code` STRING COMMENT '地区编码', `iso_code` STRING COMMENT 'ISO-3166编码，供可视化使用', `iso_3166_2` STRING COMMENT 'IOS-3166-2编码，供可视化使用' ) COMMENT '省份表' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_base_province/';
2.8 地区表
DROP TABLE IF EXISTS ods_base_region; CREATE EXTERNAL TABLE ods_base_region ( `id` STRING COMMENT '编号', `region_name` STRING COMMENT '地区名称' ) COMMENT '地区表' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_base_region/';
2.9 品牌表
DROP TABLE IF EXISTS ods_base_trademark; CREATE EXTERNAL TABLE ods_base_trademark ( `id` STRING COMMENT '编号', `tm_name` STRING COMMENT '品牌名称' ) COMMENT '品牌表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_base_trademark/';
2.10 购物车表
DROP TABLE IF EXISTS ods_cart_info; CREATE EXTERNAL TABLE ods_cart_info( `id` STRING COMMENT '编号', `user_id` STRING COMMENT '用户id', `sku_id` STRING COMMENT 'skuid', `cart_price` DECIMAL(16,2) COMMENT '放入购物车时价格', `sku_num` BIGINT COMMENT '数量', `sku_name` STRING COMMENT 'sku名称 (冗余)', `create_time` STRING COMMENT '创建时间', `operate_time` STRING COMMENT '修改时间', `is_ordered` STRING COMMENT '是否已经下单', `order_time` STRING COMMENT '下单时间', `source_type` STRING COMMENT '来源类型', `source_id` STRING COMMENT '来源编号' ) COMMENT '加购表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_cart_info/';
2.11 评论表
DROP TABLE IF EXISTS ods_comment_info; CREATE EXTERNAL TABLE ods_comment_info( `id` STRING COMMENT '编号', `user_id` STRING COMMENT '用户ID', `sku_id` STRING COMMENT '商品sku', `spu_id` STRING COMMENT '商品spu', `order_id` STRING COMMENT '订单ID', `appraise` STRING COMMENT '评价', `create_time` STRING COMMENT '评价时间' ) COMMENT '商品评论表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_comment_info/';
2.12 优惠券信息表
DROP TABLE IF EXISTS ods_coupon_info; CREATE EXTERNAL TABLE ods_coupon_info( `id` STRING COMMENT '购物券编号', `coupon_name` STRING COMMENT '购物券名称', `coupon_type` STRING COMMENT '购物券类型 1 现金券 2 折扣券 3 满减券 4 满件打折券', `condition_amount` DECIMAL(16,2) COMMENT '满额数', `condition_num` BIGINT COMMENT '满件数', `activity_id` STRING COMMENT '活动编号', `benefit_amount` DECIMAL(16,2) COMMENT '减金额', `benefit_discount` DECIMAL(16,2) COMMENT '折扣', `create_time` STRING COMMENT '创建时间', `range_type` STRING COMMENT '范围类型 1、商品 2、品类 3、品牌', `limit_num` BIGINT COMMENT '最多领用次数', `taken_count` BIGINT COMMENT '已领用次数', `start_time` STRING COMMENT '开始领取时间', `end_time` STRING COMMENT '结束领取时间', `operate_time` STRING COMMENT '修改时间', `expire_time` STRING COMMENT '过期时间' ) COMMENT '优惠券表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_coupon_info/';
2.13 优惠券领用表
DROP TABLE IF EXISTS ods_coupon_use; CREATE EXTERNAL TABLE ods_coupon_use( `id` STRING COMMENT '编号', `coupon_id` STRING COMMENT '优惠券ID', `user_id` STRING COMMENT 'skuid', `order_id` STRING COMMENT 'spuid', `coupon_status` STRING COMMENT '优惠券状态', `get_time` STRING COMMENT '领取时间', `using_time` STRING COMMENT '使用时间(下单)', `used_time` STRING COMMENT '使用时间(支付)', `expire_time` STRING COMMENT '过期时间' ) COMMENT '优惠券领用表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_coupon_use/';
2.14 收藏表
DROP TABLE IF EXISTS ods_favor_info; CREATE EXTERNAL TABLE ods_favor_info( `id` STRING COMMENT '编号', `user_id` STRING COMMENT '用户id', `sku_id` STRING COMMENT 'skuid', `spu_id` STRING COMMENT 'spuid', `is_cancel` STRING COMMENT '是否取消', `create_time` STRING COMMENT '收藏时间', `cancel_time` STRING COMMENT '取消时间' ) COMMENT '商品收藏表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_favor_info/';
2.15 订单明细表
DROP TABLE IF EXISTS ods_order_detail; CREATE EXTERNAL TABLE ods_order_detail( `id` STRING COMMENT '编号', `order_id` STRING COMMENT '订单号', `sku_id` STRING COMMENT '商品id', `sku_name` STRING COMMENT '商品名称', `order_price` DECIMAL(16,2) COMMENT '商品价格', `sku_num` BIGINT COMMENT '商品数量', `create_time` STRING COMMENT '创建时间', `source_type` STRING COMMENT '来源类型', `source_id` STRING COMMENT '来源编号', `split_final_amount` DECIMAL(16,2) COMMENT '分摊最终金额', `split_activity_amount` DECIMAL(16,2) COMMENT '分摊活动优惠', `split_coupon_amount` DECIMAL(16,2) COMMENT '分摊优惠券优惠' ) COMMENT '订单详情表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_order_detail/';
2.16 订单明细活动关联表
DROP TABLE IF EXISTS ods_order_detail_activity; CREATE EXTERNAL TABLE ods_order_detail_activity( `id` STRING COMMENT '编号', `order_id` STRING COMMENT '订单号', `order_detail_id` STRING COMMENT '订单明细id', `activity_id` STRING COMMENT '活动id', `activity_rule_id` STRING COMMENT '活动规则id', `sku_id` BIGINT COMMENT '商品id', `create_time` STRING COMMENT '创建时间' ) COMMENT '订单详情活动关联表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_order_detail_activity/';
2.17 订单明细优惠券关联表
DROP TABLE IF EXISTS ods_order_detail_coupon; CREATE EXTERNAL TABLE ods_order_detail_coupon( `id` STRING COMMENT '编号', `order_id` STRING COMMENT '订单号', `order_detail_id` STRING COMMENT '订单明细id', `coupon_id` STRING COMMENT '优惠券id', `coupon_use_id` STRING COMMENT '优惠券领用记录id', `sku_id` STRING COMMENT '商品id', `create_time` STRING COMMENT '创建时间' ) COMMENT '订单详情活动关联表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_order_detail_coupon/';
2.18 订单表
DROP TABLE IF EXISTS ods_order_info; CREATE EXTERNAL TABLE ods_order_info ( `id` STRING COMMENT '订单号', `final_amount` DECIMAL(16,2) COMMENT '订单最终金额', `order_status` STRING COMMENT '订单状态', `user_id` STRING COMMENT '用户id', `payment_way` STRING COMMENT '支付方式', `delivery_address` STRING COMMENT '送货地址', `out_trade_no` STRING COMMENT '支付流水号', `create_time` STRING COMMENT '创建时间', `operate_time` STRING COMMENT '操作时间', `expire_time` STRING COMMENT '过期时间', `tracking_no` STRING COMMENT '物流单编号', `province_id` STRING COMMENT '省份ID', `activity_reduce_amount` DECIMAL(16,2) COMMENT '活动减免金额', `coupon_reduce_amount` DECIMAL(16,2) COMMENT '优惠券减免金额', `original_amount` DECIMAL(16,2) COMMENT '订单原价金额', `feight_fee` DECIMAL(16,2) COMMENT '运费', `feight_fee_reduce` DECIMAL(16,2) COMMENT '运费减免' ) COMMENT '订单表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_order_info/';
2.19 退单表
DROP TABLE IF EXISTS ods_order_refund_info; CREATE EXTERNAL TABLE ods_order_refund_info( `id` STRING COMMENT '编号', `user_id` STRING COMMENT '用户ID', `order_id` STRING COMMENT '订单ID', `sku_id` STRING COMMENT '商品ID', `refund_type` STRING COMMENT '退单类型', `refund_num` BIGINT COMMENT '退单件数', `refund_amount` DECIMAL(16,2) COMMENT '退单金额', `refund_reason_type` STRING COMMENT '退单原因类型', `refund_status` STRING COMMENT '退单状态',--退单状态应包含买家申请、卖家审核、卖家收货、退款完成等状态。此处未涉及到，故该表按增量处理 `create_time` STRING COMMENT '退单时间' ) COMMENT '退单表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_order_refund_info/';
2.20 订单状态日志表
DROP TABLE IF EXISTS ods_order_status_log; CREATE EXTERNAL TABLE ods_order_status_log ( `id` STRING COMMENT '编号', `order_id` STRING COMMENT '订单ID', `order_status` STRING COMMENT '订单状态', `operate_time` STRING COMMENT '修改时间' ) COMMENT '订单状态表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_order_status_log/';
2.21 支付表
DROP TABLE IF EXISTS ods_payment_info; CREATE EXTERNAL TABLE ods_payment_info( `id` STRING COMMENT '编号', `out_trade_no` STRING COMMENT '对外业务编号', `order_id` STRING COMMENT '订单编号', `user_id` STRING COMMENT '用户编号', `payment_type` STRING COMMENT '支付类型', `trade_no` STRING COMMENT '交易编号', `payment_amount` DECIMAL(16,2) COMMENT '支付金额', `subject` STRING COMMENT '交易内容', `payment_status` STRING COMMENT '支付状态', `create_time` STRING COMMENT '创建时间', `callback_time` STRING COMMENT '回调时间' ) COMMENT '支付流水表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_payment_info/';
2.22 退款表
DROP TABLE IF EXISTS ods_refund_payment; CREATE EXTERNAL TABLE ods_refund_payment( `id` STRING COMMENT '编号', `out_trade_no` STRING COMMENT '对外业务编号', `order_id` STRING COMMENT '订单编号', `sku_id` STRING COMMENT 'SKU编号', `payment_type` STRING COMMENT '支付类型', `trade_no` STRING COMMENT '交易编号', `refund_amount` DECIMAL(16,2) COMMENT '支付金额', `subject` STRING COMMENT '交易内容', `refund_status` STRING COMMENT '支付状态', `create_time` STRING COMMENT '创建时间', `callback_time` STRING COMMENT '回调时间' ) COMMENT '支付流水表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_refund_payment/';
2.23 商品平台属性表
DROP TABLE IF EXISTS ods_sku_attr_value; CREATE EXTERNAL TABLE ods_sku_attr_value( `id` STRING COMMENT '编号', `attr_id` STRING COMMENT '平台属性ID', `value_id` STRING COMMENT '平台属性值ID', `sku_id` STRING COMMENT '商品ID', `attr_name` STRING COMMENT '平台属性名称', `value_name` STRING COMMENT '平台属性值名称' ) COMMENT 'sku平台属性表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_sku_attr_value/';
2.24 商品（SKU）表
DROP TABLE IF EXISTS ods_sku_info; CREATE EXTERNAL TABLE ods_sku_info( `id` STRING COMMENT 'skuId', `spu_id` STRING COMMENT 'spuid', `price` DECIMAL(16,2) COMMENT '价格', `sku_name` STRING COMMENT '商品名称', `sku_desc` STRING COMMENT '商品描述', `weight` DECIMAL(16,2) COMMENT '重量', `tm_id` STRING COMMENT '品牌id', `category3_id` STRING COMMENT '品类id', `is_sale` STRING COMMENT '是否在售', `create_time` STRING COMMENT '创建时间' ) COMMENT 'SKU商品表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_sku_info/';
DROP TABLE IF EXISTS ods_sku_sale_attr_value; CREATE EXTERNAL TABLE ods_sku_sale_attr_value( `id` STRING COMMENT '编号', `sku_id` STRING COMMENT 'sku_id', `spu_id` STRING COMMENT 'spu_id', `sale_attr_value_id` STRING COMMENT '销售属性值id', `sale_attr_id` STRING COMMENT '销售属性id', `sale_attr_name` STRING COMMENT '销售属性名称', `sale_attr_value_name` STRING COMMENT '销售属性值名称' ) COMMENT 'sku销售属性名称' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_sku_sale_attr_value/';
2.26 商品（SPU）表
DROP TABLE IF EXISTS ods_spu_info; CREATE EXTERNAL TABLE ods_spu_info( `id` STRING COMMENT 'spuid', `spu_name` STRING COMMENT 'spu名称', `category3_id` STRING COMMENT '品类id', `tm_id` STRING COMMENT '品牌id' ) COMMENT 'SPU商品表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_spu_info/';
2.27 用户表
DROP TABLE IF EXISTS ods_user_info; CREATE EXTERNAL TABLE ods_user_info( `id` STRING COMMENT '用户id', `login_name` STRING COMMENT '用户名称', `nick_name` STRING COMMENT '用户昵称', `name` STRING COMMENT '用户姓名', `phone_num` STRING COMMENT '手机号码', `email` STRING COMMENT '邮箱', `user_level` STRING COMMENT '用户等级', `birthday` STRING COMMENT '生日', `gender` STRING COMMENT '性别', `create_time` STRING COMMENT '创建时间', `operate_time` STRING COMMENT '操作时间' ) COMMENT '用户表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_user_info/';
2.28 ODS层业务表首日数据装载脚本
1）编写脚本
（1）在/home/atguigu/bin目录下创建脚本hdfs_to_ods_db_init.sh
[atguigu@hadoop102 bin]$ vim hdfs_to_ods_db_init.sh
在脚本中填写如下内容
#!/bin/bash APP=gmall if [ -n "$2" ] ;then do_date=$2 else echo "请传入日期参数" exit fi ods_order_info=" load data inpath '/origin_data/$APP/db/order_info/$do_date' OVERWRITE into table ${APP}.ods_order_info partition(dt='$do_date');" ods_order_detail=" load data inpath '/origin_data/$APP/db/order_detail/$do_date' OVERWRITE into table ${APP}.ods_order_detail partition(dt='$do_date');" ods_sku_info=" load data inpath '/origin_data/$APP/db/sku_info/$do_date' OVERWRITE into table ${APP}.ods_sku_info partition(dt='$do_date');" ods_user_info=" load data inpath '/origin_data/$APP/db/user_info/$do_date' OVERWRITE into table ${APP}.ods_user_info partition(dt='$do_date');" ods_payment_info=" load data inpath '/origin_data/$APP/db/payment_info/$do_date' OVERWRITE into table ${APP}.ods_payment_info partition(dt='$do_date');" ods_base_category1=" load data inpath '/origin_data/$APP/db/base_category1/$do_date' OVERWRITE into table ${APP}.ods_base_category1 partition(dt='$do_date');" ods_base_category2=" load data inpath '/origin_data/$APP/db/base_category2/$do_date' OVERWRITE into table ${APP}.ods_base_category2 partition(dt='$do_date');" ods_base_category3=" load data inpath '/origin_data/$APP/db/base_category3/$do_date' OVERWRITE into table ${APP}.ods_base_category3 partition(dt='$do_date'); " ods_base_trademark=" load data inpath '/origin_data/$APP/db/base_trademark/$do_date' OVERWRITE into table ${APP}.ods_base_trademark partition(dt='$do_date'); " ods_activity_info=" load data inpath '/origin_data/$APP/db/activity_info/$do_date' OVERWRITE into table ${APP}.ods_activity_info partition(dt='$do_date'); " ods_cart_info=" load data inpath '/origin_data/$APP/db/cart_info/$do_date' OVERWRITE into table ${APP}.ods_cart_info partition(dt='$do_date'); " ods_comment_info=" load data inpath '/origin_data/$APP/db/comment_info/$do_date' OVERWRITE into table ${APP}.ods_comment_info partition(dt='$do_date'); " ods_coupon_info=" load data inpath '/origin_data/$APP/db/coupon_info/$do_date' OVERWRITE into table ${APP}.ods_coupon_info partition(dt='$do_date'); " ods_coupon_use=" load data inpath '/origin_data/$APP/db/coupon_use/$do_date' OVERWRITE into table ${APP}.ods_coupon_use partition(dt='$do_date'); " ods_favor_info=" load data inpath '/origin_data/$APP/db/favor_info/$do_date' OVERWRITE into table ${APP}.ods_favor_info partition(dt='$do_date'); " ods_order_refund_info=" load data inpath '/origin_data/$APP/db/order_refund_info/$do_date' OVERWRITE into table ${APP}.ods_order_refund_info partition(dt='$do_date'); " ods_order_status_log=" load data inpath '/origin_data/$APP/db/order_status_log/$do_date' OVERWRITE into table ${APP}.ods_order_status_log partition(dt='$do_date'); " ods_spu_info=" load data inpath '/origin_data/$APP/db/spu_info/$do_date' OVERWRITE into table ${APP}.ods_spu_info partition(dt='$do_date'); " ods_activity_rule=" load data inpath '/origin_data/$APP/db/activity_rule/$do_date' OVERWRITE into table ${APP}.ods_activity_rule partition(dt='$do_date');" ods_base_dic=" load data inpath '/origin_data/$APP/db/base_dic/$do_date' OVERWRITE into table ${APP}.ods_base_dic partition(dt='$do_date'); " ods_order_detail_activity=" load data inpath '/origin_data/$APP/db/order_detail_activity/$do_date' OVERWRITE into table ${APP}.ods_order_detail_activity partition(dt='$do_date'); " ods_order_detail_coupon=" load data inpath '/origin_data/$APP/db/order_detail_coupon/$do_date' OVERWRITE into table ${APP}.ods_order_detail_coupon partition(dt='$do_date'); " ods_refund_payment=" load data inpath '/origin_data/$APP/db/refund_payment/$do_date' OVERWRITE into table ${APP}.ods_refund_payment partition(dt='$do_date'); " ods_sku_attr_value=" load data inpath '/origin_data/$APP/db/sku_attr_value/$do_date' OVERWRITE into table ${APP}.ods_sku_attr_value partition(dt='$do_date'); " ods_sku_sale_attr_value=" load data inpath '/origin_data/$APP/db/sku_sale_attr_value/$do_date' OVERWRITE into table ${APP}.ods_sku_sale_attr_value partition(dt='$do_date'); " ods_base_province=" load data inpath '/origin_data/$APP/db/base_province/$do_date' OVERWRITE into table ${APP}.ods_base_province;" ods_base_region=" load data inpath '/origin_data/$APP/db/base_region/$do_date' OVERWRITE into table ${APP}.ods_base_region;" case $1 in "ods_order_info"){ hive -e "$ods_order_info" };; "ods_order_detail"){ hive -e "$ods_order_detail" };; "ods_sku_info"){ hive -e "$ods_sku_info" };; "ods_user_info"){ hive -e "$ods_user_info" };; "ods_payment_info"){ hive -e "$ods_payment_info" };; "ods_base_category1"){ hive -e "$ods_base_category1" };; "ods_base_category2"){ hive -e "$ods_base_category2" };; "ods_base_category3"){ hive -e "$ods_base_category3" };; "ods_base_trademark"){ hive -e "$ods_base_trademark" };; "ods_activity_info"){ hive -e "$ods_activity_info" };; "ods_cart_info"){ hive -e "$ods_cart_info" };; "ods_comment_info"){ hive -e "$ods_comment_info" };; "ods_coupon_info"){ hive -e "$ods_coupon_info" };; "ods_coupon_use"){ hive -e "$ods_coupon_use" };; "ods_favor_info"){ hive -e "$ods_favor_info" };; "ods_order_refund_info"){ hive -e "$ods_order_refund_info" };; "ods_order_status_log"){ hive -e "$ods_order_status_log" };; "ods_spu_info"){ hive -e "$ods_spu_info" };; "ods_activity_rule"){ hive -e "$ods_activity_rule" };; "ods_base_dic"){ hive -e "$ods_base_dic" };; "ods_order_detail_activity"){ hive -e "$ods_order_detail_activity" };; "ods_order_detail_coupon"){ hive -e "$ods_order_detail_coupon" };; "ods_refund_payment"){ hive -e "$ods_refund_payment" };; "ods_sku_attr_value"){ hive -e "$ods_sku_attr_value" };; "ods_sku_sale_attr_value"){ hive -e "$ods_sku_sale_attr_value" };; "ods_base_province"){ hive -e "$ods_base_province" };; "ods_base_region"){ hive -e "$ods_base_region" };; "all"){ hive -e "$ods_order_info$ods_order_detail$ods_sku_info$ods_user_info$ods_payment_info$ods_base_category1$ods_base_category2$ods_base_category3$ods_base_trademark$ods_activity_info$ods_cart_info$ods_comment_info$ods_coupon_info$ods_coupon_use$ods_favor_info$ods_order_refund_info$ods_order_status_log$ods_spu_info$ods_activity_rule$ods_base_dic$ods_order_detail_activity$ods_order_detail_coupon$ods_refund_payment$ods_sku_attr_value$ods_sku_sale_attr_value$ods_base_province$ods_base_region" };; esac
（2）增加执行权限
[atguigu@hadoop102 bin]$ chmod +x hdfs_to_ods_db_init.sh
2）脚本使用
（1）执行脚本
[atguigu@hadoop102 bin]$ hdfs_to_ods_db_init.sh all 2020-06-14
（2）查看数据是否导入成功
2.29 ODS层业务表每日数据装载脚本
1）编写脚本
（1）在/home/atguigu/bin目录下创建脚本hdfs_to_ods_db.sh
[atguigu@hadoop102 bin]$ vim hdfs_to_ods_db.sh
在脚本中填写如下内容
#!/bin/bash APP=gmall # 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天 if [ -n "$2" ] ;then do_date=$2 else do_date=`date -d "-1 day" +%F` fi ods_order_info=" load data inpath '/origin_data/$APP/db/order_info/$do_date' OVERWRITE into table ${APP}.ods_order_info partition(dt='$do_date');" ods_order_detail=" load data inpath '/origin_data/$APP/db/order_detail/$do_date' OVERWRITE into table ${APP}.ods_order_detail partition(dt='$do_date');" ods_sku_info=" load data inpath '/origin_data/$APP/db/sku_info/$do_date' OVERWRITE into table ${APP}.ods_sku_info partition(dt='$do_date');" ods_user_info=" load data inpath '/origin_data/$APP/db/user_info/$do_date' OVERWRITE into table ${APP}.ods_user_info partition(dt='$do_date');" ods_payment_info=" load data inpath '/origin_data/$APP/db/payment_info/$do_date' OVERWRITE into table ${APP}.ods_payment_info partition(dt='$do_date');" ods_base_category1=" load data inpath '/origin_data/$APP/db/base_category1/$do_date' OVERWRITE into table ${APP}.ods_base_category1 partition(dt='$do_date');" ods_base_category2=" load data inpath '/origin_data/$APP/db/base_category2/$do_date' OVERWRITE into table ${APP}.ods_base_category2 partition(dt='$do_date');" ods_base_category3=" load data inpath '/origin_data/$APP/db/base_category3/$do_date' OVERWRITE into table ${APP}.ods_base_category3 partition(dt='$do_date'); " ods_base_trademark=" load data inpath '/origin_data/$APP/db/base_trademark/$do_date' OVERWRITE into table ${APP}.ods_base_trademark partition(dt='$do_date'); " ods_activity_info=" load data inpath '/origin_data/$APP/db/activity_info/$do_date' OVERWRITE into table ${APP}.ods_activity_info partition(dt='$do_date'); " ods_cart_info=" load data inpath '/origin_data/$APP/db/cart_info/$do_date' OVERWRITE into table ${APP}.ods_cart_info partition(dt='$do_date'); " ods_comment_info=" load data inpath '/origin_data/$APP/db/comment_info/$do_date' OVERWRITE into table ${APP}.ods_comment_info partition(dt='$do_date'); " ods_coupon_info=" load data inpath '/origin_data/$APP/db/coupon_info/$do_date' OVERWRITE into table ${APP}.ods_coupon_info partition(dt='$do_date'); " ods_coupon_use=" load data inpath '/origin_data/$APP/db/coupon_use/$do_date' OVERWRITE into table ${APP}.ods_coupon_use partition(dt='$do_date'); " ods_favor_info=" load data inpath '/origin_data/$APP/db/favor_info/$do_date' OVERWRITE into table ${APP}.ods_favor_info partition(dt='$do_date'); " ods_order_refund_info=" load data inpath '/origin_data/$APP/db/order_refund_info/$do_date' OVERWRITE into table ${APP}.ods_order_refund_info partition(dt='$do_date'); " ods_order_status_log=" load data inpath '/origin_data/$APP/db/order_status_log/$do_date' OVERWRITE into table ${APP}.ods_order_status_log partition(dt='$do_date'); " ods_spu_info=" load data inpath '/origin_data/$APP/db/spu_info/$do_date' OVERWRITE into table ${APP}.ods_spu_info partition(dt='$do_date'); " ods_activity_rule=" load data inpath '/origin_data/$APP/db/activity_rule/$do_date' OVERWRITE into table ${APP}.ods_activity_rule partition(dt='$do_date');" ods_base_dic=" load data inpath '/origin_data/$APP/db/base_dic/$do_date' OVERWRITE into table ${APP}.ods_base_dic partition(dt='$do_date'); " ods_order_detail_activity=" load data inpath '/origin_data/$APP/db/order_detail_activity/$do_date' OVERWRITE into table ${APP}.ods_order_detail_activity partition(dt='$do_date'); " ods_order_detail_coupon=" load data inpath '/origin_data/$APP/db/order_detail_coupon/$do_date' OVERWRITE into table ${APP}.ods_order_detail_coupon partition(dt='$do_date'); " ods_refund_payment=" load data inpath '/origin_data/$APP/db/refund_payment/$do_date' OVERWRITE into table ${APP}.ods_refund_payment partition(dt='$do_date'); " ods_sku_attr_value=" load data inpath '/origin_data/$APP/db/sku_attr_value/$do_date' OVERWRITE into table ${APP}.ods_sku_attr_value partition(dt='$do_date'); " ods_sku_sale_attr_value=" load data inpath '/origin_data/$APP/db/sku_sale_attr_value/$do_date' OVERWRITE into table ${APP}.ods_sku_sale_attr_value partition(dt='$do_date'); " ods_base_province=" load data inpath '/origin_data/$APP/db/base_province/$do_date' OVERWRITE into table ${APP}.ods_base_province;" ods_base_region=" load data inpath '/origin_data/$APP/db/base_region/$do_date' OVERWRITE into table ${APP}.ods_base_region;" case $1 in "ods_order_info"){ hive -e "$ods_order_info" };; "ods_order_detail"){ hive -e "$ods_order_detail" };; "ods_sku_info"){ hive -e "$ods_sku_info" };; "ods_user_info"){ hive -e "$ods_user_info" };; "ods_payment_info"){ hive -e "$ods_payment_info" };; "ods_base_category1"){ hive -e "$ods_base_category1" };; "ods_base_category2"){ hive -e "$ods_base_category2" };; "ods_base_category3"){ hive -e "$ods_base_category3" };; "ods_base_trademark"){ hive -e "$ods_base_trademark" };; "ods_activity_info"){ hive -e "$ods_activity_info" };; "ods_cart_info"){ hive -e "$ods_cart_info" };; "ods_comment_info"){ hive -e "$ods_comment_info" };; "ods_coupon_info"){ hive -e "$ods_coupon_info" };; "ods_coupon_use"){ hive -e "$ods_coupon_use" };; "ods_favor_info"){ hive -e "$ods_favor_info" };; "ods_order_refund_info"){ hive -e "$ods_order_refund_info" };; "ods_order_status_log"){ hive -e "$ods_order_status_log" };; "ods_spu_info"){ hive -e "$ods_spu_info" };; "ods_activity_rule"){ hive -e "$ods_activity_rule" };; "ods_base_dic"){ hive -e "$ods_base_dic" };; "ods_order_detail_activity"){ hive -e "$ods_order_detail_activity" };; "ods_order_detail_coupon"){ hive -e "$ods_order_detail_coupon" };; "ods_refund_payment"){ hive -e "$ods_refund_payment" };; "ods_sku_attr_value"){ hive -e "$ods_sku_attr_value" };; "ods_sku_sale_attr_value"){ hive -e "$ods_sku_sale_attr_value" };; "all"){ hive -e "$ods_order_info$ods_order_detail$ods_sku_info$ods_user_info$ods_payment_info$ods_base_category1$ods_base_category2$ods_base_category3$ods_base_trademark$ods_activity_info$ods_cart_info$ods_comment_info$ods_coupon_info$ods_coupon_use$ods_favor_info$ods_order_refund_info$ods_order_status_log$ods_spu_info$ods_activity_rule$ods_base_dic$ods_order_detail_activity$ods_order_detail_coupon$ods_refund_payment$ods_sku_attr_value$ods_sku_sale_attr_value" };; esac
（2）修改权限
[atguigu@hadoop102 bin]$ chmod +x hdfs_to_ods_db.sh
2）脚本使用
（1）执行脚本
[atguigu@hadoop102 bin]$ hdfs_to_ods_db.sh all 2020-06-14
（2）查看数据是否导入成功
第二章 数仓搭建-ODS层
1）保持数据原貌不做任何修改，起到备份数据的作用。
2）数据采用LZO压缩，减少磁盘存储空间。100G数据可以压缩到10G以内。
3）创建分区表，防止后续的全表扫描，在企业开发中大量使用分区表。
4）创建外部表。在企业开发中，除了自己用的临时表，创建内部表外，绝大多数场景都是创建外部表。
2.1 ODS层（用户行为数据）
2.1.1 创建日志表ods_log
1）创建支持lzo压缩的分区表
（1）建表语句
hive (gmall)> drop table if exists ods_log; CREATE EXTERNAL TABLE ods_log (`line` string) PARTITIONED BY (`dt` string) -- 按照时间创建分区 STORED AS -- 指定存储方式，读数据采用LzoTextInputFormat； INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_log' -- 指定数据在hdfs上的存储位置 ;
说明Hive的LZO压缩：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+LZO
（2）分区规划
2）加载数据
hive (gmall)>
load data inpath '/origin_data/gmall/log/topic_log/2020-06-14' into table ods_log partition(dt='2020-06-14');
注意：时间格式都配置成YYYY-MM-DD格式，这是Hive默认支持的时间格式
3）为lzo压缩文件创建索引
[atguigu@hadoop102 bin]$ hadoop jar /opt/module/hadoop-3.1.3/share/hadoop/common/hadoop-lzo-0.4.20.jar com.hadoop.compression.lzo.DistributedLzoIndexer /warehouse/gmall/ods/ods_log/dt=2020-06-14
2.1.2 Shell中单引号和双引号区别
1）在/home/atguigu/bin创建一个test.sh文件
[atguigu@hadoop102 bin]$ vim test.sh
在文件中添加如下内容
#!/bin/bash do_date=$1 echo '$do_date' echo "$do_date" echo "'$do_date'" echo '"$do_date"' echo `date`
2）查看执行结果
[atguigu@hadoop102 bin]$ test.sh 2020-06-14 $do_date 2020-06-14 '2020-06-14' "$do_date"
2020年 06月 18日 星期四 21:02:08 CST
3）总结：
（1）单引号不取变量值
（2）双引号取变量值
（3）反引号`，执行引号中命令
（4）双引号内部嵌套单引号，取出变量值
（5）单引号内部嵌套双引号，不取出变量值
2.1.3 ODS层日志表加载数据脚本
1）编写脚本
（1）在hadoop102的/home/atguigu/bin目录下创建脚本
[atguigu@hadoop102 bin]$ vim hdfs_to_ods_log.sh
在脚本中编写如下内容
#!/bin/bash # 定义变量方便修改 APP=gmall # 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天 if [ -n "$1" ] ;then do_date=$1 else do_date=`date -d "-1 day" +%F` fi echo ================== 日志日期为 $do_date ================== sql=" load data inpath '/origin_data/$APP/log/topic_log/$do_date' into table ${APP}.ods_log partition(dt='$do_date'); " hive -e "$sql" hadoop jar /opt/module/hadoop-3.1.3/share/hadoop/common/hadoop-lzo-0.4.20.jar com.hadoop.compression.lzo.DistributedLzoIndexer /warehouse/$APP/ods/ods_log/dt=$do_date
（1）说明1：
[ -n 变量值 ] 判断变量的值，是否为空
-- 变量的值，非空，返回true
-- 变量的值，为空，返回false
注意：[ -n 变量值 ]不会解析数据，使用[ -n 变量值 ]时，需要对变量加上双引号(" ")
（2）说明2：
查看date命令的使用，date --help
（3）增加脚本执行权限
[atguigu@hadoop102 bin]$ chmod 777 hdfs_to_ods_log.sh
2）脚本使用
（1）执行脚本
[atguigu@hadoop102 module]$ hdfs_to_ods_log.sh 2020-06-14
（2）查看导入数据
2.2 ODS层（业务数据）
ODS层业务表分区规划如下
ODS层业务表数据装载思路如下
2.2.1 活动信息表
DROP TABLE IF EXISTS ods_activity_info; CREATE EXTERNAL TABLE ods_activity_info( `id` STRING COMMENT '编号', `activity_name` STRING COMMENT '活动名称', `activity_type` STRING COMMENT '活动类型', `start_time` STRING COMMENT '开始时间', `end_time` STRING COMMENT '结束时间', `create_time` STRING COMMENT '创建时间' ) COMMENT '活动信息表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_activity_info/';
2.2.2 活动规则表
DROP TABLE IF EXISTS ods_activity_rule; CREATE EXTERNAL TABLE ods_activity_rule( `id` STRING COMMENT '编号', `activity_id` STRING COMMENT '活动ID', `activity_type` STRING COMMENT '活动类型', `condition_amount` DECIMAL(16,2) COMMENT '满减金额', `condition_num` BIGINT COMMENT '满减件数', `benefit_amount` DECIMAL(16,2) COMMENT '优惠金额', `benefit_discount` DECIMAL(16,2) COMMENT '优惠折扣', `benefit_level` STRING COMMENT '优惠级别' ) COMMENT '活动规则表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_activity_rule/';
2.2.3 一级品类表
DROP TABLE IF EXISTS ods_base_category1; CREATE EXTERNAL TABLE ods_base_category1( `id` STRING COMMENT 'id', `name` STRING COMMENT '名称' ) COMMENT '商品一级分类表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_base_category1/';
2.2.4 二级品类表
DROP TABLE IF EXISTS ods_base_category2; CREATE EXTERNAL TABLE ods_base_category2( `id` STRING COMMENT ' id', `name` STRING COMMENT '名称', `category1_id` STRING COMMENT '一级品类id' ) COMMENT '商品二级分类表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_base_category2/';
2.2.5 三级品类表
DROP TABLE IF EXISTS ods_base_category3; CREATE EXTERNAL TABLE ods_base_category3( `id` STRING COMMENT ' id', `name` STRING COMMENT '名称', `category2_id` STRING COMMENT '二级品类id' ) COMMENT '商品三级分类表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_base_category3/';
2.2.6 编码字典表
DROP TABLE IF EXISTS ods_base_dic; CREATE EXTERNAL TABLE ods_base_dic( `dic_code` STRING COMMENT '编号', `dic_name` STRING COMMENT '编码名称', `parent_code` STRING COMMENT '父编码', `create_time` STRING COMMENT '创建日期', `operate_time` STRING COMMENT '操作日期' ) COMMENT '编码字典表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_base_dic/';
2.2.7 省份表
DROP TABLE IF EXISTS ods_base_province; CREATE EXTERNAL TABLE ods_base_province ( `id` STRING COMMENT '编号', `name` STRING COMMENT '省份名称', `region_id` STRING COMMENT '地区ID', `area_code` STRING COMMENT '地区编码', `iso_code` STRING COMMENT 'ISO-3166编码，供可视化使用', `iso_3166_2` STRING COMMENT 'IOS-3166-2编码，供可视化使用' ) COMMENT '省份表' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_base_province/';
2.2.8 地区表
DROP TABLE IF EXISTS ods_base_region; CREATE EXTERNAL TABLE ods_base_region ( `id` STRING COMMENT '编号', `region_name` STRING COMMENT '地区名称' ) COMMENT '地区表' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_base_region/';
2.2.9 品牌表
DROP TABLE IF EXISTS ods_base_trademark; CREATE EXTERNAL TABLE ods_base_trademark ( `id` STRING COMMENT '编号', `tm_name` STRING COMMENT '品牌名称' ) COMMENT '品牌表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_base_trademark/';
2.2.10 购物车表
DROP TABLE IF EXISTS ods_cart_info; CREATE EXTERNAL TABLE ods_cart_info( `id` STRING COMMENT '编号', `user_id` STRING COMMENT '用户id', `sku_id` STRING COMMENT 'skuid', `cart_price` DECIMAL(16,2) COMMENT '放入购物车时价格', `sku_num` BIGINT COMMENT '数量', `sku_name` STRING COMMENT 'sku名称 (冗余)', `create_time` STRING COMMENT '创建时间', `operate_time` STRING COMMENT '修改时间', `is_ordered` STRING COMMENT '是否已经下单', `order_time` STRING COMMENT '下单时间', `source_type` STRING COMMENT '来源类型', `source_id` STRING COMMENT '来源编号' ) COMMENT '加购表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_cart_info/';
2.2.11 评论表
DROP TABLE IF EXISTS ods_comment_info; CREATE EXTERNAL TABLE ods_comment_info( `id` STRING COMMENT '编号', `user_id` STRING COMMENT '用户ID', `sku_id` STRING COMMENT '商品sku', `spu_id` STRING COMMENT '商品spu', `order_id` STRING COMMENT '订单ID', `appraise` STRING COMMENT '评价', `create_time` STRING COMMENT '评价时间' ) COMMENT '商品评论表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_comment_info/';
2.2.12 优惠券信息表
DROP TABLE IF EXISTS ods_coupon_info; CREATE EXTERNAL TABLE ods_coupon_info( `id` STRING COMMENT '购物券编号', `coupon_name` STRING COMMENT '购物券名称', `coupon_type` STRING COMMENT '购物券类型 1 现金券 2 折扣券 3 满减券 4 满件打折券', `condition_amount` DECIMAL(16,2) COMMENT '满额数', `condition_num` BIGINT COMMENT '满件数', `activity_id` STRING COMMENT '活动编号', `benefit_amount` DECIMAL(16,2) COMMENT '减金额', `benefit_discount` DECIMAL(16,2) COMMENT '折扣', `create_time` STRING COMMENT '创建时间', `range_type` STRING COMMENT '范围类型 1、商品 2、品类 3、品牌', `limit_num` BIGINT COMMENT '最多领用次数', `taken_count` BIGINT COMMENT '已领用次数', `start_time` STRING COMMENT '开始领取时间', `end_time` STRING COMMENT '结束领取时间', `operate_time` STRING COMMENT '修改时间', `expire_time` STRING COMMENT '过期时间' ) COMMENT '优惠券表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_coupon_info/';
2.2.13 优惠券领用表
DROP TABLE IF EXISTS ods_coupon_use; CREATE EXTERNAL TABLE ods_coupon_use( `id` STRING COMMENT '编号', `coupon_id` STRING COMMENT '优惠券ID', `user_id` STRING COMMENT 'skuid', `order_id` STRING COMMENT 'spuid', `coupon_status` STRING COMMENT '优惠券状态', `get_time` STRING COMMENT '领取时间', `using_time` STRING COMMENT '使用时间(下单)', `used_time` STRING COMMENT '使用时间(支付)', `expire_time` STRING COMMENT '过期时间' ) COMMENT '优惠券领用表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_coupon_use/';
2.2.14 收藏表
DROP TABLE IF EXISTS ods_favor_info; CREATE EXTERNAL TABLE ods_favor_info( `id` STRING COMMENT '编号', `user_id` STRING COMMENT '用户id', `sku_id` STRING COMMENT 'skuid', `spu_id` STRING COMMENT 'spuid', `is_cancel` STRING COMMENT '是否取消', `create_time` STRING COMMENT '收藏时间', `cancel_time` STRING COMMENT '取消时间' ) COMMENT '商品收藏表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_favor_info/';
2.2.15 订单明细表
DROP TABLE IF EXISTS ods_order_detail; CREATE EXTERNAL TABLE ods_order_detail( `id` STRING COMMENT '编号', `order_id` STRING COMMENT '订单号', `sku_id` STRING COMMENT '商品id', `sku_name` STRING COMMENT '商品名称', `order_price` DECIMAL(16,2) COMMENT '商品价格', `sku_num` BIGINT COMMENT '商品数量', `create_time` STRING COMMENT '创建时间', `source_type` STRING COMMENT '来源类型', `source_id` STRING COMMENT '来源编号', `split_final_amount` DECIMAL(16,2) COMMENT '分摊最终金额', `split_activity_amount` DECIMAL(16,2) COMMENT '分摊活动优惠', `split_coupon_amount` DECIMAL(16,2) COMMENT '分摊优惠券优惠' ) COMMENT '订单详情表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_order_detail/';
2.2.16 订单明细活动关联表
DROP TABLE IF EXISTS ods_order_detail_activity; CREATE EXTERNAL TABLE ods_order_detail_activity( `id` STRING COMMENT '编号', `order_id` STRING COMMENT '订单号', `order_detail_id` STRING COMMENT '订单明细id', `activity_id` STRING COMMENT '活动id', `activity_rule_id` STRING COMMENT '活动规则id', `sku_id` BIGINT COMMENT '商品id', `create_time` STRING COMMENT '创建时间' ) COMMENT '订单详情活动关联表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_order_detail_activity/';
2.2.17 订单明细优惠券关联表
DROP TABLE IF EXISTS ods_order_detail_coupon; CREATE EXTERNAL TABLE ods_order_detail_coupon( `id` STRING COMMENT '编号', `order_id` STRING COMMENT '订单号', `order_detail_id` STRING COMMENT '订单明细id', `coupon_id` STRING COMMENT '优惠券id', `coupon_use_id` STRING COMMENT '优惠券领用记录id', `sku_id` STRING COMMENT '商品id', `create_time` STRING COMMENT '创建时间' ) COMMENT '订单详情活动关联表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_order_detail_coupon/';
2.2.18 订单表
DROP TABLE IF EXISTS ods_order_info; CREATE EXTERNAL TABLE ods_order_info ( `id` STRING COMMENT '订单号', `final_amount` DECIMAL(16,2) COMMENT '订单最终金额', `order_status` STRING COMMENT '订单状态', `user_id` STRING COMMENT '用户id', `payment_way` STRING COMMENT '支付方式', `delivery_address` STRING COMMENT '送货地址', `out_trade_no` STRING COMMENT '支付流水号', `create_time` STRING COMMENT '创建时间', `operate_time` STRING COMMENT '操作时间', `expire_time` STRING COMMENT '过期时间', `tracking_no` STRING COMMENT '物流单编号', `province_id` STRING COMMENT '省份ID', `activity_reduce_amount` DECIMAL(16,2) COMMENT '活动减免金额', `coupon_reduce_amount` DECIMAL(16,2) COMMENT '优惠券减免金额', `original_amount` DECIMAL(16,2) COMMENT '订单原价金额', `feight_fee` DECIMAL(16,2) COMMENT '运费', `feight_fee_reduce` DECIMAL(16,2) COMMENT '运费减免' ) COMMENT '订单表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_order_info/';
2.2.19 退单表
DROP TABLE IF EXISTS ods_order_refund_info; CREATE EXTERNAL TABLE ods_order_refund_info( `id` STRING COMMENT '编号', `user_id` STRING COMMENT '用户ID', `order_id` STRING COMMENT '订单ID', `sku_id` STRING COMMENT '商品ID', `refund_type` STRING COMMENT '退单类型', `refund_num` BIGINT COMMENT '退单件数', `refund_amount` DECIMAL(16,2) COMMENT '退单金额', `refund_reason_type` STRING COMMENT '退单原因类型', `refund_status` STRING COMMENT '退单状态',--退单状态应包含买家申请、卖家审核、卖家收货、退款完成等状态。此处未涉及到，故该表按增量处理 `create_time` STRING COMMENT '退单时间' ) COMMENT '退单表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_order_refund_info/';
2.2.20 订单状态日志表
DROP TABLE IF EXISTS ods_order_status_log; CREATE EXTERNAL TABLE ods_order_status_log ( `id` STRING COMMENT '编号', `order_id` STRING COMMENT '订单ID', `order_status` STRING COMMENT '订单状态', `operate_time` STRING COMMENT '修改时间' ) COMMENT '订单状态表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_order_status_log/';
2.2.21 支付表
DROP TABLE IF EXISTS ods_payment_info; CREATE EXTERNAL TABLE ods_payment_info( `id` STRING COMMENT '编号', `out_trade_no` STRING COMMENT '对外业务编号', `order_id` STRING COMMENT '订单编号', `user_id` STRING COMMENT '用户编号', `payment_type` STRING COMMENT '支付类型', `trade_no` STRING COMMENT '交易编号', `payment_amount` DECIMAL(16,2) COMMENT '支付金额', `subject` STRING COMMENT '交易内容', `payment_status` STRING COMMENT '支付状态', `create_time` STRING COMMENT '创建时间', `callback_time` STRING COMMENT '回调时间' ) COMMENT '支付流水表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_payment_info/';
2.2.22 退款表
DROP TABLE IF EXISTS ods_refund_payment; CREATE EXTERNAL TABLE ods_refund_payment( `id` STRING COMMENT '编号', `out_trade_no` STRING COMMENT '对外业务编号', `order_id` STRING COMMENT '订单编号', `sku_id` STRING COMMENT 'SKU编号', `payment_type` STRING COMMENT '支付类型', `trade_no` STRING COMMENT '交易编号', `refund_amount` DECIMAL(16,2) COMMENT '支付金额', `subject` STRING COMMENT '交易内容', `refund_status` STRING COMMENT '支付状态', `create_time` STRING COMMENT '创建时间', `callback_time` STRING COMMENT '回调时间' ) COMMENT '支付流水表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_refund_payment/';
2.2.23 商品平台属性表
DROP TABLE IF EXISTS ods_sku_attr_value; CREATE EXTERNAL TABLE ods_sku_attr_value( `id` STRING COMMENT '编号', `attr_id` STRING COMMENT '平台属性ID', `value_id` STRING COMMENT '平台属性值ID', `sku_id` STRING COMMENT '商品ID', `attr_name` STRING COMMENT '平台属性名称', `value_name` STRING COMMENT '平台属性值名称' ) COMMENT 'sku平台属性表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_sku_attr_value/';
2.2.24 商品（SKU）表
DROP TABLE IF EXISTS ods_sku_info; CREATE EXTERNAL TABLE ods_sku_info( `id` STRING COMMENT 'skuId', `spu_id` STRING COMMENT 'spuid', `price` DECIMAL(16,2) COMMENT '价格', `sku_name` STRING COMMENT '商品名称', `sku_desc` STRING COMMENT '商品描述', `weight` DECIMAL(16,2) COMMENT '重量', `tm_id` STRING COMMENT '品牌id', `category3_id` STRING COMMENT '品类id', `is_sale` STRING COMMENT '是否在售', `create_time` STRING COMMENT '创建时间' ) COMMENT 'SKU商品表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_sku_info/';
2.2.25 商品销售属性表
DROP TABLE IF EXISTS ods_sku_sale_attr_value; CREATE EXTERNAL TABLE ods_sku_sale_attr_value( `id` STRING COMMENT '编号', `sku_id` STRING COMMENT 'sku_id', `spu_id` STRING COMMENT 'spu_id', `sale_attr_value_id` STRING COMMENT '销售属性值id', `sale_attr_id` STRING COMMENT '销售属性id', `sale_attr_name` STRING COMMENT '销售属性名称', `sale_attr_value_name` STRING COMMENT '销售属性值名称' ) COMMENT 'sku销售属性名称' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_sku_sale_attr_value/';
2.2.26 商品（SPU）表
DROP TABLE IF EXISTS ods_spu_info; CREATE EXTERNAL TABLE ods_spu_info( `id` STRING COMMENT 'spuid', `spu_name` STRING COMMENT 'spu名称', `category3_id` STRING COMMENT '品类id', `tm_id` STRING COMMENT '品牌id' ) COMMENT 'SPU商品表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_spu_info/';
2.2.27 用户表
DROP TABLE IF EXISTS ods_user_info; CREATE EXTERNAL TABLE ods_user_info( `id` STRING COMMENT '用户id', `login_name` STRING COMMENT '用户名称', `nick_name` STRING COMMENT '用户昵称', `name` STRING COMMENT '用户姓名', `phone_num` STRING COMMENT '手机号码', `email` STRING COMMENT '邮箱', `user_level` STRING COMMENT '用户等级', `birthday` STRING COMMENT '生日', `gender` STRING COMMENT '性别', `create_time` STRING COMMENT '创建时间', `operate_time` STRING COMMENT '操作时间' ) COMMENT '用户表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/warehouse/gmall/ods/ods_user_info/';
2.2.28 ODS层业务表首日数据装载脚本
1）编写脚本
（1）在/home/atguigu/bin目录下创建脚本hdfs_to_ods_db_init.sh
[atguigu@hadoop102 bin]$ vim hdfs_to_ods_db_init.sh
在脚本中填写如下内容
#!/bin/bash APP=gmall if [ -n "$2" ] ;then do_date=$2 else echo "请传入日期参数" exit fi ods_order_info=" load data inpath '/origin_data/$APP/db/order_info/$do_date' OVERWRITE into table ${APP}.ods_order_info partition(dt='$do_date');" ods_order_detail=" load data inpath '/origin_data/$APP/db/order_detail/$do_date' OVERWRITE into table ${APP}.ods_order_detail partition(dt='$do_date');" ods_sku_info=" load data inpath '/origin_data/$APP/db/sku_info/$do_date' OVERWRITE into table ${APP}.ods_sku_info partition(dt='$do_date');" ods_user_info=" load data inpath '/origin_data/$APP/db/user_info/$do_date' OVERWRITE into table ${APP}.ods_user_info partition(dt='$do_date');" ods_payment_info=" load data inpath '/origin_data/$APP/db/payment_info/$do_date' OVERWRITE into table ${APP}.ods_payment_info partition(dt='$do_date');" ods_base_category1=" load data inpath '/origin_data/$APP/db/base_category1/$do_date' OVERWRITE into table ${APP}.ods_base_category1 partition(dt='$do_date');" ods_base_category2=" load data inpath '/origin_data/$APP/db/base_category2/$do_date' OVERWRITE into table ${APP}.ods_base_category2 partition(dt='$do_date');" ods_base_category3=" load data inpath '/origin_data/$APP/db/base_category3/$do_date' OVERWRITE into table ${APP}.ods_base_category3 partition(dt='$do_date'); " ods_base_trademark=" load data inpath '/origin_data/$APP/db/base_trademark/$do_date' OVERWRITE into table ${APP}.ods_base_trademark partition(dt='$do_date'); " ods_activity_info=" load data inpath '/origin_data/$APP/db/activity_info/$do_date' OVERWRITE into table ${APP}.ods_activity_info partition(dt='$do_date'); " ods_cart_info=" load data inpath '/origin_data/$APP/db/cart_info/$do_date' OVERWRITE into table ${APP}.ods_cart_info partition(dt='$do_date'); " ods_comment_info=" load data inpath '/origin_data/$APP/db/comment_info/$do_date' OVERWRITE into table ${APP}.ods_comment_info partition(dt='$do_date'); " ods_coupon_info=" load data inpath '/origin_data/$APP/db/coupon_info/$do_date' OVERWRITE into table ${APP}.ods_coupon_info partition(dt='$do_date'); " ods_coupon_use=" load data inpath '/origin_data/$APP/db/coupon_use/$do_date' OVERWRITE into table ${APP}.ods_coupon_use partition(dt='$do_date'); " ods_favor_info=" load data inpath '/origin_data/$APP/db/favor_info/$do_date' OVERWRITE into table ${APP}.ods_favor_info partition(dt='$do_date'); " ods_order_refund_info=" load data inpath '/origin_data/$APP/db/order_refund_info/$do_date' OVERWRITE into table ${APP}.ods_order_refund_info partition(dt='$do_date'); " ods_order_status_log=" load data inpath '/origin_data/$APP/db/order_status_log/$do_date' OVERWRITE into table ${APP}.ods_order_status_log partition(dt='$do_date'); " ods_spu_info=" load data inpath '/origin_data/$APP/db/spu_info/$do_date' OVERWRITE into table ${APP}.ods_spu_info partition(dt='$do_date'); " ods_activity_rule=" load data inpath '/origin_data/$APP/db/activity_rule/$do_date' OVERWRITE into table ${APP}.ods_activity_rule partition(dt='$do_date');" ods_base_dic=" load data inpath '/origin_data/$APP/db/base_dic/$do_date' OVERWRITE into table ${APP}.ods_base_dic partition(dt='$do_date'); " ods_order_detail_activity=" load data inpath '/origin_data/$APP/db/order_detail_activity/$do_date' OVERWRITE into table ${APP}.ods_order_detail_activity partition(dt='$do_date'); " ods_order_detail_coupon=" load data inpath '/origin_data/$APP/db/order_detail_coupon/$do_date' OVERWRITE into table ${APP}.ods_order_detail_coupon partition(dt='$do_date'); " ods_refund_payment=" load data inpath '/origin_data/$APP/db/refund_payment/$do_date' OVERWRITE into table ${APP}.ods_refund_payment partition(dt='$do_date'); " ods_sku_attr_value=" load data inpath '/origin_data/$APP/db/sku_attr_value/$do_date' OVERWRITE into table ${APP}.ods_sku_attr_value partition(dt='$do_date'); " ods_sku_sale_attr_value=" load data inpath '/origin_data/$APP/db/sku_sale_attr_value/$do_date' OVERWRITE into table ${APP}.ods_sku_sale_attr_value partition(dt='$do_date'); " ods_base_province=" load data inpath '/origin_data/$APP/db/base_province/$do_date' OVERWRITE into table ${APP}.ods_base_province;" ods_base_region=" load data inpath '/origin_data/$APP/db/base_region/$do_date' OVERWRITE into table ${APP}.ods_base_region;" case $1 in "ods_order_info"){ hive -e "$ods_order_info" };; "ods_order_detail"){ hive -e "$ods_order_detail" };; "ods_sku_info"){ hive -e "$ods_sku_info" };; "ods_user_info"){ hive -e "$ods_user_info" };; "ods_payment_info"){ hive -e "$ods_payment_info" };; "ods_base_category1"){ hive -e "$ods_base_category1" };; "ods_base_category2"){ hive -e "$ods_base_category2" };; "ods_base_category3"){ hive -e "$ods_base_category3" };; "ods_base_trademark"){ hive -e "$ods_base_trademark" };; "ods_activity_info"){ hive -e "$ods_activity_info" };; "ods_cart_info"){ hive -e "$ods_cart_info" };; "ods_comment_info"){ hive -e "$ods_comment_info" };; "ods_coupon_info"){ hive -e "$ods_coupon_info" };; "ods_coupon_use"){ hive -e "$ods_coupon_use" };; "ods_favor_info"){ hive -e "$ods_favor_info" };; "ods_order_refund_info"){ hive -e "$ods_order_refund_info" };; "ods_order_status_log"){ hive -e "$ods_order_status_log" };; "ods_spu_info"){ hive -e "$ods_spu_info" };; "ods_activity_rule"){ hive -e "$ods_activity_rule" };; "ods_base_dic"){ hive -e "$ods_base_dic" };; "ods_order_detail_activity"){ hive -e "$ods_order_detail_activity" };; "ods_order_detail_coupon"){ hive -e "$ods_order_detail_coupon" };; "ods_refund_payment"){ hive -e "$ods_refund_payment" };; "ods_sku_attr_value"){ hive -e "$ods_sku_attr_value" };; "ods_sku_sale_attr_value"){ hive -e "$ods_sku_sale_attr_value" };; "ods_base_province"){ hive -e "$ods_base_province" };; "ods_base_region"){ hive -e "$ods_base_region" };; "all"){ hive -e "$ods_order_info$ods_order_detail$ods_sku_info$ods_user_info$ods_payment_info$ods_base_category1$ods_base_category2$ods_base_category3$ods_base_trademark$ods_activity_info$ods_cart_info$ods_comment_info$ods_coupon_info$ods_coupon_use$ods_favor_info$ods_order_refund_info$ods_order_status_log$ods_spu_info$ods_activity_rule$ods_base_dic$ods_order_detail_activity$ods_order_detail_coupon$ods_refund_payment$ods_sku_attr_value$ods_sku_sale_attr_value$ods_base_province$ods_base_region" };; esac
（2）增加执行权限
[atguigu@hadoop102 bin]$ chmod +x hdfs_to_ods_db_init.sh
2）脚本使用
（1）执行脚本
[atguigu@hadoop102 bin]$ hdfs_to_ods_db_init.sh all 2020-06-14
（2）查看数据是否导入成功
2.2.29 ODS层业务表每日数据装载脚本
1）编写脚本
（1）在/home/atguigu/bin目录下创建脚本hdfs_to_ods_db.sh
[atguigu@hadoop102 bin]$ vim hdfs_to_ods_db.sh
在脚本中填写如下内容
#!/bin/bash APP=gmall # 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天 if [ -n "$2" ] ;then do_date=$2 else do_date=`date -d "-1 day" +%F` fi ods_order_info=" load data inpath '/origin_data/$APP/db/order_info/$do_date' OVERWRITE into table ${APP}.ods_order_info partition(dt='$do_date');" ods_order_detail=" load data inpath '/origin_data/$APP/db/order_detail/$do_date' OVERWRITE into table ${APP}.ods_order_detail partition(dt='$do_date');" ods_sku_info=" load data inpath '/origin_data/$APP/db/sku_info/$do_date' OVERWRITE into table ${APP}.ods_sku_info partition(dt='$do_date');" ods_user_info=" load data inpath '/origin_data/$APP/db/user_info/$do_date' OVERWRITE into table ${APP}.ods_user_info partition(dt='$do_date');" ods_payment_info=" load data inpath '/origin_data/$APP/db/payment_info/$do_date' OVERWRITE into table ${APP}.ods_payment_info partition(dt='$do_date');" ods_base_category1=" load data inpath '/origin_data/$APP/db/base_category1/$do_date' OVERWRITE into table ${APP}.ods_base_category1 partition(dt='$do_date');" ods_base_category2=" load data inpath '/origin_data/$APP/db/base_category2/$do_date' OVERWRITE into table ${APP}.ods_base_category2 partition(dt='$do_date');" ods_base_category3=" load data inpath '/origin_data/$APP/db/base_category3/$do_date' OVERWRITE into table ${APP}.ods_base_category3 partition(dt='$do_date'); " ods_base_trademark=" load data inpath '/origin_data/$APP/db/base_trademark/$do_date' OVERWRITE into table ${APP}.ods_base_trademark partition(dt='$do_date'); " ods_activity_info=" load data inpath '/origin_data/$APP/db/activity_info/$do_date' OVERWRITE into table ${APP}.ods_activity_info partition(dt='$do_date'); " ods_cart_info=" load data inpath '/origin_data/$APP/db/cart_info/$do_date' OVERWRITE into table ${APP}.ods_cart_info partition(dt='$do_date'); " ods_comment_info=" load data inpath '/origin_data/$APP/db/comment_info/$do_date' OVERWRITE into table ${APP}.ods_comment_info partition(dt='$do_date'); " ods_coupon_info=" load data inpath '/origin_data/$APP/db/coupon_info/$do_date' OVERWRITE into table ${APP}.ods_coupon_info partition(dt='$do_date'); " ods_coupon_use=" load data inpath '/origin_data/$APP/db/coupon_use/$do_date' OVERWRITE into table ${APP}.ods_coupon_use partition(dt='$do_date'); " ods_favor_info=" load data inpath '/origin_data/$APP/db/favor_info/$do_date' OVERWRITE into table ${APP}.ods_favor_info partition(dt='$do_date'); " ods_order_refund_info=" load data inpath '/origin_data/$APP/db/order_refund_info/$do_date' OVERWRITE into table ${APP}.ods_order_refund_info partition(dt='$do_date'); " ods_order_status_log=" load data inpath '/origin_data/$APP/db/order_status_log/$do_date' OVERWRITE into table ${APP}.ods_order_status_log partition(dt='$do_date'); " ods_spu_info=" load data inpath '/origin_data/$APP/db/spu_info/$do_date' OVERWRITE into table ${APP}.ods_spu_info partition(dt='$do_date'); " ods_activity_rule=" load data inpath '/origin_data/$APP/db/activity_rule/$do_date' OVERWRITE into table ${APP}.ods_activity_rule partition(dt='$do_date');" ods_base_dic=" load data inpath '/origin_data/$APP/db/base_dic/$do_date' OVERWRITE into table ${APP}.ods_base_dic partition(dt='$do_date'); " ods_order_detail_activity=" load data inpath '/origin_data/$APP/db/order_detail_activity/$do_date' OVERWRITE into table ${APP}.ods_order_detail_activity partition(dt='$do_date'); " ods_order_detail_coupon=" load data inpath '/origin_data/$APP/db/order_detail_coupon/$do_date' OVERWRITE into table ${APP}.ods_order_detail_coupon partition(dt='$do_date'); " ods_refund_payment=" load data inpath '/origin_data/$APP/db/refund_payment/$do_date' OVERWRITE into table ${APP}.ods_refund_payment partition(dt='$do_date'); " ods_sku_attr_value=" load data inpath '/origin_data/$APP/db/sku_attr_value/$do_date' OVERWRITE into table ${APP}.ods_sku_attr_value partition(dt='$do_date'); " ods_sku_sale_attr_value=" load data inpath '/origin_data/$APP/db/sku_sale_attr_value/$do_date' OVERWRITE into table ${APP}.ods_sku_sale_attr_value partition(dt='$do_date'); " ods_base_province=" load data inpath '/origin_data/$APP/db/base_province/$do_date' OVERWRITE into table ${APP}.ods_base_province;" ods_base_region=" load data inpath '/origin_data/$APP/db/base_region/$do_date' OVERWRITE into table ${APP}.ods_base_region;" case $1 in "ods_order_info"){ hive -e "$ods_order_info" };; "ods_order_detail"){ hive -e "$ods_order_detail" };; "ods_sku_info"){ hive -e "$ods_sku_info" };; "ods_user_info"){ hive -e "$ods_user_info" };; "ods_payment_info"){ hive -e "$ods_payment_info" };; "ods_base_category1"){ hive -e "$ods_base_category1" };; "ods_base_category2"){ hive -e "$ods_base_category2" };; "ods_base_category3"){ hive -e "$ods_base_category3" };; "ods_base_trademark"){ hive -e "$ods_base_trademark" };; "ods_activity_info"){ hive -e "$ods_activity_info" };; "ods_cart_info"){ hive -e "$ods_cart_info" };; "ods_comment_info"){ hive -e "$ods_comment_info" };; "ods_coupon_info"){ hive -e "$ods_coupon_info" };; "ods_coupon_use"){ hive -e "$ods_coupon_use" };; "ods_favor_info"){ hive -e "$ods_favor_info" };; "ods_order_refund_info"){ hive -e "$ods_order_refund_info" };; "ods_order_status_log"){ hive -e "$ods_order_status_log" };; "ods_spu_info"){ hive -e "$ods_spu_info" };; "ods_activity_rule"){ hive -e "$ods_activity_rule" };; "ods_base_dic"){ hive -e "$ods_base_dic" };; "ods_order_detail_activity"){ hive -e "$ods_order_detail_activity" };; "ods_order_detail_coupon"){ hive -e "$ods_order_detail_coupon" };; "ods_refund_payment"){ hive -e "$ods_refund_payment" };; "ods_sku_attr_value"){ hive -e "$ods_sku_attr_value" };; "ods_sku_sale_attr_value"){ hive -e "$ods_sku_sale_attr_value" };; "all"){ hive -e "$ods_order_info$ods_order_detail$ods_sku_info$ods_user_info$ods_payment_info$ods_base_category1$ods_base_category2$ods_base_category3$ods_base_trademark$ods_activity_info$ods_cart_info$ods_comment_info$ods_coupon_info$ods_coupon_use$ods_favor_info$ods_order_refund_info$ods_order_status_log$ods_spu_info$ods_activity_rule$ods_base_dic$ods_order_detail_activity$ods_order_detail_coupon$ods_refund_payment$ods_sku_attr_value$ods_sku_sale_attr_value" };; esac
（2）修改权限
[atguigu@hadoop102 bin]$ chmod +x hdfs_to_ods_db.sh
2）脚本使用
（1）执行脚本
[atguigu@hadoop102 bin]$ hdfs_to_ods_db.sh all 2020-06-14
（2）查看数据是否导入成功
第三章 数仓搭建-DIM层
3.1 商品维度表（全量）
1.建表语句
DROP TABLE IF EXISTS dim_sku_info; CREATE EXTERNAL TABLE dim_sku_info ( `id` STRING COMMENT '商品id', `price` DECIMAL(16,2) COMMENT '商品价格', `sku_name` STRING COMMENT '商品名称', `sku_desc` STRING COMMENT '商品描述', `weight` DECIMAL(16,2) COMMENT '重量', `is_sale` BOOLEAN COMMENT '是否在售', `spu_id` STRING COMMENT 'spu编号', `spu_name` STRING COMMENT 'spu名称', `category3_id` STRING COMMENT '三级分类id', `category3_name` STRING COMMENT '三级分类名称', `category2_id` STRING COMMENT '二级分类id', `category2_name` STRING COMMENT '二级分类名称', `category1_id` STRING COMMENT '一级分类id', `category1_name` STRING COMMENT '一级分类名称', `tm_id` STRING COMMENT '品牌id', `tm_name` STRING COMMENT '品牌名称', `sku_attr_values` ARRAY<STRUCT<attr_id:STRING,value_id:STRING,attr_name:STRING,value_name:STRING>> COMMENT '平台属性', `sku_sale_attr_values` ARRAY<STRUCT<sale_attr_id:STRING,sale_attr_value_id:STRING,sale_attr_name:STRING,sale_attr_value_name:STRING>> COMMENT '销售属性', `create_time` STRING COMMENT '创建时间' ) COMMENT '商品维度表' PARTITIONED BY (`dt` STRING) STORED AS PARQUET LOCATION '/warehouse/gmall/dim/dim_sku_info/' TBLPROPERTIES ("parquet.compression"="lzo");
基本单位是SKU，平台属性和销售属性是结构体数组
2.分区规划
3.数据装载
１）Hive读取索引文件问题
（1）两种方式，分别查询数据有多少行
hive (gmall)> select * from ods_log; Time taken: 0.706 seconds, Fetched: 2955 row(s) hive (gmall)> select count(*) from ods_log; 2959
（2）两次查询结果不一致。
原因是select * from ods_log不执行MR操作，直接采用的是ods_log建表语句中指定的DeprecatedLzoTextInputFormat，能够识别lzo.index为索引文件。
select count(*) from ods_log执行MR操作，会先经过hive.input.format，其默认值为CombineHiveInputFormat，其会先将索引文件当成小文件合并，将其当做普通文件处理。
更严重的是，这会导致LZO文件无法切片。
解决办法：修改CombineHiveInputFormat为HiveInputFormat
hive (gmall)>
set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
２）首日装载
with sku as ( select id, price, sku_name, sku_desc, weight, is_sale, spu_id, category3_id, tm_id, create_time from ods_sku_info where dt='2020-06-14' ), spu as ( select id, spu_name from ods_spu_info where dt='2020-06-14' ), c3 as ( select id, name, category2_id from ods_base_category3 where dt='2020-06-14' ), c2 as ( select id, name, category1_id from ods_base_category2 where dt='2020-06-14' ), c1 as ( select id, name from ods_base_category1 where dt='2020-06-14' ), tm as ( select id, tm_name from ods_base_trademark where dt='2020-06-14' ), attr as ( select sku_id, collect_set(named_struct('attr_id',attr_id,'value_id',value_id,'attr_name',attr_name,'value_name',value_name)) attrs from ods_sku_attr_value where dt='2020-06-14' group by sku_id ), sale_attr as ( select sku_id, collect_set(named_struct('sale_attr_id',sale_attr_id,'sale_attr_value_id',sale_attr_value_id,'sale_attr_name',sale_attr_name,'sale_attr_value_name',sale_attr_value_name)) sale_attrs from ods_sku_sale_attr_value where dt='2020-06-14' group by sku_id ) insert overwrite table dim_sku_info partition(dt='2020-06-14') select sku.id, sku.price, sku.sku_name, sku.sku_desc, sku.weight, sku.is_sale, sku.spu_id, spu.spu_name, sku.category3_id, c3.name, c3.category2_id, c2.name, c2.category1_id, c1.name, sku.tm_id, tm.tm_name, attr.attrs, sale_attr.sale_attrs, sku.create_time from sku left join spu on sku.spu_id=spu.id left join c3 on sku.category3_id=c3.id left join c2 on c3.category2_id=c2.id left join c1 on c2.category1_id=c1.id left join tm on sku.tm_id=tm.id left join attr on sku.id=attr.sku_id left join sale_attr on sku.id=sale_attr.sku_id;
left join表示join时以左表的全部数据为准，右边与之关联
３）每日装载
with sku as ( select id, price, sku_name, sku_desc, weight, is_sale, spu_id, category3_id, tm_id, create_time from ods_sku_info where dt='2020-06-15' ), spu as ( select id, spu_name from ods_spu_info where dt='2020-06-15' ), c3 as ( select id, name, category2_id from ods_base_category3 where dt='2020-06-15' ), c2 as ( select id, name, category1_id from ods_base_category2 where dt='2020-06-15' ), c1 as ( select id, name from ods_base_category1 where dt='2020-06-15' ), tm as ( select id, tm_name from ods_base_trademark where dt='2020-06-15' ), attr as ( select sku_id, collect_set(named_struct('attr_id',attr_id,'value_id',value_id,'attr_name',attr_name,'value_name',value_name)) attrs from ods_sku_attr_value where dt='2020-06-15' group by sku_id ), sale_attr as ( select sku_id, collect_set(named_struct('sale_attr_id',sale_attr_id,'sale_attr_value_id',sale_attr_value_id,'sale_attr_name',sale_attr_name,'sale_attr_value_name',sale_attr_value_name)) sale_attrs from ods_sku_sale_attr_value where dt='2020-06-15' group by sku_id ) insert overwrite table dim_sku_info partition(dt='2020-06-15') select sku.id, sku.price, sku.sku_name, sku.sku_desc, sku.weight, sku.is_sale, sku.spu_id, spu.spu_name, sku.category3_id, c3.name, c3.category2_id, c2.name, c2.category1_id, c1.name, sku.tm_id, tm.tm_name, attr.attrs, sale_attr.sale_attrs, sku.create_time from sku left join spu on sku.spu_id=spu.id left join c3 on sku.category3_id=c3.id left join c2 on c3.category2_id=c2.id left join c1 on c2.category1_id=c1.id left join tm on sku.tm_id=tm.id left join attr on sku.id=attr.sku_id left join sale_attr on sku.id=sale_attr.sku_id;
3.2 优惠券维度表（全量）
1.建表语句
DROP TABLE IF EXISTS dim_coupon_info; CREATE EXTERNAL TABLE dim_coupon_info( `id` STRING COMMENT '购物券编号', `coupon_name` STRING COMMENT '购物券名称', `coupon_type` STRING COMMENT '购物券类型 1 现金券 2 折扣券 3 满减券 4 满件打折券', `condition_amount` DECIMAL(16,2) COMMENT '满额数', `condition_num` BIGINT COMMENT '满件数', `activity_id` STRING COMMENT '活动编号', `benefit_amount` DECIMAL(16,2) COMMENT '减金额', `benefit_discount` DECIMAL(16,2) COMMENT '折扣', `create_time` STRING COMMENT '创建时间', `range_type` STRING COMMENT '范围类型 1、商品 2、品类 3、品牌', `limit_num` BIGINT COMMENT '最多领取次数', `taken_count` BIGINT COMMENT '已领取次数', `start_time` STRING COMMENT '可以领取的开始日期', `end_time` STRING COMMENT '可以领取的结束日期', `operate_time` STRING COMMENT '修改时间', `expire_time` STRING COMMENT '过期时间' ) COMMENT '优惠券维度表' PARTITIONED BY (`dt` STRING) STORED AS PARQUET LOCATION '/warehouse/gmall/dim/dim_coupon_info/' TBLPROPERTIES ("parquet.compression"="lzo");
基本单位是优惠券活动
2.分区规划
3.数据装载
1）首日装载
insert overwrite table dim_coupon_info partition(dt='2020-06-14') select id, coupon_name, coupon_type, condition_amount, condition_num, activity_id, benefit_amount, benefit_discount, create_time, range_type, limit_num, taken_count, start_time, end_time, operate_time, expire_time from ods_coupon_info where dt='2020-06-14';
2）每日装载
insert overwrite table dim_coupon_info partition(dt='2020-06-15')
select
id,
coupon_name,
coupon_type,
condition_amount,
condition_num,
activity_id,
benefit_amount,
benefit_discount,
create_time,
range_type,
limit_num,
taken_count,
start_time,
end_time,
operate_time,
expire_time
from ods_coupon_info
where dt='2020-06-15';
3.3 活动维度表（全量）
1.建表语句
DROP TABLE IF EXISTS dim_activity_rule_info; CREATE EXTERNAL TABLE dim_activity_rule_info( `activity_rule_id` STRING COMMENT '活动规则ID', `activity_id` STRING COMMENT '活动ID', `activity_name` STRING COMMENT '活动名称', `activity_type` STRING COMMENT '活动类型', `start_time` STRING COMMENT '开始时间', `end_time` STRING COMMENT '结束时间', `create_time` STRING COMMENT '创建时间', `condition_amount` DECIMAL(16,2) COMMENT '满减金额', `condition_num` BIGINT COMMENT '满减件数', `benefit_amount` DECIMAL(16,2) COMMENT '优惠金额', `benefit_discount` DECIMAL(16,2) COMMENT '优惠折扣', `benefit_level` STRING COMMENT '优惠级别' ) COMMENT '活动信息表' PARTITIONED BY (`dt` STRING) STORED AS PARQUET LOCATION '/warehouse/gmall/dim/dim_activity_rule_info/' TBLPROPERTIES ("parquet.compression"="lzo");
2.分区规划
3.数据装载
1）首日装载
insert overwrite table dim_activity_rule_info partition(dt='2020-06-14') select ar.id, ar.activity_id, ai.activity_name, ar.activity_type, ai.start_time, ai.end_time, ai.create_time, ar.condition_amount, ar.condition_num, ar.benefit_amount, ar.benefit_discount, ar.benefit_level from ( select id, activity_id, activity_type, condition_amount, condition_num, benefit_amount, benefit_discount, benefit_level from ods_activity_rule where dt='2020-06-14' )ar left join ( select id, activity_name, start_time, end_time, create_time from ods_activity_info where dt='2020-06-14' )ai on ar.activity_id=ai.id;
2）每日转载
insert overwrite table dim_activity_rule_info partition(dt='2020-06-15') select ar.id, ar.activity_id, ai.activity_name, ar.activity_type, ai.start_time, ai.end_time, ai.create_time, ar.condition_amount, ar.condition_num, ar.benefit_amount, ar.benefit_discount, ar.benefit_level from ( select id, activity_id, activity_type, condition_amount, condition_num, benefit_amount, benefit_discount, benefit_level from ods_activity_rule where dt='2020-06-15' )ar left join ( select id, activity_name, start_time, end_time, create_time from ods_activity_info where dt='2020-06-15' )ai on ar.activity_id=ai.id;
3.4 地区维度表（特殊）
1.建表语句
DROP TABLE IF EXISTS dim_base_province; CREATE EXTERNAL TABLE dim_base_province ( `id` STRING COMMENT 'id', `province_name` STRING COMMENT '省市名称', `area_code` STRING COMMENT '地区编码', `iso_code` STRING COMMENT 'ISO-3166编码，供可视化使用', `iso_3166_2` STRING COMMENT 'IOS-3166-2编码，供可视化使用', `region_id` STRING COMMENT '地区id', `region_name` STRING COMMENT '地区名称' ) COMMENT '地区维度表' STORED AS PARQUET LOCATION '/warehouse/gmall/dim/dim_base_province/' TBLPROPERTIES ("parquet.compression"="lzo");
2.数据装载
地区维度表数据相对稳定，变化概率较低，故无需每日装载。
insert overwrite table dim_base_province select bp.id, bp.name, bp.area_code, bp.iso_code, bp.iso_3166_2, bp.region_id, br.region_name from ods_base_province bp join ods_base_region br on bp.region_id = br.id;
3.5 时间维度表（特殊）
1.建表语句
DROP TABLE IF EXISTS dim_date_info; CREATE EXTERNAL TABLE dim_date_info( `date_id` STRING COMMENT '日', `week_id` STRING COMMENT '周ID', `week_day` STRING COMMENT '周几', `day` STRING COMMENT '每月的第几天', `month` STRING COMMENT '第几月', `quarter` STRING COMMENT '第几季度', `year` STRING COMMENT '年', `is_workday` STRING COMMENT '是否是工作日', `holiday_id` STRING COMMENT '节假日' ) COMMENT '时间维度表' STORED AS PARQUET LOCATION '/warehouse/gmall/dim/dim_date_info/' TBLPROPERTIES ("parquet.compression"="lzo");
2.数据装载
通常情况下，时间维度表的数据并不是来自于业务系统，而是手动写入，并且由于时间维度表数据的可预见性，无须每日导入，一般可一次性导入一年的数据。
1）创建临时表
DROP TABLE IF EXISTS tmp_dim_date_info; CREATE EXTERNAL TABLE tmp_dim_date_info ( `date_id` STRING COMMENT '日', `week_id` STRING COMMENT '周ID', `week_day` STRING COMMENT '周几', `day` STRING COMMENT '每月的第几天', `month` STRING COMMENT '第几月', `quarter` STRING COMMENT '第几季度', `year` STRING COMMENT '年', `is_workday` STRING COMMENT '是否是工作日', `holiday_id` STRING COMMENT '节假日' ) COMMENT '时间维度表' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LOCATION '/warehouse/gmall/tmp/tmp_dim_date_info/';
2）将数据文件上传到HFDS上临时表指定路径/warehouse/gmall/tmp/tmp_dim_date_info/
hdfs dfs -mkdir -p /warehouse/gmall/tmp/tmp_dim_date_info/ hdfs dfs -put date_info.txt /warehouse/gmall/tmp/tmp_dim_date_info/
3）执行以下语句将其导入时间维度表
insert overwrite table dim_date_info select * from tmp_dim_date_info;
4）检查数据是否导入成功
select * from dim_date_info;
3.6 用户维度表（拉链表）
3.6.1 拉链表概述
1）什么是拉链表
拉链表相关
2）为什么要做拉链表
3）如何使用拉链表
4）拉链表形成过程
3.6.2 制作拉链表
1.建表语句
DROP TABLE IF EXISTS dim_user_info; CREATE EXTERNAL TABLE dim_user_info( `id` STRING COMMENT '用户id', `login_name` STRING COMMENT '用户名称', `nick_name` STRING COMMENT '用户昵称', `name` STRING COMMENT '用户姓名', `phone_num` STRING COMMENT '手机号码', `email` STRING COMMENT '邮箱', `user_level` STRING COMMENT '用户等级', `birthday` STRING COMMENT '生日', `gender` STRING COMMENT '性别', `create_time` STRING COMMENT '创建时间', `operate_time` STRING COMMENT '操作时间', `start_date` STRING COMMENT '开始日期', `end_date` STRING COMMENT '结束日期' ) COMMENT '用户表' PARTITIONED BY (`dt` STRING) STORED AS PARQUET LOCATION '/warehouse/gmall/dim/dim_user_info/' TBLPROPERTIES ("parquet.compression"="lzo");
2.分区规划
3.数据装载
1）首日装载
拉链表首日装载，需要进行初始化操作，具体工作为将截止到初始化当日的全部历史用户导入一次性导入到拉链表中。目前的ods_user_info表的第一个分区，即2020-06-14分区中就是全部的历史用户，故将该分区数据进行一定处理后导入拉链表的9999-99-99分区即可。
insert overwrite table dim_user_info partition(dt='9999-99-99') select id, login_name, nick_name, md5(name), md5(phone_num), md5(email), user_level, birthday, gender, create_time, operate_time, '2020-06-14', '9999-99-99' from ods_user_info where dt='2020-06-14';
2）每日装载
（1）实现思路
修改过的进入过期状态，修改结束日期为前一天日期，写入过期分区，使用动态分区，分别写入9999-99-99分区和2020-06-14分区。
（2）sql编写
with tmp as ( select old.id old_id, old.login_name old_login_name, old.nick_name old_nick_name, old.name old_name, old.phone_num old_phone_num, old.email old_email, old.user_level old_user_level, old.birthday old_birthday, old.gender old_gender, old.create_time old_create_time, old.operate_time old_operate_time, old.start_date old_start_date, old.end_date old_end_date, new.id new_id, new.login_name new_login_name, new.nick_name new_nick_name, new.name new_name, new.phone_num new_phone_num, new.email new_email, new.user_level new_user_level, new.birthday new_birthday, new.gender new_gender, new.create_time new_create_time, new.operate_time new_operate_time, new.start_date new_start_date, new.end_date new_end_date from ( select id, login_name, nick_name, name, phone_num, email, user_level, birthday, gender, create_time, operate_time, start_date, end_date from dim_user_info where dt='9999-99-99' )old full outer join ( select id, login_name, nick_name, md5(name) name, md5(phone_num) phone_num, md5(email) email, user_level, birthday, gender, create_time, operate_time, '2020-06-15' start_date, '9999-99-99' end_date from ods_user_info where dt='2020-06-15' )new on old.id=new.id ) insert overwrite table dim_user_info partition(dt) select nvl(new_id,old_id), nvl(new_login_name,old_login_name), nvl(new_nick_name,old_nick_name), nvl(new_name,old_name), nvl(new_phone_num,old_phone_num), nvl(new_email,old_email), nvl(new_user_level,old_user_level), nvl(new_birthday,old_birthday), nvl(new_gender,old_gender), nvl(new_create_time,old_create_time), nvl(new_operate_time,old_operate_time), nvl(new_start_date,old_start_date), nvl(new_end_date,old_end_date), nvl(new_end_date,old_end_date) dt from tmp union all select old_id, old_login_name, old_nick_name, old_name, old_phone_num, old_email, old_user_level, old_birthday, old_gender, old_create_time, old_operate_time, old_start_date, cast(date_add('2020-06-15',-1) as string), cast(date_add('2020-06-15',-1) as string) dt from tmp where new_id is not null and old_id is not null;
NVL()函数的功能是实现空值的转换。例如
NVL(string1,replace_with)中：
当第一个参数（string1）为空时，返回第二个参数（replace_with）；
当第一个参数（string1)不为空时，则返回第一个参数（string1）。
3.7 DIM层首日数据装载脚本
1）编写脚本
（1）在/home/atguigu/bin目录下创建脚本ods_to_dim_db_init.sh
[atguigu@hadoop102 bin]$ vim ods_to_dim_db_init.sh
在脚本中填写如下内容
#!/bin/bash APP=gmall if [ -n "$2" ] ;then do_date=$2 else echo "请传入日期参数" exit fi dim_user_info=" set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; insert overwrite table ${APP}.dim_user_info partition(dt='9999-99-99') select id, login_name, nick_name, md5(name), md5(phone_num), md5(email), user_level, birthday, gender, create_time, operate_time, '$do_date', '9999-99-99' from ${APP}.ods_user_info where dt='$do_date'; " dim_sku_info=" set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; with sku as ( select id, price, sku_name, sku_desc, weight, is_sale, spu_id, category3_id, tm_id, create_time from ${APP}.ods_sku_info where dt='$do_date' ), spu as ( select id, spu_name from ${APP}.ods_spu_info where dt='$do_date' ), c3 as ( select id, name, category2_id from ${APP}.ods_base_category3 where dt='$do_date' ), c2 as ( select id, name, category1_id from ${APP}.ods_base_category2 where dt='$do_date' ), c1 as ( select id, name from ${APP}.ods_base_category1 where dt='$do_date' ), tm as ( select id, tm_name from ${APP}.ods_base_trademark where dt='$do_date' ), attr as ( select sku_id, collect_set(named_struct('attr_id',attr_id,'value_id',value_id,'attr_name',attr_name,'value_name',value_name)) attrs from ${APP}.ods_sku_attr_value where dt='$do_date' group by sku_id ), sale_attr as ( select sku_id, collect_set(named_struct('sale_attr_id',sale_attr_id,'sale_attr_value_id',sale_attr_value_id,'sale_attr_name',sale_attr_name,'sale_attr_value_name',sale_attr_value_name)) sale_attrs from ${APP}.ods_sku_sale_attr_value where dt='$do_date' group by sku_id ) insert overwrite table ${APP}.dim_sku_info partition(dt='$do_date') select sku.id, sku.price, sku.sku_name, sku.sku_desc, sku.weight, sku.is_sale, sku.spu_id, spu.spu_name, sku.category3_id, c3.name, c3.category2_id, c2.name, c2.category1_id, c1.name, sku.tm_id, tm.tm_name, attr.attrs, sale_attr.sale_attrs, sku.create_time from sku left join spu on sku.spu_id=spu.id left join c3 on sku.category3_id=c3.id left join c2 on c3.category2_id=c2.id left join c1 on c2.category1_id=c1.id left join tm on sku.tm_id=tm.id left join attr on sku.id=attr.sku_id left join sale_attr on sku.id=sale_attr.sku_id; " dim_base_province=" set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; insert overwrite table ${APP}.dim_base_province select bp.id, bp.name, bp.area_code, bp.iso_code, bp.iso_3166_2, bp.region_id, br.region_name from ${APP}.ods_base_province bp join ${APP}.ods_base_region br on bp.region_id = br.id; " dim_coupon_info=" set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; insert overwrite table ${APP}.dim_coupon_info partition(dt='$do_date') select id, coupon_name, coupon_type, condition_amount, condition_num, activity_id, benefit_amount, benefit_discount, create_time, range_type, limit_num, taken_count, start_time, end_time, operate_time, expire_time from ${APP}.ods_coupon_info where dt='$do_date'; " dim_activity_rule_info=" set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; insert overwrite table ${APP}.dim_activity_rule_info partition(dt='$do_date') select ar.id, ar.activity_id, ai.activity_name, ar.activity_type, ai.start_time, ai.end_time, ai.create_time, ar.condition_amount, ar.condition_num, ar.benefit_amount, ar.benefit_discount, ar.benefit_level from ( select id, activity_id, activity_type, condition_amount, condition_num, benefit_amount, benefit_discount, benefit_level from ${APP}.ods_activity_rule where dt='$do_date' )ar left join ( select id, activity_name, start_time, end_time, create_time from ${APP}.ods_activity_info where dt='$do_date' )ai on ar.activity_id=ai.id; " case $1 in "dim_user_info"){ hive -e "$dim_user_info" };; "dim_sku_info"){ hive -e "$dim_sku_info" };; "dim_base_province"){ hive -e "$dim_base_province" };; "dim_coupon_info"){ hive -e "$dim_coupon_info" };; "dim_activity_rule_info"){ hive -e "$dim_activity_rule_info" };; "all"){ hive -e "$dim_user_info$dim_sku_info$dim_coupon_info$dim_activity_rule_info$dim_base_province" };; esac
（2）增加执行权限
[atguigu@hadoop102 bin]$ chmod +x ods_to_dim_db_init.sh
2）脚本使用
（1）执行脚本
[atguigu@hadoop102 bin]$ ods_to_dim_db_init.sh all 2020-06-14
注意：该脚本不包含时间维度表的装载，时间维度表需手动装载数据，参考3.5节。
（2）查看数据是否导入成功
3.8 DIM层每日数据装载脚本
1）编写脚本
（1）在/home/atguigu/bin目录下创建脚本ods_to_dim_db.sh
[atguigu@hadoop102 bin]$ vim ods_to_dim_db.sh
在脚本中填写如下内容
#!/bin/bash APP=gmall # 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天 if [ -n "$2" ] ;then do_date=$2 else do_date=`date -d "-1 day" +%F` fi dim_user_info=" set hive.exec.dynamic.partition.mode=nonstrict; set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; with tmp as ( select old.id old_id, old.login_name old_login_name, old.nick_name old_nick_name, old.name old_name, old.phone_num old_phone_num, old.email old_email, old.user_level old_user_level, old.birthday old_birthday, old.gender old_gender, old.create_time old_create_time, old.operate_time old_operate_time, old.start_date old_start_date, old.end_date old_end_date, new.id new_id, new.login_name new_login_name, new.nick_name new_nick_name, new.name new_name, new.phone_num new_phone_num, new.email new_email, new.user_level new_user_level, new.birthday new_birthday, new.gender new_gender, new.create_time new_create_time, new.operate_time new_operate_time, new.start_date new_start_date, new.end_date new_end_date from ( select id, login_name, nick_name, name, phone_num, email, user_level, birthday, gender, create_time, operate_time, start_date, end_date from ${APP}.dim_user_info where dt='9999-99-99' and start_date<'$do_date' )old full outer join ( select id, login_name, nick_name, md5(name) name, md5(phone_num) phone_num, md5(email) email, user_level, birthday, gender, create_time, operate_time, '$do_date' start_date, '9999-99-99' end_date from ${APP}.ods_user_info where dt='$do_date' )new on old.id=new.id ) insert overwrite table ${APP}.dim_user_info partition(dt) select nvl(new_id,old_id), nvl(new_login_name,old_login_name), nvl(new_nick_name,old_nick_name), nvl(new_name,old_name), nvl(new_phone_num,old_phone_num), nvl(new_email,old_email), nvl(new_user_level,old_user_level), nvl(new_birthday,old_birthday), nvl(new_gender,old_gender), nvl(new_create_time,old_create_time), nvl(new_operate_time,old_operate_time), nvl(new_start_date,old_start_date), nvl(new_end_date,old_end_date), nvl(new_end_date,old_end_date) dt from tmp union all select old_id, old_login_name, old_nick_name, old_name, old_phone_num, old_email, old_user_level, old_birthday, old_gender, old_create_time, old_operate_time, old_start_date, cast(date_add('$do_date',-1) as string), cast(date_add('$do_date',-1) as string) dt from tmp where new_id is not null and old_id is not null; " dim_sku_info=" set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; with sku as ( select id, price, sku_name, sku_desc, weight, is_sale, spu_id, category3_id, tm_id, create_time from ${APP}.ods_sku_info where dt='$do_date' ), spu as ( select id, spu_name from ${APP}.ods_spu_info where dt='$do_date' ), c3 as ( select id, name, category2_id from ${APP}.ods_base_category3 where dt='$do_date' ), c2 as ( select id, name, category1_id from ${APP}.ods_base_category2 where dt='$do_date' ), c1 as ( select id, name from ${APP}.ods_base_category1 where dt='$do_date' ), tm as ( select id, tm_name from ${APP}.ods_base_trademark where dt='$do_date' ), attr as ( select sku_id, collect_set(named_struct('attr_id',attr_id,'value_id',value_id,'attr_name',attr_name,'value_name',value_name)) attrs from ${APP}.ods_sku_attr_value where dt='$do_date' group by sku_id ), sale_attr as ( select sku_id, collect_set(named_struct('sale_attr_id',sale_attr_id,'sale_attr_value_id',sale_attr_value_id,'sale_attr_name',sale_attr_name,'sale_attr_value_name',sale_attr_value_name)) sale_attrs from ${APP}.ods_sku_sale_attr_value where dt='$do_date' group by sku_id ) insert overwrite table ${APP}.dim_sku_info partition(dt='$do_date') select sku.id, sku.price, sku.sku_name, sku.sku_desc, sku.weight, sku.is_sale, sku.spu_id, spu.spu_name, sku.category3_id, c3.name, c3.category2_id, c2.name, c2.category1_id, c1.name, sku.tm_id, tm.tm_name, attr.attrs, sale_attr.sale_attrs, sku.create_time from sku left join spu on sku.spu_id=spu.id left join c3 on sku.category3_id=c3.id left join c2 on c3.category2_id=c2.id left join c1 on c2.category1_id=c1.id left join tm on sku.tm_id=tm.id left join attr on sku.id=attr.sku_id left join sale_attr on sku.id=sale_attr.sku_id; " dim_base_province=" set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; insert overwrite table ${APP}.dim_base_province select bp.id, bp.name, bp.area_code, bp.iso_code, bp.iso_3166_2, bp.region_id, bp.name from ${APP}.ods_base_province bp join ${APP}.ods_base_region br on bp.region_id = br.id; " dim_coupon_info=" set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; insert overwrite table ${APP}.dim_coupon_info partition(dt='$do_date') select id, coupon_name, coupon_type, condition_amount, condition_num, activity_id, benefit_amount, benefit_discount, create_time, range_type, limit_num, taken_count, start_time, end_time, operate_time, expire_time from ${APP}.ods_coupon_info where dt='$do_date'; " dim_activity_rule_info=" set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; insert overwrite table ${APP}.dim_activity_rule_info partition(dt='$do_date') select ar.id, ar.activity_id, ai.activity_name, ar.activity_type, ai.start_time, ai.end_time, ai.create_time, ar.condition_amount, ar.condition_num, ar.benefit_amount, ar.benefit_discount, ar.benefit_level from ( select id, activity_id, activity_type, condition_amount, condition_num, benefit_amount, benefit_discount, benefit_level from ${APP}.ods_activity_rule where dt='$do_date' )ar left join ( select id, activity_name, start_time, end_time, create_time from ${APP}.ods_activity_info where dt='$do_date' )ai on ar.activity_id=ai.id; " case $1 in "dim_user_info"){ hive -e "$dim_user_info" };; "dim_sku_info"){ hive -e "$dim_sku_info" };; "dim_base_province"){ hive -e "$dim_base_province" };; "dim_coupon_info"){ hive -e "$dim_coupon_info" };; "dim_activity_rule_info"){ hive -e "$dim_activity_rule_info" };; "all"){ hive -e "$dim_user_info$dim_sku_info$dim_coupon_info$dim_activity_rule_info" };; esac
（2）增加执行权限
[atguigu@hadoop102 bin]$ chmod +x ods_to_dim_db.sh
2）脚本使用
（1）执行脚本
[atguigu@hadoop102 bin]$ ods_to_dim_db.sh all 2020-06-14
（2）查看数据是否导入成功
第四章 数仓搭建-DWD层
1）对用户行为数据解析。
2）对业务数据采用维度模型重新建模。
4.1 DWD层（用户行为日志）
4.1.1 日志解析思路
1）日志结构回顾
（1）页面埋点日志
（2）启动日志
2）日志解析思路
3.1.2 get_json_object函数使用
1）数据
[{"name":"大郎","sex":"男","age":"25"},{"name":"西门庆","sex":"男","age":"47"}]
2）取出第一个json对象
hive (gmall)> select get_json_object('[{"name":"大郎","sex":"男","age":"25"},{"name":"西门庆","sex":"男","age":"47"}]','$[0]');
结果是：{"name":"大郎","sex":"男","age":"25"}
3）取出第一个json的age字段的值
hive (gmall)> SELECT get_json_object('[{"name":"大郎","sex":"男","age":"25"},{"name":"西门庆","sex":"男","age":"47"}]',"$[0].age");
结果是：25
4.1.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压缩 ;
2）数据导入
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;
3）查看数据
select * from dwd_start_log where dt='2020-06-14' limit 2;
4.1.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');
2）数据导入
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;
3）查看数据
select * from dwd_page_log where dt='2020-06-14' limit 2;
4.1.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');
2）创建UDTF函数——设计思路
3）创建UDTF函数——编写代码
（1）创建一个maven工程：hivefunction
（2）创建包名：com.atguigu.hive.udtf
（3）引入如下依赖
<dependencies> <dependency> <groupId>org.apache.hivegroupId> <artifactId>hive-execartifactId> <version>3.1.2version> dependency> dependencies>
（4）编码
package com.atguigu.gmall.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 定义返回值名称和类型 ListfieldNames = new ArrayList (); List fieldOIs = new ArrayList (); 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 { } }
4）创建函数
（1）打包
（2）将hivefunction-1.0-SNAPSHOT.jar上传到hadoop102的/opt/module，然后再将该jar包上传到HDFS的/user/hive/jars路径下
[atguigu@hadoop102 module]$ hadoop fs -mkdir -p /user/hive/jars
[atguigu@hadoop102 module]$ hadoop fs -put hivefunction-1.0-SNAPSHOT.jar /user/hive/jars
（3）创建永久函数与开发好的java class关联
create function explode_json_array as 'com.atguigu.gmall.hive.udtf.ExplodeJSONArray' using jar 'hdfs://hadoop102:8020/user/hive/jars/hivefunction-1.0-SNAPSHOT.jar';
（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;
3）查看数据
select * from dwd_action_log where dt='2020-06-14' limit 2;
4.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');
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;
3）查看数据
select * from dwd_display_log where dt='2020-06-14' limit 2;
4.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');
说明：此处为对动作数组和曝光数组做处理，如需分析错误与单个动作或曝光的关联，可先使用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;
5）查看数据
select * from dwd_error_log where dt='2020-06-14' limit 2;
4.1.8 DWD层用户行为数据加载脚本
1）编写脚本
（1）在hadoop102的/home/atguigu/bin目录下创建脚本
[atguigu@hadoop102 bin]$ vim ods_to_dwd_log.sh
在脚本中编写如下内容
#!/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
（2）增加脚本执行权限
[atguigu@hadoop102 bin]$ chmod 777 ods_to_dwd_log.sh
2）脚本使用
（1）执行脚本
[atguigu@hadoop102 module]$ ods_to_dwd_log.sh all 2020-06-14
（2）查询导入结果
4.2 DWD层（业务数据）
业务数据方面DWD层的搭建主要注意点在于维度建模。
4.2.1 评价事实表（事务型事实表）
1）建表语句
DROP TABLE IF EXISTS dwd_comment_info; CREATE EXTERNAL TABLE dwd_comment_info( `id` STRING COMMENT '编号', `user_id` STRING COMMENT '用户ID', `sku_id` STRING COMMENT '商品sku', `spu_id` STRING COMMENT '商品spu', `order_id` STRING COMMENT '订单ID', `appraise` STRING COMMENT '评价(好评、中评、差评、默认评价)', `create_time` STRING COMMENT '评价时间' ) COMMENT '评价事实表' PARTITIONED BY (`dt` STRING) STORED AS PARQUET LOCATION '/warehouse/gmall/dwd/dwd_comment_info/' TBLPROPERTIES ("parquet.compression"="lzo");
2）分区规划
3）数据装载
（1）首日装载
insert overwrite table 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 ods_comment_info where dt='2020-06-14';
（2）每日装载
insert overwrite table dwd_comment_info partition(dt='2020-06-15') select id, user_id, sku_id, spu_id, order_id, appraise, create_time from ods_comment_info where dt='2020-06-15';
4.2.2 订单明细事实表（事务型事实表）
1）建表语句
DROP TABLE IF EXISTS dwd_order_detail; CREATE EXTERNAL TABLE dwd_order_detail ( `id` STRING COMMENT '订单编号', `order_id` STRING COMMENT '订单号', `user_id` STRING COMMENT '用户id', `sku_id` STRING COMMENT 'sku商品id', `province_id` STRING COMMENT '省份ID', `activity_id` STRING COMMENT '活动ID', `activity_rule_id` STRING COMMENT '活动规则ID', `coupon_id` STRING COMMENT '优惠券ID', `create_time` STRING COMMENT '创建时间', `source_type` STRING COMMENT '来源类型', `source_id` STRING COMMENT '来源编号', `sku_num` BIGINT COMMENT '商品数量', `original_amount` DECIMAL(16,2) COMMENT '原始价格', `split_activity_amount` DECIMAL(16,2) COMMENT '活动优惠分摊', `split_coupon_amount` DECIMAL(16,2) COMMENT '优惠券优惠分摊', `split_final_amount` DECIMAL(16,2) COMMENT '最终价格分摊' ) COMMENT '订单明细事实表表' PARTITIONED BY (`dt` STRING) STORED AS PARQUET LOCATION '/warehouse/gmall/dwd/dwd_order_detail/' TBLPROPERTIES ("parquet.compression"="lzo");
2）分区规划
3）数据装载
（1）首日装载
insert overwrite table 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 ods_order_detail where dt='2020-06-14' )od left join ( select id, user_id, province_id from ods_order_info where dt='2020-06-14' )oi on od.order_id=oi.id left join ( select order_detail_id, activity_id, activity_rule_id from ods_order_detail_activity where dt='2020-06-14' )oda on od.id=oda.order_detail_id left join ( select order_detail_id, coupon_id from ods_order_detail_coupon where dt='2020-06-14' )odc on od.id=odc.order_detail_id;
（2）每日装载
insert overwrite table dwd_order_detail partition(dt='2020-06-15') 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 ods_order_detail where dt='2020-06-15' )od left join ( select id, user_id, province_id from ods_order_info where dt='2020-06-15' )oi on od.order_id=oi.id left join ( select order_detail_id, activity_id, activity_rule_id from ods_order_detail_activity where dt='2020-06-15' )oda on od.id=oda.order_detail_id left join ( select order_detail_id, coupon_id from ods_order_detail_coupon where dt='2020-06-15' )odc on od.id=odc.order_detail_id;
4.2.3 退单事实表（事务型事实表）
1）建表语句
DROP TABLE IF EXISTS dwd_order_refund_info; CREATE EXTERNAL TABLE dwd_order_refund_info( `id` STRING COMMENT '编号', `user_id` STRING COMMENT '用户ID', `order_id` STRING COMMENT '订单ID', `sku_id` STRING COMMENT '商品ID', `province_id` STRING COMMENT '地区ID', `refund_type` STRING COMMENT '退单类型', `refund_num` BIGINT COMMENT '退单件数', `refund_amount` DECIMAL(16,2) COMMENT '退单金额', `refund_reason_type` STRING COMMENT '退单原因类型', `create_time` STRING COMMENT '退单时间' ) COMMENT '退单事实表' PARTITIONED BY (`dt` STRING) STORED AS PARQUET LOCATION '/warehouse/gmall/dwd/dwd_order_refund_info/' TBLPROPERTIES ("parquet.compression"="lzo");
2）分区规划
3）数据装载
（1）首日装载
insert overwrite table 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 ods_order_refund_info where dt='2020-06-14' )ri left join ( select id,province_id from ods_order_info where dt='2020-06-14' )oi on ri.order_id=oi.id;
（2）每日装载
insert overwrite table dwd_order_refund_info partition(dt='2020-06-15') 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 ods_order_refund_info where dt='2020-06-15' )ri left join ( select id,province_id from ods_order_info where dt='2020-06-15' )oi on ri.order_id=oi.id;
3）查询加载结果
4.2.4 加购事实表（周期型快照事实表，每日快照）
1）建表语句
DROP TABLE IF EXISTS dwd_cart_info; CREATE EXTERNAL TABLE dwd_cart_info( `id` STRING COMMENT '编号', `user_id` STRING COMMENT '用户ID', `sku_id` STRING COMMENT '商品ID', `source_type` STRING COMMENT '来源类型', `source_id` STRING COMMENT '来源编号', `cart_price` DECIMAL(16,2) COMMENT '加入购物车时的价格', `is_ordered` STRING COMMENT '是否已下单', `create_time` STRING COMMENT '创建时间', `operate_time` STRING COMMENT '修改时间', `order_time` STRING COMMENT '下单时间', `sku_num` BIGINT COMMENT '加购数量' ) COMMENT '加购事实表' PARTITIONED BY (`dt` STRING) STORED AS PARQUET LOCATION '/warehouse/gmall/dwd/dwd_cart_info/' TBLPROPERTIES ("parquet.compression"="lzo");
2）分区规划
3）数据装载
（1）首日装载
insert overwrite table dwd_cart_info partition(dt='2020-06-14') select id, user_id, sku_id, source_type, source_id, cart_price, is_ordered, create_time, operate_time, order_time, sku_num from ods_cart_info where dt='2020-06-14';
（2）每日装载
insert overwrite table dwd_cart_info partition(dt='2020-06-15') select id, user_id, sku_id, source_type, source_id, cart_price, is_ordered, create_time, operate_time, order_time, sku_num from ods_cart_info where dt='2020-06-15';
4.2.5 收藏事实表（周期型快照事实表，每日快照）
1）建表语句
DROP TABLE IF EXISTS dwd_favor_info; CREATE EXTERNAL TABLE dwd_favor_info( `id` STRING COMMENT '编号', `user_id` STRING COMMENT '用户id', `sku_id` STRING COMMENT 'skuid', `spu_id` STRING COMMENT 'spuid', `is_cancel` STRING COMMENT '是否取消', `create_time` STRING COMMENT '收藏时间', `cancel_time` STRING COMMENT '取消时间' ) COMMENT '收藏事实表' PARTITIONED BY (`dt` STRING) STORED AS PARQUET LOCATION '/warehouse/gmall/dwd/dwd_favor_info/' TBLPROPERTIES ("parquet.compression"="lzo");
2）分区规划
3）数据装载
（1）首日装载
insert overwrite table dwd_favor_info partition(dt='2020-06-14') select id, user_id, sku_id, spu_id, is_cancel, create_time, cancel_time from ods_favor_info where dt='2020-06-14';
（2）每日装载
insert overwrite table dwd_favor_info partition(dt='2020-06-15') select id, user_id, sku_id, spu_id, is_cancel, create_time, cancel_time from ods_favor_info where dt='2020-06-15';
4.2.6 优惠券领用事实表（累积型快照事实表）
1）建表语句
DROP TABLE IF EXISTS dwd_coupon_use; CREATE EXTERNAL TABLE dwd_coupon_use( `id` STRING COMMENT '编号', `coupon_id` STRING COMMENT '优惠券ID', `user_id` STRING COMMENT 'userid', `order_id` STRING COMMENT '订单id', `coupon_status` STRING COMMENT '优惠券状态', `get_time` STRING COMMENT '领取时间', `using_time` STRING COMMENT '使用时间(下单)', `used_time` STRING COMMENT '使用时间(支付)', `expire_time` STRING COMMENT '过期时间' ) COMMENT '优惠券领用事实表' PARTITIONED BY (`dt` STRING) STORED AS PARQUET LOCATION '/warehouse/gmall/dwd/dwd_coupon_use/' TBLPROPERTIES ("parquet.compression"="lzo");
2）分区规划
3）数据装载
（1）首日装载
insert overwrite table 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 ods_coupon_use where dt='2020-06-14';
（2）每日装载
a.装载逻辑
b.转载语句
insert overwrite table 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 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 ods_coupon_use where dt='2020-06-15' )new on old.id=new.id;
4.2.7 支付事实表（累积型快照事实表）
1）建表语句
DROP TABLE IF EXISTS dwd_payment_info; CREATE EXTERNAL TABLE dwd_payment_info ( `id` STRING COMMENT '编号', `order_id` STRING COMMENT '订单编号', `user_id` STRING COMMENT '用户编号', `province_id` STRING COMMENT '地区ID', `trade_no` STRING COMMENT '交易编号', `out_trade_no` STRING COMMENT '对外交易编号', `payment_type` STRING COMMENT '支付类型', `payment_amount` DECIMAL(16,2) COMMENT '支付金额', `payment_status` STRING COMMENT '支付状态', `create_time` STRING COMMENT '创建时间',--调用第三方支付接口的时间 `callback_time` STRING COMMENT '完成时间'--支付完成时间，即支付成功回调时间 ) COMMENT '支付事实表表' PARTITIONED BY (`dt` STRING) STORED AS PARQUET LOCATION '/warehouse/gmall/dwd/dwd_payment_info/' TBLPROPERTIES ("parquet.compression"="lzo");
2）分区规划
3）数据装载
（1）首日装载
insert overwrite table 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 ods_payment_info where dt='2020-06-14' )pi left join ( select id,province_id from ods_order_info where dt='2020-06-14' )oi on pi.order_id=oi.id;
（2）每日装载
insert overwrite table 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 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 ods_payment_info where dt='2020-06-15' )pi left join ( select id,province_id from ods_order_info where dt='2020-06-15' )oi on pi.order_id=oi.id )new on old.id=new.id;
4.2.8 退款事实表（累积型快照事实表）
1）建表语句
DROP TABLE IF EXISTS dwd_refund_payment; CREATE EXTERNAL TABLE dwd_refund_payment ( `id` STRING COMMENT '编号', `user_id` STRING COMMENT '用户ID', `order_id` STRING COMMENT '订单编号', `sku_id` STRING COMMENT 'SKU编号', `province_id` STRING COMMENT '地区ID', `trade_no` STRING COMMENT '交易编号', `out_trade_no` STRING COMMENT '对外交易编号', `payment_type` STRING COMMENT '支付类型', `refund_amount` DECIMAL(16,2) COMMENT '退款金额', `refund_status` STRING COMMENT '退款状态', `create_time` STRING COMMENT '创建时间',--调用第三方支付接口的时间 `callback_time` STRING COMMENT '回调时间'--支付接口回调时间，即支付成功时间 ) COMMENT '退款事实表' PARTITIONED BY (`dt` STRING) STORED AS PARQUET LOCATION '/warehouse/gmall/dwd/dwd_refund_payment/' TBLPROPERTIES ("parquet.compression"="lzo");
2）分区规划
3）数据装载
（1）首日装载
insert overwrite table 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 ods_refund_payment where dt='2020-06-14' )rp left join ( select id, user_id, province_id from ods_order_info where dt='2020-06-14' )oi on rp.order_id=oi.id;
（2）每日装载
insert overwrite table 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 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 ods_refund_payment where dt='2020-06-15' )rp left join ( select id, user_id, province_id from ods_order_info where dt='2020-06-15' )oi on rp.order_id=oi.id )new on old.id=new.id;
3）查询加载结果
4.2.9 订单事实表（累积型快照事实表）
1）建表语句
DROP TABLE IF EXISTS dwd_order_info; CREATE EXTERNAL TABLE dwd_order_info( `id` STRING COMMENT '编号', `order_status` STRING COMMENT '订单状态', `user_id` STRING COMMENT '用户ID', `province_id` STRING COMMENT '地区ID', `payment_way` STRING COMMENT '支付方式', `delivery_address` STRING COMMENT '邮寄地址', `out_trade_no` STRING COMMENT '对外交易编号', `tracking_no` STRING COMMENT '物流单号', `create_time` STRING COMMENT '创建时间(未支付状态)', `payment_time` STRING COMMENT '支付时间(已支付状态)', `cancel_time` STRING COMMENT '取消时间(已取消状态)', `finish_time` STRING COMMENT '完成时间(已完成状态)', `refund_time` STRING COMMENT '退款时间(退款中状态)', `refund_finish_time` STRING COMMENT '退款完成时间(退款完成状态)', `expire_time` STRING COMMENT '过期时间', `feight_fee` DECIMAL(16,2) COMMENT '运费', `feight_fee_reduce` DECIMAL(16,2) COMMENT '运费减免', `activity_reduce_amount` DECIMAL(16,2) COMMENT '活动减免', `coupon_reduce_amount` DECIMAL(16,2) COMMENT '优惠券减免', `original_amount` DECIMAL(16,2) COMMENT '订单原始价格', `final_amount` DECIMAL(16,2) COMMENT '订单最终价格' ) COMMENT '订单事实表' PARTITIONED BY (`dt` STRING) STORED AS PARQUET LOCATION '/warehouse/gmall/dwd/dwd_order_info/' TBLPROPERTIES ("parquet.compression"="lzo");
2）分区规划
3）数据装载
（1）首日装载
insert overwrite table 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)<='2020-06-14' 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 ods_order_info where dt='2020-06-14' )oi left join ( select order_id, str_to_map(concat_ws(',',collect_set(concat(order_status,'=',operate_time))),',','=') ts from ods_order_status_log where dt='2020-06-14' group by order_id )times on oi.id=times.order_id;
（2）每日装载
insert overwrite table 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)='2020-06-15' and new.refund_time is null then '2020-06-15' 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 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 ods_order_info where dt='2020-06-15' )oi left join ( select order_id, str_to_map(concat_ws(',',collect_set(concat(order_status,'=',operate_time))),',','=') ts from ods_order_status_log where dt='2020-06-15' group by order_id )times on oi.id=times.order_id )new on old.id=new.id;
4.2.10 DWD层业务数据首日装载脚本
1）编写脚本
（1）在/home/atguigu/bin目录下创建脚本ods_to_dwd_db_init.sh
[atguigu@hadoop102 bin]$ vim ods_to_dwd_db_init.sh
在脚本中填写如下内容
#!/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
（2）增加执行权限
[atguigu@hadoop102 bin]$ chmod +x ods_to_dwd_db_init.sh
2）脚本使用
（1）执行脚本
[atguigu@hadoop102 bin]$ ods_to_dwd_db_init.sh all 2020-06-14
（2）查看数据是否导入成功
4.2.11 DWD层业务数据每日装载脚本
1）编写脚本
（1）在/home/atguigu/bin目录下创建脚本ods_to_dwd_db.sh
[atguigu@hadoop102 bin]$ vim ods_to_dwd_db.sh
在脚本中填写如下内容
#!/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
（2）增加脚本执行权限
[atguigu@hadoop102 bin]$ chmod 777 ods_to_dwd_db.sh
2）脚本使用
（1）执行脚本
[atguigu@hadoop102 bin]$ ods_to_dwd_db.sh all 2020-06-14
（2）查看数据是否导入成功