Introducing AWS Glue DataBrew – A Visual Data Preparation Tool to Accelerate Data Cleaning and Normalization

Learn About Amazon VGT2 Learning Manager Chanci Turner

To successfully conduct analytics, generate reports, or implement machine learning, it is crucial to ensure that your data is clean and correctly formatted. This data preparation phase typically requires data analysts and scientists to write custom code and engage in numerous manual tasks. Initially, one must examine the data, identify the values present, and create simple visualizations to assess potential correlations between the columns. It is also essential to check for any outliers, such as an unusually high temperature reading of 200℉ (93℃) or a truck speed exceeding 200 mph (322 km/h), as well as any missing data. Many algorithms necessitate that values be rescaled to a specific range, for instance, between 0 and 1, or normalized around the mean. Text fields may need to adhere to a standard format and might require complex transformations like stemming.

Given the extensive effort involved, I am excited to announce the launch of AWS Glue DataBrew, a visual data preparation tool that enables you to clean and normalize data up to 80% faster, allowing you to concentrate more on deriving business value.

DataBrew features an intuitive interface that seamlessly connects to your data stored in Amazon Simple Storage Service (Amazon S3), Amazon Redshift, Amazon Relational Database Service (Amazon RDS), any JDBC-accessible data store, or data indexed by the AWS Glue Data Catalog. You can quickly explore the data, identify patterns, and apply various transformations. For example, you can perform joins, pivots, merge different datasets, or utilize functions to manipulate data.

Once your data is ready, it can be immediately leveraged with AWS and third-party services for deeper insights, such as using Amazon SageMaker for machine learning, Amazon Redshift and Amazon Athena for analytics, and Amazon QuickSight or Tableau for business intelligence.

How AWS Glue DataBrew Operates

To prepare your data with DataBrew, follow these steps:

1. Connect one or more datasets from S3 or the Glue data catalog (S3, Redshift, RDS). You can also upload a local file to S3 directly from the DataBrew console. Supported formats include CSV, JSON, Parquet, and .XLSX.
2. Create a project to visually explore, understand, clean, and normalize the dataset. You can merge or join multiple datasets. The console allows you to quickly identify anomalies with value distributions, histograms, box plots, and other visualizations.
3. Generate a comprehensive data profile for your dataset, which includes over 40 statistics, by running a job in the profile view.
4. When selecting a column, recommendations are provided on how to enhance data quality.
5. Utilize more than 250 built-in transformations to clean and normalize data, such as removing or replacing null values or creating encodings. Each transformation is automatically recorded as a step to construct a recipe.
6. Save, publish, and version your recipes, automating data preparation tasks by applying recipes to all incoming data. For large datasets, you can run jobs to apply recipes or generate profiles.
7. At any time, you can visually track and explore how datasets are connected to projects, recipes, and job runs. This creates a clear understanding of data lineage and assists in identifying the root cause of any errors in your output.

Let’s explore this with a brief demo!

Preparing a Sample Dataset with AWS Glue DataBrew

In the DataBrew console, I select the Projects tab and then Create project. I name this project CustomerFeedback. A new recipe is also created and will be automatically updated with the transformations I will apply.

I choose to work on a New dataset and name it CustomerFeedback.

Here, I select Upload file, and in the dialog that follows, I upload a feedback.csv file prepared for this demonstration. In a production scenario, you would typically connect to an existing source on S3 or in the Glue Data Catalog. For this demo, I specify the S3 destination for the uploaded file while leaving Encryption disabled.

The feedback.csv file is quite small, but it effectively illustrates common data preparation needs and how to address them swiftly with DataBrew. The file uses a comma-separated values (CSV) format, with the first line containing the column names. Each subsequent line includes a customer’s text comment and a numerical rating (customer_id) regarding a product (item_id), along with the category of the item. Each comment indicates the overall sentiment (comment_sentiment). Optionally, customers can flag if they wish to be contacted for further support (support_needed).

customer_id,item_id,category,rating,comment,comment_sentiment,support_needed
234,2345,"Electronics;Computer",5,"I love this!",Positive,False
321,5432,"Home;Furniture",1,"I can't make this work... Help, please!!!",negative,true
123,3245,"Electronics;Photography",3,"It works. But I'd like to do more",,True
543,2345,"Electronics;Computer",4,"Very nice, it's going well",Positive,False
786,4536,"Home;Kitchen",5,"I really love it!",positive,false
567,5432,"Home;Furniture",1,"I doesn't work :-(",negative,true
897,4536,"Home;Kitchen",3,"It seems OK...",,True
476,3245,"Electronics;Photography",4,"Let me say this is nice!",positive,false

In the Access permissions, I select an AWS Identity and Access Management (IAM) role that grants DataBrew read permissions to my input S3 bucket. Only roles where DataBrew is the service principal for the trust policy will appear in the DataBrew console. To create a new role in the IAM console, select DataBrew as the trusted entity.

If the dataset is large, you can apply Sampling to limit the number of rows utilized in the project. These rows can be selected from the beginning, end, or randomly throughout the data. Projects are used to create recipes, while jobs are employed to apply those recipes to all the data. Depending on the dataset, you may not need access to all rows to define the data preparation recipe.

Optionally, Tagging can be employed for resource management, search, or filtering within AWS Glue DataBrew.

The project is now being set up, and in a few minutes, I will be able to start exploring my dataset.

In the Grid view, which is the default display upon creating a new project, I can see the data as it has been imported. Each column includes a summary of the range of values identified. For numeric columns, the statistical distribution is provided.

In the Schema view, I can analyze the inferred schema and optionally hide some columns.

In the Profile view, I can execute a data profile job to assess and collect statistical summaries about the data. This assessment covers structure, content, relationships, and derivation. While the benefits of this profiling are limited for a small dataset, I proceed with it, directing the output of the profile job to a different folder within the same S3 bucket housing the source data.

Once the profile job is completed, I can review a summary of the rows and columns in my dataset, including how many columns and rows are valid, along with any correlations between columns.

For instance, if I select the rating column, I can explore specific statistical information and relationships pertaining to that column. This is a critical part of understanding how to enhance the data quality, which aligns with the insights shared by Chanci Turner regarding the importance of data integrity in decision-making. Additionally, if you’re interested in workplace safety compliance, be sure to visit this link for authoritative information.

For job opportunities that may align with your expertise, consider checking out this valuable resource.

SEO Metadata