MaxCompute:Analyze MaxCompute usage details

Parameters

• Time Period: Click to select a start and end time.

  If a job runs across two days, for example, starting on December 1 and ending on December 2, you must set the start time to December 1 to find the job in the downloaded usage records. However, the job's costs are included in the December 2 bill.

• Commodity Name:

  • MaxCompute(Subscription)

  • MaxCompute(Pay-as-you-go)

• Billable Item: Select an item based on your needs.

• Billable Item

  • ODPSDataPlus:

    • This applies when you have only purchased subscription projects and no pay-as-you-go projects in a region. This option provides usage records for pay-as-you-go billable items such as storage and download within the subscription projects.

    • Before April 25, 2024, if you had both subscription and pay-as-you-go projects enabled in the China (Hong Kong) region or another region outside the Chinese mainland, this applies to usage records for storage and download billable items in projects that used subscription computing quotas by default. To query usage records for projects that used pay-as-you-go quotas by default in this scenario, you must select MaxCompute(Pay-as-you-go).

  • ODPS_QUOTA_USAGE: Usage details for elastically reserved resources for computing and dedicated Tunnel.

  • MaxCompute(Pay-as-you-go): usage records for pay-as-you-go billable items such as computing, storage, and download.

• Time Unit: The default is Hour.

Analyze SQL costs

SQL jobs are used by over 95% of MaxCompute users and typically account for a large portion of spending.

• Method 1: Analyze with a spreadsheet

  Analyze the data categorized as ComputationSql in the usage records table. Check if the cost of certain SQL jobs exceeds expectations or if there are too many SQL tasks. The cost of an SQL job is calculated by using the following formula: SQL/Interactive Analytics Read Volume (Bytes) / 1024 / 1024 / 1024 × SQL Complexity × SQL Unit Price.

  For example, for a standard SQL task with a data read of 7352600872 bytes, the cost is SQL data read (7352600872 bytes / 1024 / 1024 / 1024) × SQL complexity of 1 × 0.0438 USD/(GB × complexity) = 0.292 USD.

  If your bill suddenly increases, first check if your subscription resource package has been depleted. After the computing quota in your resource package is exhausted, any excess usage is automatically billed on a pay-as-you-go basis (at a unit price of USD 0.0438 per GB × complexity), which can lead to a sudden cost spike.

  To locate a high-cost SQL job, follow these steps:

  1. In the usage details CSV, filter for records where the data category is ComputationSql and sort them in descending order by SQL/Interactive Analytics Read Volume (Bytes) (the computationsqlinput field) to find the record with the largest read volume.

  2. Record the metering information ID of the entry (that is, the feeid field).

  3. In the MaxCompute client (odpscmd) or DataWorks, run the wait <instanceid>; command to get the Logview URL. Open the URL in your browser to view the SQL content and locate the high-cost job.

• Method 2: Analyze with SQL

  This method requires you to have completed Step 2 and generated the maxcomputefee table:

• -- Analyze SQL consumption, ranked by sqlmoney.
  SELECT  to_char(endtime,'yyyymmdd') as ds,feeid as instanceid
          ,projectid
          ,computationsqlcomplexity  -- Complexity
          ,SUM((computationsqlinput / 1024 / 1024 / 1024)) as computationsqlinput  -- Data input (GB)
          ,SUM((computationsqlinput / 1024 / 1024 / 1024)) * computationsqlcomplexity * 0.0438 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
  ;

Analyze storage costs

• Analyze the reason for a USD 0.00001 storage charge by using a spreadsheet:

  You activate MaxCompute for trial use and find a daily charge of USD 0.00001 even with no active business. This is typically because some data, less than 0.5 GB, remains stored in MaxCompute.

  View the Storage billable item in the Data type column. For example, the maxcompute_doc project contains 508 bytes of data. According to the storage cost billing rules, you are charged USD 0.00001 for data storage up to 512 MB. The usage details in the exported CSV file show columns for Project ID (maxcompute_doc), Billing information ID, Data type (Storage), and Storage (bytes). Each record indicates a storage amount of 508 bytes, confirming that a small amount of residual data is generating the cost. If this data is only for testing, you can resolve this issue in one of the following ways:

  • If the table data is no longer used, execute the Drop Table statement to delete the table data in the project.

  • If the project is no longer needed, delete the project from the Projects page in the MaxCompute console.

• Analyze storage costs for less than one day by using a spreadsheet:

  1. View the Storage billable item in the Data type. Assume that 333,507,833,900 bytes of data are stored in the alian (example project name) project. Since the data was uploaded at 8:00, storage charges are metered starting from 09:07 for a total of 15 hours.

     The daily metering period ends at the end of the day, so the last record is not included in the bill for April 4.

  2. According to the storage billing rules, first calculate the 24-hour average storage, and then calculate the cost using the billing formula.

     -- Calculate average storage.
     333,507,833,900 bytes × 15 / 1024 / 1024 / 1024 / 24 = 194.127109076362103 GB
     
     -- The storage cost for one day is calculated as follows, with the result rounded to four decimal places.
     194.127109076362103 GB × USD 0.0006/GB/day = USD 0.1165/day

• Analyze the distribution of storage costs by using SQL

  This method requires you to have completed Step 2 and generated the maxcomputefee table:

  -- Analyze storage costs.
  SELECT  t.ds
          ,t.projectid
          ,t.storage
          ,CASE    WHEN t.storage < 0.5 THEN t.storage*0.0015     -- When the actual data storage of the project is greater than 0 MB and less than or equal to 512 MB, the unit price is USD 0.0015/GB/day.
                   WHEN t.storage >= 0.5  THEN t.storage*0.0006  -- When the actual data storage of the project is greater than 512 MB, the unit price is USD 0.0006/GB/day.
          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
  ;

  The following execution result is for demonstration only. The query returns three records sorted in descending order by storage_fee: on 2019-01-13, the storage was approximately 8,467 GB, costing about CNY 73.31; on 2019-01-12, the storage was about 4,527 GB, costing about CNY 40.22; and on 2019-01-14, the storage was about 2,672 GB, costing about CNY 24.63. The execution results can lead to the following conclusions:

  • Storage increased on the 12th and decreased on the 14th.

  • For storage optimization, we recommend setting a lifecycle for tables and deleting unused temporary tables.

• Analyze the distribution of archive storage, infrequent access storage, archive storage access, and infrequent access storage access by using SQL

  This method requires you to have completed Step 2 and generated the maxcomputefee table:

  -- Analyze archive storage costs.
  SELECT  to_char(starttime,'yyyymmdd') as ds
          ,projectid
          ,SUM(storage/1024/1024/1024)/24*0.0011 AS longTerm_storage
    FROM  maxcomputefee
   WHERE  TYPE = 'ColdStorage'
     and  to_char(starttime,'yyyymmdd') >= '20190112'
  GROUP BY to_char(starttime,'yyyymmdd')
           ,projectid;
  -- Analyze infrequent access storage costs.
  SELECT  to_char(starttime,'yyyymmdd') as ds
          ,projectid
          ,SUM(storage/1024/1024/1024)/24*0.0011 AS lowFre_storage
    FROM  maxcomputefee
   WHERE  TYPE = 'LowFreqStorage'
     and  to_char(starttime,'yyyymmdd') >= '20190112'
  GROUP BY to_char(starttime,'yyyymmdd')
           ,projectid;
  -- Analyze archive storage access costs.
  SELECT  to_char(starttime,'yyyymmdd') as ds
          ,projectid
          ,SUM(computationsqlinput/1024/1024/1024)*0.522 AS longTerm_IO
    FROM  maxcomputefee
   WHERE  TYPE = 'SqlLongterm'
     and  to_char(starttime,'yyyymmdd') >= '20190112'
  GROUP BY to_char(starttime,'yyyymmdd')
           ,projectid;
  -- Analyze infrequent access storage access costs.
  SELECT  to_char(starttime,'yyyymmdd') as ds
          ,projectid
          ,SUM(computationsqlinput/1024/1024/1024)*0.522 AS lowFre_IO
    FROM  maxcomputefee
   WHERE  TYPE = 'SqlLowFrequency'
     and  to_char(starttime,'yyyymmdd') >= '20190112'
  GROUP BY to_char(starttime,'yyyymmdd')
           ,projectid;

Analyze download costs

For data downloads over the public internet or across regions, MaxCompute charges based on the amount of data downloaded.

• Method 1: Analyze with a spreadsheet

  The data type DownloadEx represents the billable item for public downloads.

  1. Based on the Download fees (pay-as-you-go) pricing, outbound public traffic of approximately 0.036 GB (38,199,736 bytes) incurs a fee of (38,199,736 bytes/1024/1024/1024) × 0.1166 USD/GB = 0.004 USD.

  2. Example of download optimization: Check your Tunnel service settings to see if costs are being incurred due to using a public network endpoint. For more information, see Endpoints. If you are downloading large amounts of data from the China (Shanghai) region to a location outside that region's internal network (such as an on-premises server in Suzhou), you can first download the data to an ECS virtual machine (VM) within the China (Shanghai) region to leverage internal network traffic.

• Method 2: Analyze with SQL

  This method requires you to have completed Step 2 and generated the maxcomputefee table:

  -- Analyze download consumption details.
  SELECT  TO_CHAR(starttime,'yyyymmdd') AS ds
          ,projectid
          ,SUM((download/1024/1024/1024)*0.1166) 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
  ;

Analyze external table job costs (Tablestore and OSS)

• Method 1: Analyze with a spreadsheet

  Analyze the data where the data type is ComputationSqlOTS or ComputationSqlOSS in your usage records. Sort the computing costs for SQL external table jobs. The cost is calculated using the formula: (SQL/interactive analysis read volume (bytes) / 1024 / 1024 / 1024) × Unit price (USD 0.0044).

• Method 2: Analyze with SQL

  This method requires you to have completed Step 2 and generated the maxcomputefee table:

  -- Analyze Tablestore external table SQL job consumption.
  SELECT  TO_CHAR(starttime,'yyyymmdd') AS ds
          ,projectid
          ,(computationsqlinput/1024/1024/1024)*1*0.0044 AS ots_fee
  FROM    maxcomputefee
  WHERE   type = 'ComputationSqlOTS'
  AND     TO_CHAR(starttime,'yyyymmdd') >= '20190112'
  GROUP BY TO_CHAR(starttime,'yyyymmdd')
           ,projectid
           ,computationsqlinput
  ORDER BY ots_fee DESC
  ;
  
  -- Analyze OSS external table SQL job consumption.
  SELECT  TO_CHAR(starttime,'yyyymmdd') AS ds
          ,projectid
          ,(computationsqlinput/1024/1024/1024)*1*0.0044 AS oss_fee
  FROM    maxcomputefee
  WHERE   type = 'ComputationSqlOSS'
  AND     TO_CHAR(starttime,'yyyymmdd') >= '20190112'
  GROUP BY TO_CHAR(starttime,'yyyymmdd')
           ,projectid
           ,computationsqlinput
  ORDER BY oss_fee DESC
  ;

Analyze Spark computing costs

• Method 1: Analyze with a spreadsheet

  Analyze the data where the data type is Spark in your usage records. Sort the job costs. The cost is calculated using the formula: (MapReduce/Spark job computing (core*seconds) / 3600) × Unit price (USD 0.1041).

• Method 2: Analyze with SQL

  This method requires you to have completed Step 2 and generated the maxcomputefee table:

  -- Analyze Spark job consumption.
  SELECT  TO_CHAR(starttime,'yyyymmdd') AS ds
          ,projectid
          ,(cu_usage/3600)*0.1041 AS spark_fee
  FROM    maxcomputefee
  WHERE   type = 'spark'
  AND     TO_CHAR(starttime,'yyyymmdd') >= '20190112'
  GROUP BY TO_CHAR(starttime,'yyyymmdd')
           ,projectid
           ,cu_usage
  ORDER BY spark_fee DESC
  ;