About Redshift Database Access

Last updated: 2020-06-10 Contributors Craig Chan

When accessing the Redshift Historical Database you will be provided with a username, password, schema name and JDBC connection URL. The credentials will only enable a user to read/query from the database. There are also limitations as to the number of connections (5) and time that a query can run (5 minutes) before they are refused or terminated respectfully.

With the aforementioned credentials you can create a connection to the historical database with your choice of client. This client will need to support either a Postgres or preferably the Redshift JDBC driver.

The Redshift JDBC driver and generic connection information can be found here.

The AWS tutorial and help pages for Redshift can be found here.

Once connected the user will have read permissions to all schemas on the database. However, you will only be able to query your provided schema. Underneath each schema is a Redshift View. These views mirror all of the tables in the production schema and contain all of the same columns.

The following query is useful as it will provide you with a list of views that your user has access to.

SELECT
table_schema as schema_name,
table_name as view_name
FROM information_schema.views
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY schema_name, view_name;

Views (Tables)

You can also view this through each client GUI. The _XXXXXXXX schema names are visible and from there you can list the views under each schema. The available views are listed below.

  • agent_block_monitor
  • agent_login_gates
  • agent_logins
  • agent_states
  • call_events
  • call_sessions
  • calls
  • dnc_list
  • internal_chats
  • international_calls
  • reverse_match_results
  • scripting_studio_result
  • survey_response_details (Legacy Surveys - no longer supported)
  • survey_responses (Legacy Surveys - no longer supported)
  • visual_ivr_results
  • whitelist_entries

From the list of available views, there are 4 main tables that are generally of most interest when creating KPIs.

  • calls
  • call_sessions
  • agent_logins
  • agent_states

Tables

A brief description of the four core tables are below.

CALLS

By far the largest table with the most information. It is also the largest table in terms of data stored and number of columns, which as of this documentation is 165. This creates a challenge at times when querying because you must be very specific about the information that you want to return or the performance of your query will suffer.

This Calls table contains a unique record per call. In most scenarios this will mean one record per uii. A uii is a unique 30 character call identifier with the first 14 digits representing the time that the call initiated on the Engage Voice platform. An example uii might look like this.

UII - 202001010011550131070000000400

We also use a shortened version of the uii which is titled the uii_table_key. The uii_table_key is 28 characters in length. This allowed us to change the data type from a varchar to a bigint which enabled slightly faster query performance while searching for specific uiis. Using the example uii above the uii_table_key would be…

UII_TABLE_KEY - 2001010011550131070000000400

The only difference being the first two characters have been removed.

On the calls table and any other table related to call data, we use the uii_table_key as a distribution key. This is because the uii_table_key has a very high cardinality. We also create a compound sort key with uii_table_key and account_id. This keeps the data organized chronologically and makes for faster querying. A table with the sort and distribution keys is located at the bottom of this document.

The Calls table contains information on any product ID the call was associated with. These columns are gate_id, campaign_id, visual_ivr_id, trac_id, cloud_destination_id, etc. The products all have a product name column, that may make them easier to identify. An example is “campaign_name” or “gate_name”. The majority of calls will be related to either Gates (now titled as Queues in the application), Campaigns, or IVRs. It can be the case that a call contains more than one product, if that is the case all products related to that call will populate the respective column with a product_id and product name. These can be used to group calls by products, search for calls on a specific product, etc. Lastly, the calls table also contains basic information as of the final call state and agent information who may have handled the call. This includes agent_id, agent_username, agent_login_id, and agent_disposition.

CALL_SESSIONS

The call sessions table is similar to the calls table however each record is broken down by session_id. Most calls will have more than one session_id, one for each leg of the call. Records with a session_id of 1 can be thought of as the entire encapsulated call. All subsequent session_ids are an individual leg of the call. For example, an agent may dial out to a lead, then once connected the agent may determine that another agent might be better able to handle that call and then transfer to an inbound Queue (gate_id). The call would have three sessions, one for the entire call, one for the outbound leg to the lead, and one on the transfer to the Queue. The call_sessions table also uses the uii_table_key and account_id distribution and sort keys.

AGENT_LOGINS

The agent_logins table gives a summed up version of specific agent login sessions. A login session is the time period between when an agent logs into the platform and when they logout. This table contains statistics about the specific state duration for each agent session, as well as metadata related to each agent like username, agent_id, agent_group_id, agent_name, agent_team, etc. Each agent login session is given an agent_login_id which is a unique identifier that can be filtered by to determine the number of calls that an agent fielded in one login session. There are several timestamps on this table including login_dts, and logout_dts. The agent_logins table is distributed evenly across all nodes in the Redshift cluster so there is not a distribution key, however there is a compound sort key that utilizes the login_dts, and account_id columns.

AGENT_STATES

The agent_states table is a detailed breakdown of agent state information. There are only 13 columns in the table. All records are sorted by account_id and agent_login_id with agent_login_id being the distribution key for the table. This table contains state_label which is the custom label assigned to a base working state if any. It also contains the base_state and the duration in seconds for how long the agent was in that particular state. It also contains the uii_table_key for any call that may have been tied to that state.

There are also two columns on each table that are titled etl_job_timestamp and etl_job_guid. These are internal columns used to track the progress of data loading into the Redshift cluster and can be ignored.

Sample Queries

We highly advise against writing any SELECT * query especially against the CALLS table. It is not performant and may cause us to cancel any long running query.

GENERIC CALLS SELECT

SELECT
uii_table_key,
account_id,
call_timestamp,
enqueue_time,
dequeue_time,
dequeue_attempts,
dial_type,
call_type,
agent_notes,
gate_id,
gate_name,
is_presented,
is_accepted,
agent_disposition,
campaign_id,
campaign_name,
visual_ivr_id,
visual_ivr_name,
lead_id,
list_id,
pass_number,
agent_login_id,
agent_id
FROM _xxxxxxxx.calls
WHERE account_id = xxxxxxxx AND uii_table_key BETWEEN 2001010011550131070000000400 AND 2001020011550131070000000400;

GENERIC AGENT LOGINS

SELECT
account_id,
agent_id,
agent_login_id,
username,
last_name,
fist_name,
email,
login_dts,
logout_dts,
login_time,
available_time,
engaged_time,
lunch_time,
work_time,
train_time,
rna_time,
pending_disp_time,
agent_phone,
team
FROM _xxxxxxxx.agent_logins
WHERE account_id = xxxxxxxx AND login_dts BETWEEN 2020-01-01 00:00:00 AND 2020-01-02 00:00:00;

Sort and Distribution Keys

The table below contains a list of distribution and sort keys for each table. If the distkey column is true, then that column is the distribution key for the table. For the sort key column, the number corresponds to the position of the column in the compound sort. We encourage you to use both the distribution key and sort keys when writing queries. To do this include them in your WHERE clause and include them on any joins.

Table Column Data Type Distribution Key Sort Key
agent_block_monitor agent_login_id bigint false 2
agent_login_gates agent_login_id bigint false 2
agent_states agent_login_id bigint true 1
internal_chats log_id bigint true 0
agent_block_monitor account_id integer false 1
agent_login_gates account_id integer false 1
agent_logins account_id integer false 2
agent_states account_id integer false 2
call_events account_id integer false 1
call_sessions account_id integer false 2
calls account_id integer false 2
chats account_id integer false 1
dnc_list account_id integer false 1
internal_chats account_id integer false 2
international_calls account_id integer false 2
reverse_match_results account_id integer false 2
scripting_studio_result account_id integer false 2
survey_response_details account_id integer false 2
survey_response_details response_id integer true 1
survey_responses account_id integer false 2
visual_ivr_results account_id integer false 2
whitelist_entries account_id integer false 1
call_events uii_table_key numeric(28,0) true 2
call_sessions uii_table_key numeric(28,0) true 1
calls uii_table_key numeric(28,0) true 1
chats uii_table_key numeric(28,0) true 2
international_calls uii_table_key numeric(28,0) true 1
reverse_match_results uii_table_key numeric(28,0) true 1
scripting_studio_result uii_table_key numeric(28,0) true 1
survey_responses uii_table_key numeric(28,0) true 1
visual_ivr_results uii_table_key numeric(28,0) true 1
agent_logins login_dts timestamp without time zone false 1
dnc_list added_date timestamp without time zone false 2
internal_chats message_dts timestamp without time zone false 1
whitelist_entries added_dts timestamp without time zone false 2

Table Descriptions

Below are tables containing the columns their position in the tables they belong to. They also list their data type, max length, and whether that column is nullable or not.

CALLS

Position Column Data Type Max Length Nullable
1 uii_table_key numeric 28 NO
2 uii character 30 NO
3 account_id integer 32 NO
4 account_name character varying 126 YES
5 call_timestamp timestamp without time zone YES
6 call_date date YES
7 visual_ivr_id integer 32 YES
8 trac_id integer 32 YES
9 cloud_profile_id integer 32 YES
10 campaign_id integer 32 YES
11 campaign_name character varying 150 YES
12 campaign_desc character varying 255 YES
13 gate_id integer 32 YES
14 gate_name character varying 100 YES
15 gate_desc character varying 200 YES
16 source_type character 20 YES
17 lead_id integer 32 YES
18 outbound_disposition character 25 YES
19 source_app_id character varying 20 YES
20 ani character varying 20 YES
21 dnis character varying 20 YES
22 originating_dnis character varying 20 YES
23 aux_phone character varying 30 YES
24 enqueue_time timestamp without time zone YES
25 dequeue_time timestamp without time zone YES
26 call_state character 20 YES
27 priority_queue_event character varying 255 YES
28 skill_override character varying 255 YES
29 next_dequeue_time timestamp without time zone YES
30 dequeue_attempts integer 32 YES
31 curr_dequeue_event_id integer 32 YES
32 next_session_id integer 32 YES
33 recording_deleted integer 32 YES
34 opt_out boolean YES
35 sla_qualified boolean YES
36 sla_passed boolean YES
37 is_short_call boolean YES
38 is_long_call boolean YES
39 is_short_abandon boolean YES
40 dial_type integer 32 YES
41 call_type character 20 YES
42 agent_notes character varying 65535 YES
43 pci_compliant boolean YES
44 recording_url character varying 555 YES
45 call_result character 20 YES
46 is_presented boolean YES
47 agent_id integer 32 YES
48 agent_first_name character varying 160 YES
49 agent_last_name character varying 160 YES
50 agent_username character varying 260 YES
51 agent_externid character varying 255 YES
52 is_agent_disposition_complete boolean YES
53 gate_group_id integer 32 YES
54 group_name character varying 100 YES
55 trac_description character varying 255 YES
56 trac_rule_destination character varying 255 YES
57 callback_requested boolean YES
58 is_callback boolean YES
59 callback_uii character varying 30 YES
60 callback_result character varying 255 YES
61 profile_name character varying 255 YES
62 profile_desc character varying 255 YES
63 cloud_destination_id integer 32 YES
64 destination_name character varying 255 YES
65 destination_desc character varying 255 YES
66 media_isci character varying 255 YES
67 media_network character varying 255 YES
68 media_market character varying 255 YES
69 media_code character varying 255 YES
70 media_format character varying 255 YES
71 media_version character varying 255 YES
72 media_length character varying 255 YES
73 sla_time integer 32 YES
74 etl_job_timestamp timestamp without time zone YES
75 etl_job_guid character varying 256 YES
76 latitude numeric 10 YES
77 longitude numeric 10 YES
78 ani_country_code character 5 YES
79 ani_state character 5 YES
80 agent_disposition character varying 180 YES
81 outbound_externid character varying 180 YES
82 main_account_name character varying 125 YES
83 main_account_id integer 32 YES
84 ani_is_cell smallint 16 YES
85 trac_open_rule boolean YES
86 trac_locater_description character varying 255 YES
87 trac_location_description character varying 255 YES
88 ani_city character varying 264 YES
89 trac_dest_description character varying 255 YES
90 dial_group_id integer 32 YES
91 dial_group_name character varying 125 YES
92 dial_group_desc character varying 125 YES
93 billing_code character varying 256 YES
94 dnis_description character varying 256 YES
95 caller_id character varying 20 YES
96 live_answer_message character varying 256 YES
97 mach_answer_message character varying 256 YES
98 list_id bigint 64 YES
99 pass_number integer 32 YES
100 is_complete smallint 16 YES
101 uploaded_list_description character varying 400 YES
102 reserved_agent_id integer 32 YES
103 lead_state character varying 50 YES
104 next_dial_time timestamp without time zone YES
105 lead_timezone character varying 5 YES
106 title character varying 100 YES
107 mid_name character varying 100 YES
108 suffix character varying 100 YES
109 city character varying 50 YES
110 state character varying 50 YES
111 zip character varying 50 YES
112 gate_keeper character varying 325 YES
113 email character varying 325 YES
114 aux_data1 character varying 325 YES
115 aux_data2 character varying 325 YES
116 aux_data3 character varying 325 YES
117 aux_data4 character varying 325 YES
118 aux_data5 character varying 325 YES
119 aux_external_url character varying 325 YES
120 aux_greeting character varying 65535 YES
121 loaded_dts timestamp without time zone YES
122 list_desc character varying 400 YES
123 reserved_agent_firstname character varying 100 YES
124 reserved_agent_lastname character varying 100 YES
125 reserved_agent_username character varying 200 YES
126 address1 character varying 325 YES
127 address2 character varying 325 YES
128 first_name character varying 200 YES
129 last_name character varying 200 YES
130 is_contact boolean YES
131 is_success boolean YES
132 is_xfer boolean YES
133 xfer_dest character varying 50 YES
134 dial_mode character varying 30 YES
135 country_id character 10 YES
136 country_name character varying 75 YES
137 country_code character 10 YES
138 list_state character varying 265 YES
139 int_cost numeric 14 YES
140 international_prefix character varying 30 YES
141 international_destination character varying 256 YES
142 speed_to_lead_first_pass integer 32 YES
143 speed_to_lead_agent_conn integer 32 YES
144 external_agent_id character varying 284 YES
145 agent_login_id bigint 64 YES
146 session_id smallint 16 YES
147 dial_duration integer 32 YES
148 is_abandoned boolean YES
149 is_accepted boolean YES
150 is_deflected boolean YES
151 is_manual_no_connect boolean YES
152 exclude_abandon smallint 16 YES
153 agent_group_id integer 32 YES
154 agent_group_name character varying 255 YES
155 script_group_id integer 32 YES
156 script_group_name character varying 255 YES
157 track_group_id integer 32 YES
158 track_group_name character varying 255 YES
159 visual_ivr_group_id integer 32 YES
160 visual_ivr_group_name character varying 255 YES
161 cloud_group_id integer 32 YES
162 cloud_group_name character varying 255 YES
163 lead_extra_data character varying 65535 YES
164 pass_disposition character varying 30 YES
165 outbound_disposition_tracker character varying 30 YES

CALL SESSIONS

Position Column Data Type Max Length Nullable
1 uii_table_key numeric 28 NO
2 account_id integer 32 NO
3 uii character 30 NO
4 the_date timestamp without time zone YES
5 session_id smallint 16 NO
6 session_type character varying 20 YES
7 recording_url character varying 255 YES
8 is_monitoring integer 32 YES
9 on_hold integer 32 YES
10 call_dts timestamp without time zone YES
11 duration integer 32 YES
12 term_party character varying 140 YES
13 term_reason character varying 130 YES
14 agent_login_id bigint 64 YES
15 cloud_destination_id integer 32 YES
16 is_dequeue_agent smallint 16 YES
17 transfer_flag integer 32 YES
18 skill_profile_id integer 32 YES
19 dial_dts timestamp without time zone YES
20 dial_duration integer 32 YES
21 dial_disposition character varying 50 YES
22 carrier character varying 50 YES
23 wrap_time integer 32 YES
24 gate_id integer 32 YES
25 gate_name character varying 150 YES
26 agent_id integer 32 YES
27 first_name character varying 150 YES
28 last_name character varying 150 YES
29 username character varying 270 YES
30 team character varying 150 YES
31 dial_result character varying 20 YES
32 source_type character 10 YES
33 source_id bigint 64 YES
34 source_name character varying 255 YES
35 etl_job_timestamp timestamp without time zone YES
36 etl_job_guid character 38 YES
37 agent_notes character varying 65535 YES
38 agent_disposition character varying 150 YES
39 phone character varying 90 YES
40 cloud_destination_name character varying 312 YES
41 cloud_destination_desc character varying 312 YES
42 wait_time integer 32 YES

AGENT LOGINS

Position Column Data Type Max Length Nullable
1 account_id integer 32 NO
2 agent_login_id bigint 64 NO
3 agent_id integer 32 YES
4 parent_agent_id integer 32 YES
5 skill_profile_id integer 32 YES
6 login_dial_group_id integer 32 YES
7 monitor_agent_id integer 32 YES
8 agent_team character varying 65 YES
9 first_name character varying 65 YES
10 last_name character varying 65 YES
11 username character varying 65 YES
12 email character varying 150 YES
13 team character varying 65 YES
14 agent_name character varying 65 YES
15 external_agent_id character varying 65 YES
16 agent_phone character varying 255 YES
17 agent_state character varying 32 YES
18 agent_aux_state character varying 50 YES
19 agent_login_type character varying 16 YES
20 state_dts timestamp without time zone YES
21 login_dts timestamp without time zone NO
22 logout_dts timestamp without time zone YES
23 login_time bigint 64 YES
24 calls_handled integer 32 YES
25 preview_dials integer 32 YES
26 manual_dials integer 32 YES
27 rna_count integer 32 YES
28 reserved_uii character varying 30 YES
29 reserved_dts timestamp without time zone YES
30 last_call_dts timestamp without time zone YES
31 next_call_time timestamp without time zone YES
32 pending_disposition smallint 16 YES
33 pending_disp_time integer 32 YES
34 ip_map_key character varying 32 YES
35 current_chat_count integer 32 YES
36 last_archive_state_dts timestamp without time zone YES
37 agent_rank integer 32 YES
38 available_time integer 32 YES
39 ring_time integer 32 YES
40 engaged_time integer 32 YES
41 hold_time integer 32 YES
42 break_time integer 32 YES
43 away_time integer 32 YES
44 lunch_time integer 32 YES
45 train_time integer 32 YES
46 rna_time integer 32 YES
47 work_time integer 32 YES
48 dial_group_id integer 32 YES
49 dial_group_name character varying 100 YES
50 off_hook_time numeric 20 YES
51 etl_job_timestamp timestamp without time zone YES
52 etl_job_guid character varying 36 YES
53 rounded_off_hook_time numeric 20 YES
54 account_name character varying 65 YES
55 location character varying 48 YES
56 main_account_name character varying 65 YES
57 main_account_id character varying 8 YES
58 agent_group_id integer 32 YES
59 agent_group_name character varying 256 YES
60 chat_engaged_time integer 32 YES
61 chat_rna_time integer 32 YES
62 chat_available_time integer 32 YES
63 chat_presented_time integer 32 YES

AGENT STATES

Position Column Data Type Max Length Nullable
1 account_id integer 32 NO
2 agent_login_id bigint 64 NO
3 state_dts timestamp without time zone YES
4 base_state character varying 24 YES
5 state_label character varying 50 YES
6 duration integer 32 YES
7 etl_job_timestamp timestamp without time zone YES
8 etl_job_guid character varying 36 YES
9 pending_disp boolean YES
10 uii character 30 YES
11 uii_table_key numeric 28 YES
12 chat_base_state character varying 50 YES
13 chat_duration integer 32 YES