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). For more information about SQL Workbench/J, see SQL Workbench/J.

Prerequisites

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 Name, upload the JAR package of the MaxCompute JDBC driver, and then click OK.
    Manage drivers dialog box

    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.
    Configure 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 Uniform Resource Locator (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 to which you want to connect Tableau.

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

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. For more information, see the SQL Workbench/J help information.
  • 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 of all existing tables.

    Query all existing tables
  • Query 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 schema
  • 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.

    Query table data