All Products
Search
Document Center

:Use Tableau to connect to AnalyticDB for PostgreSQL

Last Updated:Nov 16, 2023

Tableau is a data analysis and visualization tool that can be seamlessly connected to tables and databases on your computer or in the cloud. This topic describes how to use Tableau to connect to an AnalyticDB for PostgreSQL database.

Procedure

  1. Start Tableau.

  2. On the Connect page, choose To a Server > More > Pivotal Greenplum Database.

  3. In the Pivotal Greenplum Database dialog box, enter the information used to connect to your AnalyticDB for PostgreSQL database and click Sign In.

    Note

    If the connection fails, check whether the connection information that you entered is correct and whether the IP address of the server where Tableau resides has been added to an IP address whitelist of your AnalyticDB for PostgreSQL instance. Then, try again. For information about how to configure an IP address whitelist, see Configure an IP address whitelist.

    Use Tableau to connect to AnalyticDB for PostgreSQL

    Parameter

    Description

    Server

    The public endpoint used to connect to your AnalyticDB for PostgreSQL database. For more information, see Manage public endpoints.

    Port

    The port used to connect to your AnalyticDB for PostgreSQL database. The default port number is 3432.

    Database

    The name of your AnalyticDB for PostgreSQL database.

    Username

    The username of the account used to connect to your AnalyticDB for PostgreSQL database.

    Password

    The password of the account used to connect to your AnalyticDB for PostgreSQL database.

Analyze data

You can analyze a table and produce an analysis report.

For example, you can choose a lineitem node under TPC-H data and view the data of a table in a dimension under that node.

If you choose a field under Dimensions or Measures and drop it to a worksheet, Tableau sends a query to your AnalyticDB for PostgreSQL database. In this example, Tableau sends the following query:

BEGIN;declare "SQL_CUR0x7fdabf04ca00" cursor with hold for SELECT "lineitem"."l_linestatus" AS "l_linestatus",
          "lineitem"."l_shipmode" AS "l_shipmode",
          SUM("lineitem"."l_orderkey") AS "sum_l_orderkey_ok",
          ((CAST("lineitem"."l_shipdate" AS DATE) + CAST(TRUNC((-1 * (EXTRACT(DAY FROM "lineitem"."l_shipdate") - 1))) AS INTEGER) * INTERVAL '1 DAY') + CAST(TRUNC((-1 * (EXTRACT(MONTH FROM "lineitem"."l_shipdate") - 1))) AS INTEGER) * INTERVAL '1 MONTH') AS "tyr_l_shipdate_ok"
        FROM "public"."lineitem" "lineitem"
        GROUP BY 1,
          2,
          4;fetch 10000 in "SQL_CUR0x7fdabf04ca00"

Disable the cursor mode

By default, Tableau uses the cursor mode to fetch data from your AnalyticDB for PostgreSQL database. Example:

FETCH 10000 in "SQL_CUR0x7fe678049e00"

If you want to fetch a large amount of data and if the server where Tableau resides provides sufficient memory to store the data, you can disable the cursor mode to improve performance.

To disable the cursor mode, perform the following steps:

  1. Create a TDC file and enter the following information in the file:

    <?xml version='1.0' encoding='utf-8' ?>  
    <connection-customization class='greenplum' enabled='true' version='4.3'>  
    <vendor name='greenplum'/>  
    <driver name='greenplum'/>  
    <customizations>  
    <customization name='odbc-connect-string-extras' value='UseDeclareFetch=0' />
    </customizations>  
    </connection-customization>

    You can also change the value of the Fetch parameter to fetch more data at a time. Example:

    <?xml version='1.0' encoding='utf-8' ?>  
    <connection-customization class='greenplum' enabled='true' version='4.3'>  
    <vendor name='greenplum'/>  
    <driver name='greenplum'/>  
    <customizations>  
    <customization name='odbc-connect-string-extras' value='Fetch=100000' />  
    </customizations>  
    </connection-customization>
  2. Save the file with an extension of .tdc.

  3. Place the TDC file to the Datasources directory.

    If you use Tableau Desktop, you must place the TDC file to the Documents/My Tableau Repository/Datasources directory.

  4. Restart Tableau to make the settings take effect.

Configure initial SQL statements

You can configure initial SQL statements that are automatically executed when a connection is established. You can configure specific parameters in the statements.

Note

Do not add semicolons (;) to the end of an SQL statement. This is because Tableau encapsulates each SQL statement. If you add semicolons (;), the system reports syntax errors. When you customize an SQL statement, do not add semicolons to its end either.