MaxCompute:User guide for MaxCompute Query Accelerator (MaxQA)

Create a MaxQA instance

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

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

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

4. Click the Basic Configurations tab, then click Edit Basic Configurations.

5. Click Add Level-2 Quota, enter a Quota Name, and set Type to Interactive. Use a business-related name to distinguish between multiple instances. The quota name is required when submitting jobs to MaxQA.

6. Click OK.

After creation, the interactive quota group status changes to Starting. The instance is available when the status changes to Running, which takes 5 to 8 minutes.

Delete a MaxQA instance

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

2. Click the Basic Configurations tab, 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.

Scale a MaxQA instance

Scale out or scale in an interactive quota group by adjusting its reserved CU count.

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

2. Click the Basic Configurations tab, then click Edit Basic Configurations.

3. Click Add Level-2 Quota and adjust the Reserved CUs [minCU, maxCU] for the interactive quota group.

   • Increasing the value scales out; decreasing it scales in.

   • The minimum step size is 25. The minimum value must be 25 or greater.

   • Set to 0 or delete the quota group if interactive resources are no longer needed.

   • Elastically Reserved CUs are not supported for interactive quotas.

4. Click OK.

The Actions column shows Scaling Out or Scaling In while the operation is in progress. Scaling out completes in 5 to 8 minutes. Scaling in may take longer because the system waits for running jobs to finish before reclaiming resources. Scaling operations do not affect running jobs.

Configure time-based scaling

Set up a scheduled scaling plan to automatically adjust CU capacity at specified times of day.

Step 1: Create a configuration plan

1. On the Quotas page, click Quota Configuration for the target quota.

2. Click the Scaling Configuration tab, then click Add Configuration Plan.

3. In the dialog box, enter a Configuration Plan Name and configure Reserved CUs [minCU, maxCU] for the level-2 quota.

To update an existing plan, find it on the Scaling Configuration tab and click Edit in the Actions column.

Step 2: Set 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, select an Effective Start Time, and assign a Configuration Plan.

Assign different configuration plans to different time slots to match expected load patterns. For more information, see Quota management for compute resources.

MaxCompute client (odpscmd)

1. Install MaxCompute client (odpscmd) V0.51.1 or later. For setup instructions, see Connect using the local client (odpscmd).

2. (Optional) Open odps_config.ini in the conf directory and add the following line to enable the connection information cache:

   enable_quota_cache=true

3. Start the client:

   • Linux or macOS: run the odpscmd script.

   • Windows: double-click odpscmd.bat.

4. Run the following command to route all subsequent jobs to your MaxQA instance:

   USE QUOTA <name_of_the_interactive_quota_group>;

   To override the quota for the current session only:

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

DataWorks data development

1. Log on to the DataWorks console and select a region.

2. In the left navigation pane, choose Data Development and O&M (Operations and Maintenance) > Data Development.

3. Select your workspace and click Go To Data Studio.

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

5. Set Computing Resource to your MaxCompute project and set Computing Quota to the interactive quota group.

Jobs submitted from SQL nodes are then routed to the MaxQA instance.

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

SQL Analysis in the MaxCompute console

1. Log on to the MaxCompute console and select a region.MaxCompute console

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

3. Enter SQL statements in the editor, then click Run Configurations on the right.

4. Set Project and Computing Quota to the interactive quota group.

Jobs run from SQL Analysis are routed to the MaxQA instance.

For more information, see SQL Analysis.

MaxCompute Studio

1. Install MaxCompute Studio 4.3.2 or later. See Install MaxCompute Studio.

2. Connect MaxCompute Studio to your MaxCompute project. See Manage project connections.

3. Create a MaxCompute SQL script. See Develop and submit SQL scripts.

4. In the SQL script editor, select MaxQA from the execution mode drop-down list.

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

6. Run the script.

JDBC

To enable MaxQA over JDBC, add interactiveMode=true and quotaName=<your_quota_nick_name> to the connection string.

For general JDBC setup, see MaxCompute JDBC.

Step 1: Add the Maven dependency

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

Replace ${Latest Version} with a specific version number. View all releases at the Maven Central Repository. The version number format is X.X.X.

Step 2: Configure the connection string

jdbc:odps:<MaxCompute_endpoint>?project=<MaxCompute_project_name>&interactiveMode=true&quotaName=<your_quota_nick_name>&enableLimit=false&enableOdpsLogger=true

Step 3: Create the Java program

The following example connects to MaxCompute and runs a query using MaxQA. Replace the sample project name, endpoint, and quota name with your own values.

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";
    // Store credentials in environment variables to avoid hardcoding secrets.
    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();
    }
}

For the full API reference, see MaxCompute JDBC.

BI analysis tools

MaxQA is compatible with Tableau, Quick BI, Guanyuan BI, and SQL Workbench/J via JDBC. All tools require the same core parameters: interactiveMode=true and quotaName=<your_quota_nick_name>.

Tableau

Add the MaxQA parameters to the server URL. For the full setup procedure, see Configure JDBC to use Tableau.

URL format:

<MaxCompute_endpoint>?project=<MaxCompute_project_name>&charset=UTF-8&interactiveMode=true&quotaName=<your_quota_nick_name>&autoSelectLimit=1000000000

Parameter	Required	Description
MaxCompute_endpoint	Yes	The endpoint for your region. See Endpoint. Example: https://service.cn-hangzhou.maxcompute.aliyun.com/api
MaxCompute_project_name	Yes	Your MaxCompute project name, for example, maxqatomax.
your_quota_nick_name	Yes	The interactive quota group name, for example, maxfor1.

URL example:

https://service.cn-hangzhou.maxcompute.aliyun.com/api?project=maxqatomax&charset=UTF-8&interactiveMode=true&quotaName=maxfor1&autoSelectLimit=1000000000

Quick BI

1. Log on to the Quick BI console.

2. In the left navigation pane, click Data Sources, then click Create data source.

3. Select MaxCompute.

4. On the Configure connection page, enter your interactive quota group name in the Quota calculation resource group field.

For more details, see Connect Quick BI to MaxCompute.

Guanyuan BI

Step 1: Add the MaxCompute JDBC driver

Upload MaxCompute JDBC Driver V3.8.5 or later to Guanyuan BI. Download from MaxCompute JDBC releases.

1. Click the icon in the upper-right corner to open Management Center.

2. Go to System Settings > General Settings > Drivers and Connectors.

3. On the Driver Management tab, click Add Driver, enter a Driver Name, and save.

4. Click the driver name, then on the Driver List tab, click Upload File to upload the JDBC driver JAR. Select com.aliyun.odps.jdbc.OdpsDriver from the Driver drop-down list and click Apply.

Step 2: Create a data account

1. In the top menu, click Data Preparation.

2. Click the Data Account tab, then click New Data Account.

3. Configure the account with the following settings: JDBC URL parameters: URL example:

   • maxcompute_endpoint: The endpoint for your region. Example: https://service.cn-hangzhou.maxcompute.aliyun.com/api.

   • maxcompute_projectName: Your MaxCompute project name, for example, maxqatomax.

   • your_quota_nickName: The interactive quota group name, for example, maxfor1.

   Parameter	Value
   Select connector	MaxCompute
   Connection method	JDBC URL
   Logon ID	Your Alibaba Cloud AccessKey ID
   Logon Key	Your Alibaba Cloud AccessKey secret
   JDBC URL	jdbc:odps:<maxcompute_endpoint>?project=<maxcompute_projectName>&charset=UTF-8&interactiveMode=true&quotaName=<your_quota_nickName>&autoSelectLimit=1000000000
   SQL version	2.0/hive compatible mode
   Maximum connections	Default: 16
   Driver	Custom — select the driver you created

   jdbc:odps:https://service.cn-hangzhou.maxcompute.aliyun.com/api?project=maxqatomax&charset=UTF-8&interactiveMode=true&quotaName=maxfor1&autoSelectLimit=1000000000

4. Click Test Connection. If successful, click Next.

5. Enter a Display Name and click Confirm and Create.

SQL Workbench/J

Modify the JDBC URL in the profile configuration. For the full setup, see Configure JDBC to use SQL Workbench/J.

URL format:

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

Parameter	Required	Description
MaxCompute_endpoint	Yes	The endpoint for your region. Example: https://service.cn-hangzhou.maxcompute.aliyun.com/api
MaxCompute_project_name	Yes	Your MaxCompute project name, for example, maxqatomax.
your_quota_nick_name	Yes	The interactive quota group name, for example, maxfor1.

URL example:

jdbc:odps:https://service.cn-hangzhou.maxcompute.aliyun.com/api?project=maxqatomax&charset=UTF-8&interactiveMode=true&quotaName=maxfor1&autoSelectLimit=1000000000

Java SDK

For the Java SDK overview, see Java SDK overview.

Step 1: Add the Maven dependency

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

Replace ${Latest Version} with a specific version number. View all releases at the Maven Central Repository. The version number format is X.X.X-public.

Step 2: Submit a query

The following example creates an SQL executor in MaxQA mode and runs a query. Replace the sample project name, endpoint, and quota name with your own values.

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[]) {
        // Store credentials in environment variables to avoid hardcoding secrets.
        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");

        SQLExecutorBuilder builder = SQLExecutorBuilder.builder();
        SQLExecutor sqlExecutor = null;
        try {
            // Create an executor in MaxQA mode (MCQA 2.0).
            sqlExecutor = builder.odps(odps)
                    .executeMode(ExecuteMode.INTERACTIVE)
                    .quotaName("maxfor1")
                    .enableMcqaV2(true)
                    .build();

            // Pass optional query hints.
            Map<String, String> queryHint = new HashMap<>();
            queryHint.put("odps.sql.mapper.split.size", "128");

            // Submit the query.
            sqlExecutor.run("select count(1) from test_table;", queryHint);

            System.out.println("Logview: " + sqlExecutor.getLogView());
            System.out.println("InstanceId: " + sqlExecutor.getQueryId());
        } catch (OdpsException e) {
            e.printStackTrace();
        } finally {
            if (sqlExecutor != null) {
                // Release resources.
                sqlExecutor.close();
            }
        }
    }
}

PyODPS

PyODPS V0.12.1 and later supports MaxQA.

import os
from odps import ODPS

# Store credentials in environment variables to avoid hardcoding secrets.
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 optional query hints.
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',  # Name of the interactive quota group.
    hints=hints,
)

# Retrieve the Logview URL.
print(inst.get_logview_address())

# Retrieve the InstanceId.
print(inst.id)

# Read results 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 Logview

Open the Logview page for the job and verify all three of the following:

• The MaxCompute InstanceId ends with _mcqa.

• The Quota Nickname matches the name of your interactive quota group.

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

Check the Jobs page in the console

1. Log on to the MaxCompute console and select a region.

2. In the left navigation pane, choose Observation O&M (Operations and Maintenance) > Jobs.

3. Click the Regular Job List tab. Set Job Type to MCQA2 and specify a Time Range to filter MaxQA jobs.