How to Connect Tableau to PostgreSQL

PostgreSQL is an advanced open-source relational database. Business analytics tools such as Excel, Tableau, and Power BI can be used to visualize the data stored in PostgreSQL databases, transforming it into valuable business insights. However, a key challenge in visualizing PostgreSQL data with analytics tools is the ability to connect to and securely access your PostgreSQL data. Progress DataDirect's ODBC and JDBC drivers for PostgreSQL offer a high-performing, secure, and reliable solution for connecting Tableau to PostgreSQL. This tutorial walks you through the process of connecting Tableau to PostgreSQL using the ODBC and JDBC drivers on Windows. The following tasks are covered:

• Install and configure the PostgreSQL ODBC driver
• Import PostgreSQL data into Tableau using the ODBC driver
• Install and configure the PostgreSQL JDBC driver
• Import PostgreSQL data into Tableau using the JDBC driver

The following steps describe how to install and configure the PostgreSQL ODBC driver on Windows.

Note: JDBC connectivity to PostgreSQL is supported with the PostgreSQL JDBC Driver.

1. Click the PostgreSQL ODBC Driver button.
The PostgreSQL ODBC driver provides ODBC connectivity to PostgreSQL database servers.
PostgreSQL ODBC Driver
2. Select either a 32-bit or 64-bit driver based on the requirements of your operating system.
3. Provide the required information; then, click DOWNLOAD.
4. After downloading the product, unzip the installer files into a temporary directory, and then double-click the installer program:
   • For 32-bit drivers: PROGRESS_DATADIRECT_ODBC_8.0_WIN_32_INSTALL.exe
   • For 64-bit drivers: PROGRESS_DATADIRECT_ODBC_8.0_WIN_64_INSTALL.exe
5. Follow the prompts to complete the installation.
6. Start the ODBC Administrator by selecting its icon from the Progress DataDirect for ODBC program group.
7. On the ODBC Administrator, select the User DSN tab, and then configure a data source. If you want to configure:
   • An existing data source, select the data source name and click Configure. The driver Setup dialog box appears.
   • A new data source, click Add to display a list of installed drivers. Select the PostgreSQL Wire Protocol driver and click Finish. The driver setup dialog box appears.

     

8. Specify values for the following connection options to authenticate using the User ID/Password authentication; then, click Test Connect.
   • Data Source Name: Type a string that identifies this data source configuration, such as Projects.
   • Description: Type an optional long description of a data source name, such as My Development Projects.
   • Host Name: Type the name or the IP address of the server to which you want to connect.
   • Port Number: Type the port number of the server listener. The default is 5432.
   • Database Name: Type the name of the database to which you want to connect.

   Note: For demonstration purposes, this tutorial uses the default authentication method, user ID and password (0 - UserID/Password), to authenticate to the server. However, you can use any of the authentication methods supported by the driver. For more information, refer to the user's guide for the driver.

9. The Logon dialog box appears. Specify the user name and password; then, click OK.

   

10. If the test was successful, the window displays a confirmation message.
11. Click OK to close the setup dialog. The values you have specified are saved and are the defaults used when you connect to the data source. You can change these defaults by using the setup dialog to modify your data source, or you can override these defaults by connecting to the data source using a connection string with alternate values.
12. Connect to your server and begin accessing data with your applications, BI tools, database tools, and more. Proceed to the next section to learn how to access PostgreSQL data from Tableau.

The following steps show how to use the driver to connect to PostgreSQL from Tableau and import data.

1. Navigate to the \tools\Tableau subdirectory of the Progress DataDirect installation directory; then, locate the following Tableau data source file:

   DataDirect PostgreSQL.tdc

2. Copy the Tableau data source file into the following directory:

   C:\Users\user_name\Documents\My Tableau Repository\Datasources

3. Open Tableau. If the Connect menu does not open by default, select Data > New Data Source or the Add New Data Source button to open the menu.
4. From the Connect menu, select Other Databases (ODBC).
5. The Other Databases (ODBC) dialog appears. In the DSN field, select the data source you want to use from the drop-down menu. For example, PostgreSQL Wire Protocol. Then, click Connect.

   

6. The Logon dialog appears pre-populated with the connection information you provided in your data source. If required, type your user name and password; then, click OK. The Logon dialog closes. Then, click Sign in on the Other Databases (ODBC) dialog.
7. The Data Source window appears. By default, Tableau connects live, or directly, to your data. We recommend that you use the default settings to avoid extracting all of your data. However, if you prefer, you can import your data by selecting the Extract radio button.

   

8. Select the schema you want to use from its drop-down list. The tables stored in the schema you selected are now available for selection in the Table field. To access data, you can either drag the tables into the canvas on the right or double-click the New Custom SQL button at the bottom of the window and then run SQL statements in the Edit Custom SQL window.

You have successfully accessed your data and are now ready to create reports with Tableau. For more information, refer to the Tableau product documentation at: http://www.tableau.com/support/help.

The following steps describe how to install and configure the PostgreSQL JDBC driver.

Note: ODBC connectivity to PostgreSQL is supported with the PostgreSQL ODBC Driver.

1. Click the PostgreSQL JDBC Driver button.
The PostgreSQL JDBC driver provides JDBC connectivity to PostgreSQL database servers.
PostgreSQL JDBC Driver
2. Provide the required information; then, click DOWNLOAD.
3. After downloading the product, unzip the installer files into a temporary directory, and then double-click the installer program:

   PROGRESS_DATADIRECT_JDBC_POSTGRESQL_WIN.exe

4. Follow the prompts to complete the installation.
5. Set your system CLASSPATH to include the driver .jar file. The CLASSPATH is the search string your Java Virtual Machine (JVM) uses to locate JDBC drivers on your computer. The following example demonstrates setting the CLASSPATH from a command line using the default installation directory.

   CLASSPATH=.;C:\Program Files\Progress\DataDirect\JDBC\lib\60\postgresql.jar

6. Configure your driver using one of the following methods:
   • Connection URL: You can begin using the driver immediately by passing a connection URL with your application or tool. The following example shows how to connect using user ID/password authentication. jdbc:datadirect:postgresql://server1:5432;User=test;Password=secret;

     Note: The User and Password properties are not required to be stored in the connection string. They can also be passed separately by the application.

     Note: For demonstration purposes, this tutorial uses the default authentication method, user ID/password, to authenticate to the server. However, you can use any of the authentication methods supported by the driver. For more information, refer to the user's guide for the driver.

   • Data sources: The driver also supports connecting using JDBC data sources. A JDBC data source is a Java object, specifically a DataSource object, that defines connection information required for a JDBC driver to connect to the database. For more information, refer to Connecting using data sources in the user's guide.

   Note: For most connections, specifying the minimum required connection properties is sufficient to begin accessing data; however, you can provide values for optional properties to use additional supported features and improve performance. For more information, refer to Connection properties in the user's guide.

The following steps show how to use the driver to connect to PostgreSQL from Tableau and import data.

1. Navigate to the \lib\xx subdirectory of the Progress DataDirect installation directory; then, locate the jar file for your driver: postgresql.jar.
2. Copy the .jar file for your driver into the following directory: C:\Program Files\Tableau\Drivers
3. Open Tableau. If the Connect menu does not open by default, select Data > New Data Source or the Add New Data Source button to open the menu.
4. From the Connect menu, select Other Databases (JDBC).

   

5. In the Other Databases (JDBC) dialog, provide values for the following fields; then, click Sign In.
   • URL: Copy and paste your connection URL into this field. For example: jdbc:datadirect:postgresql://server1:5432;
   • Dialect: Select SQL92 (the default) from the drop-down box.
   • Username: Specify your user name. Alternatively, this value can be specified with the User property in the connection string.
   • Password: Specify your password. Alternatively, this value can be specified with the Password property in the connection string.
6. The Data Source window appears. Select the database you want to use from the drop-down menu. The tables stored in the database you selected are now available for selection in the Table field. To access data, you can either drag the tables into the canvas on the right or double-click the New Custom SQL button at the bottom of the window and then run SQL statements in the Edit Custom SQL window.

   

You have successfully accessed your data and are now ready to create reports with Tableau. For more information, refer to the Tableau product documentation at: http://www.tableau.com/support/help.

Thank you for taking the time to consider the Progress DataDirect solution for connecting Tableau to PostgreSQL. Please contact us for additional information.