使用SSIS进行数据清洗

本文涉及的产品
日志服务 SLS,月写入数据量 50GB 1个月
简介: 原文:使用SSIS进行数据清洗简介     OLTP系统的后端关系数据库用于存储不同种类的数据,理论上来讲,数据库中每一列的值都有其所代表的特定含义,数据也应该在存入数据库之前进行规范化处理,比如说“age”列,用于存储人的年龄,设置的数据类型为INT类型。
原文: 使用SSIS进行数据清洗

简介

    OLTP系统的后端关系数据库用于存储不同种类的数据,理论上来讲,数据库中每一列的值都有其所代表的特定含义,数据也应该在存入数据库之前进行规范化处理,比如说“age”列,用于存储人的年龄,设置的数据类型为INT类型。存入数据库的值是2000虽然看起来没有任何问题,但结合业务规则,这样的”Noisy”数据在数据分析过程中就会造成数据分析的结果严重失真,比如极大的拉高平均年龄。在真实的OLTP系统中,这类不该存在的数据往往会由于各种各样的原因大量存在,类似这种类型的数据都会在数据进入数据仓库之前,在预处理阶段进行清洗。

    上面举出的简单例子仅仅是一个违反业务规则的情况,但实际的情况会根据具体业务的不同而不同,并不是一个简单统一的过程就能够解决,文本列举了一些典型的需要数据清洗的场景和SSIS中的实现方式。

为什么不使用SQL语句进行处理?

    使用SQL语句来查询和处理这类问题是可行的,但使用SQL语句有其局限性,例如:

  • 假如数据源不是关系数据库呢?
  • 假如业务逻辑非常复杂,需要非常复杂的SQL语句呢,处理过程中的调试、日志如何处理?

    使用SSIS可以很好的解决上述问题,SSIS提供了基于图的数据流,并在处理过程中提供了调试、日志、查看转换中的数据的能力,并且SSIS包相较于SQL更适合于团队作业。

 

使用SSIS进行数据清洗

    下面是一些典型的数据清理作业,使用SSIS进行处理。

 

缺失默认值

    缺失默认值是最常见的一种需要数据清洗的一种情况,缺失默认值指的是数据库列中的值为“NULL”,而在数据分析中,该类为NULL的数据需要替换中符合业务逻辑的值,比如NULL出现在销售地区列可以替换成“其他”,出现在销售数据列可以替换成“未知”等。

    在SSIS中我们可以通过“派生列”任务来为缺失默认值的列添加默认值,本例中我们对Adventureworks示例数据库的Person.Address数据库的AddressLine2列进行替换,该列中为NULL的值我们统一替换成“无附加地址”,如图1所示。

1

图1.使用派生列替换NULL

 

    除了上述使用常量替换"NULL"值之外,我们也可以按照业务规则根据其他数据源的数据填补NULL值,图2中的例子是ProductCostHistory表中部分EndDate列为NULL,通过查找Product表的ModifiedDate列将为NULL的EndDate列中的值补全。

    2

   图2.使用有条件拆分判断EndDate列值是否为NULL

 

语义转换

    有时候,某些列的值需要做语义上的转换,比如说某个产品的"产地"列为5,分析时很难知道5代表什么,因此可以去对应的表或文件进行查找来替换这个5,比如5代表河北。在SSIS中,实现该类转换可以类似图2中直接去表中查找,也可以为该查找表(5代表河北这张表)建立缓存,然后通过缓存进行引用查找。

    为了实现通过缓存做语义转换,我们首先设置两个步骤,第一步首先将查找表的数据导入缓存,第二步再对缓存进行查找从而实现语义转换。

    图3是总体图。

3

图3.语义转换

 

    首先将查找表的数据导入缓存,如图4所示。

4

图4.导入缓存

 

    然后通过缓存更新数据,如图5所示。

image

图5.使用缓存进行数据查找

 

数据类型转换

    当数据来自不同数据源时,不同类型的数据源数据类型不兼容可能导致报错。在SSIS中,可以使用“派生列”来实现数据转换,派生列允许写自定义公式实现比较复杂的转换,也可以通过数据转换任务实现简单的数据类型转换,从而使得不同数据源的数据兼容。如图6所示。

6

图6.数据类型转换

 

数据截断问题

    某些情况下,数据可能在传输过程中由于数据长度的限制导致数据截断,在SSIS中的解决办法是将截断或者出错的数据导入到一个中间表中待进一步处理,如图7所示。

7

图7.数据截断错误输出到另一个源

    在OLEDB源的配置如图8所示。

image

图8.OLEDB数据源设置

 

查找匹配失败

    在某些情况下,将多个数据源中的数据进行集成时,同一个语义的数据可能以不同的方式存储,比如一些数据源存的值为“北京”,而另一些数据源存的为“北京市”。解决该类问题的办法就是“模糊查找”任务。如图9所示。

    9

图9.对数据进行模糊查找

 

    在图9中,我们对数据进行了模糊查找,设置匹配度大于0.5的为匹配,小于0.5的为不匹配,分别输出到不同的数据源中。从而帮助数据进行了统一。

 

违反业务规则

    在设计数据仓库时,很重要的一点是列中何种类型的值是合适的。比如值是否在业务范围之内,或者列中数据与其相关的数据结合是否有效(比如说结束日志不能小于开始日志)。对于该类数据导入到目标表之前应该进行清理,在SSIS中清洗的方式有很多,比如使用“有条件拆分”任务将不符合业务规范的数据进行剔除或处理,或导入stage表。这取决于业务类型。

 

小结

    本文简述了数据清洗的概念,并对一些常见的数据清洗场景进行了实现。使用SSIS进行数据清洗相较于其他方式有很大的灵活性和性能。

相关实践学习
日志服务之使用Nginx模式采集日志
本文介绍如何通过日志服务控制台创建Nginx模式的Logtail配置快速采集Nginx日志并进行多维度分析。
目录
相关文章
|
8月前
|
数据采集 机器学习/深度学习 自然语言处理
数据清洗与过滤
【10月更文挑战第6天】数据清洗与过滤
117 1
|
JSON 前端开发 Java
SpringBoot中Date格式化处理
日期格式化处理:从混乱到清晰,轻松转换日期格式
245 1
|
Java Maven
maven 使用国内代理
一直使用 maven默认仓库,maven 的下载速度一直不快不慢;最近换了网络,maven秒变蜗牛,换了阿里云的镜像,瞬间飞快。 配置文件 ${USER_HOME}/.m2/settings.xml: 设置阿里云镜像 alimaven aliyun maven http://maven.
7911 0
|
3月前
|
存储 JSON 分布式计算
StarRocks + Paimon 在阿里集团 Lakehouse 的探索与实践
阿里集团在推进湖仓一体化建设过程中,依托 StarRocks 强大的 OLAP 查询能力与 Paimon 的高效数据入湖特性,实现了流批一体、存储成本大幅下降、查询性能数倍提升的显著成效: A+ 业务借助 Paimon 的准实时入湖,显著降低了存储成本,并引入 StarRocks 提升查询性能。升级后,数据时效提前60分钟,开发效率提升50%;JSON列化存储减少50%,查询性能提升最高达10倍;OLAP分析中,非JOIN查询快1倍,JOIN查询快5倍。 饿了么升级为准实时Lakehouse架构后,在时效性仅损失1-5分钟的前提下,实现Flink资源缩减、StarRocks查询性能提升(仅5%
401 60
StarRocks + Paimon 在阿里集团 Lakehouse 的探索与实践
|
9月前
|
存储 SQL 缓存
快手:从 Clickhouse 到 Apache Doris,实现湖仓分离向湖仓一体架构升级
快手 OLAP 系统为内外多个场景提供数据服务,每天承载近 10 亿的查询请求。原有湖仓分离架构,由离线数据湖和实时数仓组成,面临存储冗余、资源抢占、治理复杂、查询调优难等问题。通过引入 Apache Doris 湖仓一体能力,替换了 Clickhouse ,升级为湖仓一体架构,并结合 Doris 的物化视图改写能力和自动物化服务,实现高性能的数据查询以及灵活的数据治理。
快手:从 Clickhouse 到 Apache Doris,实现湖仓分离向湖仓一体架构升级
|
数据采集 SQL 分布式计算
Hadoop数据清洗和转换
【5月更文挑战第9天】Hadoop数据清洗和转换
234 2
|
9月前
|
存储 安全 程序员
Windows任务管理器开发原理与实现
Windows任务管理器开发原理与实现
|
存储 分布式计算 Hadoop
HDFS基本原理及操作
通过实验了解HDFS的基本原理,掌握HDFS Shell常用命令。
|
SQL Java 数据处理
实时计算 Flink版产品使用合集之怎么热加载Java和Python的UDF
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
154 1
|
SQL Java HIVE
使用 Paimon + StarRocks 极速批流一体湖仓分析
本文整理自阿里云智能高级开发工程师王日宇,在 Flink Forward Asia 2023 流式湖仓(二)专场的分享。
80424 3
使用 Paimon + StarRocks 极速批流一体湖仓分析