Oracle笔记 之 使用Oracle建立简单的数据分析数据库服务器

本文将介绍使用Oracle搭建简单的数据分析平台数据库。

设计思路

1，保障源数据库安全

数据分析平台数据库必须是独立的。

数据分析平台数据库与业务数据库之间通过 Databaselink 连接。

Databaselink 使用的用户必须是新建的专门用户且仅有connect权限。

通过Databaselink读取的数据必须经过DBA的授权(权限仅限于select)。

2，数据更新方式

要保证数据分析平台数据库和业务数据库的数据一致性。

数据尽量避免频繁的全量更新。

选择适当的数据同步方式。

对数据更新做适当的审计。

3，数据更新纪录

数据更新需要做好记录工作以便后期查看及查找问题。

4，保留可扩展

保障数据库的可扩展性与冗余。

实现步骤

创建数据库

本人使用的是 Oraclelinux7.8 搭配 Oracle12c，感觉比 Oraclelinux6.5 搭配 Oracle11g 性能要略好一些。

搭建过程可参考本人的另一篇博文。

如果有更合适的搭配请留言，谢谢。

创建Databaselink

1，请让业务系统数据库管理员为你先建一个专门的用户并赋权connect。

create user da identified by 123465;
grant connect to da;

2，请提供给业务系统数据库管理员你需要读取的数据表和同义词名对照表。

例如

维度表：

dbuser.shop==> shop

grant select on dbuser.shop to da;

create synonym da.shop for dbuser.shop;

事实表：

dbuser.wastebook ==> wastebook

grant select on dbuser.wastebook to da;

create synonym da.wastebook for dbuser.wastebook ;

3，使用分析数据库DBA创建Databaselink。

create database link test
connect to da
using '127.0.0.1:1521/test'

新建表结构

本人是采用更新与全量并用的方式，具体说明就是：

1，默认表采用更新方式，每日由存储过程自动比较更新。

有时间戳的按照时间戳更新，无时间戳选择比较更新(minus函数)。

有时间戳

create table dim_shop
(
id integer,
name varchar2(20),
note varchar2(200),
sdate date,
edate date
)

无时间戳

create table dim_goods
(
id integer,
name varchar2(20),
note varchar2(200)
)

2，加 fload 后缀表使用全量重载方式，每周由存储过程自动重载。

create table dim_shop_fload
(
id integer,
name varchar2(20),
note varchar2(200),
sdate date,
edate date
)

3，_fload 表重载后对相关表的统计信息做简单的对比，如果统计信息有差异则重载更新表。

4，执行日志

create table CTRL_RUNLOG
(
id INTEGER,
pkname VARCHAR2(50),
pcname VARCHAR2(50),
btime DATE,
etime DATE default sysdate,
msgcode INTEGER,
msgtext VARCHAR2(500)
)

5，同步信号

#暂时没用

create table CTRL_SIGNAL
(
tablename VARCHAR2(50),
updatetype INTEGER ,
lastsuccessdate DATE,
lastdate DATE,
nextdate DATE,
flag INTEGER default 1
)

6，简单校验

create table audit_simple
(
stable varchar2(50),
tablename varchar2(50),
flag integer,
adate date
)

编写存储过程

1，执行信号

procedure nextdate(p_tbname in varchar2,
p_uptype in integer,
p_flag in integer) is
pragma autonomous_transaction;
d_next date;
d_lastsuccessdate date;
begin
select trunc(sysdate, 'dd') + decode(p_uptype, 0, 7, 1, 1, null)
into d_next
from dual;
case p_flag
when 1 then
merge into ctrl_signal a
using (select p_tbname tablename, p_uptype uptype, p_flag flag
from dual) b
on (a.tablename = b.tablename and a.updatetype = b.uptype)
when matched then
update
set a.lastdate = sysdate,
a.nextdate = d_next,
a.flag = b.flag,
a.lastsuccessdate = trunc(sysdate, 'dd')
when not matched then
insert
(a.tablename,
a.updatetype,
a.lastdate,
a.nextdate,
a.flag,
a.lastsuccessdate)
values
(b.tablename,
b.uptype,
sysdate,
d_next,
b.flag,
trunc(sysdate, 'dd'));
when 0 then
merge into ctrl_signal a
using (select p_tbname tablename, p_uptype uptype, p_flag flag
from dual) b
on (a.tablename = b.tablename and a.updatetype = b.uptype)
when matched then
update
set a.lastdate = sysdate, a.nextdate = d_next, a.flag = b.flag
when not matched then
insert
(a.tablename, a.updatetype, a.lastdate, a.nextdate, a.flag)
values
(b.tablename, b.uptype, sysdate, d_next, b.flag);
end case;
commit;
end nextdate;

2，执行日志

–存储过程执行日志
procedure runlog(p_pkname in varchar2,
p_pcname in varchar2,
p_tbname in varchar2,
p_uptype in integer,
p_btime in date,
p_msgcode in integer,
p_msgtext in varchar2) is
pragma autonomous_transaction;
i_flag integer;
begin
insert into ctrl_runlog
(pkname, pcname, btime, msgcode, msgtext)
values
(p_pkname, p_pcname, p_btime, p_msgcode, p_msgtext);
commit;
select decode(p_msgcode, 0, 1, 0) into i_flag from dual;
nextdate(p_tbname => p_tbname,
p_uptype => p_uptype,
p_flag => i_flag);
end runlog;

3，数据校验

create or replace procedure audit_simple as
cursor cs_tables is
select distinct tablename from ctrl_signal where updatetype = 1;
sql_update varchar2(200);
sql_fullload varchar2(200);
rnums_update integer;
rnums_fullload integer;
flag integer := 0;
begin
for tablename in cs_tables loop
sql_update = '';
sql_fullload = '';
begin
sql_update = 'select count(0) from ' || tablename.tablename;
sql_fullload = 'select count(0) from ' || tablename.tablename || '_fload'; execute immediate sql_update returning into rnums_update; execute immediate sql_fullload returning into rnums_fullload; if rnums_update = rnums_fullload then flag := 1; end if; exception when others then flag := 0; end; insert into audit_simple(stable,tablename,flag,adate) values(tablename.tablename,tablename.tablename || '_fload',falg,sysdate)
end loop;
commit;
exception
when others then
rollback;
end audit_simple

4，有时间戳的update

-- 机构数据更新
-- 时间：2021年3月10日 16:04:45
----增量
----机构采用根据 sdate 字段增量更新
----取最后一次成功更新日期后的数据更新 update 表数据
procedure shop_update as
d_btime date := sysdate;
i_msgcode number := 0;
v_msgtext varchar2(200) := '机构更新成功';
d_lasttime date;
begin
--取机构表 update 的最后更新日期
begin
select trunc(lastsuccessdate, 'dd')
into d_lasttime
from ctrl_signal
where tablename = 'dim_shop'
and updatetype = 1;
exception
when no_data_found then
d_lasttime := to_date('20000101', 'yyyymmdd');
end;
merge into dim_shop a
using (select id, name, note, sdate, edate
from shop@test
where sdate >= d_lasttime) b
on (a.id = b.id)
when matched then
update
set a. name = b. name,
a. note = b. note,
a. sdate = b. sdate,
a. edate = b. edate
when not matched then
insert
(a. id, a. name, a. note, a. sdate, a. edate)
values
(b. id, b. name, b. note, b. sdate, b. edate);
commit;
pk16_common.runlog(p_pkname => 'test',
p_pcname => 'shop_update',
p_tbname => 'dim_shop',
p_uptype => 1,
p_btime => d_btime,
p_msgcode => i_msgcode,
p_msgtext => v_msgtext);
exception
when others then
rollback;
i_msgcode := sqlcode;
v_msgtext := substr(sqlerrm, 1, 500);
pk16_common.runlog(p_pkname => 'test',
p_pcname => 'shop_update',
p_tbname => 'dim_shop',
p_uptype => 1,
p_btime => d_btime,
p_msgcode => i_msgcode,
p_msgtext => v_msgtext);
end shop_update ;

5,无时间戳的update

-- 商品更新
----增量
-- 时间：2021年3月10日 15:10:01
----商品数据量少且没有创建时间字段，采用比较法更新数据
procedure goods_update as
d_btime date := sysdate;
i_msgcode number := 0;
v_msgtext varchar2(200) := '商品更新成功';
begin
merge into dim_goods a
using (select id, name,note
from goods@test
minus
select id, name,note
from dim_goods) b
on (a.id = b.id)
when matched then
update
set a.name = b.name,
a.note = b.note
when not matched then
insert
(a.id, a.name, a.note)
values
(b.id, b.name, b.note);
commit;
pk16_common.runlog(p_pkname => 'test',
p_pcname => 'goods_update',
p_tbname => 'dim_goods',
p_uptype => 1,
p_btime => d_btime,
p_msgcode => i_msgcode,
p_msgtext => v_msgtext);
exception
when others then
rollback;
i_msgcode := sqlcode;
v_msgtext := substr(sqlerrm, 1, 500);
pk16_common.runlog(p_pkname => 'test',
p_pcname => 'goods_update',
p_tbname => 'dim_goods',
p_uptype => 1,
p_btime => d_btime,
p_msgcode => i_msgcode,
p_msgtext => v_msgtext);
end goods_update;`

6，截取重载

---- 全量更新
---- 时间：2021年3月11日 08:43:57
---- 采用截断法更新机构
procedure shop_fullload as
d_btime date := sysdate;
i_msgcode number := 0;
v_msgtext varchar2(200) := '机构重载成功';
begin
execute immediate 'truncate table dim_shop_fload';
insert into dim_shop_fload(id, name,note,sdate,edate)
select id, name,note,sdate,edate from shop@test;
commit;
pk16_common.runlog(p_pkname => 'test',
p_pcname => 'shop_fullload',
p_tbname => 'dim_shop_fload',
p_uptype => 0,
p_btime => d_btime,
p_msgcode => i_msgcode,
p_msgtext => v_msgtext);
exception
when others then
rollback;
i_msgcode := sqlcode;
v_msgtext := substr(sqlerrm, 1, 500);
pk16_common.runlog(p_pkname => 'test',
p_pcname => 'shop_fullload',
p_tbname => 'dim_shop_fload',
p_uptype => 0,
p_btime => d_btime,
p_msgcode => i_msgcode,
p_msgtext => v_msgtext);
end shop_fullload;

设计自动执行

数据抽取应选择业务系统空闲窗口期，原则先执行update更新后执行fullload重载，重载完成后进行简单的数据检验。