国产开源大数据数据仓库技术架构设计 大数据数仓开发规范

数仓的分层总结

ODS：（原始数据层）： 原始，对采集的数据不做处理！
DWD： （明细数据层）： 对原始数据层的数据，展开明细，进行ETL过滤！
DWS： （数据服务层）： 基于ADS需要统计的主题，创建宽表
ADS： （应用数据层）： 基于DWS的宽表，计算出结果

范式

范式：数据库在设计建模时需要遵守的规范和样式！

好处和目的：
①减少数据冗余
②保证数据的一致性

第一范式： 列具有原子性，不能再拆
第二范式： 所有非主键列必须完全依赖于主键，不能存在部分函数依赖
第三范式： 不能存在传递函数依赖

建模

关系(ER)建模: 抽象程度高！侧重于数据的治理！强调数据的整合(不能冗余)和数据的一致性！
关系型数据库中，常用ER建模！

维度建模： 维度建模一般是面向某个业务（主题），将这个业务所需要的有相同特征的数据
放入到一个维度中！特点是会造成数据的冗余(宽表)，好处是面向业务，在具体业务的计算时，
效率高！

大数据领域，一般都使用维度建模！ hive中 表与表之间的join效率低！
hbase中不支持表的join!
采用维度建模，创建宽表！把某个业务所需的所有数据全部集中在表中！

维度建模的常见模型
星型模型： 维度表直接关联在事实表上！
雪花模型： 维度表间接关联在事实表上！
星座模型： 本质上是一种特殊的星型模型！在一个业务场景中，存在多个事实表！
多个事实表共享某个维度表！

 with语法 的使用

with t1 as
(select 
    id user_id,gender user_gender,
    ceil(months_between('2020-02-16',birthday)/12) user_age,
    user_level
from  dwd_user_info
where dt='2020-02-16'),
t2 as 
(select 
    id sku_id,price order_price,sku_name,tm_id sku_tm_id,
    category3_id sku_category3_id,
    category2_id sku_category2_id,
    category1_id sku_category1_id,
    category3_name sku_category3_name, 
    category2_name sku_category2_name, 
    category1_name sku_category1_name,
    spu_id spu_id
from  dwd_sku_info
where  dt='2020-02-16'),
t3 as 
(select 
    orderdatail.sku_num,orderdatail.sku_id,orderdatail.user_id
from ods_order_detail orderdatail join ods_payment_info payment
on orderdatail.order_id = payment.order_id
),
t4 as 
(select 
    orderdatail.sku_id,orderdatail.user_id,
    count(*) order_count,
    sum(orderdatail.order_price*orderdatail.sku_num) order_amount
from ods_order_detail orderdatail join ods_payment_info payment
on orderdatail.order_id = payment.order_id
group by orderdatail.sku_id,orderdatail.user_id)
insert overwrite TABLE dws_sale_detail_daycount PARTITION(dt='2020-02-16')
select 
    t1.user_id,t2.sku_id,t1.user_gender,t1.user_age,t1.user_level,
    t2.order_price,t2.sku_name,t2.sku_tm_id,t2.sku_category3_id,
    t2.sku_category2_id,t2.sku_category1_id,
    t2.sku_category3_name,t2.sku_category2_name,t2.sku_category1_name,
    t2.spu_id,t3.sku_num,t4.order_count,t4.order_amount
from  t4 join t3 
on t4.sku_id=t3.sku_id and t4.user_id=t3.user_id
join t1  on t1.user_id=t3.user_id
join t2 on t3.sku_id=t2.sku_id

if语句得使用

select
    sku_tm_id, sku_category1_id,sku_category1_name,
    sum(if(order_count_per_mn>=1,1,0)) buycount,
    sum(if(order_count_per_mn>=2,1,0)) buy_twice_last,
    cast(sum(if(order_count_per_mn>=2,1,0))/sum(if(order_count_per_mn>=1,1,0))*100 as decimal(10,2)) buy_twice_last_ratio,
    sum(if(order_count_per_mn>=3,1,0)) buy_3times_last,
    cast(sum(if(order_count_per_mn>=3,1,0)) / sum(if(order_count_per_mn>=1,1,0)) * 100 as decimal(10,2)) buy_3times_last_ratio,
    date_format('2020-02-16','yyyy-MM') stat_mn,
    '2020-02-16'
from 
(select 
    user_id,sku_tm_id,count(order_count) order_count_per_mn,sku_category1_id,sku_category1_name
from  dws_sale_detail_daycount
where date_format(dt,'yyyy-MM')=date_format('2020-02-16','yyyy-MM')
group by sku_tm_id,user_id,sku_category1_id,sku_category1_name ) tmp
group by sku_tm_id,sku_category1_id,sku_category1_name

 

数仓各层级脚本 

#!/bin/bash
#向ods的两个表中导入每天的数据，为数据创建LZO索引
#接受要导入数据的日期参数,-n可以判断后面的参数是否为赋值，如果赋值，返回true,否则返回false
#为判断的变量名加双引号
if [ -n "$1" ]
then
     do_date=$1
else
    do_date=$(date -d yesterday +%F)
fi

echo ===日志日期为$do_date===

APP=gmall
sql="
load data inpath '/origin_data/gmall/log/topic_start/$do_date' into table $APP.ods_start_log partition(dt='$do_date');

load data inpath '/origin_data/gmall/log/topic_event/$do_date' into table $APP.ods_event_log partition(dt='$do_date');
"
hive  -e "$sql"

hadoop jar /opt/module/hadoop-2.7.2/share/hadoop/common/hadoop-lzo-0.4.20.jar com.hadoop.compression.lzo.DistributedLzoIndexer /warehouse/gmall/ods/ods_start_log/dt=$do_date

hadoop jar /opt/module/hadoop-2.7.2/share/hadoop/common/hadoop-lzo-0.4.20.jar com.hadoop.compression.lzo.DistributedLzoIndexer /warehouse/gmall/ods/ods_event_log/dt=$do_date

ods层

#!/bin/bash
if [ -n "$1" ]
then
     do_date=$1
else
    do_date=$(date -d yesterday +%F)
fi

echo ===日志日期为$do_date===


sql="

"
hive  -e "$sql"

每层脚本模板

#!/bin/bash
if [ -n "$1" ]
then
     do_date=$1
else
    do_date=$(date -d yesterday +%F)
fi

echo ===日志日期为$do_date===


sql="

insert overwrite table gmall.dwd_start_log
PARTITION (dt='$do_date')
select 
    get_json_object(line,'$.mid') mid_id,
    get_json_object(line,'$.uid') user_id,
    get_json_object(line,'$.vc') version_code,
    get_json_object(line,'$.vn') version_name,
    get_json_object(line,'$.l') lang,
    get_json_object(line,'$.sr') source,
    get_json_object(line,'$.os') os,
    get_json_object(line,'$.ar') area,
    get_json_object(line,'$.md') model,
    get_json_object(line,'$.ba') brand,
    get_json_object(line,'$.sv') sdk_version,
    get_json_object(line,'$.g') gmail,
    get_json_object(line,'$.hw') height_width,
    get_json_object(line,'$.t') app_time,
    get_json_object(line,'$.nw') network,
    get_json_object(line,'$.ln') lng,
    get_json_object(line,'$.la') lat,
    get_json_object(line,'$.entry') entry,
    get_json_object(line,'$.open_ad_type') open_ad_type,
    get_json_object(line,'$.action') action,
    get_json_object(line,'$.loading_time') loading_time,
    get_json_object(line,'$.detail') detail,
    get_json_object(line,'$.extend1') extend1
from gmall.ods_start_log 
where dt='$do_date';


"
hive  -e "$sql"

dwd层

 

get_json_object 的使用

get_json_object的介绍

get_json_object(json_txt, path) - Extract a json object from path 从json对象中抽取指定的内容
Extract json object from a json string based on json path specified,
and return json string of the extracted json object.
It will return null if the input json string is invalid.

需要指定要抽取的内容的path路径！
如果函数传入的数据不是JSON,此时会返回Null！

参数介绍如下：

A limited version of JSONPath supported:
  $   : Root object     代表整个JSON对象
  .   : Child operator   代表获取JSON对象中子元素(属性)的操作符
  []  : Subscript operator for array 获取JSONArray中的某个元素
案列：
{"name":"jack","age":18,"parents":[{"name":"oldjack","age":48},{"name":"jackmom","age":49}]}获取18
select get_json_object('{"name":"jack","age":18,"parents":
[{"name":"oldjack","age":48},{"name":"jackmom","age":48}]}','$.age')获取49
select get_json_object('{"name":"jack","age":18,"parents":
[{"name":"oldjack","age":48},{"name":"jackmom","age":49}]}','$.parents[1].age')

综合案列

select line
,get_json_object(line,'$.action') as action
,get_json_object(line,'$.ba') as ba
,get_json_object(line,'$.g') as gmaill
from    gmall.ods_start_log
where dt='2021-03-15' limit 10 ; 
执行结果：

 

 

 lateral view 的使用

描述

lateral view用于和split, explode等UDTF一起使用，它能够将一行数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。lateral view首先为原始表的每行调用UDTF，UTDF会把一行拆分成一或者多行，lateral view再把结果组合，产生一个支持别名表的虚拟表。

例子

假设我们有一张表pageAds，它有两列数据，第一列是pageid string，第二列是adid_list，即用逗号分隔的广告ID集合：

create table pageAds 
as select   'front_page' as pageid,array(1,2,3) as adid_list  
union all 
select   'contact_page' as pageid,array(3,4,5) as adid_list
-----表里的数据如下：两行数据

pageid      adid_list

front_page    [1,2,3]

contact_page    [3,4,5]

要统计所有广告ID在所有页面中出现的次数。

首先分拆广告ID

select pageid,adid 
from pageAds
lateral view explode (adid_list) ad_table as adid
----执行结果如下：
pageid              adid 
front_page            1
front_page           2
front_page            3
contact_page           3
contact_page           4
contact_page              5

接下来就是一个聚合的统计

select adid ,count(1) as cn 
from pageAds
lateral view explode (adid_list) ad_table as adid
group by adid
----执行结果如下：
adid  cn 
1    1
2    1
3    2
4    1
5    1

concat_ws和collect_set结合使用把列表用指定符号拼接

---创建表
create table temp_set_test
(  INT,
user_id  int,
version_code string,
version_name string
)
----插入测试数据
insert into temp_set_test values (11,11000,'aa','aa_alex'),(11,12000,'aa','aa_alex'),(11,13000,'cc','cc_alex')
insert into temp_set_test values (22,22000,'bb','bb_alex'),(22,23000,'cc','cc_alex'),(22,24000,'dd','dd_alex')
-----查看数据
 select *     
from temp_set_test
---执行结果如下：
mid_id  user_id   version_code   version_name
11          11000           aa             aa_alex
11          12000           aa             aa_alex
11          13000           cc             cc_alex
22          22000           bb             bb_alex
22          23000           cc             cc_alex
22          24000           dd             dd_alex


----把每个mid_id的结果集 转成一列 列表 
select mid_id
,collect_set(version_code) as result
from temp_set_test
group by mid_id
---执行结果如下：
mid_id   result
11          ["aa","cc"]
22          ["bb","cc","dd"]

----把每一行结果集 用符号（_）进行拼接  collect_list和collect_set的区别就是是否进行去重
select mid_id
,concat_ws('_',collect_list(version_code)  ) as result
from temp_set_test
group by mid_id
---执行结果如下：
mid_id  result
11         aa_aa_cc
22         bb_cc_dd

set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite TABLE gmall.dws_uv_detail_wk PARTITION(wk_dt)
SELECT 
mid_id, 
concat_ws('|',collect_set(user_id)) user_id,
concat_ws('|',collect_set(version_code)) version_code,
concat_ws('|',collect_set(version_name)) version_name, 
concat_ws('|',collect_set(lang)) lang, 
concat_ws('|',collect_set(source)) source,
concat_ws('|',collect_set(os)) os, 
concat_ws('|',collect_set(area)) area, 
concat_ws('|',collect_set(model)) model,
concat_ws('|',collect_set(brand)) brand,
concat_ws('|',collect_set(sdk_version)) sdk_version,
concat_ws('|',collect_set(gmail)) gmail,
concat_ws('|',collect_set(height_width)) height_width,
concat_ws('|',collect_set(app_time)) app_time,
concat_ws('|',collect_set(network)) network,
concat_ws('|',collect_set(lng)) lng, 
concat_ws('|',collect_set(lat)) lat,
date_sub(next_day('2020-02-14','mo'),7) monday_date,
date_sub(next_day('2020-02-14','mo'),1) sunday_date,
concat(date_sub(next_day('2020-02-14','mo'),7),'-',date_sub(next_day('2020-02-14','mo'),1))
FROM gmall.dws_uv_detail_day
where dt BETWEEN date_sub(next_day('2020-02-14','mo'),7)
and date_sub(next_day('2020-02-14','mo'),1) 
group by mid_id;

案列

 

 连续三天登陆

逻辑就是根据日期自增一天，给用户ID 也自增一列，日期和自增列相减的结果值一样的个数 表示连续登陆的天数

----准备测试数据
create table test_lianxu
(mid_id INT,
create_time string 

)
insert into test_lianxu values (1,'2021-03-01'),(1,'2021-03-03'),(1,'2021-03-04'),(1,'2021-03-05'),(1,'2021-03-08') 

insert into test_lianxu values (2,'2021-03-01'),(2,'2021-03-05'),(2,'2021-03-06'),(2,'2021-03-08'),(2,'2021-03-10') 
insert into test_lianxu values (4,'2021-03-01'),(4,'2021-03-05'),(4,'2021-03-06'),(4,'2021-03-09'),(4,'2021-03-18') 


insert into test_lianxu values (0,'2021-03-01'),(0,'2021-03-02'),(0,'2021-03-03'),(0,'2021-03-04'),(0,'2021-03-05'),(0,'2021-03-06')

测试数据准备

----子查询结果集大于等于3 表示3天连续登陆
select 
temp.mid_id,temp.date_diff
,count(1)
from (
------计算出明细
select 
mid_id
,create_time
,row_number() over(partition by mid_id order by create_time asc) as rn 
,date_sub(create_time,row_number() over(partition by mid_id order by create_time asc)) as date_diff  ---计算出日期和自增列的差值
from test_lianxu
) temp 
group by temp.mid_id,temp.date_diff
having count(1) >=3 
---查询结果如下：
0    2021-02-28    6
1    2021-03-01    3
3    2021-03-03    3

数仓开发红线

1. hera迁移必须完全做到任务名和数据插入表名完全一致
2. 线上数仓（不含Temp、test、APP）的动态分区表，禁用 insert overwrite table ${job_name} partition(dt),改用 insert into ${job_name} partition(dt)
3. temp,test库里,只放临时表和测试用的表,并可随时删除的
4. 禁止对带分区的直接表加列(万一要加,要先把数据移走备份,重建表再把数据插回)
5. hera迁移完成之后,收回直接登录主机操作权限(非数据平台管理员一律不能登录主机操作)
6. 数仓直接修改数据规范:禁止直接 INSERT 或 TRUNCATE,只要不是通过任务对数据的修改,都要走SQL审批
7. 数仓重跑任务: 本人只能重跑自己的任务,依赖本任务的下游的任务只能由数据平台管理员或具有平台管理员权限的人重跑:
a. 带分区的数据表重跑数据
A. 先手工备份原数据
B. 删除需重跑数据分区
C. 修复分区
D. 重跑任务
8.数仓开发,验数,试跑只能在test库,验数完成后,业务方发上线邮件(邮件发给业务方领导,大数据领导,数仓开发工程师)才能上线(上线后再修改,需同样发邮件,并且一周只响应一次修改)

如果不按规范执行,相关责任人的绩效将会降一个级别

数仓开发规范

1. 便于数据的统一管理和使用，达到见表识其义，且易于维护，制定此操作规范，此规范针对本部门开发人员，望共同遵守。

2. 分层规范
统一拉通层：
2.1 把DW层的数据做统一的清洗处理。去重、去噪、字典翻译、空值转化，日期格式化等操作。

2.1.1 DWD（明细层）:
和ODS粒度一致的明细数据，对数据进行去重，脏数据过滤和砍字段处理，空处理，保证数据质量，简单逻辑通过视图实现，并解决数据的完整 度问题。

2.1.2 DWS（服务层）：
轻度汇总数据及集市大宽表(按主题)存放数据。

2.2 DIM:（ 维表层）：
通过ods层获取得到。

2.3 APP：（应用层）:
存放应用类表数据，如标签，各业务部门报表，第三方应用数据，按应用主题存放一般是业务部门，如销售、风控、运营、财务等。

3. 表规范
3.1 命名
3.1.1 维表 命名形式：dim_描述
3.1.2 事实表 命名形式：fact_描述_[AB]
3.1.3 临时表 命名形式：tmp_ 正式表名_ [C自定义序号]
3.1.4 桥接表 命名形式：map_主题_描述_[AB]
3.1.5 宽表 命名形式：dws_主题_描述_[AB]
3.1.6 备份表 命名形式：正式表名_bak_yyyymmdd

3.2 表命名解释：
3.2.1 表名使用英文小写字母，单词之间用下划线分开，长度不超过40个字符，命名一般控制在小于等于6级。
3.2.2 其中ABC第一位"A"时间粒度：使用"c"代表当前数据，"h"代表小时数据，"d"代表天数据，"w"代表周数据，"m"代表月数据，"q"代表季度数据， "y" 代表年数据。
3.2.3 其中ABC的第二位"B"表示对象属性，用"t"表示表，用"v"表示视图。
3.2.4 其中ABC的第三位"C"自定义序号用于标识多个临时表的跑数顺序。
3.2.5 目前主题缩写为：dev（设备）、user（用户）、log（日志）、book（记账）、bill（账单）、loan（贷款）、fin（理财）、card（卡片）、mkt（营销）。
3.2.6 桥接表主要用于一些多值维度，比如用户和设备（存在多对多关联）。

3.3 注释
注释要结合表的英文名，要求注释简洁明了，体现出表的业务出处、主题和用途。

3.4 表分区
在数仓中建立分区表统一用静态分区，一般建立分区表时，将ymd作为一级分区，在dws、dwd层可根据具体业务逻辑，确定使用一级分区，或多级分区。

3.5 存储格式
3.5.1 所谓的存储格式就是在Hive建表的时候指定的将表中的数据按照什么样子的存储方式，如果指定了方式，那么在向表中插入数据的时候，将会使用该方式 3.5.2 向HDFS中添加相应的数据类型。在数仓中建表默认用的都是PARQUET存储格式，相关语句如下所示：
stored as parquet TBLPROPERTIES ('parquet.compression'='SNAPPY')

3.6 字符集
Hadoop和hive 都是用utf-8编码的，在建表时可能涉及到中文乱码问题，所以导入的文件的字符编码统一为utf-8格式。

3.7 约定
3.7.1 类型
1) 所有的表都应该使用内部表
和ods场景不同，数仓数据来源于ods，统一使用内部表，location默认位置(无需额外指定)
2) 分区表必须包含yyyy-MM-dd字段
yyyy-MM-dd在维表层便于识别数据更新日期，服务层便于查询跑批数据。

3) 使用动态分区来插数据需要有相关设置
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

在进行宽表数据初始化或者其它场景时，可能会涉及到使用动态分区的情况，此时应该对hive进行一些参数的设置。

3.8 会话级临时表
3.8.1 在当前会话期间存在，会话结束后自动销毁。
create TEMPORARY table xyy_temp.tablename

3.8.2 会话级的临时表和temp库下的临时表使用场景不一样，temp库下的临时表主要用来放一些需要加工的中间数据，方便跑数时出现问题好回溯，而会话级的 3.8.3 临时表一般存放一些简单但数据量大的中间结果，且一般不会被查询使用，用来节省存储空间。

3.9 空值
理论上在数仓落地的表不应该出现null未知类型，对于可能出现null的字段，如果为字符型统一为空字符串，如果是数值则给0。

4. 字段规范
4.1 命名
4.1.1 使用英文小写字母，单词之间用下划线分开，长度不超过30个字符，命名一般控制在小于等于4级；
4.1.2 和源数据bdl层表字段名一致，如为新增字段，尽量言简意赅；
4.1.3 英文名尽量专业，符合业界要求，不得使用汉语拼音；
4.1.4 尽量避免使用关键字。如无法避免，使用”`”转义；
4.1.5 指标字段能使用缩写的尽量使用统一的缩写，如申请金额统计apply_amt_sum,放款笔数loan_cnt，平均合同金额avg_contract_amt等,日后会建立一个词根库，用来规范一些缩写名称。

4.2 注释
4.2.1 注释本着简洁、详实、完整的原则，对于有业务含义的字段，在注释中需要枚举并解释其业务含义，如odl_loan_apidata_order_info.order_status 订 4.2.2 单状态：3审核中，4申请被退回,41用户取消审核，5审核不通过，6审核通过；

4.3 类型
4.3.1 目前暂时不会涉及到map、array、struct等复杂的数据类型，日期时间等格式统一用string类型，字符串也是用string，数值的话，会根据字段定义来确定，对于有小数点要求的，比如某些金额、利率，需要用到decimal类型，无小数点要求的用浮点类型double和整数类型（int，bigint）。

5.代码规范
5.1 sql编码
5.1.1 关键字右对齐，代码注释详尽，查询字段时每行不超过三个字段，缩进时空四格等相关书写规范。
5.1.2 服务层依赖于ods层，应用层依赖于数仓层，原则上，不允许跨层查询。
5.1.3 相对独立的逻辑块间加空行。
5.1.4 如果SQL语句连接多表时，应使用表的别名来引用列。
5.1.5 WHERE条件中参数与参数值使用的类型应当匹配，避免进行隐式类型转化。
5.1.6 在SELECT语句中只获取实际需要的字段。

5.2 shell脚本: 不建议使用shell脚本
5.2.1 调度脚本主要是通过跑shell脚本，shell脚本的注意点：
5.2.2 命名与所跑的目标表名相同，注释要完善，后缀以.sh结尾。
5.2.3 脚本头需要加上分割线、作者、日期、数据更新策略、目的、描述等信息。
5.2.4 脚本在末尾要给出脚本运行状态，并且需要同步脚本运行状态到门户。
5.2.5 脚本里环境、配置等公用信息必须通过配置文件parameter_config。