Connecting to EventDB and AudienceDB with SQLWorkbench/J

Connecting to EventDB and AudienceDB with SQLWorkbench/J

by ‎11-25-2015 02:29 PM - edited ‎05-10-2017 04:37 PM (921 Views)

This guide shows how to download and install SQL Workbench/J, then connect to the database associated with your DataAccess account. This is a quick and easy way to connect to your database, see the schema, and analyze your data.

SQL Workbench/J is a simple and free SQL query tool that can also connect to many database management systems (DBMS) such as PostgreSQL (what Redshift is based on) or MySQL.

This article covers the following topics: 

Table of Contents Placeholder

Download SQL Workbench and Amazon Redshift JDBC Driver

  1. For downloading and installing SQL Workbench, follow the directions located at: http://www.sql-workbench.net/getting-started.html.
  2. SQL Workbench needs to be configured 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. Any location is fine, but this file cannot be deleted or connections will break.

JAR file in Home Directory.png

Alternate guide for 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.

To install the Amazon Redshift JDBC driver:

  1. Open SQL Workbench and click Manage Drivers
    Manage Drivers.png
  2. Click the Create a new entry… button
    Create a New Entry.png
  3. Name the entry “Redshift”
    Name Driver.png
  4. Click the Select the JAR file… button
    Select JAR File.png
  5. Navigate to download folder, click the JAR file, and then click Choose to apply
  6. The Classname should be auto-filled with “com.amazon.redshift.jdbc41.Driver”
    Classname Prefilled.png
  7. Click OK in the Manage Drivers window to exit. Your Redshift Database is now configured

Create a Connection Profile

Now it's time to create a connection profile. This is where you provide SQL Workbench with the connection credentials to your Tealium database.

  1. Click the Create a new connection profile button
    New Connection Profile.png
  2. Name your new profile eg. a name combining your Tealium account and profile such as “ACCOUNT-PROFILE”
  3. In the Driver drop-down, choose Redshift (com.amazon.redshift.jdbc41.Driver)
  4. Now, within DataAccess, navigate to Act > DataAccess Console > DB > Get DB Connection Details
    Get DB Credentials.png
  5. Keep the below window open as you proceed, noting that we've removed our credentials for confidentiality. Your Account or Deployment Manager should have previously provided you with your password. If not, you can click Regenerate DB Credentials to receive new details. This will not delete any existing data, but will break any existing connections you might have.
    DB Credentials.png
  6. In SQL Workbench, enter a URL based on the following format, 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

    Configure Connection.png
    Note, t
    he path may change in the future based on enhancements. Please reach out to your Account or Deployment Manager for connection assistance.
  7. Enter your Password from the credentials.
  8. Check the checkbox for Autocommit
  9. Click the OK to attempt to connect

If successful, you will be taken to the Statement page where 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.

Upon closing SQL Workbench for the first time after successfully creating a new conncetion profile, you will be prompted to save your new connection profile. Be sure to accept.

Common Errors

  • The Redshift JAR file cannot be deleted or connections will break. Be sure to save to your Home directory as described above.
  • Mixing the Username and Database name: the Username contains your account_profile where the Database is your Tealium iQ account name.
  • Not checking the "Autocommit" option: otherwise only 1 query can be made per connection instance.

Notes

  • If you need to access your password, click the "Show password" button to the right of the input field in SQLWorkbench/J.
  • For EventDB, you have to enable a Filtered Stream to be sent to EventDB. Follow the guide for Creating a Stream.
  • For AudienceDB, you have to enable each attribute to be sent to AudienceDB. Follow the guide for Adding Attributes to AudienceDB.
    • If you do not complete the above steps, you will see many "visit_" and "visitor_" tables and views, but they will only contain basic visitor level data as well as Audiences. Meaning it will not contains any attribute data.
    • Please note there is a soft limit of 250 attributes in AudienceDB, above which, performance will degrade.