原創(chuàng)公眾號文章《Easy Sql讓ETL開發(fā)變輕松》- 作者：陽沁珂

ETL任務(wù)的搭建是數(shù)據(jù)型項目的必要，但是在搭建ETL任務(wù)時，你是否有過這樣的煩惱孩饼？

• 多種數(shù)據(jù)庫平臺連接，使得連接的管理變得混亂竹挡，更改數(shù)據(jù)庫連接方式的時候需要改大量散步各處的代碼镀娶。
• SQL的代碼整潔度不夠，在不同的SQL文件中存在大量的重復(fù)語句揪罕。
• SQL能做的處理有限梯码，大部分時候?qū)τ跀?shù)據(jù)還需要銜接額外的處理，使得整個ETL流程不清晰好啰。
• 沒有l(wèi)og和debug的工具

公司自主研發(fā)的開源代碼Easy SQL (https://github.com/easysql/easy_sql)使用python搭建了一套工具系統(tǒng)忍些，使得上述問題能被輕松解決。下面就這幾個方面講解一下如何使用Easy SQL來加速ETL的搭建與管理坎怪。

一鍵換數(shù)據(jù)庫

在設(shè)計上罢坝，雖然可擴展的數(shù)據(jù)庫后端不同，但是Easy SQL的process模塊是通用的搅窿。這就意味著嘁酿，只需將所需的數(shù)據(jù)庫后端引擎?zhèn)魅雙rocess模塊，即可完成數(shù)據(jù)庫后端的轉(zhuǎn)換男应。如下代碼所示闹司，對于sparkSQL的后端，只需依據(jù)spark session創(chuàng)建spark backend對象沐飘，而對于使用SQLAlchemy直接連接的關(guān)系型數(shù)據(jù)庫游桩，則創(chuàng)建RDB Backend對象。目前支持的后端引擎有：SparkSQL耐朴，Clickhouse借卧，Postgre，Bigquery筛峭。通過這樣的解耦铐刘，可以實現(xiàn)一鍵切換數(shù)據(jù)庫連接，其執(zhí)行邏輯由processor封裝好再進行調(diào)用影晓。

Template組裝SQL

在項目中镰吵，我們經(jīng)常強調(diào)代碼的整潔性檩禾，但是容易忽略ETL的執(zhí)行SQL也是代碼的一部分，SQL的整潔性低會給項目帶來不必要的混亂疤祭。舉個例子盼产，許多項目需要被執(zhí)行的SQL文件單獨放在一個文件里，并且不同的SQL中可能存在大量重復(fù)的語句戏售，比如where的過濾條件谓传，或者join的對應(yīng)約束芹关。雖然這樣的SQL文件簡單直接明了，但是設(shè)想一個場景如果我們的數(shù)據(jù)庫表列名改變侥衬，導(dǎo)致所有相關(guān)的join與where都需要被修改轴总。這時，我們并不知道在大量的SQL文件中有哪些文件是調(diào)用了修改列的功偿，甚至需要開發(fā)人員一個一個文件查看排查往堡，這無疑會使得代碼變得沉重難以修改并且降低項目的開發(fā)速度虑灰。當(dāng)然這個問題已經(jīng)被許多的開發(fā)人員發(fā)現(xiàn)，他們有在實踐中自定義template模塊來加強對SQL的管理颤诀。但是不同項目的管理方法并沒有統(tǒng)一对湃，這又增加了on boarding成員的學(xué)習(xí)成本拍柒。因為這時讀懂一個ETL，不僅僅需要懂SQL的調(diào)用剧包，還需要了解單屬于這一個項目的template系統(tǒng)是怎樣運作來管理和生成執(zhí)行語句的。

我推薦使用Easy SQL中的template功能一铅，因為他通過簡易的語句快速組裝SQL潘飘。在Easy SQL中，一份SQL執(zhí)行文件卜录，不是單獨的一個SQL語句艰毒，而是一整個的執(zhí)行邏輯丑瞧。其中語句的功能除了一般的增刪改查蜀肘，還有新加入聲明變量，聲明模版等功能西乖，其中每個語句的作用获雕，由他所在行上方的target定義轿偎。一個應(yīng)用場景如下方的例子中，需要將兩張表的部分列數(shù)據(jù)使用union 的方法select萝玷，如果每一個select都寫出需要的列名會使得代碼變得冗余球碉。但是在Easy SQL里睁冬，我們將所需要的列名放入了名叫dim_cols的template中，在之后的語句中使用@{dim_cols}代替了列名直奋。通過對template的調(diào)用脚线，大幅度提高代碼整潔性弥搞，減少不必要的重復(fù)語句。

除了聲明template以外船逮，Easy SQL的變量聲明功能也使得ETL變得更加靈活易用挖胃。一個應(yīng)用場景承耿，假設(shè)我們有一張規(guī)則表記錄閾值伪煤，一張數(shù)據(jù)表抱既。并且情境下不允許我們使用join，要知道有哪些數(shù)據(jù)超過了閾值蚀之，我們需要先從規(guī)則表得到閾值再到數(shù)據(jù)表中做過濾足删。一般需要兩個SQL锁右，并且使用第一個SQL的執(zhí)行結(jié)果來動態(tài)生成第二個SQL的執(zhí)行過濾條件咏瑟。在這個時候码泞，整個執(zhí)行過程由于需要額外的處理來生成下一個執(zhí)行語句而割裂開了。在代碼中领铐，整體的可讀性會大幅降低，因為開發(fā)人員需要到多個文件中去理解生成的過程呐馆。而Easy SQL的動態(tài)組建功能汹来，將所需的信息拼接在一個文件之中改艇。當(dāng)一個執(zhí)行語句的目標(biāo)被定義為variables谒兄，該語句的執(zhí)行結(jié)果會變成變量名與變量值儲存到內(nèi)存之中承疲。其中變量名是由select as { }預(yù)設(shè)的燕鸽，變量值由執(zhí)行結(jié)果產(chǎn)生。在后續(xù)的SQL的任意位置中使用這個變量名御滩，這個變量名會被替代為變量值之后再被執(zhí)行削解。對比之下氛驮，在使用了easy sql時济似，執(zhí)行過程不再是被割裂的，在一個文件中開發(fā)人員就能清晰得了解到整個語句的生成邏輯磷脯。

Function流程銜接

在上面所描述的情形中娩脾，我們將一種拼接的需求銜接了起來，使得代碼變得整體化俩功。但是通常還有另外一種情況需要將執(zhí)行割裂開來，那就是需要SQL語句以外的python模塊的一些方法之后再繼續(xù)SQL邏輯诡蜓。在這里可以使用Easy SQL熬甫，其function功能十分強大，使用者自定義任意的python函數(shù)蔓罚，將其注冊在processor之中后椿肩，既可以在SQL中調(diào)用。舉個例子豺谈，我們自己定義一個簡單的相加的方法在easy SQL中直接使用（如下方左圖）郑象。這種function的調(diào)用還能用于控制流程（如下方右圖），在SQL的處理當(dāng)中穿插python的處理模塊茬末。其優(yōu)勢是讓我們的流程在一個文件中一目了然厂榛。

除此之外，Easy SQL的功能還可以在SQL語句較為復(fù)雜的時候丽惭，使用function來生成執(zhí)行語句击奶。比如柜砾，在select的時候喜爷，我們想要過濾掉不需要的列名，留下剩余的列诅挑。在寫SQL的時候我們并不想明確寫出列名没龙，而是通過function的調(diào)用來幫助我們生成需要的語句解滓。比如下面的例子中的function溪王，就是通過在被調(diào)用時執(zhí)行select語句查找table的所有列，并且排除掉不需要的列，返回正確的select中的語句。這種方法可以大幅提高我們的SQL整潔性嫂易，不再需要枚舉所有的需要的列名缕允，而且避免因為table的列名改變而需要改變大量的select語句。

Log 與debug的工具

日志的記錄是項目健康必不可少的清單买置，一份好的日志幫助開發(fā)人員在遇錯的時候準(zhǔn)確得把握原因脆栋。但是通常的SQL執(zhí)行缺少統(tǒng)一的日志，往往都是在代碼中生成日志信息。但是這樣的日志處理也是一種割裂，因為開發(fā)人員只看SQL文件并不能知道有哪些信息被log記錄了，而是需要翻看代碼去了解合住。Easy SQL提供的log工具能在SQL文件中隨時指定需要被log的信息僚害。假設(shè)一個情景蹄胰，我們需要檢查order表的數(shù)據(jù)量是否達(dá)標(biāo)帮坚，并且將信息放入到log系統(tǒng)之中纫普。easy sql通過封裝到模塊拳锚，我們可以直接用target=log來控制需要被log出的信息，其中l(wèi)og之后的名字是會被一起log出的message用于定位杆烁。除了信息的log之外兔魂，還可以使用target=check來實現(xiàn)一個核查的功能析校。這樣的設(shè)計，讓log與debug的指定信息與SQL位于同一份文件之中铜涉，整個代碼更加得易讀易懂骄噪。

總結(jié)

Easy SQL致力于將ETL變得更加容易輕松易于管理滔韵，使得開發(fā)人員能將更多的時間用于產(chǎn)品的研發(fā)之中宴卖。目前項目剛剛開源，還在繼續(xù)迭代加入新的功能更哄，近期正在結(jié)合SQL Fluff工具實現(xiàn)自動排查SQL代碼整潔性。