做网站下载功能,公司怎么做网页,国外优秀的网站,微信电影小程序搭建数据仓库 基本概念数据库#xff08;database#xff09;和数据仓库#xff08;Data Warehouse#xff09;的异同 整体架构分层架构方法论ER模型#xff08;建模理论#xff09;维度模型 何为分层第一层#xff1a;数据源#xff08;ODS ER模型#xff09;设计要点日志… 数据仓库 基本概念数据库database和数据仓库Data Warehouse的异同 整体架构分层架构方法论ER模型建模理论维度模型 何为分层第一层数据源ODS ER模型设计要点日志表业务表1活动信息表全量表2活动规则表全量表3一级品类表全量表4二级品类表全量表5三级品类表全量表6编码字典表全量表7省份表全量表8地区表全量表9品牌表全量表10购物车表全量表11优惠券信息表全量表12商品平台属性表全量表13商品表全量表14商品销售属性值表全量表15SPU表全量表16营销坑位表全量表17营销渠道表全量表18购物车表增量表19评论表增量表20优惠券领用表增量表21收藏表增量表22订单明细表增量表23订单明细活动关联表增量表24订单明细优惠券关联表增量表25订单表增量表26退单表增量表27订单状态流水表增量表28支付表增量表29退款表增量表30用户表增量表31数据装载脚本 第二层数据加工DWD data warehouse detail事实表设计事务型事实表 事务的原子性事实表设计周期型快照事实表 从当前表中取数据后再放回去需考虑去重问题增加retry的容错性事实表设计累积型快照事实表分区策略 第三层数据统计DWS data warehouse summary 提高性能的关键层第四层数据分析ADS application data service优化 第五层共通层DIM dimension设计要点维度表设计 拉链表设计任务调度器 基本概念
本质是对数据进行加工处理后对外提供数据服务
数据库database和数据仓库Data Warehouse的异同
数据库用于存储企业基础核心的业务数据从数据来源进行区分 数据库企业的业务系统数据仓库数据库后台的后台 从数据存储进行区分 数据库存储的目的为了可以快速进行数据查询操作 索引 SQL 存储方式行式存储 数据量不能存储海量数据数据仓库存储的目的为了可以快速进行统计分析 索引 没有索引k-v 存储方式列式存储 数据量必须存储海量数据 从数据价值进行区分 数据库 保障企业业务系统的执行 事务回滚数据仓库 统计分析的结果可以为企业的经营决策提供数据依据 没有事务 数据仓库不是数据流转的终点 可视化才是数据的终点 整体架构
Spark : 数据的统计分析 数据仓库数据的统计分析 数据仓库不能直接对接MySQL数据库作为数据源
数据库不是为了数据仓库服务的。数据仓库如果直接对象数据库会导致数据库的性能降低数据库不能存储海量数据。数据仓库必须获取海量数据数据库采用行式存储。数据仓库为了提高统计分析效率所以需要列式存储
数据仓库应该增加自己的数据源 数据仓库的数据源中的数据应该和MySQL数据库中的数据保持一致 数据仓库的数据源应该不断融合汇总MySQL数据库中的数据 将数据库的数据汇总的到数据仓库数据源的过程一般称之为数据同步也称之为数据采集
分层架构
数据仓库计算周期为1天1天统计一回数据结果
方法论
ER模型建模理论
EREntity Relationship实体关系模型 采用面向对象的方式设计表和Java一样
将对象理解为表将对象之间的关系理解为表之间的关系 超详细内容带图看这里
维度模型
事实 行为所产生的事情数据 维度分析数据的角度状态 超详细内容带图看这里
何为分层
Spark中的方法可能会含有shuffle功能 shuffle操作会将完整的计算流程一分为二会分为2个阶段Stage,前面一个阶段称之为Map阶段后面的阶段称之为Reduce阶段 shuffle中前一个阶段的任务不执行完后面的阶段的任务不允许执行的 Task Pool(任务池) - 任务调度FIFO, FAIR。
数据仓库也存在同样的问题将整个计算流程分为了4段 在数据仓库中不称之为段一般称之为层每一层有特殊的含义和特殊的功能 前面一层的数据没有处理完后面一层的数据没有办法处理 第一层数据源ODS ER模型
功能 为整个数据仓库作为数据来源 不断汇总业务数据和日志数据 数据量非常大海量数据 - 考虑资源问题使用最少的资源存储最多的资源考虑使用压缩算法gzip、lzo、snappy考虑网络资源考虑传输方式数据尽可能不变格式、压缩方式、存储方式 统计本质上就是对行为数据进行统计 分析本质上就是站在什么角度对统计结果进行分析
-- ODS-- 1. ODS层表建模方式ER模型-- 2. 数据格式不变数据压缩方式 gzip-- 3. 表名-- 分层标记ods_ 同步数据的表名 增量/全量inc/full-- 增量全量设计要点
1ODS层的表结构设计依托于从业务系统同步过来的数据结构。 2ODS层要保存全部历史数据故其压缩格式应选择压缩比较高的此处选择gzip。 3ODS层表名的命名规范为ods_表名_单分区增量全量标识inc/full。
日志表
1建表语句
DROP TABLE IF EXISTS ods_log_inc;
CREATE EXTERNAL TABLE ods_log_inc
(common STRUCTar :STRING,ba :STRING,ch :STRING,is_new :STRING,md :STRING,mid :STRING,os :STRING,sid :STRING,uid :STRING,vc :STRING COMMENT 公共信息,page STRUCTduring_time :STRING,item :STRING,item_type :STRING,last_page_id :STRING,page_id :STRING,from_pos_id :STRING,from_pos_seq :STRING,refer_id :STRING COMMENT 页面信息,actions ARRAYSTRUCTaction_id:STRING,item:STRING,item_type:STRING,ts:BIGINT COMMENT 动作信息,displays ARRAYSTRUCTdisplay_type :STRING,item :STRING,item_type :STRING,pos_seq :STRING,pos_id :STRING COMMENT 曝光信息,start STRUCTentry :STRING,first_open :BIGINT,loading_time :BIGINT,open_ad_id :BIGINT,open_ad_ms :BIGINT,open_ad_skip_ms :BIGINT COMMENT 启动信息,err STRUCTerror_code:BIGINT,msg:STRING COMMENT 错误信息,ts BIGINT COMMENT 时间戳
) COMMENT 活动信息表PARTITIONED BY (dt STRING)ROW FORMAT SERDE org.apache.hadoop.hive.serde2.JsonSerDe
LOCATION /warehouse/gmall/ods/ods_log_inc/
TBLPROPERTIES (compression.codecorg.apache.hadoop.io.compress.GzipCodec);2数据装载
load data inpath /origin_data/gmall/log/topic_log/2022-06-08 into table ods_log_inc partition(dt2022-06-08);3每日数据装载脚本 1在hadoop102的/home/atguigu/bin目录下创建hdfs_to_ods_log.sh
vim hdfs_to_ods_log.sh2编写如下内容
#!/bin/bash# 定义变量方便修改
APPgmall# 如果是输入的日期按照取输入日期如果没输入日期取当前时间的前一天
if [ -n $1 ] ;thendo_date$1
elsedo_datedate -d -1 day %F
fiecho 日志日期为 $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 $sql3增加脚本执行权限
chmod x hdfs_to_ods_log.sh4脚本用法 hdfs_to_ods_log.sh 2022-06-08业务表
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 创建时间,operate_time STRING COMMENT 修改时间
) COMMENT 活动信息表PARTITIONED BY (dt STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY \tNULL DEFINED AS
LOCATION /warehouse/gmall/ods/ods_activity_info_full/
TBLPROPERTIES (compression.codecorg.apache.hadoop.io.compress.GzipCodec);2活动规则表全量表
DROP TABLE IF EXISTS ods_activity_rule_full;
CREATE EXTERNAL TABLE ods_activity_rule_full
(id STRING COMMENT 编号,activity_id STRING COMMENT 活动ID,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 优惠级别,create_time STRING COMMENT 创建时间,operate_time STRING COMMENT 修改时间
) COMMENT 活动规则表PARTITIONED BY (dt STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY \tNULL DEFINED AS
LOCATION /warehouse/gmall/ods/ods_activity_rule_full/
TBLPROPERTIES (compression.codecorg.apache.hadoop.io.compress.GzipCodec);3一级品类表全量表
DROP TABLE IF EXISTS ods_base_category1_full;
CREATE EXTERNAL TABLE ods_base_category1_full
(id STRING COMMENT 编号,name STRING COMMENT 分类名称,create_time STRING COMMENT 创建时间,operate_time STRING COMMENT 修改时间
) COMMENT 一级品类表PARTITIONED BY (dt STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY \tNULL DEFINED AS
LOCATION /warehouse/gmall/ods/ods_base_category1_full/
TBLPROPERTIES (compression.codecorg.apache.hadoop.io.compress.GzipCodec);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 一级分类编号,create_time STRING COMMENT 创建时间,operate_time STRING COMMENT 修改时间
) COMMENT 二级品类表PARTITIONED BY (dt STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY \tNULL DEFINED AS
LOCATION /warehouse/gmall/ods/ods_base_category2_full/
TBLPROPERTIES (compression.codecorg.apache.hadoop.io.compress.GzipCodec);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 二级分类编号,create_time STRING COMMENT 创建时间,operate_time STRING COMMENT 修改时间
) COMMENT 三级品类表PARTITIONED BY (dt STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY \tNULL DEFINED AS
LOCATION /warehouse/gmall/ods/ods_base_category3_full/
TBLPROPERTIES (compression.codecorg.apache.hadoop.io.compress.GzipCodec);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 \tNULL DEFINED AS
LOCATION /warehouse/gmall/ods/ods_base_dic_full/
TBLPROPERTIES (compression.codecorg.apache.hadoop.io.compress.GzipCodec);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 STRING COMMENT 新版国际标准地区编码供可视化使用,create_time STRING COMMENT 创建时间,operate_time STRING COMMENT 修改时间
) COMMENT 省份表PARTITIONED BY (dt STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY \tNULL DEFINED AS
LOCATION /warehouse/gmall/ods/ods_base_province_full/
TBLPROPERTIES (compression.codecorg.apache.hadoop.io.compress.GzipCodec);8地区表全量表
DROP TABLE IF EXISTS ods_base_region_full;
CREATE EXTERNAL TABLE ods_base_region_full
(id STRING COMMENT 地区ID,region_name STRING COMMENT 地区名称,create_time STRING COMMENT 创建时间,operate_time STRING COMMENT 修改时间
) COMMENT 地区表PARTITIONED BY (dt STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY \tNULL DEFINED AS
LOCATION /warehouse/gmall/ods/ods_base_region_full/
TBLPROPERTIES (compression.codecorg.apache.hadoop.io.compress.GzipCodec);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的图片路径,create_time STRING COMMENT 创建时间,operate_time STRING COMMENT 修改时间
) COMMENT 品牌表PARTITIONED BY (dt STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY \tNULL DEFINED AS
LOCATION /warehouse/gmall/ods/ods_base_trademark_full/
TBLPROPERTIES (compression.codecorg.apache.hadoop.io.compress.GzipCodec);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 下单时间
) COMMENT 购物车全量表PARTITIONED BY (dt STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY \tNULL DEFINED AS
LOCATION /warehouse/gmall/ods/ods_cart_info_full/
TBLPROPERTIES (compression.codecorg.apache.hadoop.io.compress.GzipCodec);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、商品(SPUID) 2、品类(三级品类id) 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 过期时间,range_desc STRING COMMENT 范围描述
) COMMENT 优惠券信息表PARTITIONED BY (dt STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY \tNULL DEFINED AS
LOCATION /warehouse/gmall/ods/ods_coupon_info_full/
TBLPROPERTIES (compression.codecorg.apache.hadoop.io.compress.GzipCodec);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 SKU_ID,attr_name STRING COMMENT 平台属性名称,value_name STRING COMMENT 平台属性值名称,create_time STRING COMMENT 创建时间,operate_time STRING COMMENT 修改时间
) COMMENT 商品平台属性表PARTITIONED BY (dt STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY \tNULL DEFINED AS
LOCATION /warehouse/gmall/ods/ods_sku_attr_value_full/
TBLPROPERTIES (compression.codecorg.apache.hadoop.io.compress.GzipCodec);13商品表全量表
DROP TABLE IF EXISTS ods_sku_info_full;
CREATE EXTERNAL TABLE ods_sku_info_full
(id STRING COMMENT SKU_ID,spu_id STRING COMMENT SPU_ID,price DECIMAL(16, 2) COMMENT 价格,sku_name STRING COMMENT SKU名称,sku_desc STRING COMMENT SKU规格描述,weight DECIMAL(16, 2) COMMENT 重量,tm_id STRING COMMENT 品牌ID,category3_id STRING COMMENT 三级品类ID,sku_default_img STRING COMMENT 默认显示图片地址,is_sale STRING COMMENT 是否在售,create_time STRING COMMENT 创建时间,operate_time STRING COMMENT 修改时间
) COMMENT 商品表PARTITIONED BY (dt STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY \tNULL DEFINED AS
LOCATION /warehouse/gmall/ods/ods_sku_info_full/
TBLPROPERTIES (compression.codecorg.apache.hadoop.io.compress.GzipCodec);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 销售属性值名称,create_time STRING COMMENT 创建时间,operate_time STRING COMMENT 修改时间
) COMMENT 商品销售属性值表PARTITIONED BY (dt STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY \tNULL DEFINED AS
LOCATION /warehouse/gmall/ods/ods_sku_sale_attr_value_full/
TBLPROPERTIES (compression.codecorg.apache.hadoop.io.compress.GzipCodec);15SPU表全量表
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,create_time STRING COMMENT 创建时间,operate_time STRING COMMENT 修改时间
) COMMENT SPU表PARTITIONED BY (dt STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY \tNULL DEFINED AS
LOCATION /warehouse/gmall/ods/ods_spu_info_full/
TBLPROPERTIES (compression.codecorg.apache.hadoop.io.compress.GzipCodec);16营销坑位表全量表
DROP TABLE IF EXISTS ods_promotion_pos_full;
CREATE EXTERNAL TABLE ods_promotion_pos_full
(id STRING COMMENT 营销坑位ID,pos_location STRING COMMENT 营销坑位位置,pos_type STRING COMMENT 营销坑位类型banner,宫格,列表,瀑布,promotion_type STRING COMMENT 营销类型算法、固定、搜索,create_time STRING COMMENT 创建时间,operate_time STRING COMMENT 修改时间
) COMMENT 营销坑位表PARTITIONED BY (dt STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY \tNULL DEFINED AS
LOCATION /warehouse/gmall/ods/ods_promotion_pos_full/
TBLPROPERTIES (compression.codecorg.apache.hadoop.io.compress.GzipCodec);17营销渠道表全量表
DROP TABLE IF EXISTS ods_promotion_refer_full;
CREATE EXTERNAL TABLE ods_promotion_refer_full
(id STRING COMMENT 外部营销渠道ID,refer_name STRING COMMENT 外部营销渠道名称,create_time STRING COMMENT 创建时间,operate_time STRING COMMENT 修改时间
) COMMENT 营销渠道表PARTITIONED BY (dt STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY \tNULL DEFINED AS
LOCATION /warehouse/gmall/ods/ods_promotion_refer_full/
TBLPROPERTIES (compression.codecorg.apache.hadoop.io.compress.GzipCodec);18购物车表增量表
DROP TABLE IF EXISTS ods_cart_info_inc;
CREATE EXTERNAL TABLE ods_cart_info_inc
(type STRING COMMENT 变动类型,ts BIGINT COMMENT 变动时间,data STRUCTid :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 COMMENT 数据,old MAPSTRING,STRING COMMENT 旧值
) COMMENT 购物车增量表PARTITIONED BY (dt STRING)ROW FORMAT SERDE org.apache.hadoop.hive.serde2.JsonSerDe
LOCATION /warehouse/gmall/ods/ods_cart_info_inc/
TBLPROPERTIES (compression.codecorg.apache.hadoop.io.compress.GzipCodec);19评论表增量表
DROP TABLE IF EXISTS ods_comment_info_inc;
CREATE EXTERNAL TABLE ods_comment_info_inc
(type STRING COMMENT 变动类型,ts BIGINT COMMENT 变动时间,data STRUCTid :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 MAPSTRING,STRING COMMENT 旧值
) COMMENT 评论表PARTITIONED BY (dt STRING)ROW FORMAT SERDE org.apache.hadoop.hive.serde2.JsonSerDe
LOCATION /warehouse/gmall/ods/ods_comment_info_inc/
TBLPROPERTIES (compression.codecorg.apache.hadoop.io.compress.GzipCodec);20优惠券领用表增量表
DROP TABLE IF EXISTS ods_coupon_use_inc;
CREATE EXTERNAL TABLE ods_coupon_use_inc
(type STRING COMMENT 变动类型,ts BIGINT COMMENT 变动时间,data STRUCTid :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, create_time :STRING,operate_time :STRING COMMENT 数据,old MAPSTRING,STRING COMMENT 旧值
) COMMENT 优惠券领用表PARTITIONED BY (dt STRING)ROW FORMAT SERDE org.apache.hadoop.hive.serde2.JsonSerDe
LOCATION /warehouse/gmall/ods/ods_coupon_use_inc/
TBLPROPERTIES (compression.codecorg.apache.hadoop.io.compress.GzipCodec);21收藏表增量表
DROP TABLE IF EXISTS ods_favor_info_inc;
CREATE EXTERNAL TABLE ods_favor_info_inc
(type STRING COMMENT 变动类型,ts BIGINT COMMENT 变动时间,data STRUCTid :STRING,user_id :STRING,sku_id :STRING,spu_id :STRING,is_cancel :STRING,create_time :STRING,operate_time:STRING COMMENT 数据,old MAPSTRING,STRING COMMENT 旧值
) COMMENT 收藏表PARTITIONED BY (dt STRING)ROW FORMAT SERDE org.apache.hadoop.hive.serde2.JsonSerDe
LOCATION /warehouse/gmall/ods/ods_favor_info_inc/
TBLPROPERTIES (compression.codecorg.apache.hadoop.io.compress.GzipCodec);22订单明细表增量表
DROP TABLE IF EXISTS ods_order_detail_inc;
CREATE EXTERNAL TABLE ods_order_detail_inc
(type STRING COMMENT 变动类型,ts BIGINT COMMENT 变动时间,data STRUCTid :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),operate_time :STRING COMMENT 数据,old MAPSTRING,STRING COMMENT 旧值
) COMMENT 订单明细表PARTITIONED BY (dt STRING)ROW FORMAT SERDE org.apache.hadoop.hive.serde2.JsonSerDe
LOCATION /warehouse/gmall/ods/ods_order_detail_inc/
TBLPROPERTIES (compression.codecorg.apache.hadoop.io.compress.GzipCodec);23订单明细活动关联表增量表
DROP TABLE IF EXISTS ods_order_detail_activity_inc;
CREATE EXTERNAL TABLE ods_order_detail_activity_inc
(type STRING COMMENT 变动类型,ts BIGINT COMMENT 变动时间,data STRUCTid :STRING,order_id :STRING,order_detail_id :STRING,activity_id :STRING,activity_rule_id :STRING,sku_id:STRING,create_time :STRING, operate_time :STRING COMMENT 数据,old MAPSTRING,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/
TBLPROPERTIES (compression.codecorg.apache.hadoop.io.compress.GzipCodec);24订单明细优惠券关联表增量表
DROP TABLE IF EXISTS ods_order_detail_coupon_inc;
CREATE EXTERNAL TABLE ods_order_detail_coupon_inc
(type STRING COMMENT 变动类型,ts BIGINT COMMENT 变动时间,data STRUCTid :STRING,order_id :STRING,order_detail_id :STRING,coupon_id :STRING,coupon_use_id :STRING,sku_id:STRING,create_time :STRING, operate_time :STRING COMMENT 数据,old MAPSTRING,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/
TBLPROPERTIES (compression.codecorg.apache.hadoop.io.compress.GzipCodec);25订单表增量表
DROP TABLE IF EXISTS ods_order_info_inc;
CREATE EXTERNAL TABLE ods_order_info_inc
(type STRING COMMENT 变动类型,ts BIGINT COMMENT 变动时间,data STRUCTid :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 MAPSTRING,STRING COMMENT 旧值
) COMMENT 订单表PARTITIONED BY (dt STRING)ROW FORMAT SERDE org.apache.hadoop.hive.serde2.JsonSerDe
LOCATION /warehouse/gmall/ods/ods_order_info_inc/
TBLPROPERTIES (compression.codecorg.apache.hadoop.io.compress.GzipCodec);26退单表增量表
DROP TABLE IF EXISTS ods_order_refund_info_inc;
CREATE EXTERNAL TABLE ods_order_refund_info_inc
(type STRING COMMENT 变动类型,ts BIGINT COMMENT 变动时间,data STRUCTid :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,operate_time :STRING COMMENT 数据,old MAPSTRING,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/
TBLPROPERTIES (compression.codecorg.apache.hadoop.io.compress.GzipCodec);27订单状态流水表增量表
DROP TABLE IF EXISTS ods_order_status_log_inc;
CREATE EXTERNAL TABLE ods_order_status_log_inc
(type STRING COMMENT 变动类型,ts BIGINT COMMENT 变动时间,data STRUCTid :STRING,order_id :STRING,order_status :STRING,create_time :STRING,operate_time :STRING COMMENT 数据,old MAPSTRING,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/
TBLPROPERTIES (compression.codecorg.apache.hadoop.io.compress.GzipCodec);28支付表增量表
DROP TABLE IF EXISTS ods_payment_info_inc;
CREATE EXTERNAL TABLE ods_payment_info_inc
(type STRING COMMENT 变动类型,ts BIGINT COMMENT 变动时间,data STRUCTid :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,operate_time :STRING COMMENT 数据,old MAPSTRING,STRING COMMENT 旧值
) COMMENT 支付表PARTITIONED BY (dt STRING)ROW FORMAT SERDE org.apache.hadoop.hive.serde2.JsonSerDe
LOCATION /warehouse/gmall/ods/ods_payment_info_inc/
TBLPROPERTIES (compression.codecorg.apache.hadoop.io.compress.GzipCodec);29退款表增量表
DROP TABLE IF EXISTS ods_refund_payment_inc;
CREATE EXTERNAL TABLE ods_refund_payment_inc
(type STRING COMMENT 变动类型,ts BIGINT COMMENT 变动时间,data STRUCTid :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,operate_time :STRING COMMENT 数据,old MAPSTRING,STRING COMMENT 旧值
) COMMENT 退款表PARTITIONED BY (dt STRING)ROW FORMAT SERDE org.apache.hadoop.hive.serde2.JsonSerDe
LOCATION /warehouse/gmall/ods/ods_refund_payment_inc/
TBLPROPERTIES (compression.codecorg.apache.hadoop.io.compress.GzipCodec);30用户表增量表
DROP TABLE IF EXISTS ods_user_info_inc;
CREATE EXTERNAL TABLE ods_user_info_inc
(type STRING COMMENT 变动类型,ts BIGINT COMMENT 变动时间,data STRUCTid :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 MAPSTRING,STRING COMMENT 旧值
) COMMENT 用户表PARTITIONED BY (dt STRING)ROW FORMAT SERDE org.apache.hadoop.hive.serde2.JsonSerDe
LOCATION /warehouse/gmall/ods/ods_user_info_inc/
TBLPROPERTIES (compression.codecorg.apache.hadoop.io.compress.GzipCodec);31数据装载脚本
1在hadoop102的/home/atguigu/bin目录下创建hdfs_to_ods_db.sh
vim hdfs_to_ods_db.sh2编写如下内容
#!/bin/bashAPPgmallif [ -n $2 ] ;thendo_date$2
else do_datedate -d -1 day %F
fiload_data(){sqlfor i in $*; do#判断路径是否存在hadoop fs -test -e /origin_data/$APP/db/${i:4}/$do_date#路径存在方可装载数据if [[ $? 0 ]]; thensql$sqlload data inpath /origin_data/$APP/db/${i:4}/$do_date OVERWRITE into table ${APP}.$i partition(dt$do_date);fidonehive -e $sql
}case $1 inods_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_promotion_pos_full)load_data ods_promotion_pos_full;;ods_promotion_refer_full)load_data ods_promotion_refer_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_promotion_pos_full ods_promotion_refer_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;;
esac3增加脚本执行权限
chmod x hdfs_to_ods_db.sh4脚本用法
hdfs_to_ods_db.sh all 2022-06-08第二层数据加工DWD data warehouse detail
功能将数据源中的数据进行加工处理判空、无效 为了后续统计分析做数据准备 数据量非常大所以分离出了DIM层将数据整合 压缩方式snappy
事实表设计事务型事实表
-- DWD-- Data Warehouse Detail-- detail : 详细明细-- DWD层表主要设计的目的为了统计分析做准备-- 表中主要保存的是行为数据-- 多个行为数据中如果存在共通性的内容那么可以提炼出来形成DIM层维度表的数据-- 表的设计要点-- 表的设计要依据维度建模理论中的事实表-- 表设计时需要orc列式存储以及snappy压缩-- 命名规范-- 分层标记dwd_ 数据域(分类) 原子性行为名称 增量/全量inc/full-- 绝大多数的行为数据都是增量数据采集-- 特殊情况例外可以采用全量方式实现。-- dwd_user_login_success_inc-- 事实表-- 维度引用 度量值行为产生时可以用于统计分析的数值金额数量个数-- 事实表会根据场景分为3大类-- 1. 事务型事实表-- 行为是原子性-- 用户登录非原子-- 用户登录成功原子-- 用户登录失败原子-- 粒度描述一行数据的详细程度-- 描述的越详细维度越多粒度越细-- 描述的越简单维度越多粒度越粗-- 设计步骤-- 1. 选择业务过程 确定表-- 2. 声明粒度确定行-- 3. 确认维度确定列-- 4. 确认事实确定度量值-- 2. 周期快照事实表-- 3. 累积快照事实表-- 交易域加购事务事实表-- 交易域 : trade-- 加购 : 行为-- 将商品加入到购物车中的行为-- 购物车中没有这个商品往购物车中增加商品-- 购物车中有这个商品继续往购物车中增加该商品-- 事务事实表-- 原子性-- 时间行为时间 用户 商品 数量-- 表的字段结构必要的维度属性 度量值 可选的维度属性-- 建表语句-- 分区策略哪一天的行为数据存放到哪一天分区事务的原子性
登录成功OK 登录失败OK 下单成功OK 下单失败非正常业务行为不需要再创建一张表 支付成功OK 支付失败OK
事实表设计周期型快照事实表
全量
-- 事务性事实表局限性
-- 事实表只针对于当前行为进行的统计分析时性能可以得到保障。
-- 当前行为事实表和其他行为数据进行关联时数据量会几何爆炸性增长性能会急剧下降。
-- 存量性统计指标使用事务性事实表效率太低所以一般会采用其他事实表的设计方式-- 2. 周期型快照事实表-- 交易域购物车周期快照事实表-- 交易域-- 购物车 cart_info-- 周期快照事实表从当前表中取数据后再放回去需考虑去重问题增加retry的容错性
事实表设计累积型快照事实表
-- 多行为统计指标使用事务性事实表效率太低所以一般会采用其他事实表的设计方式
-- 3. 累积型快照事实表-- 使用一张表保存多个行为的状态数据-- 交易域交易流程累积快照事实表-- 交易域-- 交易流程 : 以订单为基础的交易流程-- 累积快照事实表分区策略
-- 事务性事实表哪一天的行为数据存放到哪一天的分区
-- 周期性事实表每一天存储一份数据
-- 累积快照事实表从业务流程中获取最后一个业务行为时间作为分区字段-- 下单时间 (X)-- 支付时间 (X)-- 收货时间 (OK)第三层数据统计DWS data warehouse summary 提高性能的关键层
功能将加工后的数据进行统计 数据量非常大 压缩方式snappy 第四层数据分析ADS application data service
功能将统计结果进行分析为用户提供经营决策 压缩方式gzip 数据格式tsv
优化
Spark
reduceByKey函数内部combine减少落盘数据量和groupByKeycache、persist和checkpointDWS
第五层共通层DIM dimension
功能将共同的数据放在共通的表中可在多个统计需求中使用 dimension维度分析数据的角度 该层不需要一开始就设计可以等DWD层设计的差不多了或是写着写着发现DWD中有好多表都用到了共通的字段有大量冗余数据那么就可以将这部分共通的数据提取成一个表
设计要点
1DIM层的设计依据是维度建模理论该层存储维度模型的维度表。 2DIM层的数据存储格式为orc列式存储snappy压缩。 3DIM层表名的命名规范为dim_表名_全量表或者拉链表标识full/zip。 绝大多数的维度表都是全量表
维度表设计
确定维度表确定维度的表是否该创建 原则上来讲每一个分析数据的角度维度都应该创建一张表 案例统计各个省份各个品牌的订单总销量 – 订单属于事实行为表省份和品牌就是维度表案例统计各个性别不同年龄段的订单总销量 – 订单属于事实行为表, 性别和年龄就是维度表 如果多个维度存在关联那么一般就会只创建一张表表中包含了多个关联的维度如果分析数据的角度应用场景少而且数据量小不需要创建专门的维度表 案例支付方式微信支付支付宝支付 确定主维表和相关维表(用于分析维度表的列) 确定表中的列 案例省份维度表 – 列名称数据仓库的数据都来自于MySQL业务数据 – 维度表的列的声明可以参考业务数据库表的字段MySQL业务数据库中具有唯一性字段的那个业务表称之为主维表 – 其他的表称之为相关维表。 确定表中的列 尽可能丰富多编码和文字共存沉淀通用属性 tel, xxx – 计算或转换
-- 商品维度表 dim_sku_full-- 确定维度表-- 主维表和相关维表-- 主维表和相关维表都是MySQL业务表-- 主要用于分析列的表称之主维表主键-- sku_info-- 其他用于分析列的表称之相关维表-- sku_attr_value-- sku_sale_attr_value-- 确定表的列-- 建表语句其中日期维度表不需要从MySQL中导而是从文件中另行导入也不需要每天导入每年导入一次即可
拉链表设计
-- 数据装载-- load-- save-- 增量表得数据操作一般都会写2个-- 首日数据装载-- 每日数据装载
-- 首日数据装载-- 同步方式maxwell - 全量 - bootstrap - select * from user_info-- MySQL不保存行为数据也就意味着不保存历史行为数据-- 拉链表会在当前表得字段得基础上额外添加两个字段start, end用于标记状态得有效范围-- start : 无法判断开始范围-- end : 无法判断-- 折中地考虑-- 从当天开始结束时间取时间极大值避免数据频繁修改-- 分区策略-- 绝大多数得维度表得分区策略都是以天为单位-- 分区不能采用开始日期作为分区字段-- 无法判断数据是否为历史状态还是最新状态-- 好得方式是使用结束时间为分区字段任务调度器
保证每一层的SQL跑完再跑下一层
