The following sections use major client tools, such as SQL Workbench/J, PSQL, and Tableau BI tools, as examples to describe how to access MaxCompute Lightning. Other commonly used tools can be connected to MaxCompute Lightning in the same way as to PostgreSQL databases.

Alibaba Cloud Quick BI

  1. Log on Quick BI console, click Data source in the left-side navigation pane.
  2. On the data source management page, click the Create data source in the upper-right corner.
  3. Select PostgreSQL in the cloud database or external data source, and add a data source.
  4. In the dialog box that appears, enter the connection information for MaxCompute Lightning. Then, test the connection.
    Parameter Description
    Database address Enter the endpoint for the region of MaxCompute Lightning. You can enter the endpoint for a public network, classic network, or VPC network.
    Database Enter the name of the to-be-accessed MaxCompute project.
    Schema MaxCompute project name
    User name/Password User Access Key ID/Access Key Secret.

SQL Workbench/J

SQL Workbench/J is a widely used free and cross-platform SQL query tool. This tool can be connected to MaxCompute Lightning using the PostgreSQL driver.

  1. Download and install SQL Workbench/J.
  2. Start SQL Workbench/J, establish a database connection.

    Select the PostgreSQL driver, connect SQL Workbench/J to the MaxCompute Lightning URL of a project. You must enter the Access Key ID and Access Key Secret of the user.



    Alternatively, you can click Extended Properties and set ssl to true in the displayed dialog box.

  3. After SQL Workbench/J is connected to MaxCompute Lightning, you can view, query, and analyze the table data in the SQL Workbench/J workspace.

psql

The psql is a PostgreSQL interactive terminal that enables you to perform queries using commands. The clients of psql are installed by default when PostgreSQL databases are installed in a local PC.

You can connect psql to MaxCompute Lightning using psql commands. The syntax for the connection is the same as that for the connection to the PostgreSQL database.
psql -h <endpoint> -U <userid> -d <databasename> -p <port>
Parameter description:
  • <endpoint>: The endpoint of MaxCompute Lightning. For more information, see Access domain name.
  • <userid>: Access Key ID.
  • <databasename>: MaxCompute project name.
  • <port>: 443

After the command is executed, enter the <userid> password (Access Key Secret) in the command prompt.

Example:

Note SSL connections are preferred for psql by default.

Tableau Desktop

Start BI tools, select the PostgreSQL data source, and configure the connection.

When you configure the connection, select the SSL Connection check box.

After logging on to Tableau Desktop, you can create charts for visual analysis.

Note For better performance, it is recommended that you customize the connection to the Lightning data source using the TDC file supported by Tableau. Procedure:
  1. Save the following xml content as a postgresql.tdc file.
    <?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 Documents\My Tableau Repository\Datasources directory. If it is Tableau Server, save it in C:\ProgramData\Tableau\Tableau Server\data\tabsvc\vizqlserver\Datasources under Windows, and save it in /var/opt/tableau/tableau_server/data/tabsvc/vizqlserver/Datasources/ under Linux. .
  3. Reopen Tableau and use the PostgreSQL data source to connect to the MaxCompute Lightning service. For more information about custom data sources for tdc files, see official Tableau documentation.

FineReport

  1. Start FineReport, and select Server > Define database connection.
  2. Add a JDBC connection.
    The configurations are described as follows:
    Parameter Description
    Database Postgre
    Driver org.postgresql.Driver that is integrated in FineReport
    URL

    jdbc:postgresql://<MaxCompute Lightning Endpoint>:443/<Project_Name>? ssl=true&amp;prepareThreshold=0

    For example, jdbc:postgresql://lightning.cn-shanghai.maxcompute.aliyun.com:443/lightning_demo? ssl=true&amp;prepareThreshold=0

    User name/Password User Access Key ID and Access Key Secret