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

时间:2022-08-22 19:22:05

开篇介绍

最近有人问我有关文件处理中空值处理的相关问题:
  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 或者空白字符串。
 
所以这些小细节是非常容易出错,概念的混淆也是普遍性的。因此,这篇文章以不同的测试案例总结让大家区分不同的配置对空值的影响,最终理解这些细节。

概括与总结

我先用下面这幅图来说明这个区别,后面是详细的比较案例,最后再来解释这些区别的原因。
微软BI 之SSIS 系列 - 对于平面文件中 NULL 值处理过程中容易极易混淆的几个细节
数据源如下图所示,是一个以逗号分隔的文本文件,注意到第4行的逗号分隔部分的内容是一个空字符串,而第5行是没有值,表示空值。
微软BI 之SSIS 系列 - 对于平面文件中 NULL 值处理过程中容易极易混淆的几个细节
测试目标表部分结构, 注意到 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。
微软BI 之SSIS 系列 - 对于平面文件中 NULL 值处理过程中容易极易混淆的几个细节
微软BI 之SSIS 系列 - 对于平面文件中 NULL 值处理过程中容易极易混淆的几个细节
Mapping 中自动标识列忽略掉。
微软BI 之SSIS 系列 - 对于平面文件中 NULL 值处理过程中容易极易混淆的几个细节
配置完成后保存并执行包,查看表结果,第四行输出的是空白字符串。
微软BI 之SSIS 系列 - 对于平面文件中 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。
微软BI 之SSIS 系列 - 对于平面文件中 NULL 值处理过程中容易极易混淆的几个细节
看似查询结果没有什么变化,和第一个测试案例中的是一样的。
微软BI 之SSIS 系列 - 对于平面文件中 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。
微软BI 之SSIS 系列 - 对于平面文件中 NULL 值处理过程中容易极易混淆的几个细节
保存并执行,查看数据表结果,第4行的 MIDDLE NAME 自动转换为 NULL 了。
微软BI 之SSIS 系列 - 对于平面文件中 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 使用了默认值 "-"。
微软BI 之SSIS 系列 - 对于平面文件中 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 - 选中。
微软BI 之SSIS 系列 - 对于平面文件中 NULL 值处理过程中容易极易混淆的几个细节
保存并执行,查看数据表结果,第4行的 MIDDLE NAME 此时又变成NULL 了。
微软BI 之SSIS 系列 - 对于平面文件中 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 - 选中。
微软BI 之SSIS 系列 - 对于平面文件中 NULL 值处理过程中容易极易混淆的几个细节
保存运行之后的查询结果是空白字符串。
微软BI 之SSIS 系列 - 对于平面文件中 NULL 值处理过程中容易极易混淆的几个细节

案例测试的结论

通过以上六个测试案例,可以看到当文件数据源中包含有空值的时候,不同的设置输出到数据表中的结果是不一样的,大概有三种:
  1. 空白字符串
  2. NULL 值
  3. 数据表中列的默认值
简单总结一下,不同的配置对空值的处理结果。
微软BI 之SSIS 系列 - 对于平面文件中 NULL 值处理过程中容易极易混淆的几个细节

第一阶段 - 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 的过程中,选中与否的区别。
微软BI 之SSIS 系列 - 对于平面文件中 NULL 值处理过程中容易极易混淆的几个细节
默认不选中的情况下,空值变成字符串。
微软BI 之SSIS 系列 - 对于平面文件中 NULL 值处理过程中容易极易混淆的几个细节
选中之后的效果。
微软BI 之SSIS 系列 - 对于平面文件中 NULL 值处理过程中容易极易混淆的几个细节
这是一定要理解的第一点,自 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 值。
再回头来看这个总结就很容易了,比如在面试中被问到 - 如果文件源中有空值,如何让它在输出到目标表的时候使用目标表列的默认值? 那么就很容易回答了!
微软BI 之SSIS 系列 - 对于平面文件中 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 博客推荐栏中快速看到这些文章。

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

  1. 微软BI 之SSIS 系列 - 带有 Header 和 Trailer 的不规则的平面文件输出处理技巧

    案例背景与需求介绍 之前做过一个美国的医疗保险的项目,保险提供商有大量的文件需要发送给比如像银行,医疗协会,第三方服务商等.比如像与银行交互的 ACH 文件,传送给协会的 ACH Credit 等文件 ...

  2. 微软BI 之SSIS 系列 - 在 SSIS 中导入 ACCESS 数据库中的数据

    开篇介绍 来自 天善学院 一个学员的问题,如何在 SSIS 中导入 ACCESS 数据表中的数据. 在 SSIS 中导入 ACCESS 数据库数据 ACCESS 实际上是一个轻量级的桌面数据库,直接使 ...

  3. 微软BI 之SSIS 系列 - MVP 们也不解的 Scrip Task 脚本任务中的一个 Bug

    开篇介绍 前些天自己在整理 SSIS 2012 资料的时候发现了一个功能设计上的疑似Bug,在 Script Task 中是可以给只读列表中的变量赋值.我记得以前在 2008 的版本中为了弄明白这个配 ...

  4. 微软BI 之SSIS 系列 - 数据仓库中实现 Slowly Changing Dimension 缓慢渐变维度的三种方式

    开篇介绍 关于 Slowly Changing Dimension 缓慢渐变维度的理论概念请参看 数据仓库系列 - 缓慢渐变维度 (Slowly Changing Dimension) 常见的三种类型 ...

  5. 微软BI 之SSIS 系列 - Lookup 中的字符串比较大小写处理 Case Sensitive or Insensitive

    开篇介绍 前几天碰到这样的一个问题,在 Lookup 中如何设置大小写不敏感比较,即如何在 Lookup 中的字符串比较时不区分大小写? 实际上就这个问题已经有很多人提给微软了,但是得到的结果就是 C ...

  6. 微软BI 之SSIS 系列 - 使用 Script Component Destination 和 ADO.NET 解析不规则文件并插入数据

    开篇介绍 这一篇文章是 微软BI 之SSIS 系列 - 带有 Header 和 Trailer 的不规则的平面文件输出处理技巧 的续篇,在上篇文章中介绍到了对于这种不规则文件输出的处理方式.比如下图中 ...

  7. 微软BI 之SSIS 系列 - 再谈Lookup 缓存

    开篇介绍 关于 Lookup 的缓存其实在之前的一篇文章中已经提到了 微软BI 之SSIS 系列 - Lookup 组件的使用与它的几种缓存模式 - Full Cache, Partial Cache ...

  8. 微软BI 之SSIS 系列 - 使用 Script Task 访问非 Windows 验证下的 SMTP 服务器发送邮件

    原文:微软BI 之SSIS 系列 - 使用 Script Task 访问非 Windows 验证下的 SMTP 服务器发送邮件 开篇介绍 大多数情况下我们的 SSIS 包都会配置在 SQL Agent ...

  9. 微软BI 之SSIS 系列 - 使用 SQL Profilling Task (数据探测) 检测数据源数据

    开篇介绍 SQL Profilling Task 可能我们很多人都没有在 SSIS 中真正使用过,所以对于这个控件的用法可能也不太了解.那我们换一个讲法,假设我们有这样的一个需求 - 需要对数据库表中 ...

随机推荐

  1. Linux进程含义知多少

    理想情况下,您应该明白在您的系统中运行的每一个进程.要获得所有进程的列表,可以执行命令 ps -ef(POSIX 风格)或 ps ax(BSD 风格).进程名有方括号的是内核级的进程,执行辅助功能(比 ...

  2. eclipse提交subversion+apache的中文路径问题解决过程

    原文链接:http://cavenfeng.iteye.com/blog/1921400 .download & patch: # wget http://webdav.todo.gr.jp/ ...

  3. careercup-高等难度 18.6

    18.6 设计一个算法,给定10亿个数字,找出最小的100万个数字.假定计算机内存足以容纳全部10亿个数字. 解法: 方法1:排序 按升序排序所有的元素,然后取出前100万个数,时间复杂度为O(nlo ...

  4. poj 2226 Muddy Fields (转化成二分图的最小覆盖)

    http://poj.org/problem?id=2226 Muddy Fields Time Limit: 1000MS   Memory Limit: 65536K Total Submissi ...

  5. 使用git部署服务器

    每回上传服务器是把我的web项目打包成war上传到服务器根目录下,总是觉得很麻烦,并且每次就算只添加或修改了一个文件,都要把整个服务器war包上传.于是我学着用git部署我的web项目 下面是详情: ...

  6. Linux磁盘挂载

            mount命令 用于加载文件系统到指定的加载点.此命令的最常用于挂载cdrom,使我们可以访问cdrom中的数据,因为你将光盘插入cdrom中,Linux并不会自动挂载,必须使用Lin ...

  7. 正则解析json数据

    http://tool.chinaz.com/regex http://tool.oschina.net/regex/

  8. webpack学习笔记—优化缓存、合并、懒加载等

    除了前面的webpack基本配置,还可以进一步添加配置,优化合并文件,加快编译速度.下面是生产环境配置文件webpack.production.js,与wenbpack.config.js相比其不需要 ...

  9. MFQ

    一什么是MFQ&PPDCS?MFQ&PPDCS是由外部教练邰晓梅提出的一套测试分析和测试设计方法.MFQ将被测对象分层,针对不同层次进行测试分析和设计进行,使测试设计人员不会那么容易忘 ...

  10. 页面倒计时跳转页面效果,js倒计时效果

    页面倒计时跳转页面效果,js倒计时效果 >>>>>>>>>>>>>>>>>>>> ...