Create SQL transformations on ProductInformation
Last updated
Last updated
We'll be doing a series of SQL transformations. Do not apply the SQL transformations until the very end.
Click on the link Construct SQL rollup and/or transformation:
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_time
We’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_at
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-6:
PREVIEW BOX: You’ll see that _event_time, deleted_at, and modified_at fields are a timestamp object
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 price
Action Item: Delete all the code SQL transformation editor and apply the code snippet below. You'll notice we've applied Line 5:
PREVIEW BOX: You’ll see that the pricefield is now added:
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:
PREVIEW BOX: You’ll see that there is the _id and product_name field added:
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.
