当前位置:Gxlcms > mysql > 史上最简单的数据抽取

史上最简单的数据抽取

时间:2021-07-01 10:21:17 帮助过:33人阅读

史上最简单的数据抽取 做为一名全职DBA,在之前的工作中,常会收到这样的需求,需要我配合开发定时的从几张目标表取出他们需要的数据,并存放到临时表中,开发们再从临时表中取出数据展现给前端页面。 收到这样的需求,我觉得应该考虑以下几点: (1)目标表有

史上最简单的数据抽取

做为一名全职DBA,在之前的工作中,常会收到这样的需求,需要我配合开发定时的从几张目标表取出他们需要的数据,并存放到临时表中,开发们再从临时表中取出数据展现给前端页面。

收到这样的需求,我觉得应该考虑以下几点:

(1)目标表有哪几张,它们的关联关系如何,这决定了我如何取数据

(2)定时抽取,意味着要周期性的提取数据,此抽取周期是多少?每次抽取数据的频率是多久?

(3)周期性的抽取数据,那么临时表的命名要有规则,通过就是"临时表名_日期时间",这样命名方便开发前端取数

(4)周期性的抽取数据,那么临时表的保留及清理也要考虑一下,防止表空间使用率过高

(5)如果每一次抽取数据消耗的时间比较长,那么要有1个监控的手段,方便查看本次抽取数据的进度

(6)数据抽取的过程中,如果本次抽取数据时发现有问题,那么你抽数的存储过程要可以复用。

也就是说,当前你第2次执行抽数的存储过程时,本次操作之前抽取出的脏数据要清空掉。

那么什么是数据抽取?

最简单的解释就是,从原始数据中抽取出感兴趣数据的过程。

针对上面我总结出的6点,咱们开始模拟一套最简单的数据抽取案例。

(一)、表结构及字段说明

wKiom1WOSSaSw7E1AAKe9uJmSkA271.jpg

wKioL1WOSubDwyk4AAN2ODh7XpU924.jpg

(二)、模拟业务需求

(1)、从emp和dept表中抽取出:emp.empno、emp.ename、emp.job、emp.deptno、dept.dname、dept.loc、sysdate字段,构造产生临时表:T_EMP_DEPT
(2)、emp和dept表的关联关系:emp表的deptno字段  参照引用  dept表的deptno字段
(3)、开发人员每天13点会查询使用当天产生的临时表
(4)、每次产生的临时表,保留周期是30天,超过30天的临时表可以被清理掉

(三)、给出解决方案

(1)、从emp和dept表关联查询出需要的字段,关联字段是deptno,并创建临时表:T_EMP_DEPT
(2)、每天抽取一次,我们在每天13点之前把临时表创建好就可以了
(3)、临时表的命名规则:T_EMP_DEPT_yyyymmdd(取当前系统的年月日)
(4)、抽取数据的存储过程中,加入逻辑判断,取当前系统时间,并将30天前的临时表清除(先truncate,再drop)
(5)、单独写1个存储过程及表,用来保存每次抽取数据的进度情况,方便我们监控抽取数据的进度
(6)、在抽取数据的存储过程中,每次都要先truncate临时表、drop临时表,然后再进行本次的数据抽取。实现数据抽取的复用

既然全想清楚了,那么我们就开始操练起来吧

1、创建日志表(存储抽取进度)

wKioL1WOS2GjvszPAAUrB8R9cvE643.jpg

2、创建监控进度的存储过程

wKiom1WOSdfCkIcaAASKc1-toA4015.jpg

3、创建抽取数据的存储过程

CREATE OR REPLACE PROCEDURE prc_emp_dept authid current_user is
  table_name_1 varchar2(100); --临时表名
  table_flag   number;        --标识临时表是否存在 0:不存在 1:存在  
  create_sql   varchar2(5000);--创建临时表的SQL语句 
  insert_sql   varchar2(5000);--Insert操作SQL语句 
  date_30  varchar2(20); --数据过期的时间 30天以前
  date_cur varchar2(20); --当前日期
  log_detail varchar2(4000); --日志明细参数
  
begin
  date_cur   := to_char(sysdate, 'yyyymmdd'); --当前日期
  date_30    := to_char(sysdate - 30, 'yyyymmdd'); --30天以前的日期
  table_flag   := 0; --初始状态0,目标不存在
  table_name_1 := 'T_EMP_DEPT'||date_cur;
  ---如果临时表存在先清空、再干掉(实现功能复用)
  execute immediate 'select count(*) from user_tables 
  where table_name='''||table_name_1|| '''' into table_flag;
  
  if table_flag = 1 then
  
    execute immediate 'truncate table '||table_name_1;
    execute immediate 'drop table '||table_name_1;  
      
   --日志明细信息
  log_detail :=  '删除临时表的时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
   
   P_INSERT_LOG(sysdate,'prc_emp_dept','00',log_detail);
      
  end if;
  
  ---创建中间表T_EMP_DEPTyyyymmdd
 create_sql :='create table '|| table_name_1 || ' nologging as  
 select 
e.EMPNO   ,
e.ENAME   ,
e.JOB     ,
e.MGR     ,
e.HIREDATE,
e.SAL     ,
e.COMM    ,
e.DEPTNO  ,
d.DNAME   ,
d.LOC     ,
sysdate as current_time 
 from emp e,dept d where e.deptno=d.deptno';
  execute immediate create_sql;
  
     --日志明细信息
  log_detail := '中间表创建完毕时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
  
     P_INSERT_LOG(sysdate,'prc_emp_dept','01',log_detail);
  
 insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;  
  execute immediate insert_sql;   
commit;
   --日志明细信息
  log_detail := '第1次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
                
    P_INSERT_LOG(sysdate,'prc_emp_dept','02',log_detail);
                   
 insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;  
  execute immediate insert_sql;   
commit;
   --日志明细信息
  log_detail :=  '第2次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
    P_INSERT_LOG(sysdate,'prc_emp_dept','03',log_detail);
 insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;  
  execute immediate insert_sql;   
commit;
   --日志明细信息
  log_detail := '第3次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
                
    P_INSERT_LOG(sysdate,'prc_emp_dept','04',log_detail);
 insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;  
  execute immediate insert_sql;   
commit;
   --日志明细信息
  log_detail := '第4次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
    P_INSERT_LOG(sysdate,'prc_emp_dept','05',log_detail);
 insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;  
  execute immediate insert_sql;   
commit;
   --日志明细信息
  log_detail := '第5次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
    P_INSERT_LOG(sysdate,'prc_emp_dept','06',log_detail);
 insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;  
  execute immediate insert_sql;   
commit;
   --日志明细信息
  log_detail := '第6次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
    P_INSERT_LOG(sysdate,'prc_emp_dept','07',log_detail);
 insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;  
  execute immediate insert_sql;   
commit;
   --日志明细信息
  log_detail := '第7次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
    P_INSERT_LOG(sysdate,'prc_emp_dept','08',log_detail);
 insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;  
  execute immediate insert_sql;   
commit;
   --日志明细信息
  log_detail := '第8次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
    P_INSERT_LOG(sysdate,'prc_emp_dept','09',log_detail);
 insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;  
  execute immediate insert_sql;   
commit;
   --日志明细信息
  log_detail := '第9次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
    P_INSERT_LOG(sysdate,'prc_emp_dept','10',log_detail);
 insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;  
  execute immediate insert_sql;   
commit;
   --日志明细信息
  log_detail := '第10次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
    P_INSERT_LOG(sysdate,'prc_emp_dept','11',log_detail);
 insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;  
  execute immediate insert_sql;   
commit;
   --日志明细信息
  log_detail := '第11次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
    P_INSERT_LOG(sysdate,'prc_emp_dept','12',log_detail);
 insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;  
  execute immediate insert_sql;   
commit;
   --日志明细信息
  log_detail := '第12次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
    P_INSERT_LOG(sysdate,'prc_emp_dept','13',log_detail);
 insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;  
  execute immediate insert_sql;   
commit;
   --日志明细信息
  log_detail := '第13次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
    P_INSERT_LOG(sysdate,'prc_emp_dept','14',log_detail);
 insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;  
  execute immediate insert_sql;   
commit;
   --日志明细信息
  log_detail := '第14次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
    P_INSERT_LOG(sysdate,'prc_emp_dept','15',log_detail);
 
 insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;  
  execute immediate insert_sql;   
commit; 
   --日志明细信息
  log_detail := '第15次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
    P_INSERT_LOG(sysdate,'prc_emp_dept','16',log_detail);
 insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;  
  execute immediate insert_sql;   
commit;
   --日志明细信息
  log_detail := '第16次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
    P_INSERT_LOG(sysdate,'prc_emp_dept','17',log_detail);
 insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;  
  execute immediate insert_sql;   
commit;
   --日志明细信息
  log_detail := '第17次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
    P_INSERT_LOG(sysdate,'prc_emp_dept','18',log_detail);
 
 insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;  
  execute immediate insert_sql;   
commit; 
   --日志明细信息
  log_detail := '第18次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
    P_INSERT_LOG(sysdate,'prc_emp_dept','19',log_detail);
    
 insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;  
  execute immediate insert_sql;   
commit; 
   --日志明细信息
  log_detail := '第19次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
    P_INSERT_LOG(sysdate,'prc_emp_dept','20',log_detail);
    
 insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;  
  execute immediate insert_sql;   
commit; 
   --日志明细信息
  log_detail := '第20次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
    P_INSERT_LOG(sysdate,'prc_emp_dept','21',log_detail);        
    P_INSERT_LOG(sysdate,'prc_emp_dept','22','抽取数据部分结束了!');
    
  ---删除30天以前的临时表
  execute immediate 'select count(*) from user_TABLES 
  where table_name=''T_EMP_DEPT'||date_30 || '''' into table_flag;
  
  if table_flag = 1 then     --找到了30天前的表
    execute immediate ' truncate table T_EMP_DEPT' ||date_30;
    execute immediate ' drop table T_EMP_DEPT' ||date_30;
    
    log_detail := '删除30天前临时表完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
    P_INSERT_LOG(sysdate,'prc_emp_dept','23',log_detail);  
  end if;   
    
end prc_emp_dept;
/

4、使用sys用户显示授权给scott,防止dbms_job调用存储过程时报没有权限

wKioL1WOTAKRpoGZAAHXpVeEOVM888.jpg

5、查看监控日志表(新表什么也没有)

wKiom1WOSmbw-b2JAAJxMjK9lKg042.jpg

6、手工执行抽取数据的存储过程

wKiom1WOSpHg2MX3AAJZtn0JhQs370.jpg

7、查看监控日志表(记录得比较详细)

wKioL1WOTGviF8ReABDY6ps0lqU349.jpg

朋友们,咱们的数据抽取功能、监控抽取进度、业务的需求咱们基本就全完成了。

现在还差1项就是把抽取数据的存储过程设置成定时任务,然后周期性的执行。

常用的定时任务有2种:

a.crontab (操作系统层面的)

b.dbms_job (oracle自带的)

将咱们的数据抽取存储过程加入到定时任务,让它自己周期性的执行就可以了。

8、我使用的是dbms_job,详细如下图

wKiom1WOSySDpeRVAAVlYlJyX0c416.jpg

好了,朋友们至此,本套史上最简单的数据自动抽取文章就结束了!

此篇文章中涵盖了以下知识点:

1、SQL多表联合查询

2、关于业务的分析及思考

3、数据的自动抽取

4、数据抽取的进度监控

5、定时任务

6、表的管理

结束语:

相信大家已经发现了,本次文章中使用到的操作用户是scott,咱们oracle中基础练习的用户。

本篇文章是我上课的一个小案例,我的学生们反应不错,他们说接受起来比较容易。

所以我就把这个小案例移植到了51的博客上,分享给更多需要的朋友们!

其实每个人都有拖延症,都会害怕去尝试新鲜事物,所以我想说本篇文章:

送给想做数据抽取的朋友们、

送给对数据处理感兴趣的朋友们、

送给想学习oracle开发方向的朋友们、

送给想和做不同步的朋友们、

送给想学习的朋友们

附:本篇文章中的代码,全部手工测试过没有问题。如果朋友们在操作过程中发现报错,请好好检查一下代码。

人气教程排行