3.3ODS层
完全仿照业务数据库中的表字段,一模一样的创建ODS层对应表。
3.3.1创建订单表
3.3.1创建订单表
hive (gmall)>
drop table if exists ods_order_info;
create external table ods_order_info (
id
string COMMENT ‘订单编号’,
total_amount
decimal(10,2) COMMENT ‘订单金额’,
order_status
string COMMENT ‘订单状态’,
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)
row format delimited fields terminated by ‘\t’
location ‘/warehouse/gmall/ods/ods_order_info/’
;
3.3.2创建订单详情表
hive (gmall)>
drop table if exists ods_order_detail;
create external table ods_order_detail(
id
string COMMENT ‘订单编号’,
order_id
string 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)
row format delimited fields terminated by ‘\t’
location ‘/warehouse/gmall/ods/ods_order_detail/’
;
3.3.3创建商品表
hive (gmall)>
drop table if exists ods_sku_info;
create external table ods_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 ‘品类id’,
create_time
string COMMENT ‘创建时间’
) COMMENT ‘商品表’
PARTITIONED BY (dt
string)
row format delimited fields terminated by ‘\t’
location ‘/warehouse/gmall/ods/ods_sku_info/’
;
3.3.4创建用户表
hive (gmall)>
drop table if exists ods_user_info;
create external table ods_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)
row format delimited fields terminated by ‘\t’
location ‘/warehouse/gmall/ods/ods_user_info/’
;
3.3.5创建商品一级分类表
hive (gmall)>
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’
location ‘/warehouse/gmall/ods/ods_base_category1/’
;
3.3.6创建商品二级分类表
hive (gmall)>
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’
location ‘/warehouse/gmall/ods/ods_base_category2/’
;
3.3.7创建商品三级分类表
hive (gmall)>
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’
location ‘/warehouse/gmall/ods/ods_base_category3/’
;
3.3.8创建支付流水表
hive (gmall)>
drop table if exists ods_payment_info;
create external table ods_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)
row format delimited fields terminated by ‘\t’
location ‘/warehouse/gmall/ods/ods_payment_info/’
;