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

業(yè)務(wù)簡(jiǎn)介

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

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

業(yè)務(wù)痛點(diǎn)

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

具體原因分析

分析 MaxCompute 賬單發(fā)現(xiàn)費(fèi)用波動(dòng)是因?yàn)榇笥?jì)算任務(wù)和QuickBI報(bào)表數(shù)據(jù)集的自定義sql，主要為以下五點(diǎn)。

1、單SQL查詢(xún)費(fèi)用較高

MaxCompute計(jì)算和部分QuickBI報(bào)表按照時(shí)間維度來(lái)進(jìn)行查詢(xún)數(shù)據(jù)，但是有些時(shí)間查詢(xún)跨度較大，或者基表數(shù)據(jù)量大從而形成一條大查詢(xún)sql。

2、分區(qū)不合理

部分MaxCompute計(jì)算邏輯和報(bào)表數(shù)據(jù)集設(shè)置不合理，有些查詢(xún)是直接查詢(xún)近3年分區(qū)的數(shù)據(jù)， 造成計(jì)算成本費(fèi)用增加。

3、報(bào)表訪問(wèn)頻率高，篩選項(xiàng)不同

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

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

部分報(bào)表數(shù)據(jù)集為了兼容數(shù)據(jù)產(chǎn)出，需要增加部分維表數(shù)據(jù)來(lái)進(jìn)行關(guān)聯(lián)，但有些維表數(shù)據(jù)集其實(shí)很大，最后也會(huì)形成一條大查詢(xún)sql。

5、運(yùn)行時(shí)間較長(zhǎng)

MaxCompute部分計(jì)算sql和QuickBI報(bào)表數(shù)據(jù)集計(jì)算時(shí)間較長(zhǎng)，影響整體業(yè)務(wù)運(yùn)行時(shí)間和報(bào)表數(shù)據(jù)產(chǎn)出。

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

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

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

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

分析SQL腳本

這里我們使用的是租戶(hù)級(jí)別的 Information Schema，相比于項(xiàng)目級(jí)別的 Information Schema，租戶(hù)級(jí)別的只需要?jiǎng)?chuàng)建一個(gè)計(jì)算節(jié)點(diǎn)就可以計(jì)算所有 project 的任務(wù)，而項(xiàng)目級(jí)別的 Information Schema 每個(gè) project 都需要一個(gè)計(jì)算節(jié)點(diǎn)，這里更推薦租戶(hù)級(jí)別的 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)境類(lèi)型'
    ,cost_type string comment '消費(fèi)類(lèi)型'
    ,inst_id string comment  '唯一id，作業(yè)id'
    ,owner_name string comment  '作業(yè)所屬人'
    ,task_type string  comment  '作業(yè)類(lèi)型  SQL：SQL作業(yè) CUPID：Spark或Mars作業(yè) SQLCost：SQL預(yù)估作業(yè) SQLRT：查詢(xún)加速SQL作業(yè) LOT：MapReduce作業(yè) PS：PAI的Parameter Server AlgoTask：機(jī)器學(xué)習(xí)作業(yè)'
    ,input_records string comment  '作業(yè)輸入的records數(shù)目'
    ,output_records string comment '作業(yè)輸出的records數(shù)目'
    ,input_bytes string comment '實(shí)際掃描的數(shù)據(jù)量，與Logview相同。'
    ,output_bytes string comment '輸出字節(jié)數(shù)。'
    ,status string comment '數(shù)據(jù)采集瞬間的運(yùn)行狀態(tài)（非實(shí)時(shí)狀態(tài)）。包含以下?tīng)顟B(tài)：Terminated：作業(yè)已執(zhí)行結(jié)束。Failed：作業(yè)失敗。 Cancelled：作業(yè)被取消。'
    ,cost_pay DECIMAL(18,5) comment '費(fèi)用 單位元'
    ,complexity string  comment  '任務(wù)復(fù)雜度'
    ,settings string comment '上層調(diào)度或用戶(hù)傳入的信息，以JSON格式存儲(chǔ)。包含字段：USERAGENT、BIZID、SKYNET_ID和SKYNET_NODENAME。'
    ,sql_script string comment 'sql 代碼'
    ,start_time string comment '開(kāi)始時(shí)間'
    ,end_time string comment '結(jié)束時(shí)間'
        ,data_collection string comment  'quickbi數(shù)據(jù)集'
)
comment 'odps 費(fèi)用 明細(xì)'
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 '測(cè)試'
             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_collection
from  SYSTEM_CATALOG.INFORMATION_SCHEMA.TASKS_HISTORY where ds=${bizdate};

注：sql成本計(jì)算公式（官方示例）：

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整體成本對(duì)比

報(bào)表產(chǎn)出明細(xì)數(shù)據(jù)

因?yàn)楣臼前戳扛顿M(fèi)的MaxCompute，所有我們主要關(guān)心的是成本問(wèn)題和報(bào)表的訪問(wèn)情況。對(duì)此我們主要從環(huán)境、數(shù)據(jù)集、用戶(hù)等維度進(jìn)行分析。

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

QuickBI報(bào)表Demo

QuickBI數(shù)據(jù)集字段是從sql-script中正則匹配出來(lái)，且QuickBI數(shù)據(jù)集需要單獨(dú)增加一個(gè)字段用來(lái)抽取數(shù)據(jù)集名。

1、手動(dòng)在QuickBI數(shù)據(jù)集增加如下字段：

‘quickbi=xxx數(shù)據(jù)集=quickbi’ as 數(shù)據(jù)集自定義字段

2、利用MaxCompute函數(shù)regexp_extract按照如下方式正則匹配：

regexp_extract(operation_text,'(?<=quickbi=).*?(?==quickbi)',0)

分析改進(jìn)項(xiàng)：

1、替換分區(qū)不合理數(shù)據(jù)表或數(shù)據(jù)集。

2、維表數(shù)據(jù)在上層加工，下層減少依賴(lài)項(xiàng)，做到最好只查一張表。

3、高頻訪問(wèn)數(shù)據(jù)集優(yōu)化存儲(chǔ)大小和QuickBI 報(bào)表儀表盤(pán)數(shù)量。

4、減少報(bào)表產(chǎn)出時(shí)間。

綜上：借助MaxCompute 租戶(hù)級(jí)別Information Schema，拉取每日歷史作業(yè)信息，公司成功把每日MaxCompute成本降低到合理波動(dòng)區(qū)間。