MaxCompute:MCQA(Query Acceleration 1.0) -deprecated

Follow these steps to enable MCQA for a subscription MaxCompute instance:

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. Quota basic configuration

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

   2. Click Add Level-2 Quota, or configure basic parameters for an existing level-2 quota.

      Set a custom Quota Name and select Interactive for Type. Configure quotas.

5. Quota scaling configuration

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

   2. On the Scaling Configuration tab, click Add Configuration Plan, or click Edit in the Actions column of an existing plan to update it.

   3. In the Add Configuration Plan or Edit Configuration Plan dialog box, configure Reserved CUs [minCU,maxCU] for the Interactive (MaxQA/MCQA) resource group.

      • minCU must equal maxCU.

      • minCU must be ≥ 50CU. Set to 0 if interactive resources are not needed.

      • Interactive quota groups do not support elastic reserved CU.

   4. Click OK to save the quota configuration.

   5. On the Scaling Configuration tab, click Apply Immediately in the Actions column of the target plan to apply it immediately. You can also use existing plans when configuring Scheduled Scaling Management.

6. Configure time plans.

   Time plans let you apply different quota configurations at different times of day.

MCQA supports the following access methods:

• Method 1: Enable MCQA via the MaxCompute client

• Method 2: Enable MCQA via DataWorks ad hoc queries or data development

• Method 3: Enable MCQA via JDBC

• Method 4: Enable MCQA via Java SDK

• Method 5: Enable MCQA via PyODPS with SQLAlchemy or other SQLAlchemy-compatible tools

Method 3: Enable MCQA via JDBC

Method 4: Enable MCQA via Java SDK

Java SDK overview. Configure POM dependency via Maven:

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

import com.aliyun.odps.Odps;
import com.aliyun.odps.OdpsException;
import com.aliyun.odps.OdpsType;
import com.aliyun.odps.account.Account;
import com.aliyun.odps.account.AliyunAccount;
import com.aliyun.odps.data.Record;
import com.aliyun.odps.data.ResultSet;
import com.aliyun.odps.sqa.*;

import java.io.IOException;
import java.util.*;

public class SQLExecutorExample {

  public static void SimpleExample() {
    // Set account and project information.
    // An Alibaba Cloud account AccessKey has access to all API operations, which poses a high security risk. 
    // We strongly recommend that you create and use a RAM user for API access or routine O&M. Log on to the RAM console to create a RAM user.
    // This example uses AccessKey and AccessKeySecret stored in environment variables. You can also store them in a configuration file based on your business requirements.
    // We strongly recommend that you do not save the AccessKey and AccessKeySecret in your code. This may cause the AccessKey pair to be leaked.
    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("<YOUR_PROJECT_NAME>");
    odps.setEndpoint("http://service.<regionid>.maxcompute.aliyun.com/api");

    // Prepare to build an SQLExecutor.
    SQLExecutorBuilder builder = SQLExecutorBuilder.builder();

    SQLExecutor sqlExecutor = null;
    try {
      // run in offline mode or run in interactive mode
      if (false) {
        // Create an Executor that runs offline SQL by default.
        sqlExecutor = builder.odps(odps).executeMode(ExecuteMode.OFFLINE).build();
      } else {
        // Create an Executor that runs SQL in query acceleration mode by default and automatically falls back to offline mode on failure.
        sqlExecutor = builder.odps(odps).executeMode(ExecuteMode.INTERACTIVE).fallbackPolicy(FallbackPolicy.alwaysFallbackPolicy()).build();
      }
      // Pass custom query settings if needed.
      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);

      // List commonly used methods for retrieving information.
      // UUID
      System.out.println("ExecutorId:" + sqlExecutor.getId());
      // Logview of the current query job.
      System.out.println("Logview:" + sqlExecutor.getLogView());
      // Instance object of the current query job (in Interactive mode, multiple query jobs may share the same Instance).
      System.out.println("InstanceId:" + sqlExecutor.getInstance().getId());
      // Stage progress of the current query job (console progress bar).
      System.out.println("QueryStageProgress:" + sqlExecutor.getProgress());
      // Execution status change log of the current query job, such as fallback information.
      System.out.println("QueryExecutionLog:" + sqlExecutor.getExecutionLog());

      // Two methods are provided for retrieving results.
      if(false) {
        // Retrieve all query results at once. This synchronous method may block the current thread until the query succeeds or fails.
        // Reads all result data into memory at once. Not recommended for large datasets due to potential memory issues.
        List<Record> records = sqlExecutor.getResult();
        printRecords(records);
      } else {
        // Retrieve a ResultSet iterator for query results. This synchronous method may block the current thread until the query succeeds or fails.
        // Recommended for large result sets. Reads query results in batches.
        ResultSet resultSet = sqlExecutor.getResultSet();
        while (resultSet.hasNext()) {
          printRecord(resultSet.next());
        }
      }

      // run another query
      sqlExecutor.run("select * from test_table;", new HashMap<>());
      if(false) {
        // Retrieve all query results at once. This synchronous method may block the current thread until the query succeeds or fails.
        // Reads all result data into memory at once. Not recommended for large datasets due to potential memory issues.
        List<Record> records = sqlExecutor.getResult();
                printRecords(records);
            } else {
        // Retrieve a ResultSet iterator for query results. This synchronous method may block the current thread until the query succeeds or fails.
        // Recommended for large result sets. Reads query results in batches.
                ResultSet resultSet = sqlExecutor.getResultSet();
                while (resultSet.hasNext()) {
                    printRecord(resultSet.next());
                }
            }
        } catch (OdpsException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (sqlExecutor != null) {
                // Close the Executor to release resources.
                sqlExecutor.close();
            }
        }
    }

    // SQLExecutor can be reused by pool mode
    public static void ExampleWithPool() {
        // Execute queries using a connection pool.
        // An Alibaba Cloud account AccessKey has access to all API operations, which poses a high security risk. 
        // We strongly recommend that you create and use a RAM user for API access or routine O&M. Log on to the RAM console to create a RAM user.
        // This example uses AccessKey and AccessKeySecret stored in environment variables. You can also store them in a configuration file based on your business requirements.
        // We strongly recommend that you do not save the AccessKey and AccessKeySecret in your code. This may cause the AccessKey pair to be leaked.
        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("your_project_name");
        odps.setEndpoint("http://service.<regionid>.maxcompute.aliyun.com/api");

        // Prepare a connection pool and set the pool size and default execution mode.
        SQLExecutorPool sqlExecutorPool = null;
        SQLExecutor sqlExecutor = null;
        try {
            // Prepare a connection pool and set the pool size and default execution mode.
            SQLExecutorPoolBuilder builder = SQLExecutorPoolBuilder.builder();
            builder.odps(odps)
                    .initPoolSize(1) // init pool executor number
                    .maxPoolSize(5)  // max executors in pool
                    .executeMode(ExecuteMode.INTERACTIVE); // run in interactive mode

            sqlExecutorPool = builder.build();
            // Get an Executor from the connection pool. If none is available, a new one is created within the maximum limit.
            sqlExecutor = sqlExecutorPool.getExecutor();

            // The Executor usage is the same as in the previous example.
            sqlExecutor.run("select count(1) from test_table;", new HashMap<>());
            System.out.println("InstanceId:" + sqlExecutor.getId());
            System.out.println("Logview:" + sqlExecutor.getLogView());

            List<Record> records = sqlExecutor.getResult();
            printRecords(records);
        } catch (OdpsException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            sqlExecutor.close();
        }
        sqlExecutorPool.close();
    }

    private static void printRecord(Record record) {
        for (int k = 0; k < record.getColumnCount(); k++) {

            if (k != 0) {
                System.out.print("\t");
            }

            if (record.getColumns()[k].getType().equals(OdpsType.STRING)) {
                System.out.print(record.getString(k));
            } else if (record.getColumns()[k].getType().equals(OdpsType.BIGINT)) {
                System.out.print(record.getBigint(k));
            } else {
                System.out.print(record.get(k));
            }
        }
    }

    private static void printRecords(List<Record> records) {
        for (Record record : records) {
            printRecord(record);
            System.out.println();
        }
    }

    public static void main(String args[]) {
        SimpleExample();
        ExampleWithPool();
    }
}