AWS Glue Studio Guide—How to Build Data Pipelines Without Writing Code
Tags: aws, devops, cloud
Published: 2022-04-11 07:34:37

_Originally written by Maksymilian Jaworski and Lidia Kurasińska_

You’ve probably heard that creating ETL (extract, transform, load) pipelines, especially complex ones, is a complicated task. Various tools have been developed to make this process much easier, but most of them still require some knowledge of a programming language (for example, [Python or R](https://www.stxnext.com/blog/r-and-python-comparison/)) often combined with an understanding of tools such as Spark.

In August 2017, AWS created Glue DataBrew, a tool perfect for data and business analysts, since it facilitates data preparation and profiling. A year ago, the company released AWS Glue Studio, a visual tool to create, run, and monitor Glue ETL Jobs.

AWS Glue Studio supports various types of data sources, such as S3, Glue Data Catalog, Amazon Redshift, RDS, MySQL, PostgreSQL, or even streaming services, including Kinesis and Kafka. Out of the box, it offers many transformations, for instance ApplyMapping, SelectFields, DropFields, Filter, FillMissingValues, SparkSQL, among many. We can save the results of our jobs to Amazon S3 and tables defined in the **AWS Glue Data** Catalog.

Also, apparently, we can use it all without knowing Spark, as Glue Studio will generate Apache Spark code for us.

**So, let’s see in practice what we can do with AWS Glue Studio. I promised myself that I would try not to write a single line of code when solving my case.**

In this article, I used a slightly modified [E-Commerce Data](https://archive.ics.uci.edu/ml/datasets/online+retail) dataset.

## AWS Glue Studio in practice

Let’s assume that you received the following task from your data analysts:

_One system is uploading daily CSV files, which contain the following information: invoice_no, stock_code, description, quantity, unit_price, customer_id, country, invoice_date. Calculate the total number of sold items (count quantity) and the total purchase value (sum per item quantity multiplied by unit_price) per customer and day. Save the results in a CSV file separated by commas in an S3 bucket._

I know this sounds more like a job for a data analyst and some of you probably think this is a simple and boring task, but don’t worry, we will add some action later on.

My input files are located in this directory: aws-glue-demo-202109/inputs![S3 Input files](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jpk8yvoet4x8zok03yjg.png)Okay, let’s see how we can do this with AWS Glue Studio.

To access it, choose AWS Glue from the main AWS Management Console, then from the left panel (under ETL) click on AWS Glue Studio. Go to Jobs, and at the top you should see the Create job panel—it allows you to create new jobs in a few different ways: _Visual with a source and target, Visual with a blank canvas, Spark script editor,_ and _Python Shell script editor_.
![Create job section of AWS Glue Studio](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jcvi7hy90jp0skoreh82.png)I selected Visual with a blank canvas. It should create a new, empty, untitled job.

Before we start building our ETL process, let’s go to the Job Details tab and discuss some important properties that determine how AWS Glue will run the job. Except for the job’s name, you can change those settings any time you want.
![Create job details in AWS Glue Studio](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qkhhz5zhndpqwn5xcgw3.png)
- _Glue version_—it determines the versions of Apache Spark and Python that are available to the job. Note: some features might not be available for particular versions. For instance, at the time of writing, the Data Preview function is not working with 3.0, the latest version.
- _Language_—either Python or Scala. I will go with Python.
- _Worker type_ (G.1X or G.2X)—for the G.1X worker type, each worker maps to 1 DPU. For the G.2X, it’s 2 DPU for each worker. I chose G.1X as it has way more resources than I will actually need.
- _Job bookmark_ (Enable, Disable, Pause)—now **this is a pretty important variable**, especially that Enable is a default value. In a nutshell: when you enable job bookmarks, once you process some data (e.g., an S3 file), **the data is marked by a job as processed and will not be processed by this job in the next executions.** Bookmarks are tied to jobs, so different jobs can process the same file. I’m disabling it now, as I will probably have to process the same files over and over again during the development of my job. For extended description of Job Bookmarks, please read [Tracking Processed Data Using Job Bookmarks.](https://docs.aws.amazon.com/glue/latest/dg/monitor-continuations.html?icmpid=docs_glue_studio_helppanel)
- _Number of retries_—this is pretty self-explanatory. I switch this value from 3 to 0 right now, as I don’t want Glue to retry executing a task which fails just because I made some dummy mistake, like using an empty file, for example.  

I’m not going into the _Advanced properties_ section, but keep in mind that this is where you can configure your S3 script path, Spark UI logs path, set maximum concurrency, disable metrics, etc.

**Note:** you have to set those settings for each created job, unless you’re cloning a job—then its copy has the same settings as the original one.

Now, let’s go back to the Visual section.

1.From Source, select the Amazon S3 node.
![AWS Glue Studio - source types - Amazon S3 node](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/atqa2f8t9txf5qcw3o3s.png)In Data source properties - S3 set:
- S3 source type: S3 location—so you will access S3 files directly
- S3 url: s3://aws-glue-demo-202109/input/
- Recursive: true
- Data format: CSV
- Delimiter: Comma (,)
**Note:** you are limited to the following delimiters: Comma, Ctrl+A, Pipe, Semicolon, Tab.
![AWS Glue Studio - data source properties](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gx9k24tjur6tjeqf68e6.png)Now, let’s go to the Output schema tab.
![AWS Glue Studio - data output schema](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9pl2tw4mtg2k3hkdgpp2.png)Well, those datatypes don’t look right—you will have to change this. Click the _Edit_ button and for each key set the right data type (according to the information provided by data analysts): 
![Output schema tab](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kumvr8pwctqyrb1g3fqe.png)It looks good, click on Next and go to the next step.

2.Doing aggregations. Here lies the first “obstacle,” as Glue Studio does not have a built-in transformation node which allows us to do aggregates. The best solution is to use _Transform - Spark SQL node_. You can name the node _aggByCustomerByDate_, and in the _transform_ section, select _Amazon S3_ (the name of the parent node) as input source and give it a sales alias, which you can use in your SQL code as the table name. In the _Code block_, you can put a simple SQL query, which takes the _customer_id_ column, gets the date from the _invoice_date_ column, sums _quantity_ and _quantity*unit_price_, and groups it by customer and _sale_date_.

Let’s assume that it doesn’t break my “not-writing-a-line-of-code” rule.

**Note:** this editor does not verify syntax, so double-check your query before you run it.
![AWS Glue Studio - transform node](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zxaxicz4m4q70hjc9i6o.png)3.Now that you are done with aggregates, save its result to S3. From the _Target_ menu, choose Amazon S3, pick up CSV Format. Decide if you want to apply any compression and set the target location to _s3://aws-glue-demo-202109/output/1st-direct_
![AWS Glue Studio - target node properties](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7gdt6jia5qzl9osdyr3x.png)4.The job is ready. In the right-upper corner, hit Save, then Run, and wait. You can go into the Runs tab and see the job progress, as well as links to logs and run metadata.

After a minute, you can see that the job has succeeded.
![AWS Glue Studio - job run finished](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gljyvl30bvycumqdz145.png)Now, go to the target S3 directory. You have 4 files here:
![AWS Glue Studio - target S3 directory](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k05k94wqdnfral0ahssv.png)Each file contains _customer_id, sale_date, total_quantity_ and _total_sale_.
![Each file contains customer_id, sale_date, total_quantity and total_sale.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q8wo40euv60nhswo2p51.png)Okay, that was quite quick and simple. But I’m not satisfied with this solution, and you might not be, either. True, I did calculations for all files, but what will happen tomorrow when the new file arrives? Like you, I don’t want to process all the data once again. We’re only interested in processing newly created files.

Besides, data analysts also have some further requests.

1. They remembered that sometimes quantity might be a negative value which indicates that an item was returned. They would like me to exclude those rows from the aggregations and store the list of returned items in a separate file.

2. If you took a good look at the last image, you should notice that the second row is missing a value for _customer_id_. Analysts would like to get rid of empty customers.

3. Analysts would like to link some customers’ data (first name, last name, address) to the file with aggregations.

4. Working on four separate files is a bit troubling—they would prefer to have one file only.

Let’s address my issue first. The way I see it, there are two possible approaches:

1. The aforementioned Job Bookmarks. As the doc says: _“Job bookmarks are used to track the source data that has already been processed, preventing the reprocessing of old data. Job bookmarks can be used with JDBC data sources and some Amazon Simple Storage Service (Amazon S3) sources. Job bookmarks are tied to jobs. If you delete a job, then its job bookmark is also deleted. You can rewind your job bookmarks for your Glue Spark ETL jobs to any previous job run, which allows your job to reprocess the data. If you want to reprocess all the data using the same job, you can reset the job bookmark.”_

2. Manually, or by using a function or some parameter, decide which files (day or range of days) should be processed.

The first solution seems really cool, but let’s say that I’m not 100% sure how the data is loaded—perhaps files are overwritten every day? Or they could suddenly start doing that? Or you’re going to modify our jobs often and re-run them for particular files, and you don’t want to remember about resetting the job bookmark?

Either way, let’s say that for the sake of this article, you simply can’t or don’t want to rely on job bookmarks. What can you do now?

You could filter out the data in, for instance, _Transform - Spark SQL node_, but it doesn’t really solve the issue—the job will still be processing all files, you will just filter out the output or the data which goes into aggregations. You have to figure out how to divide those files “logically.”

Instead of working directly with S3 files, try to organize S3 files into databases and tables. Perhaps then you will be able to query the data more efficiently. Use the AWS Glue Crawler for this.

In a nutshell, AWS Glue can combine S3 files into tables that can be partitioned based on their paths. For example, if your files are organized as follows:

```
bucket1/year/month/day/file.csv
```
then AWS Glue can create one table from all files in bucket1, which will be partitioned by year, month, and day. The level of partition creation is also definable, and you can have, for example, a table for each separate day, month or year. You’ll find more details [in this article](https://aws.amazon.com/blogs/big-data/work-with-partitioned-data-in-aws-glue/) on working with partitioned data in AWS Glue.

For now, there are two ideas for you to test:

1. Create a separate table for each day.

2. Create one table with partitions by year, month, and day.

But before you create databases and tables, you have to reorganize the structure of your S3 bucket from:

```
bucket/YYYYMMDD_data.csv
```
to

```
bucket/year/month/day/data
```
So, instead of:

```
aws-glue-demo-2021/inputs/20210901_data.csv
aws-glue-demo-2021/inputs/20210902_data.csv
aws-glue-demo-2021/inputs/20210903_data.csv
aws-glue-demo-2021/inputs/20210904_data.csv
aws-glue-demo-2021/inputs/20210905_data.csv
```
your files will be organized this way:

```
aws-glue-demo-2021/inputs/2021/09/01/data.csv
aws-glue-demo-2021/inputs/2021/09/02/data.csv
aws-glue-demo-2021/inputs/2021/09/03/data.csv
aws-glue-demo-2021/inputs/2021/09/04/data.csv
aws-glue-demo-2021/inputs/2021/09/05/data.csv
```
Once it’s done, you can start working with AWS Glue Crawler (which is also available from the AWS Glue Studio panel in the Glue Console tab.)
![AWS Glue Studio - access Glue crawlers](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f6s4xelwoe3n21kgephj.png)First, configure a crawler which will create a **single** table out of all the files.

Click on _Add Crawler_, then:
1. Name the Crawler _get-sales-data-partitioned_, and click _Next_.
2. Keep the Crawler source type on default settings (Crawler source type: _Data stores & Crawl all folders_), then click _Next_ again.
3. Select S3 as the datastore and specify the path of _s3://aws-glue-demo-202109/inputs_ and click _Next_.
4. No, you don’t want to add another data store.
5. Now, you can either choose an existing role or create a new one. Go ahead and create a new one.
6. For now, choose _Frequency: Run on demand_.
7. To store the Crawler output, create a database called _sales_partitioned_ and select the created database from the drop-down menu. In configuration options, select _Ignore the change and don’t update the table in the data catalog and Delete tables and partitions from the data catalog_ (I will explain why later on), and click _Next_.
8. Review your crawler and confirm.
![AWS Glue Studio - crawler creation setup](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zylhnaqttp10zfqnobnn.png)For the crawler that will create separate tables the process is pretty much the same; the only changes are in following steps:
- Step #1: Name it _get-sales-data-partitioned-sep_.
- Step #2: Choose an already existing role (created for the previous crawler.)
- Step #7: In Configure the crawler output, create a new database called _sales_partitioned_sep_. In Group behavior for S3 - Table level, enter 5. Why 5? Well, counting from the beginning: bucket is the first level, inputs—the second one, year—the third one, month—the fourth one, and day comes fifth.
![AWS Glue Studio - crawler create database](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/acb25dh4jabx3q4tld30.png)Next, review your crawler and save it. At this point, you should have two crawlers that will create two separate databases—run both and wait.

After a while, you can see that the get-sales-data-partitioned crawler created one table and get-sales-data-partitioned-sep created nine tables.
![AWS Glue Studio - glue crawlers](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g4lapx03j4hirdplmohy.png)Why nine? Most likely because someone created new files in the meantime. You can find out by going into _Databases_, where you should see your databases:
![AWS Glue Studio - all databases catalog](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dn8mieh90gqat96324bj.png) In _sales_partitioned_ you should see one table:
![sales_partitioned catalog](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1wpwiqshsd0k2i6jlcgo.png)Whereas in sales_partitioned_sep, you should see nine tables.
![Sales_partitioned_sep catalog](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qfykh8cksr3o9micij4o.png)The first thing that stands out is that one of the tables repeated in two months got an ugly suffix. But let’s go to the input_partitioned table. At the bottom you should see columns, which are your partitions:
![Edit schema](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o4uvz7upus6mzsnadmke.png)How to figure out what’s behind _partition_0, partition_1,_ and _partition_2_? Click on _View Partitions_: you can see that _partition_0_ is year, _partition_1_ is month, and _partition_2_ is day.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0h5spuxtcmji77amubcr.png)Click on _Close Partitions_, next _Edit Schema_, and rename these columns. Save it, and now your table should look like this:
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ctqaxx23h3dai4dug6yf.png)**Note:** now it is important to explain why I previously checked Ignore the change and don’t update the table in the data catalog option during crawler creation. If I had not selected this option and run the crawler tomorrow, the column names I gave (year, month, day) would have been overwritten back to partition_0, partition_1, and partition_2.

_Update! There is one thing that I missed while writing this article. If you name your S3 partitions the following way:_

```
year=2021/month=08/day=30/data.csv
```
_Glue Crawler will automatically pick up partition names, and you won't have to rename columns by yourself._

Now, back to your jobs—what are the options? On the left-hand side, you can see that I can select individual tables from the sales_partitioned_sep database. On the right-hand side, I have only one table to choose from, but I can send **partition predicates.**
![Partition predictates](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/67c4lkpl6qpjjlmols0v.png)Both approaches reduce the amount of initial data. However, there are few things that I don’t like about the second one:
- the names of the tables do not clearly indicate (at least in my case) what data we are working on,
- when I have to work on more than one day, I will have to add separate source nodes and create joins, whereas in the first solution, I will only have to modify the query in predicament pushdown.

Okay, so I’m good with the first one but, before we proceed, here are a few notes.

Now that we are working with a _Data Catalog_ table instead of S3 files directly, we should note that the output of the file has changed. We no longer have _choice_ fields—now data types were defined by Glue Crawler.

So, how can you change this? There are three options:
- you can edit schema types in the _Glue Data Catalog_ (same way we changed the partition name),
- you can change the types in the first step (like in the previous job),
- you can add a separate transformation node that will change the types.

I chose the third option. Why? Firstly, because I want everyone to see that this step is taking place (that we are expecting some data types.) Secondly, because I want to show you another transformation.

So, with partition predicate and extra step for applying data types, our job looks like this:
![Apply mapping](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b09eimn2hl3hm4nvseas.png)Now, back to the analyst issues.

Firstly, let’s address item returns. You want to filter out rows with negative _unit_price_ and save them in a separate file. Add two additional transforms - _Filter nodes_ after _setDatatypes_ node. The first one (_getSales_) takes records with _unit_price_ >= 0; the second one (_getReturns_) takes records with _unit_price_ < 0. After _getSales_, proceed with aggregations and then save it to S3; after _getReturns_, just save it to S3.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/euh240lesap9y7ozbmsz.png)**Note:** it seems that you cannot combine or and and operators in one step. If you really need to do it, you should use a Spark SQL node and write a custom SQL query.

What’s next? Try to remove **empty customers** from the calculation. Now, this one is a bit tricky—you want to filter it out after you set data types using _Transform - Filter node_, but this step is limited to “=, !=, <, >, <=, >=” and using != with Null or empty string is not working...
![Remove empty customers](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cge6yjzm550yogyxba23.png)So maybe you could filter out before you set data types? Well, you can’t do that—before applying data types you can only filter columns for matching values using regex.
![Language selection box](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vjvx4tq3033nli0zj3gb.png)So, what’s left? Once again, you have to use Spark SQL transformation to write a query which will filter out null values. The key function is _isnotnull(column) = 1_. I placed this step after applying data types and before filtering out discounts.
![Spark SQL transformation](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fm5npzsovrhjeu257gij.png)Next, let’s handle the number of partitions of output files. And here I have to give up—**it can’t be accomplished without using custom transformation** (well, at least I didn’t figure out how to do that.) Is it tough?

Well, it depends. The documentation (AWS doc: [transforms custom](https://docs.aws.amazon.com/glue/latest/ug/edit-jobs-transforms.html#transforms-custom)) is not very extensive and contains only one example. The most important thing to remember is that the _Custom Transformation node_ only accepts _glueContext_ and _DynamicFrameCollection_ as input and must also return _DynamicFrameCollections_ (collections of _DynamicFrames_) as output.

What does it mean? It means that in order to perform any transformations, in the first step you have to choose which _DynamicFrame_ you want to work on. If the transform has only one parent, there is no problem—you choose the first one and convert _DynamicFrame_ to _DataFrame_:

```
def reducePartitionNumber(glueContext, dfc) -> DynamicFrameCollection:
  df = dfc.select(list(dfc.keys())[0]).toDF()
```
Then, you can do whatever Spark transformations you might want. In my case, I want to reduce the number of partitions created, so I do:

```
df_w_less_partitions = df.coalesce(1)
```
Then I convert the dataframe back to _DynamicFrame_ and return it as the _DynamicFrames_ collection:

```
df_one_partition = DynamicFrame.fromDF(df_w_less_partitions, glueContext, "one_part_df")
return DynamicFrameCollection({"CustomTransform0": df_one_partition}, glueContext)

```
To sum up, the node should look like this:
![Node should look like this](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9jrgqk2n6hap80cqu6cu.png)But that’s not the end. According to the documentation and what you saw above, _“A custom code transform returns a collection of DynamicFrames, even if there is only one DynamicFrame in the result set,”_ and, unfortunately, output nodes **do not** accept DynamicFrameCollection as input, so you will have to add one more a step that will select a specific DynamicFrame from the collection.

For this step you can use _SelectFromCollection_ transform node which allows you to indicate which dataset you want to use. Since there is only one parent node here, there is only one dataset to choose from.

But if this node had several parents or a parent returning many _DynamicFrames_ (for example SplitFields transformation, which splits dataframe into two separate dataframes), you would have a choice.
![SelectFromCollection](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jz9enosksy0t0vfyiwru.png)With that done, we can move on to the last task: adding customer information to the dataframe with aggregates. My analysts loaded the customers.csv file into the main folder _aws-glue-demo-202109_. File contains the following data: _id, name, surname, address_.

In our job, we first add another source that will directly reference the CSV file contained in S3 bucket. I give this node the name Customers. AWS Glue Studio detects data formats, separators and defines data types by itself.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ckgwyh2l0lyjgkrue9x5.png)Then we will use the _Transform - Join node_ to combine the data from the two sources. This type requires at least two parent nodes, so I’m adding _aggByCustomers_ node as the second source.

Why am I joining here? Well, I’m afraid that joining after reducing the number of partitions (with the _getReducedDynamicFrame node_) will once again result in multiple partitions, and joining earlier (before the aggregate is performed) is less efficient, since that joining would be performed on larger number of rows.
![Transform - join node](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yz7k9zy0dx9494myslco.png)Now you need to select the join type and declare join conditions. Since my left dataset is _Customers_ node, right is _aggByCustomers_ and I know that I don’t have data for all customers, I chose _right join_. I join those datasets using _columns id_ and _customer_id_.
![Join type](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/87axiyjd4qpa19o7pxs5.png)The last thing to do is to change the parent output of _reducePartitionsNumber_ from _aggByCustomers_ to _joinAggregatesWithCustomers_ and you are good to go. Now the job should look like this:
![Finished node](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fj1yyounkjydho0kpnt0.png)Let’s run it and see what happens. Once the job is finished, go to S3 and...

...it’s working! You received only two files:
![Received files](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3lr4s6zi3pb5mx2zk6ia.png)In the first one, you’ll have customers with their data (if found) and aggregates:
![First file - customers with their data](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6hsbte5lv7fe56crz1cs.png)In the second one, you’ll have returned items:
![Second file - returned items](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yyk7e6a8kah7ofedhb1t.png)Well, frankly speaking I do not like the order of the columns, because when join did not find the client’s data, it looks like this:
![Join did not find client's data output](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/66rlyxr8mecvc0s8xptf.png)And I don’t see any other way to reorder them than another Spark SQL node, where I would just select the columns in a different order. But let’s leave it as-is. Optionally, I could also use a _FillMissingValues_ ​​transformer node and type, for example, _“NOT_FOUND”_ to fill missing values and make the file more readable.
 
## How to schedule an AWS Glue job?

At this point, you could say: “Okay, but I don’t want to edit and run my job manually every day.” There are two things that should be set up to automate the whole process.

1. Automate crawler, so newly added files are visible as consecutive partitions. You don’t have to create a new crawler—you can edit an existing one. Choose a crawler, click _Action -> Edit Crawler_ and hit _Next_ until you reach step number #5 (Create a scheduler for this crawler.) Choose _Daily_ instead of _Run from demand_ and then you can set up the time.
![Automate crawler](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tiijuolhd2lo1iu72f4q.png)We go through the next steps and save changes. If everything’s gone well, you should see that a scheduler appeared in the crawler:
![Schedule crawler](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5c4fj2ws0e6cahqo40ji.png)
2. The second thing is job automation. First, create a job scheduler, which is a relatively simple task. Go to the created job and click the _Scheduler_ tab. You’ll immediately see the options to create a new scheduler:
![Scheduler tab](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9f1hu7pj65tf9x3xd27c.png)You can set up a scheduler name, choose its frequency (Hourly, Daily, Weekly, Monthly, Custom) and, optionally, add a description. I set up a job scheduler to run one hour after the Glue scheduler.
![Details of schedule](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qmcufvevkyu2bqnifp6r.png)Now, it would be great if you did not have to manually change the date for which the aggregates are to be made. The easiest solution would be to modify the _Partition predicate_ function in the source node, so it automatically extracts year, month and day from the current date.

```
year == year(current_date) AND 
month == month(current_date) AND 
day == day(current_date)
```
**Note:** please keep in mind that **the months and days are not integer values** (01, 02, 03, etc.), and Spark functions _Extracts the month as an **integer** from a given date/timestamp/string_. So, you would have to once again reorganize your bucket structure.

For those interested in more advanced solutions, please read about passing custom parameters to Glue Job (since AWS Glue Studio also allows you to pass up to 50 custom parameters) in this article on [Calling AWS Glue APIs in Python—AWS Glue](https://docs.aws.amazon.com/glue/latest/dg/aws-glue-programming-python-calling.html?icmpid=docs_glue_studio_helppanel#aws-glue-programming-python-calling-parameters).

## When should you use AWS Glue Studio?

You might be wondering when you should use AWS Glue Studio. Let’s focus on Glue Studio, not Glue as a whole service.

AWS Glue Studio is great if you want to:
- quickly create ETL jobs that run regularly,
- combine large amounts of data from many different sources,
- and perform _simple_ transformations such as rename or drop fields, join, split, or filter dataframe. 
 
Since you can see and copy the code, a job prepared in Studio could be used as a starting point for a larger, more complex job in Glue.

And when is it not that useful? For instance, when you are looking for a job orchestrator that allows you to repeat your work from any step, use sensors to wait for a file, among other things, you will be better off with Step Functions or Airflow. If you are looking for a data preparation tool, DataBrew would be a better choice since it offers way more data transformation options.

Also, please keep in mind that AWS Glue Studio is not free. When you create a job you have to declare the number of workers (a minimum of two) and choose one of the two possible instance types: G.X1 or G.X2. In G.X1, each worker maps to one DPU and one executor, which launches with eight Spark cores and 10 GiB of memory. G.X2 doubles those values. And you are charged an hourly rate based on the number of DPUs used to run your ETL jobs.

## What are the pros and cons of AWS Glue Studio? 
  
**One of the main advantages of AWS Glue Studio is the fact that, except for just one step, I didn’t need to use any programming language to create the above-mentioned ETL job.**

What is not obvious can often be played around with a Spark SQL query. Integrating AWS Glue Studio with S3 or Data Catalog and scheduling jobs is extremely easy, and the same applies to job scheduling. Besides, let’s not forget that you can get data from streaming services like Kinesis or Kafka.

What’s more, in AWS Glue Studio we can monitor all the jobs in one view, and _Job bookmarks_ is a very handy feature, too.

The main disadvantage is definitely the UI, which is a bit clumsy. It can hang and behave unpredictably at times. Since you can’t manually move nodes, larger jobs might be unclear at some point.

For users unfamiliar with Spark, the limited number of transforms might be a big pain in some cases. And, the latest version does not support all features such as _Data Preview_, which is something you will find out only when you try to run it.

## Final thoughts on building complex data pipelines with AWS Glue Studio

Here is what you’ve accomplished by following the instructions in this article:
- took files from both Data Glue Catalog and S3 directly, 
- applied data types, 
- removed unwanted data,
- divided the data based on certain values,
- did some joins and aggregations,
- reduced the number of partitions,
- saved the results to desired paths in an S3 bucket.
  
As you’ve seen in this tutorial, building complex data pipelines doesn’t need to be challenging. I hope you’ve enjoyed following the instructions and learnt a bit more about AWS Glue Studio.

Here are some other articles you might find useful:
- [Python for Data Engineering: Why Do Data Engineers Use Python?](https://www.stxnext.com/blog/python-for-data-engineering/)
- [How to Build a Spark Cluster with Docker, JupyterLab, and Apache Livy—a REST API for Apache Spark](https://www.stxnext.com/blog/docker-jupyterlab-apache-livy-rest-api-apache-spark/)
- [Machine Learning Implementation and Project Management: A How-To Guide](https://www.stxnext.com/blog/machine-learning-implementation-project-management/)

_In the article, I used a dataset provided by Dua, D. and Graff, C. (2019). UCI Machine Learning Repository [http://archive.ics.uci.edu/ml]. Irvine, CA: University of California, School of Information and Computer Science._