概述

ODS完成后，接下来就是进行DWD建模。对ODS层数据进行清洗和维度退化。

ODDA建模

DWD建模

1. 建表

-- 进入数据库
use mall;

-- 创建订单表
drop table if exists dwd_order_info;
create external table dwd_order_info ( 
    `id` string COMMENT '',
    `total_amount` decimal(10,2) COMMENT '', 
    `order_status` string COMMENT ' 1 2  3  4  5', 
    `user_id` string COMMENT 'id' ,
    `payment_way` string COMMENT '',  
    `out_trade_no` string COMMENT '',  
    `create_time` string COMMENT '',  
    `operate_time` string COMMENT '' 
) COMMENT ''
PARTITIONED BY ( `dt` string)
stored as  parquet
location '/warehouse/mall/dwd/dwd_order_info/'
tblproperties ("parquet.compression"="snappy")
;

-- 创建订单详情表
drop table if exists dwd_order_detail;
create external table dwd_order_detail( 
    `id` string COMMENT '',
    `order_id` decimal(10,2) COMMENT '', 
    `user_id` string COMMENT 'id' ,
    `sku_id` string COMMENT 'id',  
    `sku_name` string COMMENT '',  
    `order_price` string COMMENT '',  
    `sku_num` string COMMENT '', 
    `create_time` string COMMENT ''
) COMMENT ''
PARTITIONED BY ( `dt` string)
stored as  parquet
location '/warehouse/mall/dwd/dwd_order_detail/'
tblproperties ("parquet.compression"="snappy")
;

-- 创建用户表
drop table if exists dwd_user_info;
create external table dwd_user_info( 
    `id` string COMMENT 'id',
    `name`  string COMMENT '', 
    `birthday` string COMMENT '' ,
    `gender` string COMMENT '',  
    `email` string COMMENT '',  
    `user_level` string COMMENT '',  
    `create_time` string COMMENT ''
) COMMENT ''
PARTITIONED BY ( `dt` string)
stored as  parquet
location '/warehouse/mall/dwd/dwd_user_info/'
tblproperties ("parquet.compression"="snappy")
;

-- 创建支付流水表
drop table if exists `dwd_payment_info`;
create external  table  `dwd_payment_info`(
    `id`   bigint COMMENT '',
    `out_trade_no`   string COMMENT '',
    `order_id`        string COMMENT '',
    `user_id`         string COMMENT '',
    `alipay_trade_no` string COMMENT '',
    `total_amount`    decimal(16,2) COMMENT '',
    `subject`         string COMMENT '',
    `payment_type` string COMMENT '',
    `payment_time`   string COMMENT ''
   )  COMMENT ''
PARTITIONED BY ( `dt` string)
stored as  parquet
location '/warehouse/mall/dwd/dwd_payment_info/'
tblproperties ("parquet.compression"="snappy")
;

-- 创建商品表（增加分类）
drop table if exists dwd_sku_info;
create external table dwd_sku_info( 
    `id` string COMMENT 'skuId',
    `spu_id` string COMMENT 'spuid', 
    `price` decimal(10,2) COMMENT '' ,
    `sku_name` string COMMENT '',  
    `sku_desc` string COMMENT '',  
    `weight` string COMMENT '',  
    `tm_id` string COMMENT 'id',  
    `category3_id` string COMMENT '1id',  
    `category2_id` string COMMENT '2id',  
    `category1_id` string COMMENT '3id',  
    `category3_name` string COMMENT '3',  
    `category2_name` string COMMENT '2',  
    `category1_name` string COMMENT '1',  
    `create_time` string COMMENT ''
) COMMENT ''
PARTITIONED BY ( `dt` string)
stored as  parquet
location '/warehouse/mall/dwd/dwd_sku_info/'
tblproperties ("parquet.compression"="snappy")
;

2.检查创建的表

[root@node03 ~]# hive
hive> show databases;
hive> use mall;
hive> show tables;
OK
dwd_order_detail
dwd_order_info
dwd_payment_info
dwd_sku_info
dwd_user_info
ods_base_category1
ods_base_category2
ods_base_category3
ods_order_detail
ods_order_info
ods_payment_info
ods_sku_info
ods_user_info
Time taken: 0.097 seconds, Fetched: 13 row(s)

3.编写数据导入和清洗脚本

#!/bin/bash

# 定义变量方便修改
APP=mall
hive=hive

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n $1 ] ;then
    log_date=$1
else 
    log_date=`date  -d "-1 day"  +%F`  
fi 

sql="

set hive.exec.dynamic.partition.mode=nonstrict;

insert  overwrite table   "$APP".dwd_order_info partition(dt)
select  * from "$APP".ods_order_info 
where dt='$log_date'  and id is not null;
 
insert  overwrite table   "$APP".dwd_order_detail partition(dt)
select  * from "$APP".ods_order_detail 
where dt='$log_date'   and id is not null;

insert  overwrite table   "$APP".dwd_user_info partition(dt)
select  * from "$APP".ods_user_info
where dt='$log_date'   and id is not null;
 
insert  overwrite table   "$APP".dwd_payment_info partition(dt)
select  * from "$APP".ods_payment_info
where dt='$log_date'  and id is not null;

insert  overwrite table   "$APP".dwd_sku_info partition(dt)
select  
    sku.id,
    sku.spu_id, 
    sku.price,
    sku.sku_name,  
    sku.sku_desc,  
    sku.weight,  
    sku.tm_id,  
    sku.category3_id,  
    c2.id category2_id ,  
    c1.id category1_id,  
    c3.name category3_name,  
    c2.name category2_name,  
    c1.name category1_name,  
    sku.create_time,
    sku.dt
from
    "$APP".ods_sku_info sku 
join "$APP".ods_base_category3 c3 on sku.category3_id=c3.id 
    join "$APP".ods_base_category2 c2 on c3.category2_id=c2.id 
    join "$APP".ods_base_category1 c1 on c2.category1_id=c1.id 
where sku.dt='$log_date'  and c2.dt='$log_date'  
and  c3.dt='$log_date' and  c1.dt='$log_date' 
and sku.id is not null;

"
$hive -e "$sql"

4.执行数据导入和清洗脚本

./dwd_db.sh 2021-03-24

5.查看结果

hive> select * from dwd_sku_info;
OK
1005    650     1242    oKJcqyuCBcJaiPAgebWC    VXamgWnzKZTuuLyjzTXeJSuvUmARIQ  0.76    87      641     62      11      坡跟鞋  时尚女鞋        鞋靴    2021-03-24 23:59:17.0        2021-03-24