MaxCompute:Analyze top N billing accounts and time-consuming jobs in MaxCompute

Step 1: Get the Information Schema service

Starting from March 1, 2024, MaxCompute no longer automatically installs the project-level Information Schema for new projects. To query metadata, use the tenant-level Information Schema instead, which provides more comprehensive information. For more information, see Tenant-level Information Schema.

(Optional) Step 2: Grant permissions to users other than the project owner

The Information Schema views contain all user data at the project level. By default, only the project owner can access this data. If other users or roles in the project need to access it, you must grant them permissions. For more information, see Access resources across projects based on packages.

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

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

Step 3: Download and back up metadata

Create a metadata backup table in your MaxCompute project and schedule a recurring task to write metadata to it. The following steps provide an example that uses the MaxCompute client:

1. Log on to the MaxCompute client and run the following command to create a metadata backup table.

   -- project_name is the name of your MaxCompute project.
   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
   );

2. Go to the DataWorks Data Studio interface, create an ODPS SQL node (information_history), and configure timed scheduling to periodically write data to the information_history backup table. Then, click the icon in the upper-left corner to save the node.

   For more information, see Create an ODPS SQL node.

   The following code shows an example of the command to run on the ODPS SQL node:

   -- project_name is the name of the MaxCompute project.
   use <project_name>;
   insert into table <project_name>.information_history select * from information_schema.tasks_history where ds ='datetime1';

   ${datetime1} is a DataWorks scheduling parameter. On the right of the ODPS SQL node, click Scheduling Configuration. In the Basic Properties section, set the Parameter to datetime1=${yyyymmdd}.

   Note

   To analyze metadata from multiple MaxCompute projects at the same time, create multiple ODPS SQL nodes. Configure each node to write the metadata from a different project into the same backup table.

Step 4: Create jobs to analyze top N billing accounts and time-consuming jobs

The settings field in the TASKS_HISTORY view records information from the scheduler or user in JSON format. This includes useragent, bizid, skynet_id, and skynet_nodename. You can use the settings field to find the RAM user who created the job. This lets you use the backup table to find the top N billing accounts and time-consuming jobs. The steps are as follows:

1. Log on to the MaxCompute client and create a RAM user details table named user_ram. This table will record the accounts and account IDs to be analyzed.

   The following code shows an example command:

   CREATE TABLE if NOT EXISTS <project_name>.user_ram
   (
       user_id STRING
       ,user_name STRING
   );

2. Create a details table named cost_topn to record the details of the top N billing accounts.

   The following code shows an example command:

   CREATE TABLE if NOT EXISTS <project_name>.cost_topn
   (
       cost_sum DECIMAL(38,5)
       ,task_owner STRING
   )
   partitioned BY 
   (
       ds STRING
   );

3. Create a details table named time_topn to record the details of the top N time-consuming jobs.

   The following code shows an example command:

   CREATE TABLE if NOT EXISTS <project_name>.time_topn
   (
       inst_id STRING
       ,cost_time BIGINT
       ,task_owner STRING
   )
   partitioned BY 
   (
       ds STRING
   );

4. Go to the DataWorks Data Studio interface. Create an ODPS SQL node (topn) and configure timed scheduling to periodically write the statistical data from the `cost_topn` table to the `user_ram` table. Then, click the icon in the upper-left corner to save the node.

   For more information, see Create an ODPS SQL node.

   The following code shows an example of the command to run on the ODPS SQL node:

   -- Enable data type 2.0. For more information about data type 2.0, see Data type editions.
   SET odps.sql.decimal.odps2=true;
   
   -- Write metadata to the cost_topn and time_topn tables. user_id is the account ID. You can find the account ID on your personal information page.
   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;

   Note

   In the example, task_type = 'SQL' represents an SQL job, task_type = 'SQLRT' represents a query acceleration job, and task_type = 'CUPID' represents a Spark job. To analyze other billable jobs, such as MapReduce or Mars jobs, add code lines based on their billing formulas. For more information about billing, see Compute pricing (pay-as-you-go).

   ${datetime1} is a DataWorks scheduling parameter. On the right of the ODPS SQL node, click Scheduling Configuration. In the Basic Properties section, set the Parameter to datetime1=${yyyymmdd}.

Step 5: Create a DingTalk group chatbot and push information about top N billing accounts and time-consuming jobs

The following steps show how to create a DingTalk group chatbot and push information about the top N billing accounts and time-consuming jobs. The steps use the DingTalk PC client as an example.

1. Create a DingTalk group chatbot.

   1. Select the target DingTalk group and click the icon in the upper-right corner.

   2. On the Group Settings panel, click Smart Group Assistant.

   3. On the Smart Group Assistant panel, click Add Robot.

   4. In the Add Robot section of the Group Robot dialog box, click the icon.

   5. In the Group Robot dialog box, click Custom.

   6. In the Robot Details dialog box, click Add.

   7. In the Add Robot dialog box, edit the robot information.

      Property Name	Configure Rules
      Profile picture	Click the icon in the lower-right corner of the profile picture to edit it.
      Robot Name	Enter a name for the robot.
      Security Settings	Configure the required security settings (select at least one), select I have read and agree to the "Custom Robot Service and Disclaimer", and then click Finish. There are three types of security settings: Custom Keywords: You can set up to 10 keywords. Add Signature: Select Add Signature to get the robot's key. IP Address (Range): Only requests from the specified IP address range are processed.

   8. In the Add Robot dialog box, copy the generated Webhook URL. Then, click Finish.

      Important

      Keep the Webhook URL secure. Do not post it on external websites. A leaked URL can cause security risks.

2. Use IntelliJ IDEA to create a Maven project and compile the Java program that pushes messages to the DingTalk group. After compilation, a JAR package is generated.

   For more information about how to use IntelliJ IDEA, click Help in the upper-right corner of the IntelliJ IDEA interface.

   1. Configure Pom dependencies.

      The following code shows the Pom dependencies.

      <?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>

   2. Develop the Java program and generate the JAR package topn_new.jar.

      The following code shows a Java example:

      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("Please enter the date parameter.");
                  System.exit(0);
              }
              System.out.println("Start reading data.");
              DingTalkClient client = new DefaultDingTalkClient(
                      "<Your chatbot's Webhook URL>");
              OapiRobotSendRequest request = new OapiRobotSendRequest();
              request.setMsgtype("markdown");
              OapiRobotSendRequest.Markdown markdown = new OapiRobotSendRequest.Markdown();
              // The date here is used as a parameter.
              markdown.setText(getContent(args[0]));
              markdown.setTitle("Top N jobs by consumption");
              request.setMarkdown(markdown);
              OapiRobotSendResponse response = client.execute(request);
              System.out.println("Message sent successfully.");
          }
      
          /**
           * Read from ODPS to get the data to send.
           */
      
          public static String getContent(String day) {
              Odps odps = createOdps();
              StringBuilder sb = new StringBuilder();
              try {
                  //==================Billing accounts=====================
                  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("Top N Billing Accounts (").append(day).append(
                          ")[Calculated based on Alibaba Cloud pay-as-you-go billing]").append("</font>").append("\n\n");
                  AtomicInteger costIndex = new AtomicInteger(1);
                  costTopnRecords.forEach(item -> {
                      sb.append(costIndex.getAndIncrement()).append(".").append("Account:");
                      sb.append("<font color=#2E64FE>").append(item.getString("task_owner")).append("\n\n").append("</font>");
                      sb.append("  ").append(" ").append("Cost:").append("<font color=#2E64FE>").append(item.get("cost_sum"))
                              .append(" CNY").append(
                              "</font>").append("\n\n")
                              .append("</font>");
                  });
                  //==================Time-consuming jobs=====================
                  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("Top N Time-consuming Jobs (").append(day).append(")")
                          .append("\n\n").append("</font>");
                  AtomicInteger timeIndex = new AtomicInteger(1);
                  timeTopnRecords.forEach(item -> {
                      sb.append(timeIndex.getAndIncrement()).append(".").append("Job:");
                      sb.append("<font color=#2E64FE>").append(item.getString("inst_id")).append("\n\n").append("</font>");
                      sb.append("   ").append("Account:").append("<font color=#2E64FE>").append(item.getString("task_owner")).append("\n\n").append("</font>");
                      sb.append("   ").append("Duration:").append("<font color=#2E64FE>").append(item.get("cost_time"))
                              .append("s").append(
                              "</font>").append("\n\n");
                  });
              } catch (OdpsException | IOException e) {
                  e.printStackTrace();
              }
              return sb.toString();
          }
      
          /**
           * Create an ODPS object.
           */
          public static Odps createOdps() {
              String project = "<project_name>";
              // An Alibaba Cloud account AccessKey has permissions to access all APIs. This poses a high security risk. We strongly recommend that you create and use a RAM user to make API calls or perform O&M. To create a RAM user, log on to the RAM console.
      		// This example shows how to store the AccessKey ID and AccessKey secret in environment variables. You can also store them in a configuration file as needed.
      		// We strongly recommend that you do not hard-code the AccessKey ID and AccessKey secret in your code. This can lead to key leakage.
      		String accessId = System.getenv("ALIBABA_CLOUD_ACCESS_KEY_ID");
      		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;
          }
      }

   3. Upload the generated topn_new.jar package as a MaxCompute resource.

      For more information, see Create and use MaxCompute resources.

3. Create a Shell node named dingsend, reference the topn_new.jar package, and configure a recurring schedule.

   For more information, see Shell node.

   The following code shows an example of the command to run on the Shell node:

   java -jar  topn_new.jar  $1

   $1 is a DataWorks scheduling parameter. On the right of the Shell node, click Scheduling Configuration. In the Basic Properties section, set the Parameter to ${yyyymmdd}.

Step 6: Configure scheduling properties for ancestor and descendant nodes and run the nodes

On the business flow panel, connect the information_history, topn, and dingsend nodes to create dependencies. Configure the rerun properties and ancestor node dependencies for each node. After the configuration is complete, right-click a node and select Run Node.

For more information about how to configure dependencies, see Configure same-cycle scheduling dependencies.

For more information about how to configure ancestor and descendant nodes, see Configure node context.

References

• Analyze MaxCompute bill and usage details

• View bill details

Online support

If you have any questions or suggestions when you use MaxCompute, you can submit a ticket to contact technical support.