Transform and write data to Amazon Athena

1. Create a data output

Click on DATA OUTPUT > NEW and SELECT Amazon Athena

Provide the Output a NAME > shopping and choose DATA SOURCES > Orders Click on NEXT.

From the Upsolver Transformation Screen, we can add columns to the output, create calculated fields, manage aggregations, and perform UPSERTs. Upsolver provides both a UI for building the transformations as well as a SQL interface. This functionality makes Upsolver approachable to less technical users that may not be as familiar with big data technologies.

Please find a raw sample data coming from Amazon S3 below:

{
"orderId": "abc",
"buyerEmail": "[email protected]",
"orderDate": "2020/07/07T01:00:00Z",
"items": [{"itemId": "widget","price": 19.99}, 
          {"itemId": "trinket","price": 9.99}],
"netTotal": 29.98,
"salesTax": 1.80
}

The desired output will be an Amazon Athena table, flattening the nested items into individual rows. In addition, the buyerEmail should be hashed to protect PII, and an order_grand_total will be added by summing up the netTotal and salesTax.

2. Map fields from the source to Amazon Athena

Map source fields to output

The first transformation will be to add a column mapping for the Items. Expand the items array and click the + symbol next to itemId and price on the left-hand side. Dismiss the warning(s) if you see them.

Add fields and edit the output field name

Click in the OUTPUT COLUMN NAME and change the column names from data_items_itemsid > item_id and data_items_price > item_price.

Click on the PREVIEW button, and verify that the output contains a row for each ordered item.

Add 3 more columns to the output and rename them orderId > order_id, netTotal > order_net_total, and salesTax > order_sales_tax. Dismiss the warnings.

3. Transform data by creating calculated field

Transform DATE format

Click on Add Calculated Field on the upper left corner. Find the TO_DATE function and click on SELECT. Under DATETIME type in data.orderDate NAME > root.order_date Click on PREVIEW to make sure the transformation looks ok. Click on SAVE.

Mask field with MD5 hashing function

To protect PII, we're going to hash buyers' email addresses using the MD5 algorithm. Click on Add Calculated Field on the upper left corner. Find MD5 function and click on SELECT. Enter INPUT > data.buyerEmail and NAME > root.buyer_email_hash Click PREVIEW to make sure everything looks ok and then click on SAVE.

4. Use SQL interface for aggregation

Click over to the SQL tab by clicking SQL on the upper right corner.

Add a simple calculated field data.netTotal + data.salesTax AS order_total:DOUBLE directly to the SQL. Notice that the added field is automatically reflected in the UI. Click on PREVIEW to make sure everything looks correct.

SET order_date = TO_DATE(data.orderDate);
SET buyer_email_hash = MD5(data.buyerEmail);
// GENERATED @ 2020-10-11T20:45:11.427116Z
SELECT time AS processing_time:TIMESTAMP,
       data.items[].itemId AS item_id:STRING,
       data.items[].price AS item_price:DOUBLE,
       data.orderId AS order_id:STRING,
       data.netTotal AS order_net_total:DOUBLE,
       data.salesTax AS order_sales_tax:DOUBLE,
       order_date AS order_date:TIMESTAMP,
       buyer_email_hash AS buyer_email_hash:STRING,
       data.netTotal + data.salesTax AS order_total:DOUBLE
  FROM "Orders"  

5. Connect to Amazon Athena

Configure Amazon Athena run parameter

Click on RUN on the upper right corner and choose S3 STORAGE > S3 Default Output Storage

Select CONNECTION > Create your first Amazon Athena Connection and provide a NAME > athena and leave everything else as the default value click on CREATE

Go back to the RUN PARAMETER screen by clicking on the back button of your browser twice and you should see athena populated under CONNECTION. Provide the DATABASE NAME that you want to output to. This example uses upsolver. TABLE NAME > orders Click on NEXT.

Leave everything on RUN shopping as default and click on DEPLOY.

6. Verify data in Amazon Athena

Using Athena you can now query the data set created by Upsolver (it may take a few minutes before the data is available). Some sample queries that can be run are below:

Click here to login to Athena in order to run below queries

// get a list of orders

SELECT order_date,
         order_id,
         buyer_email_hash,
         order_net_total,
         order_sales_tax,
         order_total
FROM upsolver.orders
GROUP BY  1,2,3,4,5,6 limit 10

// how many orders per day?

SELECT count(distinct order_id) order_per_day,
         date_trunc('DAY',order_date) Date
FROM upsolver.orders
GROUP BY  date_trunc('DAY',order_date)
ORDER BY  date_trunc('DAY',order_date) DESC