MaxCompute:Analyze MaxCompute billing usage records

Parameter descriptions

• Time Period: Click to select the start and end times.

  If a job starts on December 1 and ends on December 2, set the start time to December 1 to include the job in the downloaded usage records. However, the consumption record appears in the December 2 bill.

• Commodity Name:

  • MaxCompute(Subscription)

  • MaxCompute (Pay-as-you-go)

• Billable Item: You can select as needed.

• Billable Item

  • ODPSDataPlus:

    • If you have only purchased subscription projects for MaxCompute in a region, the usage of pay-as-you-go billing items, such as storage and download, is metered within the subscription project.

    • Before April 25, 2024, if you had both subscription and pay-as-you-go MaxCompute projects enabled in Hong Kong (China) or a region outside China, the default compute quota was associated with the subscription project. This quota also covered the measurement details for pay-as-you-go storage and download billing items. In such cases, you can obtain the measurement details for the pay-as-you-go project’s default compute quota by querying MaxCompute (Pay-As-You-Go).

  • ODPS_QUOTA_USAGE: Metering records for elastically reserved computing resources and dedicated Tunnel resources.

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

• Time Unit: Hour, by default.

Analyze SQL fees

Ninety-five percent of cloud users meet their business requirements using MaxCompute SQL alone. SQL fees also make up a large portion of MaxCompute bills.

• Method 1: Analyze using Excel

  Analyze records where Metering type is ComputationSql in the usage records. Check whether any SQL job fees exceed expectations or if too many SQL jobs exist. Calculate SQL job fees using this formula: SQL input (bytes)/1024/1024/1024 × SQL complexity × SQL unit price.

  For example, a standard SQL job with an input volume of 7352600872 bytes incurs a fee of SQL input (7352600872 bytes/1024/1024/1024) × SQL complexity 1 × CNY 0.3 per GB = CNY 2.

• Method 2: Analyze using SQL

  You have completed Step 2 and created the maxcomputefee table:

• -- Analyze SQL fees, ranked by sqlmoney.
  SELECT  to_char(endtime,'yyyymmdd') as ds,feeid as instanceid
          ,projectid
          ,computationsqlcomplexity  -- Complexity
          ,SUM((computationsqlinput / 1024 / 1024 / 1024)) as computationsqlinput  -- Input volume (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 fees

• Analyze why storage fees are CNY 0.01 using Excel:

  After you activate the MaxCompute trial, no workloads run on MaxCompute, but you receive a ¥0.01 bill each day. This usually occurs because residual data is stored in MaxCompute, and the data volume does not exceed 0.5 GB.

  Check records where Metering type is Storage. As shown in the figure, the maxcompute_doc project stores 508 bytes. Per storage billing rules, data ≤ 512 MB incurs a fee of CNY 0.01. If this data is for testing only, resolve the issue as follows:

  • If only table data is unused, run the Drop Table statement to delete table data in the project.

  • If the project is unused, you can delete it in the MaxCompute console under Manage Configurations > Projects.

• Excel Analysis of less than one day data storage fees:

  1. Check records where Metering type is Storage. Assume the alian (example project name) project stores 333507833900 bytes. Because the data was uploaded at 08:00, billing starts at 09:07 and covers 15 hours.

     Billing cycles end at the end of each day, so the last record is excluded from the April 4 bill.

  2. Calculate the 24-hour average storage per storage billing rules, then apply the billing formula.

     -- Calculate average storage.
     333507833900 bytes × 15 / 1024 / 1024 / 1024 / 24 = 194.127109076362103 GB
     
     -- Calculate daily storage fee, rounded to four decimal places.
     194.127109076362103 GB × USD 0.0006 per GB per day = USD 0.1165 per day

• Analyze storage fee distribution using SQL

  You have completed Step 2 and created the maxcomputefee table:

  -- Analyze storage fees.
  SELECT  t.ds
          ,t.projectid
          ,t.storage
          ,CASE    WHEN t.storage < 0.5 THEN t.storage*0.01     -- Unit price is CNY 0.01 per GB per day if actual storage is > 0 MB and ≤ 512 MB.
                   WHEN t.storage >= 0.5  THEN t.storage*0.004  -- Unit price is CNY 0.004 per GB per day if actual storage is > 512 MB.
          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
  ;

  Results: Conclusions:

  • Storage increased on January 12 and decreased on January 14.

  • To optimize storage, set lifecycles for tables and delete unnecessary temporary tables.

• Analyze long-term storage, Infrequent Access (IA) storage, long-term storage access, and IA storage access using SQL

  You have completed Step 2 and created the maxcomputefee table:

  -- Analyze long-term storage fees.
  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 IA storage fees.
  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 long-term storage access fees.
  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 IA storage access fees.
  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 fees

MaxCompute charges you for Internet-based or cross-region data downloads based on the amount downloaded.

• Method 1: Analyze using Excel

  Metering type = DownloadEx indicates Internet download billing.

  1. For example, you find a record for download traffic of 0.036 GB (38,199,736 bytes). In this case, the download fee is calculated based on the billing rules that are described in Download pricing (pay-as-you-go) using the following formula: (38,199,736 bytes/1024/1024/1024) × 0.1166 USD/GB = 0.004 USD.

  2. Optimize downloads. Check if your Tunnel service uses the public network, which incurs fees. For more information, see Endpoint. For bulk downloads, if you are in Suzhou and your region is China (Shanghai), first download data to a virtual machine using an ECS instance in China (Shanghai), then use your subscription ECS instance to download the data.

• Method 2: Analyze using SQL

  You have completed Step 2 and created the maxcomputefee table:

  -- Analyze download fees.
  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 jobs (Tablestore and OSS)

• Method 1: Analyze using Excel

  Analyze records where Metering type is ComputationSqlOTS or ComputationSqlOSS in the usage records. Sort SQL external table fees. Fee formula: SQL input (bytes)/1024/1024/1024 × Unit price (USD 0.0044).

• Method 2: Analyze using SQL

  You have completed Step 2 and created the maxcomputefee table:

  -- Analyze Tablestore external table SQL job fees.
  SELECT  TO_CHAR(starttime,'yyyymmdd') AS ds
          ,projectid
          ,(computationsqlinput/1024/1024/1024)*1*0.03 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 fees.
  SELECT  TO_CHAR(starttime,'yyyymmdd') AS ds
          ,projectid
          ,(computationsqlinput/1024/1024/1024)*1*0.03 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 compute fees

• Method 1: Analyze using Excel

  Analyze records where Metering type is Spark in the usage records. Sort job fees. Fee formula: (MR/Spark compute (core*second)/3600 × Unit price (USD 0.1041)).

• Method 2: Analyze using SQL

  You have completed Step 2 and created the maxcomputefee table:

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