Analyzing Billing Consumption Data Using DLI

Scenario

This article explains how to use actual consumption data on Huawei Cloud DLI (all account information in this document has been desensitized) to perform analysis on the DLI big data analytics platform, identify opportunities for cost optimization, and provide optimization measures to reduce costs during DLI usage.

Process Overview

The process for billing analysis and optimization using DLI mainly includes the following steps:

Step 1: Obtain Consumption Data. Obtain an account's actual consumption data.

Step 2: Analyze and Optimize the Account's Consumption Structure. Analyze the account's spending structure on DLI, identify high-cost resources or users, and propose optimization measures to reduce costs.

Resource and Cost Planning

Step 1: Obtain Consumption Data

1. Obtain detailed consumption data.
   1. Log in to the console using your Huawei Cloud account.
   2. Click Billing & Costs in 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 displayed data header row, set Service Type to Data Lake Insight (DLI), and Resource Type to DLI cuh, and click Export. On the Export page, set Export Content and Period and click Export. The Export History page appears.

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

Step 2: Analyze and Optimize the Account's Consumption Structure

1. Analyze detailed consumption on DLI.
   1. Upload the detailed consumption data downloaded in Step 1: Obtain Consumption Data to a pre-created OBS bucket.
   2. Create a table in DLI.
      1. Log in to the DLI console. In the navigation pane, choose SQL Editor. Set Engine to spark and select the execution queue and database. In this example, the default queue and database are used.
      2. The downloaded file contains data such as time and usage. Create a table in DLI according to the header semantics. Refer to the following example, where the amount column represents cost.

         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,
           resource_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 consumption in this period.

      Using the following statement, you can find that both the SQL and Flink queues have a cost of 1,842, accounting for 98% of the total cost of 3,754.

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

      Figure 3 Query results
      
   4. Use the following statement to further analyze the time periods of consumption for the SQL and Flink resources.

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

      You can observe that the SQL queue starts generating new hourly charges from May 14, 2020 17:00:00 GMT+08:00 and continues until May 28, 2020 10:00:00 GMT+08:00, indicating continuous usage during this period.

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

2. Optimization recommendations.

   Based on the analysis above, the SQL and Flink queues are almost continuously used. You are advised to purchase yearly/monthly queues to reduce usage costs. In addition, for jobs with clearly estimated CUH requirements, you can pre-purchase corresponding CUH packages to reduce usage costs.