Recording Events

Raw Events Table

Appuri loads the raw events posted to the HTTPS endpoint into the data warehouse in a table called _events. This table has all time series events and associated metadata in their body.

For example, posting these two events:

{
  "ts": "2013-10-24T13:15:30Z",
  "entype": "user",
  "evname": "purchased",
  "enid": "be652872",
  "body": {
    "item": 57,
    "price": 123.45
  }
}

 {
   "ts": "2014-03-01T09:56:44.682Z",
   "evname": "app_launched",
   "entype": "device",
   "enid": "01f7582b",
   "body": {
     "app_version": "1.2"
   }
 }

will load the _events table with this time-series data:

           ts            |    evname    | entype  |   enid   |           body
-------------------------+--------------+---------+----------+---------------------------------------------
 2013-10-24 13:15:30     | purchased    | user    | be652872 | { "item": 57, "price": 123.45 }
 2014-03-01 09:56:44.682 | app_launched | device  | 01f7582b | { "app_version": "1.2" }

Note: The body is a VARCHAR in the data warehouse. You can access the internal key-value pairs via Redshift JSON Functions.

Individual Event Tables

To make time-series queries easier, each event in the raw _events table is expanded into its own table and the parameters in the body become columns.

For example, these two events:

{
  "ts": "2013-10-24T13:15:30Z",
  "evname": "purchased",
  "entype": "user",
  "enid": "be652872",
  "body": {
    "item": 57,
    "price": 123.45
  }
}

{
  "ts": "2014-03-01T09:56:44.682Z",
  "evname": "app_launched",
  "entype": "device",
  "enid": "01f7582b",
  "body": {
    "app_version": "1.2"
  }
}

Will become a raw event in the _events table:

           ts            |    evname    | entype  |   enid   |           body
-------------------------+--------------+---------+----------+---------------------------------------------
 2013-10-24 13:15:30     | purchased    | user    | be652872 | { "item": 57, "price": 123.45 }
 2014-03-01 09:56:44.682 | app_launched | device  | 01f7582b | { "app_version": "1.2" }

This raw event will get expanded into its own event table given the key/value pairs in the body:

select * from _user__purchased;

           ts        | user_id  | item | price
---------------------+----------+------+--------
 2013-10-24 13:15:30 | be652872 |  57  | 123.45

select * from _device__app_launched;

             ts          | device_id | app_version
-------------------------+-----------+-------------
 2014-03-01 09:56:44.682 | 01f7582b  |    1.2

Note: The name of the event table is the combination of the _ + entype + __ + evname.

Note: The column table type is set with the format of the first type encountered. To change this type after the initial load, the database table will have to be changed manually via ALTER TABLE (see Amazon Redshift SQL Commands).