← back

Adventures in AWS, S3, Glue and Redshift

Created on: 2023-03-02

Collect and upload data to S3. I have done that, and you probably have as well. S3 is a convenient way to dump raw data without thinking twice and then moving to something else. So far, so good until you actually try to inspect and use that data.

It is not that the solution is hard; boto3 in Python makes it quite simple to download data from a bucket. Then, it’s just a matter of a few Python lines to process that data using your favorite libraries (pandas user here) and get the statistics we were after.

There are a few problems with the naive implementation of the solution above:

Ideally, we would run all the data processing in the cloud and have the analysis available and updated for anyone on our team to see. There are many ways of doing this in the cloud, but this is the story of a few days of playing with the AWS ecosystem. It took a lot longer than I would like to admit, but the results are close to what I intended. Happy reading!

Problem Statement

AWS is complex enough when you know what you are trying to achieve, and it’s almost impossible to navigate if you are just browsing around. This calls for properly framing what you are trying to achieve before you start and ensuring you stick to those goals. Otherwise, you are bound to get lost!

The problem that I was trying to solve can be stated as follows. I ran different experiments, and each experiment uploaded its results to the same S3 bucket. The format of each experiment was the same, but they differed across experiments.

In my initial setup, the content of the S3 bucket looked as follows:

Furthermore, the results of an experiment_1 had the following format:

{
	target-1_metric-1: value,
	target-1_metric-2: value,
	target-1_metric-3: value,
	target-2_metric-1: value,
	target-2_metric-2: value,
	…,
	target-n_metric-m: value,
}

But I wanted my experiments on a SQL table, so that my team and I could perform quick queries with a language we all knew. The output table I had in my mind was something along the lines of:

| Date | Target | Metric | Value | | ------------------- | ------ | ------ | ----- | | 2023/02/10 10:12:11 | 1 | 1 | 5.23 | | 2023/02/10 10:12:11 | 1 | 2 | 1.32 | | … | … | … | … |

This worried me because the transformation between formats is non-trivial and requires extracting information from the body of each json but also from its file name. I was unsure that I could do this entirely in AWS and for a while I considered modifying the program that created the outputs to be more amenable to analysis later.

I was not a fan of that option because I would always have the raw data and modify it later rather than risking corrupting the “ground truth,” which could not be recovered.

The Solution

After looking around and reading a couple of blog posts, I decided I wanted my processed data in AWS Redshift. Being a data warehouse with an online query editor and the ability to generate plots, it felt like the right house for our data. Having decided that the problem was to get it there.

In AWS, we can use AWS Glue (as its name cleverly suggests) to send data from one place to another while transforming it in the process (ETL). Originally, I started using their visual jobs, but I quickly realized I had no idea how to perform the complex transformations I had in mind.

The next step was to try to use one of their notebooks. As someone who spends most of their time writing code in Python, a Jupyter Notebook very quickly allowed me to do what their visual tool did not. Even then, it was not without some challenges.

Reading from S3

Reading from S3 using their notebooks is fairly easy. What wasn’t was understanding the format in which the output was returned. AWS Glue jobs return DynamicFrames, an object somewhat similar to a pandas DataFrame, but that does not allow for most of the transformation that one would expect. Because I was reading JSON objects in which each file contained a single JSON, each row of the dynamic frame also consisted of a JSON. I needed to change quite dramatically the structure of the frame, but it didn’t seem like the dynamic frames allowed for such complex transformations.

At some point, I realized I could perform transformations on whole rows using a custom function that would take a record (dictionary) and had to return a record as well. Each row was transformed, but it didn’t allow me to split one row into multiple, something I dearly needed.

One thing that can be done with DynamicFrames is transforming them into Pyspark DataFrames. This is useful because they have a function that I wasn’t aware existed, but not only is it super practical, but it is also available for pandas!

The function in question is called explode and does the following. If you start with a data frame in which one column has an array of objects, it returns a new data frame in which a row is created for each element of the array (with all other columns the same)

Input:

| Col1 | ColArray | | ------- | --------- | | Cubes | [1, 2, 3] | | Circles | [4, 5] |

Output:

| Col1 | ColArray | | ------- | -------- | | Cubes | 1 | | Cubes | 2 | | Cubes | 3 | | Circles | 4 | | Circles | 5 |

Now, how could I use this information for my task? My solution involved using a custom function to transform the original JSON file into a list of JSON.

For a given row

  1. I iterated over all the keys in the JSON.
  2. I separated the key into the target and the metric
  3. For each key, I created a new record: {“target”: target, “metric”: metric, “value”: value}
  4. I added each new record to a list
  5. I made that list a new column

After transforming each record using my custom function, I transformed the DynamicFrame into a PySpark DataFrame and exploded the new column. This got me almost to where I wanted.

| {“target”: target-1”, “ metric”: metric-1, “value: 5.23” | | --------------------------------------------------------- | | {“target”: target-1”, “ metric”: metric-2, “value: 1.32” | | … |

I then could use the select function to extract the record’s content as a new column. There was only one thing missing: extracting the information from the file name.

At first, I thought this was impossible because there was no option for adding the file name when loading from S3, and none of the DynamicFrame functions allowed adding a file name. Strangely and fortunately, when transforming the DynamicFrame into Spark, we can use a Spark function to add the filename as a new column (input_filename). Once that was done, writing regular expressions to extract the required information from the string was quite simple. Finally, I managed to get the data in the format I wanted.

Make sure the glue sticks.

Even though I had managed to load data from S3 in an AWS Glue Job and formatted it to my desire, I still had to write the new data as a table somewhere. That proved more challenging than I had anticipated.

Getting this right requires quite a few things:

Wrap up

Learning to set up a data workflow in AWS took much longer than it would have taken me to simply download the data to my computer and run the analysis locally. Nevertheless, I think that it will bring quite a few benefits:

Update

After setting the AWS GLUE job for a few days, I noticed a VERY high cost in the cost explorer (~ $20 / day for a few kilobytes of data). This automatically made this solution unfeasible as it would have represented 95% of the cost of the deployment.

I created a set of lambda functions written in good ol’ Python that are triggered whenever a new object is created in the bucket. They are virtually free, took me a fraction of the time to develop, and are easy to change.

I would advise starting small if you can: lambdas are great and can get the job faster and cheaper.