基于Oracle数据库的传统数仓搭建
先大概讲以下,是什么原因写这篇文章!
在这个大数据的时代,全国各地、各大大中型企业都陆续进入数据中台、数据仓库开发的过程中。
其中以深圳起步比较早,在逐步进入数据流建设的下一个阶段。当我们把所有的数据通道建设OK后,不难看出中国将进入真正的数据时代。
所以在本行业的自己,就简单写下最基本的数仓搭建流程,给想要往这个方向发展的大家一个引导!
文章目录
一、数仓架构
本次介绍的是最简单的数仓搭建流程。
该数仓主要分为三层:ODS--->DW---->DM三层。每一层在该数仓都起着不可或缺的作用。
数据流向
数据仓库流程:业务系统——>ODS(贴源层)——>DW(数仓层)——>DM(集市层)——>报表
(一)业务系统—>ODS层
该层主要实现从业务系统复制数据到我们的数仓中。
该过程分为以下几步:
第一步:我们先连接业务系统和ODS层数据库系统
第二步:在ODS层创建目标表
第三步:将数据从业务系统抽取到ODS层
该过程还可以称为ETL过程(ETL--Extract-Transform-Load)
将业务系统的数据抽取、转换、加载加载到目标端。
(二)ODS—>DW层
该层主要实现将抽取到数仓的数据进一步转换。
DW层涉及到的三个必须知道的名词:事实表、维度表、模型。
--模型:主要反映事实表和维度表之间的关系,常见的两种:星型模型、雪花模型
--事实表:表内主要包含各个维度表关联字段和我们关注的内容
--维度表:我们需要的数据信息
该层主要是实现对事实表和维度表内数据的填充。
一般通过编写存储过程的方式,数据同步的方式:增量同步和全量同步。
(三)DW—>DM层
该层主要实现对我们所关注的指标进行计算。
通过将DW层中我们关注的内容,输出到同一张宽表中,通过各种计算
方式,得出我们所需要的指标。
同时,通过FineReport实现报表的输出。
二、各层搭建Demo
(一)ODS层搭建
该层一般通过库与库之间的连接工具实现数据的ETL。
本文介绍下Kettle实现的过程,常见的还有datastage......
1.连接数据库
在Kettle中点击connect,依次连接业务系统数据库(Mysql)和数仓数据库(Oracle)
2.创建目标表
在我们的数仓数据库中建立目标表来接收业务系统中的数据。
--创建表的代码
-- 建表的相关操作
------------------ 创建表结构.sql
--1 创建表结构
CREATE TABLE ODS_SAL_0705();
--2 给表添加注释
COMMENT ON TABLE ODS_SAL_0705 IS 'TABLE_NAME' ;
COMMENT ON COLUMN ODS_SAL_0705 .EMPNO IS 'COLUMN_NAME' ;
--3 给表创建 主键 约束
ALTER TABLE ODS_SAL_0705 ADD CONSTRAINT PK_EMP_0615 PRIMARY KEY(EMPNO);
在创建目标表的时候,主要参考对象是业务系统中的业务表。
3.数据抽取
数据抽取的过程主要是通过Kettle工具,简单实现的。
***参考kettle使用教程:***Jmayday大佬
(二)DW层搭建
1.数仓模型设计
数仓模型的设计,一般使用**PowerDesigner**来实现。
模型包括很多,常见的是:星型模型、雪花模型。主要规范事实表和
维度表之间的关系。
--星型模型:事实表直接和所有维度表字段相连
--雪花模型:事实表和维度表之间存在间接连接
2.创建目标表
该层的目标表包括:事实表和维度表。
创建主要是确认以下几项内容:表内字段、字段类型、取数逻辑。
主要通过制作数据字典的方式,通过源表来确认所需的表内数据。
3.实现数据同步
数据的同步主要分为两种:全量同步、增量同步。
a.全量同步
--全量同步:
CREATE OR REPLACE PROCEDURE SP_EMP_BAK1
IS
/*全量同步的逻辑:
1 先删除目标表的数据
2 将源表的数据全量插入到目标表中 */
BEGIN
/* 1 先删除目标表的数据*/
-- 每次更新目标表之前,先清空目标表中的原有数据 ,这样可以支持重跑
DELETE FROM EMP_BAK1 WHERE 1 = 1 ; -- 如果没有这一步,那么表中有主键的话就会报 主键冲突/ 违反唯一约束 。
/*2 将源表的数据全量插入到目标表中*/
INSERT INTO EMP_BAK1(EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO,
LAST_UPDATE_DATE)
SELECT
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO,
SYSDATE
FROM EMP ;
COMMIT ; -- TCL事务控制语言 ,对 DML语句(增,删,改)的操作都需要做 TCL的操作
END SP_EMP_BAK1 ;
--3 开发完成以后 ,调用 并测试逻辑是否正确 。
BEGIN
SP_EMP_BAK1 ;
END ;
SELECT * FROM EMP_BAK1 ;
b.增量实现
1)游标实现
--增量同步:游标实现
CREATE OR REPLACE PROCEDURE SP_EMP_BAK1_CURSOR
IS
CURSOR C_EMP IS
SELECT * FROM EMP ;
/* 增量同步的逻辑:
1 先判断目标表中是否包含 源表中的数据 ,一般是通过 主键字段比较的。
2 如果源表的数据在目标表中 存在 ,那么就用 源表的数据 更新 目标表的数据 。
如果源表的数据在目标表中 不存在 ,,那么就将 源表的数据 直接插入到目标表中。*/
V_CT NUMBER(1) ;
BEGIN
FOR X IN C_EMP LOOP
/*1 先判断目标表中是否包含 源表中的数据 ,一般是通过 主键字段比较的。*/
SELECT COUNT(*) INTO V_CT FROM EMP_BAK1 WHERE EMPNO = X.EMPNO ;
/*2 如果源表的数据在目标表中 存在 ,那么就用 源表的数据 更新 目标表的数据 。
*/
IF V_CT = 1 THEN
UPDATE EMP_BAK1 M
SET -- 更新的字段中 不包括 比较字段 EMPNO ,这里不更新
M.ENAME = X.ENAME ,
M.JOB = X.JOB ,
M.MGR = X.MGR ,
M.HIREDATE = X.HIREDATE ,
M.SAL = X.SAL ,
M.COMM = X.COMM ,
M.DEPTNO = X.DEPTNO ,
M.LAST_UPDATE_DATE = SYSDATE
WHERE M.EMPNO = X.EMPNO ;
/* 如果源表的数据在目标表中 不存在 ,那么就将 源表的数据 直接插入到目标表中。 */
ELSIF V_CT = 0 THEN
INSERT INTO EMP_BAK1(EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO,
LAST_UPDATE_DATE)
VALUES(
X.EMPNO ,
X.ENAME ,
X.JOB ,
X.MGR ,
X.HIREDATE ,
X.SAL ,
X.COMM ,
X.DEPTNO ,
SYSDATE
);
END IF ;
END LOOP;
COMMIT ;
END SP_EMP_BAK1_CURSOR;
2) Merge实现
--增量同步:merge实现
CREATE OR REPLACE PROCEDURE SP_EMP_BAK1_MERGE
IS
/* 增量同步的逻辑:
1 先判断目标表中是否包含 源表中的数据 ,一般是通过 主键字段比较的。
2 如果源表的数据在目标表中 存在 ,那么就用 源表的数据 更新 目标表的数据 。
如果源表的数据在目标表中 不存在 ,,那么就将 源表的数据 直接插入到目标表中。*/
BEGIN
MERGE INTO EMP_BAK1 M -- 通过 MERGE 方法更新 EMP_BAK1 表
USING ( SELECT * FROM EMP ) X -- 使用 X 这个结果集
/*1 先判断目标表中是否包含 源表中的数据 ,一般是通过 主键字段比较的。 */
ON (M.EMPNO = X.EMPNO) -- 比较字段是2个表的主键字段 EMPNO
/*2 如果源表的数据在目标表中 存在 ,那么就用 源表的数据 更新 目标表的数据 。*/
WHEN MATCHED THEN
-- 这里 UPDATE SET 中间没有表名 ,因为要更新的目标表在上面 MERGE 后面已经指定了
UPDATE SET
M.ENAME = X.ENAME ,
M.JOB = X.JOB ,
M.MGR = X.MGR ,
M.HIREDATE = X.HIREDATE ,
M.SAL = X.SAL ,
M.COMM = X.COMM ,
M.DEPTNO = X.DEPTNO ,
M.LAST_UPDATE_DATE = SYSDATE
-- 这个后面不用写 WHERE 条件 ,同时也不能写 分号 ;
/* 如果源表的数据在目标表中 不存在 ,,那么就将 源表的数据 直接插入到目标表中。 */
WHEN NOT MATCHED THEN
-- 这里的 INSERT 后面不跟表名,因为上面已经指定了。
INSERT (
M.EMPNO ,
M.ENAME ,
M.JOB ,
M.MGR ,
M.HIREDATE ,
M.SAL ,
M.COMM ,
M.DEPTNO ,
M.LAST_UPDATE_DATE
)
VALUES(
X.EMPNO ,
X.ENAME ,
X.JOB ,
X.MGR ,
X.HIREDATE ,
X.SAL ,
X.COMM ,
X.DEPTNO ,
SYSDATE
);
COMMIT ;
END SP_EMP_BAK1_MERGE ;
(三)DM层搭建
DM层主要是对我们关注的指标进行计算,
例如:常见的电商系统中,年/月/日销售额、销量等...
1. 建立目标宽表
该层数据输出方式分为:OLAP和OLTP两种方式。
OLTP常见报表软件:FineReport
OLAP常见报表软件:Tableau
2.计算指标
a.FineReport
当我们使用FineReport时,既可以在FineReport这个软件中直接写
sql取数,来实现指标的计算,也可以先在oracle编辑器中将指标计算
好然后直接在FineReport中取数就好了。
b.Tableau
OLAP类偏重于对历史数据进行分析,一般使用Tableau时,指标的
计算过程在Oracle中完成,输出数据到Tableau实现报表的绘制。