MaxCompute:Analyze the usage records of MaxCompute bills

Background information

MaxCompute is a big data analytics platform. You can use the subscription and pay-as-you-go billing methods for computing resources of MaxCompute. You are charged based on MaxCompute projects on a daily basis, and daily bills are generated before 06:00 of the next day. For more information about billable items and billing methods of MaxCompute, see Overview.

Step 1: Download the usage records of bills

Step 2 (Optional): Upload the usage records to MaxCompute

1. Create a MaxCompute table named maxcomputefee on the MaxCompute client (odpscmd). Sample statements:

   CREATE TABLE IF NOT EXISTS maxcomputefee 
   (
       projectid STRING COMMENT 'ProjectId'
       ,feeid STRING COMMENT 'MeteringId'
       ,meteringtime STRING COMMENT 'MeteringTime'
       ,type STRING COMMENT 'MeteringType, such as Storage, ComputationSQL, or DownloadEx'
       ,starttime STRING COMMENT 'StartTime'
       ,storage BIGINT COMMENT 'Storage'
       ,endtime STRING COMMENT 'EndTime'
       ,computationsqlinput BIGINT COMMENT 'SQLInput(Byte)'
       ,computationsqlcomplexity DOUBLE COMMENT 'SQLComplexity'
       ,uploadex BIGINT COMMENT 'UploadEx'
       ,download BIGINT COMMENT 'DownloadEx(Byte)'
       ,cu_usage DOUBLE COMMENT 'MRCompute(Core*Second)'
       ,Region STRING COMMENT 'Region'
       ,input_ots BIGINT COMMENT 'InputOTS(Byte)'
       ,input_oss BIGINT COMMENT 'InputOSS(Byte)'
       ,source_id STRING COMMENT 'DataWorksNodeID'
       ,source_type STRING COMMENT 'SpecificationType'
       ,RecycleBinStorage BIGINT COMMENT 'RecycleBinStorage'
       ,JobOwner STRING COMMENT 'JobOwner'
       ,Signature STRING COMMENT 'Signature'
   );

   Fields of usage records:

   • ProjectId: the ID or name of a MaxCompute project of your Alibaba Cloud account or a MaxCompute project of the Alibaba Cloud account to which the current RAM user belongs.

   • MeteringId: the billing ID. The billing ID indicates the ID of a storage task, an SQL computing task, an upload task, or a download task. The ID of an SQL computing task is specified by InstanceID, and the ID of an upload or download task is specified by Tunnel SessionId.

   • MeteringType: the billing type. Valid values: Storage, ComputationSql, UploadIn, UploadEx, DownloadIn, and DownloadEx. Only the items that are outlined in red are charged based on billing rules.

   • Storage: the volume of data that is read per hour. Unit: bytes.

   • StartTime or EndTime: the time when a job started to run or the time when a job stopped. Only storage data is obtained on an hourly basis.

   • SQLInput(Byte): the SQL computation item. This field specifies the amount of input data each time an SQL statement is executed. Unit: bytes.

   • SQLComplexity: the complexity of SQL statements. This field is one of the SQL billing factors.

   • UploadEx or DownloadEx(Byte): the amount of data that is uploaded or downloaded over the Internet. Unit: bytes.

   • MRCompute(Core*Second): the billable hours of a MapReduce job or a Spark job. The value is calculated by using the following formula: Number of cores × Period of time for running a job (second). After calculation, you must convert the result into billable hours.

   • InputOTS(Byte) or InputOSS(Byte): the amount of data that is read from Tablestore or Object Storage Service (OSS) by using Tablestore external tables or OSS external tables. Unit: bytes. These fields are used when fees for Tablestore external tables or OSS external tables are generated.

   • RecycleBinStorage: the amount of data that is read for backup per hour. Unit: bytes.

   • Region: the region where the MaxCompute project resides.

   • JobOwner: the user who submits the job.

   • Signature: the signature of an SQL job. The signatures of SQL jobs that have the same content and are repeatedly run or scheduled multiple times are the same.

2. Run the following Tunnel command to upload the usage records of bills.

   Before you upload a CSV file that contains the usage records of bills to MaxCompute, make sure that the number and data types of columns in the CSV file are the same as the number and data types of columns in the maxcomputefee table. If the preceding condition is not met, the data upload fails.

   tunnel upload ODPS_2019-01-12_2019-01-14.csv maxcomputefee -c "UTF-8" -h "true" -dfp "yyyy-MM-dd HH:mm:ss";

   

   Note

   • For more information about the configurations of Tunnel commands, see Tunnel commands.

   • You can also use the data import feature of DataWorks to upload the usage records of bills. For more information, see Use DataWorks (offline and real-time).

3. Execute the following statement to check whether all usage records are uploaded:

   SELECT * FROM maxcomputefee limit 10;

   

Step 3: Analyze the usage records of bills

Analyze the fees of SQL jobs

Analyze the trend of the number of jobs

Analyze storage fees

• Use Excel to analyze the reasons for charging CNY 0.01 for storage

  This issue is caused by existing data that is stored on MaxCompute and the data volume does not exceed 0.5 GB.

  View the records whose MeteringType is Storage. 508 bytes of data is stored for the maxcompute_doc project. The following figure shows the details. You are charged CNY 0.01 for data whose size is no more than 512 MB based on storage billing rules. If the data is only used for testing, you can perform the following operations to resolve the issue:

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

  • If the project is no longer used, log on to the MaxCompute console and delete the project on the Projects page.

• Use Excel to analyze the storage fees of data that is stored less than one day

  1. View the records whose MeteringType is Storage. A total of 333,507,833,900 bytes of data is stored for the alian project. The data is uploaded at 08:00. Therefore, storage is billed from 09:07 and you are charged for 15-hour storage.

     Note

     If the billing cycle is one day, the billing stops at the end of each day. As a result, the last record is not included in the bill of the day.

  2. The average storage within 24 hours is calculated based on storage billing rules. Then, the average storage fee is calculated by using the billing formula.

     -- Calculate the average storage. 
     333507833900 Byte×15/1024/1024/1024/24=194.127109076362103 GB
     
     The storage fee per day is calculated by using the following formula. Four decimal places are retained in the result. 
     194.127109076362103 GB × USD 0.0006 per GB per day = USD 0.1165 per day

• Analyze usage records by using MaxCompute SQL: Before you use MaxCompute SQL to analyze usage records, make sure that the data upload described in Step 2 is complete, and the maxcomputefee table is generated.

  -- Analyze storage fees. 
  SELECT  t.ds
          ,t.projectid
          ,t.storage
          ,CASE    WHEN t.storage < 0.5 THEN t.storage*0.01     --- If the actually used storage space of a project is greater than 0 MB and less than or equal to 512 MB, the unit price for the storage space is CNY 0.01 per GB per day.
                   WHEN t.storage >= 0.5  THEN t.storage*0.004  --- If the actually used storage space of a project is greater than 512 MB, the unit price for the storage space is CNY 0.004 per GB per 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 figure shows the execution result. The following conclusions can be drawn from the execution result:

  • Storage fees increased on January 12, 2019 and decreased on January 14, 2019.

  • To reduce storage fees, we recommend that you specify a lifecycle for tables and delete unnecessary temporary tables.

• Analyze long-term storage, Infrequent Access (IA) storage, long-term storage access, and low-frequency storage access by using MaxCompute SQL: Before you use MaxCompute SQL to analyze the usage records, make sure that the data upload described in Step 2 is complete, and the maxcomputefee table is generated.

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

Analyze the fees of MapReduce jobs

• Analyze usage records by using Excel: Analyze the records whose MeteringType is MapReduce in the usage records. Calculate and sort the fees of MapReduce jobs based on the value that is specified by SpecificationType. The fee of a MapReduce job or a Spark job is calculated by using the following formula: Number of cores × Period of time for running a job (second)/3600 × Unit price (USD 0.0690).

• Analyze usage records by using MaxCompute SQL: Before you use MaxCompute SQL to analyze usage records, make sure that the data upload described in Step 2 is complete, and the maxcomputefee table is generated.

  -- Analyze the fees of MapReduce jobs. 
  SELECT  TO_CHAR(starttime,'yyyymmdd') AS ds
          ,projectid
          ,(cu_usage/3600)*0.0690 AS mr_fee
  FROM    maxcomputefee
  WHERE   type = 'MapReduce'
  AND     TO_CHAR(starttime,'yyyymmdd') >= '20190112'
  GROUP BY TO_CHAR(starttime,'yyyymmdd')
           ,projectid
           ,cu_usage
  ORDER BY mr_fee DESC
  ;

Analyze the fees of jobs that use Tablestore external tables or OSS external tables

• Analyze usage records by using Excel: Analyze the records whose MeteringType is ComputationSqlOTS or ComputationSqlOSS in the usage records. Sort the computing fees for SQL jobs that use Tablestore external tables or OSS external tables. The computing fee is calculated by using the following formula: SQLInput(Byte)/1024/1024/1024 × Unit price (USD 0.0044).

• Analyze usage records by using MaxCompute SQL: Before you use MaxCompute SQL to analyze usage records, make sure that the data upload described in Step 2 is complete, and the maxcomputefee table is generated.

  -- Analyze the fees of SQL jobs that use Tablestore external tables. 
  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 the fees of SQL jobs that use OSS external tables. 
  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 the computing fees for Spark jobs

• Analyze usage records by using Excel: Analyze the records whose MeteringType is spark in the usage records. Sort the computing fees for Spark jobs. The computing fee for a Spark job is calculated by using the following formula: Number of cores × Period of time for running a job (second)/3600 × Unit price (USD 0.1041).

• Analyze usage records by using MaxCompute SQL: Before you use MaxCompute SQL to analyze usage records, make sure that the data upload described in Step 2 is complete, and the maxcomputefee table is generated.

  -- Analyze the fees of Spark jobs. 
  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
  ;

References

TO_CHAR is a date function or a string function. For more information about the TO_CHAR function, see TO_CHAR.