什么是ETL?一文掌握ETL设计过程
更关键的是,数据来源的业务系统也是在不断地更新维护中的,任何一个变更都会对下游的数据分析程序产生巨大的影响。因此,有了ETL过程作为一个缓冲区,当上游的业务系统变更时,只需要对ETL过程进行相应变更,下游的数据分析就能够比较稳定,从而降低系统维护成本。
ETL,是英文 Extract-Transform-Load 的缩写,用来描述将数据从来源端经过抽取(extract)、转换(transform)、加载(load)至目的端的过程。
1、数据清洗
首先进行数据清洗,对原始数据中的错误予以纠正,或者对缺失数据进行补填。譬如,现在要建设一个增值税发票的数据中台。这时,系统从许多不同的来源采集与增值税发票相关的数据。当收集完这些原始数据以后,进行数据清洗工作。增值税发票的数据结构如下图所示。
增值税发票的数据结构图
在正常的增值税发票的数据结构中,每张进项发票都应当有至少一条发票明细。然而,可能由于采集的数据不一致,发票与明细经常不是同时到来,可能相差几天,造成用发票分析的数据与用发票明细分析的数据不一致。这时,必须要先补填一个发票明细,虽然商品名称与数量不知道,但至少要保证发票明细的金额之和要等于发票金额,才不至于影响后续的分析质量。至于商品名称,可以暂时补填一个“未知商品”。这样,当该发票真正的发票明细到来时,再覆盖原有补填的明细。
此外,原本每张发票都应当有购方纳税人与销方纳税人,然而由于纳税人信息的基础数据来源于不同的系统,可能造成该发票的纳税人信息不在纳税人信息表中的情况。这时,必须要补填一条纳税人信息,使得发票表与纳税人能够对应上,不会造成数据无法关联而缺失数据。
同理,每个纳税人都应当有各自的税务机关、地域和行业,这些信息都可能缺失。对于税务机关和地域,可以通过纳税人社会信用代码中的内容进行推测。但是,行业信息是无法推测的。即使无法推测,也不能将其置为null,而是填一个默认值X99999,对应到行业表中的“未知行业”。
数据清洗的过程通过SparkSQL来实现。通过SparkSQL从原始表中查询数据,然后经过以下处理过程,最终写入ETL临时表中:
在以上SparkSQL程序中,首先从原始数据dzdz.DZDZ_FPXX_ZZSFP中查询数据,通过公用方法UdfRegister.fillNsr(spark)与UdfRegister.fillSwjg(spark)对纳税人与税务机关进行补填,保证发票在与纳税人信息、税务机关信息关联时不会因为数据为null而造成数据缺失。最终,将结果数据写入etl_jxfp的临时表中。
此外,在处理发票明细时加入了这样一段语句:
通过该语句在发票明细中加入了名为“无商品明细”的记录,保证发票明细、发票的金额与税额没有缺失,保障后续数据分析的准确性。
2.数据转换
以上一系列的数据清洗,可以有效杜绝因为缺失数据或关联不上造成的数据分析质量问题。接着,就是数据转换与集成。
数据中台的数据来源于不同的业务系统,因此数据格式、计算口径都可能存在差异。当把它们都抽取到数据中台以后,应当将其转换成统一口径,并规范计算口径。譬如,如何识别代开发票,不同的系统有不同的判断逻辑,但经过数据转换以后,可以在表中增加一个“是否代开发票”字段,这样后续的分析业务就不必再去判断了,直接看该字段即可。此外,同样是税务机关代码,有的系统是9位,有的系统是11位,应该将它们都统一成11位。以上这些工作就是数据转换。
3.数据集成
清洗和转换工作完成以后,将相同或者相似的数据都集成在一起。譬如,从各个不同路径采集的纳税人信息,包括纳税人的基础信息、认证信息、核定信息、资格信息,都集成到了纳税人表中;从各个不同路径采集的各种不同的增值税发票,如增值税专票、增值税普票、机动车统一销售发票、电子发票等类型的发票,都统一集成到发票信息表中。它们都来源于不同的业务系统,字段与类型都各不相同。因此,在集成的过程中,需要进行转换或补填,彼此格式一致,并最终存入同一张表中。譬如,其他发票都有发票明细,但机动车统一销售发票没有,因此需要给它补填一条发票明细,商品就是那辆汽车,金额与税额都是那张发票的金额与税额。
在具体设计实现上,就是为每一种发票都编写一个发票与发票明细的SparkSQL程序。它们分别从各自的原始数据中获取,但经过一个SQL语句的转换,最终都存入名为etl_jxfp与etl_jxfp_qd的发票与发票明细临时表中。
本书摘编自《架构真意:企业级应用架构设计方法论与实践》,经出版方授权发布。
相关文章
- 【面试高频题】难度 3/5,设计跳表(数据结构经典考察)
- 大数据Kudu(十):Flink操作Kudu
- 大数据Kudu(十一):ClickHouse与Kudu对比
- 大数据NiFi(一):什么是NiFi
- 三步在 Linux 上安装 JDBC
- MyBatis批量插入几千条数据慎用foreach
- 计算机网络——IP数据报的发送和转发过程
- USB-C数据线改名了!不提版本、只看速度
- 2022年安徽大学考研数据分析(学硕)
- 数据分析架构新变革?Doris Summit 2022 议程首公布!|即刻报名
- 整理了十个经典的Pandas数据查询案例!
- 一图看明白!在DTCC大会,腾讯云数据库专家团说了哪些重点?
- 腾讯云数据库:用一张成绩单和2022说再见!
- 【论文笔记】Automating DBSCAN via Deep Reinforcement Learning
- 强大的文件数据恢复工具,误删、误格式化、丢失,都能找回!
- 使用 pandas 对数据进行移动计算
- 新增MySQL to ClickHouse,Squids DBMotion再添利器
- 精准定位数据差异行,DBMotion 数据库迁移工具再添新功能
- Linux系统(X64)安装Oracle11g完整安装图文教程另附基本操作
- oracle定时器定时清理某张表指定日期前的数据