社区编辑申请
注册/登录
5000多张数据表,如何用SQL迁移到数据仓库?
数据库 SQL Server 数据仓库
需求背景最近公司打算集中梳理几大业务系统的数据,希望将各个业务系统中的数据集中到数据仓库中。总共有5000多张数据表,但是好在业务数据量没有像电商那么庞大,也就几十个G。

 需求背景

最近公司打算集中梳理几大业务系统的数据,希望将各个业务系统中的数据集中到数据仓库中。总共有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为例来查找数据库中的所有表。

 

  1. SELECT NAME FROM SYSOBJECTS WHERE TYPE='U' 

上面的代码就可以把当前库中的所有表的表名都给查询出来,我这里在家里电脑测试了一下,给大家看下截图:

 

大家也可以在自己的电脑上试一试就知道了。

Oracle获取用户表表名的代码如下:

 

  1. SELECT * FROM USER_TABLES; 

MySQL获取用户表表名的代码如下:

 

  1. select table_name 
  2. from information_schema.tables 
  3. where table_schema='db_name'

3、循环抽取数据

我们在完成上面两步后,就可以开始循环抽取各业务系统的数据了。这里我们需要写一个游标来循环执行。具体代码如下:

 

  1. DECLARE @TableName varchar(50),@Sql varchar(500) 
  2. --定义两个变量,一个用来存储表名,一个用来存储插入语句 
  3. DECLARE cursor_variable CURSOR FOR 
  4. --定义一个游标,并且将目标表的所有表名插入游标中 
  5. select name from [192.168.0.39].[test].[dbo].sysobjects where xtype='u' order by name
  6. OPEN cursor_variable 
  7. --打开游标 
  8. FETCH NEXT FROM cursor_variable 
  9. INTO @TableName 
  10. --获取游标中的数据插入到变量中 
  11. WHILE @@FETCH_STATUS=0 
  12. --循环执行,当游标中的数据被读完为止 
  13. BEGIN  
  14.    SET @Sql='select * into dbo.'+@TableName +' from [192.168.0.39].[test].[dbo].'+@TableName 
  15.    Exec @Sql 
  16. FETCH NEXT FROM cursor_variable 
  17. INTO @TableName 
  18. END 
  19. CLOSE cursor_variable 
  20. --关闭游标 
  21. DEALLOCATE cursor_variable; 
  22. --释放游标 

目前只是测试代码,后续在性能上还可以继续优化。

4、设置定时任务

代码写好了,肯定不可能每天手动去执行,这时候我们可以使用数据库的定时任务,这个我在以前的文章中有提到过。《数据库任务自动化其实很简单,JOB的简单介绍》

 

我们把代码放到定时任务里面,让它每天凌晨1点执行即可。

总结

这个办法在处理数据量不多的情况下是可行的,如果数据量较大,性能上会存在较大风险。下面我们回顾一下做了哪些内容:

1、创建不同数据库的DBLINK

2、查询到每个数据库的所有表名

3、使用游标循环插入到数据仓库

4、设置定时任务执行上面的游标

每个步骤都可能会存在问题,但是只要把这些问题都解决了,这件事就解决了。

觉得不错,记得转发分享给更多人~

责任编辑:华轩 来源: SQL数据库开发
相关推荐

2022-05-30 11:10:04

数据库MySQL系统

2022-06-30 18:17:00

数据集云数据建模计数据仓库

2022-05-24 09:52:37

Spark SQL大数据处理Hive

2012-03-05 10:06:40

云计算数据仓库数据迁移

2011-04-06 17:30:41

SQL ServerSQL

2011-03-15 17:36:24

SQL Server数据迁移云端

2022-06-29 18:12:26

Doris数据仓库

2022-06-29 09:43:14

SQL优化数据库

2022-06-29 10:16:25

数据库SQL

2022-06-13 08:00:00

数据湖数据仓库大数据

2022-06-24 09:38:43

数据库大数据

2022-06-18 09:26:00

Flink SQLJoin 操作

2011-04-01 16:12:04

SQL Server数

2022-05-11 09:02:27

Python数据库Excel

2021-01-28 09:00:00

SQL数据库NoSQL

2010-07-20 09:26:17

SQL Server

2022-06-28 22:13:33

Polars数据处理与分析

2011-08-25 18:09:36

SQL Server创建数据仓库已分区表

2022-05-22 10:02:32

CREATESQL 查询SQL DDL

2010-07-21 15:01:09

SQL Server

同话题下的热门内容

提高分层 SQL 结构的性能对国产数据库厂商提几个关于SQL引擎的小需求

编辑推荐

SQL Server 2016最值得关注的10大新特性SQL编程之高级查询及注意事项SQL Server性能调优方法论与常用工具【SQL Server 2016动态数据屏蔽入门】Azure SQL 数据库V12版的动态数据屏蔽【SQL Server 2016动态数据屏蔽入门】定义屏蔽
我收藏的内容
点赞
收藏

51CTO技术栈公众号