All Products
Search
Document Center

:Access MaxCompute Lightning by using common tools

Last Updated:Aug 01, 2023

This topic describes how to use common client tools to access MaxCompute Lightning. You can also use the other tools that support PostgreSQL to access MaxCompute Lightning.

Alibaba Cloud Quick BI

Quick BI is an easy-to-use, high-performance big data analytics and visualization platform that is developed for users in the cloud. Quick BI is a user-friendly platform that allows all users to perform the tasks of a data analyst. To use Quick BI to access MaxCompute Lightning, perform the following steps:

  1. Log on to the Quick BI console. In the left-side navigation pane, click Data Sources.

  2. In the upper-right corner of the Data Sources page, click Create Data Source.

    Add Data Source
  3. In the Add Data Source dialog box, click the Cloud Data Sources or User-created Data Sources tab and click the PostgreSQL card.

    AddPostgreSQLData Sources
  4. In the AddPostgreSQLData Sources dialog box, select Manually connect to the database and configure the parameters for accessing MaxCompute Lightning.

    Parameter

    Description

    Name

    The name of the data source.

    Database Address

    The endpoint of MaxCompute Lightning in the region in which the MaxCompute project resides. You can select an endpoint based on the network type and the region in which the project resides. For more information about the endpoints of MaxCompute Lightning in different regions and networks, see Endpoints.

    Port Number

    The port that is used to access the MaxCompute project. Set the value to 443.

    Database

    The name of the MaxCompute project that you want to access.

    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. In the top navigation bar, select a region. Then, you can view the name of the MaxCompute project on the Project management tab.

    Schema

    Set the value to public.

    Username

    The AccessKey ID that is used to access the MaxCompute project.

    You can obtain the AccessKey ID from the AccessKey Pair page.

    Password

    The AccessKey secret that corresponds to the AccessKey ID.

    You can obtain the AccessKey secret from the AccessKey Pair page.

    SSL

    Select SSL.

  5. In the AddPostgreSQLData Sources dialog box, click Test Connection. After the connection is established, click OK.

    Connectivity test

For more information about how to query and analyze data by using Quick BI, see Use Quick BI to query and analyze data.

SQL Workbench/J

SQL Workbench/J is a popular, free, and cross-platform tool that is used to analyze SQL queries. You can use SQL Workbench/J to access MaxCompute Lightning based on the PostgreSQL driver. To use SQL Workbench/J to access MaxCompute Lightning, perform the following steps:

  1. Start SQL Workbench/J.

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

  2. On the right side of the Select Connection Profile dialog box, configure the parameters. The following table describes the parameters. You can configure other parameters as shown in the following figure.

    Select Connection Profile

    Parameter

    Description

    Driver

    The driver that is used to access the MaxCompute project. Set the value to PostgreSQL (org.postgresql.Driver).

    URL

    The URL that is used to access the MaxCompute project. The URL is in the jdbc:postgresql://<Lighting_endpoint>:<port>/<MaxCompute_project_name> format. When you configure this parameter, delete angle brackets (<>). Parameters in the URL:

    • Lighting_endpoint: the endpoint of MaxCompute Lighting in the region in which the MaxCompute project resides. You can select an endpoint based on the network type and the region in which the project resides. For more information about the endpoints of MaxCompute Lightning in different regions and networks, see Endpoints.

    • port: the port that is used to access the MaxCompute project. Set the value to 443.

    • MaxCompute_project_name: the name of the MaxCompute project that you want to access.

      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. In the top navigation bar, select a region. Then, you can view the name of the MaxCompute project on the Project management tab.

    Username

    The AccessKey ID that is used to access the MaxCompute project.

    You can obtain the AccessKey ID from the AccessKey Pair page.

    Password

    The AccessKey secret that corresponds to the AccessKey ID.

    You can obtain the AccessKey secret from the AccessKey Pair page.

  3. In the Select Connection Profile dialog box, click Extended Properties, add the ssl property, set the value to true, and then click OK.

    Add a property
  4. In the lower-right corner of the Select Connection Profile dialog box, click Test. After the connection is established, click OK.

For more information about how to use SQL Workbench/J to query and analyze data, see Connect SQL Workbench/J to MaxCompute.

psql

The PostgreSQL client (psql) is a command line tool of PostgreSQL. By default, psql is installed when you install the PostgreSQL database on your on-premises machine.

You can run the following command on psql to access MaxCompute Lightning:

psql -h <endpoint> -U <username> -d <MaxCompute_project_name> -p <port>

Parameter

Description

endpoint

The endpoint of MaxCompute Lightning in the region in which the MaxCompute project resides. You can select an endpoint based on the network type and the region in which the project resides. For more information about the endpoints of MaxCompute Lightning in different regions and networks, see Endpoints.

username

The AccessKey ID that is used to access the MaxCompute project.

You can obtain the AccessKey ID from the AccessKey Pair page.

MaxCompute_project_name

The name of the MaxCompute project that you want to access.

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. In the top navigation bar, select a region. Then, you can view the name of the MaxCompute project on the Project management tab.

port

The port that is used to access the MaxCompute project. Set the value to 443.

After you run the preceding command, enter the AccessKey secret that corresponds to the value of username when psql prompts you to enter a password. This way, you can establish a connection. The following figure shows an example.

psql

Tableau Desktop

Tableau Desktop is an application that is developed by the Tableau team based on the advanced technologies of Stanford University. You can use Tableau Desktop to analyze structured data and generate charts, coordinate graphs, dashboards, and reports in a few minutes. To use Tableau Desktop to access MaxCompute Lightning, perform the following steps:

  1. Start Tableau Desktop.

  2. In the left-side navigation pane of the To a Server section, click More. On the right side of the page, enter PostgreSQL in the search box and press Enter. In the Installed Connectors section, click PostgreSQL.

  3. In the PostgreSQL dialog box, configure the parameters. The following table describes the parameters.

    Parameter

    Description

    Server

    The endpoint of MaxCompute Lightning in the region in which the MaxCompute project resides. You can select an endpoint based on the network type and the region in which the project resides. For more information about the endpoints of MaxCompute Lightning in different regions and networks, see Endpoints.

    Port

    The port that is used to access the MaxCompute project. Set the value to 443.

    Database

    The name of the MaxCompute project that you want to access.

    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. In the top navigation bar, select a region. Then, you can view the name of the MaxCompute project on the Project management tab.

    Authentication

    Select Username and Password from the drop-down list.

    Username

    The AccessKey ID that is used to access the MaxCompute project.

    You can obtain the AccessKey ID from the AccessKey Pair page.

    Password

    The AccessKey secret that corresponds to the AccessKey ID.

    You can obtain the AccessKey secret from the AccessKey Pair page.

    Require SSL

    Select Require SSL.

  4. In the lower-right corner of the PostgreSQL dialog box, click Sign In.

    After the logon, use Tableau to create a worksheet for visual analysis.

    Note

    By default, the TIME function returns the UTC time. If you want to return time in the time zone of UTC+08:00, use the following configuration:

    SET timezone = 'Asia/Shanghai';

We recommend that you use TDC files supported by Tableau to customize the data sources that access MaxCompute Lightning. This improves the performance of MaxCompute Lightning. If you want to use TDC files to customize data sources, perform the following steps:

  1. Save the following content in a file named postgresql.tdc:

    <?xml version='1.0' encoding='utf-8' ?>
    <connection-customization class='postgres' enabled='true' version='8.10'>
    <vendor name='postgres'/>
    <driver name='postgres'/>
    <customizations>
    <customization name='CAP_CREATE_TEMP_TABLES' value='no' />
    <customization name='CAP_STORED_PROCEDURE_TEMP_TABLE_FROM_BUFFER' value='no' />
    <customization name='CAP_CONNECT_STORED_PROCEDURE' value='no' />
    <customization name='CAP_SELECT_INTO' value='no' />
    <customization name='CAP_SELECT_TOP_INTO' value='no' />
    <customization name='CAP_ISOLATION_LEVEL_SERIALIZABLE' value='yes' />
    <customization name='CAP_SUPPRESS_DISCOVERY_QUERIES' value='yes' />
    <customization name='CAP_SKIP_CONNECT_VALIDATION' value='yes' />
    <customization name='CAP_ODBC_TRANSACTIONS_SUPPRESS_EXPLICIT_COMMIT' value='yes' />
    <customization name='CAP_ODBC_TRANSACTIONS_SUPPRESS_AUTO_COMMIT' value='yes' />
    <customization name='CAP_ODBC_REBIND_SKIP_UNBIND' value='yes' />
    <customization name='CAP_FAST_METADATA' value='no' />
    <customization name='CAP_ODBC_METADATA_SUPPRESS_SELECT_STAR' value='yes' />
    <customization name='CAP_ODBC_METADATA_SUPPRESS_EXECUTED_QUERY' value='yes' />
    <customization name='CAP_ODBC_UNBIND_AUTO' value='yes' />
    <customization name='SQL_TXN_CAPABLE' value='0' />
    <customization name='CAP_ODBC_CURSOR_FORWARD_ONLY' value='yes' />
    <customization name='CAP_ODBC_TRANSACTIONS_COMMIT_INVALIDATES_PREPARED_QUERY' value='yes' />
    </customizations>
    </connection-customization>
  2. Save the file to the \My Tableau Repository\Datasources directory. If you use Tableau Server, save the file to \Tableau\Tableau Server\data\tabsvc\vizqlserver\Datasources in Windows or /var/opt/tableau/tableau_server/data/tabsvc/vizqlserver/Datasources/ in Linux.

  3. Restart Tableau and use the PostgreSQL data source to access MaxCompute Lightning. For more information about how to customize a data source by using a TDC file, see Tableau documentation.

For more information about how to use Tableau to query and analyze data, see Connect Tableau to MaxCompute.