sort、merge、merge join、union all

原文：http://www.cnblogs.com/gudujianxiao/archive/2012/07/17/2594709.html

SSIS组件转换_排序、合并、合并联转换

一、排序转换（Sort）

排序转换功能是按升序或降序对输入流中的数据进行排序.并将排序后的数据复制到转换输出中.它类似于T-SQL中的order by 语句。下面以数据库AdventureWorks为例来介绍一下排序转换的使用。

1.新建名为”SortDemo”的包，在控制流中拖放一个数据流任务，然后在连接管理器中建立一个OLE DB连接器，用于连接AdventureWorks数据库。

2.在BIDS界面左边的变量工具箱中设置一个变量Count 类型是Int32。

3.在数据流选项卡中拖放一个OLE DB数据源，连接AdventureWorks，选择Production.product.如图:

 

4.拖放一个排序转换任务。按如下图设置:

 

 

 

下面详细介绍一下该图中各个属性的使用方法:

属性	说明
可用输入列	使用此复选框可以指定要排序的列
名称	可用输入列的名称
传递	指示是否在排序输出中包含该列
输入列	选择需要排序的列，也可以通过可用输入列中选择
输出别名	为要排序的输入列设置一个输出别名，默认为输入列名称
排序类型	指示是按升序还是按降序排序
排序顺序	指示列的排序顺序。必须是手动设置此项，而且设置时按初始值为1的递增数据

上图中理解为先按Name降序，再按ProductName升序排序。

5.拖放一个行计数转换，将排序与之连接，箭头指向行计数转换。并设置

 

其中变量User::Count在变量工具箱中创建。

6.在排序转换与行计数转换中的绿色连线上建立一个带有Grid网格的查看器。然后执行包。如下图:

 

我们发现Name和ProductName列按我们在排序转换中的设置输出到数据管道中。

二、合并转换(Merge)

合并转换是将两个排序后的数据集合合并为一个数据集。根据每个数据集中的行的键列的值，将这些行插入到输出中。合并转换功能类似于T-sql语句中的Union All子句。

合并转换要求输入列具有匹配的源数据。在SSIS设计器中，合并转换的用户界面会自动映射映射具有元数据的列。然后你可以手动映射具有兼容数据类型的其他列。

下面我们以AdventureWorks数据库中的Production.Product表为例介绍如何使用合并转换。

 

1.创建一个名为“MergerDemo”的包，然后在控制流中拖放一个DataFlow任务，在连接管理器中创建一个OLE DB连接器，用于连接AdventureWorks数据库。

2.在BIDS中的变量工具箱中创建一个变量Count，类型为Int32。

3.在数据流中拖放两个OLE DB数据源。连接器连接AdventureWorks数据库,两个数据源的访问模式都选择sql命令。Sql语句分别为:

select top 5 * from Production.Product

select top 6 * from Production.Product

4.在数据流中拖放两个排序转换功能。分别对两个数据源数据的列ProductNumber按升序排序。

5.拖放一个合并转换，分别与两个排序转换连接。设置如下：

 

 

 

6.拖放一个行计数。与合并转换相连。设置VariableName=User：：Count

7.在合并转换与行计数转换中间的连接创建一个数据查看器。然后执行包。结果如下:

 

 

 

8.在弹出框中可以看到我们合并的部分数据。可以持续单击绿色三角按钮来查看合并的数据。想让包执行完毕。可以关闭弹出框即可。

到此，合并转换功能的使用已介绍完毕。其中一些注意的事项我们将与合并联接转换一起介绍。

三、合并联接转换(Merge Join)

合并联接转换功能是将两个已排序的输入列的数据通过使用FULL JOIN、Left JOIN或INNERR JOIN联接形成一个输出数据集。它类似于T-SQL语句中的连接查询语句。但又与之有所区别。

•  使用合并联接要求输入已排序的数据。而T-SQL中的连接查询不需要

• 如果两个输入列关联的列是字符类型。则需要对它进行大小写转换或去除空格等操作。而在T-sql的连接查询中不需要。

了解这些信息后，下面介绍如何使用。

1.创建一个名为“MergeJoinDemo”包，在控制流中拖放一个Data Flow 任务。在连接管理器中添加一个OLE DB连接器。用于连接AdventureWorks数据库。

2.在变量工具箱中创建一个如下图的变量

 

 

3.在数据流选项卡中拖放两个OLE DB数据源。都连接刚才创建的名为“LocalHost.AdventureWorks”连接器。数据访问模式使用sql命令。Sql语句分别如下

select ProductID,ProductModelID,Name from Production.Product

select ProductModelID,Name from Production.ProductModel

4.拖放两个排序转换到数据流选项卡中，分别对两个数据源数据的列ProductModelID按升序排序。

5.拖放一个合并联接转换，分别与两个排序转换连接，然后双击合并联接转换，弹出窗口根据窗口设置合并联转换属性。

 

 

在图中有一个联接类型选项，它包含三种类型:左外部联接、内部联接和完全外部联接，分别等同于T—SQL中的LEFT JOIN、INNER JOIN和FULL JOIN

6.拖放一个行计数。与合并联接转换相连。设置VariableName=User：：Count

7.在合并转换与行计数转换中间的连接创建一个数据查看器。然后执行包。结果如下:

 

在弹出框中可以看到我们合并的部分数据。可以持续单击绿色三角按钮来查看合并的数据,想让包执行完毕,可以关闭弹出框即可

 

 

 

 

原文：http://www.cnblogs.com/biwork/p/3308846.html

开篇介绍

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 组件的差别来体会一下这两种处理方式的不同。