How to run queries on AudienceDB/EventDB using SQL Workbench/J

How to run queries on AudienceDB/EventDB using SQL Workbench/J

by on ‎11-30-2015 02:21 PM - edited a month ago by (714 Views)

Overview

The goal of this document is to teach you how to create SQL queries for either EventDB and/or AudienceDB using the program SQL Workbench/J.

Some Background

You've created your connection profile for SQL Workbench/J and are ready to start querying data, but aren't really sure where to start, what your data columns are named, or what the Table and List schema represent. This document will help guide you in the right direction.

SQL Workbench/J

Statements

This tab is where you write SQL statements, view your results, or view any error messages.

Statement Tab.png

 

Database Explorer

The Database Explorer is an excellent tool to review for understanding what data is available.

First, the left panel states the Tables and Views accessible within your Redshift instance.

Tables and Views.png

A table is a standard method of storing data in a Database. Any "NAME" not ending with "_view" is considered a table. The number of tables depends on the number of filtered streams that have been enabled for EventDB and if AudienceDB is enabled.

 

For each table there is a correspnding view. A view is provided for a more human-readable representation of the table's columns. Examples:

  • a table will display "tags_main_1_executed" whereas a view will display "event - tags - tealium collect (main 1) - executed"
  • a table will display "udo_job_role" whereas a view will display "event - udo - job_role"

In regard to EventDB

  • by default there is the “events__all_events” table that holds all events
  • then each filtered stream will have a table such as “events__f84fc357_4ded_413d_d28a_de70624ff2d5”

In regard to 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 those visitor profiles which have been stitched
  • visitors contains the latest visitor scoped data for each visitor, any Audiences they belong to, and will be the most commonly used table
  • visits contains the latest current visitor scoped data for each visitor

Note, list and tally attributes are stored in their respective tables, whereas funnels and timelines are not yet supported.

 

Second, each table or view contains different pieces of data. In order to build your query in the Statement tab correctly, you need to know the data column names that are available to be queried, example udo_job_role. The right pane will help you navigate those data column names and build your queries.

 

Columns

Objects>Columns will provide the column names for each table or view.

Data Schema.png

By default, the COLUMN_NAME is not in alphapetical order. Click the header row to sort the names alphabetically.

 

Data

Objects>Data will pull a random sample of data.

Sample Data.png

 

Troubleshooting

  • Not declaring UDO variables within utag_data in the Data Layer tab in Tealium iQ. If the variables are not declared, they will not show within EventDB.
  • Not enabliing 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.

Conclusion

A data scientist or database administrator can help guide you in the right direction for querying this data. If there are any other questions please contact your Account Manager. However, please know that beyond connection and data schema support, there will be limited support that Tealium will provide. Anything regarding querying support, data export, integration into BI tools, and more will need to be handled by your internal team or an agency.