Cheat Sheet: No-Code ETL with AWS Glue Studio

AWS Glue Studio is a graphical interface for AWS Glue that makes it easy to create, run, and monitor extract, transform, and load (ETL) jobs. Its primary purpose is to allow users—including those who are not expert coders—to build sophisticated data pipelines visually.

Why Use Glue Studio?

• No-Code / Low-Code: The visual, drag-and-drop interface allows you to build complex ETL logic without writing any PySpark or Scala code. This makes data integration accessible to a broader audience, including data analysts and BI professionals.

• Accelerated Development: Visually composing a job is much faster than writing, testing, and debugging code from scratch.

• Automatic Code Generation: While you build the job visually, Glue Studio automatically generates the underlying Spark code. You can view and even edit this code if you need to add custom transformations or logic, providing a "best of both worlds" experience.

• End-to-End Visibility: The graphical representation of the job provides a clear, easy-to-understand view of your entire data flow, from source to target.

The Visual Interface Components

A Glue Studio job is represented as a directed acyclic graph (DAG) made up of three types of nodes:

1. Sources

This is where your data comes from. You start your job by adding one or more source nodes.

• Common Sources:

  • AWS Glue Data Catalog: The most common source. You select a database and table from your catalog, and Glue Studio knows its schema and location (e.g., in S3).

  • Amazon S3: Directly read files from S3.

  • Amazon Redshift: Read data from a Redshift cluster.

  • Amazon RDS / JDBC: Connect to various relational databases.

  • Amazon DynamoDB: Read data from a NoSQL table.

2. Transforms

These are the nodes that modify your data. You can chain multiple transforms together to perform complex data preparation.

• Visual Transform Library: Glue Studio provides a rich set of pre-built transformation nodes, including:

  • Join: Combine two datasets based on a common key (inner, outer, left, right joins).

  • Filter: Remove rows based on a specified condition.

  • DropFields: Remove unwanted columns from the dataset.

  • SelectFields: Choose specific columns to keep.

  • DerivedColumn: Create a new column based on an SQL expression.

  • ChangeSchema (ApplyMapping): Rename columns, change data types, or reorder columns.

  • Aggregate: Perform group-by operations (e.g., COUNT, SUM, AVG).

  • Union: Combine rows from two datasets with the same schema.

  • SQL: Write a custom Spark SQL query to perform complex transformations.

3. Targets

This is where you write the final, transformed data.

• Common Targets:

  • Amazon S3: Write the output data to an S3 bucket in formats like Parquet, ORC, CSV, or JSON. This is the most common target for populating a data lake.

  • Amazon Redshift: Load the transformed data into a Redshift data warehouse.

  • AWS Glue Data Catalog: Create or update a table in the Data Catalog with the schema of the transformed data. This makes the output immediately queryable by services like Amazon Athena.

  • JDBC: Write data to a relational database.

How to Build a Job in Glue Studio: A Typical Workflow

1. Create a New Job: In the AWS Glue console, navigate to Glue Studio and select "Create and manage jobs". Choose the "Visual with a source and target" option.

2. Configure the Source:

   • Select your source node on the graph.

   • Choose the data source type (e.g., AWS Glue Data Catalog).

   • Select the specific database and table you want to process.

3. Add and Configure Transforms:

   • Click the + icon after the source node and select a transform from the menu (e.g., "Join").

   • Configure the transform's properties. For a "Join" transform, you would select the second source table and specify the join condition (the keys to join on).

   • Add another transform, like "Filter", to the output of the Join node. Configure the filter condition (e.g., status = 'COMPLETED').

   • Add a "DropFields" transform to remove intermediate columns used in the join.

4. Configure the Target:

   • Select the target node.

   • Choose the target type (e.g., Amazon S3).

   • Specify the desired output format (Parquet is recommended for analytics).

   • Provide the S3 path where the output data should be written.

   • Configure how the Data Catalog should be updated (e.g., create a new table for the output data).

5. Configure Job Details:

   • Go to the "Job details" tab.

   • Give your job a name.

   • Select an IAM role that gives Glue permission to access your sources, targets, and other required services.

   • Adjust settings like the number of workers (DPUs) and timeout if needed.

6. Save and Run:

   • Save the job.

   • Click the "Run" button. Glue Studio will validate the job, generate the underlying Spark script, provision a serverless Spark environment, and execute the pipeline. You can monitor the progress in the "Run details" tab.