Analyzing DLI Billing Data

Application Scenarios

You can analyze DLI billing data (account information has been masked) on the big data analysis platform of DLI, find possible optimization, and figure out some measures to reduce costs for using DLI.

Analysis Process

Perform the following steps to analyze billing data and reduce costs:

Step 1: Obtaining Consumption Data. Obtain billing data of an account.

Step 2: Analyzing Billing Data and Reducing Costs. Analyze the consumption data, find the resources or users with high expenditure, and provide optimization measures to reduce cost.

Resources and Costs

Step 1: Obtaining Consumption Data

1. Obtain billing details.
   1. Log in to the DLI console.
   2. Click Billing & Costs on the upper right corner of the page. Choose Bills.
      Figure 1 Bills
      
   3. On the Dashboard page of the Billing Center, click Expenditure Details. On the displayed page, set Data Type to Usage Type and Data Period to Details. Set time to the billing cycle you want.
      In the title row of the displayed table, set Service Type to Data Lake Insight (DLI) and Resource Type to DLI cuh. Click Export. On the Export page, configure Export Content and Period as you need, and click Export. The Export History page is displayed.

      Figure 2 DLI Bills
      
   4. On the Export History page, wait until the file status changes to Successful. Click Download.

Step 2: Analyzing Billing Data and Reducing Costs

1. Analyze billing details.
   1. Upload the billing details downloaded in Step 1: Obtaining Consumption Data to the created OBS bucket.
   2. Create a table on DLI.
      1. Log in to the DLI console. In the navigation pane, choose SQL Editor. Select spark for Engine, and select the queue and database. In this example, the default queue and database are used.
      2. The downloaded file contains information such as time and usage. Create a table on DLI based on these table headers. For details, see the following example.

         CREATE TABLE `spending` (
           account_period string,
           EnterpriseProject string,
           EnterpriseProjectID string,
           accountID string,
           product_type_code string,
           product_type string,
           product_code string,
           product_name string,
           product_id string,
           mode string,
           time1 string,
           use_start string,
           use_end string,
           orderid string,
           ordertime string,
           resource_type string,
           resource_id string,
           resouce_name string,
           tag string,
           skuid string,
           `c22name` STRING,
           `c23name` STRING,
           `c24name` STRING,
           `c25name` STRING,
           `c26name` STRING,
           `c27name` STRING,
           `c28name` STRING,
           `c29name` STRING,
           size STRING,
           `c31name` STRING,
           `c32name` STRING,
           `c33name` STRING,
           `c34name` STRING,
           `c35name` STRING,
           `amount` STRING,
           `c37name` STRING,
           `c38name` STRING,
           `c39name` STRING,
           `c40name` STRING,
           `c41name` STRING,
           `c42name` STRING,
           `c43name` STRING,
           `c44name` STRING,
           `c45name` STRING,
           `c46name` STRING,
           `c47name` STRING,
           `c48name` STRING,
           `c49name` STRING,
           `c50name` STRING,
           `c51name` STRING,
           `c52name` STRING,
           `c53name` STRING,
           `c54name` STRING
         ) USING csv options (
           path 'obs://xxx/Spendings(ByTransaction)_20200501_20200531.csv',
           header true)

   3. Query resource_id and resource_name with the highest amount within the period.

      The following statement shows the amount charged for using the SQL and Flink queues.

      select resource_id, resouce_name, sum(size) 
         as usage, sum(amount) 
         as sum_amount 
         from spending 
         group by resource_id, resouce_name 
         order by sum_amount desc

      Figure 3 Query results
      
   4. Run the following statements to analyze the usage periods of SQL and Flink resources:

      select * from spending where resource_id = 'd91d4616-b10c-471a-820d-e676e6c5f4b4' order by ordertime

      The SQL queue was billed each hour from May 14 2020 17:00:00 GMT+08:00 to May 28, 2020 10:00:00 GMT+08:00.

      Similarly, the Flink queue was continuously used from May 14, 2020 17:00:00 GMT+08:00 to May 28 2020 10:00:00 GMT+08:00.

2. Suggestion for reducing the cost

   You can change the SQL and Flink queues to yearly/monthly queues for lower costs. If you are sure about the number of CUHs required for a job, you can purchase a package to reduce the cost.