“ 在大数据数仓加工过程中，关系型数据库中都存在着宽表加工的业务，那么数据宽表有什么优缺点，怎么设计宽表，什么场景下可以用。”
面试官问题点：
说一下数据宽表的优缺点吧？
你们公司宽表用起来感觉怎么样，遇到什么问题吗？
你们为什么要设计成宽表了？
01
—
宽表的定义
什么是宽表？
宽表，通常是指业务主体相关的指标、维度、属性关联在一起的一张数据库表。
为什么要使用宽表
在汇总数据层，加群指标的维度退化，采取更多的宽表化手段构建公 共指标数据层，提升公共指标的复用性，减少重复加工。
二 优点
提高查询性能
快速响应
方便使用，降低使用成本
提高用户满意度
三 缺点
由于把不同的内容都放在同一张表存储，宽表已经不符合三范式的模型设计规范，随之带来的主要坏处就是数据的大量冗余。方便查数据，但是数据整合多了就数据量大，运行就慢。
另外就是灵活性差，就比如说线上业务表结构变更，宽表模式改造量也比较大。
开发宽表为了避免宽表重复迭代，我们应该去了解业务全流程，得需要知道需扩展哪些维度，沉淀哪些指标，这样就流程会比较长，特别是有些业务快速迭代的话，就有点捉襟见肘。
02
—
关系型数据库中的宽表
关系型数据库做宽表的意义：
宽表对于性能提升
Oracle就没法显现优势，因为Oracle是要及时响应的，所以人们想到的一个办法，就是将事实表join维表，把属性字段加到事实表中，所以这个事实表就变宽了。数据库有的是最多256个字段，而宽表可能有上千个字段，尤其在电信行业中，这个宽表会特别常见。
宽表对于业务作用
当一张事实表关联多个维表的时候，7,8张，10几张的时候，业务自己使用表的查询很麻烦，成网状结构，比较难搞对关联，会把数据库跑死。
02
—
数据仓库中的宽表
数仓加工宽表，在数仓中一般是在哪一层加工？
DWS这一层，一般是事实表和多个维表相关联，加工成轻度汇总表；或者处理成基础标签表；
其它的层也会有，比如ODS层
怎么处理成一个宽表？案例？
基本知识：
对于银行的业务：
一个事实数据表都要和一个或多个维度表相关联。
用户在利用事实数据表创建多维数据集时，可以使用一个或多个维度表。
举例：银行对存款记账，
A表中存放实际数据，包括账号、所属机构号、存款金额等，
B表存放机构号和机构名称的对应关系。
则A是事实表，B是维表。
度量值，是跟维度放在一起说的，维度，维度的度量值。
维度和指标值加起来的才叫宽表。
通常是指业务主题相关的指标、维度、属性关联在一起的一张数据库表。
案例：数据仓库 DWS层之用户行为宽表
为什么需要用户行为宽表？把每个用户单日的行为聚合起来组成一张多列宽表，以便之后关联用户维度信息后，进行不同角度的统计分析。
数据来源：DWD层相关的业务数据表
创建用户行为宽表：
这张宽表整合了下单、支付和评论3种行为。
整合思路：
三个表用with as group by 用户id 后分别求各个表的聚合值，形成三个临时表，
之后将三个表union all之后，group by 用户id ,求整合后的指标值
drop table if exists dws_user_action;
create external table dws_user_action
(
user_id string comment '用户 id',
order_count bigint comment '下单次数 ',
order_amount decimal(16,2) comment '下单金额 ',
payment_count bigint comment '支付次数',
payment_amount decimal(16,2) comment '支付金额 ',
comment_count bigint comment '评论次数'
) COMMENT '每日用户行为宽表'
PARTITIONED BY (`dt` string)
stored as parquet
location '/warehouse/gmall/dws/dws_user_action/'
tblproperties ("parquet.compression"="snappy");数据导入脚本：
with as基本语法为如下，作用是定义一个临时表，可以在后续的语句中多次使用，提高sql可读性。注意多个临时表之间用逗号，而最后一个临时表和查询语句之间没有符号。
WITH t1 AS (
SELECT *
FROM carinfo
),
t2 AS (
SELECT *
FROM car_blacklist
)
SELECT *
FROM t1, t2#!/bin/bash
# 定义变量方便修改
APP=gmall
hive=/opt/module/hive/bin/hive
# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;then
do_date=$1
else
do_date=`date -d "-1 day" +%F`
fi
sql="
with
tmp_order as
(
select
user_id,
sum(oi.total_amount) order_amount,
count(*) order_count
from "$APP".dwd_order_info oi
where date_format(oi.create_time,'yyyy-MM-dd')='$do_date'
group by user_id
) ,
tmp_payment as
(
select
user_id,
sum(pi.total_amount) payment_amount,
count(*) payment_count
from "$APP".dwd_payment_info pi
where date_format(pi.payment_time,'yyyy-MM-dd')='$do_date'
group by user_id
),
tmp_comment as
(
select
user_id,
count(*) comment_count
from "$APP".dwd_comment_log c
where date_format(c.dt,'yyyy-MM-dd')='$do_date'
group by user_id
)
Insert overwrite table "$APP".dws_user_action partition(dt='$do_date')
select
user_actions.user_id,
sum(user_actions.order_count),
sum(user_actions.order_amount),
sum(user_actions.payment_count),
sum(user_actions.payment_amount),
sum(user_actions.comment_count)
from
(
select
user_id,
order_count,
order_amount,
0 payment_count,
0 payment_amount,
0 comment_count
from tmp_order
union all
select
user_id,
0,
0,
payment_count,
payment_amount,
0
from tmp_payment
union all
select
user_id,
0,
0,
0,
0,
comment_count
from tmp_comment
) user_actions
group by user_id;
"
$hive -e "$sql"案例二：02、订单宽表模型开发.sql
#***************************
#** 功能描述：订单宽表模型开发
#***************************
–订单主要信息表BDM层
create database if not exists bdm;
create external table if not exists bdm.itcast_bdm_order(
order_id string, --订单ID
order_no string, --订单号
order_date string, --订单日期
user_id string, --用户ID
user_name string, --登录名
order_money double, --订单金额
order_type string, --订单类型
order_status string, --订单状态
pay_status string, --支付状态
pay_type string, --支付方式 1、在线支付，2、货到付款
order_source string, --订单来源
update_time timestamp --订单更新时间
) partitioned by (dt string)
row format delimited fields terminated by ‘,’
lines terminated by ‘\n’
location ‘/business/itcast_bdm_order’;
alter table bdm.itcast_bdm_order add partition (dt=‘2017-01-01’) location ‘/business/itcast_bdm_order/dt=2017-01-01’;
hdfs dfs -put root/source_data/itcast_bdm_order.txt business/itcast_bdm_order/dt=2017-01-01/
–订单主要信息表FDM层
create database if not exists fdm;
create table if not exists fdm.itcast_fdm_order(
order_id string, --订单ID
order_no string, --订单号
order_date string, --订单日期
user_id string, --用户ID
user_name string, --登录名
order_money double, --订单金额
order_type string, --订单类型
order_status string, --订单状态
pay_status string, --支付状态
pay_type string, --支付方式 1、在线支付，2、货到付款
order_source string, --订单来源
update_time timestamp, --订单更新时间
dw_date timestamp
) partitioned by (dt string);
–加载数据
insert overwrite table fdm.itcast_fdm_order partition(dt=‘2017-01-01’)
select
t.order_id, --订单ID
t.order_no, --订单号
t.order_date, --订单日期
t.user_id, --用户ID
t.user_name, --登录名
t.order_money, --订单金额
t.order_type, --订单类型
t.order_status, --订单状态
t.pay_status, --支付状态
t.pay_type, --支付方式
t.order_source, --订单来源
t.update_time timestamp,–订单更新时间
from_unixtime(unix_timestamp()) dw_date
from bdm.itcast_bdm_order t where dt=‘2017-01-01’;
-------订单详细信息表BDM层----------------
create database if not exists bdm;
create external table if not exists bdm.itcast_bdm_order_desc(
order_id string, --订单ID
order_no string, --订单号
consignee string, --收货人姓名
area_id string, --收货人地址ID
area_name string, --地址ID对应的地址段
address string, --收货人地址
mobile string, --收货人手机号
telphone string, --收货人电话
coupon_id bigint, --使用代金券ID
coupon_money double, --使用代金券金额
carriage_money double, --运费
create_time timestamp, --创建时间
update_time timestamp, --更新时间
dw_date timestamp
)partitioned by (dt string)
row format delimited fields terminated by ‘,’
location ‘/business/itcast_bdm_order_desc’;
alter table bdm.itcast_bdm_order_desc add partition (dt=‘2017-01-01’) location ‘/business/itcast_bdm_order_desc/dt=2017-01-01’;
hdfs dfs -put root/source_data/itcast_bdm_order_desc.txt business/itcast_bdm_order_desc/dt=2017-01-01
-----订单详细信息表FDM层
create database if not exists fdm;
create table if not exists fdm.itcast_fdm_order_desc(
order_id string, --订单ID
order_no string, --订单号
consignee string, --收货人姓名
area_id string, --收货人地址ID
area_name string, --地址ID对应的地址段
address string, --收货人地址
mobile string, --收货人手机号
telphone string, --收货人电话
coupon_id bigint, --使用代金券ID
coupon_money double, --使用代金券金额
carriage_money double, --运费
create_time timestamp, --创建时间
update_time timestamp, --更新时间
dw_date timestamp
) partitioned by (dt string);
------加载数据
insert overwrite table fdm.itcast_fdm_order_desc partition(dt=‘2017-01-01’)
select
t.order_id, --订单ID
t.order_no, --订单号
t.consignee, --收货人姓名
t.area_id, --收货人地址ID
t.area_name, --地址ID对应的地址段
t.address, --收货人地址
t.mobile, --收货人手机号
t.telphone, --收货人电话
t.coupon_id, --使用代金券ID
t.coupon_money, --使用代金券金额
t.carriage_money, --运费
t.create_time, --创建时间
t.update_time, --更新时间
from_unixtime(unix_timestamp()) dw_date
from bdm.itcast_bdm_order_desc t where dt=‘2017-01-01’;
--------订单宽表模型表GDM-------------------
----加载数据
insert overwrite table gdm.itcast_gdm_order partition(dt=‘2017-01-01’)
select
a.order_id, --订单ID
a.order_no, --订单号
a.order_date, --订单日期
a.user_id, --用户ID
a.user_name, --用户名
a.order_money, --订单金额
a.order_type, --订单类型
a.order_status, --订单状态
a.pay_status, --支付类型
a.pay_type, --支付方式
a.order_source, --订单来源
b.consignee, --收货人姓名
b.area_id, --收货人地址ID
b.area_name, --地址ID对应的地址段
b.address, --收货人地址
b.mobile, --收货人手机号
b.telphone, --收货人电话
b.coupon_id, --使用代金券ID
b.coupon_money, --使用代金券金额
b.carriage_money,–运费
b.create_time, --创建时间
b.update_time, --更新时间
from_unixtime(unix_timestamp()) dw_date
from (select * from fdm.itcast_fdm_order where dt=‘2017-01-01’) a
join (select * from fdm.itcast_fdm_order_desc where dt=‘2017-01-01’) b on a.order_id=b.order_id;
03
—
在设计数据表的时候，是一个宽表好，还是多个维度表好？
回答一：
数据仓库每张表的搭建，主要依赖于这个表在整个数据仓库中的作用和相关意义。首先要清楚这个表的存在是为了解决那些问题，什么角色使用，怎么保证使用者尽可能好的体验解决问题。从以上所提到的角度去看待问题，拆解以下几点因素：
拆表情况下多张数据表的查询SQL的编写难度有多大，是否会出现为了数据提取需要关联多张表，并且需要提前知道各个表之间的关联关系。如果使用这个数据的人员较多，每个人都需要先了解所需要多张表的关联关系，然后才进行数据查询，这样是不是维度沟通成本较高，查询体验下降，影响使用者的工作效率？
多表关联查询的使用频次有多高，将重复高频的事情简化，是不是更好？
查询体验上需要考虑多表关联之后的查询性能问题，如果一张表的内容过度，是否影响查询速度？
多表关联的合理性，不同的数据维度和内容与订单表关联，是不是会存在违背常理的坑存在。比如，数据字段的对应关系是一对一，还是多对多，是否会让使用者忽略查询数据时候的过滤限制条件。
数据的安全问题，每张数据表的安全范围不同，合并成同一张表是面临的是更大的权限开放。比如订单表可能仅需要让一部分人员知晓订单信息，并不想让他们知道供应商信息。
回答二：
结合我司的一些经验来说说哈，我司会将数据用于各种各样不同主题和纬度的报表,也会将数据用于数据挖掘做模型的，因此数据分成肯定是必要的，针对报表类的数据根据报表的不同反向划分出不同的纬度表,这种方式其实就是将mysql业务库的数据经过sql语句之后重新生成一张或者多张维度表,在这之中根据经验会抽取出一个经常用的字段作为公共字段放入公共层数据中,一些经常需要用到的度量值也会抽取到度量表中,那么一些非开发人员来看数据的时候只要在页面上简单写几个sql语句就可以统计出数据来,比如月销量,周销量,日销量这些。
若是机器学习模型的同学要数据的话,我们就只需要从维度表,度量表,事实表中抽取数据做成大宽表给他们了,由于模型做的比较少,对于大宽表的经验比较少,暂时只能来一个模型数据的需求,单独写sql语句去抽取。
0x02 总结
这个问题，从本质上来讲。想讨论是数据模型设计里面的规范化和反规范化的问题。
从规范化的角度来讲，数据仓库的设计者是希望越规范越好，因为这样会减少数据的冗余，而且也便于模型的扩展。从反规范化的角度来讲，数据仓库的使用者是希望使用越方便越好，他们并不太关系规范不规范冗余不冗余，只要用着方便就好。
这种情况在工作中是十分常见的，那么该怎样来解决它？下面有两个思路：
两种方式都存。虽然，这样看起来会占用更多的存储空间，但不失为一种合适的解决方案，因为宽表是通过别的表拼接而成的，因此宽表的存储周期是可以短一些。 只存多个维度表，通过视图来创建宽表。这种方式适合于宽表的查询次数较少的情况。比如在Hive中，宽表其实只是为了计算出来之后导入Es等系统中供其它系统查询，那么久没必要存储一份宽表，直接通过视图来封装就可以。
另外，数据仓库的设计，往往不能是以计算出几张表就结束了，我们更应该提供的是数据服务，让使用方都通过服务的方式来访问我们的数据，而不是简单地将表暴露出去。当我们以数据服务的方式提供数据的时候，不管是易用性还是安全性都更容易得到满足。
#我是媛姐，一枚有多年大数据经验的程序媛，打过螺丝搬过砖，关注数仓，关注分析。让我们得到关于数据体系更多的认知，愿你我走得更远！
