Create SQL transformations on CustomerInformation

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

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

2. We'll convert the times, event_time and modified_at to a timestamp object:

Similar to what we did before, weโ€™re going to convert event_time to a timestamp object and remap it to _event_time(do not type this in the SQL transformation editor):

EXAMPLE:

PARSE_TIMESTAMP_ISO8601(event_time)as _event_time

PARSE_TIMESTAMP_ISO8601(modified_at) as modified_at

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

PREVIEW BOX: Youโ€™ll see that _event_time and modified_at fields are a timestamp object:

3. Cast id to _id:

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

PREVIEW BOX: Youโ€™ll see that the _id field is added:

4. Add the rest of the fields to the SQL transformation:

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

PREVIEW BOX: Youโ€™ll see that the rest of the fields are added:

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

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

7. 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.

Last updated