如何通過MaxCompute中繼資料統計費用帳號及耗時作業 - MaxCompute

資料開發人員在使用MaxCompute開發過程中，需要統計MaxCompute專案中帳號的費用以及作業的耗時情況，助力合理規劃和調整作業。本文為您介紹如何通過MaxCompute中繼資料（Information Schema）統計TOP費用帳號及耗時作業，同時通過DingTalk推送到客戶群。

背景資訊

通常，資料開發人員會通過DataWorks標準模式使用MaxCompute，MaxCompute會在Information Schema中記錄所有作業的執行帳號為同一個主帳號，只有小部分的作業執行帳號為RAM使用者。此時資料開發人員會關注如何統計各個帳號的費用和耗時作業。MaxCompute提供如下方案解決這兩個問題：

帳號費用：您可以通過賬單詳情中的用量明細來查詢，但是這種方式無法將用量明細歸屬到對應的RAM使用者。Information Schema視圖中的TASKS_HISTORY會記錄MaxCompute專案內已完成的作業詳情，且保留近14天資料。您可以將TASKS_HISTORY中的資料備份到指定MaxCompute專案中，基於該資料統計TOP費用帳號。
耗時作業：您可以通過TASKS_HISTORY中的資料統計TOP耗時作業。

更多關於Information Schema的功能及使用限制，請參見專案層級Information Schema（即將下線）。

統計MaxCompute TOPN費用帳號及耗時作業的流程如下：

步驟一：擷取Information Schema服務。
（可選）步驟二：對除Project Owner外的使用者授權。
步驟三：下載並備份中繼資料。
步驟四：建立統計TOPN費用帳號及耗時作業。
步驟五：建立DingTalk群機器人並推送TOPN費用帳號及耗時作業資訊。
步驟六：配置上下遊節點調度屬性並運行節點。

步驟一：擷取Information Schema服務

自2024年03月01日開始，MaxCompute停止對新增專案自動安裝專案層級Information Schema，即新增的專案預設沒有專案層級Information Schema的Package。若您有查中繼資料的業務，您可以查詢租戶層級的Information Schema，以便擷取更全的資訊。租戶層級Information Schema的具體使用說明請參見租戶層級Information Schema。

對於存量MaxCompute專案，在您開始使用Information Schema服務前，需要以專案所有者（Project Owner）或具備Super_Administrator管理角色的RAM使用者身份安裝Information Schema許可權包，獲得訪問專案中繼資料的許可權。更多為使用者授權管理角色操作資訊，請參見將角色賦予使用者。安裝方式有如下兩種：

登入MaxCompute用戶端，執行如下命令：
```
install package Information_Schema.systables;
```
登入DataWorks控制台，進入臨時查詢介面。更多臨時查詢操作詳情，請參見使用臨時查詢運行SQL語句（可選）。執行如下命令：
```
install package Information_Schema.systables;
```

說明

如果統計多個MaxCompute專案的中繼資料，您需要分別對各個MaxCompute專案安裝Information Schema許可權包。然後把各個MaxCompute專案的中繼資料的備份資料插入到同一個表中做集中統計分析。

（可選）步驟二：對除Project Owner外的使用者授權

Information Schema的視圖包含了專案層級的所有使用者資料，預設專案所有者可以查看。如果專案內其他使用者或角色需要查看，需要進行授權，請參見基於Package跨專案訪問資源。

授權文法如下。

grant <actions> on package Information_Schema.systables to user <user_name>;
grant <actions> on package Information_Schema.systables to role <role_name>;

actions：待授予的操作許可權，取值為Read。
user_name：已添加至專案中的阿里雲帳號或RAM使用者。
您可以通過MaxCompute用戶端執行list users;命令擷取使用者帳號。
role_name：已添加至專案中的角色。
您可以通過MaxCompute用戶端執行list roles;命令擷取角色名稱。

授權樣本如下。

grant read on package Information_Schema.systables to user RAM$Bob@aliyun.com:user01;

步驟三：下載並備份中繼資料

在MaxCompute專案上建立中繼資料備份表，並定時將中繼資料寫入備份表中。以MaxCompute用戶端為例，操作流程如下：

登入MaxCompute用戶端，執行如下命令建立中繼資料備份表。

--project_name為MaxCompute專案名稱。
create table if not exists <project_name>.information_history
(
    task_catalog STRING
    ,task_schema STRING
    ,task_name STRING
    ,task_type STRING
    ,inst_id STRING
    ,`status` STRING
    ,owner_id STRING
    ,owner_name STRING
    ,result STRING
    ,start_time DATETIME
    ,end_time DATETIME
    ,input_records BIGINT
    ,output_records BIGINT
    ,input_bytes BIGINT
    ,output_bytes BIGINT
    ,input_tables STRING
    ,output_tables STRING
    ,operation_text STRING
    ,signature STRING
    ,complexity DOUBLE
    ,cost_cpu DOUBLE
    ,cost_mem DOUBLE
    ,settings STRING
    ,ds STRING
);

進入DataWorks資料開發介面，建立ODPS SQL節點（information_history）並配置定時調度，用於定時將資料寫入備份表information_history。完成後單擊左上方表徵圖儲存。
建立ODPS SQL節點操作，請參見開發ODPS SQL任務。
ODPS SQL節點啟動並執行命令樣本如下：
```
--project_name為MaxCompute專案名稱。
use <project_name>;
insert into table <project_name>.information_history select * from information_schema.tasks_history where ds ='datetime1';
```
${datetime1}為DataWorks的調度參數，您需要在ODPS SQL節點右側，單擊調度配置，在基礎屬性地區配置參數值為datetime1=${yyyymmdd}。
說明
如果需要同時對多個MaxCompute專案的中繼資料進行統計分析，您可以建立多個ODPS SQL節點，將這些MaxCompute專案的中繼資料寫入到同一張資料備份表中。

步驟四：建立統計TOPN費用帳號及耗時作業

TASKS_HISTORY視圖中的settings會記錄上層調度或使用者傳入的資訊，以JSON格式儲存。包含的具體資訊有：useragent、bizid、skynet_id和skynet_nodename。您可以通過settings欄位定位到建立作業的RAM使用者資訊。因此您可以基於備份資料表計算TOPN費用帳號及耗時作業。操作流程如下：

登入MaxCompute用戶端，建立一張RAM使用者明細表user_ram，記錄需要統計的帳號及帳號ID。
命令樣本如下：
```
create table if not exists <project_name>.user_ram
(
    user_id STRING
    ,user_name STRING
);
```

建立一張統計帳號費用的明細表cost_topn，記錄TOPN費用帳號明細。

命令樣本如下：

create table if not exists <project_name>.cost_topn
(
    cost_sum DECIMAL(38,5)
    ,task_owner STRING
)
partitioned by 
(
    ds STRING
);

建一張統計耗時作業的明細表time_topn，記錄TOPN耗時作業明細。

命令樣本如下：

create table if not exists <project_name>.time_topn
(
    inst_id STRING
    ,cost_time BIGINT
    ,task_owner STRING
)
partitioned by 
(
    ds STRING
);

進入DataWorks資料開發介面，建立ODPS SQL節點（topn）並配置定時調度，用於定時將cost_topn表中統計的資料寫入user_ram表。完成後單擊左上方表徵圖儲存。

建立ODPS SQL節點操作，請參見開發ODPS SQL任務。

ODPS SQL節點啟動並執行命令樣本如下：

--開啟2.0資料類型開關。2.0資料類型詳情，請參見2.0資料類型版本。
set odps.sql.decimal.odps2=true;
--將中繼資料寫入cost_topn、time_topn表。user_id為帳號ID。您可以在個人資訊頁面查看帳號ID。
insert into table <project_name>.cost_topn partition (ds = '${datetime1}')
select   
nvl(cost_sum,0) cost_sum
        ,case when a.task_owner='<user_id>' or a.task_owner='<user_id>' or a.task_owner='<user_id>' then b.user_name 
              else a.task_owner 
         end task_owner 
from    (
            select  inst_id
                    ,owner_name
                    ,task_type
                    ,a.input_bytes
                    ,a.cost_cpu
                    ,a.status
                    ,case    when a.task_type = 'SQL' then cast(a.input_bytes/1024/1024/1024 * a.complexity * 0.3 as DECIMAL(18,5) )
                             when a.task_type = 'SQLRT' then cast(a.input_bytes/1024/1024/1024 * a.complexity * 0.3 as DECIMAL(18,5) )
                             when a.task_type = 'CUPID' and a.status='Terminated'then cast(a.cost_cpu/100/3600 * 0.66 as DECIMAL(18,5) ) 
                             else 0 
                     end cost_sum
                    ,a.settings
                    ,get_json_object(settings, "$.SKYNET_ONDUTY") owner
                    ,case    when get_json_object(a.settings, "$.SKYNET_ONDUTY") is null then owner_name 
                             else get_json_object(a.settings, "$.SKYNET_ONDUTY") 
                     end task_owner
            from    information_history
            where   ds = '${datetime1}'
        ) a
left join <project_name>.user_ram b
on      a.task_owner = b.user_id;

insert into table <project_name>.time_topn partition(ds = '${datetime1}')
select  inst_id
        ,cost_time
        ,case    when a.task_owner='<user_id>' or a.task_owner='<user_id>' or a.task_owner='<user_id>' then b.user_name 
                 else a.task_owner 
         end task_owner
from    (
            select  inst_id
                    ,task_type
                    ,status
                    ,datediff(a.end_time, a.start_time, 'ss') AS cost_time
                    ,case    when get_json_object(a.settings, "$.SKYNET_ONDUTY") is null then owner_name 
                             else get_json_object(a.settings, "$.SKYNET_ONDUTY") 
                     end task_owner
            from    <project_name>.information_history a
            where   ds = '${datetime1}'
        ) a
left join <project_name>.user_ram b
on      a.task_owner = b.user_id
;

說明

樣本中的task_type = 'SQL'表示SQL作業，task_type = 'SQLRT'表示查詢加速作業，task_type = 'CUPID'表示Spark作業。如果需要統計其他計費作業，例如MapReduce、Mars，您可以按照計費公式添加相應程式碼。計費詳情，請參見計算費用。

${datetime1}為DataWorks的調度參數，您需要在ODPS SQL節點右側，單擊調度配置，在基礎屬性地區配置參數值為datetime1=${yyyymmdd}。

步驟五：建立DingTalk群機器人並推送TOPN費用帳號及耗時作業資訊

以PC端為例，建立DingTalk群機器人並推送TOPN費用帳號及耗時作業資訊的操作流程如下：

建立DingTalk群機器人。

選擇目標DingTalk群，單擊右上方的表徵圖。
在群設定面板，單擊智能群助手。
在智能群助手面板，單擊添加機器人。
在群機器人對話方塊的添加機器人地區，單擊表徵圖。
在群機器人對話方塊，單擊自訂機器人。
在機器人詳情對話方塊，單擊添加。

在添加機器人對話方塊，編輯機器人資訊。

屬性名稱	設定規則
頭像	單擊頭像右下角的表徵圖來編輯頭像。
機器人名字	輸入機器人名字。
安全設定	完成必要的安全設定（至少選擇1種），勾選我已閱讀並同意《自訂機器人服務及免責條款》，單擊完成。安全設定有3種方式：自訂關鍵詞：最多可以設定10個關鍵詞。加簽：勾選加簽可以擷取到機器人的密鑰。 IP地址（段）：只有來自IP位址範圍內的請求才會被正常處理。

在添加機器人對話方塊，複製產生的Webhook地址。單擊完成。
重要
請保管好此Webhook地址，不要公布在外部網站上，泄露後會有安全風險。

通過IntelliJ IDEA建立Maven專案並編譯推送DingTalk群訊息的Java程式，編譯完成後產生JAR包。

IntelliJ IDEA操作詳情，請單擊IntelliJ IDEA工具介面右上方的Help擷取。

配置Pom依賴。

Pom依賴如下。

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>DingTalk_Information</groupId>
    <artifactId>DingTalk_Information</artifactId>
    <version>1.0-SNAPSHOT</version>
    <dependencies>
        <dependency>
            <groupId>com.aliyun.odps</groupId>
            <artifactId>odps-sdk-core</artifactId>
            <version>0.35.5-public</version>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.15</version>
            <exclusions>
                <exclusion>
                    <groupId>com.sun.jmx</groupId>
                    <artifactId>jmxri</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>com.sun.jdmk</groupId>
                    <artifactId>jmxtools</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>javax.jms</groupId>
                    <artifactId>jms</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>com.aliyun</groupId>
            <artifactId>alibaba-dingtalk-service-sdk</artifactId>
            <version>1.0.1</version>
        </dependency>
        <dependency>
            <groupId>com.aliyun.odps</groupId>
            <artifactId>odps-jdbc</artifactId>
            <version>3.0.1</version>
            <classifier>jar-with-dependencies</classifier>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-assembly-plugin</artifactId>
                <version>2.4.1</version>
                <configuration>
                    <!-- get all project dependencies -->
                    <descriptorRefs>
                        <descriptorRef>jar-with-dependencies</descriptorRef>
                    </descriptorRefs>
                    <!-- MainClass in mainfest make a executable jar -->
                    <archive>
                        <manifest>
                            <mainClass>com.alibaba.sgri.message.test</mainClass>
                        </manifest>
                    </archive>
                </configuration>
                <executions>
                    <execution>
                        <id>make-assembly</id>
                        <!-- bind to the packaging phase -->
                        <phase>package</phase>
                        <goals>
                            <goal>single</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>
</project>

開發Java程式並產生JAR包topn_new.jar。

Java程式碼範例如下：

package com.alibaba.sgri.message;
import java.io.IOException;
import java.util.concurrent.atomic.AtomicInteger;
import com.aliyun.odps.Instance;
import com.aliyun.odps.Odps;
import com.aliyun.odps.OdpsException;
import com.aliyun.odps.account.Account;
import com.aliyun.odps.account.AliyunAccount;
import com.aliyun.odps.data.ResultSet;
import com.aliyun.odps.task.SQLTask;
import com.dingtalk.api.DefaultDingTalkClient;
import com.dingtalk.api.DingTalkClient;
import com.dingtalk.api.request.OapiRobotSendRequest;
import com.dingtalk.api.response.OapiRobotSendResponse;
import com.taobao.api.ApiException;

public class test {

    public static void main(String[] args) throws ApiException {
        if (args.length < 1) {
            System.out.println("請輸入日期參數");
            System.exit(0);
        }
        System.out.println("開始讀取資料");
        DingTalkClient client = new DefaultDingTalkClient(
                "https://oapi.dingtalk"
                        + ".com/robot/send?access_token=<機器人Webhook地址>\n");
        OapiRobotSendRequest request = new OapiRobotSendRequest();
        request.setMsgtype("markdown");
        OapiRobotSendRequest.Markdown markdown = new OapiRobotSendRequest.Markdown();
        //這裡的日期作為參數
        markdown.setText(getContent(args[0]));
        markdown.setTitle("作業消費TOPN");
        request.setMarkdown(markdown);
        OapiRobotSendResponse response = client.execute(request);
        System.out.println("訊息發送成功");
    }

    /**
     * 讀取ODPS,擷取要發送的資料
     */

    public static String getContent(String day) {
        Odps odps = createOdps();
        StringBuilder sb = new StringBuilder();
        try {
            //==================這是費用帳號=====================
            String costTopnSql = "select sum(cost_sum)cost_sum,task_owner from cost_topn where ds='" + day + "' " + "group by task_owner order by cost_sum desc limit 5;";
            Instance costInstance = SQLTask.run(odps, costTopnSql);
            costInstance.waitForSuccess();
            ResultSet costTopnRecords = SQLTask.getResultSet(costInstance);
            sb.append("<font color=#FF0000 size=4>").append("費用帳號TOPN(").append(day).append(
                    ")[按照阿里雲隨用隨付計算]").append("</font>").append("\n\n");
            AtomicInteger costIndex = new AtomicInteger(1);
            costTopnRecords.forEach(item -> {
                sb.append(costIndex.getAndIncrement()).append(".").append("帳號:");
                sb.append("<font color=#2E64FE>").append(item.getString("task_owner")).append("\n\n").append("</font>");
                sb.append("  ").append(" ").append("消費:").append("<font color=#2E64FE>").append(item.get("cost_sum"))
                        .append("元").append(
                        "</font>").append("\n\n")
                        .append("</font>");
            });
            //==================這是耗時作業=====================
            String timeTopnSql = "select * from time_topn where ds='" + day + "' ORDER BY cost_time DESC limit 5;";
            Instance timeInstance = SQLTask.run(odps, timeTopnSql);
            timeInstance.waitForSuccess();
            ResultSet timeTopnRecords = SQLTask.getResultSet(timeInstance);
            sb.append("<font color=#FF8C00 size=4>").append("耗時作業TOPN(").append(day).append(")")
                    .append("\n\n").append("</font>");
            AtomicInteger timeIndex = new AtomicInteger(1);
            timeTopnRecords.forEach(item -> {
                sb.append(timeIndex.getAndIncrement()).append(".").append("作業：");
                sb.append("<font color=#2E64FE>").append(item.getString("inst_id")).append("\n\n").append("</font>");
                sb.append("   ").append("帳號：").append("<font color=#2E64FE>").append(item.getString("task_owner")).append("\n\n").append("</font>");
                sb.append("   ").append("耗時：).append("<font color=#2E64FE>").append(item.get("cost_time"))
                        .append("秒").append(
                        "</font>").append("\n\n");
            });
        } catch (OdpsException | IOException e) {
            e.printStackTrace();
        }
        return sb.toString();
    }

    /**
     * 建立ODPS
     */
    public static Odps createOdps() {
        String project = "<project_name>";
        // 阿里雲帳號AccessKey擁有所有API的存取權限，風險很高。強烈建議您建立並使用RAM使用者進行API訪問或日常營運，請登入RAM控制台建立RAM使用者
				// 此處以把AccessKey 和 AccessKeySecret 儲存在環境變數為例說明。您也可以根據業務需要，儲存到設定檔裡
				// 強烈建議不要把 AccessKey 和 AccessKeySecret 儲存到代碼裡，會存在密鑰泄漏風險
				private static String accessId = System.getenv("ALIBABA_CLOUD_ACCESS_KEY_ID");
				private static String accessKey = System.getenv("ALIBABA_CLOUD_ACCESS_KEY_SECRET");
        String endPoint = "http://service.odps.aliyun.com/api";
        Account account = new AliyunAccount(accessId, accessKey);
        Odps odps = new Odps(account);
        odps.setEndpoint(endPoint);
        odps.setDefaultProject(project);
        return odps;
    }
}

上傳產生的topn_new.jar包為MaxCompute資源。
上傳MaxCompute資源操作，請參見建立並使用MaxCompute資源。

建立Shell節點（dingsend），引用topn_new.jar包並配置定時調度。
建立Shell節點操作，請參見Shell節點。
Shell節點啟動並執行命令樣本如下：
```
java -jar  topn_new.jar  $1
```
$1為DataWorks的調度參數，您需要在Shell節點右側，單擊調度配置，在基礎屬性地區配置參數值為${yyyymmdd}。

步驟六：配置上下遊節點調度屬性並運行節點

在商務程序面板將information_history、topn和dingsend節點連線形成依賴關係，並配置每個節點的重跑屬性和依賴的上遊節點。配置完成後在節點上單擊右鍵，選擇運行節點即可。

依賴關係配置，請參見配置同周期調度依賴。

節點上下遊配置，請參見配置節點上下文。

線上支援

如果您在使用MaxCompute的過程中有任何疑問或建議，搜尋（DingTalk群號：11782920）加入DingTalk群進行反饋。

MaxCompute：統計MaxCompute TOPN費用帳號及耗時作業