Back

This guide describes how to download and install SQL Workbench/J and connect to the database associated with your DataAccess account.

In this article:

Table of Contents Placeholder

How it Works

SQL Workbench/J is a free query tool that is simple to use and can connect to many database management systems (DBMS), such as PostgreSQL (which Redshift is based on), or MySQL. Using SQL Workbench/J is a quick and easy way to connect to your database, view your database schema, and analyze your data.

Download SQL Workbench and Amazon Redshift JDBC Driver

Use the following steps to download SQL Workbench and the Amazon Redshift JDBC driver:

  1. To download and install SQL Workbench, follow the directions located at: http://www.sql-workbench.net/getting-started.html.
  2. To configure SQL Workbench to make use of the Amazon Redshift JDBC driver, download the Amazon Redshift JDBC driver from: http://docs.aws.amazon.com/redshift/latest/mgmt/configure-jdbc-connection.html.
    At time of this writing, the compatible driver version is 4.1.
  3. Save the .jar file to a safe location on your computer.
    Once saved, do not delete as this will cause connections to break.

For additional information, see connecting using SQL Workbench for Windows.

Build Amazon Redshift JDBC Driver

The driver for Amazon Redshift JDBC must be installed in SQL Workbench before you can connect to the database.

Use the following steps to install the Amazon Redshift JDBC driver:

  1. Open SQL Workbench/J.
  2. Click Manage Drivers in the lower left of the screen.
  3. Click the Create a new entry icon.
    The Manage drivers dialog displays.
    WhiteUI_DataAccess_Connecting to EventDB and AudienceDB with SQL WorkbenchJ_Create a New Entry Button.jpg
  4. In the Name field, enter Redshift.
  5. Click the folder icon to the right of the Library box.
    WhiteUI_DataAccess_Connecting to EventDB and AudienceDB with SQL WorkbenchJ_Select the JAR File_Button.jpg
  6. Navigate to the location of the driver, click to select it, and then click Open.
    The Classname field is now auto-filled with com.amazon.redshift.jdbc41.Driver
    Where "41" indicates the driver version.
  7. Click OK to exit.
    Your Redshift database is now configured

Create a Connection Profile

Use the following steps to create a connection profile by providing SQL Workbench with the connection credentials to your Tealium database:

  1. In the SQL Workbench/J interface, click the Create a new connection profile icon.
    WhiteUI_DataAccess_Connecting to EventDB and AudienceDB with SQL WorkbenchJ_Create a New Connection Profile.jpg
  2. Enter a name your new profile.
    Example:  A name that combines your Tealium account and profile, such as “ACCOUNT-PROFILE”
  3. In the Driver drop-down list, select Amazon Redshift JDBC Driver (com.amazon.redshift.jdbc41.Driver).
    WhiteUI_DataAccess_Connecting to EventDB and AudienceDB with SQL WorkbenchJ_Create a New Connection Profile.jpg
  4. In your Universal Data Hub (UDH) account, use the sidebar to navigate to DataAccess > EventDB or DataAccess > AudienceDB.
  5. Click Get DB Connection Details.
    The DB Connection Details dialog displays. Keep this window open as you proceed. Note that, in the following example, credentials are removed for confidentiality.
    WhiteUI_DataAccess_Connecting to EventDB and AudienceDB with SQL Workbench J_Get DB Connection Details.jpg

    Your Account or Deployment Manager should have previously provided you with your password. If not, you can click Regenerate DB Credentials to receive new credentials. This will not delete any existing data, but will break existing connections you might have.

  6. Return to the SQL Workbench interface and enter a URL based on the following example, where "HOST", "PORT", and "DATABASE" are replaced with the connection values from DataAccess:
     jdbc:redshift://HOST:PORT/DATABASE?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory
    WhiteUI_DataAccess_Connecting to EventDB and AudienceDB with SQL WorkbenchJ_Create and Enter URL.jpg

    The path may change in the future based on enhancements. If you believe this is an issue, reach out to your Account or Deployment Manager for connection assistance.

  7. Use your credentials to enter your Username and Password.
  8. Check the Autocommit checkbox.
    WhiteUI_DataAccess_Connecting to EventDB and AudienceDB with SQL Workbench:J_Check Autocommit Checkbox.jpg
  9. Click the Save Profile List icon to save.
    WhiteUI_DataAccess_Connecting to EventDB and AudienceDB with SQL WorkbenchJ_Save Profile List.jpg
  10. Click OK to attempt to connect.
    If successful, the Statement page displays. From here, you can begin writing scripts to query the data.
    If you have recently enabled EventDB or AudienceDB, it may take up to an hour for data to become available.

Troubleshooting

The following sections provide information about common errors and troubleshooting tips.

Common Errors

  • Deleting the Redshift JAR file
    If the Redshift JAR file is deleted, connections will break. Ensure to save to your Home directory or another common location
  • Mixing the Username and Database Name
    • The Username contains your account_profile.
    • The Database is your Tealium iQ account name.
  • Not Checking the Autocommit Checkbox
    If you do not check the Autocommit checkbox prior to saving, only one (1) query can be made per connection instance.

Tips

  • Show Password
    If you need to view your password, click Show password to the right of the Password input field in SQLWorkbench/J.
    WhiteUI_DataAccess_Connecting to EventDB and AudienceDB with SQL WorkbenchJ_Show Password.jpg
  • Enabling an Event Feed for EventDB
    For EventDB, you must enable an event feed to be sent to EventDB. For additional information, see Creating an Event Feed.
  • Enabling Attributes for AudienceDB
    For AudienceDB, you must enable each attribute to be sent to AudienceDB. For additional information, go to Adding Attributes to AudienceDB.
    • If you do not complete the above steps, you will see many  visit_ and  visitor_ tables and views that contain only basic visitor-level data and Audiences. No attribute data will be included.
    • There is a "soft" limit of 250 attributes in AudienceDB. After this number is reached, you may experience performance degradation.
Tags (1)