Creating Field Mappings

We’ll be doing 3 different types of field mappings:

1. CONVERT A DATE THAT COMES IN AS A STRING INTO A TIMESTAMP

We can convert the date that comes in as a string into a timestamp data type by using PARSE_DATETIME_ISO8601. You can learn more here in this Rockset Community post. When you’re done, your SQL transformations should be written like this:

SELECT PARSE_DATETIME_ISO8601(_input.timeiso8601) AT TIME ZONE 'America/Los_Angeles' as _event_time
FROM _input

You’ll notice that at the bottom, you’ll see the results of the SQL transformation as _event_time:

Note that we’ll be iterating on this SQL query throughout this section. Don’t click Apply until the end.

2. CONVERT PRICE THAT COMES IN AS A STRING INTO A FLOAT

This Rockset Community post covers how to convert a price that comes in as a string into a float.

When you’re done, the SQL transformations in the editor should be written like this. Please note that the below includes the previous transformation:

SELECT PARSE_DATETIME_ISO8601(_input.timeiso8601) AT TIME ZONE 'America/Los_Angeles' as _event_time, try_cast(REGEXP_REPLACE(_input.payment.price, '[^\d.]') as float) as price_float
FROM _input

You’ll notice the final results have both _event_time_ and price_float:

3. CONVERT THE CREDIT CARD NUMBER INTO A HASH

Rockset supports PHI/PII masking, which stores only the hashed value and not the original value. This Rockset Community post covers PHI/PII masking. To convert the credit card number into a hash, copy the below, which again is inclusive of the above transformations:

SELECT PARSE_DATETIME_ISO8601(_input.timeiso8601) AT TIME ZONE 'America/Los_Angeles' as _event_time, try_cast(REGEXP_REPLACE(_input.payment.price, '[^\d.]') as float) as price_float, TO_HEX(SHA256(_input.payment.credit_card_number)) as credit_card_number FROM _input

4. FINAL COMBINED TRANSFORMATION QUERY

When we incorporate the fields that don’t need transformation, our final SQL transformation query will look like this:

SELECT try_cast(REGEXP_REPLACE(_input.payment.price, '[^\d.]') as float) as price_float, PARSE_DATETIME_ISO8601(_input.timeiso8601) AT TIME ZONE 'America/Los_Angeles' as _event_time, car, company_id, id, ip_address, _input.items_clicked_on, person,TO_HEX(SHA256(_input.payment.credit_card_number)) as credit_card_number, _input.payment.discount, _input.payment.credit_card_type
FROM _input

Once you’re done, Apply the transformation:

From there, Create the collection: