Shulou(Shulou.com)05/31 Report--

How to analyze the MaxCompute bill, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

MaxCompute Billing background

MaxCompute is a commercial big data analysis platform. There are two billing methods for computing resources: prepaid and postpaid. And the product is billed according to the dimension of project every day (the bill will basically be produced by 6: 00 the next day). The following is the real data of customers on the cloud, so the screenshots below are all mask.

For more information on MaxCompute metering and billing, please see the official document:

Cdn.com/98c5157f8a4be2c5e1789b9ecbde657800a6a18e.png ">

But usually, during the data development stage or on the eve of launch, we will issue a bill that fluctuates (usually increases). In fact, users can first analyze the bill fluctuation by self-help, and then force their homework to optimize. Aliyun fee Center is a good channel, where all commercially charged products can be downloaded.

Get billing information

Usually you need to use the main account to view the details of the bill. If you need to use a sub-account to view the billing information, please first refer to the fee center RAM configuration policy line sub-account authorization.

Step1: use the main account or authorized RAM sub-account to log in to the Ali Cloud console.

Step2: enter the fee center in the upper right corner.

Prepaid post-payment refers to the storage and download fees generated by the project after the implementation of the prepaid billing mode (storage and download fees are only postpaid).

Step4: in order to facilitate batch analysis of data, we choose to download and use the record csv file for local analysis.

-- csv header item number, measurement information number, data classification, storage (Byte), SQL read volume (Byte), SQL complexity (Byte), public network uplink traffic (Byte), public network downstream traffic (Byte), MR job calculation, start time, end time, SQL read volume _ access OTS (Byte), SQL read volume _ access OSS (Byte) upload bill details to MaxCompute

Use the record details field to explain:

Project number: the MaxCompute project list of the main account under the current account or sub-account.

Measurement information number: it will contain the billing information numbers for storage, calculation, upload and download, SQL is instanceid, upload and download is Tunnel sessionid.

Data classification: Storage (storage), ComputationSql (computing), UploadIn (intranet upload), UploadEx (extranet upload), DownloadIn (intranet download), DownloadEx (extranet download). According to the billing rules, only red is the actual billing item.

Start time / end time: it is measured according to the actual job execution time, and only Storage takes data once per hour.

Storage (Byte): the unit of storage read per hour is Byte.

SQL read quantity (Byte): the SQL calculation item, the amount of input data of the SQL during each SQL execution, in Byte.

SQL complexity (Byte): the complexity of each execution of SQL, which is one of the SQL billing factors.

Public network uplink traffic (Byte) and public network downlink traffic (Byte): the amount of data transmitted and downloaded on the public network, in Byte.

MR Job Computing (CoreSecond): the MR job is calculated in coresecond and needs to be converted to hour at the time of calculation.

SQL read quantity _ access OTS (Byte), SQL read quantity _ access OSS (Byte): the amount of data read after the external table is charged (in Byte).

① confirms CSV file data, especially column delimiters, and so on (UE is recommended).

③ creates the MaxCompute table to store the consumption details of the download.

DROP TABLE IF EXISTS maxcomputefee CREATE TABLE IF NOT EXISTS maxcomputefee (projectid STRING COMMENT 'project number', feeid STRING COMMENT 'billing information number', type STRING COMMENT 'data classification Including Storage, ComputationSQL, DownloadEx', starttime DATETIME COMMENT 'start time', storage BIGINT COMMENT 'storage capacity', endtime DATETIME COMMENT 'end time', computationsqlinput BIGINT COMMENT 'input data volume', computationsqlcomplexity DOUBLE COMMENT 'sql complexity', uploadex BIGINT COMMENT 'public network uplink traffic Byte', download BIGINT COMMENT' public network downlink traffic Byte', cu_usage DOUBLE COMMENT'MR calculation * second' Input_ots BIGINT COMMENT 'access OTS data input', input_oss BIGINT COMMENT 'access OSS data input')

④ Tunnel uploads data. For more information on the configuration of Tunnel, please see the official documentation.

Odps@ sz_mc > tunnel upload / Users/yangyi/Desktop/ODPS_2019-01-12 2019-01-14.csv maxcomputefee-c "UTF-8" >

Of course, users can also do this through the function of importing DataWorks data. For more information, please see the operation steps.

⑤ validates the data.

Analyze billing data through SQL. 1. Analyze SQL expenses.

Cloud customers who use MaxCompute,95% can meet their needs through SQL, and SQL also accounts for the vast majority of consumption growth.

SQL cost = one SQL calculation cost = calculating the amount of input data SQL complexity 0.30 yuan / GB

-- Analysis of SQL consumption Ranked by SQL 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.3 AS sqlmoneyFROM maxcomputefeeWHERE TYPE = 'ComputationSql'AND to_char (endtime,'yyyymmdd') > =' 20190112'GROUP BY to_char (endtime,'yyyymmdd') Feeid, projectid, computationsqlcomplexityORDER BY sqlmoney DESC LIMIT 10000

-- query results

Based on the results of this SQL execution, the following conclusions can be drawn:

The points that can be optimized for large tasks: * * whether the amount of data reading and complexity can be reduced to optimize the cost.

You can also analyze the trend of SQL consumption amount within a certain period of time by summarizing the ds field (by day). For example, using tools such as local excle or cloud QuickBI to draw line charts to more intuitively reflect the trend of the job.

Get the specific instanceid and do wait instanceid; in the console or DataWorks script to check the specific job and SQL.

Get the DataWorks node name from logview:

Open SourceXML in logview to view the specific execution information, such as SKYNET_NODENAME represents the node name of DataWorks (of course, only the jobs executed by the scheduled system have values, and the temporary query is empty, as shown in the following figure). If you get the node name, you can quickly find the node in DataWorks for optimization or check the responsible person.

2. Analyze the growth trend of homework.

In general, the increase in cost is actually behind the sharp increase in the volume of work, which may be repeated execution, or the allocation of scheduling attributes is not very reasonable.

SELECT TO_CHAR (endtime,'yyyymmdd') AS ds, projectid, COUNT (*) AS tasknumFROM maxcomputefeeWHERE TYPE = 'ComputationSql'AND TO_CHAR (endtime,'yyyymmdd') > =' 20190112'GROUP BY TO_CHAR (endtime,'yyyymmdd'), projectidORDER BY tasknum DESCLIMIT 10000

-- implementation results--

From the execution results, we can see the fluctuation trend of the number of jobs submitted to MaxCompute and executed successfully from 12 to 14 days.

3. Analyze the storage cost

The billing rules for storage charges are relatively complex because the details downloaded are fetched once an hour. According to the MaxCompute storage billing rules, the overall 24-hour summation and then the tiered charge will be charged after the average value. See the official website for details.

-- Analysis of storage costs SELECT t.ds, t.projectid, t.storage, CASE WHEN t.storage

= 0.5 AND t.storage 100 AND t.storage 1024 AND t.storage 10240 AND t.storage 102400 AND t.storage = '20190112' GROUP BY to_char (starttime,'yyyymmdd'), projectid) tORDER BY storage_fee DESC

-- implementation results--

According to the calculation results, a conclusion can be drawn:

There is a growth process for the highest storage on the 13th, but there is a decrease on the 14th.

Storage optimization, it is recommended that tables set life cycle, delete temporary tables that have not been used for a long time, etc.

4. Analyze the download cost

For public network or cross-Region data downloads, MaxCompute will be billed according to the size of the downloaded data. The billing formula is as follows: one download fee = download data volume * 0.8 CNY / GB.

-- analyze and download consumption details SELECT TO_CHAR (starttime,'yyyymmdd') AS ds, projectid, SUM ((download/1024/1024/1024) * 0.8) AS download_feeFROM maxcomputefeeWHERE type = 'DownloadEx'AND TO_CHAR (starttime,'yyyymmdd') > =' 20190112'GROUP BY TO_CHAR (starttime,'yyyymmdd'), projectidORDER BY download_fee DESC

According to the implementation results, we can also analyze the trend of download costs in a certain period of time. In addition, you can view specific historical information through tunnel show history. For more information on specific commands, please see the official documentation.

The following computing jobs are similar to SQL. You can write SQL according to the official billing document.

5. Analyze the consumption of MapReduce homework

Calculation cost of MR task on the same day = total calculation of the day * 0.46 yuan

-- Analysis of MR job consumption SELECT TO_CHAR (starttime,'yyyymmdd') AS ds, projectid, (cu_usage/3600) * 0.46 AS mr_feeFROM maxcomputefeeWHERE type = 'MapReduce'AND TO_CHAR (starttime,'yyyymmdd') > =' 20190112'GROUP BY TO_CHAR (starttime,'yyyymmdd'), projectid, cu_usageORDER BY mr_fee DESC;6, analysis external table jobs (OTS and OSS)

Billing rules for SQL external table function: cost of one SQL calculation = calculation of input data SQL complexity 0.03 CNY / GB

-- Analysis of OTS external table SQL job consumption SELECT TO_CHAR (starttime,'yyyymmdd') AS ds, projectid, (input_ots/1024/1024/1024) * 1x 0.03 AS ots_feeFROM maxcomputefeeWHERE type = 'ComputationSql'AND TO_CHAR (starttime,'yyyymmdd') > =' 20190112'GROUP BY TO_CHAR (starttime,'yyyymmdd'), projectid, input_otsORDER BY ots_fee DESC -- Analysis of OSS external table SQL job consumption SELECT TO_CHAR (starttime,'yyyymmdd') AS ds, projectid, (input_oss/1024/1024/1024) * 1x 0.03 AS ots_feeFROM maxcomputefeeWHERE type = 'ComputationSql'AND TO_CHAR (starttime,'yyyymmdd') > =' 20190112'GROUP BY TO_CHAR (starttime,'yyyymmdd'), projectid, input_ossORDER BY ots_fee DESC

The growth (surge) of MaxCompute product consumption is often due to the substantial increase in the volume of work. In order to optimize their own expenses and costs, the first choice is to know what problems exist in their SQL and other assignments, and which SQL should be optimized.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.