【大数据课程】高途课程实践-Day02:利用Hive SQL编写离线数仓实现可视化展示
2022/2/28 2:21:26
本文主要是介绍【大数据课程】高途课程实践-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;
这篇关于【大数据课程】高途课程实践-Day02:利用Hive SQL编写离线数仓实现可视化展示的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-11-01服务器购买课程:新手入门必备指南
- 2024-11-01动态路由表学习详解
- 2024-11-01服务器购买学习:新手入门指南
- 2024-11-01动态路由表入门详解
- 2024-11-01服务器购买入门:新手必读指南
- 2024-10-31云服务实战:新手入门教程
- 2024-10-31云服务资料入门教程:轻松上手云服务
- 2024-10-31初学者指南:服务器购买全流程解析
- 2024-10-30在Snowflake上运行SFTP服务器的窍门
- 2024-10-30Snowflake数据度量函数在数据质量验证中的应用技巧