共计 1867 个字符,预计需要花费 5 分钟才能阅读完成。
本篇内容介绍了“怎么用 SQL 吧数据表迁移到数据仓库中”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!
需求背景
最近公司打算集中梳理几大业务系统的数据,希望将各个业务系统中的数据集中到数据仓库中。总共有 5000 多张数据表,但是好在业务数据量没有像电商那么庞大,也就几十个 G。
需求分析
其实这个需求很简单,就是把这 5000 多张不同数据库中的表放到一个地方。需要注意的有以下几点:
1、数据来自各种不同类型的数据库,有 SQL Server,MySQL 和 Oracle
2、表的数据量较多,一个一个写查询代码肯定不现实
3、后续数据仓库的维护
方案建议
由于数据量不是很大,我打算用 DBLINK 来实现从不同的库中抽取数据到数据仓库中。
方案思路
1、创建不同的 DBLINK
数据仓库我们目前使用的是 SQL Server 的服务器,整体性能还可以。但是业务系统的数据库类型不一,在新建 DBLINK 时有不同的要求:
a、针对 SQL Server 的业务数据库可以直接在服务器上新建
b、针对 MySQL 和 Oracle 的业务数据库需要先使用 ODBC 作用中间组件来配置。
2、查询数据库中的所有表表名
每个业务数据库都是全库抽取,那么首先需要找到这些数据库中的所有表。这里我们以 SQL Server 为例来查找数据库中的所有表。
SELECT NAME FROM SYSOBJECTS WHERE TYPE= U
上面的代码就可以把当前库中的所有表的表名都给查询出来,我这里在家里电脑测试了一下,给大家看下截图:
大家也可以在自己的电脑上试一试就知道了。
Oracle 获取用户表表名的代码如下:
SELECT * FROM USER_TABLES;
MySQL 获取用户表表名的代码如下:
select table_name from information_schema.tables where table_schema= db_name
3、循环抽取数据
我们在完成上面两步后,就可以开始循环抽取各业务系统的数据了。这里我们需要写一个游标来循环执行。具体代码如下:
DECLARE @TableName varchar(50),@Sql varchar(500) -- 定义两个变量,一个用来存储表名,一个用来存储插入语句 DECLARE cursor_variable CURSOR FOR -- 定义一个游标,并且将目标表的所有表名插入游标中 select name from [192.168.0.39].[test].[dbo].sysobjects where xtype= u order by name; OPEN cursor_variable -- 打开游标 FETCH NEXT FROM cursor_variable INTO @TableName -- 获取游标中的数据插入到变量中 WHILE @@FETCH_STATUS=0 -- 循环执行,当游标中的数据被读完为止 BEGIN SET @Sql= select * into dbo. +@TableName + from [192.168.0.39].[test].[dbo]. +@TableName Exec @Sql FETCH NEXT FROM cursor_variable INTO @TableName END CLOSE cursor_variable -- 关闭游标 DEALLOCATE cursor_variable; -- 释放游标
目前只是测试代码,后续在性能上还可以继续优化。
4、设置定时任务
代码写好了,肯定不可能每天手动去执行,这时候我们可以使用数据库的定时任务,这个我在以前的文章中有提到过。《数据库任务自动化其实很简单,JOB 的简单介绍》
我们把代码放到定时任务里面,让它每天凌晨 1 点执行即可。
总结
这个办法在处理数据量不多的情况下是可行的,如果数据量较大,性能上会存在较大风险。下面我们回顾一下做了哪些内容:
1、创建不同数据库的 DBLINK
2、查询到每个数据库的所有表名
3、使用游标循环插入到数据仓库
4、设置定时任务执行上面的游标
每个步骤都可能会存在问题,但是只要把这些问题都解决了,这件事就解决了。
觉得不错,记得转发分享给更多人~
“怎么用 SQL 吧数据表迁移到数据仓库中”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站,丸趣 TV 小编将为大家输出更多高质量的实用文章!