MaxCompute:User guide for MaxCompute Query Accelerator (MaxQA)

Official launch date by region

Notes

Newly created interactive quota groups default to MaxQA mode. Existing MCQA services are not affected.

Create a MaxQA instance

MaxQA is available only for subscription MaxCompute projects. To create a MaxQA instance, follow these steps:

1. Log on to the MaxCompute console and select a region in the top-left corner.

2. In the navigation pane on the left, choose Manage Configurations > Quotas.

3. On the Quotas page, find the target quota and click Quota Configuration in the Actions column.

4. Basic configuration

   1. On the Quota Configuration page, click the Basic Configurations tab, and then click Edit Basic Configurations.

   2. Click Add Level-2 Quota, enter a Quota Name, and set Type to Interactive.

      • When you use MaxQA, you must specify the Quota Name. We recommend using a business-related name to easily distinguish between multiple instances.

      • Interactive quotas are dedicated to running query acceleration (MCQA) jobs.

5. Click OK.

After you create the instance, the status of the interactive quota group changes to Starting. This process takes 5 to 8 minutes. You can use the instance after its status changes to Running.

Delete a MaxQA instance

1. On the Quotas page, find the target quota and click Quota Configuration in the Actions column.

2. On the Quota Configuration page, click the Basic Configurations tab, and then find the target interactive quota and click Delete in the Actions column.

The deletion runs in the background. The page does not display status updates or deletion progress.

Scale a MaxQA instance

You can scale out or scale in an interactive quota group as needed. To do so, follow these steps:

1. On the Quotas page, find the target quota and click Quota Configuration in the Actions column.

2. On the Quota Configuration page, click the Basic Configurations tab, and then click Edit Basic Configurations.

3. Click Add Level-2 Quota and adjust the Reserved CUs [minCU,maxCU] for the interactive quota group that you want to scale. Increasing the value scales out the group, and decreasing the value scales it in.

   Note

   • The minimum step size for CUs is 25.

   • The minimum number of CUs must be 25 or greater. If you do not need interactive resources, set the value to 0 or delete the quota group.

   • Elastically Reserved CUs are not supported for interactive quotas.

4. Click OK.

   The reserved CUs for the interactive quota group are adjusted.

The Actions column for the interactive quota group displays Scaling Out or Scaling In. Scaling out takes about 5 to 8 minutes. Scaling in may take longer because it depends on when the running jobs are complete. In a worst-case scenario, the system may wait for jobs to time out to ensure that all jobs on the scaled-in machines are finished before the scale-in operation begins. Scaling operations do not affect running jobs.

Configure time-based scaling rules for MaxQA

You can configure a time-based scaling plan to schedule scaling operations. To do so, follow these steps:

1. On the Quotas page, find the target quota and click Quota Configuration in the Actions column.

2. On the Quota Configuration page, click the Scaling Configuration tab.

3. Configure a time-based scaling configuration

   1. On the Scaling Configuration tab, click Add Configuration Plan. In the Add Configuration Plan dialog box, enter a Configuration Plan Name and configure Reserved CUs [minCU,maxCU] for the level-2 quota.

   2. To modify an existing configuration plan:

      On the Scaling Configuration tab, find the target configuration plan and click Edit in the Actions column to update it.

4. Configure a time-based plan

   1. On the Scaling Configuration tab, click Edit Time Plan in the Scheduled Scaling Management area.

   2. Click Add Effective Period and select an Effective Start Time and a Configuration Plan.

      You can set different quota configuration plans to take effect at different times of the day to manage quota configurations based on time. For more information, see Quota management for compute resources.

Scheduling policy

When you submit a job, you must explicitly specify the name of the interactive quota group in the client or Java Database Connectivity (JDBC) connection string. The job is then scheduled to the corresponding MaxQA instance.

Backoff policy

MaxQA does not support automatic backoff policies. If a MaxQA job fails because of limits or other reasons, you must resubmit it or submit it to a batch processing quota group.

You can connect to MaxQA in the following ways:

• MaxCompute client

• Ad-hoc queries or data development in DataWorks

• SQL Analysis in the MaxCompute console

• Use MaxQA in MaxCompute Studio

• Connect through JDBC

• BI analysis tools

• Java SDK

• Python SDK

Enable MaxQA using the MaxCompute client

1. Install and configure MaxCompute client (odpscmd) V0.51.1 or later. For more information, see Connect using the local client (odpscmd).

2. (Optional) In the conf directory of the client installation path, open the odps_config.ini file and add the following command to the end of the file.

   -- Enables the MaxQA connection information cache.
   enable_quota_cache=true

3. In the bin directory of the installation path, run the script file to start the MaxCompute client.

   Important

   • For Linux or macOS, run the odpscmd script file.

   • For Windows, double-click the odpscmd.bat file.

4. Execute the following command before you run a job. Subsequent jobs submitted by the client will then use MaxQA mode and run in the MaxQA instance that corresponds to the interactive quota group.

   USE QUOTA <name_of_the_interactive_quota_group>;

   You can also specify the MaxQA instance for the current job at the session level.

   SET odps.task.wlm.quota=<name_of_the_interactive_quota_group>;

Enable MaxQA for data development in DataWorks

1. Log on to the DataWorks console and select a region in the upper-left corner.

2. In the navigation pane on the left, choose Data Development and O&M > Data Development.

3. Select Workspace, and click Go To Data Studio.

4. On the Data Development page, click Debugging Configurations on the right.

5. On the Debugging Configurations page, for Computing Resource, select the attached MaxCompute project, and for Computing Quota, select the interactive configuration group.

6. After you complete the configuration, jobs run by the SQL node are scheduled to the MaxQA instance of the interactive quota group.

For more information about the procedure, see Create a MaxCompute SQL node in DataWorks.

Enable MaxQA for SQL Analysis in the MaxCompute console

1. Log on to the MaxCompute console and select a region in the top-left corner.

2. In the navigation pane on the left, choose Data Exploration > SQL Analysis.

3. After you enter SQL statements in the SQL editor, click Run Configurations on the right to configure the Project and Computing Quota.

4. Select the compute resources that are associated with the interactive quota group.

5. After you complete the configuration, jobs that are run on the SQL Analysis page are scheduled to the MaxQA instance of the interactive quota group.

For more information about the procedure, see SQL Analysis.

Use MaxQA in MaxCompute Studio

Ensure that you have installed MaxCompute Studio and connected it to your MaxCompute project. You can then run a script in MaxCompute Studio to use MaxQA. To do so, follow these steps:

1. Install and configure MaxCompute Studio 4.3.2 or later. For more information, see Install MaxCompute Studio.

2. Create a MaxCompute Studio project and connect it to your MaxCompute project. For more information, see Manage project connections.

3. Create a MaxCompute SQL script in the project. For more information, see Develop and submit SQL scripts.

4. Enter a SQL script statement in the MaxCompute SQL script. From the execution mode drop-down list, select MaxQA.

5. Click the specify additional parameters icon , enter the name of the MaxQA quota, and then click OK.

6. Run the script. The job then uses the MaxQA query acceleration feature.

Enable MaxQA using JDBC

When you use JDBC to connect to MaxCompute, you can enable MaxQA by following these steps. For more information about how to use JDBC to connect to MaxCompute, see Instructions.

1. Download the source code that supports MaxQA and can be compiled.

2. Configure the Pom dependency in Maven.

   <dependency>
      <groupId>com.aliyun.odps</groupId>
      <artifactId>odps-jdbc</artifactId>
      <version>${Latest Version}</version>
   </dependency>

   Important

   The Latest Version in the preceding code is a placeholder. You must replace it with a specific SDK version number to compile the project. To view all released versions of odps-jdbc, visit the Maven Central Repository. We recommend that you use the latest version. The version number format is X.X.X.

3. Create a Java program based on the source code and adapt the information to your needs. For more information, see MaxCompute JDBC. The following is an example.

   import java.sql.Connection;
   import java.sql.DatabaseMetaData;
   import java.sql.DriverManager;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.Statement;
   
   public class MaxCompute {
   
       private static final String DRIVER_NAME = "com.aliyun.odps.jdbc.OdpsDriver";
       // An Alibaba Cloud account AccessKey has full permissions on all APIs and poses a high security risk. We strongly recommend that you create and use a RAM user to make API calls or perform O&M. To create a RAM user, log on to the RAM console.
       // This example shows how to save the AccessKey ID and AccessKey secret to environment variables. You can also save them to a configuration file as needed.
       // We strongly recommend that you do not save the AccessKey ID and AccessKey secret in your code to avoid key leakage.
       private static String accessId = System.getenv("ALIBABA_CLOUD_ACCESS_KEY_ID");
       private static String accessKey = System.getenv("ALIBABA_CLOUD_ACCESS_KEY_SECRET");
   
       public static void main(String[] args) throws SQLException {
           try {
               Class.forName(DRIVER_NAME);
           } catch (ClassNotFoundException e) {
               e.printStackTrace();
               System.exit(1);
           }
           Connection conn = DriverManager.getConnection(
                   "jdbc:odps:https://service.cn-hangzhou.maxcompute.aliyun.com/api?project=maxqatomax&charset=UTF-8&interactiveMode=true&quotaName=maxfor1&autoSelectLimit=1000000000",
                   MaxCompute.accessId, MaxCompute.accessKey);
   
           // Create a table.
           Statement stmt = conn.createStatement();
           final String tableName = "jdbc_test11";
           stmt.execute("DROP TABLE IF EXISTS " + tableName);
           stmt.execute("CREATE TABLE " + tableName + " (key BIGINT, value STRING)");
   
           // Get metadata.
           DatabaseMetaData metaData = conn.getMetaData();
           System.out.println("product = " + metaData.getDatabaseProductName());
           System.out.println("jdbc version = "
                   + metaData.getDriverMajorVersion() + ", "
                   + metaData.getDriverMinorVersion());
           ResultSet tables = metaData.getTables(null, "default", tableName, null);
           while (tables.next()) {
               String name = tables.getString("TABLE_NAME");
               System.out.println("inspecting table: " + name);
               ResultSet columns = metaData.getColumns(null, null, name, null);
               while (columns.next()) {
                   System.out.println(
                           columns.getString("COLUMN_NAME") + "\t" +
                                   columns.getString("TYPE_NAME") + "(" +
                                   columns.getInt("DATA_TYPE") + ")");
               }
               columns.close();
           }
           tables.close();
           stmt.close();
           conn.close();
       }
   }

The following code provides a sample JDBC connection string configuration.

// your_quota_nick_name is the name of the interactive quota group that you want to use.
jdbc:odps:<MaxCompute_endpoint>?
project=<MaxCompute_project_name>&interactiveMode=true&quotaName=your_quota_nick_name&enableLimit=false&enableOdpsLogger=true"

Connect to MaxQA using BI analysis tools

You can connect to MaxQA using Tableau, Quick BI, Guanyuan BI, SQLWorkBench, . The procedures are as follows.

Enable MaxQA using the Java SDK

For more information about the Java SDK, see Java SDK overview.

1. Configure the Pom dependency in Maven. The following is a sample configuration.

   <dependency>
     <groupId>com.aliyun.odps</groupId>
     <artifactId>odps-sdk-core</artifactId>
     <version>${Latest Version}</version>
   </dependency>

   Important

   The Latest Version in the preceding code is a placeholder. You must replace it with a specific SDK version number to compile the project. To view all released versions of odps-sdk-core, visit the Maven Central Repository. We recommend that you use the latest version. The version number format is X.X.X-public.

2. Create a Java program. The following code provides a sample command.

   import com.aliyun.odps.Odps;
   import com.aliyun.odps.OdpsException;
   import com.aliyun.odps.account.Account;
   import com.aliyun.odps.account.AliyunAccount;
   import com.aliyun.odps.sqa.ExecuteMode;
   import com.aliyun.odps.sqa.SQLExecutor;
   import com.aliyun.odps.sqa.SQLExecutorBuilder;
   import java.util.HashMap;
   import java.util.Map;
   
   public class MaxCompute {
   
       public static void main(String args[]) {
           // Set the account and project information.
           // An Alibaba Cloud account AccessKey has full permissions on all APIs and poses a high security risk. We strongly recommend that you create and use a RAM user to make API calls or perform O&M. To create a RAM user, log on to the RAM console.
           // This example shows how to save the AccessKey ID and AccessKey secret to environment variables. You can also save them to a configuration file as needed.
           // We strongly recommend that you do not save the AccessKey ID and AccessKey secret in your code to avoid key leakage.
           Account account = new AliyunAccount(System.getenv("ALIBABA_CLOUD_ACCESS_KEY_ID"),
                           System.getenv("ALIBABA_CLOUD_ACCESS_KEY_SECRET"));
           Odps odps = new Odps(account);
           odps.setDefaultProject("maxqatomax");
           odps.setEndpoint("https://service.cn-hangzhou.maxcompute.aliyun.com/api");
           // Prepare to build the SQLExecutor.
           SQLExecutorBuilder builder = SQLExecutorBuilder.builder();
   
           SQLExecutor sqlExecutor = null;
           try {
               // Create an executor that uses MCQA 2.0 mode by default.
               sqlExecutor = builder.odps(odps)
                       .executeMode(ExecuteMode.INTERACTIVE)
                       .quotaName("maxfor1")
                       .enableMcqaV2(true)
                       .build();
               // If needed, you can pass in special settings for the query.
               Map<String, String> queryHint = new HashMap<>();
               queryHint.put("odps.sql.mapper.split.size", "128");
               // Submit a query job. Hints are supported.
               sqlExecutor.run("select count(1) from test_table;", queryHint);
               // The logview of the current query job.
               System.out.println("Logview:" + sqlExecutor.getLogView());
               // The InstanceId of the current query job.
               System.out.println("InstanceId:" + sqlExecutor.getQueryId());
           } catch (OdpsException e) {
               e.printStackTrace();
           } finally {
               if (sqlExecutor != null) {
                   // Close the executor to release resources.
                   sqlExecutor.close();
               }
           }
       }
   }

Use MaxQA in PyODPS

PyODPS V0.12.1 and later supports MaxQA. The following code provides a sample.

import os
from odps import ODPS

# Set the account and project information.
# Make sure that the ALIBABA_CLOUD_ACCESS_KEY_ID environment variable is set to your AccessKey ID,
# and the ALIBABA_CLOUD_ACCESS_KEY_SECRET environment variable is set to your AccessKey secret.
# We do not recommend that you use the AccessKey ID and AccessKey secret strings directly.
o = ODPS(
    os.getenv('ALIBABA_CLOUD_ACCESS_KEY_ID'),
    os.getenv('ALIBABA_CLOUD_ACCESS_KEY_SECRET'),
    project='maxto****',
    endpoint='https://service.cn-hangzhou.maxcompute.aliyun.com/api',
)

# If needed, you can pass in special settings for the query.
hints = {"odps.sql.mapper.split.size": "128"}

inst = o.execute_sql_interactive(
    'select count(1) from test_table',
    use_mcqa_v2=True,
    quota_name='maxfor1',
    # Set quota_name to the name of the interactive quota group that corresponds to the MaxQA instance.
    hints=hints,
)

# The logview of the current query job.
print(inst.get_logview_address())

# The InstanceId of the current query job.
print(inst.id)

# Read data row by row.
with inst.open_reader() as reader:
    result = [res.values for res in reader]

odps://<access_id>:<ACCESS_KEY>@<project>/?endpoint=<endpoint>&quota_name=<YOUR_INTERACTIVE_QUOTA_NICKNAME>&interactive_mode=v2&reuse_odps=true

Check the Logview

After a job is complete, go to the Logview page. You can check the following three items to confirm that the job used MaxQA mode.

• Check whether the MaxCompute InstanceId ends with _mcqa.

• Check whether the Quota Nickname is the name of the specified interactive quota.

• On the Summary tab, check whether the Job run mode is mcqa 2.0.

Check the observability page in the console

1. Log on to the MaxCompute console and select a region in the top-left corner.

2. In the navigation pane on the left, choose Observation O&M > Jobs.

3. On the Jobs page, click the Regular Job List tab. Set Job Type to MCQA2 to view MaxQA jobs in the specified Time Range.