Connect to SQL Workbench/J and manage data-MaxCompute(MaxCompute)-阿里云帮助中心

MaxCompute enables you to use the database management tool SQL Workbench/J to access your MaxCompute project and perform data management operations. This topic describes how to connect SQL Workbench/J to a MaxCompute project using the MaxCompute JDBC driver and manage data.

Background information

SQL Workbench/J is a free, cross-platform SQL query tool that operates independently of any database management system (DBMS). Written in Java, SQL Workbench/J runs on any operating system that provides a Java runtime environment.

Prerequisites

Before proceeding, confirm that you meet the following requirements:

You have created a MaxCompute project.

For more information about creating a MaxCompute project, see Create a MaxCompute project.
You have obtained an AccessKey ID and AccessKey secret with access permissions to the MaxCompute project.

You can obtain your AccessKey ID and AccessKey secret from the AccessKey Management page.
You have downloaded the MaxCompute JDBC driver (version 3.0.1 or later) that includes the complete dependency JAR package jar-with-dependencies.

The example in this topic uses MaxCompute JDBC driver version 3.2.9.
You have downloaded and installed SQL Workbench/J.

The example in this topic uses SQL Workbench/J Build 127, released on August 22, 2020.
You have installed Java 8 or a later version.

Procedure

Follow these steps to connect SQL Workbench/J to MaxCompute:

Step 1: Create a driver

Upload the MaxCompute JDBC driver JAR file to SQL Workbench/J so that SQL Workbench/J can access your MaxCompute project through this driver.
Step 2: Connect SQL Workbench/J to MaxCompute

Configure connection parameters to link SQL Workbench/J with your MaxCompute project.
Step 3: Manage your MaxCompute project using SQL Workbench/J

Use the established data connection in SQL Workbench/J to manage your MaxCompute project.

Step 1: Create a driver

Start SQL Workbench and open the SQL Workbench interface.

When the SQL Workbench interface opens, the Select Connection Profile dialog box appears automatically.
In the lower-left corner of the Select Connection Profile dialog box, click Manage Drivers. Create a new driver, enter a custom driver name, upload the MaxCompute JDBC driver JAR file, and then click OK to complete the driver configuration.

After uploading the MaxCompute JDBC driver, the Classname field is automatically set to com.aliyun.odps.jdbc.OdpsDriver.

Step 2: Connect SQL Workbench/J to MaxCompute

In the Profile configuration pane on the right side of the Select Connection Profile dialog box, enter a connection name (for example, MCtoSQL) and configure the parameters listed in the following table.

Parameter	Description
Driver	The driver used to connect to your MaxCompute project—the MaxCompute driver (com.aliyun.odps.jdbc.OdpsDriver) you created in Step 1.
URL	The URL for connecting to your MaxCompute project. Use the format `jdbc:odps:<MaxCompute_endpoint>?project=<MaxCompute_project_name>[&interactiveMode={true\|false}]`. Remove the `<>` symbols when configuring. The parameters are described below: <MaxCompute_endpoint>: Required. The endpoint of the region where your MaxCompute project is located. For a list of endpoints, see Endpoints. <MaxCompute_project_name>: Required. The name of the target MaxCompute project. This is the name of your MaxCompute project, not a workspace. You can sign in to the MaxCompute console, switch the region in the upper-left corner, and find the project name on the Projects page. interactiveMode: Optional. Specifies whether to enable the Query Acceleration feature. To enable Query Acceleration, append `&interactiveMode=true` to the URL. For more information, see Query Acceleration.
Username	The AccessKey ID that has access privileges to the target MaxCompute project. You can obtain your AccessKey ID from the AccessKey Management page.
Password	The AccessKey secret corresponding to the AccessKey ID. You can obtain your AccessKey secret from the AccessKey Management page.

Click Test. After the connection succeeds, click OK to create the data connection between SQL Workbench/J and MaxCompute.

Step 3: Manage your MaxCompute project using SQL Workbench/J

In the SQL Workbench/J interface, use the newly created data connection to manage your MaxCompute project by running MaxCompute SQL commands.

View all tables

After creating the data connection, SQL Workbench/J automatically switches to the SQL execution interface. Run the command show tables; to list all tables.

Enter your SQL statement in the Statement area and click the Run (▶) button on the toolbar to execute it. Results appear in the Result tab below.
View a table schema

After creating the data connection, SQL Workbench/J automatically switches to the SQL execution interface. Run the command desc <table_name>; to view the table schema.
View table data

After creating the data connection, SQL Workbench/J automatically switches to the SQL execution interface. Run a SELECT query to view table data.

Enter an SQL statement in the Statement area—for example, select * from bank_data_pt where age=35;—and click the Execute button on the toolbar. Query results appear in a table in the Result tab below.