All Products
Search
Document Center

MaxCompute:Connect SQL Workbench/J to MaxCompute

Last Updated:Nov 08, 2024

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-dependencies package.

    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:

  1. 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.

  2. Step 2: Connect SQL Workbench/J to MaxCompute

    Configure connection parameters to connect SQL Workbench/J to the MaxCompute project.

  3. 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

  1. Start SQL Workbench/J.

    When you start SQL Workbench/J, the Select Connection Profile dialog box is automatically displayed.

  2. 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

  1. 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 the angle 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=true to 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.

  2. 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.

    查看表数据