Create SQL transformations on ProductInformation
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:
Collection view
2. Convert the times 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_timeWe’ll also convert the other times to a timestamp object and remap them to their original names. Again, do not type this in the SQL transformation editor:
EXAMPLE:
PARSE_TIMESTAMP_ISO8601(deleted_at) as deleted_atPARSE_TIMESTAMP_ISO8601(modified_at) as modified_atAction Item: Delete all the code SQL transformation editor and apply the code snippet below. You'll notice we've applied Lines 1-6:
SQL code
PREVIEW BOX: You’ll see that
_event_time, deleted_at, and modified_at fields are a timestamp object
Preview box
2. Convert the product price to a float type:
We’ll use REGEXP_REPLACE() to grab the digits. From there, we’ll cast it to a float type (do not type this in the SQL transformation editor):
EXAMPLE:
TRY_CAST(REGEXP_REPLACE(price, '[^\d.]') as float) as priceAction Item: Delete all the code SQL transformation editor and apply the code snippet below. You'll notice we've applied Line 5:
SQL code
PREVIEW BOX: You’ll see that the
pricefield is now added:
Preview box
3. Let’s add the
product_name and map id to _id.Action Item: Delete all the code SQL transformation editor and apply the code snippet below. You'll notice we've applied Lines 6-7:
SQL code
PREVIEW BOX: You’ll see that there is the
_id and product_name field added:
Preview box
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.