772
技術社區[雲棲]
ODPS SQL費用估算與控製
MaxCompute ODPS SQL費用估算與控製
作者：顧飛
一 需求背景
費用挑戰
ODPS老用戶應該都了解過其計費方式，如果不清楚計費方式，可以參考阿裏雲文章：https://help.aliyun.com/document_detail/27989.html?spm=5176.doc27833.6.701.8vl39E 。阿裏雲本身提供了CU（固定資源）和計算兩種計費方式，而我們公司在BI上雲的過程中使用的是采雲間，它僅支持SQL計算計費方式步支持CU方式，而保險行業又是一個基於數據才能工作的行業， 每個部門都有自己的數據需求，在數據倉庫之上的數據用戶和應用又是非常的多，這使得我們在ODPS SQL費用控製上遇到了很大的挑戰。剛開始3個月，我們的ODPS費用都是差好幾倍，這與我們的業務增長兩完全不符合。 在使用ODPS的前四個月我們麵對如此大的費用差距，而我們的SQL 每個月都有幾萬次執行次數，幾乎無從下手。
具體問題
1 遇到費用差距如此之大，
那我們就要去找到那些費用非常高的SQL， 我們根據阿裏雲提供的SQL 執行日誌，發現很多SQL 運行一次要數千元， 很多都是LEFT JOIN沒有做到分區過濾造成，比如：a left outer join b on a.id = b.id where a.pt= "${date}" and b.pt= "${date}",對於b表的分區過濾是失效的，造成sql的全表掃描，而需要將其改成： a left outer join (select * from b where b.pt= "${date}" ) b on a.id = b.id where pt where a.pt= "${date}" 這樣寫才能做到過濾分區
2 還有一些SQL 有數千條，都是100元以上的費用， 主要原因由於每個SQL都是從最明細的底層表去抽取，造成SQL 的複雜度和輸入數據量都非常得高。
二 解決方案
對於這樣局麵我們提供了三種解決方案，來改進這樣的局麵。
方案一： SQL 簽名
大家用過ORACLE 應該都知道AWR報表，裏麵可以根據同一個SQL ID在不同時間維度下進行SQL執行次數、平均執行時長、耗CPU率來分析性能問題，同樣這對ODPS SQL同樣適用，如下：
每個SQL都需要有一個唯一的簽名才能做到， 我們的做法是用MD5算法進行哈希，生成一個128位的Hash Value，其中低32位作為HASH VALUE顯示，SQL_ID則取了後64位，你可以用任何語言來實現。當這個SQL簽名出來以後，我們很容易找到那些執行次數多， 數據量大，SQL複雜度高的SQL，給我們SQL 費用優化上帶來了極大的便利。
方案二： SQL費用估算器
接下來我們需要一個SQL估算器，我們每寫完一個SQL都需要去估算下多少錢，我們才能上線，ODPS計算計費公式為：一次SQL計算費用 = 計算輸入數據量 * SQL複雜度 * SQL價格， 那麼我就需要用一個WEB界麵來實現這個公式。 非常感覺阿裏雲提供了ODPS sdk，使得用戶可以構建給予ODPS的SAAS服務。
下麵是我的工程目錄：
其中最核心的API調用如下，SqlCostTask 這個函數就能獲取（計算輸入數據量 * SQL複雜度 * SQL價格）這三個變量了：
估算界麵：
方案三：在數據倉庫之上構建匯總層
在我們知道哪些SQL費用比較高以後，我們就可以有針對性地優化， 最好的方式就是根據需求的共性在DW 層之上建立一層匯總層， 很報表和對帳MaxCompute最佳實踐
最後更新：2017-07-17 16:44:20