MaxCompute:MaxCompute Query Acceleration

• Accelerates the execution of small- and medium-sized query jobs and reduces the time that is required to complete such a query job from minutes to seconds. The MCQA feature is compatible with other query features of MaxCompute.
• Supports mainstream business intelligence (BI) tools to perform ad hoc queries or BI analysis.
• Uses an independent resource pool that does not occupy offline computing resources. MCQA can automatically identify query jobs to reduce the load of the job queue and improve user experience.
• Allows you to write the query results of MCQA jobs to a temporary cache. If you run the same query job later, MaxCompute preferentially returns the query results in the cache to accelerate the execution of the job.

• The version of the MaxCompute client must be V0.40.8 or later. You can click odpscmd to download the required version of the MaxCompute client.
• The version of the MaxCompute JDBC driver must be V3.3.0 or later. You can click ODPS-JDBC to download the required version of the JDBC driver.
• The version of MaxCompute SDK must be V0.40.8-public or later. You can click ODPS-SDK to download the required version of MaxCompute SDK.

• The resource usage of a MaxCompute project is high.
• The cached results become invalid due to changes in the tables or views that are referenced by the cached results.
• The cached results expire.

• If you want to query data from the cached results, the MCQA job that you run must be the same as the original MCQA job that generates the cached results and the context of the MCQA job that you run must be the same as that of the original MCQA job. When you rerun an MCQA job, MaxCompute directly reuses the cached results.
• If the tables or views that are referenced by the cached results change, the cached results become invalid. If you rerun the same job, you cannot obtain the cached data.
• The maximum cache size of a temporary dataset is 10 GB.

1. Log on to the MaxCompute console, and select a region in the top navigation bar.
2. In the left-side navigation pane, click Quotas.
3. On the Quotas page, find the level-1 quota that you want to configure and click Quota Configuration in the Actions column.
4. Configure a quota template.
   1. On the Quota Templates tab of the Quota Configuration page, click Add Quota.
   2. In the Add Quota dialog box, click + Add Quota and configure the Name and Type parameters.

      Enter a custom name in the Name field. Then, select Interactive from the Type drop-down list.

   3. Click OK.
5. Configure a quota plan.
   1. On the Quota Plans tab of the Quota Configuration page, click Add Plan to create a quota plan. You can also find a desired plan and click Edit in the Actions column to edit the plan.
   2. In the Create Quota Plan or Edit Quota Plan dialog box, enter quota values in the Reserved CUs [minCU,maxCU] column.
      When you enter quota values in the Reserved CUs [minCU,maxCU] column, take note of the following points:

      • The minimum number of reserved CUs must be equal to the maximum number of reserved CUs.
      • The minimum number of reserved CUs must be greater than or equal to 50. If you do not require interactive resources, enter 0 for the quota of the interactive resource type.
      • Quotas of the interactive resource type do not support elastically reserved CUs.
   3. Click OK.
   4. On the Quota Plans tab, find the quota plan that you created or edited, and click Apply Immediately in the Actions column.
6. Configure a time plan.

   You can configure a time plan to apply different quota plans at different time points every day. This implements the time-specific logic for quota configuration.

• MaxCompute client
• Ad hoc query or data analytics feature of DataWorks
• MaxCompute JDBC driver
• MaxCompute SDK
• MaxCompute Studio
• PyODPS
• SQLAlchemy

1. Download the JDBC JAR file that supports MCQA or download the source code that 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.3.0</version>
     <classifier>jar-with-dependencies</classifier>
   </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:

   String accessId = "your_access_id";
   String accessKey = "your_access_key";
   String conn = "jdbc:odps:http://service.<regionid>.maxcompute.aliyun.com/api?project=<your_project_name>"&accessId&accessKey&charset=UTF-8&interactiveMode=true&alwaysFallback=false&autoSelectLimit=1000000000";
   Statement stmt = conn.createStatement();
   -- Replace your_access_id with the AccessKey ID of your Alibaba Cloud account and your_access_key with the AccessKey secret of your Alibaba Cloud account. Replace your_project_name with the name of the project for which MCQA is enabled. 
   Connection conn = DriverManager.getConnection(conn, accessId, accessKey);
   Statement stmt = conn.createStatement();
   String tableName = "testOdpsDriverTable";
   stmt.execute("drop table if exists " + tableName);
   stmt.execute("create table " + tableName + " (key int, value string)");

   Configure the parameters in String conn to optimize the processing logic. The following table describes the parameters.

   Parameter	Description
   enableOdpsLogger	This parameter is used to display logs. If you do not configure Simple Logging Facade for Java (SLF4J), we recommend that you set this parameter to True.
   fallbackForUnknownError	Default value: False. If this parameter is set to True, the system switches to the offline mode when an unknown error occurs.
   fallbackForResourceNotEnough	Default value: False. If this parameter is set to True, the system switches to the offline mode when resources are insufficient.
   fallbackForUpgrading	Default value: False. If this parameter is set to True, the system switches to the offline mode during an upgrade.
   fallbackForRunningTimeout	Default value: False. If this parameter is set to True, the system switches to the offline mode when a connection times out.
   fallbackForUnsupportedFeature	Default value: False. If this parameter is set to True, the system switches to the offline mode in scenarios in which MCQA is not supported.
   alwaysFallback	Default value: False. If this parameter is set to True, the system switches to the offline mode in one of the preceding scenarios. This parameter is supported only for the MaxCompute JDBC driver of V3.2.3 or later.

• Example 1: Enable MCQA on Tableau.

  Add interactiveMode=true to the URL of Tableau Server. We recommend that you also add enableOdpsLogger=true to display logs. For more information, see Configure MaxCompute JDBC on Tableau.

  Sample URL:

  http://service.cn-beijing.maxcompute.aliyun.com/api?
  project=****_beijing&interactiveMode=true&enableOdpsLogger=true&autoSelectLimit=1000000000"

  To enable MCQA for specific tables in a MaxCompute project, add the table_list=table_name1, table_name2 property to the URL of Tableau Server. Then, use this property to specify the tables for which you want to enable MCQA. Separate table names with commas (,). If you specify an excessive number of tables, access to the URL of Tableau Server becomes time-consuming. We recommend that you specify only the required tables in the URL of Tableau Server. If a table contains a large number of partitions, we recommend that you do not use data from all partitions as the data source. You can specify partitions to obtain required data or run custom SQL queries to obtain the required data. Sample code:

  http://service.cn-beijing.maxcompute.aliyun.com/api?project=****_beijing
  &interactiveMode=true&alwaysFallback=true&enableOdpsLogger=true&autoSelectLimit=1000000000"
  &table_list=orders,customers

• Example 2: Enable MCQA on SQL Workbench/J.

  After you configure the MaxCompute JDBC driver, you can use MCQA on SQL Workbench/J by modifying the JDBC URL that you specified on the profile configuration page. For more information about how to configure the profile, see Configure MaxCompute JDBC on SQL Workbench/J.

  The following example shows the format of the URL that must be configured:

  jdbc:odps:<MaxCompute_endpoint>?
  project=<MaxCompute_project_name>&accessId=<AccessKey ID>&accessKey=<AccessKey Secret>
  &charset=UTF-8&interactiveMode=true&autoSelectLimit=1000000000"

  The following table describes the parameters in the SQL statement.

  Parameter	Description
  MaxCompute_endpoint	The endpoint of the region in which MaxCompute resides. For more information, see Endpoints.
  MaxCompute_project_name	The name of the MaxCompute project.
  AccessKey ID	The AccessKey ID of your Alibaba Cloud account that can access the project. You can obtain the AccessKey ID from the AccessKey Pair page.
  AccessKey Secret	The AccessKey secret that corresponds to the AccessKey ID. You can obtain the AccessKey secret from the AccessKey Pair page.
  charset=UTF-8	The encoding format of the character set.
  interactiveMode	Specifies whether to enable MCQA. If this parameter is set to true, MCQA is enabled.
  autoSelectLimit	This parameter is required when the number of data records exceeds 1 million.

<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() {
        // Specify the Alibaba Cloud account and project information. 
        Account account = new AliyunAccount("<your_access_id>", "<your_access_key>");
        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 SQLExecutor that runs offline SQL queries by default. 
                sqlExecutor = builder.odps(odps).executeMode(ExecuteMode.OFFLINE).build();
            } else {
                // Create an SQLExecutor that runs SQL queries with MCQA enabled by default. Make sure that the system automatically rolls back to the offline query mode if an SQL query with MCQA enabled fails. 
                sqlExecutor = builder.odps(odps).executeMode(ExecuteMode.INTERACTIVE).fallbackPolicy(FallbackPolicy.alwaysFallbackPolicy()).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);

            // List the System.out.println() interfaces that can be used to query common information. 
            // UUID
            System.out.println("ExecutorId:" + sqlExecutor.getId());
            // Query the Logview URL of the current query job. 
            System.out.println("Logview:" + sqlExecutor.getLogView());
            // Query the instance on which the current query job is run. In interactive mode, multiple query jobs may be run on the same instance. 
            System.out.println("InstanceId:" + sqlExecutor.getInstance().getId());
            // Query the progress of the current query job. You can check the progress bar in the console. 
            System.out.println("QueryStageProgress:" + sqlExecutor.getProgress());
            // Query the changelogs about the status of the current query job, such as rollback messages. 
            System.out.println("QueryExecutionLog:" + sqlExecutor.getExecutionLog());

            // Obtain the results of query jobs by calling one of the following API operations: 
            if(false) {
                // Query the results of all query jobs. The API operation that you called is a synchronous operation and may occupy a thread until the query succeeds or fails. 
                // Write the results of all query jobs to the memory at the same time. To prevent memory issues, we recommend that you do not perform this operation if the amount of data is large. 
                List<Record> records = sqlExecutor.getResult();
                printRecords(records);
            } else {
                // Query the ResultSet iterator of the query results. The API operation that you called is a synchronous operation and may occupy a thread until the query succeeds or fails. 
                // Read the results of query jobs in several batches. We recommend that you perform this operation if the amount of data is large. 
                ResultSet resultSet = sqlExecutor.getResultSet();
                while (resultSet.hasNext()) {
                    printRecord(resultSet.next());
                }
            }

            // run another query
            sqlExecutor.run("select * from test_table;", new HashMap<>());
            if(false) {
                // Query the results of all query jobs. The API operation that you called is a synchronous operation and may occupy a thread until the query succeeds or fails. 
                // Write the results of all query jobs to the memory at the same time. To prevent memory issues, we recommend that you do not perform this operation if the amount of data is large. 
                List<Record> records = sqlExecutor.getResult();
                printRecords(records);
            } else {
                // Query the ResultSet iterator of the query results. The API operation that you called is a synchronous operation and may occupy a thread until the query succeeds or fails. 
                // Read the results of query jobs in several batches. We recommend that you perform this operation if the amount of data is large. 
                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 SQLExecutor and release related resources. 
                sqlExecutor.close();
            }
        }
    }

    // SQLExecutor can be reused by pool mode
    public static void ExampleWithPool() {
        // Specify the Alibaba Cloud account and project information. 
        Account account = new AliyunAccount("your_access_id", "your_access_key");
        Odps odps = new Odps(account);
        odps.setDefaultProject("your_project_name");
        odps.setEndpoint("http://service.<regionid>.maxcompute.aliyun.com/api");

        // Run query jobs by using a connection pool. 
        SQLExecutorPool sqlExecutorPool = null;
        SQLExecutor sqlExecutor = null;
        try {
            // Create a connection pool. Specify the connection pool size and the 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();
            // Obtain an SQLExecutor from the connection pool. If no SQLExecutor is obtained from the connection pool and the total number of SQLExecutors does not exceed the upper limit, an SQLExecutor is automatically added. 
            sqlExecutor = sqlExecutorPool.getExecutor();

            // Use the SQLExecutor in the same manner as you use it in the preceding 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();
    }
}

• UseShortQueryAcceleration: Use MCQA to execute query statements.
• FallbackWhenFailed: Use MCQA to roll back the system to the default offline mode and execute SQL query statements again if the SQL query statements fail to be executed.

odps = ODPS(...)
instance = odps.run_sql_interactive('select count(*) from test_table')
# logview
print(instance.get_logview_address())
# Optional. If you use Tunnel to download query results, the MaxCompute client does not need to wait for the task instance to be complete. 
# Call the wait_for_success method to wait for the task instance to be complete. You can also call the wait_for_completion method to perform this operation. The difference between the two methods is that an error is returned if the task instance fails when you call the wait_for_success method. 
instance.wait_for_success()
# Download the results.
# Display alerts returned by PyODPS.
print(instance.get_warnings())
# Display results. An error is returned if the task instance fails.
print(instance.get_printable_result())
# Download results from the frontend.
for each_record in instance.open_reader():
    pass # The data type of each record in MaxCompute.
# Use Tunnel to download the results.
for each_record in instance.open_reader(tunnel=True):
    pass

• service_name: optional. The name of the MCQA session.
• service_startup_timeout: optional. The timeout period for waiting for a session attach.
• force_reattach: optional. run_sql_interactive() automatically reuses sessions. The force_reattach parameter is used to forcibly reattach to a session.

• interactive_mode: required. This parameter specifies whether to enable MCQA. Set this parameter to true.
• reuse_odps: optional. This parameter specifies whether to forcibly reuse connections. Set this parameter to true. This value indicates that connections are forcibly reused. Connection reuse improves the performance of specific third-party tools, such as Apache SuperSet.

• generic: The default value is False. If this parameter is set to True, the system rolls back to the offline mode when an unknown error occurs.
• noresource: The default value is False. If this parameter is set to True, the system rolls back to the offline mode when resources are insufficient.
• upgrading: The default value is False. If this parameter is set to True, the system rolls back to the offline mode during an upgrade.
• timeout: The default value is False. If this parameter is set to True, the system rolls back to the offline mode when a connection times out.
• unsupported: The default value is False. If this parameter is set to True, the system switches to the offline mode in scenarios in which MCQA is not supported.
• default: If you specify this parameter, the system performs a rollback based on the settings of the unsupported, upgrading, noresource, or timeout parameters. If you do not specify fallback_policy in the connection string, the default rollback policy is used.
• all: The default value is False. If this parameter is set to True, the system rolls back to the offline mode in any of the preceding scenarios. This parameter is supported only for MaxCompute JDBC driver V3.2.3 and later.

odps://<access_id>:<access_key>@<project>/?endpoint=<endpoint>&interactive_mode=true&reuse_odps=true&fallback_policy=unsupported,upgrading,noresource