MaxCompute:Consumption control

You can set a limit for the resources that an SQL statement can consume for a project or a session.

• Set a limit for a project.

  The project owner or a user with the Super_Administrator role must execute the following command to set or remove the limit on resources consumed by an SQL statement:

  -- Impose the upper limit on the resources consumed by an SQL statement.
  setproject odps.sql.metering.value.max=<m_value>;
  
  -- Remove the upper limit on the resources consumed by an SQL statement.
  setproject odps.sql.metering.value.max;

  m_value represents the maximum threshold for resources consumed by an SQL statement. It is calculated as SQL read volume (GB) × SQL complexity, not the actual consumption amount.

  Note

  The resources consumed by an SQL statement are limited by m_value. If the estimated consumption exceeds m_value, the statement will not execute.

• Set a limit for a session.

  Submit the following command with the SQL statement you want to execute to set the limit for resources consumed by the statement. If the command is not added before the SQL statement, the limit is removed. This configuration only applies to the current statement.

  set odps.sql.metering.value.max=<m_value>;

Considerations

• Determine the m_value for a project based on historical SQL consumption and your expected usage.

  Download detailed daily metering information and use the project as the granularity to calculate the computing consumption of SQL tasks which are classified as ComputationSql. This is done by calculating SQL read volume (GB) × SQL complexity. Choose an appropriate value based on the highest normal daily consumption and business fluctuations.

  Alternatively, convert the computing consumption into a fee . Determine an appropriate fee based on the business situation, and then convert it into a consumption amount .

• First, set the consumption limit for a project. Then, set the m_value limit for specific SQL statements at the session level.

  Note

  • Before setting a session-level consumption limit, make sure that the corresponding SQL indeed requires a session-level limit to prevent the misuse of this function.

  • If you set limits for both a project and a session, the session configuration takes precedence. For example, if the project's m_value is 100, and the session's m_value for an SQL statement is 200, the statement's execution is restricted only if the estimated consumption exceeds 200. SQL statements without a session-level limit are subject to the project-level limit and will not execute if the estimated resource consumption exceeds 100.

• Configuring an upper limit for either a project or a session can impact task execution. We recommend that you utilize the task failure alert feature, such as DataWorks' intelligent monitoring, to promptly detect task failures and intervene manually, ensuring that critical tasks are not hindered and business operations remain unaffected.

To set the daily cumulative SQL consumption limit for a project, use the following command. Only the project owner or a user with the Super_Administrator role can execute this command.

setproject odps.costcontrol.rule={"byDate":{"sql":Limit}};

• byDate: Daily cumulative. The cumulative consumption from 00:00:00 to 23:59:59 on the current day is calculated based on UTC+8. The cumulative consumption is reset the following day.

• Limit: The daily cumulative consumption threshold for standard SQL in a project, measured in USD. This threshold does not account for any discounts or promotions.

  Note

  Consumption of a single SQL statement = Scanned data volume (GB) × Complexity × 0.0438. In the SAU (Riyadh - Partner Region), the standard billing price for SQL is 0.05256 USD/GB.

After executing the command:

• If this is your first time setting the limit, the system calculates the cumulative consumption of all standard SQL statements in a project for the current day and estimates the consumption of new standard SQL statements. If you modify the value, the cumulative consumption is not recalculated. The estimated consumption of new standard SQL statements is verified against the cumulative consumption and the latest value to determine if execution is restricted. The system begins to calculate the cumulative consumption of standard SQL statements in a project for the current day and estimates the consumption of new standard SQL statements.

• If the cumulative consumption and the estimated consumption exceed the Limit, new standard SQL statements are blocked and cannot be executed. For example, if you execute the command setproject odps.costcontrol.rule={"byDate":{"sql":100}}; to set the consumption limit, an error is returned when the daily cumulative consumption of standard SQL statements in a project exceeds the limit. The error message is as follows:

  Exceed Cost Limit : 
  {"AlreadyCost":"100.1","InstanceId":"xxx","Limit":"100","Project":"xxx","TaskType":"SQL","ThisTaskWillCost":"0","TimeWindow":"BYDATE"}

  Note

  We recommend using the task failure alert feature, such as DataWorks' intelligent monitoring, to promptly detect task failures and enable manual intervention. This helps prevent critical tasks from being hindered and impacting your business operations.