代码改变世界

微软BI 之SSIS 系列 - 对于平面文件中 NULL 值处理过程中容易极易混淆的几个细节

2014-08-21 22:22  BIWORK  阅读(3555)  评论(6)  编辑  收藏  举报

开篇介绍

最近有人问我有关文件处理中空值处理的相关问题：
  1. OLE DB Destination 中的 Keep Nulls 如何控制 NULL 值的显示？
  2. 为什么选中了 Keep Nulls 但是数据库中没有 NULL 值？
  3. 为什么在 Flat File Source 中勾选上了 Retain null values..但目标表上显示的是一个当前日期，而不是 NULL ？
单开此文来解释这些非常容易混淆的概念。

项目需求和文件处理中的问题

在比较纯粹的 ETL 项目中都会碰到对空值的处理，特别是对以文件数据源中的控制处理。不同的项目，不同的设计，对最终输出值的控制是不一样的。
就 SSIS 对平面文件 NULL 值的处理，可以有这么几个实际开发中会碰到的问题或者需求，如果文件源中有空值 -
  1. 如何让它在输出到目标表的时候使用目标表列的默认值？
  2. 如何让它在输出到目标表的时候显示 NULL ?
  3. 如何让它在输出到目标表的时候显示空白字符串 ？

普遍性的概念理解上的误区

通常情况下，在以平面文件为数据源的 ETL 处理过程中，对于数据源中的空值的控制往往涉及到源控件和目标控件，例如 Flat File Source 和 OLE DB Destination。但是，即使经验和丰富的 ETL 开发人员也非常容易混淆这几种不同配置的差异，并且特别对于 DEFAULT VALUE 这一点常常被忽略掉了。
我看到的一篇博客 - http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/61818/ 这位作者本身的介绍的出发点没有错，但是就没有正确区分 OLE DB Destination 中 Fast Load 和 非 Fast Load 对于这个 NULL 值的影响。
包括之前我遇到的一个面试，面试我的人错误的认为对于空值的输出只有 NULL 和 空白字符串两种方式，而漏掉了 DEFAULT 处理。但是这一个细节其实是非常重要的，了解这一个特性可以避免在 ETL 中加入条件判断，类型转换来确保目标列的值不是 NULL 或者空白字符串。
 
所以这些小细节是非常容易出错，概念的混淆也是普遍性的。因此，这篇文章以不同的测试案例总结让大家区分不同的配置对空值的影响，最终理解这些细节。

概括与总结

我先用下面这幅图来说明这个区别，后面是详细的比较案例，最后再来解释这些区别的原因。
数据源如下图所示，是一个以逗号分隔的文本文件，注意到第4行的逗号分隔部分的内容是一个空字符串，而第5行是没有值，表示空值。
测试目标表部分结构, 注意到 Middle Name 和 Hire Date 这两列上有 Default 约束。
IF OBJECT_ID('T005_STAGING_EMPLOYEE') IS NOT NULL
DROP TABLE T005_STAGING_EMPLOYEE
GO

CREATE TABLE [T005_STAGING_EMPLOYEE_1] (
    ID INT PRIMARY KEY IDENTITY(1,1),
    [FIRSTNAME] VARCHAR(50),
    [LASTNAME] VARCHAR(50),
    [MIDDLENAME] VARCHAR(50) DEFAULT('-'),
    [NAMESTYLE] VARCHAR(50),
    [TITLE] VARCHAR(50),
    [HIREDATE] VARCHAR(50) DEFAULT(GETDATE()),
    [BIRTHDATE] VARCHAR(50),
    [EMAILADDRESS] VARCHAR(50)
）

第一个测试  

Flat File Source  - Retain null values from the source as null values in the data flow - 默认不选中。
OLE DB Destination - Data Access Mode - 选择 Table or View。
Mapping 中自动标识列忽略掉。
配置完成后保存并执行包，查看表结果，第四行输出的是空白字符串。

第二个测试  

Flat File Source  - Retain null values from the source as null values in the data flow - 默认不选中。
OLE DB Destination - Data Access Mode - 选择 Table or View - Fast Load。
看似查询结果没有什么变化，和第一个测试案例中的是一样的。

第三个测试  

Flat File Source  - Retain null values from the source as null values in the data flow - 选中。
OLE DB Destination - Data Access Mode - 选择 Table or View。
保存并执行，查看数据表结果，第4行的 MIDDLE NAME 自动转换为 NULL 了。

第四个测试  

Flat File Source  - Retain null values from the source as null values in the data flow - 选中。
OLE DB Destination - Data Access Mode - 选择 Table or View - Fast Load。
保存并执行包，查看结果， 会发现第4行的 Middle Name 使用了默认值 "-"。

第五个测试 

Flat File Source  - Retain null values from the source as null values in the data flow - 选中。
OLE DB Destination - Data Access Mode - 选择 Table or View - Fast Load。
Keep Nulls - 选中。
保存并执行，查看数据表结果，第4行的 MIDDLE NAME 此时又变成NULL 了。

第六个测试 

Flat File Source  - Retain null values from the source as null values in the data flow - 不选中。
OLE DB Destination - Data Access Mode - 选择 Table or View - Fast Load。
Keep Nulls - 选中。
保存运行之后的查询结果是空白字符串。

案例测试的结论

通过以上六个测试案例，可以看到当文件数据源中包含有空值的时候，不同的设置输出到数据表中的结果是不一样的，大概有三种：
  1. 空白字符串
  2. NULL 值
  3. 数据表中列的默认值
简单总结一下，不同的配置对空值的处理结果。

第一阶段 - Flat File Source 对空值的转换过程

我们第一点要理解 Flat File Source  中 "Retain null values from the source as null values in the data flow" 的作用，很容易理解：
默认不选中 - 只要是空值就转成空白字符串向下输出
选中 - 只要是空值就转成 NULL向下输出
 
可以打开一个监视器，看看在从 FF_SRC_EMPLOYEE 到 OLD_DST_STAGING_EMPLOYEE_1 的过程中，选中与否的区别。
默认不选中的情况下，空值变成字符串。
选中之后的效果。
这是一定要理解的第一点，自 FF_SRC_EMPLOYE 它输出以后，最终到数据表中是否为 NULL 就已经与它自身无关了。只用理解它何时输出为 NULL 何时输出为空字符串就可以了。

第二阶段 OLE DB Destination 对空值的处理过程

无论是 Fast Load 模式还是非 Fast Load 模式，只要接受上游空间向下输出的是非空值，即非NULL值，那么上游数据是什么，下游就接受成什么。

因此，即使是文件数据源中存在 NULL 空值，但是只要 Flat File Source 中的 Retain null values from the source as null values in the data flow 勾选上，那么空值在文件源段向下输出时就已经变成空白字符串。所以在 OLE DB Destination 中无论设置什么模式，最终都不会影响空白字符串的输出，因为空白字符串是有值的，长度为0的字符串。

只有当 Flat File Source 中的 Retain null values from the source as null values in the data flow 勾选上的时候，空值为转换为 NULL 值，这时 OLE DB Destination 中的设置才会影响到最终输出的值。

Access Mode 中各种配置对 NULL 值的处理方式
  1. Table or view - 对 NULL 不处理，上游是 NULL 值，它最终输出就是 NULL 值。
  2. Table or view - fast load 加上默认不选中 Keep nulls - 如果目标列没有 DEFAULT 默认约束的话，那么输出到目标列就是 NULL 值；如果目标列有 DEFAULT 默认约束的话，那么输出到目标列使用的就是 DEFAULT 默认约束中的值。
  3. Table or view - fast load + 选中 Keep nulls - 无论目标列有没有 DEFAULT 默认约束，都将保留 NULL 值。
再回头来看这个总结就很容易了，比如在面试中被问到 - 如果文件源中有空值，如何让它在输出到目标表的时候使用目标表列的默认值？ 那么就很容易回答了！

所以，把 Flat File Source 和 OLE DB Destination 分开来看，只考虑如何控制 Flat File Source 它的输出是否为 NULL，只考虑 OLE DB Destination 中对 NULL 值的不同处理方式，分成这样两个阶段这个概念就很容易理解了。并且，理解了上面这些内容，就不会再认为只要选中了 Flat File Source 中的 Retain null values from the source as null values in the data flow，文件中的 NULL 值就会输出到目标表了。

当然，这里面涉及到了 OLE DB Destination 控件中两个不同的加载模式 - Fast Load 和 非 Fast Load，包括其它的几个配置选项，会单独放到一篇文章中去讲解。

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