作者:石玉陽 人力家 高級數(shù)據(jù)研發(fā)工程師

　　業(yè)務簡介

　　人力家是由阿里釘釘和人力窩共同投資成立,幫助客戶進入人力資源數(shù)字化,依靠產(chǎn)品技術創(chuàng)新驅(qū)動戰(zhàn)略的互聯(lián)網(wǎng)公司。公司主要提供包括人事管理、薪酬管理、社保管理、增值服務在內(nèi)的人力資源SaaS服務,加速對人力資源領域賦能,實現(xiàn)人力資源新工作方式。目前已服務電子商務、零售服務等領域的多行業(yè)客戶。

　　人力家是一家典型的創(chuàng)業(yè)公司,目前處于一個競爭激烈的市場環(huán)境中,公司具有多產(chǎn)品性質(zhì),每個產(chǎn)品的數(shù)據(jù)具有獨立性,同時為了配合內(nèi)部CRM數(shù)據(jù)需求,更好地把數(shù)據(jù)整合,對于數(shù)倉團隊來說是一個不小的挑戰(zhàn),對于數(shù)倉團隊要求的是穩(wěn),準,及時響應。需要數(shù)倉團隊既要滿足內(nèi)部的數(shù)據(jù)需求,也需要在計算的成本上實現(xiàn)優(yōu)化。

　　業(yè)務痛點

　　MaxCompute作為一款優(yōu)秀的大數(shù)據(jù)產(chǎn)品,其不僅可以高性價比分析處理海量數(shù)據(jù),同時MaxCompute支持開發(fā)接口和生態(tài),為數(shù)據(jù)、應用遷移、二次開發(fā)提供靈活性。QuickBI可以直連MaxCompute產(chǎn)出報表數(shù)據(jù)供公司內(nèi)部分析、統(tǒng)計、決策。因為公司開通的MaxCompute是按量付費規(guī)格,所以計算任務和QuickBI 報表每次不同的查詢都會耗費計算資源導致MaxCompute計算費用增加,在過去的一段時間,MaxCompute每個月的成本波動較大,不符合期望值,且不能有效、及時的發(fā)現(xiàn)一些高成本sql和多頻訪問報表數(shù)據(jù)集。

　　具體原因分析

　　分析 MaxCompute 賬單發(fā)現(xiàn)費用波動是因為大計算任務和QuickBI報表數(shù)據(jù)集的自定義sql,主要為以下五點。

　　1、單SQL查詢費用較高

　　MaxCompute計算和部分QuickBI報表按照時間維度來進行查詢數(shù)據(jù),但是有些時間查詢跨度較大,或者基表數(shù)據(jù)量大從而形成一條大查詢sql。

　　2、分區(qū)不合理

　　部分MaxCompute計算邏輯和報表數(shù)據(jù)集設置不合理,有些查詢是直接查詢近3年分區(qū)的數(shù)據(jù), 造成計算成本費用增加。

　　3、報表訪問頻率高,篩選項不同

　　部分QuickBI報表的數(shù)據(jù)集成本其實很低,但是每天訪問的次數(shù)確實很大,由于重復執(zhí)行造成MaxCompute計算作業(yè)量增加,從而導致計算費用增加。

　　4、兼容報表增加維表數(shù)據(jù)

　　部分報表數(shù)據(jù)集為了兼容數(shù)據(jù)產(chǎn)出,需要增加部分維表數(shù)據(jù)來進行關聯(lián),但有些維表數(shù)據(jù)集其實很大,最后也會形成一條大查詢sql。

　　5、運行時間較長

　　MaxCompute部分計算sql和QuickBI報表數(shù)據(jù)集計算時間較長,影響整體業(yè)務運行時間和報表數(shù)據(jù)產(chǎn)出。

　　基于Information Schema分析項目作業(yè)

　　MaxCompute元數(shù)據(jù)服務Information Schema提供了項目元數(shù)據(jù)及使用歷史數(shù)據(jù)等信息。在ANSI SQL-92的Information Schema基礎上,添加了面向MaxCompute服務特有的字段及視圖。

　　租戶級別Information Schema是原項目級別Information Schema的升級版,是在每個阿里云賬號下創(chuàng)建名為SYSTEM_CATALOG的項目,并內(nèi)置Information Schema,通過訪問該內(nèi)置Schema提供的只讀視圖,查詢當前用戶所有項目的元數(shù)據(jù)信息以及使用歷史信息。元數(shù)據(jù)視圖列表如下

　　對于以上部分視圖元數(shù)據(jù)信息,我們更關心的是Information_Schema.TASKS_HISTORY表中每日任務計算的時間、成本和次數(shù)。

　　分析SQL腳本

　　這里我們使用的是租戶級別的 Information Schema,相比于項目級別的 Information Schema,租戶級別的只需要創(chuàng)建一個計算節(jié)點就可以計算所有 project 的任務,而項目級別的 Information Schema 每個 project 都需要一個計算節(jié)點,這里更推薦租戶級別的 Information Schema。

set odps.namespace.schema=true;set odps.sql.decimal.odps2=true;create table if not exists ads_project_cost_pay_di
(
    env_type  string comment '環(huán)境類型'
    ,cost_type string comment '消費類型'
    ,inst_id string comment  '唯一id,作業(yè)id'
    ,owner_name string comment  '作業(yè)所屬人'
    ,task_type string  comment  '作業(yè)類型  SQL:SQL作業(yè) CUPID:Spark或Mars作業(yè) SQLCost:SQL預估作業(yè) SQLRT:查詢加速SQL作業(yè) LOT:MapReduce作業(yè) PS:PAI的Parameter Server AlgoTask:機器學習作業(yè)'
    ,input_records string comment  '作業(yè)輸入的records數(shù)目'
    ,output_records string comment '作業(yè)輸出的records數(shù)目'
    ,input_bytes string comment '實際掃描的數(shù)據(jù)量,與Logview相同。'
    ,output_bytes string comment '輸出字節(jié)數(shù)。'
    ,status string comment '數(shù)據(jù)采集瞬間的運行狀態(tài)(非實時狀態(tài))。包含以下狀態(tài):Terminated:作業(yè)已執(zhí)行結束。Failed:作業(yè)失敗。 Cancelled:作業(yè)被取消。'
    ,cost_pay DECIMAL(18,5) comment '費用 單位元'
    ,complexity string  comment  '任務復雜度'
    ,settings string comment '上層調(diào)度或用戶傳入的信息,以JSON格式存儲。包含字段:USERAGENT、BIZID、SKYNET_ID和SKYNET_NODENAME。'
    ,sql_script string comment 'sql 代碼'
    ,start_time string comment '開始時間'
    ,end_time string comment '結束時間'
        ,data_collection string comment  'quickbi數(shù)據(jù)集')comment 'odps 費用 明細'partitioned by (ds string comment '分區(qū)')
;insert overwrite table ads_project_cost_pay_di partition(ds=${bizdate})select  case when task_catalog = 'renlijia_ng' then '生產(chǎn)' 
             when task_catalog = 'renlijia_ng_dev' then '測試'
             else  task_catalog         end as   env_type
        ,if(regexp_count(settings,'quickbi')>0,'quickbi',task_catalog)cost_type
        ,inst_id
        ,owner_name
        ,task_type
        ,input_records
        ,output_records
        ,input_bytes
        ,output_bytes
        ,status
        ,nvl(case   when task_type = 'SQL' then cast(input_bytes/1024/1024/1024 * complexity * 0.3 as DECIMAL(18,5) )                    when task_type = 'SQLRT' then cast(input_bytes/1024/1024/1024 * complexity * 0.3 as DECIMAL(18,5) )                    when task_type = 'CUPID' and status='Terminated'then cast(cost_cpu/100/3600 * 0.66 as DECIMAL(18,5) ) 
                    else 0 
        end,0) cost_pay
        ,complexity 
        ,settings
        ,operation_text sql_script
        ,start_time
        ,end_time
        ,regexp_extract(operation_text,'(?<=quickbi=).*?(?==quickbi)',0)data_collectionfrom  SYSTEM_CATALOG.INFORMATION_SCHEMA.TASKS_HISTORY where ds=${bizdate};注:sql成本計算公式(官方示例):
注:sql成本計算公式(官方示例):
case   when task_type = 'SQL' then cast(input_bytes/1024/1024/1024 * complexity * 0.3 as DECIMAL(18,5) )
when task_type = 'SQLRT' then cast(input_bytes/1024/1024/1024 * complexity * 0.3 as DECIMAL(18,5) )
when task_type = 'CUPID' and status='Terminated'then cast(cost_cpu/100/3600 * 0.66 as DECIMAL(18,5) ) 
else 0 end;

治理前后MaxCompute整體成本對比

　　報表產(chǎn)出明細數(shù)據(jù)
因為公司是按量付費的MaxCompute,所有我們主要關心的是成本問題和報表的訪問情況。對此我們主要從環(huán)境、數(shù)據(jù)集、用戶等維度進行分析。

QuickBI數(shù)據(jù)集(查ads_project_cost_pay_di表)

　　QuickBI報表Demo

　　QuickBI數(shù)據(jù)集字段是從sql-script中正則匹配出來,且QuickBI數(shù)據(jù)集需要單獨增加一個字段用來抽取數(shù)據(jù)集名。
　1、手動在QuickBI數(shù)據(jù)集增加如下字段:
  ‘quickbi=xxx數(shù)據(jù)集=quickbi’ as 數(shù)據(jù)集自定義字段
2、利用MaxCompute函數(shù)regexp_extract按照如下方式正則匹配:
   regexp_extract(operation_text,'(?<=quickbi=).*?(?==quickbi)',0)
分析改進項:
1、替換分區(qū)不合理數(shù)據(jù)表或數(shù)據(jù)集。
2、維表數(shù)據(jù)在上層加工,下層減少依賴項,做到最好只查一張表。
　3、高頻訪問數(shù)據(jù)集優(yōu)化存儲大小和QuickBI 報表儀表盤數(shù)量。
4、減少報表產(chǎn)出時間。
綜上:借助MaxCompute 租戶級別Information Schema,拉取每日歷史作業(yè)信息,公司成功把每日MaxCompute成本降低到合理波動區(qū)間。