微软BI 之SSIS 系列 - Merge, Merge Join, Union All 合并组件的使用以及Sort 排序组件同步异步的问题

2022-05-29 14:08:02

开篇介绍

SSIS Data Flow 中有几个组件可以实现不同数据源的数据合并功能，比如 Merger, Merge Join 和 Union All。它们的功能比较类似，同时也比较容易混淆，下面是对它们之间的区别的对比总结。

下面通过三个 Data Flow 来演示这三个组件的使用以及相关的配置。

测试数据源 -

第一个数据源是一张表

USE BIWORK_SSIS

GO

-- Merge demo table

IF OBJECT_ID('DEMO_MG_Customer','U') IS NOT NULL

DROP TABLE DEMO_MG_Customer

GO

CREATE TABLE DEMO_MG_Customer

(

  CustomerID INT PRIMARY KEY,

  CustomerCompany NVARCHAR(255),

  CustomerName NVARCHAR(20),

  CustomerAddress NVARCHAR(255)

)

INSERT INTO DEMO_MG_Customer VALUES

(1,'HFBZG','Allen,Michael','Obere Str. 0123'),

(2,'MLTDN','Hassall, Mark','Avda. de la Constitución 5678'),

(3,'KBUDE','Peoples, John','Mataderos  1000')

SELECT * FROM DEMO_MG_Customer

第二个数据源是一个文本文件

ID,Company,CustomerName,Title,Address

1,'NRZBB','Allen,Michael','Sales Representative','Obere Str. 0123'

2,'MLTDN','Hassall, Mark','Owner','Avda. de la Constitución 5678'

3,'KBUDE','Peoples, John','Owner','Mataderos 7890'

4,'HFBZG','Arndt, Torsten','Sales Representative','7890 Hanover Sq.'

5,'HGVLZ','Higginbotham, Tom','Order Administrator','Berguvsvägen 5678'

示例一 - 使用 Merge 来合并数据

上面已经说了 Merge 的特点-
输入数据源 - 两个
输入数据源 - 表或者文件等
合并时要求元数据相同，数据类型相同
合并前需要排序

合并操作类似于 SQL 语句中的 UNION ALL。

OLE_SRC_Customer - OLE DB Source 中指定的数据源来自 BIWORK_SSIS 数据库中的 dbo.DEMO_MG_Customer 表。

表中的 5 个列都将作为输出列向下输出。

FF_SRC_Customer (Flat File Source) 的 Flat File Connection Manager 指向文本文件源。并且要注意 Text qualifier 是'，因为要注意到文本文件中位于 ' ' 之间的才是真正要处理的文本。

1,'NRZBB','Allen,Michael','Sales Representative','Obere Str. 0123'

并且指明文本文件中的第一行是列标题。

逗号分割列

在这里要注意的是文本文件中 ID 的数据类型指定 DT_I4 来映射 SQL Server 数据库中的INT 类型，否则两个数据源一旦有一个列数据类型不一致的话，那么合并操作时就会出现错误。

其它字符串用 DT_WSTR 即可，因为要和数据库中的 NVARCHAR 数据类型匹配。

注意在 Flat File Source 向下输出的时候并没有选择 Title, 因为这一列在 Input Table Source 中并不存在。在合并两个来自不同数据源的时候，我们要求两边的元数据一致，即列的数量和类型也应该一致。

因为本身 ID 就是有序的，所以为了演示的效果选择 CustomerName 作为排序列，两边的源的拍序列也应该选择一致。

Merger Transformation 列出了输出的列，两个输入源并且显示了它们的排序列，最终输出的结果也会按照 CustomerName 排序的结果来输出。

Merge 之后来自于两个不同数据源的数据就合并到了一起，并且 CustomerID = 2 的数据分别来自两个数据源，内容也是一致的，但并没有在合并的时候删除重复的数据，这类似于 SQL 语句中的 UNION ALL 的操作，保留了重复项。

示例二 - 使用 Merge Join 组件合并数据

Merge Join 类似于 SQL 中的 Full/Left/Inner Join 等操作，因为不需要两边数据源的元数据一致。但是，它也要求左右两边的数据源排序，并且排序列必须包含后面使用到的 JOIN 列。

前面的配置和上一个例子中一样，只是排序列改成了 CustomerID，只看 Merge Join 部分。

除了 Inner Join 外，还有 Full Join 和 Left Outer Join 等同于 SQL 中的 Inner Join/Full Join/Left Join 等操作。

看上面图片中显示了左右两边的数据源，其中 Join Key 必须包含在排序列中。在左边的表数据源中有4个输出列，与右边文件中的 Title 输出列共同组成了5个输出列。

如果使用 SQL 语句来表示这里的逻辑，可以理解成-

SELECT tbl.CustomerID,

            tbl.CustomerCompany,

            tbl.CustomerName,

            tbl.CustomerAddress,

            ff.Title

FROM ST_TBL_Customer AS tbl

INNER JOIN ST_FF_Customer AS ff

ON tbl.CustomerID = ff.ID

从这里看出 Inner Join 能关联上3条数据，其中 Title 列来源于文件数据源。

在这里，也可以使用 Merge Join 组件完成对已存在的数据进行更新，对不存在的数据进行插入操作。比如可以使用 Left Outer Join, 假设以左表为目标表的话，那么就能够利用关联上 ID 的右文件数据源来更新左表，关联不上的就作为新数据插入到左表中。只需要在 Merge Join 下加一个 Conditional Split 组件来判断即可，可以参考我的另一篇文章 -

SSIS 系列 - Lookup 组件的使用与它的几种缓存模式 - Full Cache, Partial Cache, NO Cache

示例三 - 使用 UNION ALL 组件合并数据

UNION ALL 组件与上面两个组件最大的区别就是，一可以合并两个以上的数据源，二是不需要对数据源进行排序。

这个示例中有三个数据源，前两个和上面示例中的配置一样，第三个数据源和第二个数据源实质上相同，都是指向同一个数据表。

直接看 UNION ALL 组件的配置，非常的简单。

看到输出列了吗？默认情况下将第一个文件数据源的列作为默认的整个组件的输出列，如果后面的数据源没有这些列的话，那么就忽略掉，其它的列再一一设置匹配一下，当然数据类型应该一致。

输出的结果如下，有重复的数据并且也未排序。

三个组件各自不同的特点

简单的可以归纳一下何时应该选择 Merge, Merge Join 和 Union All 组件来合并不同数据源的数据呢？

如果有两个以上的数据源 - UNION ALL
如果只有两个数据源，并且是从两个不同数据源基于一些关联条件各取一部分数据 - Merge Join
如果只有两个数据源，目的为了合并而非关联 - UNION ALL/Merge
如果只有两个数据源，目的只为了合并但不需要输出的结果排序 - UNION ALL
如果只有两个数据源，目的只为了合并但需要输出的结果排序 - Merge

Asynchronous Transformation VS Synchronous Transformation

当然，除此之外还有些细节需要知道的是 - 尽量避免使用 Sort 排序组件，原因在于 Sort 排序组件被称之为 Asynchronous Transformation。

Asynchronous Transformation 非同步转换 - Blocked Transformation 阻塞转换。Sort 排序组件就属于这一类，和它相同的还有 Pivot 组件。

它们处理数据的过程是先从上游数据源中抽取所有数据，再开始处理排序，全部排序完成之后再产生输出。这样的过程极大的消耗了内存并且使得整个处理的过程变得缓慢。

相对于这类组件，有一类组件是属于 Synchronous Transformation 同步转换，比如：

Derived Column
Copy Column
Data Conversion

这类组件基本上是从数据源一条一条的取，一条一条的处理并同时输出给下游转换组件。

所以在上面的几个示例中，更优的选择应该是在 OLE DB Source 的操作中使用 SELECT 语句加上排序操作来代替直接使用表或者视图，这样避免转换阻塞。

因此对示例一做出一些修改，去掉中间的排序组件。

在 OLE_SRC_Customer 中使用 SELECT 语句加上排序操作使得输出是已经排好序的结果。

SELECT CustomerID,

       CustomerCompany,

       CustomerName,

       CustomerAddress

FROM dbo.DEMO_MG_Customer

ORDER BY CustomerName

但是再次连接到 Merge 组件上时会发生错误，因为你还要通知一下 Merge 组件你是如何排序的。

右键 OLE_SRC_Customer 选择 Advanced Editor，在 Input and Output Properties 这里修改一下 IsSorted 属性，设置为 True, 默认是 False。这样就告诉了下游转换组件，这里的结果是已经排好序的。

同时还需要指定如何排序，按照哪些列来排的序。因为在示例 Merge 中我们选择的是 Customer Name, 因此这里将它的 0 修改为1。 0 表示是不排序的，1 表示是第一个排序位，这里应该按照 ORDER BY 后面的列顺序来设置，第二个排序列就设置为 2，依此类推。

修改完毕后，再运行一下第一个示例，结果是一样的

这里的数据量比较少，可以试一下10W级，100W级以上使用 Sort 组件和不使用 Sort 组件的差别来体会一下这两种处理方式的不同。

更多 BI 文章请参看 BI 系列随笔列表 (SSIS, SSRS, SSAS, MDX, SQL Server) 如果觉得这篇文章看了对您有帮助，请帮助推荐，以方便他人在 BIWORK 博客推荐栏中快速看到这些文章。