Downloading JDBC and connecting to MaxCompute-MaxCompute(MaxCompute)-阿里云帮助中心

This guide explains how to download the MaxCompute JDBC driver and connect to MaxCompute. It includes sample code to help you get started.

Precautions

To execute SQL statements and obtain execution results by using the MaxCompute JDBC driver, you must meet the following requirements:
- You are a member of a project.
- You have the CREATE INSTANCE permission on the project.
- You have the SELECT and DOWNLOAD permissions on the table that you want to use.
  Note
  - When you use MaxCompute JDBC V1.9 or earlier, a temporary table is automatically created for each query. You can use Tunnel commands to obtain query results from the temporary table. To use these versions, you must have the CREATE TABLE permission.
  - When you use MaxCompute JDBC V2.2 or later, no temporary table is automatically created for each query. You can call the InstanceTunnel interface to obtain query results, regardless of whether you have the CREATE TABLE permission.
  For more information about MaxCompute permissions, see MaxCompute permissions.
MaxCompute provides the data protection feature. If the data protection feature is enabled for a project, you cannot move data out of the project. If you use MaxCompute JDBC of a version earlier than V2.4, no result sets can be obtained. If you use MaxCompute JDBC V2.4 or later, the number of result rows that you obtain cannot exceed the value of the READ_TABLE_MAX_ROW parameter. For more information about this parameter, see Project operations. For more information about the data protection feature, see Project data protection.

The MaxCompute V2.0 data type edition supports more data types, such as TINYINT, SMALLINT, DATETIME, TIMESTAMP, ARRAY, MAP, and STRUCT. To use these new data types, you must run the following command to enable the MaxCompute V2.0 data type edition. For more information, see Data type version guide.
```
set odps.sql.type.system.odps2=true
```
For JDBC V3.4.1 and later, if you have long SQL queries (longer than 1 KB), you should carefully control concurrency. For example, when you use an ECS instance with 8 vCPUs and 16.0 GiB of memory, the concurrency should not exceed 100. If you cannot effectively control concurrency, you can upgrade JDBC to V3.8.8 or V3.9.3 and set skipCheckIfSelect=true to disable the SQL parsing feature. For more information about this feature, see Version updates.

Download JDBC driver

You can obtain MaxCompute JAR packages for different versions from OSS, GitHub, or Maven repository. We recommend that you download the JAR package that includes all dependencies (jar-with-dependencies).

The following example shows the Project Object Model (POM) dependency for using the MaxCompute JDBC driver with Maven.

<dependency>
  <groupId>com.aliyun.odps</groupId>
  <artifactId>odps-jdbc</artifactId>
  <version>3.8.6</version>
  <classifier>jar-with-dependencies</classifier>
</dependency>

Note

The MaxCompute JDBC driver is an open source project available at aliyun-odps-jdbc.

We welcome your contributions to the development and improvement of the JDBC driver. You can report issues on the Issues page or contribute code improvements through Pull requests. When using Issues and Pull requests, follow the project's template requirements.

JDBC parameters

You can configure JDBC by using URL parameters and Properties objects. Properties objects have a higher priority and override URL parameters.

Note

If the URL key contains odps_config=config_file, JDBC reads config_file as Properties parameters.

Basic parameters

URL key	Property key	Required	Description
project	project_name	Yes	The name of the MaxCompute project.
accessId	access_id	Yes	The AccessKey ID of your Alibaba Cloud account. You can obtain your AccessKey ID on the AccessKey Management page.
accessKey	access_key	Yes	The AccessKey secret of your Alibaba Cloud account. You can obtain your AccessKey secret on the AccessKey Management page.
logview	logview_host	No	The URL of MaxCompute LogView. The value is fixed at `http://logview.odps.aliyun.com`.
tunnelEndpoint	tunnel_endpoint	No	The endpoint of the MaxCompute Tunnel service. For the Tunnel endpoints for each region and network type, see Endpoints.

Log configuration parameters

URL key	Property key	Required	Description
enableOdpsLogger	enable_odps_logger	No	Specifies whether to enable the MaxCompute JDBC logger. Valid values: False (default): Disables the logger. True: Enabled. Logs are written to the `jdbc.log` file in the JAR package's directory.
logConfFile	log_conf_file	No	You can specify an additional SLF4J configuration file to flexibly configure log output, such as the output file and logLevel. This method requires you to add the following dependencies to the `pom.xml` file of your project: `<dependency> <groupId>ch.qos.logback</groupId> <artifactId>logback-core</artifactId> <version>1.2.3</version> </dependency> <dependency> <groupId>ch.qos.logback</groupId> <artifactId>logback-classic</artifactId> <version>1.2.3</version> </dependency>` For a configuration example, see Configuration file example.
logLevel	log_level	No	The log level of the output. Default value: INFO.

Other parameters

URL key	Property key	Required	Description
stsToken	sts_token	No	The Alibaba Cloud STS token.
charset	charset	No	The character set for input and output. Default value: UTF-8.
useProjectTimeZone	use_project_time_zone	No	Specifies whether to use the `odps.sql.timezone` property of the project. Valid values: False (default): Does not use the property. True: Uses the property. Note You can also specify the time zone in a statement by using `set odps.sql.timezone=xxx`. Priority order: Statement > project > null.
disableConnectionSetting	disable_connection_setting	No	Specifies whether to allow setting SQL parameters for a connection. Valid values: False (default): Disallowed. True: Allowed. When this parameter is set to true, the `set xxx` command applies to both the statement and the connection. Otherwise, it applies only to the statement.
settings	settings	No	A JSON string for the global default `sql setting`. Example: `{"key":"value"}`.
tableList	table_list	No	The names of MaxCompute tables. Format: `projectname.tablename,projectname1.tablename1`.
connectTimeout	connect_timeout	No	The timeout for establishing a network connection. Default value: 10 seconds (s).
readTimeout	read_timeout	No	The timeout for reading data from a network connection. Default value: 120 seconds (s). Note The total timeout for each RESTful API request is the sum of connectTimeout and readTimeout, which defaults to 130 seconds. The driver retries each request up to 3 times. To adjust the connection timeout for RESTful API requests, modify the readTimeout parameter.
enableCommandApi	enable_command_api	No	Specifies whether to use the command API. Valid values: False (default): The command API is not used. True: The command API is used. When enabled, you can run commands in JDBC that are normally exclusive to odpscmd.
httpsCheck	https_check	No	Specifies whether to perform HTTPS certificate verification. Valid values: False (default): Verification is not performed. True: Verification is performed.
tunnelConnectTimeout	tunnel_connect_timeout	No	The connection timeout period for Tunnel when you download data. Default value: 180 seconds (s).
tunnelReadTimeout	tunnel_read_timeout	No	The read timeout period for Tunnel when you download data. Default value: 300 seconds (s).
skipCheckIfSelect	skipCheckIfSelect	No	Specifies whether to skip SQL parsing. Valid values: False (default): Does not skip parsing. True: Skips parsing. Note Skipping parsing can reduce client-side CPU and memory consumption, but may increase latency for non-SELECT statements.

Non-MCQA parameters (effective only in offline mode)

URL key

Property key

Required

Description

autoLimitFallback

auto_limit_fallback

Automatic limit fallback. Valid values:

False (default): Does not fall back.
True: Falls back. In offline mode, when Tunnel reports a no download permission exception, the driver automatically falls back and limits the number of downloaded records to 10,000.

MaxQA/MCQA 1.0 parameters (effective only for MaxQA/MCQA 1.0)

Basic configuration

URL key	Property key	Required	Description
interactiveMode	interactive_mode	No	Specifies whether to enable MaxQA/MCQA 1.0. Valid values: False (default): Disabled. True: Enabled.
executeProject	execute_project_name	No	The name of the MaxCompute project where the SQL task actually runs.
tunnelRetryTime	tunnel_retry_time	No	The number of Tunnel retries for SQLExecutor. Default value: 6.
attachTimeout	attach_timeout	No	The timeout period for establishing an MCQA 1.0 connection. Default value: 60 seconds (s).
fallbackQuota	fallback_quota	No	The quota to use when an MCQA 1.0 job falls back. If not configured, the project's default quota is used.
quotaName	quota_name	No	The computing resource quota used by the MaxQA job.

Limit-related parameters

URL key	Property key	Required	Description
instanceTunnelMaxRecord	instance_tunnel_max_record	No	The maximum number of records in the result set. Note This parameter takes effect only when the enableLimit parameter is set to False.
instanceTunnelMaxSize	instance_tunnel_max_size	No	The maximum size of the result set. Unit: byte.
autoSelectLimit	auto_select_limit	No	The automatic query limit. By default, you can query a maximum of 1,000,000 rows in Alibaba Cloud public cloud environments. To query more data, you can configure this parameter. Note This parameter takes effect only when the enableLimit parameter is set to False. For JDBC V3.2.29 and later, if you set the autoSelectLimit parameter, the enableLimit parameter is automatically set to False.
enableLimit	enable_limit	No	Specifies whether to enable the limit. Valid values: False: The limit is not enabled. True (default): The limit is enabled. If enabled, the download permission is not checked, and result sets are limited to 10,000 records by default.

Fallback-related parameters

URL key	Property key	Required	Description
fallbackForUnknownError	fallback_for_unknownerror	No	Specifies whether to fall back to offline mode when an unknown error occurs. Valid values: False: Does not fall back. True (default): Falls back.
fallbackForResourceNotEnough	fallback_for_resourcenotenough	No	Specifies whether to fall back to offline mode when resources are insufficient. Valid values: False: Does not fall back. True (default): Falls back.
fallbackForUpgrading	fallback_for_upgrading	No	Specifies whether to fall back to offline mode during an upgrade. Valid values: False: Does not fall back. True (default): Falls back.
fallbackForRunningTimeout	fallback_for_runningtimeout	No	Specifies whether to fall back to offline mode when an operation command times out. Valid values: False: Does not fall back. True (default): Falls back.
fallbackForUnsupportedFeature	fallback_for_unsupported_feature	No	Specifies whether to fall back to offline mode when an unsupported MCQA feature is used. Valid values: False: Does not fall back. True (default): Falls back.
alwaysFallback	always_fallback	No	Specifies whether to fall back to offline mode in all the preceding scenarios. Valid values: False (default): Does not fall back. True: Falls back. Note This parameter is supported only in JDBC V3.2.3 and later.
disableFallback	disable_fallback	No	Specifies whether to disable fallback to offline mode in all the preceding scenarios. Valid values: False (default): Falls back. True: Does not fall back.
fallbackQuota	fallback_quota	No	The name of the quota to which an MCQA job falls back. If not configured, the project's default quota is used.

Connect to MaxCompute

Load the MaxCompute JDBC driver.

Class.forName("com.aliyun.odps.jdbc.OdpsDriver");

Create a connection by using DriverManager.
```
Connection cnct = DriverManager.getConnection(url, accessId, accessKey);
```
- url: The URL must be in the following format: jdbc:odps:<maxcompute_endpoint>?project=<maxcompute_project_name>[&useProjectTimeZone={true|false}]. The parameters are described as follows:
  - <maxcompute_endpoint>: The endpoint of the MaxCompute service for the region. For example, the public endpoint for the China (Hangzhou) region is http://service.cn-hangzhou.maxcompute.aliyun.com/api. For more information, see Endpoints.
  - <maxcompute_project_name>: The name of the MaxCompute project.
  - useProjectTimeZone: Specifies whether to use the time zone of the MaxCompute project.
  Example:
```
jdbc:odps:http://service.cn-hangzhou.maxcompute.aliyun.com/api?project=test_project&useProjectTimeZone=true;
```
- accessId: The AccessKey ID of your Alibaba Cloud account.
- accessKey: The AccessKey secret that corresponds to the AccessKey ID.
  Note
  To create and view an AccessKey ID and an AccessKey secret, see Prepare an Alibaba Cloud account.

Execute a query.

try (
    Statement stmt = cnct.createStatement();
    ResultSet rset = stmt.executeQuery("SELECT foo FROM bar;")
) {
    while (rset.next()) {
      // process the results
    }
} catch (SQLException e) {
  // handle the exception
} finally {
    if (cnct != null) {
        try {
            cnct.close();
        } catch (SQLException e) {
            // Ignore or record a closed exception
        }
    }
}

Sample code

Drop a table, create a table, and get metadata

Note

If you add the JDBC dependency to your project, do not add the SDK dependency separately. The JDBC dependency includes the required SDK, and adding it separately can cause version-mismatch errors.

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";
    // An Alibaba Cloud account's AccessKey pair has permissions for all API operations, which is a security risk. We strongly recommend using a RAM user for API access or routine O&M. To create a RAM user, log on to the RAM console.
    // This example stores the AccessKey ID and AccessKey secret in environment variables. You can also store them in a configuration file based on your business requirements.
    // For security, do not hardcode the AccessKey ID and AccessKey secret in your 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)");

            // get meta data
            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();
        }
    }
}

Example output:

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

Update a table

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";
    // An Alibaba Cloud account's AccessKey pair has permissions for all API operations, which is a security risk. We strongly recommend using a RAM user for API access or routine O&M. To create a RAM user, log on to the RAM console.
    // This example stores the AccessKey ID and AccessKey secret in environment variables. You can also store them in a configuration file based on your business requirements.
    // For security, do not hardcode the AccessKey ID and AccessKey secret in your 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()
        ) {
            // The following DML also works
            // String dml = "INSERT INTO jdbc_test SELECT 1, \"foo\"";
            String dml = "INSERT INTO jdbc_test VALUES(1, \"foo\")";
            int ret = stmt.executeUpdate(dml);

            assert ret == 1;
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Batch update a table

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";
    // An Alibaba Cloud account's AccessKey pair has permissions for all API operations, which is a security risk. We strongly recommend using a RAM user for API access or routine O&M.
    // This example stores the AccessKey ID and AccessKey secret in environment variables. You can also store them in a configuration file based on your business requirements.
    // For security, do not hardcode the AccessKey ID and AccessKey secret in your 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);
            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();
        }
    }
}

Note

The executeBatch method does not support batch writes to clustered tables, such as Transaction Table 2.0 tables.

To write data to a standard partitioned table in batches, you must specify the target partition in the INSERT INTO statement. Example:

-- The table creation statement for the partitioned table sale_detail is as follows.
create table if not exists sale_detail
(
shop_name string,
customer_id string,
total_price double
)
partitioned by (sale_date string, region string);

-- Assume that the partition sale_date='20240219', region='hangzhou' already exists. The INSERT INTO statement for batch writes to the partitioned table is as follows.
INSERT INTO sale_detail PARTITION(sale_date='20240219', region='hangzhou') VALUES(?, ?, ?)

Query 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";
    // An Alibaba Cloud account's AccessKey pair has permissions for all API operations, which is a security risk. We strongly recommend using a RAM user for API access or routine O&M.
    // This example stores the AccessKey ID and AccessKey secret in environment variables. You can also store them in a configuration file based on your business requirements.
    // For security, do not hardcode the AccessKey ID and AccessKey secret in your 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>",
                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();
        }
    }
}

Note

OdpsStatement supports three methods: execute(sql), executeQuery(sql), and executeUpdate(sql). The execute(sql) and executeQuery(sql) methods also support three common commands: desc table, show tables, and show partitions.