MaxCompute:MaxQA user guide

Start time of public preview in different regions

Usage notes

• After you participate in the public preview, all interactive quotas that you create are in MaxQA mode by default. Existing MCQA quotas are not affected. If you still want to create an MCQA quota after you participate in the public preview of MaxQA, submit a ticket.

• To help you make better use of MaxQA, we provide 100 CUs of computing resources (valued at USD 2,200) for one month to support your testing of this feature, thereby reducing your trial costs.

Technical support

If you participate in the public preview, we recommend that you scan the QR code below to join the official MaxQA support DingTalk group (ID: 87535025714). MaxCompute technical support engineers will help you solve public preview related problems.

Create a MaxQA instance

You can use the MaxQA feature only in a MaxCompute subscription project. Perform the following steps:

1. Log on to the MaxCompute console, and select a region in the top navigation bar.

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

3. On the Quotas page, find the level-1 quota that you want to manage and click Quota Configuration in the Actions column.

4. Complete basic configurations.

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

   2. Click + Add Level-2 Quota, enter a quota name, and select a value for Type.

      Parameter	Description
      Quota Name	Enter a custom name. This name is required when you use MaxQA later. We recommend that you specify a name related to the business attribute. This helps distinguish multiple instances.
      Type	Select Interactive.

5. Click OK to create a MaxQA instance.

After the MaxQA instance is created, the interactive quota is in the Starting state. Wait for 5 to 8 minutes until the status changes to Running.

Delete a MaxQA instance

If you no longer use a MaxQA instance, you can perform the following steps to delete it:

1. On the Quotas page, find the level-1 quota that you want to manage and click Quota Configuration in the Actions column.

2. On the Basic Configurations tab of the Quota Configuration page, find the interactive quota that you want to delete and click Delete in the Actions column.

The deletion will be performed in the background, and the status update and deletion progress will not be displayed on the page.

Scale out or in a MaxQA instance

You can perform the following steps to scale out or scale in an interactive quota based on your business requirements. You can also configure a time-based scaling plan to perform scheduled scaling. For more information, see Configure a time-based scaling plan for MaxQA.

1. On the Quotas page, find the level-1 quota that you want to manage and click Quota Configuration in the Actions column.

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

3. Adjust the values in the Reserved CUs [minCU,maxCU] column for the interactive quota that you want to scale. To scale out the quota, increase the values. To scale in the quota, decrease the values.

   Note

   • The minimum step size of change in the number of CUs is 32.

   • The minimum number of CUs must be greater than or equal to 32. If you do not need interactive resources, set the values to 0 or delete the quota.

   • You cannot configure elastically reserved CUs for interactive quotas.

4. Click OK.

Scaling Out or Scaling In is displayed in the Actions column of the interactive quota. The scale-out operation takes 5 to 8 minutes, and the scale-in operation may take a longer time. The duration depends on the termination of running jobs. In the worst case, you may need to wait for the jobs to time out to ensure that all jobs running on the nodes to be removed are terminated before the scale-in operation begins. The scale-out and scale-in operations do not affect the execution of jobs.

Configure a time-based scaling plan for MaxQA

You can also configure a time-based scaling plan to perform scheduled scaling. Perform the following steps:

1. On the Quotas page, find the level-1 quota that you want to manage and click Quota Configuration in the Actions column.

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

3. Complete time-based scaling configurations.

   1. In the Resource Configuration Plan List section, click Add Configuration Plan.

   2. In the Add Configuration Plan dialog box, adjust values of Reserved CUs [minCU,maxCU] for each interactive quota and click OK.

   3. Optional. Click Edit in the Actions column of an existing configuration plan to make modifications.

4. Configure a time-based scaling plan.

   1. In the Scheduled Scaling Management section, click Edit Time Plan.

   2. Click Add Effective Period and configure the parameters to enable different configuration plans at different time points every day. This way, you can implement time-based management of quota configurations. For more information, see Configure quotas.

Scheduling policy

When you submit a job, you need to explicitly specify the name of an interactive quota on the client or in the JDBC connection string. The job will be scheduled to the corresponding MaxQA instance for execution.

Fallback policy

MaxQA does not support automatic fallback policies. If a MaxQA job fails due to usage limits or other reasons, you must submit the job again or submit it to a batch quota.

You can use one of the following tools or features to enable MaxQA:

• MaxCompute client

• DataWorks Ad Hoc Query or DataWorks Data Studio

• SQL Analysis in the MaxCompute console

• JDBC driver

• BI analysis tools

• Java SDK

• Python SDK

Use the MaxCompute client to enable MaxQA

1. Install and configure the MaxCompute client (odpscmd) of V0.51.0 or later. For more information, see Install and configure the MaxCompute client.

2. Optional. Add the following command to the end of the odps_config.ini file in the conf folder of the client installation directory:

   enable_quota_cache=true --Turn on MaxQA connection information caching.

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

   Important

   • Linux or macOS: Run the odpscmd script file.

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

4. Run the following command before you run a job. All jobs submitted by the client subsequently will run in the MaxQA instance that corresponds to the interactive quota.

   USE QUOTA <Name of the interactive quota>;

   You can also specify the MaxQA instance to which you want to submit the current job at the session level by running the following command:

   SET odps.task.wlm.quota=<Name of the interactive quota>;

Use DataWorks Data Studio to enable MaxQA

1. Go to the Data Studio page in the DataWorks console. In the right-side navigation pane of the configuration tab of your MaxCompute SQL node, click Debugging Configurations.

2. On the Debugging Configurations tab, select an associated MaxCompute project resource from the Computing Resource drop-down list and an interactive quota from the Computing Quota drop-down list.

For more information about the procedure, see MaxCompute SQL node.

After you complete the configurations, jobs run on the MaxCompute SQL node will be submitted to the MaxQA instance that corresponds to the interactive quota.

Use SQL Analysis in the MaxCompute console to enable MaxQA

1. Log on to the MaxCompute console. In the left-side navigation pane, choose Workspace > SQL Analysis.

2. Go to the SQL Query page and create a text file.

3. Click Run Params in the right-side navigation pane. On the tab that appears, select a project from the Project drop-down list and select the computing resources of an interactive quota from the Computing Quota drop-down list.

For more information about the procedure, see SQL Analysis.

After you complete the configurations, jobs run on the SQL Analysis page will be submitted to the MaxQA instance that corresponds to the interactive quota.

Use the MaxCompute JDBC driver to enable MaxQA

If you use the MaxCompute JDBC driver to connect to MaxCompute, perform the following steps to enable MaxQA. For more information about how to use the MaxCompute JDBC driver to connect to MaxCompute, see Usage notes.

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

2. Add the following dependency to the pom.xml file in the Maven repository:

   <dependency>
      <groupId>com.aliyun.odps</groupId>
      <artifactId>odps-jdbc</artifactId>
      <version>3.9.0-rc4</version>
   </dependency>

3. Create a Java program based on the source code to adapt to your business information. For more information, see MaxCompute JDBC. Sample code:

   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";
       // The AccessKey pair of an Alibaba Cloud account has permissions on all API operations. If you use an AccessKey pair to call API operations, a risk may occur. We recommend that you 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 saved in the environment variables. You can also save your AccessKey pair in the configuration file based on your business requirements.
       // To prevent AccessKey pair leaks, we recommend that you do not directly specify the AccessKey ID and AccessKey secret in the code.
       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 sample code shows the configuration of a JDBC connection string.

// Replace your_quota_nick_name with the name of the interactive quota that you want to use.
jdbc:odps:<MaxCompute_endpoint>?
project=<MaxCompute_project_name>&charset=UTF-8&interactiveMode=true&quotaName=your_quota_nick_name&autoSelectLimit=1000000000&enableOdpsLogger=true"

Use BI analysis tools to connect to MaxQA

You can use Tableau or SQL Workbench/J to connect to MaxQA.

Use MaxCompute SDK for Java to enable MaxQA

For more information about MaxCompute SDK for Java, see SDK for Java.

1. Add the required dependency to the pom.xml file in the Maven repository. Sample configuration:

   <dependency>
     <groupId>com.aliyun.odps</groupId>
     <artifactId>odps-sdk-core</artifactId>
     <version>0.51.5-public</version>
   </dependency>

2. Create a Java program. Sample code:

   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[]) {
           // Specify the Alibaba Cloud account and project information. 
           // The AccessKey pair of an Alibaba Cloud account has permissions on all API operations. If you use an AccessKey pair to call API operations, a risk may occur. We recommend that you 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 configured as environment variables. You can also save your AccessKey pair in the configuration file based on your business requirements.
           // We recommend that you do not directly specify the AccessKey ID and AccessKey secret in code to prevent AccessKey pair leaks.
           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 an SQLExecutor. 
           SQLExecutorBuilder builder = SQLExecutorBuilder.builder();
   
           SQLExecutor sqlExecutor = null;
           try {
               // Create an executor that uses the MCQA 2.0 mode by default. 
               sqlExecutor = builder.odps(odps)
                       .executeMode(ExecuteMode.INTERACTIVE)
                       .quotaName("maxfor1")
                       .enableMcqaV2(true)
                       .build();
               // Pass special query settings if required. 
               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);
               // Query the LogView URL of the current query job. 
               System.out.println("Logview:" + sqlExecutor.getLogView());
               // Query 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 SQLExecutor and release related resources. 
                   sqlExecutor.close();
               }
           }
       }
   }

Use PyODPS to enable MaxQA

PyODPS V0.12.1 and later support MaxQA. The following code provides an example:

import os
from odps import ODPS

# Specify the Alibaba Cloud account and project information.
# Set the environment variable ALIBABA_CLOUD_ACCESS_KEY_ID to the AccessKey ID of your Alibaba Cloud account. 
# Set the environment variable ALIBABA_CLOUD_ACCESS_KEY_SECRET to the AccessKey secret of your Alibaba Cloud account. 
# We recommend that you do not directly use your AccessKey ID or AccessKey secret.
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',
)

# Pass special query settings if required.
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 corresponding to the MaxQA instance.
    hints=hints,
)

# Query the LogView URL of the current query job.
print(inst.get_logview_address())

# Query the instance ID of the current query job.
print(inst.id)

# Read data 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

View information on the LogView page

After a job is executed, go to the LogView page and perform the following checks to determine whether MaxQA is enabled for the job:

• Check whether the MaxCompute Instance ID ends with _mcqa.

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

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

View information on the Jobs page in the MaxCompute console

1. Log on to the MaxCompute console. In the left-side navigation pane, choose Workspace > Jobs.

2. On the Jobs page, specify a time range and set Job Type to MCQA2 to filter MaxQA jobs within the specified time range.