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, verify that the connection information you entered is correct and the IP address of the server where Tableau resides is added to a whitelist of your AnalyticDB for PostgreSQL instance. Then try again. For information about how to configure a whitelist, see Configure an IP address whitelist for an AnalyticDB for PostgreSQL instance.
    Parameter Description
    Server The public endpoint used to connect to your AnalyticDB for PostgreSQL database. For more information, see Manage public endpoints.
    Port The public port used to connect to your AnalyticDB for PostgreSQL database. The default port is 3432.
    Database The name of your AnalyticDB for PostgreSQL database.
    Username The username used to log on to your AnalyticDB for PostgreSQL database.
    Password The password used to log on to your AnalyticDB for PostgreSQL database.
  4. When the connection is established, the page shown in the following figure appears.tableau-adbpg2

Best practices

Analyze data.

You can analyze a table and produce an analysis report.

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

tableau-adbpg3

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 extract data from your AnalyticDB for PostgreSQL database:

FETCH 10000 in "SQL_CUR0x7fe678049e00"

If you want to extract a large volume of data and the server where Tableau resides provides sufficient memory to house the data, you can disable the cursor mode to increase performance.

To disable the cursor mode, follow these steps:

  1. Create a TDC file in which you specify to disable the cursor mode. The configuration of the TDC file is as follows:
    <? 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>
  2. The extension of the TDC file is tdc. If you use Tableau Desktop, place the TDC file to the Documents/My Tableau Repository/Datasources directory. If you use any other Tableau edition, find the Datasources folder and place the TDC file in it.
  3. Restart Tableau to make the settings take effect.

You can also change the value of the Fetch parameter, so more data can be fetched at a time.

<? 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>

Initialize SQL statements.

When you establish a connection, you can configure parameters by initializing SQL statements.

tableau-adbpg4
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.