Back

This document describes how to create SQL queries for EventDB and/or AudienceDB using the program SQL Workbench/J.

In this Article

Table of Contents Placeholder

Getting Started

After you create your connection profile for SQL Workbench/J, you are ready to start querying data. Follow this document to get started. First, learn your data column names and what your tables and list schema represent.

For additional questions regarding connection and data schema support, contact your Account Manager. If you need additional help regarding query support, data export, or integration into Business Intelligence (BI) tools, reach out to a member of your team, such as a data scientist or database administrator to guide you in the right direction.

Using the SQL Workbench/J Interface

The following sections provide general information about the most commonly used screens in the SQL Workbench/J interface.

Statements

From the SQL Workbench/J interface, click the Statements tab to write SQL statements and view results or error messages.

WhiteUI_DataAccess_Running Queries on AudienceDB_Event DB Using SQL Workbench:J_Statement Editor.jpg

Database Explorer

From the SQL Workbench/J interface, go to Database Explorer to review and understand the your available data. 

WhiteUI_DataAccess_Running Queries on AudienceDB_EventDB Using SQL WorkbenchJ.jpg

The left panel displays the tables and views that are accessible within your Redshift instance. Each table or view contains different pieces of data. In order to build your query in the Statement tab correctly, you must know the data column names that are available to be queried, such as udo_job_role. The right pane helps you navigate the data column names and build your queries.

  • Table
    A table is a standard method of storing data in a database. Items in the "NAME" column that do not end with _view are considered tables. The number of tables depends on the number of filtered streams enabled for EventDB and AudienceDB (if enabled).
  • View
    For each table, there is a corresponding view. A view is provided for a more human-readable representation of the table columns.
  • Examples
    • A table displays tags_main_1_executed whereas a view displays event - tags - tealium collect (main 1) - executed
    • A table displays udo_job_role whereas a view displays event - udo - job_role
  • Using with EventDB
    • The events__all_events table holds all events by default
    • Each filtered stream then has a table, such as events__f84fc357_4ded_413d_d28a_de70624ff2d5
  • Using with AudienceDB
    • visit_lists contains the current visit-scoped list attributes for querying
    • visit_tallies contains the current visit-scoped tally attributes for querying
    • visitor_lists contains the visitor-scoped list attributes for querying
    • visitor_tallies contains the visitor-scoped tally attributes for querying
    • visitor_replaces contains visitor profiles that have been stitched
    • visitors is the most commonly-used table and contains the latest visitor-scoped data for each visitor and any audiences that they belong to
    • visits contains the latest current visitor-scoped data for each visitor
  • Limitations
    • List and tally attributes are stored in their respective tables, whereas funnels and timelines are not yet supported.

Viewing Column Names

Click the Objects tab to view the column names for each table or view. You can use the available filters in the interface to fine-tune your results. By default, COLUMN_NAME is not in alphabetical order. Click the header row to sort the names alphabetically.

WhiteUI_DataAccess_Running Queries on AudenceDB_EventDB Using SQL WorkbenchJ_Search Tables Objects Tab.jpg

Viewing Random Sample Data

Click the Search table data tab to view random samples of data. You can use the available filters in the interface to fine-tune your results.

WhiteUI_DataAccess_Running Queries on AudenceDB_EventDB Using SQL WorkbenchJ_Search Table Data.jpg

Common Errors to Avoid

The following items are noted as common errors to avoid:

  • Not declaring UDO variables within utag_data in your iQ Tag Management (TiQ) data layer. If the variables are not declared, they will not display in EventDB.
  • Not enabling an AudienceStream attribute to be sent to AudienceDB. Each attribute must be manually selected. There is a soft limit of 250 attributes. If more columns are needed, contact your Account Manager.