Create SQL transformations on CustomerClickActivities

We'll be doing a series of SQL transformations. Do not apply the SQL transformations until the very end.

1. Click on the Construct SQL rollup and/or transformation button:

Click on construct SQL and/or transfomration

2. Map event_time to _event_time:

Notice that the underscore in the latter is the difference between these two fields.

Best Practice Tip: why should we map a time field to `_event_time`?

Rockset associates a timestamp with each document in a field named _event_time. If an _event_time is not provided when you insert a doc, Rockset provides it as the time the data was ingested. Here, it’s better to map `event_time` to Rockset reserved field, _event_time since queries on this field are significantly faster than similar queries on regularly-indexed fields.

In order to map event_time to _event_time we need to convert theevent_timefield to a timestamp from a string. We can use PARSE_TIMESTAMP_ISO860 function to do this. Below, I show you how to apply this function for our SQL transformation.

Action Item: Delete all the code SQL transformation editor and apply the code snippet below. You'll notice we've applied Lines 1-4:

3. Map id to _id:

Again, notice the difference with the underscore in the latter field. The _id will be updated. This will save on storage because we'll avoid creating a new field, id. The requirement is that _id must return a string data type and be unique across documents.

After we map id to _id, we can add the remaining fields. We don’t need to worry about the session_id because we’ll not be using it in our queries.

Action Item: Delete all the code SQL transformation editor and apply the code snippet below. You'll notice we've applied Lines 3-6:

SQL code

Here's an example of what the SQL preview should look like after we've applied all the transformations:

SQL transformation preview

4. Go ahead and Apply the SQL transformations at the bottom of the SQL transformation page.

5. Keep the collection default settings as is. You can learn more about VI sizing and document retention by clicking on the links.

6. Create the collection at the bottom of the collection page.

NOTE: You can find us on the Rockset Community if you have questions or comments about the workshop.