文章目录
- 1.2.2 活动规则表（全量表）
- 1.2.3 一级品类表（全量表）
- 1.2.4 二级品类表（全量表）
- 1.2.5 三级品类表
- 1.2.6 编码字典表（全量表）
- 1.2.7 省份表（全量表）
- 1.2.8 地区表（全量表）
- 1.2.9 品牌表（全量表）
- 1.2.10 购物车表（全量表）
- 1.2.11 优惠券信息表（全量表）
- 1.2.12 商品平台属性表
- 1.2.13 商品表（全量表）
- 1.2.14 商品销售属性值表
- 1.2.15 SPU表（全量表）
- 1.2.16 购物车表（增量表）
- 1.2.17 评论表（增量表）
- 1.2.18 优惠券领用表（增量表）
- 1.2.19 收藏表（增量表）
- 1.2.20 订单明细表（增量表）
- 1.2.21 订单明细活动关联表（增量表）
- 1.2.22 订单明细优惠券关联表（增量表）
- 1.2.23 订单表（增量表）
- 1.2.24 退单表（增量表）
- 1.2.25 订单状态流水表（增量表）
- 1.2.26 支付表（增量表）
- 1.2.27 退款表（增量表）
- 1.2.28 用户表（增量表）
- 1.2.29 数据装载脚本
一、数仓开发之ODS层
ODS层的设计要点如下：
（1）ODS层的表结构设计依托于从业务系统同步过来的数据结构。
（2）ODS层要保存全部历史数据，故其压缩格式应选择压缩比较高的，此处选择gzip。
（3）ODS层表名的命名规范为：ods_表名_单分区增量全量标识（inc/full）
1.1 日志表
1、建表数据
drop table if exists ods_log_inc; create External table ods_log_inc ( `common` STRUCT<ar:String,ba:String,ch:String,is_new:String,md:String,mid:String,os:String,uid:String,vc:String> COMMENT '公共信息', `page` STRUCT<during_time:String,item:String,item_type:String,last_page_id:String,page_id:String,source_type:String> COMMENT '页面信息', `actions` ARRAY<STRUCT<action_id:String,item:STRING,item_type:STRING,ts:BIGINT>> COMMENT '动作信息', `displays` ARRAY<STRUCT<display_type:STRING,item:STRING,item_type:STRING,`order`:STRING,pos_id:STRING>> COMMENT '曝光信息', `start` STRUCT<entry:STRING,loading_time:BIGINT,open_ad_id:BIGINT,open_ad_ms:BIGINT,open_ad_skip_ms:BIGINT> COMMENT '启动信息', `err` STRUCT<error_code:BIGINT,msg:STRING> COMMNET '错误信息', `ts` BigINT COMMENT '时间戳' ) COMMENT '活动信息表' PARTITIONED BY (`dt` STRING) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe' LOCATION '/warehouse/gmall/ods/ods_log_inc/';
2、数据装载
load data inpath '/orgin_data/gmall/log/topic_log/2020-06-14' into table ods_log_inc partition by(dt='2020-06-14');
3、每日数据装载脚本
（1）在hadoop102的/home/zhm/bin目录下创建hdfs_to_ods_log.sh
vim hdfs_to_ods_log.sh
（2）添加如下内容
#!/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_inc partition(dt='$do_date'); " hive -e "$sql"
（3）增加脚本权限
chmod +x hdfs_to_ods_log.sh
（4）脚本用法
hdfs_to_ods_log.sh 2020-06-14
1.2 业务表
1.2.1 活动信息表（全量表）
Drop table if exists ods_activity_info_full; create external table ods_activity_info_full ( `id` String Comment '活动id', `activity_name` String Comment '活动名称', `activity_type` String Comment '活动类型', `activity_desc` 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' null defined as '' location '/warehouse/gmall/ods/ods_activity_info_full';
1.2.2 活动规则表（全量表）
DROP TABLE IF EXISTS ods_activity_rule_full; CREATE EXTERNAL TABLE ods_activity_rule_full ( `id` STRING COMMENT '编号', `activity_id` STRING COMMENT '类型', `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' NULL DEFINED AS '' LOCATION '/warehouse/gmall/ods/ods_activity_rule_full/';
1.2.3 一级品类表（全量表）
DROP TABLE IF EXISTS ods_base_category1_full; CREATE EXTERNAL TABLE ods_base_category1_full ( `id` STRING COMMENT '编号', `name` STRING COMMENT '分类名称' ) COMMENT '一级品类表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' NULL DEFINED AS '' LOCATION '/warehouse/gmall/ods/ods_base_category1_full/';
1.2.4 二级品类表（全量表）
DROP TABLE IF EXISTS ods_base_category2_full; CREATE EXTERNAL TABLE ods_base_category2_full ( `id` STRING COMMENT '编号', `name` STRING COMMENT '二级分类名称', `category1_id` STRING COMMENT '一级分类编号' ) COMMENT '二级品类表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' NULL DEFINED AS '' LOCATION '/warehouse/gmall/ods/ods_base_category2_full/';
1.2.5 三级品类表
DROP TABLE IF EXISTS ods_base_category3_full; CREATE EXTERNAL TABLE ods_base_category3_full ( `id` STRING COMMENT '编号', `name` STRING COMMENT '三级分类名称', `category2_id` STRING COMMENT '二级分类编号' ) COMMENT '三级品类表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' NULL DEFINED AS '' LOCATION '/warehouse/gmall/ods/ods_base_category3_full/';
1.2.6 编码字典表（全量表）
DROP TABLE IF EXISTS ods_base_dic_full; CREATE EXTERNAL TABLE ods_base_dic_full ( `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' NULL DEFINED AS '' LOCATION '/warehouse/gmall/ods/ods_base_dic_full/';
1.2.7 省份表（全量表）
DROP TABLE IF EXISTS ods_base_province_full; CREATE EXTERNAL TABLE ods_base_province_full ( `id` STRING COMMENT '编号', `name` STRING COMMENT '省份名称', `region_id` STRING COMMENT '地区ID', `area_code` STRING COMMENT '地区编码', `iso_code` STRING COMMENT '旧版ISO-3166-2编码，供可视化使用', `iso_3166_2` STRING COMMENT '新版IOS-3166-2编码，供可视化使用' ) COMMENT '省份表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' NULL DEFINED AS '' LOCATION '/warehouse/gmall/ods/ods_base_province_full/';
1.2.8 地区表（全量表）
DROP TABLE IF EXISTS ods_base_region_full; CREATE EXTERNAL TABLE ods_base_region_full ( `id` STRING COMMENT '编号', `region_name` STRING COMMENT '地区名称' ) COMMENT '地区表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' NULL DEFINED AS '' LOCATION '/warehouse/gmall/ods/ods_base_region_full/';
1.2.9 品牌表（全量表）
DROP TABLE IF EXISTS ods_base_trademark_full; CREATE EXTERNAL TABLE ods_base_trademark_full ( `id` STRING COMMENT '编号', `tm_name` STRING COMMENT '品牌名称', `logo_url` STRING COMMENT '品牌logo的图片路径' ) COMMENT '品牌表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' NULL DEFINED AS '' LOCATION '/warehouse/gmall/ods/ods_base_trademark_full/';
1.2.10 购物车表（全量表）
DROP TABLE IF EXISTS ods_cart_info_full; CREATE EXTERNAL TABLE ods_cart_info_full ( `id` STRING COMMENT '编号', `user_id` STRING COMMENT '用户id', `sku_id` STRING COMMENT 'sku_id', `cart_price` DECIMAL(16, 2) COMMENT '放入购物车时价格', `sku_num` BIGINT COMMENT '数量', `img_url` BIGINT COMMENT '商品图片地址', `sku_name` STRING COMMENT 'sku名称 (冗余)', `is_checked` STRING COMMENT '是否被选中', `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' NULL DEFINED AS '' LOCATION '/warehouse/gmall/ods/ods_cart_info_full/';
1.2.11 优惠券信息表（全量表）
DROP TABLE IF EXISTS ods_coupon_info_full; CREATE EXTERNAL TABLE ods_coupon_info_full ( `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' NULL DEFINED AS '' LOCATION '/warehouse/gmall/ods/ods_coupon_info_full/';
1.2.12 商品平台属性表
DROP TABLE IF EXISTS ods_sku_attr_value_full; CREATE EXTERNAL TABLE ods_sku_attr_value_full ( `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' NULL DEFINED AS '' LOCATION '/warehouse/gmall/ods/ods_sku_attr_value_full/';
1.2.13 商品表（全量表）
DROP TABLE IF EXISTS ods_sku_info_full; CREATE EXTERNAL TABLE ods_sku_info_full ( `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', `sku_default_igm` STRING COMMENT '商品图片地址', `is_sale` STRING COMMENT '是否在售', `create_time` STRING COMMENT '创建时间' ) COMMENT 'SKU商品表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' NULL DEFINED AS '' LOCATION '/warehouse/gmall/ods/ods_sku_info_full/';
1.2.14 商品销售属性值表
DROP TABLE IF EXISTS ods_sku_sale_attr_value_full; CREATE EXTERNAL TABLE ods_sku_sale_attr_value_full ( `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' NULL DEFINED AS '' LOCATION '/warehouse/gmall/ods/ods_sku_sale_attr_value_full/';
1.2.15 SPU表（全量表）
DROP TABLE IF EXISTS ods_spu_info_full; CREATE EXTERNAL TABLE ods_spu_info_full ( `id` STRING COMMENT 'spu_id', `spu_name` STRING COMMENT 'spu名称', `description` STRING COMMENT '描述信息', `category3_id` STRING COMMENT '品类id', `tm_id` STRING COMMENT '品牌id' ) COMMENT 'SPU商品表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' NULL DEFINED AS '' LOCATION '/warehouse/gmall/ods/ods_spu_info_full/';
1.2.16 购物车表（增量表）
DROP TABLE IF EXISTS ods_cart_info_inc; CREATE EXTERNAL TABLE ods_cart_info_inc ( `type` STRING COMMENT '变动类型', `ts` BIGINT COMMENT '变动时间', `data` STRUCT<id :STRING,user_id :STRING,sku_id :STRING,cart_price :DECIMAL(16, 2),sku_num :BIGINT,img_url :STRING,sku_name :STRING,is_checked :STRING,create_time :STRING,operate_time :STRING,is_ordered :STRING,order_time :STRING,source_type :STRING,source_id :STRING> COMMENT '数据', `old` MAP<STRING,STRING> COMMENT '旧值' ) COMMENT '购物车增量表' PARTITIONED BY (`dt` STRING) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe' LOCATION '/warehouse/gmall/ods/ods_cart_info_inc/';
1.2.17 评论表（增量表）
DROP TABLE IF EXISTS ods_comment_info_inc; CREATE EXTERNAL TABLE ods_comment_info_inc ( `type` STRING COMMENT '变动类型', `ts` BIGINT COMMENT '变动时间', `data` STRUCT<id :STRING,user_id :STRING,nick_name :STRING,head_img :STRING,sku_id :STRING,spu_id :STRING,order_id :STRING,appraise :STRING,comment_txt :STRING,create_time :STRING,operate_time :STRING> COMMENT '数据', `old` MAP<STRING,STRING> COMMENT '旧值' ) COMMENT '评价表' PARTITIONED BY (`dt` STRING) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe' LOCATION '/warehouse/gmall/ods/ods_comment_info_inc/';
1.2.18 优惠券领用表（增量表）
DROP TABLE IF EXISTS ods_coupon_use_inc; CREATE EXTERNAL TABLE ods_coupon_use_inc ( `type` STRING COMMENT '变动类型', `ts` BIGINT COMMENT '变动时间', `data` STRUCT<id :STRING,coupon_id :STRING,user_id :STRING,order_id :STRING,coupon_status :STRING,get_time :STRING,using_time :STRING,used_time :STRING,expire_time :STRING> COMMENT '数据', `old` MAP<STRING,STRING> COMMENT '旧值' ) COMMENT '优惠券领用表' PARTITIONED BY (`dt` STRING) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe' LOCATION '/warehouse/gmall/ods/ods_coupon_use_inc/';
1.2.19 收藏表（增量表）
DROP TABLE IF EXISTS ods_favor_info_inc; CREATE EXTERNAL TABLE ods_favor_info_inc ( `type` STRING COMMENT '变动类型', `ts` BIGINT COMMENT '变动时间', `data` STRUCT<id :STRING,user_id :STRING,sku_id :STRING,spu_id :STRING,is_cancel :STRING,create_time :STRING,cancel_time :STRING> COMMENT '数据', `old` MAP<STRING,STRING> COMMENT '旧值' ) COMMENT '收藏表' PARTITIONED BY (`dt` STRING) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe' LOCATION '/warehouse/gmall/ods/ods_favor_info_inc/';
1.2.20 订单明细表（增量表）
DROP TABLE IF EXISTS ods_order_detail_inc; CREATE EXTERNAL TABLE ods_order_detail_inc ( `type` STRING COMMENT '变动类型', `ts` BIGINT COMMENT '变动时间', `data` STRUCT<id :STRING,order_id :STRING,sku_id :STRING,sku_name :STRING,img_url :STRING,order_price :DECIMAL(16, 2),sku_num :BIGINT,create_time :STRING,source_type :STRING,source_id :STRING,split_total_amount :DECIMAL(16, 2),split_activity_amount :DECIMAL(16, 2),split_coupon_amount :DECIMAL(16, 2)> COMMENT '数据', `old` MAP<STRING,STRING> COMMENT '旧值' ) COMMENT '订单明细表' PARTITIONED BY (`dt` STRING) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe' LOCATION '/warehouse/gmall/ods/ods_order_detail_inc/';
1.2.21 订单明细活动关联表（增量表）
DROP TABLE IF EXISTS ods_order_detail_activity_inc; CREATE EXTERNAL TABLE ods_order_detail_activity_inc ( `type` STRING COMMENT '变动类型', `ts` BIGINT COMMENT '变动时间', `data` STRUCT<id :STRING,order_id :STRING,order_detail_id :STRING,activity_id :STRING,activity_rule_id :STRING,sku_id :STRING,create_time :STRING> COMMENT '数据', `old` MAP<STRING,STRING> COMMENT '旧值' ) COMMENT '订单明细活动关联表' PARTITIONED BY (`dt` STRING) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe' LOCATION '/warehouse/gmall/ods/ods_order_detail_activity_inc/';
1.2.22 订单明细优惠券关联表（增量表）
DROP TABLE IF EXISTS ods_order_detail_coupon_inc; CREATE EXTERNAL TABLE ods_order_detail_coupon_inc ( `type` STRING COMMENT '变动类型', `ts` BIGINT COMMENT '变动时间', `data` STRUCT<id :STRING,order_id :STRING,order_detail_id :STRING,coupon_id :STRING,coupon_use_id :STRING,sku_id :STRING,create_time :STRING> COMMENT '数据', `old` MAP<STRING,STRING> COMMENT '旧值' ) COMMENT '订单明细优惠券关联表' PARTITIONED BY (`dt` STRING) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe' LOCATION '/warehouse/gmall/ods/ods_order_detail_coupon_inc/';
1.2.23 订单表（增量表）
DROP TABLE IF EXISTS ods_order_info_inc; CREATE EXTERNAL TABLE ods_order_info_inc ( `type` STRING COMMENT '变动类型', `ts` BIGINT COMMENT '变动时间', `data` STRUCT<id :STRING,consignee :STRING,consignee_tel :STRING,total_amount :DECIMAL(16, 2),order_status :STRING,user_id :STRING,payment_way :STRING,delivery_address :STRING,order_comment :STRING,out_trade_no :STRING,trade_body :STRING,create_time :STRING,operate_time :STRING,expire_time :STRING,process_status :STRING,tracking_no :STRING,parent_order_id :STRING,img_url :STRING,province_id :STRING,activity_reduce_amount :DECIMAL(16, 2),coupon_reduce_amount :DECIMAL(16, 2),original_total_amount :DECIMAL(16, 2),freight_fee :DECIMAL(16, 2),freight_fee_reduce :DECIMAL(16, 2),refundable_time :DECIMAL(16, 2)> COMMENT '数据', `old` MAP<STRING,STRING> COMMENT '旧值' ) COMMENT '订单表' PARTITIONED BY (`dt` STRING) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe' LOCATION '/warehouse/gmall/ods/ods_order_info_inc/';
1.2.24 退单表（增量表）
DROP TABLE IF EXISTS ods_order_refund_info_inc; CREATE EXTERNAL TABLE ods_order_refund_info_inc ( `type` STRING COMMENT '变动类型', `ts` BIGINT COMMENT '变动时间', `data` STRUCT<id :STRING,user_id :STRING,order_id :STRING,sku_id :STRING,refund_type :STRING,refund_num :BIGINT,refund_amount :DECIMAL(16, 2),refund_reason_type :STRING,refund_reason_txt :STRING,refund_status :STRING,create_time :STRING> COMMENT '数据', `old` MAP<STRING,STRING> COMMENT '旧值' ) COMMENT '退单表' PARTITIONED BY (`dt` STRING) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe' LOCATION '/warehouse/gmall/ods/ods_order_refund_info_inc/';
1.2.25 订单状态流水表（增量表）
DROP TABLE IF EXISTS ods_order_status_log_inc; CREATE EXTERNAL TABLE ods_order_status_log_inc ( `type` STRING COMMENT '变动类型', `ts` BIGINT COMMENT '变动时间', `data` STRUCT<id :STRING,order_id :STRING,order_status :STRING,operate_time :STRING> COMMENT '数据', `old` MAP<STRING,STRING> COMMENT '旧值' ) COMMENT '退单表' PARTITIONED BY (`dt` STRING) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe' LOCATION '/warehouse/gmall/ods/ods_order_status_log_inc/';
1.2.26 支付表（增量表）
DROP TABLE IF EXISTS ods_payment_info_inc; CREATE EXTERNAL TABLE ods_payment_info_inc ( `type` STRING COMMENT '变动类型', `ts` BIGINT COMMENT '变动时间', `data` STRUCT<id :STRING,out_trade_no :STRING,order_id :STRING,user_id :STRING,payment_type :STRING,trade_no :STRING,total_amount :DECIMAL(16, 2),subject :STRING,payment_status :STRING,create_time :STRING,callback_time :STRING,callback_content :STRING> COMMENT '数据', `old` MAP<STRING,STRING> COMMENT '旧值' ) COMMENT '支付表' PARTITIONED BY (`dt` STRING) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe' LOCATION '/warehouse/gmall/ods/ods_payment_info_inc/';
1.2.27 退款表（增量表）
DROP TABLE IF EXISTS ods_refund_payment_inc; CREATE EXTERNAL TABLE ods_refund_payment_inc ( `type` STRING COMMENT '变动类型', `ts` BIGINT COMMENT '变动时间', `data` STRUCT<id :STRING,out_trade_no :STRING,order_id :STRING,sku_id :STRING,payment_type :STRING,trade_no :STRING,total_amount :DECIMAL(16, 2),subject :STRING,refund_status :STRING,create_time :STRING,callback_time :STRING,callback_content :STRING> COMMENT '数据', `old` MAP<STRING,STRING> COMMENT '旧值' ) COMMENT '退款表' PARTITIONED BY (`dt` STRING) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe' LOCATION '/warehouse/gmall/ods/ods_refund_payment_inc/';
1.2.28 用户表（增量表）
DROP TABLE IF EXISTS ods_user_info_inc; CREATE EXTERNAL TABLE ods_user_info_inc ( `type` STRING COMMENT '变动类型', `ts` BIGINT COMMENT '变动时间', `data` STRUCT<id :STRING,login_name :STRING,nick_name :STRING,passwd :STRING,name :STRING,phone_num :STRING,email :STRING,head_img :STRING,user_level :STRING,birthday :STRING,gender :STRING,create_time :STRING,operate_time :STRING,status :STRING> COMMENT '数据', `old` MAP<STRING,STRING> COMMENT '旧值' ) COMMENT '用户表' PARTITIONED BY (`dt` STRING) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe' LOCATION '/warehouse/gmall/ods/ods_user_info_inc/';
1.2.29 数据装载脚本
1、在hadoop102的/home/zhm/bin目录下创建hdfs_to_ods_db.sh
vim hdfs_to_ods_db.sh • 1
2、添加如下内容
#!/bin/bash APP=gmall if [ -n "$2" ] ;then do_date=$2 else do_date=`date -d '-1 day' +%F` fi load_data(){ sql="" for i in $*; do #判断路径是否存在 hadoop fs -test -e /origin_data/$APP/db/${i:4}/$do_date #路径存在方可装载数据 if [[ $? = 0 ]]; then sql=$sql"load data inpath '/origin_data/$APP/db/${i:4}/$do_date' OVERWRITE into table ${APP}.$i partition(dt='$do_date');" fi done hive -e "$sql" } case $1 in "ods_activity_info_full") load_data "ods_activity_info_full" ;; "ods_activity_rule_full") load_data "ods_activity_rule_full" ;; "ods_base_category1_full") load_data "ods_base_category1_full" ;; "ods_base_category2_full") load_data "ods_base_category2_full" ;; "ods_base_category3_full") load_data "ods_base_category3_full" ;; "ods_base_dic_full") load_data "ods_base_dic_full" ;; "ods_base_province_full") load_data "ods_base_province_full" ;; "ods_base_region_full") load_data "ods_base_region_full" ;; "ods_base_trademark_full") load_data "ods_base_trademark_full" ;; "ods_cart_info_full") load_data "ods_cart_info_full" ;; "ods_coupon_info_full") load_data "ods_coupon_info_full" ;; "ods_sku_attr_value_full") load_data "ods_sku_attr_value_full" ;; "ods_sku_info_full") load_data "ods_sku_info_full" ;; "ods_sku_sale_attr_value_full") load_data "ods_sku_sale_attr_value_full" ;; "ods_spu_info_full") load_data "ods_spu_info_full" ;; "ods_cart_info_inc") load_data "ods_cart_info_inc" ;; "ods_comment_info_inc") load_data "ods_comment_info_inc" ;; "ods_coupon_use_inc") load_data "ods_coupon_use_inc" ;; "ods_favor_info_inc") load_data "ods_favor_info_inc" ;; "ods_order_detail_inc") load_data "ods_order_detail_inc" ;; "ods_order_detail_activity_inc") load_data "ods_order_detail_activity_inc" ;; "ods_order_detail_coupon_inc") load_data "ods_order_detail_coupon_inc" ;; "ods_order_info_inc") load_data "ods_order_info_inc" ;; "ods_order_refund_info_inc") load_data "ods_order_refund_info_inc" ;; "ods_order_status_log_inc") load_data "ods_order_status_log_inc" ;; "ods_payment_info_inc") load_data "ods_payment_info_inc" ;; "ods_refund_payment_inc") load_data "ods_refund_payment_inc" ;; "ods_user_info_inc") load_data "ods_user_info_inc" ;; "all") load_data "ods_activity_info_full" "ods_activity_rule_full" "ods_base_category1_full" "ods_base_category2_full" "ods_base_category3_full" "ods_base_dic_full" "ods_base_province_full" "ods_base_region_full" "ods_base_trademark_full" "ods_cart_info_full" "ods_coupon_info_full" "ods_sku_attr_value_full" "ods_sku_info_full" "ods_sku_sale_attr_value_full" "ods_spu_info_full" "ods_cart_info_inc" "ods_comment_info_inc" "ods_coupon_use_inc" "ods_favor_info_inc" "ods_order_detail_inc" "ods_order_detail_activity_inc" "ods_order_detail_coupon_inc" "ods_order_info_inc" "ods_order_refund_info_inc" "ods_order_status_log_inc" "ods_payment_info_inc" "ods_refund_payment_inc" "ods_user_info_inc" ;; esac
3、增加脚本执行权限
4、脚本用法
hdfs_to_ods_db.sh all 2020-06-14
您的支持是我创作的无限动力
希望我能为您的未来尽绵薄之力
如有错误，谢谢指正若有收获，谢谢赞美
