Usage notes

Run a query job on the Ad-Hoc Query page. If the returned results contain the following information, the MCQA feature is enabled. For more information about ad hoc queries, see Create an ad hoc query.

Run a query job on the Manually Triggered Workflow page. If the returned results contain the following information, the MCQA feature is enabled. For more information about the manually triggered workflow, see Manage manually triggered workflows.

You can use the MaxCompute JDBC driver in the following situations:

• Use the MaxCompute JDBC driver to connect to MaxCompute. For more information, see Usage notes of JDBC. To enable the MCQA feature, you must modify the relevant configurations. For more information, see Enable MCQA by using the MaxCompute JDBC driver.
• Use the MaxCompute JDBC driver to connect to Tableau. Then, you can use Tableau to analyze MaxCompute data in a visualized manner. For more information, see Configure MaxCompute JDBC on Tableau. To enable the MCQA feature in this scenario, you must modify the relevant configurations. For more information, see Enable MCQA on Tableau Server by using the MaxCompute JDBC driver.
• Use the MaxCompute JDBC driver to connect to SQL Workbench/J. Then, you can use SQL Workbench/J to execute SQL statements on the data in MaxCompute. For more information, see Configure MaxCompute JDBC on SQL Workbench/J. To enable the MCQA feature in this scenario, you must modify the relevant configurations. For more information, see Enable MCQA on SQL Workbench/J by using the MaxCompute JDBC driver.

The following example shows how to configure a server:

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

To enable MCQA for some 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. Sample URL:

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

If a table contains a large number of partitions, we recommend that you do not use data from all the partitions as the data source. You can filter the required partitions or run custom SQL queries to obtain the required data.

Specify the JDBC URL in the following format: jdbc:odps:<MaxCompute_endpoint>?project=<MaxCompute_project_name>&accessId=<AccessKey ID>&accessKey=<AccessKey Secret>&charset=UTF-8&interactiveMode=true. Parameter description:

• maxcompute_endpoint: the endpoint of the region where your MaxCompute service is deployed. For more information, see Configure endpoints.
• maxcompute_project_name: the name of your MaxCompute project.
• AccessKey ID: the AccessKey ID that is used to access the specified project.
• AccessKey Secret: the AccessKey secret that corresponds to the AccessKey ID.
• charset=UTF-8: the character set encoding format.
• interactiveMode: specifies whether to enable the MCQA feature. To enable the MCQA feature, set this parameter to true.

For more information about Alibaba Cloud MaxCompute SDK for Java, see Overview. You must add a specified dependency to the pom.xml file in the Maven repository. Sample dependency:

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

</dependency>

The following sample code shows how to create a Java program:

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 execution 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 avoid 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 avoid 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 can be obtained from the connection pool, you can add an SQLExecutor. Make sure that the total number of SQLExecutors does not exceed the upper limit.
            sqlExecutor = sqlExecutorPool.getExecutor();

            // Use the SQLExecutor the same way as it is used 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();
    }
}