【大数据课程】高途课程实践-Day02：利用Hive SQL编写离线数仓实现可视化展示

〇、概述

1、实现内容

使用Hive SQL编程，构造分层离线数仓

并可以通过Quick Bi进行展示

2、过程

（1）数据接⼊到ODS层

（2）进⾏ODS到DWD层数据开发

（3）进⾏ODS到DIM层数据开发

a.创建 【电商_商家维度表_⽇】表

b.创建【电商_商品维度表_⽇】表

c.创建【电商_⽤⼾维度表_⽇】表

（4）进⾏DWS层数据开发

（5）进⾏ADS应⽤层的数据开发

a.看板_总指标看板统计

b.看板_商品销售地域分析

c.看板_商品销量排行

3、逻辑模型

4、示例数据

一、数据导入ODS层

-- 订单表
create table if not exists ods_plato_orders_da(
    id                  bigint  comment '交易id',
    deal_time           string  comment '交易日期',
    stock_id            bigint  comment '商品id',
    shop_id             bigint  comment '商家id',
    user_id             bigint  comment '用户id',
    deal_status         string  comment '交易状态',
    stock_cnt           bigint  comment '商品数量',
    deal_amount         decimal(38,18)  comment '订单金额'
)
partitioned by(pt string comment '日期分区');

-- 商家表
create table if not exists ods_plato_shops_da(
    id             bigint  comment '商家id',
    name           string  comment '商家店铺名称',
    level          int     comment '商家等级',
    register_time  string  comment '商家注册时间'
)
partitioned by(pt string comment '日期分区');

-- 用户表
create table if not exists ods_plato_users_da(
    id                 bigint  comment '用户id',
    account            string  comment '用户账号',
    level              int     comment '用户等级',
    register_time      string  comment '用户注册时间',
    status             string  comment '用户状态',
    addr               string  comment '用户收货地址',
    city               string  comment '用户所在城市'
)
partitioned by(pt string comment '日期分区');

-- 商品表
create table if not exists ods_plato_stocks_da(
    id                bigint  comment '商品id',
    name              string  comment '商品名称',
    level_1st         string  comment '商品一级类目',
    level_2st         string  comment '商品二级类目',
    price             decimal(38,18)  comment '商品单价',
    status            string  comment '商品状态',
    activity_status   string  comment '商品活动状态',
    shop_id           bigint  comment '商家id'
)
partitioned by(pt string comment '日期分区');

二、进行ODS到DWD层的数据开发

1.先创建 dwd_deal_orders_detail_da 数据表； 2.编写insert 语句进⾏数据加载。

参考dwd/dwd_deal_orders_detail_da.sql ⽂件

代码

--odps sql 
--********************************************************************--
--author:侯老师
--create time:2021-11-10 20:27:27
--table name: 电商_交易事物事实表_日
--********************************************************************--

create table if not exists dwd_deal_orders_detail_da(
    deal_id         bigint  comment '交易id',
    deal_date       string  comment '交易日期',
    stock_id        bigint  comment '商品id',
    shop_id         bigint  comment '商家id',
    user_id         bigint  comment '用户id',
    deal_status     string  comment '交易状态',
    stock_cnt       bigint  comment '商品数量',
    deal_amount     decimal(38,18)  comment '订单金额'
)
comment '电商_交易事物事实表_日'
partitioned by(pt string comment '日期分区');


insert overwrite table dwd_deal_orders_detail_da partition(pt='${bizdate}')
select
     id deal_id   -- 交易id
    ,to_date(deal_time) as deal_date     -- 交易日期
    ,stock_id      -- 商品id
    ,shop_id   -- 商家id
    ,user_id   -- 用户id
    ,deal_status   -- 交易状态
    ,stock_cnt     -- 商品数量
    ,deal_amount   -- 订单金额
from ods_plato_orders_da
where pt='${bizdate}';

三、进⾏ODS到DIM层数据开发

1、创建 【电商_商家维度表_⽇】表

参考dim/dim_shops_main_info_da.sql脚本，进⾏数据开发。

--odps sql 
--********************************************************************--
--author:侯老师
--create time:2021-11-10 20:27:27
--table name: 电商_商家维度表_日
--********************************************************************--

create table if not exists dim_shops_main_info_da(
    shop_id             bigint  comment '商家id',
    shop_name           string  comment '商家店铺名称',
    shop_level          int     comment '商家等级',
    shop_register_time  string  comment '商家注册时间'
)
comment '电商_商家维度表_日'
partitioned by(pt string comment '日期分区');


insert overwrite table dim_shops_main_info_da partition(pt='${bizdate}')
select
     id     as shop_id      -- 商家id
    ,name   as shop_name    -- 商家店铺名称
    ,level  as shop_level   -- 商家等级
    ,register_time  as shop_register_time   -- 商家注册时间
from ods_plato_shops_da
where pt='${bizdate}';

2、创建【电商_商品维度表_⽇】表

参考dim/dim_stocks_main_info_da.sql脚本，进⾏数据开发。

--odps sql 
--********************************************************************--
--author:侯老师
--create time:2021-11-10 20:27:27
--table name: 电商_商品维度表_日
--********************************************************************--

create table if not exists dim_stocks_main_info_da(
    stock_id                bigint  comment '商品id',
    stock_name              string  comment '商品名称',
    stock_1st_level         string  comment '商品一级类目',
    stock_2st_level         string  comment '商品二级类目',
    stock_price             decimal(38,18)  comment '商品单价',
    stock_status            string  comment '商品状态',
    stock_activity_status   string  comment '商品活动状态',
    shop_id                 bigint  comment '商家id'
)
comment '电商_商品维度表_日'
partitioned by(pt string comment '日期分区');


insert overwrite table dim_stocks_main_info_da partition(pt='${bizdate}')
select
     id                 as stock_id    -- 商品id
    ,name               as stock_name  -- 商品名称
    ,level_1st          as stock_1st_level -- 商品一级类目
    ,level_2st          as stock_2st_level -- 商品二级类目
    ,price              as stock_price -- 商品单价
    ,status             as stock_status    -- 商品状态
    ,activity_status    as stock_activity_status   -- 商品活动状态
    ,shop_id            as shop_id -- 商家id
from ods_plato_stocks_da
where pt='${bizdate}';

3、创建【电商_⽤⼾维度表_⽇】表

参考dim/dim_users_main_info_da.sql脚本，进⾏数据开发。

--odps sql 
--********************************************************************--
--author:侯老师
--create time:2021-11-10 20:27:27
--table name: 电商_用户维度表_日
--********************************************************************--

create table if not exists dim_users_main_info_da(
    user_id             bigint  comment '用户id',
    user_account        string  comment '用户账号',
    user_level          int     comment '用户等级',
    user_register_time  string  comment '用户注册时间',
    user_status         string  comment '用户状态',
    user_addr           string  comment '用户收货地址',
    user_city           string  comment '用户所在城市'
)
comment '电商_用户维度表_日'
partitioned by(pt string comment '日期分区');


insert overwrite table dim_users_main_info_da partition(pt='${bizdate}')
select
     id             as user_id -- 用户id
    ,account        as user_account    -- 用户账号
    ,level          as user_level  -- 用户等级
    ,register_time  as user_register_time  -- 用户注册时间
    ,status         as user_status -- 用户状态
    ,addr           as user_addr   -- 用户收货地址
    ,city as user_city   -- 用户所在城市
from ods_plato_users_da
where pt='${bizdate}';

四、进⾏DWS层数据开发

参考dws/dws_deal_orders_summary_da.sql 脚本，进⾏数据汇总表开发。

--odps sql 
--********************************************************************--
--author:侯老师
--create time:2021-11-10 20:27:27
--table name: 商品交易轻度汇总事实表
--********************************************************************--

create table if not exists dws_deal_orders_summary_da(
    deal_id             bigint comment '订单ID',
    deal_date           string comment '交易日期',
    stock_id            bigint comment '商品ID',
    stock_name          string comment '商品名称',
    stock_1st_level     string comment '商品一级类目',
    stock_2st_level     string comment '商品二级类目',
    stock_price         decimal(38,18) comment '商品单价',
    stock_cnt           bigint comment '商品订单数',
    shop_id             bigint comment '商家ID',
    shop_name           string comment '商家名称',
    user_id             bigint comment '买家用户ID',
    user_city           string comment '买家用户所在城市',
    deal_amount         decimal(38,18) comment '订单金额'
)
comment '商品交易轻度汇总事实表'
partitioned by(pt string comment '日期分区');


insert overwrite table dws_deal_orders_summary_da partition(pt='${bizdate}')
select
     u1.deal_id             -- 订单ID
    ,u1.deal_date           -- 交易日期
    ,u4.stock_id            -- 商品ID
    ,u4.stock_name          -- 商品名称
    ,u4.stock_1st_level     -- 商品一级类目
    ,u4.stock_2st_level     -- 商品二级类目
    ,u4.stock_price         -- 商品单价
    ,u1.stock_cnt           -- 商品订单数
    ,u2.shop_id             -- 商家ID
    ,u2.shop_name           -- 商家名称
    ,u3.user_id             -- 买家用户ID
    ,u3.user_city           -- 买家用户所在城市
    ,u1.deal_amount         -- 订单金额
from
(-- 交易事务事实表
    select
         deal_id -- 交易id
        ,stock_id    -- 商品id
        ,deal_date  -- 交易日期
        ,shop_id -- 商家id
        ,user_id -- 用户id
        ,stock_cnt   -- 商品数量
        ,deal_amount -- 订单金额
    from dwd_deal_orders_detail_da
    where pt='${bizdate}'
    and deal_status='有效'
) u1
left outer join
(-- 商家注册时间
    select
         shop_id --  商家id
        ,shop_name   --  商家店铺名称
    from dim_shops_main_info_da
    where pt='${bizdate}'
) u2
on(u1.shop_id=u2.shop_id)
left outer join
(-- 用户维度表
    select
         user_id
        ,user_city   -- 用户所在城市
    from dim_users_main_info_da
    where pt='${bizdate}'
) u3
on(u1.user_id=u3.user_id)
left outer join
(-- 商品维度表
    select
         stock_id        -- 商品id
        ,stock_name      -- 商品名称
        ,stock_1st_level     -- 商品一级类目
        ,stock_2st_level     -- 商品二级类目
        ,stock_price     -- 商品单价
        ,stock_activity_status       -- 商品活动状态
        ,shop_id     -- 商家id
    from dim_stocks_main_info_da
    where pt='${bizdate}'
    and stock_activity_status='促销'
) u4
on(u1.stock_id=u4.stock_id);

五、进⾏应⽤层的数据开发

参考ads⽬录，进⾏数据指标的汇总开发

1、看板_总指标看板统计

参考ads/ads_dashboard_total_stat_da.sql 脚本

--odps sql 
--********************************************************************--
--author:侯老师
--create time:2021-11-10 20:27:27
--table name: 看板_总指标看板统计
--********************************************************************--

create table if not exists ads_dashboard_total_stat_da(
    total_deal_amont      decimal(38,18)  comment '总销售金额',
    total_user_ucnt       bigint  comment '购买用户数'
)
comment '看板_总指标看板统计'
partitioned by(pt string comment '日期分区');


insert overwrite table ads_dashboard_total_stat_da partition(pt='${bizdate}')
select
    sum(deal_amount)           as total_deal_amont     -- 总销售金额
    ,count(distinct user_id)    as total_user_ucnt      -- 购买用户数
from dws_deal_orders_summary_da
where pt='${bizdate}';

2、看板_商品销售地域分析

参考ads/ads_dashboard_city_stat_da.sql脚本

--odps sql 
--********************************************************************--
--author:侯老师
--create time:2021-11-10 20:27:27
--table name: 看板_商品销售地域分析
--********************************************************************--

create table if not exists ads_dashboard_city_stat_da(
    user_city               string          comment '用户所在城市',
    total_deal_amount       decimal(38,18)  comment '商品销售地域销售额'
)
comment '看板_商品销售地域分析'
partitioned by(pt string comment '日期分区');


insert overwrite table ads_dashboard_city_stat_da partition(pt='${bizdate}')
select
    user_city           -- 用户所在城市
    ,sum(deal_amount)    as total_deal_amount      -- 商品销售地域分析
from dws_deal_orders_summary_da
where pt='${bizdate}'
group by user_city;

3、看板_商品销售地域分析

参考ads/ads_dashboard_stock_stat_da.sql脚本

--odps sql 
--********************************************************************--
--author:侯老师
--create time:2021-11-10 20:27:27
--table name: 看板_商品销量排行
--********************************************************************--

create table if not exists ads_dashboard_stock_stat_da(
    stock_name           string  comment '商品名称',
    total_deal_cnt       bigint  comment '商品销售量'
)
comment '看板_商品销量排行'
partitioned by(pt string comment '日期分区');


insert overwrite table ads_dashboard_stock_stat_da partition(pt='${bizdate}')
select
    stock_name          -- 商品名称
    ,count(deal_id)    as total_deal_cnt      -- 商品销量
from dws_deal_orders_summary_da
where pt='${bizdate}'
group by stock_name;