# Expanded table

The UDP Event store's **`expanded`** table explodes the nested attributes of 1EdTech Caliper events into a tabular format. By decomposing and flattening an event's hierarchical data, the UDP Event store's expanded table makes querying event data far more performant and efficient.

## Features of the "expanded" table <a href="#expandedtable-featuresofthe-expanded-table" id="expandedtable-featuresofthe-expanded-table"></a>

The UDP Event store schema explodes the nested attributes and values of a Caliper event into a single, tabular schema. The UDP Event store also computes a small number of variables that make downstream querying and reporting more convenient.

### Identifiers <a href="#expandedtable-identifiers" id="expandedtable-identifiers"></a>

The `expanded` table presents many identifiers related to an event. The identifiers in the `expanded` table fall into one of two categories.

* **Given identifiers** are the values in the `id` fields of a Caliper event. They are almost always [IRIs](https://en.wikipedia.org/wiki/Internationalized_Resource_Identifier) that use a qualified namespace to communicate a native identifier. These identifiers are represented, unchanged, in `id` columns in the `expanded` table. For example, the `group.id` column in the `expanded` table contains the IRI value found in the `group[id]` location of the Caliper event.
* **Learning tool identifiers**. The UDP is able to use event data to unambiguously identify, generate, or lookup a tool's native identifier. When this is possible, the UDP will store the learning tool's identifiers with column names that communicate to which tool an identifier belongs. For example, the `engage_id` and `sis_id` columns represent identifiers fro Unizin Engage and an SIS, respectively. The values for these identifiers are represented in their native form (not using IRIs). At present, learning tool identifiers are provided for Persons and Course offerings.

To illustrate the different categories of identifiers captured in the UDP Event store, consider a single event from Instructure Canvas. The `actor.id` column value for this event will be the given, fully qualified IRI value in the actor\[id] location (e.g., "urn:instructure:canvas:user:1"). By contrast, the `person.canvas_id` value will be the native, unqualified identifier (e.g., "1").

### Use of the STRUCT data type <a href="#expandedtable-useofthestructdatatype" id="expandedtable-useofthestructdatatype"></a>

The UDP Event store's expanded table is implemented in Google BigQuery, which supports the [STRUCT data type](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#struct_type). A STRUCT data type is a container of ordered fields, each of which is defined by its own data type and a name. The advantage of a STRUCT data type is that it enables you to store multiple attributes related to a single object in a single row.

The UDP Event store's expanded table makes liberal use of the STRUCT data type to represent data points in Caliper events that contain 1 or more other data points. For example, the `actor` attribute in a Caliper event will typically look like this:

```json
{
  "id": "urn:vendor:tool:user:12345",
  "type": "Person"
}
```

In the UDP Event store's expanded table, this data is represented as an actor STRUCT with two fields: `id` and `type`. To query a field in a STRUCTure, you use the dot (`.`) operator, like so:

```sql
SELECT
  actor.id AS actor_id
FROM
  event_store.expanded
WHERE
  event_time >= '2021-01-01'
;
```

### Root nodes <a href="#expandedtable-rootnodes" id="expandedtable-rootnodes"></a>

As a JSON object, a Caliper event will contain a number of root attributes, many of which are nodes for nested attributes and sub-nodes. For example, the following event describes that an instructor has graded a particular student assignment:

```json
{
  "@context": "http://purl.imsglobal.org/ctx/caliper/v1p1",
  "id": "urn:uuid:cc0115bd-da36-450a-87aa-6c29a2f632f7",
  "eventTime": "2020-10-01T00:01:31.480Z",
  "type": "GradeEvent",
  "action": "Graded",
  "actor": {
    "id": "urn:tool:user:1",
    "type": "Person"
  },
  "edApp": {
    "id": "http://learningtool.com/",
    "type": "SoftwareApplication"
  },
  "object": {
    "id": "urn:tool:submission:5",
    "type": "Attempt",
    "assignable": {
      "id": "urn:tool:assignment:2",
      "type": "AssignableDigitalResource"
    },
    "assignee": {
      "id": "urn:tool:user:1",
      "type": "Person"
    }
  }
}
```

In this event, the `object` node contains a mix of attributes and sub-nodes. For every Caliper event, the UDP Event store will represent a certain number of root nodes as STRUCTs. Each of these structs will contain the following attributes:

<table><thead><tr><th width="190">Attribute</th><th>Description</th></tr></thead><tbody><tr><td><code>id</code></td><td>The value of the <code>id</code> attribute in the node.</td></tr><tr><td><code>type</code></td><td>The value of the <code>type</code> attribute in the node.</td></tr><tr><td><code>name</code></td><td>The value of the <code>name</code> attribute in the node.</td></tr><tr><td><code>extensions</code></td><td>The value of the extensions attribute in the node.</td></tr><tr><td><code>json</code></td><td>The node's full JSON.</td></tr></tbody></table>

Given the example above, here is how you might query the Event store for the attributes of the `object` node in Caliper events:

```sql
SELECT
  object.id AS object_id
  , object.extensions as object_extensions
FROM
  event_store.expanded
WHERE
  event_time >= '2021-01-01'
;
```

The following root nodes of Caliper events are:

* actor
* edApp
* extensions
* federatedSession
* generated
* group
* membership
* object
* profile
* referrer
* session
* target

### Sub-nodes <a href="#expandedtable-sub-nodes" id="expandedtable-sub-nodes"></a>

In a Caliper event, the `generated` and `object` root nodes often contain sub-nodes called `assignable`, `assignee`, and `attempt`, which usually refer to an assigned learning activity (`assignable`) and the person who completed or created it (`assignee`).

The UDP Event store will extract these sub-nodes and as attributes of their parent nodes STRUCTs. The sub-nodes are then represented as nested STRUCTs.

Given the example "graded" event above, the following SQL can be used to query the `type` attributes of a nested `assignable` attribute:

```sql
SELECT
  object.assignable.type as object_assignable_type
FROM
  event_store.expanded
WHERE
  event_time >= '2021-01-01'
;
```

## Dates and times <a href="#expandedtable-datesandtimes" id="expandedtable-datesandtimes"></a>

The UDP Event store records when the event was written to the UDP Event store (`store_time`) along with when the behavior occurred (`event_time`) and the date and hour when the behavior occurred (`event_date` and `event_hour`). The **`expanded`** table dates and times are listed in UTC.

## Table partitioning <a href="#expandedtable-tablepartitioning" id="expandedtable-tablepartitioning"></a>

The UDP Event store table is a partitioned table. A partitioned table is divided into segments (called partitions) along a particular column in the table schema. Partitioned tables are more performant and cost-effective if queries on the table use the relevant column in queries on the table.

The UDP Event store table is partitioned on the `event_time` column. Consequently, queries against the UDP Event store that select fixed periods of time to query data (using `event_time`) will be generally more performant and cost less.

## Canvas events data mapping <a href="#expandedtable-canvaseventsdatamapping" id="expandedtable-canvaseventsdatamapping"></a>

Unique to the expanded events table is how the STRUCT data type allows for the normalization of Canvas events data. For further information, please visit our [documentation on Canvas edApp mapping](https://resources.unizin.org/products/data-and-analytics/unizin-data-platform/data-stores/data-lake/udp-event-store/expanded-table/expanded-table-canvas-edapp-mapping).

## Schema <a href="#expandedtable-schema" id="expandedtable-schema"></a>

Each record in the UDP Event store table represents a single, timestamped event.

The UDP Event store table schema enables common event query patterns over sets of Caliper events.

Beyond storing the entirety of a Caliper event payload itself, the UDP Event store schema defines a set of columns for values that are common to query patterns. The values are extracted from the Caliper event payload during the event ETL process. The values include the UDP identifiers that correspond to an event’s native identifiers if the event qualifies for [enrichment](https://resources.unizin.org/products/data-and-analytics/unizin-data-platform/system-overview/event-data-pipeline/udp-event-enricher). For example, values in the actor\_id column of the Event store correspond to the id value in the actor node of the Caliper event.

The following table describes the Event record schema for the UDP Event store.

<table data-full-width="true"><thead><tr><th width="445.13629160063397">Column</th><th>Description</th></tr></thead><tbody><tr><td><code>id</code></td><td>The event UUID is generated by the Caliper sensor that emitted the event or, if no UUID is part of the event, as generated by the UDP Caliper endpoint.</td></tr><tr><td><code>store_time</code></td><td>The date and time when the event record was written to the event store.</td></tr><tr><td><code>event_time</code></td><td>The timestamp representing when the behavior occured.</td></tr><tr><td><code>event_date</code></td><td>The date, in YYYY-MM-DD format, that corresponds to the event_time.</td></tr><tr><td><code>event_hour</code></td><td>The hour, in 0-23 integer format, that corresponds to the event_time.</td></tr><tr><td><code>type</code></td><td>The value of the Caliper 1.1 type, as required by the standard.</td></tr><tr><td><code>action</code></td><td>The value of the Caliper 1.1 action, as required by the standard.</td></tr><tr><td><code>ed_app</code></td><td>A STRUCT containing a number of attributes for the <code>edApp</code> data in the Caliper event.</td></tr><tr><td><code>ed_app.id</code></td><td>The value of the <code>id</code> attribute in the <code>edApp</code> node of the event.</td></tr><tr><td><code>ed_app.type</code></td><td>The value of the <code>type</code> attribute in the <code>edApp</code> node of the event.</td></tr><tr><td><code>ed_app.name</code></td><td>The value of the <code>name</code> attribute in the <code>edApp</code> node of the event.</td></tr><tr><td><code>ed_app.extensions</code></td><td>The JSON blob of the <code>extensions</code> attribute in the <code>edApp</code> node of the event.</td></tr><tr><td><code>ed_app.json</code></td><td>The JSON blob of the <code>edApp</code> node of the event.</td></tr><tr><td><code>actor</code></td><td>A STRUCT containing a number of attributes for the <code>actor</code> data in the Caliper event.</td></tr><tr><td><code>actor.id</code></td><td>The value of the <code>id</code> attribute in the <code>actor</code> of the event.</td></tr><tr><td><code>actor.type</code></td><td>The value of the <code>type</code> attribute in the <code>actor</code> of the event.</td></tr><tr><td><code>actor.name</code></td><td>The value of the <code>name</code> attribute in the <code>actor</code> of the event.</td></tr><tr><td><code>actor.extensions</code></td><td>The JSON blob of the <code>extensions</code> attribute in the <code>actor</code> node of the event.</td></tr><tr><td><code>actor.json</code></td><td>The JSON blob of the <code>actor</code> node of the event.</td></tr><tr><td><code>person</code></td><td>A STRUCT that contains a variety of identifies for the <code>actor</code> in the Caliper event.</td></tr><tr><td><code>person.udp_id</code></td><td>The UDP ID for the Person (actor).</td></tr><tr><td><code>person.canvas_id</code></td><td>The Canvas ID for the Person (actor).</td></tr><tr><td><code>person.roles</code></td><td>The role(s) array of the Person who performed the behavior.</td></tr><tr><td><code>person.canvas_username</code></td><td>The Canvas username for the Person (actor).</td></tr><tr><td><code>person.sis_id</code></td><td>The SIS ID for the Person (actor).</td></tr><tr><td><code>group</code></td><td>A STRUCT containing a number of attributes for the <code>group</code> data in the Caliper event.</td></tr><tr><td><code>group.id</code></td><td>The value of the <code>id</code> attribute in the <code>group</code> of the event.</td></tr><tr><td><code>group.type</code></td><td>The value of the <code>type</code> attribute in the <code>group</code> of the event.</td></tr><tr><td><code>group.name</code></td><td>The value of the <code>name</code> attribute in the <code>group</code> of the event.</td></tr><tr><td><code>group.extensions</code></td><td>The JSON blob of the <code>extensions</code> attribute in the <code>group</code> node of the event.</td></tr><tr><td><code>group.json</code></td><td>The JSON blob of the <code>group</code> node of the event.</td></tr><tr><td><code>course_offering</code></td><td>A STRUCT that contains a variety of identifiers for the <code>courseOffering</code> in the Caliper event.</td></tr><tr><td><code>course_offering.id</code></td><td>The ID of the Course offering, according to the learning tool that emitted the event.</td></tr><tr><td><code>course_offering.udp_id</code></td><td>The UDP ID for the Course offering.</td></tr><tr><td><code>course_offering.canvas_id</code></td><td>The Canvas ID for the Course offering.</td></tr><tr><td><code>membership</code></td><td>A STRUCT containing a number of attributes for the <code>membership</code> data in the Caliper event.</td></tr><tr><td><code>membership.id</code></td><td>The value of the <code>id</code> attribute in the <code>membership</code> of the event.</td></tr><tr><td><code>membership.type</code></td><td>The value of the <code>type</code> attribute in the <code>membership</code> of the event.</td></tr><tr><td><code>membership.name</code></td><td>The value of the <code>name</code> attribute in the <code>membership</code> of the event.</td></tr><tr><td><code>membership.extensions</code></td><td>The JSON blob of the <code>extensions</code> attribute in the <code>membership</code> node of the event.</td></tr><tr><td><code>membership.json</code></td><td>The JSON blob of the <code>membership</code> node of the event.</td></tr><tr><td><code>profile</code></td><td>A STRUCT containing a number of attributes for the <code>profile</code> data in the Caliper event.</td></tr><tr><td><code>profile.id</code></td><td>The value of the <code>id</code> attribute in the <code>profile</code> of the event.</td></tr><tr><td><code>profile.type</code></td><td>The value of the <code>type</code> attribute in the <code>profile</code> of the event.</td></tr><tr><td><code>profile.name</code></td><td>The value of the <code>name</code> attribute in the <code>profile</code> of the event.</td></tr><tr><td><code>profile.extensions</code></td><td>The JSON blob of the <code>extensions</code> attribute in the <code>profile</code> node of the event.</td></tr><tr><td><code>profile.json</code></td><td>The JSON blob of the <code>profile</code> node of the event.</td></tr><tr><td><code>referrer</code></td><td>A STRUCT containing a number of attributes for the <code>referrer</code> data in the Caliper event.</td></tr><tr><td><code>referrer.id</code></td><td>The value of the <code>id</code> attribute in the <code>referrer</code> of the event.</td></tr><tr><td><code>referrer.type</code></td><td>The value of the <code>type</code> attribute in the <code>referrer</code> of the event.</td></tr><tr><td><code>referrer.name</code></td><td>The value of the <code>name</code> attribute in the <code>referrer</code> of the event.</td></tr><tr><td><code>referrer.extensions</code></td><td>The JSON blob of the <code>extensions</code> attribute in the <code>referrer</code> node of the event.</td></tr><tr><td><code>referrer.json</code></td><td>The JSON blob of the <code>referrer</code> node of the event.</td></tr><tr><td><code>session</code></td><td>A STRUCT containing a number of attributes for the <code>session</code> data in the Caliper event.</td></tr><tr><td><code>session.id</code></td><td>The value of the <code>id</code> attribute in the <code>session</code> of the event.</td></tr><tr><td><code>session.type</code></td><td>The value of the <code>type</code> attribute in the <code>session</code> of the event.</td></tr><tr><td><code>session.name</code></td><td>The value of the <code>name</code> attribute in the <code>session</code> of the event.</td></tr><tr><td><code>session.extensions</code></td><td>The JSON blob of the <code>extensions</code> attribute in the <code>session</code> node of the event.</td></tr><tr><td><code>session.json</code></td><td>The JSON blob of the <code>session</code> node of the event.</td></tr><tr><td><code>target</code></td><td>A STRUCT containing a number of attributes for the <code>target</code> data in the Caliper event.</td></tr><tr><td><code>target.id</code></td><td>The value of the <code>id</code> attribute in the <code>target</code> of the event.</td></tr><tr><td><code>target.type</code></td><td>The value of the <code>type</code> attribute in the <code>target</code> of the event.</td></tr><tr><td><code>target.name</code></td><td>The value of the <code>name</code> attribute in the <code>target</code> of the event.</td></tr><tr><td><code>target.extensions</code></td><td>The JSON blob of the <code>extensions</code> attribute in the <code>target</code> node of the event.</td></tr><tr><td><code>target.json</code></td><td>The JSON blob of the <code>target</code> node of the event.</td></tr><tr><td><code>generated</code></td><td>A STRUCT containing a number of attributes for the <code>generated</code> node in the Caliper event.</td></tr><tr><td><code>generated.id</code></td><td>The value of the <code>id</code> attribute in the <code>generated</code> node of the event.</td></tr><tr><td><code>generated.type</code></td><td>The value of the <code>type</code> attribute in the <code>generated</code> node of the event.</td></tr><tr><td><code>generated.name</code></td><td>The value of the <code>name</code> attribute in the <code>generated</code> node of the event.</td></tr><tr><td><code>generated.extensions</code></td><td>The JSON blob of the <code>extensions</code> attribute in the <code>generated</code> node of the event.</td></tr><tr><td><code>generated.json</code></td><td>The JSON blob of the <code>generated</code> node of the event.</td></tr><tr><td><code>generated.assignable</code></td><td>A STRUCT containing a number of attributes for the <code>generated.assignable</code> data in the Caliper event.</td></tr><tr><td><code>generated.assignable.id</code></td><td>The value of the <code>id</code> attribute in the <code>generated.assignable</code> node of the event.</td></tr><tr><td><code>generated.assignable.type</code></td><td>The value of the <code>type</code> attribute in the <code>generated.assignable</code> node of the event.</td></tr><tr><td><code>generated.assignable.name</code></td><td>The value of the <code>name</code> attribute in the <code>generated.assignable</code> node of the event.</td></tr><tr><td><code>generated.assignable.extensions</code></td><td>The JSON blob of the <code>extensions</code> attribute in the <code>generated.assignable</code> node of the event.</td></tr><tr><td><code>generated.assignable.json</code></td><td>The JSON blob of the <code>generated.assignable</code> node of the event.</td></tr><tr><td><code>generated.assignee</code></td><td>A STRUCT containing a number of attributes for the <code>generated.assignee</code> data in the Caliper event.</td></tr><tr><td><code>generated.assignee.id</code></td><td>The value of the <code>id</code> attribute in the <code>generated.assignee</code> node of the event.</td></tr><tr><td><code>generated.assignee.type</code></td><td>The value of the <code>type</code> attribute in the <code>generated.assignee</code> node of the event.</td></tr><tr><td><code>generated.assignee.name</code></td><td>The value of the <code>name</code> attribute in the <code>generated.assignee</code> node of the event.</td></tr><tr><td><code>generated.assignee.extensions</code></td><td>The JSON blob of the <code>extensions</code> attribute in the <code>generated.assignee</code> node of the event.</td></tr><tr><td><code>generated.assignee.json</code></td><td>The JSON blob of the <code>generated.assignee</code> node of the event.</td></tr><tr><td><code>generated.attempt.assignable</code></td><td>A STRUCT containing a number of attributes for the <code>generated.assignable</code> data in the Caliper event.</td></tr><tr><td><code>generated.attempt.assignable.id</code></td><td>The value of the <code>id</code> attribute in the <code>generated.attempt.assignable</code> node of the event.</td></tr><tr><td><code>generated.attempt.assignable.type</code></td><td>The value of the <code>type</code> attribute in the <code>generated.attempt.assignable</code> node of the event.</td></tr><tr><td><code>generated.attempt.assignable.name</code></td><td>The value of the <code>name</code> attribute in the <code>generated.attempt.assignable</code> node of the event.</td></tr><tr><td><code>generated.attempt.assignable.extensions</code></td><td>The JSON blob of the <code>extensions</code> attribute in the <code>generated.attempt.assignable</code> node of the event.</td></tr><tr><td><code>generated.attempt.assignable.json</code></td><td>The JSON blob of the <code>generated.attempt.assignable</code> node of the event.</td></tr><tr><td><code>generated.attempt.assignee</code></td><td>A STRUCT containing a number of attributes for the <code>generated.attempt.assignee</code> data in the Caliper event.</td></tr><tr><td><code>generated.attempt.assignee.id</code></td><td>The value of the <code>id</code> attribute in the <code>generated.attempt.assignee</code> node of the event.</td></tr><tr><td><code>generated.attempt.assignee.type</code></td><td>The value of the <code>type</code> attribute in the <code>generated.attempt.assignee</code> node of the event.</td></tr><tr><td><code>generated.attempt.assignee.name</code></td><td>The value of the <code>name</code> attribute in the <code>generated.attempt.assignee</code> node of the event.</td></tr><tr><td><code>generated.attempt.assignee.extensions</code></td><td>The JSON blob of the <code>extensions</code> attribute in the <code>generated.attempt.assignee</code> node of the event.</td></tr><tr><td><code>generated.attempt.assignee.json</code></td><td>The JSON blob of the <code>generated.attempt.assignee</code> node of the event.</td></tr><tr><td><code>object</code></td><td>A STRUCT containing a number of attributes for the <code>object</code> node in the Caliper event.</td></tr><tr><td><code>object.id</code></td><td>The value of the <code>id</code> attribute in the <code>object</code> node of the event.</td></tr><tr><td><code>object.type</code></td><td>The value of the <code>type</code> attribute in the <code>object</code> node of the event.</td></tr><tr><td><code>object.name</code></td><td>The value of the <code>name</code> attribute in the <code>object</code> node of the event.</td></tr><tr><td><code>object.extensions</code></td><td>The JSON blob of the <code>extensions</code> attribute in the <code>object</code> node of the event.</td></tr><tr><td><code>object.json</code></td><td>The JSON blob of the <code>object</code> node of the event.</td></tr><tr><td><code>object.assignable</code></td><td>A STRUCT containing a number of attributes for the <code>object.assignable</code> node in the Caliper event.</td></tr><tr><td><code>object.assignable.id</code></td><td>The value of the <code>id</code> attribute in the <code>object.assignable</code> node of the event.</td></tr><tr><td><code>object_assignable.type</code></td><td>The value of the <code>type</code> attribute in the <code>object.assignable</code> node of the event.</td></tr><tr><td><code>object_assignable.name</code></td><td>The value of the <code>name</code> attribute in the <code>object.assignable</code> node of the event.</td></tr><tr><td><code>object_assignable.extensions</code></td><td>The JSON blob of the <code>extensions</code> attribute in the <code>object.assignable</code> node of the event.</td></tr><tr><td><code>object_assignable.json</code></td><td>The JSON blob of the <code>object.assignable</code> node of the event.</td></tr><tr><td><code>object.assignee</code></td><td>A STRUCT containing a number of attributes for the <code>object.assignee</code> node in the Caliper event.</td></tr><tr><td><code>object.assignee.id</code></td><td>The value of the <code>id</code> attribute in the <code>object.assignable</code> node of the event.</td></tr><tr><td><code>object.assignee.type</code></td><td>The value of the <code>type</code> attribute in the <code>object.assignable</code> node of the event.</td></tr><tr><td><code>object.assignee.name</code></td><td>The value of the <code>name</code> attribute in the <code>object.assignable</code> node of the event.</td></tr><tr><td><code>object.assignee.extensions</code></td><td>The JSON blob of the <code>extensions</code> attribute in the <code>object.assignable</code> node of the event.</td></tr><tr><td><code>object.assignee.json</code></td><td>The JSON blob of the <code>object.assignable</code> node of the event.</td></tr><tr><td><code>extensions_json</code></td><td>The JSON blob of the <code>extensions</code> node of the event.</td></tr><tr><td><code>client_ip</code></td><td>The IP address reported in the event. This usually refers to the public IP addresses of the individual who performed the event.</td></tr><tr><td><code>federated_session_json</code></td><td>The JSON blob of the <code>federated_session</code> node of the event.</td></tr></tbody></table>