从暂存到仓库执行ETL的最佳策略

本文关键字：ETL 最佳 策略 执行 | 更新日期: 2023-09-27 18:14:30

我正在构建一个c#控制台应用程序，它将由SQL Server Agent在我们想要传输大量员工数据的场景中执行:

  1. 从一个SQL Server实例到另一个SQL Server实例，在两个不同网络上具有相同模式的两个雇员表之间。

  2. 在同一个SQL Server实例上的两个雇员表之间(没有网络延迟)，一个表是staging表，另一个表是仓库。

该进程是顺序的，因此ETL作业必须先传输#1，然后传输#2。

考虑到进程#1是跨网络的，而进程#2涉及在向目标表传输新数据之前删除目标表，我想知道为了获得最佳性能，应该采用什么最佳策略。

我是这么想的:

过程# 2: 

BEGIN TRANSACTION EmployeesInsert
    WITH MARK N'Transfering employees from Staging to Warehouse';
GO
USE CorporateWarehouse;
GO
DELETE FROM CorporateWarehouse.WarehouseEmployee
INSERT INTO CorporateWarehouse.WarehouseEmployee
            (FirstName,
             LastName,
             Address,
             PhoneNumber)
SELECT FirstName,
       LastName,
       Address,
       PhoneNumber
FROM CorporateWarehouse.StagingEmployee
GO
COMMIT TRANSACTION EmployeesInsert;
GO

从暂存到仓库执行ETL的最佳策略

在考虑了SSIS、BCP和链接服务器之后，我最终决定使用SqlBulkCopy类实现一个解决方案。从GetUserDataReader()方法获得的用户列表开始，该方法返回一个IDataReader类型的对象。

选择这个选项是因为UseInternalTransaction选项，您可以显式地使它在自己的事务中执行:

var usersDataReader = _warehouseRepository.GetUserDataReader();
var connectionString = ConfigurationManager.ConnectionStrings["CorporateWarehouse"].ToString();
using (var bulkCopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction | SqlBulkCopyOptions.TableLock))
{
    bulkCopy.BatchSize = extractInfo.BatchSize;
    bulkCopy.BulkCopyTimeout = extractInfo.BatchTimeout;
    bulkCopy.DestinationTableName = "StagingEmployee";
    try
    {
        bulkCopy.WriteToServer(usersDataReader);
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
    finally
    {
        usersDataReader.Close();
    }
}
相关文章：