Query Acceleration MaxQA

Availability timeline by region

Notes

New interactive quota groups default to MaxQA mode. Existing MaxQA are not affected.

Create a MaxQA instance

MaxQA is available only for MaxCompute projects that use the subscription billing method. To create a MaxQA instance, follow these steps:

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

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

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

4. Basic configuration

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

   2. Click Add Level-2 Quota, enter a Quota Name, and select Interactive for the Type.

      • When you use MaxQA, you need to specify a Quota Name. We recommend that you use a name related to your business attributes to make it easier to distinguish between multiple instances.

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

5. Click OK.

After creating the instance, the status of the corresponding interactive quota group changes to Starting. Wait for 5 to 8 minutes until the status changes to Running. You can then use the instance.

Delete a MaxQA instance

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

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

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

Scale a MaxQA instance

You can scale out or scale in an interactive quota group based on your business needs. To do so, follow these steps:

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

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

3. Click Add Level-2 Quota and adjust the Reserved CUs [minCU,maxCU] of the interactive quota group that you want to scale up or down. A larger value indicates a scale-up, and a smaller value indicates a scale-down.

   Note

   • The minimum increment for CUs is 25.

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

   • You cannot set Elastically Reserved CUs for interactive quotas.

4. Click OK.

   The adjustment of reserved CUs for the interactive quota group begins.

Scaling Out or Scaling In appears in the Actions column for the interactive quota group. Scaling out takes about 5 to 8 minutes. Scaling in may take longer, depending on when the currently running jobs finish. In a worst-case scenario, the system may need to wait for jobs on the machines being removed to time out. This ensures all jobs are complete before the scale-in operation begins. Your jobs are not affected during scaling operations.

Configure time-based scaling for MaxQA

You can configure a time-based scaling plan for scheduled scaling. Follow these steps:

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

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

3. Configure a time-based scaling plan

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

   2. To modify an existing configuration plan:

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

4. Configure a time-based schedule

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

   2. Click Add Effective Period, and then select an Effective start time and a Configuration Plan.

      This allows you to activate different quota configuration plans at different times of the day. For more information, see Manage compute resources and quotas.

Scheduling policy

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

Fallback policy

MaxQA does not support an automatic fallback policy. If a MaxQA job fails due to usage limits or other reasons, you must resubmit it manually or submit it to a batch quota group.

You can access MaxQA using the following methods:

• MaxCompute client

• DataWorks: ad-hoc queries or DataStudio

• MaxCompute console: SQL Analysis

• MaxCompute Studio

• JDBC

• BI tools

• Java SDK

• Python SDK

Using the MaxCompute client

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

2. (Optional) In the conf directory of the client installation, modify the odps_config.ini file by adding the following command to the last line.

   -- Enable MaxQA connection information caching.
   enable_quota_cache=true

3. Run the script file in the bin directory to start the MaxCompute client.

   Important

   • For Linux/macOS systems: Run the odpscmd script file.

   • Windows: Double-click the odpscmd.bat file.

4. Before you run a job, execute the following command. Subsequent jobs submitted from the client will run in MaxQA mode on the MaxQA instance that corresponds to the interactive quota group.

   USE QUOTA <name_of_the_interactive_quota_group>;

   Alternatively, you can specify the MaxQA instance for the current job at the session level.

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

Using DataWorks DataStudio

1. Log in 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 Run Configuration on the right.

5. On the Debugging Configurations page, select the MaxCompute project resource bound to this workspace for Computing Resource. The following table describes how to select the computing quota for different scenarios:

   Note

   • Run environment: Jobs run in the data analysis or data development module. Interactive-to-offline fallback does not apply.

   • Fallback run environment: Jobs run in the scheduling module. If interactive mode is specified, you can also configure fallback to offline mode when the interactive mode fails.

   Computing Quota Selection	Specified Quota / Routing Rule	Expected Run Mode	Scheduling Configuration and Expected Fallback
   Project default quota	If an offline routing rule exists, the routing rule takes effect. Interactive routing rules do not apply.	The project default quota is an offline quota. Jobs run on the offline quota.	Jobs run on the offline quota. Fallback does not apply.
   Specified quota	Select an offline quota.	Jobs run on the offline quota.	Jobs run on the offline quota. Fallback does not apply.
   Specified quota	Select an interactive quota.	Jobs run on the MaxQA instance that corresponds to the interactive quota group.	In Scheduling Configuration: if fallback is disabled, no fallback is attempted; if project default quota is selected, the project default quota is used for fallback (offline routing rules do not apply in fallback); if a specific quota is selected, the specified offline quota is used for fallback.
   Interactive mode routing rule	Only offline routing rules exist. No matching interactive routing rule is found.	An error is reported: Cannot find MCQA quota to start connection on project *****. To create one, see Using quotas for computing resources.	If fallback is disabled, no fallback is attempted; if project default quota is selected, the project default quota is used for fallback; if a specific quota is selected, the specified offline quota is used for fallback.
   Interactive mode routing rule	A matching interactive routing rule exists.	Jobs run on the MaxQA instance that corresponds to the interactive quota group.

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

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

Using SQL Analysis in the console

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

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

3. After entering your 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 correspond to the interactive quota group.

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

For more information, see SQL Analysis.

Using MaxCompute Studio

Ensure you have installed MaxCompute Studio and connected it to your MaxCompute project. To use MaxQA, run a script by following these steps:

1. Install and configure MaxCompute Studio v4.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 an SQL script.

4. In the MaxCompute SQL script, enter SQL statements. From the Execution Mode drop-down list, select MaxQA.

5. Click the Specify additional parameters button, enter the interactive quota group name, and then click OK.

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

Using JDBC

To enable MaxQA when connecting to MaxCompute using JDBC, follow these steps. For more information about JDBC connections, see User guide.

1. Download the source code with MaxQA support.

2. Configure the POM dependency in Maven.

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

   Important

   In the preceding code, Latest Version is a placeholder. Replace it with a specific SDK version number to successfully compile the project. Visit the Maven Central Repository to view all released odps-jdbc versions. We recommend that you use the latest version. The version number format is X.X.X.

3. Create a Java program from the source code, adapting it for your environment. For more information, see MaxCompute JDBC. The following code provides 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 AccessKey ID of an Alibaba Cloud account has permissions to call all API operations. This is a high-risk operation. We recommend that you create and use a RAM user to call API operations or perform routine O&M. To create a RAM user, log on to the RAM console.
       // In this example, the AccessKey ID and AccessKey Secret are stored in environment variables. You can also store them in a configuration file based on your business needs.
       // We strongly recommend that you do not hard-code the AccessKey ID and AccessKey Secret in your code. This poses a security risk.
       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 meta data
           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 shows an example JDBC connection string.

// your_quota_group_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_group_name&enableLimit=false&enableOdpsLogger=true"

Using BI tools

You can connect to MaxQA using BI tools such as Tableau, Quick BI, Guandata BI, and SQL Workbench/J, or FineBI. The following sections describe the procedures.

jdbc:odps:<MaxCompute_endpoint>?
project=<MaxCompute_project_name>&charset=UTF-8&interactiveMode=true&quotaName=<your_quota_group_name>&autoSelectLimit=1000000000"

Using the Java SDK

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

1. Configure the POM dependency in Maven. The following code shows an example.

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

   Important

   In the preceding code, Latest Version is a placeholder. You must replace it with a specific SDK version number to successfully compile the project. Visit the Maven Central Repository to view all released versions of odps-sdk-core. 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 an example.

   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 AccessKey ID of an Alibaba Cloud account has permissions to call all API operations. This is a high-risk operation. We recommend that you create and use a RAM user to call API operations or perform routine O&M. To create a RAM user, log on to the RAM console.
           // In this example, the AccessKey ID and AccessKey Secret are stored in environment variables. You can also store them in a configuration file based on your business needs.
           // We strongly recommend that you do not hard-code the AccessKey ID and AccessKey Secret in your code. This poses a security risk.
           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 MaxQA mode by default.
               // enableMcqaV2(true) enables MaxQA mode
               sqlExecutor = builder.odps(odps)
                       .executeMode(ExecuteMode.INTERACTIVE)
                       .quotaName("maxfor1")
                       .enableMcqaV2(true)
                       .build();
               // If needed, you can pass custom settings for the query.
               Map<String, String> queryHint = new HashMap<>();
               queryHint.put("odps.sql.mapper.split.size", "128");
               // Submit a query job. You can pass hints.
               sqlExecutor.run("select count(1) from test_table;", queryHint);
               // The Logview of the current query job.
               System.out.println("Logview:" + sqlExecutor.getLogView());
               // The instance ID 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();
               }
           }
       }
   }

Using PyODPS

PyODPS v0.12.1 and later supports MaxQA. The following code shows an example.

import os
from odps import ODPS
# Set the account and project information.
# Make sure that the ALIBABA_CLOUD_ACCESS_KEY_ID and ALIBABA_CLOUD_ACCESS_KEY_SECRET 
# environment variables are set to your AccessKey ID and 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 custom 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 for the MaxQA instance.
    hints=hints,
)
# The Logview of the current query job.
print(inst.get_logview_address())
# The instance ID 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_GROUP_NAME>&interactive_mode=v2&reuse_odps=true

From the Logview page

After a job completes, go to its Logview page to verify that it used MaxQA mode. Check for the following:

• The MaxCompute InstanceId ends with _mcqa.

• The quota nickname is the name of the specified interactive quota.

• On the Summary tab, the job run mode is mcqa 2.0.

From the Job O&M page

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

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

3. On the Jobs page, click the Regular Job List tab and set Job Type to MCQA2 to view MaxQA jobs within a specified Time Range.