MaxCompute:Control computing costs

Estimate computing costs

You can estimate computing costs before you run computing jobs. For more information, see TCO tools in the "Select a billing method" topic. You can also configure alerts for resource consumption to prevent extra costs from being generated. If computing costs are high, you can use the methods described in this topic to reduce the costs.

Control the computing costs of SQL jobs

Some SQL jobs that trigger full table scans incur high computing costs. The frequent scheduling of SQL jobs may cause an accumulation of jobs, which also increases computing costs. If an accumulation occurs and the pay-as-you-go billing method is used, jobs are queued and require more resources. As a result, the bill generated the next day is abnormally high. You can use the following methods to control the computing costs of SQL jobs:

• Avoid frequent scheduling. MaxCompute provides a computing service to process large amounts of data at a time. It is different from real-time computing services. If SQL jobs are executed at short intervals, computing frequency is increased. The increased computing frequency and improper execution of SQL jobs cause increases in computing costs. If you require frequent scheduling, use CostSQL to estimate the costs of SQL jobs to avoid extra costs.

• Reduce full table scans. You can use the following methods:

  • Specify the required parameters to disable the full table scan feature. You can disable the feature for a session or project.

    -- Disable the feature for a session. 
    set odps.sql.allow.fullscan=false;
    -- Disable the feature for a project. 
    SetProject odps.sql.allow.fullscan=false;

  • Prune columns. Column pruning allows the system to read data only from the required columns. We recommend that you do not use the SELECT * statement. This is because a full table scan is triggered when you execute the statement.

    SELECT a,b FROM T WHERE e < 10;

    In this statement, the T table contains the a, b, c, d, and e columns. However, only the a, b, and e columns are read.

  • Prune partitions. Partition pruning allows you to specify filter conditions for partition key columns. This way, the system reads data only from the required partitions. This avoids the errors and waste of resources caused by full table scans.

    SELECT a,b FROM T WHERE partitiondate='2017-10-01';

  • Optimize SQL keywords that incur costs. The keywords include JOIN, GROUP BY, ORDER BY, DISTINCT, and INSERT INTO. You can optimize the keywords based on the following rules:

    • Before a JOIN operation, you must prune partitions. Otherwise, a full table scan may be performed. For more information about scenarios in which partition pruning is invalid, see Scenarios where partition pruning does not take effect in the "Check whether partition pruning is effective" topic.

    • Use UNION ALL instead of FULL OUTER JOIN.

      SELECT COALESCE(t1.id, t2.id) AS id, SUM(t1.col1) AS col1
       , SUM(t2.col2) AS col2
      FROM (
       SELECT id, col1
       FROM table1
      ) t1
      FULL OUTER JOIN (
       SELECT id, col2
       FROM table2
      ) t2
      ON t1.id = t2.id
      GROUP BY COALESCE(t1.id, t2.id);
      -- Optimized: 
      SELECT t.id, SUM(t.col1) AS col1, SUM(t.col2) AS col2
      FROM (
       SELECT id, col1, 0 AS col2
       FROM table1
       UNION ALL
       SELECT id, 0 AS col1, col2
       FROM table2
      ) t
      GROUP BY t.id;

    • Do not include GROUP BY in UNION ALL. Use GROUP BY outside UNION ALL.

      SELECT t.id, SUM(t.val) AS val
      FROM (
       SELECT id, SUM(col3) AS val
       FROM table3
       GROUP BY id
       UNION ALL
       SELECT id, SUM(col4) AS val
       FROM table4
       GROUP BY id
      ) t
      GROUP BY t.id;
      Optimized:---------------------------
      SELECT t.id, SUM(t.val) AS val
      FROM (
       SELECT id, col3 AS val
       FROM table3
       UNION ALL
       SELECT id, col4 AS val
       FROM table4
      ) t
      GROUP BY t.id;

    • To sort temporarily exported data, sort the data by using tools such as EXCEL instead of ORDER BY.

    • Do not use DISTINCT. Use GROUP BY instead.

      SELECT COUNT(DISTINCT id) AS cnt
      FROM table1;
      Optimized:---------------------------
      SELECT COUNT(1) AS cnt
      FROM (
       SELECT id
       FROM table1
       GROUP BY id
      ) t;

    • Do not use INSERT INTO to write data. Add a partition field instead. This reduces SQL complexity and saves computing costs.

• Do not execute SQL statements to preview table data. You can use the table preview feature to view table data. This method does not consume computing resources. If you use DataWorks, you can preview a table and query details about the table on the Data Map page. For more information, see View the details of a table. If you use MaxCompute Studio, double-click a table to preview its data.

• Select an appropriate tool for data computing. MaxCompute responds to a query within minutes. It is not suitable for frontend queries. Computing results are synchronized to an external storage system. Most users use relational databases to store results. We recommend that you use MaxCompute for lightweight computing jobs and relational databases, such as ApsaraDB for RDS, for frontend queries. Frontend queries require the real-time generation of query results. If the query results are displayed in the frontend, no conditional clauses are executed on the data. The data is not aggregated or associated with dictionaries. The queries do not even include the WHERE clause.

Control the computing costs of MapReduce jobs

References

• When you use MaxCompute, you can optimize costs in terms of storage and data uploads and downloads. For more information, see Optimize storage costs and Optimize the costs of data uploads and downloads.

• For more information about how to analyze and optimize exceptions in bills, see Manage costs.

• For more information about how to optimize computing costs and improve resource utilization, see Generate a computing resource optimization plan.