数仓--DW--Hadoop数仓实践Case-01-模型设计及数据准备

本文是对Hadoop构建数据仓库实践中订单销售系统案例的学习与总结

  • 组件架构：Hadoop2.7.2、Hive2.3.4、Sqoop1.4.6、Mysql5.7、Centos6.8，（组件均为Apache原生版，VMware虚拟机搭建集群）
  • 本文按照Kimball数仓思想，按照维度建模思想，对销售订单系统进行数据仓库建模总结。采用星型结构模型。
  • 案例来源为 《Haoop构建数据仓库实践》，进行自由发挥和思考。

系列文章

数仓--DW--Hadoop数仓实践Case-01-模型设计及数据准备
数仓--DW--Hadoop数仓实践Case-02-数据抽取
数仓--DW--Hadoop数仓实践Case-03-数据初始化装载
数仓--DW--Hadoop数仓实践Case-04-数据定期装载
数仓--DW--Hadoop数仓实践Case-05-维度表增加列
数仓--DW--Hadoop数仓实践Case-06-维度子集

数仓模型设计

  • 数据仓库分层
    • ods：原始日志
    • source:维度表
    • dw:事实表
    • test：临时数据及测试数据存放
  • 按照Kimball数仓架构思想，进行数据仓库建模"四步走"：
      1. 选择业务过程；
    • 2.声明粒度（业务粒度/维度粒度）；
    • 3.确认维度；
    • 4.确认事实。

构建数仓总线架构

  • 1.选择业务过程；
    • 销售订单
  • 2.声明粒度；
    • ETL处理时间周期为每天一次，事实表中存储最细粒度的订单事务记录
  • 3.确认维度
    • 维度为日期维度、产品维度、顾客维度、订单维度(特意设计，后期可做退化维度处理)
  • 4.确认事实
    • 销售订单是作为事实表

实现步骤：ER模型-->>总线矩阵-->>高层气泡模型-->>维度建模
订单系统ER关系模型如下：


销售订单源ER模型.PNG

总结出数仓的总线矩阵,如下图所示：


数据仓库总线矩阵.PNG

维度模型设计

  • 根据总线架构和ER关系模型，转换到维度模型
    气泡图如下所示（维度中字段参考下文）：


    dw_气泡图.PNG
日期维度

  • 日期维度，通过Java、Python、Mysql、Shell脚本直接生成即可；
    表关系如下：


    source_date_dim.PNG
订单维度

表关系如下：


source_order_dim.PNG
产品维度

产品维度表关系如下：


source_product_dim.PNG
顾客维度

表关系如下：


source_consumer_dim.PNG

维度建模总结

  • 在根据总线架构矩阵进行维度建模的时候，需要根据业务进行指导，在设计维度表的时候，需要对字段、名称、SCD类型、源表等进行一一的定义。这样，在进行数据建表的时候会节省很多精力。
  • 采用理论指导实践的原则和指导思想，对Kimball数仓思想进行理解和实践总结。

测试数据初始化

Mysql数据准备

  • consumer 表
-- 建表语句
CREATE TABLE `sale_order` (
  `order_number` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单号，主键',
  `consumer_number` int(11) DEFAULT NULL COMMENT '客户编号',
  `product_code` int(11) DEFAULT NULL COMMENT '产品编码',
  `order_date` datetime DEFAULT NULL COMMENT '订单时间',
  `entry_date` datetime DEFAULT NULL COMMENT '登记时间',
  `order_mount` decimal(10,2) DEFAULT NULL COMMENT '销售金额',
  PRIMARY KEY (`order_number`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1;

数据内容：


mysql_consumer_table.PNG
  • 其中，6-consumer_street_address去掉update为原始数据，7-consumer_name 字段去掉update为原始数据，添加update为下文测试所用。
  • product表
-- 建表语句
CREATE TABLE `product` (
  `product_code` int(11) NOT NULL AUTO_INCREMENT COMMENT '产品编码，主键',
  `product_name` varchar(50) DEFAULT NULL COMMENT '产品名称',
  `product_category` varchar(50) DEFAULT NULL COMMENT '产品分类',
  PRIMARY KEY (`product_code`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

数据准备：


mysql_product_table.PNG
  • 其中3-product_name的原始值为mi，redmi为修改后的值
  • sale_order表
-- 建表语句
CREATE TABLE `sale_order` (
  `order_number` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单号，主键',
  `consumer_number` int(11) DEFAULT NULL COMMENT '客户编号',
  `product_code` int(11) DEFAULT NULL COMMENT '产品编码',
  `order_date` datetime DEFAULT NULL COMMENT '订单时间',
  `entry_date` datetime DEFAULT NULL COMMENT '登记时间',
  `order_mount` decimal(10,2) DEFAULT NULL COMMENT '销售金额',
  PRIMARY KEY (`order_number`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1;

数据准备


msyql_sale_order.PNG

Hive数据准备

  • 在hive建表中，因为需要对维度表进行SCD处理，所以某些表需要创建为ORCFILE格式，需要对hive的配置文件进行修改。
    hive-site.xml 基本配置如下,必须配置条件为hive的事务操作和笛卡尔积操作。
<configuration>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://liyahui-02:3306/hive_2?createDatabaseIfNotExist=true&amp;characterEncoding=utf-8&amp;useSSL=false</value>
<description>JDBC connect string for a JDBC metastore</description>
</property>

<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
<description>Driver class name for a JDBC metastore</description>
</property>

<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
<description>username to use against metastore database</description>
</property>

<property>
        <name>javax.jdo.option.ConnectionPassword</name>
        <value>liyahui</value>
<description>password to use against metastore database</description>
</property>
<property>
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive2/warehouse</value>
<description>location of default database for the warehouse</description>
</property>
<property>
        <name>hive.cli.print.header</name>
        <value>true</value>
</property>

<property>
        <name>hive.cli.print.current.db</name>
<!-- 添加如下6个属性以支持事务 -->
<property>
<name>hive.support.concurrency</name>
<value>true</value>
</property>
<property>
<name>hive.exec.dynamic.partition.mode</name>
<value>nonstrict</value>
</property>
<property>
<name>hive.txn.manager</name>
<value>org.apache.hadoop.hive.ql.lockmgr.DbTxnManager</value>
</property>
<property>
<name>hive.compactor.initiator.on</name>
<value>true</value>
</property>
<property>
<name>hive.compactor.worker.threads</name>
<value>1</value>
</property>
<property>
<name>hive.enforce.bucketing</name>
<value>true</value>
</property>
<!--支持hive的笛卡尔积操作 -->
<property> 
          <name>hive.mapred.mode</name> 
          <value>nonstrict</value> 
          <description>The mode in which the hive operations are being performed. In strict mode, some risky queries are not allowed to
 run</description> 
</property>
<property> 
        <name>hive.strict.checks.cartesian.product</name> 
        <value>false</value>
</property>
</configuration>
  • hive建库建表语句
  • 文件命名为1-hive_dw_prepare.hql(包含所有的库和表)，采用hive -f 的方式执行。
-- ****************************************************
-- @Author:  LiYahui
-- @Date:  Created in  2019/04/13 10:32
-- @Description: TODO  初始化hive数据库和脚本
-- @Version: V1.0 
-- ****************************************************
-- 创建ods数据库
create database if not exists ods;
-- 创建客户表
create external table if not exists ods.ods_consumer(
customer_number int comment 'number',
customer_name varchar(30) comment 'name',
customer_street_address varchar(30) comment 'address',
customer_zip_code int comment 'zipcode',
customer_city varchar(30) comment 'city',
customer_state varchar(30) comment 'state'
)
row format delimited
fields terminated by '\t'
lines terminated by '\n'
NULL defined as ''
STORED AS textfile
location "/user/root/dw/ods/ods_consumer"
;

-- 创建产品表
create external table if not exists ods.ods_product(
product_code int comment 'code',
product_name varchar (30) comment 'name',
product_category varchar(30) comment 'category'
)
row format delimited
fields terminated by '\t'
lines terminated by '\n'
NULL defined as ''
STORED AS textfile
location "/user/root/dw/ods/ods_product"
;

-- 创建销售订单表
create external table if not exists ods.ods_sale_order(
order_number int comment 'order number',
customer_number int comment 'customer number',
product_code int comment 'product code',
order_date timestamp comment 'order date',
entry_date timestamp comment 'entry date',
order_amount decimal(10 , 2 ) comment 'order amount'
)
row format delimited
fields terminated by '\t'
lines terminated by '\n'
NULL defined as ''
STORED AS textfile
location "/user/root/dw/ods/ods_sale_order"
;

-- 创建source层数据库
create database if not exists source ;

-- 创建日期维度表
create external  table if not exists source.source_date_dim(
day_key string comment '日期代理键',
day_id date comment '标准日期格式',
day_of_month_id  tinyint comment '月份',
day_of_month_name string comment '月份名称',
day_of_qrt_id string comment '季度',
day_of_year_id smallint comment '年id'
) comment "日期维度表"
row format delimited fields terminated by ','
NULL defined as ''
stored as textfile
location "/user/root/dw/source/source_date_dim"
;

-- 创建客户维度表
create table if not exists source.source_consumer_dim(
consumer_key int  comment "代理键",
consumer_number varchar(50) comment "顾客编号",
consumer_name varchar(50) comment "顾客名称",
consumer_street_address varchar(50) comment "顾客地址",
consumer_zip_code varchar(50) comment "邮政编码",
consumer_city varchar(50) comment "城市",
consumer_province varchar(50) comment "省份",
consumer_valid_from date comment "有效期开始日期",
consumer_valid_to date comment "有效期结束日期",
consumer_indicator varchar(50) comment "状态指示器",
consumer_version int comment "顾客变化版本号"
)comment "客户维度表"
clustered by (consumer_key) into
8 buckets
stored as
orc tblproperties ('transactional'='true')
;

-- 创建产品维度
create table if not exists source.source_product_dim(
product_key string comment "代理键",
product_code string comment "商品编码",
product_name string comment "商品名称",
product_category string comment "商品分类",
product_valid_from date comment "有效期开始日期",
product_valid_to date comment "有效期结束日期",
product_indicator string comment "状态指示器",
product_version int  comment "产品变化版本号"
)comment "产品维度"
clustered by (product_key) into
8 buckets
stored as
orc tblproperties ('transactional'='true')
;

-- 创建订单维度表
create table if not exists source.source_order_dim(
order_key int comment "代理键",
order_code int comment "商品编码",
order_valid_from date comment "有效期开始日期",
order_valid_to date comment "有效期结束日期",
order_indicator string comment "状态指示器",
order_version int comment "订单变化版本号"
)comment "订单维度"
clustered by (order_key) into
8 buckets
stored as
orc tblproperties ('transactional'='true')
;

--创建dw层数据库
create database if not exists dw;

-- 创建订单事实表
create table if not exists dw.sale_order_fact(
order_sk int comment 'order surrogate key',
customer_sk int comment 'customer surrogate key',
product_sk int comment 'product surrogate key',
order_date_sk string comment 'date surrogate key',
order_amount decimal (10 , 2 ) comment'order amount'
)comment "销售订单事实表"
clustered by (order_sk) into 8 buckets
stored as
orc tblproperties ('transactional'='true')
;

总结步骤

数据抽取、初始化装载、定期装载、定时调度等详见下文。

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 216,651评论 6赞 501
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 92,468评论 3赞 392
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 162,931评论 0赞 353
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,218评论 1赞 292
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,234评论 6赞 388
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,198评论 1赞 299
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,084评论 3赞 418
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 38,926评论 0赞 274
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,341评论 1赞 311
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,563评论 2赞 333
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,731评论 1赞 348
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,430评论 5赞 343
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,036评论 3赞 326
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,676评论 0赞 22
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,829评论 1赞 269
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,743评论 2赞 368
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,629评论 2赞 354