mysql etl_从SQL Server到MySQL的ETL实现

最新推荐文章于 2024-04-03 22:08:36 发布

薛一山

最新推荐文章于 2024-04-03 22:08:36 发布

阅读量408

点赞数

文章标签： mysql etl

本文链接：https://blog.csdn.net/weixin_30769455/article/details/113120189

版权

本文介绍了一个从SQL Server到MySQL的ETL过程中遇到的问题，即使用ODBC连接加载数据速度慢。为了解决这个问题，提出了通过SQL Server的bcp命令将数据导出为文件，然后利用MySQL的LOAD DATA LOCAL INFILE命令快速导入的解决方案。测试结果显示，这种方法将原本43分钟的加载时间缩短到了10秒。

摘要由CSDN通过智能技术生成

场景：

需要一个从SQL Server源表拉数据到MySQL目标表的SSIS ETL Package，本来一个简单的Data Flow组件就可以解决的问题，然而SSIS 2014不支持在Data flow中使用ADO.NET connection作为MySQL desitination，运行时会报错(做source connection使用没有问题)，换成ODBC connection可以成功，但load速度太慢。

在以下测试表中插入260908条测试数据，然后用ODBC做Data Flow的destination，load所有数据耗时43分27秒！！

1 CREATE TABLEtblTest(2 ID BIGINT NOT NULL IDENTITY(1,1),3 Col1 NVARCHAR(30),4 Col2 NVARCHAR(30),5 Col3 NVARCHAR(30),6 Col4 NVARCHAR(30),7 Col5 NVARCHAR(30),8 Col6 DECIMAL(10,4),9 Col7 DECIMAL(10,4),10 Col8 DECIMAL(10,4),11 Col9 INT,12 Col10 DATETIME2 DEFAULTGETUTCDATE()13 )

解决方案：

将源表通过SQL Server的bcp命令导出为文件，然后在利用MySQL的LOAD DATA LOCAL INFILE命令导入目标表中。

1 --在SQL Server中创建SP如下，用以被SSIS Package调用，将源表导出至文件

2 CREATE PROCEDUREusp_ExtracttblTest3 AS

4 BEGIN

6 EXECsp_configure7 'show advanced options',8 1;9

10 RECONFIGURE;11

12 EXECsp_configure13 'xp_cmdshell',14 1;15

16 RECONFIGURE;17

18 DECLARE @cmd NVARCHAR(4000);19

20 SET @cmd = 'bcp "SELECT ID,Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10 FROM Test..tblTest" queryout C:/TEST/tblTest.csv -S"SQL Server IP" -U"DBUser" -P"DBUserPassword" -c -C"65001" -t","';21

22 EXEC master..xp_cmdshell @cmd;23

24 END

26 GO

SSIS Package需要两个Execute SQL Task来实现整个ETL：

Execute SQL Task 1: 调用以上存储过程，将源表导出为文件，导出260908条测试数据耗时1秒

Execute SQL Task 2: 用ADO.NET connection的方式连接到MySQL目标数据库，SQL Statement内容如下。导入所有测试数据耗时8秒

1 LOAD DATA LOCAL INFILE 'C:/Test/tblTest.csv'

2 INTO TABLEtblTest3 CHARACTER SETutf84 FIELDS TERMINATED BY ','

5 ESCAPED BY '\\'

6 LINES TERMINATED BY '\r\n'

7 (ID,Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10);

改进后的ETL Package跑下来耗时总和仅需10秒！