最近刚接触一个线上运行的数仓环境，是针对用户流量日志做点击量指标的多维度分析，维度表每天一个快照，经过数据统计分析发现有的维度表数据量很大，每天竟然有5亿多条的素材日志，并且这些维度数据是渐变维度，数据存储在亚马逊S3文件系统上面，严重浪费公司的存储成本，同时要是查询跨度一个周的数据则涉及到的维度数据就40亿条进行关联，这还不算其他维度的统计在内，个人观点，涉及到这些大维度数据的统计应该通过当前构建的数仓应该没有实际的应用价值。
多维设计的整体思路要简化和加速查询，不要求必须满足3NF(消除数据冗余，规范化程度越高，需要关联的表也越多)，也就是说维度模型允许可控的数据冗余(数据更新异常，由于数据仓库中的数据很少有更新，主要是查询操作)，减少表和表间关系的数量，从而提高查询速度；例如，假设有100万订单，每个订单有10条明细，订单状态和订单明细状态各有10种，如果用户要查询某种状态特性的订单(状态是where过滤条件-->维度，即看问题的角度)，按3NF模型，逻辑上需要关联100万与1000万的两个大表，然后过滤两个表的状态值得到所要的结果，其中100万是订单粒度，1000万是订单明细粒度；另一方面，事实表按最细数据粒度有1000万记录(订单明细)，3NF里的订单表属性在事实表里是冗余数据，状态维度有100条数据(10条订单状态 * 10条订单明细状态)，只需要关联1000万与100的两个表，再进行状态过滤即可。
通过对现存数仓的调研提出下面的解决方案。
对维度表的处理：一、为提高查询性能，会优化表的存储，主要体现在1）存储类型采用压缩率比较高的ORC，降低公司存储成本，更重要的是该存储格式支持hive进行行级更新；（ ./hive –orcfiledump -j -p /hivedata/warehouse2/lxw1234_orc1/000000_0）2)通过水平拆分技术，采用分区+分桶，这些手段主要为减少扫描的数据量，分桶会根据下面的多维模型进行详细介绍；3)垂直拆分，因为有的表字段变化比较慢，有的表变化比较快，所以要对这些不同类型的字段区别对待，通过对各个维度表的数据统计分析，大部分字段变化的都很慢，凭借经验而谈，只有变化较慢的字段采用统计的价值，实际上90%以上的统计应该都落到变化较慢的字段上面，对应变化较慢的字段采用SCD2技术实现拉链表。二、为提供更丰富的查询类型，从事实表中抽取出维度数据，例如操作系统维度和设备品牌维度。
最终设计出的多维数据模型如下所示：
图 1
上面构建的多维数据模型中的维度表字段没有全列出来，因为公司因为所限，但是不影响技术的分享；经过对维度表的分析，主要有三张维度表的数据量特别大，对应的数据量如下表所示：
| 维度名称 | 总条数 | 时间跨度(天) | 平均每天的记录数 | 拉链表之后总条数 | 维度类型 | 备注 |
| dim_adn_creative_list | 123926985436 | 205 | 604521880 | 610904029 | 大维表 |
|
| dim_adn_creative_source | 38318221849 | 205 | 186918155 | 721969702 | 大维表 | utime(更新时间戳) 和status(状态)两个字段变化相对较大 |
| dim_adn_campain_list | 23392624691 | 205 | 114110364 |
| 大维表 | 已经定位到RDS数据库中个别字段中由\n导致，还没有做SCD2拉链表 |
| dim_adn_advertiser_list | 180283 | 197 | 915 | 943 | 小维表 |
|
dim_adn_campaign_list每天产生上亿量级记录,如图2所示：
图2
在构建数仓的整个过程中有可能会出现的问题以及对应的解决方案(原则上要保证在查询数据时能够快速查询)：
1.有的维度字段数据变化比较慢，尽管是采用SCD2技术，数据量依然较大，通过技术评估，分区技术不是太好解决，采用分桶技术；
2.有的维度表字段变化比较快，这种可以考虑采用分区技术，例如一个月或者一个季度一个分区，把夸分区的生效时间和失效时间在分区时间点进行切分；
3.数据量比较大的维度表不止一个，如果是一个分桶就创建在大维度表与事实表关联的字段上面，这样可以采用hive的优化机制，bucket_join来大大减少表关联的数据量(set hive.enforce.bucketing=true设置该参数(默认为false)，分桶才生效)；对这种多个维度表都比较大的，采用一种变通的方式(采用反向箭头)，让维度表的关联字段指向事实表的主键，而不是采用在事实表中打维度表的代理键；在事实表的主键上面创建分桶的关联字段，然后其他三个大维度表通过关联得到事实表上的主键来进行查询(对应的SQL如下)，在维度表上创建分桶，且分桶字段是指向事实表的主键字段，且维度表的分桶数量是事实表的倍数(因为事实表已经做过聚合操作，数据量大概在两千多万的量级）。
4.在广告维度进行开发过程中发现异常数据，把表的存储格式转换成text格式后，如何快速定位到这条异常数据所做的数据文件，通过在hiveSQL中添加虚列:1). INPUT__FILE__NAME显示map任务读入File的全路径;2). BLOCK__OFFSET__INSIDE__FILE，如果是RCFile或者是SequenceFile块压缩格式文件则显示Block file Offset，如果是TextFile，显示当前行的第一个字节在文件中的偏移量;3). ROW__OFFSET__INSIDE__BLOCK RCFile和SequenceFile显示row number, textfile显示为0，要显示ROW__OFFSET__INSIDE__BLOCK ，必须设置set hive.exec.rowoffset=true;
SQL样例：
fact_adn_tracking_click表有 两千万 的记录，把fact_adn_tracking_click 分发到所有的 map 上也是个不小的开销，而且 map join 不支持这么大的小表。如果用普通的 join，又会碰到数据倾斜的问题。
解决思路通过mapjoin仅仅查询出需要的字段进行：
insert overwrite table dim_adn_creative_list
select /*+mapjoin(c)*/c.id,d.*
from ( select distinct id,creative_id from fact_adn_tracking_click) c
join dim_adn_creative_list_snapshot d
on c.creative_id = d.creative_list_id
目标表建表语句：
CREATE EXTERNAL TABLE `dim_adn_creative_list`(
`fact_sk` int COMMENT 'surrogate key',
`creative_list_id` bigint,
`creative_name` string,
`user_id` bigint,
`campaign_id` bigint,
`type` int,
`lang` int,
`height` int,
`width` int,
`image` string,
`text` string,
`comment` string,
`stime` bigint,
`etime` bigint,
`status` int,
`timestamp` bigint,
`tag` int,
`version` int,
`effective_date` string,
`expiry_date` string)
CLUSTERED BY (
creative_list_sk)
INTO 10000 BUCKETS
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
's3://mob-emr-test/dataplatform/DataWareHouse/mobvista_dwh/tables/perm/dim_adn_creative_list';
---------------------------普通表，分桶普通表，分桶ORC格式------------------------------------------------------
CREATE EXTERNAL TABLE `dim_adn_creative_list_perfomance`(
`creative_list_sk` int,
`creative_list_id` bigint,
`creative_name` string,
`user_id` bigint,
`campaign_id` bigint,
`type` int,
`lang` int,
`height` int,
`width` int,
`image` string,
`text` string,
`comment` string,
`stime` bigint,
`etime` bigint,
`status` int,
`timestamp` bigint,
`tag` int,
`version` int,
`effective_date` string,
`expiry_date` string)
ROW FORMAT SERDE
'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://mob-emr-test/dataplatform/DataWareHouse/mobvista_dwh/tables/perm/dim_adn_creative_list_perfomance';
CREATE EXTERNAL TABLE `dim_adn_creative_list_perfomance_bucket`(
`creative_list_sk` int,
`creative_list_id` bigint,
`creative_name` string,
`user_id` bigint,
`campaign_id` bigint,
`type` int,
`lang` int,
`height` int,
`width` int,
`image` string,
`text` string,
`comment` string,
`stime` bigint,
`etime` bigint,
`status` int,
`timestamp` bigint,
`tag` int,
`version` int,
`effective_date` string,
`expiry_date` string)
CLUSTERED BY (
creative_list_id)
SORTED BY (
creative_list_id ASC)
INTO 50 BUCKETS
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://mob-emr-test/dataplatform/DataWareHouse/mobvista_dwh/tables/perm/dim_adn_creative_list_perfomance_bucket';
CREATE EXTERNAL TABLE `dim_adn_creative_list_orc_bucket`(
`creative_list_sk` int COMMENT 'surrogate key',
`creative_list_id` bigint,
`creative_name` string,
`user_id` bigint,
`campaign_id` bigint,
`type` int,
`lang` int,
`height` int,
`width` int,
`image` string,
`text` string,
`comment` string,
`stime` bigint,
`etime` bigint,
`status` int,
`timestamp` bigint,
`tag` int,
`version` int,
`effective_date` string,
`expiry_date` string)
CLUSTERED BY (
creative_list_sk)
INTO 50 BUCKETS
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
's3://mob-emr-test/dataplatform/DataWareHouse/mobvista_dwh/tables/perm/dim_adn_creative_list_orc_bucket';
641
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
