21分鐘教會你分析MaxCompute賬單

背景

阿裡雲大計算服務MaxCompute是一款商業化的大資料分析平台，其計算資源有預付費和後付費兩種計費方式。并且産品每天按照project為次元進行計量計費（賬單基本情況下會第二天6點前産出）。本文使用的為雲上客戶真實資料，故在下文中的截圖都mask掉了。

關于MaxCompute計量計費說明，詳見官方文檔：

21分鐘教會你分析MaxCompute賬單21分鐘教會你分析MaxCompute賬單

但是通常情況下，我們在資料開發階段或者在上線前夕會發下賬單有波動（通常情況下為增大），其實使用者首先可以通過自助的方式來分析賬單波動情況，再倒逼自己的作業進行優化。阿裡雲費用中心就是一個很好的通道，阿裡雲所有商業化收費的産品都可以在其中下載下傳費用明細。

擷取賬單資訊

通常您需要使用主賬号檢視賬單詳情。如果您需要使用子賬号檢視賬單資訊，請首先參考

費用中心RAM配置政策

行子賬号授權。

step1：使用主賬号或者被授權的RAM子賬号來登入

阿裡雲管控台

。

step2：右上角進入費用中心。

step3：在費用中心-消費記錄-消費明細中，選擇産品和賬單日期。

包年包月中的後付費是指項目開通包年包月計算計費模式後，還産生的存儲、下載下傳對應的費用（存儲、下載下傳費用隻有後付費）。

step4：為了友善批量分析資料，我們選擇下載下傳使用記錄csv檔案在本地分析。

下載下傳csv檔案如下，可以在本地打開進行分析。

--csv的表頭
項目編号,計量資訊編号,資料分類,存儲（Byte）,結束時間,SQL/互動式分析,讀取量（Byte）,SQL複雜度,公網上行流量（Byte）,公網下行流量（Byte）,MR/Spark作業計算（Core*Second）,SQL讀取量_通路OTS（Byte）,SQL讀取量_通路OSS（Byte）,開始時間,計算資源規格,DataWorks排程任務ID

上傳賬單明細至MaxCompute

使用記錄明細字段解釋：

項目編号：目前賬号下或子賬号對應的主賬号的MaxCompute project清單。
計量資訊編号：其中會包含存儲、計算、上傳和下載下傳的計費資訊編号，SQL為instanceid，上傳和下載下傳為Tunnel sessionid。
資料分類：Storage（存儲）、ComputationSql（計算）、UploadIn（内網上傳）、UploadEx（外網上傳）、DownloadIn（内網下載下傳）、DownloadEx（外網下載下傳）、spark（計算）、LightningQuery（計算）。按照計費規則其中隻有紅色為實際計費項目。
開始時間/結束時間：按照實際作業執行時間進行計量，隻有Storage是按照每個小時取一次資料。
存儲（Byte）：每小時讀取的存儲量機關為Byte。
SQL 讀取量（Byte）：SQL計算項，每一次SQL執行時SQL的input資料量，機關為Byte。
SQL 複雜度（Byte）：每次執行SQL的複雜度，為SQL計費因子之一。
公網上行流量（Byte），公網下行流量（Byte）：分别為公網上傳和下載下傳的資料量，機關Byte。
MR作業計算（CoreSecond）：MR作業的計算時機關為coresecond，需要轉換為計算時hour。
SQL讀取量_通路OTS（Byte）,SQL讀取量_通路OSS（Byte）：外部表實施收費後的讀取資料量，機關Byte。
計算資源規格：暫時為空。
DataWorks排程任務ID：通過DataWorks排程執行的節點ID，可以通過ID在運維中心中定位到具體某個節點。

① 确認CSV檔案資料，尤其是列分隔符等（推薦使用UE）。

資料以逗号分隔，且單元格值都帶有雙引号。

② 資料預處理：替換掉文檔所有雙引号，以友善使用Tunnel等上傳工具。

替換為不用填寫。直接點選全部替換。

③ 建立MaxCompute表，存儲下載下傳的消費明細。

DROP TABLE IF EXISTS maxcomputefee ;

CREATE TABLE IF NOT EXISTS maxcomputefee 
(
    projectid STRING COMMENT '項目編号'
    ,feeid STRING COMMENT '計費資訊編号'
    ,type STRING COMMENT '資料分類，包括Storage、ComputationSQL、DownloadEx等'
    ,storage BIGINT COMMENT '存儲量'
    ,endtime DATETIME COMMENT '結束時間'
    ,computationsqlinput BIGINT COMMENT '輸入資料量'
    ,computationsqlcomplexity DOUBLE COMMENT 'sql複雜度'
    ,uploadex BIGINT COMMENT '公網上行流量Byte'
    ,download BIGINT COMMENT '公網下行流量Byte'
    ,cu_usage DOUBLE COMMENT 'MR計算時*second'
    ,input_ots BIGINT COMMENT '通路OTS的資料輸入量'
    ,input_oss BIGINT COMMENT '通路OSS的資料輸入量'
    ,starttime DATETIME COMMENT '開始時間'
    ,guige string COMMENT '計算資源規格'
    ,nodeid string COMMENT '對應DataWorks節點ID'
)
;

④ Tunnel上傳資料，具體Tunnel的配置詳見

odps@ sz_mc>tunnel upload /Users/yangyi/Desktop/ODPS_2019-01-12_2019-01-14.csv maxcomputefee -c "UTF-8" -h "true" -dfp "yyyy-MM-dd HH:mm:ss";

當然使用者也可以通過DataWorks資料導入的功能來進行，具體詳見操作步驟

⑤ 驗證資料。

通過SQL分析賬單資料

1、分析SQL費用

雲上客戶使用MaxCompute，95%的使用者通過SQL即可滿足需求，SQL也在消費成長中占據了絕大部分。

SQL費用=一次SQL計算費用 = 計算輸入資料量 × SQL複雜度 × 0.3元/GB

--分析SQL消費，按照SQL進行排行
SELECT  to_char(endtime,'yyyymmdd') as ds,feeid as instanceid
        ,projectid
        ,computationsqlcomplexity  --複雜度
        ,SUM((computationsqlinput / 1024 / 1024 / 1024)) as computationsqlinput  --資料輸入量GB
        ,SUM((computationsqlinput / 1024 / 1024 / 1024)) * computationsqlcomplexity * 0.3 AS sqlmoney
FROM    maxcomputefee
WHERE   TYPE = 'ComputationSql'
AND to_char(endtime,'yyyymmdd') >= '20190112'
GROUP BY to_char(endtime,'yyyymmdd'),feeid
         ,projectid
         ,computationsqlcomplexity
ORDER BY sqlmoney DESC 
LIMIT   10000
;

--查詢結果--

根據此段SQL執行結果可以得到如下結論：

大作業可以優化的點：**是否可以減小資料讀取量、降低複雜度來優化費用成本。
也可以按照ds字段（按照天）進行彙總，分析某個時間段内的SQL消費金額走勢。比如利用本地excle或雲上QuickBI等工具繪制折線圖等方式，更直覺的反應作業的趨勢。
拿到具體的instanceid，在console或者DataWorks腳本中進行 wait instanceid; 檢視具體作業和SQL。

21分鐘教會你分析MaxCompute賬單21分鐘教會你分析MaxCompute賬單

随即在浏覽器中打開logview的url位址（關于logview的介紹詳見

）：

從logview中擷取DataWorks節點名稱：

在logview中打開SourceXML可以檢視到具體執行資訊，如SKYNET_NODENAME表示DataWorks的節點名稱（當然隻有被排程系統執行的作業才有值，臨時查詢為空，如下圖所示）。拿到節點名稱可以快速的在DataWorks找到該節點進行優化或檢視責任人。

2、分析作業增長趨勢

一般情況下費用的增長背後其實是作業量的暴漲，可能是重複執行，也可能是排程屬性配置的不是很合理。

--分析作業增長趨勢
SELECT  TO_CHAR(endtime,'yyyymmdd') AS ds
        ,projectid
        ,COUNT(*) AS tasknum
FROM    maxcomputefee
WHERE   TYPE = 'ComputationSql'
AND     TO_CHAR(endtime,'yyyymmdd') >= '20190112'
GROUP BY TO_CHAR(endtime,'yyyymmdd')
         ,projectid
ORDER BY tasknum DESC
LIMIT   10000
;

--執行結果--

從執行結果可以看出來12-14日送出到MaxCompute且執行成功的作業數的波動趨勢。

3、分析存儲費用

存儲費用的計費規則相對來說比較複雜，因為下載下傳到的明細是每個小時取一次資料。按照MaxCompute存儲計費規則，會整體24小時求和然後平均之後的值再階梯收費。具體詳見官網

--分析存儲費用
SELECT  t.ds
        ,t.projectid
        ,t.storage
        ,CASE    WHEN t.storage < 0.5 THEN 0.01
                 WHEN t.storage >= 0.5 AND t.storage <= 100 THEN t.storage*0.0192
                 WHEN t.storage > 100 AND t.storage <= 1024 THEN (100*0.0192+(t.storage-100)*0.0096)
                 WHEN t.storage > 1024 AND t.storage <= 10240 THEN (100*0.0192+(1024-100)*0.0096+(t.storage-1024)*0.0084)
                 WHEN t.storage > 10240 AND t.storage <= 102400 THEN (100*0.0192+(1024-100)*0.0096+(10240-1024)*0.0084+(t.storage-10240)*0.0072)
                 WHEN t.storage > 102400 AND t.storage <= 1048576 THEN (100*0.0192+(1024-100)*0.0096+(10240-1024)*0.0084+(102400-10240)*0.0072+(t.storage-102400)*0.006) 
         END storage_fee
FROM    (
            SELECT  to_char(starttime,'yyyymmdd') as ds
                    ,projectid
                    ,SUM(storage/1024/1024/1024)/24 AS storage
            FROM    maxcomputefee
            WHERE   TYPE = 'Storage'
            and to_char(starttime,'yyyymmdd') >= '20190112'
            GROUP BY to_char(starttime,'yyyymmdd')
                     ,projectid
        ) t
ORDER BY storage_fee DESC
;

根據計算結果可以分析得出結論：

存儲在13日為最高有一個增長的過程，但是在14日是有降低。
存儲優化，建議表設定生命周期，删除長期不使用的臨時表等。

4、分析下載下傳費用

對于公網或者跨Region的資料下載下傳，MaxCompute将按照下載下傳的資料大小進行計費。

計費公式為：一次下載下傳費用=下載下傳資料量*0.8元/GB。

--分析下載下傳消費明細
SELECT  TO_CHAR(starttime,'yyyymmdd') AS ds
        ,projectid
        ,SUM((download/1024/1024/1024)*0.8) AS download_fee
FROM    maxcomputefee
WHERE   type = 'DownloadEx'
AND     TO_CHAR(starttime,'yyyymmdd') >= '20190112'
GROUP BY TO_CHAR(starttime,'yyyymmdd')
         ,projectid
ORDER BY download_fee DESC
;

按照執行結果也可以分析出某個時間段内的下載下傳費用走勢。另外可以通過tunnel show history檢視具體曆史資訊，具體指令詳見

以下幾種計算作業與SQL類似，按照

官方計費文檔

編寫SQL即可。

5、分析MapReduce作業消費

MR任務當日計算費用=當日總計算時*0.46元

--分析MR作業消費
SELECT  TO_CHAR(starttime,'yyyymmdd') AS ds
        ,projectid
        ,(cu_usage/3600)*0.46 AS mr_fee
FROM    maxcomputefee
WHERE   type = 'MapReduce'
AND     TO_CHAR(starttime,'yyyymmdd') >= '20190112'
GROUP BY TO_CHAR(starttime,'yyyymmdd')
         ,projectid
         ,cu_usage
ORDER BY mr_fee DESC
;

6、分析外部表作業（OTS和OSS）

SQL外部表功能計費規則：一次SQL計算費用=計算輸入資料量×SQL複雜度×0.03元/GB

--分析OTS外部表SQL作業消費
SELECT  TO_CHAR(starttime,'yyyymmdd') AS ds
        ,projectid
        ,(input_ots/1024/1024/1024)*1*0.03 AS ots_fee
FROM    maxcomputefee
WHERE   type = 'ComputationSql'
AND     TO_CHAR(starttime,'yyyymmdd') >= '20190112'
GROUP BY TO_CHAR(starttime,'yyyymmdd')
         ,projectid
         ,input_ots
ORDER BY ots_fee DESC
;

--分析OSS外部表SQL作業消費
SELECT  TO_CHAR(starttime,'yyyymmdd') AS ds
        ,projectid
        ,(input_oss/1024/1024/1024)*1*0.03 AS ots_fee
FROM    maxcomputefee
WHERE   type = 'ComputationSql'
AND     TO_CHAR(starttime,'yyyymmdd') >= '20190112'
GROUP BY TO_CHAR(starttime,'yyyymmdd')
         ,projectid
         ,input_oss
ORDER BY ots_fee DESC
;

7、分析Lightning查詢費用

一次Lightning查詢費用 = 查詢輸入資料量 * 單價（0.03元/GB）

SELECT  to_char(endtime,'yyyymmdd') as ds,feeid as instanceid
        ,projectid
        ,computationsqlcomplexity
        ,SUM((computationsqlinput / 1024 / 1024 / 1024)) as computationsqlinput
        ,SUM((computationsqlinput / 1024 / 1024 / 1024)) * computationsqlcomplexity * 0.03 AS sqlmoney
FROM    maxcomputefee
WHERE   TYPE = 'LightningQuery'
--AND to_char(endtime,'yyyymmdd') >= '20190112'
GROUP BY to_char(endtime,'yyyymmdd'),feeid
         ,projectid
         ,computationsqlcomplexity
ORDER BY sqlmoney DESC 
LIMIT   10000
;

8、分析Spark計算費用

Spark任務當日計算費用 = 當日總計算時 * 0.66元（人民币）

--分析MR作業消費
SELECT  TO_CHAR(starttime,'yyyymmdd') AS ds
        ,projectid
        ,(cu_usage/3600)*0.66 AS mr_fee
FROM    maxcomputefee
WHERE   type = 'spark'
AND     TO_CHAR(starttime,'yyyymmdd') >= '20190112'
GROUP BY TO_CHAR(starttime,'yyyymmdd')
         ,projectid
         ,cu_usage
ORDER BY mr_fee DESC
;

總結

MaxCompute産品消費的增長（暴漲）往往背後都是由于作業量的大幅度提升，要優化自己的費用成本，首選要知道自己SQL等作業中存在什麼問題，要優化具體哪一個SQL。本文期望能夠給予大家一些幫助。更多關于費用成本優化的文章可以詳見，雲栖社群《

幫助企業做好MaxCompute大資料平台成本優化的最佳實踐

》和《

衆安保險如果優化自己的MaxCompute費用成本實踐

》。

21分鐘教會你分析MaxCompute賬單21分鐘教會你分析MaxCompute賬單

21分鐘教會你分析MaxCompute賬單

背景

擷取賬單資訊

上傳賬單明細至MaxCompute

通過SQL分析賬單資料

1、分析SQL費用

2、分析作業增長趨勢

3、分析存儲費用

4、分析下載下傳費用

5、分析MapReduce作業消費

6、分析外部表作業（OTS和OSS）

7、分析Lightning查詢費用

8、分析Spark計算費用

總結

繼續閱讀

Sql優化一：sql語句優化

SQL優化SQL語句優化的目的

Nacos 2.0 更新前後性能對比壓測

JAVA高效程式設計指南

尚矽谷—韓順平—圖解 Java設計模式（結構型）（55～）

Storm編譯打包過程中遇到的一些問題及解決方法

關于SQL語言

SQL語言基礎：常用的資料查詢語句

MapReduce的幾個企業級經典面試案例MapReduce的幾個企業級經典面試案例

9.spark Core 進階2--Cashe

淺談企業活動中進行資料分析的重要性

neo4j之cypher使用文檔

Ambari介紹和架構原理

NOSQL安全攻擊

sqlServer根據經緯查距離

win10本地scala和spark安裝安裝scala安裝spark