微软BI 之SSIS 系列 - Merge Join 的特征

浏览: 3588

开篇介绍

除了在上一篇介绍到了数据合并的 Merge 控件以及排序控件 Sort 之外,我们还有两个另外的数据流合并组件 Merge Join 和 Union All。 今天这篇文章主要是讲解 Merge Join 控件在 SSIS 中的使用以及它的特点。

Merge Join 

Merge Join 的首要特点是 Join,想到 Join 我们就完全可以想象到我们在 SQL Server 数据库中的 Join 操作,比如 Left Outer Join, Inner Join , Right Outer Join 等。 Merge Join 就是在 SSIS 中来实现这样的功能的,只不过区别就在于 Merge Join 的对象两端的源可以是表,也可以是文件。也就是说,Merge Join 两端的源结构类型是可以不一样的。但只要进入到数据流中,格式就可以统一处理了。

同样的 SSIS Merge Join 还有一个特点就是 SSIS Merge,在合并之前源数据是需要排序的。比如如果 OLE DB Source 没有排序的话,还是会出现这种错误信息:

The IsSorted property must be set to True on both sources of this transformation.

测试案例

本篇讲解的是 Merge 控件的使用,并且在使用的过程中也会介绍到 Sort 排序控件的操作。

-- Merge demo table
IF OBJECT_ID(\'T037_CUSTOMER\',\'U\') IS NOT NULL
DROP TABLE T037_CUSTOMER
GO

CREATE TABLE T037_CUSTOMER
(
CustomerID
INT PRIMARY KEY,
CustomerCompany
NVARCHAR(255),
CustomerName
NVARCHAR(20),
CustomerAddress
NVARCHAR(255)
)

INSERT INTO T037_CUSTOMERVALUES
(
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 T037_CUSTOMER

测试数据

第二个数据源是文本文件,注意到第 5 行是重复的数据。

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\'

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

基于上一篇文章,只需要修改

无论是哪一种 Join,关联的 Key 必须是 Merge Join 两端的排序列。左右两端需要输出的列只需要勾选中即可,可作为共同输出列往下输出,可以在 Output Alias 完成重命名操作。

Inner Join

从下面这个包运行的图中可以看出来:

  • 来自于 OLE_SRC_CUSTOMER 中有 3 行数据,FF_SRC_CUSTOMER 有 6 行数据。
  • FF_SRC_CUSTOMER 经过 SORT 排序之后由 6 条数据变为 5 条数据,说明 SORT 组件中排序去重(去掉重复排序键)这个选项已经开启,排序列有重复数据。
  • 来自表源和文件源基于 表.CustomerID = 文件.ID 有 3 条数据关联的上,最终输出 3 条数据。

在 Audit 之上开启一个 Data Viewer 重新运行一次就可以看到 Inner Join 之后的效果,和 SQL 中的效果是一样的。

Left Outer Join

Left Outer Join 的效果和上面的 Inner Join 效果看上去是一致的,这是因为左连接以左表为主,左表全有的才会列出来。

执行结果。

Right Outer Join

在关联 Merge Join 之处,源表默认选择了 Left Outer Join 所以这里就只能看到左外连接了。如果要基于源表使用右外连接,那么只需要选择 Swap Inputs,交换数据源,将文件数据源交换到左边的位置,而表源交换到右边的位置。这个时候的 Left Outer Join 就相当于变成了 文件源 Left Outer Join 表了。

交换之后的效果。

执行包就会发现这时就是以文件源为主的左外连接起到作用了,相对来说就是以源表为基础的右外连接起到作用了。

在这种流程中是完全可以结合使用 Conditional Split 来对一些业务进行判断了,比如查询商品和商品销售记录,哪些商品有交易记录,哪些商品没有记录只需要判断一下 NULL 值就可以了。

Full Outer Join 

再插入一条数据到测试表中

INSERT INTO T037_CUSTOMER VALUES (6,\'HFBZG\',\'Allen,Michael\',\'Obere Str. 0123\') 

然后将 Merge Join 改为 Full Outer Join 就可以看到两端的数据都全部出来了。

总结

SSIS Merge Join 既具备 SSIS Merge 的特征又具备 SQL Join 的特征,这是它的最显著的特点。只要理解了 SSIS Merge 控件的操作和 SQL Server 中 Inner Join, Left Outer Join, Right Outer Join, Full Outer Join 这些概念,基本上这个控件的使用就掌握了。

它的特点就是:

  • 数据源只能有两个。
  • 数据源可以是表,视图或 SQL 查询返回的数据集,也可以是不同类型的文件,异构数据类型。
  • 合并时对元数据没有要求,只要求关联列类型一致即可。
  • Merge Join 控件两端的源和 Merge 控件一样都要求排序。
  • 输出结果也是排序的。
  • Join 列必须包含在排序列中。
推荐 0
本文由 BIWORK 创作,采用 知识共享署名-相同方式共享 3.0 中国大陆许可协议 进行许可。
转载、引用前需联系作者,并署名作者且注明文章出处。
本站文章版权归原作者及原出处所有 。内容为作者个人观点, 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台,并不用于任何商业目的,如果有任何问题,请及时联系我们,我们将根据著作权人的要求,立即更正或者删除有关内容。本站拥有对此声明的最终解释权。

2 个评论

如果是LEFT JOIN和INNER JOIN这两种情况的话,会不会用查找组件更方便高效些,查找组件不用预先排序这个步骤,不知道这个MERGE JOIN和查找LOOKUP控件分别哪些场合应用?特别是MERGE JOIN有哪些是查找LOOKUP组件没法替代的部分?
Merge demo table 那段SQL语句是错误的,插入的测试数据应该是:

INSERT INTO T037_CUSTOMER
(CustomerID,CustomerCompany,CustomerName,CustomerAddress)
values
(1,'HFBZG','Allen,Michael','Obere Str. 0123'),
(2,'MLTDN','Hassall, Mark','Avda. de la Constitución 5678'),
(3,'KBUDE','Peoples, John','Mataderos 1000')

要回复文章请先登录注册