数仓开发

2024-01-24 23:41 由 xQil 发表于 #其他

数仓开发

一.数仓分层

1.为什么要分层？

清晰数据结构：每一个数据分层都有它的作用域，这样我们在使用表的时候能更方便地定位和理解。
数据血缘追踪：如果有一张来源表出问题了，我们希望能够快速准确地定位到问题，并清楚它的危害范围。
减少重复开发：规范数据分层，开发一些通用的中间层数据，能够减少极大的重复计算。
统一数据口径：通过数据分层，提供统一的数据出口，统一对外输出的数据口径。
屏蔽业务的影响：不必改一次业务就需要重新接入数据。
屏蔽原始数据的异常：不论是数据的异常还是数据敏感性，使真实数据与统计数据解耦开。

2.三层设计（ODS,DW,ADS）

2.1数据运营层:ODS（Operational Data Store）

ODS（Operational Data Store）层存放未经过处理的原始数据至数据仓库系统，结构上与
源系统保持一致，是数据仓库的数据准备区。(严格的说
ODS层不属于数仓建模的范畴，但是合理的规划ODS层并做好数据同步也非常重要。)

2.1.1命名规范

表命名规则：{层次}{源系统表名}

ODS ETL过程的临时表：{层次}tmp{临时表所在过程的输出表}{从0开始的序号}。
按小时同步的增量表：{层次}{源系统表名}{delta}{hh}。
按小时同步的全量表：{层次}{源系统表名}{hh}

MySql上有一张表为car_sales_info(记录汽车的商品信息), 则在数据仓库（Hive）建立一张跟car_sales_info表结构一样的表，命名为ods_car_sales_info,并将car_sale_info的数据原封不动地导入ods_car_sales_info。
表或字段命名尽量和业务系统保持一致，但是需要通过额外的标识来区分增量和全量表还有。例
如，我们可以通过_delta来标识该表为增量表,__full为增量表。
命名时需要特别注意冲突处理，例如不同业务系统的表可能是同一个名称。为区分两个不同的
表，可以将这两个同名表的来源数据库名称作为后缀或前缀。
ODS层的表建议以统计日期及时间分区表的方式存储，便于管理数据的存储成本和策略控制和满足历史数据分析需求。

2.2数据仓库层：DW（Data Warehouse）

数据仓库层从上到下，又可以分为3个层：

数据细节层DWD(Data Warehouse Details)、数据中间层DWM(Data Warehouse Middle)、数据服务层DWS(Data Warehouse Service)。

2.2.1数据细节层DWD(Data Warehouse Details)

该层是业务层和数据仓库的隔离层，保持和ODS层一样的数据颗粒度；主要是对ODS数据层做一些数据的清洗和规范化的操作，比如去除空数据、脏数据、离群值等。

为了提高数据明细层的易用性，该层通常会才采用一些维度退化方法，将维度退化至事实表中，减少事实表和维表的关联。

-- 根据2.4下面DIM表进行维度退化
-- 说白了就算将2张有关联的维度表合并在一起，减少查询带来的消耗
CREATE TABLE IF NOT EXISTS dwd_asale_trd_itm_di (
item_id BIGINT COMMENT '商品ID',
item_title STRING COMMENT '商品名称',
item_price DOUBLE COMMENT '商品价格',
item_stuff_status BIGINT COMMENT '商品新旧程度_0全新1闲置2二手',
item_prov STRING COMMENT '商品省份',
item_city STRING COMMENT '商品城市',
cate_id BIGINT COMMENT '商品类目ID',
cate_name STRING COMMENT '商品类目名称',
commodity_id BIGINT COMMENT '品类ID',
commodity_name STRING COMMENT '品类名称',
buyer_id STRING COMMENT '买家ID',
city_code STRING COMMENT '买家城市code',
city_name STRING COMMENT '买家城市名称',
prov_code STRING COMMENT '买家省份code',
prov_name STRING COMMENT '买家省份名称'
)
COMMENT '交易商品信息事实表'
PARTITIONED BY (ds STRING COMMENT '日期')

命名规范：
通常需要遵照的命名规范为：dwd_{业务板块/pub}_{数据域缩写}_{业务过程缩写}[_{自定义表命名标签缩
写}] _{单分区增量全量标识}，pub表示数据包括多个业务板块的数据。单分区增量全量标识通常为：i表示增
量，f表示全量。例如： dwd_asale_trd_ordcrt_trip_di（A电商公司航旅机票订单下单事实表，日刷新
增量）
dwd_asale_itm_item_df（A电商商品快照事实表，日刷新全量）
d：表示day

2.2.2数据中间层：DWM（Data WareHouse Middle）

层会在DWD层的数据基础上，对数据做轻度的聚合操作，生成一系列的中间表。直观来讲，就是对通用的核心维度进行聚合操作，算出相应的统计指标。（有时候可以省略这层，下面会讲）

2.2.3数据服务层DWS(Data Warehouse Service)。

DWS层是基于DWM上的基础数据，用于整合汇总特定主题域的数据服务。它通常是宽表，提供后续的业务查询、OLAP分析和数据分发等功能。该层的数据表较少，每张表包含多个业务内容，字段较多。为了减少计算量并增加维度，一般会在DWM层先计算多个小的中间表，然后再拼接成一张宽表(DWS)。根据需要，也可以直接在DWS层进行数据存储，而省略DWM层。

CREATE TABLE IF NOT EXISTS dws_asale_trd_byr_ord_1d (
buyer_id BIGINT COMMENT '买家id',
buyer_nick STRING COMMENT '买家昵称',
mord_prov STRING COMMENT '收货人省份',
cate_id BIGINT COMMENT '商品类目id',
cate_name STRING COMMENT '商品类目名称',
confirm_paid_amt_sum_1d DOUBLE COMMENT '最近一天订单已经确认收货的金额总和'
)
COMMENT '买家粒度所有交易最近一天汇总事实表'
PARTITIONED BY (ds STRING COMMENT '分区字段YYYYMMDD')

公共汇总事实表命名规范：dws_{业务板块缩写/pub}_{数据域缩写}_{数据粒度缩写}[_{自定义表命名标签
缩写}]_{统计时间周期范围缩写}。
1. 关于统计实际周期范围缩写，缺省情况下，离线计算应该包括最近一天（_1d）,最近N天（_nd）和历史截
至当天（_td）三个表。如果出现_nd的表字段过多需要拆分时，只允许以一个统计周期单元作为原子拆分。即
一个统计周期拆分一个表，例如最近7天（_1w）拆分一个表。不允许拆分出来的一个表存储多个统计周期。
2. 对于小时表[无论是天刷新还是小时刷新]，都用_hh来表示。
3. 对于分钟表[无论是天刷新还是小时刷新]，都用_mm来表示。
例如：
dws_asale_trd_byr_subpay_1d （A电商公司买家粒度交易分阶段付款一日汇总事实表）
dws_asale_trd_byr_subpay_td（A电商公司买家粒度分阶段付款截至当日汇总表）
dws_asale_trd_byr_cod_nd（A电商公司买家粒度货到付款交易汇总事实表）
dws_asale_itm_slr_td（A电商公司卖家粒度商品截至当日存量汇总表）
dws_asale_itm_slr_hh（A电商公司卖家粒度商品小时汇总表）---维度为小时
dws_asale_itm_slr_mm（A电商公司卖家粒度商品分钟汇总表）---维度为分钟

2.3数据应用层ADS（Application Data Service）

该层主要是提供给数据产品和数据分析使用的数据，一般会存放在ES、Redis、PostgreSql等系统中供线上系统使用；也可能存放在hive或者Druid中，供数据分析和数据挖掘使用，比如常用的数据报表就是存在这里的。

2.4维度表DIM（Dimension）

维度表（Dimension Table）或维表，有时也称查找表（Lookup Table），是与事实表相对应的一种表；它保存了维度的属性值，可以跟事实表做关联，相当于将事实表上经常重复出现的属性抽取、规范出来用一张表进行管理。维度表主要是包含两个部分：

高基数维度数据：一般是用户资料表、商品资料表类似的资料表，数据量可能是千万级或者上亿级别
低基数维度数据：一般是配置表，比如枚举字段对应的中文含义，或者日期维表等；数据量可能就是个位数或者几千几万。

常见的维度表有：日期表（存储与日期对应的周、月、季度等的属性）、地点表（包含国家、省／州、城市等属性）等。

事实表是指存储有事实记录的表，比如系统日志、销售记录等。

DIM层维表命名规范：dim_{业务板块名称/pub}_{维度定义}[_{自定义命名标签}]，pub是与具体业务板块
无关或各个业务板块都可公用的维度。例如，时间维度，举例如下：
公共区域维表dim_pub_area
A公司电商板块的商品全量表dim_asale_itm

-- 商品的维度表
CREATE TABLE IF NOT EXISTS dim_asale_itm (
item_id BIGINT COMMENT '商品ID',
item_title STRING COMMENT '商品名称',
item_price DOUBLE COMMENT '商品成交价格_元',
item_stuff_status BIGINT COMMENT '商品新旧程度_0全新1闲置2二手',
cate_id BIGINT COMMENT '商品类目ID',
cate_name STRING COMMENT '商品类目名称',
commodity_id BIGINT COMMENT '品类ID',
commodity_name STRING COMMENT '品类名称',
umid STRING COMMENT '买家ID',
item_status BIGINT COMMENT '商品状态_0正常1用户删除2下架3未上
架',
city STRING COMMENT '商品所在城市',
prov STRING COMMENT '商品所在省份'
)
COMMENT '商品全量表'
PARTITIONED BY (ds STRING COMMENT '日期,yyyymmdd');
-- 地区的维度表
CREATE TABLE IF NOT EXISTS dim_pub_area (
buyer_id STRING COMMENT '买家ID',
city_code STRING COMMENT '城市code',
 city_name STRING COMMENT '城市名称',
prov_code STRING COMMENT '省份code',
prov_name STRING COMMENT '省份名称'
)
COMMENT '公共区域维表'
PARTITIONED BY (ds STRING COMMENT '日期分区,格式yyyymmdd')