MaxCompute allows you to use the database management tool SQL Workbench/J to access MaxCompute projects and manage data. This topic describes how to use a MaxCompute Java Database Connectivity (JDBC) driver to connect SQL Workbench/J to MaxCompute projects and use SQL Workbench/J to manage data.
Background information
SQL Workbench/J is a free, DBMS-independent, and cross-platform SQL query tool. SQL Workbench/J is written in Java and can run on operating systems that provide the Java Runtime Environment (JRE).
Prerequisites
A MaxCompute project is created.
For more information about how to create a MaxCompute project, see Create a MaxCompute project.
The AccessKey pair of your Alibaba Cloud account that is used to access the MaxCompute project is obtained.
You can click the profile picture in the upper-right corner of the MaxCompute console and select AccessKey Management to obtain the AccessKey pair.
The JAR package of MaxCompute JDBC driver V3.0.1 or later is downloaded. This package includes the
jar-with-dependenciespackage.MaxCompute JDBC driver V3.2.9 is used in this topic.
SQL Workbench/J is downloaded and installed.
SQL Workbench/J Build 127 (2020-08-22) is used in this topic.
Java 8 or later is installed.
Procedure
To connect SQL Workbench/J to the MaxCompute project and use SQL Workbench/J to manage the MaxCompute project, perform the following steps:
Step 1: Add the MaxCompute JDBC driver
Upload the JAR package of the MaxCompute JDBC driver to SQL Workbench/J. Then, SQL Workbench/J can use this driver to access the MaxCompute project.
Step 2: Connect SQL Workbench/J to MaxCompute
Configure connection parameters to connect SQL Workbench/J to the MaxCompute project.
Step 3: Use SQL Workbench/J to manage the MaxCompute project
Use SQL Workbench/J to manage the MaxCompute project based on the established connection.
Step 1: Add the MaxCompute JDBC driver
Start SQL Workbench/J.
When you start SQL Workbench/J, the Select Connection Profile dialog box is automatically displayed.
In the lower-left corner of the Select Connection Profile dialog box, click Manage Drivers. In the Manage drivers dialog box, enter a custom driver name in the Name field, upload the JAR package of the MaxCompute JDBC driver, and then click OK.
After the JAR package of the MaxCompute JDBC driver is uploaded, the Classname parameter is automatically set to com.aliyun.odps.jdbc.OdpsDriver.
Step 2: Connect SQL Workbench/J to MaxCompute
On the right side of the Select Connection Profile dialog box, enter the connection name, such as MCtoSQL, and configure connection parameters. The following table describes the connection parameters.
Parameter
Description
Driver
The driver that is used to connect SQL Workbench/J to the MaxCompute project. Select the MaxCompute JDBC driver (com.aliyun.odps.jdbc.OdpsDriver) that is configured in Step 1 from the Driver drop-down list.
URL
The URL that is used to connect to the MaxCompute project. The format is
jdbc:odps:<MaxCompute_endpoint>?project=<MaxCompute_project_name>[&interactiveMode={true|false}]. Delete theangle brackets (<>)when you configure this parameter. Parameters:<MaxCompute_endpoint>: required. The endpoint of MaxCompute. Configure this parameter based on the region where the MaxCompute project resides.
For more information about the endpoints of MaxCompute in different regions, see Endpoints.
<MaxCompute_project_name>: required. The name of the MaxCompute project.
This parameter specifies the name of your MaxCompute project instead of the DataWorks workspace to which the MaxCompute project corresponds. You can log on to the MaxCompute console, select the region where your MaxCompute project resides in the top navigation bar, and then view the name of the MaxCompute project on the Project management tab.
interactiveMode: optional. This parameter specifies whether to enable the MaxCompute Query Acceleration (MCQA) feature.
If you want to enable the MCQA feature, add
&interactiveMode=trueto the end of the URL. For more information about the MCQA feature, see Query acceleration.
Username
The AccessKey ID that is used to access the MaxCompute project.
You can click the profile picture in the upper-right corner of the MaxCompute console and select AccessKey Management to obtain the AccessKey ID.
Password
The AccessKey secret that corresponds to the AccessKey ID.
You can click the profile picture in the upper-right corner of the MaxCompute console and select AccessKey Management to obtain the AccessKey secret.
Click Test. If the message shown in the following figure appears, click OK. The connection between SQL Workbench/J and the MaxCompute project is established.
Step 3: Use SQL Workbench/J to manage the MaxCompute project
On SQL Workbench/J, you can execute MaxCompute SQL statements to manage the MaxCompute project based on the established connection.
Query all existing tables
After you establish the connection between SQL Workbench/J and the MaxCompute project, you are redirected to the SQL execution page. Then, you can execute the
show tables;statement to query information about all existing tables.
Query a table schema
After you establish the connection between SQL Workbench/J and the MaxCompute project, you are redirected to the SQL execution page. Then, you can execute the
desc <table_name>;statement to query the schema of a table.
Query table data
After you establish the connection between SQL Workbench/J and the MaxCompute project, you are redirected to the SQL execution page. Then, you can execute the SELECT statement to query the data of a table.
