前面我們介紹了3大類核心轉(zhuǎn)換場景。
【ETL】系列四：核心轉(zhuǎn)換場景—單表間直轉(zhuǎn)
【ETL】系列五：核心轉(zhuǎn)換場景-字段合并與字段拆分
【ETL】系列六：核心轉(zhuǎn)換場景-行轉(zhuǎn)列與列轉(zhuǎn)行
之前的三篇文章器赞，主要針對(duì)來源數(shù)據(jù)進(jìn)行直接映射處理禁筏，并未涉及到來源表數(shù)據(jù)清洗后再同步到目標(biāo)表的場景降宅。今天這個(gè)篇幅，我們來介紹規(guī)格化清洗的轉(zhuǎn)換場景。
希望在這篇文章結(jié)束之后，你可以對(duì)以下問題有進(jìn)一步的理解悬秉。
什么是規(guī)格化清洗？
怎么用kettle對(duì)數(shù)據(jù)進(jìn)行規(guī)格化清洗冰蘑？
kettle的規(guī)格化清洗有哪些不足和泌？如何改進(jìn)？
一祠肥、什么是規(guī)格化清洗武氓？
規(guī)格化就是將一個(gè)屬性取值范圍投射到一個(gè)特定范圍之內(nèi)，以消除數(shù)值型屬性因大小不一而造成挖掘結(jié)果的偏差仇箱。
規(guī)格化清洗應(yīng)用于來源表的數(shù)據(jù)并非直接進(jìn)行轉(zhuǎn)換的應(yīng)用場景县恕，即當(dāng)來源表的數(shù)據(jù)與目標(biāo)表數(shù)據(jù)由于一些格式上的差異產(chǎn)生的不一致，需要經(jīng)過清洗以保證數(shù)據(jù)的規(guī)范性時(shí)剂桥，轉(zhuǎn)換的流程中就會(huì)用到規(guī)格化清洗忠烛，以消除數(shù)據(jù)上的偏差，輸出干凈渊额、規(guī)范的數(shù)據(jù)况木。
接下來垒拢，我們通過具體的實(shí)例來介紹幾類常見的規(guī)格化清洗的規(guī)則旬迹。
1、文本清洗規(guī)則
2求类、數(shù)值清洗規(guī)則
3奔垦、日期清洗規(guī)則
（一）文本清洗規(guī)則
文本清洗規(guī)則用于處理文本字符串的，數(shù)據(jù)庫的字段類型是文本類型尸疆，如CHAR椿猎、VARCHAR惶岭、TEXT等串?dāng)?shù)據(jù)類型。
文本規(guī)格化清洗樣例：
| 清洗規(guī)則 | 規(guī)則描述 | 清洗前數(shù)據(jù) | 清洗后數(shù)據(jù) |
|---|---|---|---|
| 首字母轉(zhuǎn)大寫犯眠，其他小寫 | 當(dāng)字段值包含英文詞：首字母轉(zhuǎn)大寫按灶，其他小寫 | BEIJING TECHNOLOGY CO.,LTD beijing technology co.,ltd |
Beijing Technology Co.,Ltd |
| 去除(數(shù)字,中文)序號(hào) | 當(dāng)字段值為“序號(hào)”+“內(nèi)容”組合，去除首位序號(hào)： 1筐咧、必須包含明確序號(hào)特征 ：. 鸯旁。、, ）】]（）()【】[] 2量蕊、必須包含數(shù)字或中文數(shù)字 如 ：1-999铺罢， 一-九百九十九 |
2、內(nèi)容 2残炮。內(nèi)容 2,內(nèi)容 2】內(nèi)容 2）內(nèi)容 2]內(nèi)容 （2）內(nèi)容 (2)內(nèi)容 【2】內(nèi)容 [2]內(nèi)容 |
內(nèi)容 |
（二）數(shù)值清洗規(guī)則
數(shù)值清洗規(guī)則用于處理數(shù)值的韭赘，數(shù)據(jù)庫的字段類型是數(shù)值類型，如BIT势就、BIGINT泉瞻、FLOAT等數(shù)值數(shù)據(jù)類型。
數(shù)值規(guī)格化清洗樣例：
| 清洗規(guī)則 | 規(guī)則描述 | 清洗前數(shù)據(jù) | 清洗后數(shù)據(jù) |
|---|---|---|---|
| 財(cái)務(wù)類括號(hào)轉(zhuǎn)為負(fù)號(hào) | 當(dāng)字段值包含財(cái)務(wù)類括號(hào)苞冯，將其轉(zhuǎn)為負(fù)號(hào) | (16,725,226.56) | -16,725,226.56 |
| 去除千分位號(hào) | 當(dāng)字段值包含千分位號(hào)瓦灶，去除千分位號(hào) | 16,725,226.56 | 16725226.56 |
（三）日期清洗規(guī)則
數(shù)值清洗規(guī)則用于處理日期和時(shí)間的，數(shù)據(jù)庫的字段類型是數(shù)值類型抱完，如DATE贼陶、DATETIME、TIME等數(shù)值數(shù)據(jù)類型巧娱。
日期規(guī)格化清洗樣例：
| 清洗規(guī)則 | 規(guī)則描述 | 清洗前數(shù)據(jù) | 清洗后數(shù)據(jù) |
|---|---|---|---|
| 統(tǒng)一日期格式Y(jié)YYY-MM-DD | 當(dāng)字段值為日期碉怔，將日期數(shù)據(jù)統(tǒng)一轉(zhuǎn)為YYYY-MM-DD格式 | 2019/11/25 2019年11月25日 2019.11.25 20191125 |
2019-11-25 |
| 獲取系統(tǒng)時(shí)間（精確到秒） | 獲取系統(tǒng)時(shí)間（精確到秒） | 2019-11-25 | 2019-11-25 15:55:00 |
二、怎么用kettle對(duì)數(shù)據(jù)進(jìn)行規(guī)格化清洗禁添？
Kettle里沒有單一的數(shù)據(jù)清洗步驟撮胧，但有很多的步驟組合起來可以完成數(shù)據(jù)清洗的功能老翘。
我們將通過幾種方式來進(jìn)行規(guī)格化清洗的實(shí)操卫键。
操作方式1：通過JavaScript 函數(shù)進(jìn)行數(shù)據(jù)清洗
實(shí)操樣例：將來源數(shù)據(jù)中的字符串首字母轉(zhuǎn)大寫，其他小寫，如清洗前的“BEIJING TECHNOLOGY CO.,LTD”和“beijing technology co.,ltd”清洗為“Beijing Technology Co.,Ltd”。
操作步驟：
1泪蔫、點(diǎn)擊左側(cè)的 核心對(duì)象餐曹，選擇自定義常量數(shù)據(jù)饱狂，進(jìn)行待清洗數(shù)據(jù)的定義。
2物赶、點(diǎn)擊左側(cè)的 核心對(duì)象， 選擇javaScript腳本 宏邮，進(jìn)行字符串函數(shù)的配置埋哟。
變量信息輸入： 由于在上一步“自定義常量數(shù)據(jù)”中已定義元數(shù)據(jù)為“公司英文名”，因此我們只需要在 “java script：”下的輸入框中輸入initCap（var）聲明變量郎汪。
var result = initCap(公司英文名)
3赤赊、點(diǎn)擊左側(cè)的 **核心對(duì)象 ** ，選擇 **Excel輸出 **插件煞赢，定義清洗后數(shù)據(jù)的存儲(chǔ)位置抛计。
以上是一個(gè)通過JavaScript 函數(shù)對(duì)數(shù)據(jù)進(jìn)行清洗的操作方式。
關(guān)于在Kettle中 JavaScript內(nèi)置函數(shù)說明可以參考這篇文章的介紹：
https://blog.csdn.net/u010192145/article/details/102220563
操作方式2：通過字符串替換插件進(jìn)行數(shù)據(jù)清洗
實(shí)操樣例：將來源數(shù)據(jù)中的字符串去除(數(shù)字,中文)序號(hào)照筑，如清洗前的“2吹截、內(nèi)容 2。內(nèi)容 2,內(nèi)容 2】內(nèi)容 2）內(nèi)容 2]內(nèi)容（2）內(nèi)容 (2)內(nèi)容 （2）內(nèi)容 【2】內(nèi)容”清洗為“內(nèi)容”凝危。
操作步驟：
1饭弓、點(diǎn)擊左側(cè)的 核心對(duì)象，選擇自定義常量數(shù)據(jù)媒抠，進(jìn)行待清洗數(shù)據(jù)的定義弟断。
2、點(diǎn)擊左側(cè)的 核心對(duì)象趴生， 選擇字符串替換 阀趴，進(jìn)行字符串替換的配置。
在這里苍匆，需要理解輸入流字段刘急、輸出流字段、使用正則表達(dá)式浸踩、搜索叔汁、使用…替換、設(shè)置為空串、使用字段值替換等的含義据块。
1.輸入流字段：上一步驟中自定義常量數(shù)據(jù)定義的元數(shù)據(jù)名稱码邻，該樣例中輸入流字段為“簡介”；
2.輸出流字段：自己命名另假，用來保存處理后的結(jié)果的字段像屋，可以和輸入流字段保持一致，也可以在命名上做一點(diǎn)區(qū)分边篮，該樣例中的輸出流字段我們定義為“清洗后簡介”己莺；
3.使用正則表達(dá)式：可以選擇使用正則表達(dá)式，搜索匹配范圍會(huì)更加靈活準(zhǔn)確戈轿，這里我們選擇“是”凌受；
4.搜索：就是你希望被替換的字符、字符串思杯，可以是一個(gè)正則表達(dá)式胁艰，針對(duì)待清洗數(shù)據(jù)需要具體數(shù)據(jù)具體分析，這里我們使用了正則表達(dá)式來搜索智蝠，如“\d.[腾么、，杈湾。,】）)[]【】（）()（【[]”解虱；
5、使用…替換：就是你期望用什么值替換被替換的部分漆撞，這該樣例是需要將數(shù)據(jù)和中文的序號(hào)去除殴泰，因此，替換為空值即可浮驳，此處不填內(nèi)容悍汛；
6、設(shè)置為空串：將被選擇的部分用空替換至会；
7离咐、使用字段值替換：可以使用現(xiàn)有的某個(gè)字段的值替換你期望被替換的部分；
8奉件、整個(gè)單詞匹配：搜索范圍是否是整個(gè)單詞宵蛀；
9、大小寫敏感：搜索范圍是否需要區(qū)分大小寫县貌，主要是針對(duì)字母的术陶。
3、點(diǎn)擊左側(cè)的 **核心對(duì)象 ** 煤痕，選擇 **Excel輸出 **插件梧宫，定義清洗后數(shù)據(jù)的存儲(chǔ)位置接谨，查看清洗結(jié)果。
字符串替換是個(gè)比較好用的清洗插件塘匣，對(duì)于上文中提到【財(cái)務(wù)類括號(hào)轉(zhuǎn)為負(fù)號(hào)】脓豪、【去除千分位號(hào)】等數(shù)值清洗都是適用的，此處不做贅述馆铁。
操作方式3：通過SQL函數(shù)進(jìn)行數(shù)據(jù)清洗
實(shí)操樣例：將來源庫A中的【TQ_COMP_INFOCHG（機(jī)構(gòu)信息變動(dòng)表）】的數(shù)據(jù)推送到目標(biāo)數(shù)據(jù)庫B【org_basic_info_chg（機(jī)構(gòu)信息變動(dòng)表）】跑揉，并對(duì)來源表中的日期字段YYYYMMDD（如20191125）格式的值規(guī)格化清洗為YYYY-MM-DD（如2019-11-25）锅睛。
操作步驟：
1埠巨、選擇表輸入并把它拖到右側(cè)的編輯區(qū)中進(jìn)行配置，主要是進(jìn)行自定義SQL語句现拒。
SQL語句中辣垒，通過CAST函數(shù)將需要進(jìn)行日期格式轉(zhuǎn)換的字段進(jìn)行語句的輸入。
SQL語句如下：
SELECT
ID, COMPCODE,CAST(PUBLISHDATE AS DATE) , CHGTYPE, CAST(BEGINDATE AS DATE) , CAST(ENDDATE AS DATE) , BECHG, AFCHG, CHGEXP
FROM TQ_COMP_INFOCHG where COMPCODE='10000001'
2印蔬、選擇插入/更新并把它拖到右側(cè)的編輯區(qū)中進(jìn)行相關(guān)配置勋桶。
3、查看清洗轉(zhuǎn)換結(jié)果侥猬。
4例驹、轉(zhuǎn)換成功后，也可以檢查本地?cái)?shù)據(jù)庫退唠，查看數(shù)據(jù)是否推送成功鹃锈，是否將日期字段進(jìn)行正確清洗。
我們在使用SQL語句時(shí)瞧预，有一個(gè)重要的內(nèi)容就是使用數(shù)據(jù)處理函數(shù)屎债，SQL支持利用函數(shù)來處理數(shù)據(jù)，函數(shù)一般是在數(shù)據(jù)上執(zhí)行的垢油，它給數(shù)據(jù)的轉(zhuǎn)換和處理提供了方便盆驹，也是規(guī)格化清洗的常見的實(shí)現(xiàn)方式。
操作方式4：通過獲取系統(tǒng)信息插件進(jìn)行數(shù)據(jù)清洗
實(shí)操樣例：獲取系統(tǒng)時(shí)間（精確到秒）
操作步驟：
1滩愁、點(diǎn)擊左側(cè)的 核心對(duì)象躯喇，選擇獲取系統(tǒng)信息插件，選擇要獲取的信息類型硝枉。
選擇獲取系統(tǒng)日期后玖瘸，得到的日期格式為YYYY/MM/DD HH:MM:SS XXX，我們需要將改格式轉(zhuǎn)換為 YYYY/MM/DD HH:MM:SS檀咙。
2雅倒、點(diǎn)擊左側(cè)的 核心對(duì)象，選擇字段選擇插件弧可，進(jìn)行日期格式的轉(zhuǎn)換蔑匣。
如截圖所示劣欢，在元數(shù)據(jù)tab中進(jìn)行字段類型的變更，將Date 類型的格式定義為yyyy-MM-dd HH:mm:ss裁良。
3凿将、點(diǎn)擊左側(cè)的 核心對(duì)象 ，選擇 Excel輸出插件价脾，定義清洗后數(shù)據(jù)的存儲(chǔ)位置牧抵，并查看清洗后的日期數(shù)據(jù)。
以上是4類規(guī)格化清洗的方式侨把，除了已介紹的方式犀变，kettle還提供了其他的規(guī)格化清洗的方法，在此就不一一描述了秋柄。
三获枝、 kettle的規(guī)格化清洗有哪些不足？如何改進(jìn)骇笔？
從上述介紹的一些規(guī)格化清洗的方式中省店，相信大家一方面覺得kettle的強(qiáng)大，另一方面也會(huì)發(fā)現(xiàn)在操作會(huì)相對(duì)繁瑣笨触，學(xué)習(xí)成本較高懦傍。
Kettle的規(guī)格化清洗有哪些不足：
1、多種清洗方式芦劣，增加學(xué)習(xí)成本：不同清洗要求的數(shù)據(jù)需要使用不同的清洗方式粗俱，在kettle中缺少聚合、交互統(tǒng)一的清洗模塊持寄；
2源梭、多個(gè)清洗規(guī)則無法快速配置：實(shí)際業(yè)務(wù)中，針對(duì)來源表的清洗字段稍味，需要用到多個(gè)清洗規(guī)則废麻，在kettle中無法快速配置；
3模庐、缺少批量操作：清洗插件中的操作烛愧，缺少批量操作功能，影響配置效率掂碱；
4怜姿、清洗規(guī)則缺少繼承關(guān)系和互斥關(guān)系：數(shù)據(jù)表中的清洗規(guī)則部分是同通用的，可以適用于全庫數(shù)據(jù)疼燥，部分清洗規(guī)則之間是互斥的沧卢，互斥規(guī)則會(huì)導(dǎo)致清洗結(jié)果無效，這在kettle中無法有效地滿足具體的業(yè)務(wù)需求醉者；
5但狭、缺失清洗規(guī)則之外的管理功能：缺失以來源表等維度的清洗規(guī)則的增刪查改和操作日志等管理功能披诗。
規(guī)格化清洗的改進(jìn)方向：
在實(shí)際的業(yè)務(wù)處理過程中，上述提到的kettle規(guī)格化清洗的不足立磁，會(huì)導(dǎo)致ETL模型配置繁雜呈队，耗時(shí)耗力，且無法有效地管理積累的規(guī)格化清洗規(guī)則唱歧。對(duì)于ETL工具產(chǎn)品來說宪摧，是明顯的短板，需要去不斷優(yōu)化颅崩，以下就kettle規(guī)格化清洗的不足几于，分享幾個(gè)改進(jìn)方向。
1挨摸、整合kettle已有的清洗規(guī)則：kettle的清洗規(guī)則已經(jīng)很全面了孩革，但是缺少規(guī)則的系統(tǒng)性整合岁歉，也缺少針對(duì)具體業(yè)務(wù)的相對(duì)定制化的清洗規(guī)則得运，可以從數(shù)據(jù)業(yè)務(wù)中，抽取出一類業(yè)務(wù)的清洗規(guī)則锅移，減少業(yè)務(wù)人員對(duì)具體規(guī)則的配置熔掺；
2、支持自定義規(guī)則集：一類待清洗數(shù)據(jù)使用一類已定義好的清洗規(guī)則集非剃，減少單個(gè)規(guī)則的選擇和配置置逻；
3、增加批量操作：對(duì)于清洗規(guī)則的配置备绽、規(guī)則集的定義券坞、規(guī)則執(zhí)行方式的設(shè)置都需要在交互上支持批量操作；
4肺素、增加清洗服務(wù)的管理功能：對(duì)于具體的業(yè)務(wù)來說恨锚，需要支持?jǐn)?shù)據(jù)表、規(guī)則名稱等維度的管理功能倍靡，可以按照業(yè)務(wù)所需的維度猴伶，進(jìn)行清洗規(guī)則的查看、新增塌西、修改和刪除他挎，當(dāng)然，對(duì)于規(guī)則的操作日志都需要完整地被記錄下來捡需。
除了上述的一些改進(jìn)方向之外办桨，還有很多可以挖掘改進(jìn)的點(diǎn)，這些就需要根據(jù)具體業(yè)務(wù)來不斷梳理和實(shí)現(xiàn)了站辉。
總之呢撞，作為產(chǎn)品經(jīng)理來說贸街，你需要用戶在使用產(chǎn)品時(shí)，能夠滿足多＠晗唷薛匪！快！脓鹃！好Ｒ菁狻！爽Ｈ秤摇娇跟！的需求。也就是：
支持的清洗規(guī)則多太颤！
配置和運(yùn)行的速度快苞俘！
產(chǎn)品的體驗(yàn)好！
才能用的爽龄章！
以上吃谣。
