Recording Properties

Capturing raw, time-series events is great for queries such as "Which users signed in last week?", but queries become more powerful when they can combine common RDBMS queries such as "Which users signed in last week and have a gold level subscription?".

Entities can be anything that has would have a table and row in a traditional RDBMS such as users, devices, sessions, etc.

Appuri has a special event type called @set which takes the key-value pairs in the body of the event and creates database columns:

For example, posting these @set events:

JSON

{
  "ts": "2013-10-24T13:15:35Z",
  "evname": "@set",
  "entype": "user",
  "enid": "be652872",
  "body": {
    "subscription": "gold"
  }
}

{
  "ts": "2014-03-01T09:56:45Z",
  "evname": "@set",
  "entype": "device",
  "enid": "01f7582b",
  "body": {
    "device_type": "ipad"
  }
}

will create _p_user__subscription, _p_history_user__subscription, and _p_device__device_type, _p_history_device__device_type tables (if they are not already created) and load the key-value pairs from the body into columns.

The _p_user__subscription table will look like this:

 user_id  | subscription
----------+--------------
 be652872 | gold

The _p_device__device_type table will look like this:

 device_id | device_type
-----------+-------------
 01f7582b  | ipad

📘

Use snake case instead of camel case

Key names become SQL table column names so it is recommended to use snake_case with all lowercase letters and underscores to separate words. For example, it's better to name an event "logged_in" instead of "loggedIn".

📘

ISO-8601 timestamps are detected automatically

Strings that are formatted as valid ISO 8601 Timestamp will be imported as a SQL TIMESTAMP type.

📘

Numbers are imported as the best SQL type

Numbers will imported as one of INTEGER, BIGINT, REAL, DOUBLE PRECISION or DECIMAL(9,2) depending upon the format of the first type encountered.

Setting different properties on an entity

There may be different processes setting different properties on the entity.

For example, the email address might be set from a database dump of your accounts database, number of purchases might be a batch process that adds up data from log files and the predicted churn might be added by a machine learning algorithm.

Each of these events will add new _p_user__* table if it doesn't exist, or overwrite the value if the timestamp is greater than or equal to the time that property was set:

JSON

{
  "ts": "2013-11-24T13:15:35Z",
  "evname": "@set",
  "entype": "user",
  "enid": "be652872",
  "body": {
    "subscription": "gold",
    "months":12
  }
}

{
  "ts": "2013-11-25T13:15:35Z",
  "evname": "@set",
  "entype": "user",
  "enid": "be652872",
  "body": {
    "age": 42
  }
}

{
  "ts": "2013-12-26T13:15:35Z",
  "evname": "@set",
  "entype": "user",
  "enid": "be652872",
  "body": {
    "email": "[email protected]"
    }
}

{
  "ts": "2013-12-26T13:15:35Z",
  "evname": "@set",
  "entype": "user",
  "enid": "fbe53b25",
  "body": {
    "email": "[email protected]"
  }
}