MaxCompute:Usage notes

Basic parameters

Log configuration parameters

Connection and timeout parameters

Other parameters

Offline mode parameters

MaxCompute Query Acceleration (MaxQA/MCQA 1.0) parameters

Enable MaxQA/MCQA 1.0 mode by setting interactiveMode=True. The parameters below apply only in this mode.

Basic parameters:

Result limit parameters:

Fallback parameters:

These parameters control when a MaxQA/MCQA 1.0 job falls back to offline mode.

Create a table and query metadata

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 Main {

    private static final String DRIVER_NAME = "com.aliyun.odps.jdbc.OdpsDriver";
    // Read credentials from environment variables to avoid exposing them in 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) {
        try {
            Class.forName(DRIVER_NAME);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.exit(1);
        }

        try (
            Connection conn = DriverManager.getConnection(
                "jdbc:odps:<maxcompute_endpoint>?project=<maxcompute_project>",
                Main.accessId, Main.accessKey);
            Statement stmt = conn.createStatement()
        ) {
            // Create a table
            final String tableName = "jdbc_test";
            stmt.execute("DROP TABLE IF EXISTS " + tableName);
            stmt.execute("CREATE TABLE " + tableName + " (key BIGINT, value STRING)");

            // Query metadata
            DatabaseMetaData metaData = conn.getMetaData();
            System.out.println("product = " + metaData.getDatabaseProductName());
            System.out.println("jdbc version = "
                               + metaData.getDriverMajorVersion() + ", "
                               + metaData.getDriverMinorVersion());

            try (ResultSet tables = metaData.getTables(null, "default", tableName, null)) {
                while (tables.next()) {
                    String name = tables.getString("TABLE_NAME");
                    System.out.println("inspecting table: " + name);

                    try (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") + ")");
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Expected output:

product = MaxCompute/ODPS
jdbc version = 3, 8
inspecting table: jdbc_test
key    BIGINT(-5)
value    STRING(12)

Insert a single row

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class Main {

    private static final String DRIVER_NAME = "com.aliyun.odps.jdbc.OdpsDriver";
    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) {
        try {
            Class.forName(DRIVER_NAME);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.exit(1);
        }

        try (
            Connection conn = DriverManager.getConnection(
                "jdbc:odps:<maxcompute_endpoint>?project=<maxcompute_project>",
                Main.accessId, Main.accessKey);
            Statement stmt = conn.createStatement()
        ) {
            String dml = "INSERT INTO jdbc_test VALUES(1, \"foo\")";
            int ret = stmt.executeUpdate(dml);
            assert ret == 1;
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Insert multiple rows in a batch

Use PreparedStatement.executeBatch() to insert multiple rows in one operation.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Main {

    private static final String DRIVER_NAME = "com.aliyun.odps.jdbc.OdpsDriver";
    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) {
        try {
            Class.forName(DRIVER_NAME);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.exit(1);
        }

        try (
            Connection conn = DriverManager.getConnection(
                "jdbc:odps:<maxcompute endpoint>?project=<maxcompute project>",
                Main.accessId, Main.accessKey);
            PreparedStatement pstmt = conn.prepareStatement("INSERT INTO jdbc_test VALUES(?, ?)")
        ) {
            // First batch
            pstmt.setLong(1, 1L);
            pstmt.setString(2, "foo");
            pstmt.addBatch();

            // Second batch
            pstmt.setLong(1, 2L);
            pstmt.setString(2, "bar");
            pstmt.addBatch();

            int[] ret = pstmt.executeBatch();
            assert ret[0] == 1;
            assert ret[1] == 1;
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

executeBatch() does not support batch writes to clustered tables, including Transaction Table 2.0.

For standard partitioned tables, specify the target partition in the INSERT INTO statement. Example:

  -- Create a partitioned table
  CREATE TABLE IF NOT EXISTS sale_detail
  (
    shop_name     STRING,
    customer_id   STRING,
    total_price   DOUBLE
  )
  PARTITIONED BY (sale_date STRING, region STRING);

  -- Insert into a specific partition
  INSERT INTO sale_detail PARTITION(sale_date='20240219', region='hangzhou') VALUES(?, ?, ?)

Query data from a table

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Main {

    private static final String DRIVER_NAME = "com.aliyun.odps.jdbc.OdpsDriver";
    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) {
        try {
            Class.forName(DRIVER_NAME);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.exit(1);
        }

        try (
            Connection conn = DriverManager.getConnection(
                "jdbc:odps:<maxcompute endpoint>?project=<maxcompute project>",
                accessId, accessKey);
            Statement stmt = conn.createStatement();
            ResultSet rset = stmt.executeQuery("SELECT * FROM JDBC_TEST")
        ) {
            while (rset.next()) {
                System.out.println(rset.getInt(1) + "\t" + rset.getString(2));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

OdpsStatement supports execute(sql), executeQuery(sql), and executeUpdate(sql). The execute(sql) and executeQuery(sql) methods also accept metadata commands such as desc table, show tables, and show partitions.