Post Syndicated from Leonardo Gomez original https://aws.amazon.com/blogs/big-data/detect-and-process-sensitive-data-using-aws-glue-studio/

Data lakes offer the possibility of sharing diverse types of data with different teams and roles to cover numerous use cases. This is very important in order to implement a data democratization strategy and incentivize the collaboration between lines of business. When a data lake is being designed, one of the most important aspects to consider is data privacy. Without it, sensitive information could be accessed by the wrong team, which may affect the reliability of a data platform. However, identifying sensitive data inside a data lake could represent a challenge due to the diversity of the data and also its volume.

Earlier this year, AWS Glue announced the new sensitive data detection and processing feature to help you identify and protect sensitive information in a straightforward way using AWS Glue Studio. This feature uses pattern matching and machine learning to automatically recognize personally identifiable information (PII) and other sensitive data at the column or cell level as part of AWS Glue jobs.

Sensitive data detection in AWS Glue identifies a variety of sensitive data like phone and credit card numbers, and also offers the option to create custom identification patterns or entities to cover your specific use cases. Additionally, it helps you take action, such as creating a new column that contains any sensitive data detected as part of a row or redacting the sensitive information before writing records into a data lake.

This post shows how to create an AWS Glue job that identifies sensitive data at the row level. We also show how create a custom identification pattern to identify case-specific entities.

Overview of solution

To demonstrate how to create an AWS Glue job to identify sensitive data, we use a test dataset with customer comments that contain private data like Social Security number (SSN), phone number, and bank account number. The goal is to create a job that automatically identifies the sensitive data and triggers an action to redact it.

Prerequisites

For this walkthrough, you should have the following prerequisites:

• An AWS account
• An AWS Identity and Access Management (IAM) user with access to the following services:
  • Amazon Athena
  • AWS CloudFormation
  • AWS Glue
  • Amazon Simple Storage Service (Amazon S3)

If the AWS account you use to follow this post uses AWS Lake Formation to manage permissions on the AWS Glue Data Catalog, make sure that you log in as a user with access to create databases and tables. For more information, refer to Implicit Lake Formation permissions.

Launch your CloudFormation stack

To create your resources for this use case, complete the following steps:

1. Launch your CloudFormation stack in us-east-1:
   
2. Under Parameters, enter a name for your S3 bucket (include your account number).
3. Select I acknowledge that AWS CloudFormation might create IAM resources with custom names.
4. Choose Create stack.
   
5. Wait until the creation of the stack is complete, as shown on the AWS CloudFormation console.
   

Launching this stack creates AWS resources. You need the following resources from the Outputs tab for the next steps:

• GlueSenRole – The IAM role to run AWS Glue jobs
• BucketName – The name of the S3 bucket to store solution-related files
• GlueDatabase – The AWS Glue database to store the table related to this post
  

Create and run an AWS Glue job

Let’s first create the dataset that is going to be used as the source of the AWS Glue job:

1. Open AWS CloudShell.
   
2. Run the following command:

   aws s3 cp s3://aws-bigdata-blog/artifacts/gluesendata/sourcedata/customer_comments.csv s3://glue-sendata-blog-<YOUR ACCOUNT NUMBER>/customer_comments/customer_comments.csv

   
   This action copies the dataset that is going to be used as the input for the AWS Glue job covered in this post.

   Now, let’s create the AWS Glue job.

3. On the AWS Glue Studio console, choose Jobs in the navigation pane.
   
4. Select Visual with blank canvas.
   
5. Choose the Job Details tab to configure the job.
   
6. For Name, enter GlueSenJob.
7. For IAM Role, choose the role GlueSenDataBlogRole.
8. For Glue version, choose Glue 3.0.
9. For Job bookmark, choose Disable.
   
   
10. Choose Save.
    
11. After the job is saved, choose the Visual tab and on the Source menu, choose Amazon S3.
    
12. On the Data source properties -S3 tab, for S3 source type, select S3 location.
13. Add the S3 location of the file that you copied previously using CloudShell.
14. Choose Infer schema.
    

This last action infers the schema and file type of the of the source for this post, as you can see in the following screenshot.

Now, let’s see what the data looks like.

15. On the Data preview tab, choose Start data preview session.
    
16. For IAM role, choose the role GlueSeDataBlogRole.
17. Choose Confirm.
    

This last step may take a couple of minutes to run.

When you review the data, you can see that sensitive data like phone numbers, email addresses, and SSNs are part of the customer comments.

Now let’s identify the sensitive data in the comments dataset and mask it.

18. On the Transform menu, choose Detect PII.
    

The AWS Glue sensitive data identification feature allows you to find sensitive data at the row and column level, which covers a diverse number of use cases. For this post, because we scan comments made by customers, we use the row-level scan.

19. On the Transform tab, select Find sensitive data in each row.
20. For Types of sensitive information to detect, select Select specific patterns.
    

Now we need to select the entities or patterns that are going to be identified by the job.

21. For Selected patterns, choose Browse.
    
22. Select the following patterns:
    1. Credit Card
    2. Email Address
    3. IP Address
    4. Mac Address
    5. Person’s Name
    6. Social Security Number (SSN)
    7. US Passport
    8. US Phone
    9. US/Canada bank account
23. Choose Confirm.
    
    

After the sensitive data is identified, AWS Glue offers two options:

• Enrich data with detection results – Adds a new column to the dataset with the list of the entities or patterns that were identified in that specific row.
• Redact detected text – Replaces the sensitive data with a custom string. For this post, we use the redaction option.

24. For Actions, select Redact detected text.
25. For Replacement text, enter ####.
    

Let’s see how the dataset looks now.

26. Check the result data on the Data preview tab.
    

As you can see, the majority of the sensitive data was redacted, but there is a number on row 11 that isn’t masked. This is because it’s a Canadian permanent resident number, and this pattern isn’t part of the ones that the sensitive data identification feature offers. However, we can add a custom pattern to identify this number.

27. On the Transform tab, for Selected patterns, choose Create new.
    

This action opens the Create detection pattern window, where we create the custom pattern to identify the Canadian permanent resident number.

28. For Pattern name, enter Can_PR_Number.
29. For Expression, enter the regular expression [P]+[D]+[0]\d\d\d\d\d\d
30. Choose Validate.
31. Wait until you get the validation message, then choose Create pattern.
    

Now you can see the new pattern listed under Custom patterns.

32. On the AWS Glue Studio Console, for Selected patterns, choose Browse.
    

Now you can see Can_PR_Number as part of the pattern list.

33. Select Can_PR_Number and choose Confirm.
    

On the Data preview tab, you can see that the Canadian permanent resident number has been redacted.

Let’s add a destination for the dataset with redacted information.

34. On the Target menu, choose Amazon S3.
    
35. On the Data target properties -S3 tab, for Format, choose Parquet.
36. For S3 Target Location, enter s3://glue-sendata-blog-<YOUR ACCOUNT ID>/output/redacted_comments/.
37. For Data Catalog update options, select Create a table in the Data Catalog and on subsequent runs, update the schema and add new partitions.
38. For Database, choose gluesenblog.
39. For Table name, enter custcomredacted.
    
40. Choose Save, then choose Run.
    

You can view the job run details on the Runs tab.

Wait until the job is complete.

Query the dataset

Now let’s see what the final dataset looks like. To do so, we query the data with Athena. As part of this post, we assume that a query result location for Athena is already configured; if not, refer to Working with query results, recent queries, and output files.

1. On the Athena console, open the query editor.
2. For Database, choose the gluesenblog database.
   
3. Run the following query:

   SELECT * FROM "gluesenblog"."custcomredacted" limit 15;

4. Verify the results; you can observe that all the sensitive data is redacted.
   

Clean up

To avoid incurring future charges, and to clean up unused roles and policies, delete the resources you created: Datasets, CloudFormation stack, S3 bucket, AWS Glue job, AWS Glue database, and AWS Glue table.

Conclusion

AWS Glue sensitive data detection offers an easy way to identify and process private data, without coding. This feature allows you to detect and redact sensitive data when it’s ingested into a data lake, enforcing data privacy before the data is available to data consumers. AWS Glue sensitive data detection is generally available in all Regions that support AWS Glue.

To learn more and get started using AWS Glue sensitive data detection, refer to Detect and process sensitive data.

About the author

Leonardo Gómez is a Senior Analytics Specialist Solutions Architect at AWS. Based in Toronto, Canada, he has over a decade of experience in data management, helping customers around the globe address their business and technical needs. Connect with him on LinkedIn

Post Syndicated from Leonardo Gomez original https://aws.amazon.com/blogs/big-data/making-etl-easier-with-aws-glue-studio/

AWS Glue Studio is an easy-to-use graphical interface that speeds up the process of authoring, running, and monitoring extract, transform, and load (ETL) jobs in AWS Glue. The visual interface allows those who don’t know Apache Spark to design jobs without coding experience and accelerates the process for those who do.

AWS Glue Studio was designed to help you create ETL jobs easily. After you design a job in the graphical interface, it generates Apache Spark code for you, abstracting users from the challenges of coding. When the job is ready, you can run it and monitor the job status using the integrated UI.

AWS Glue Studio supports different types of data sources, both structured and semi-structured, and offers data processing in real time and batch. You can extract data from sources like Amazon Simple Storage Service (Amazon S3), Amazon Relational Database Service (Amazon RDS), Amazon Kinesis, and Apache Kafka. It also offers Amazon S3 and tables defined in the AWS Glue Data Catalog as destinations.

This post shows you how to create an ETL job to extract, filter, join, and aggregate data easily using AWS Glue Studio.

Overview of solution

To demonstrate how to create an ETL job using AWS Glue Studio, we use the Toronto parking tickets dataset, specifically the data about parking tickets issued in the city of Toronto in 2018, and the trials dataset, which contains all the information about the trials related to those parking tickets. The goal is to filter, join, and aggregate the two datasets to get the number of parking tickets handled per court in the city of Toronto during that year.

Prerequisites

For this walkthrough, you should have an AWS account. For this post, you launch the required AWS resources using AWS CloudFormation in the us-east-1 Region. If you haven’t signed up for AWS, complete the following tasks:

1. Create an account.
2. Create an AWS Identity and Access Management (IAM) user. For instructions, see Create an IAM User.

Important: If the AWS account you use to follow this guide uses AWS Lake Formation to manage permissions on the Glue data catalog, make sure that you log in as a user that is both a Data lake administrator and a Database creator, as described in the documentation.

Launching your CloudFormation stack

To create your resources for this use case, complete the following steps:

1. Launch your stack in us-east-1:
   
2. Select the I acknowledge that AWS CloudFormation might create IAM resources with custom names option.
3. Choose Create stack.

Launching this stack creates AWS resources. The following resources shown in the AWS CloudFormation output are the ones you need in the next steps:

• Key – Description
• AWSGlueStudioRole – IAM role to run AWS Glue jobs
• AWSGlueStudioS3Bucket – Name of the S3 bucket to store blog-related files
• AWSGlueStudioTicketsYYZDB – AWS Glue Data Catalog database
• AWSGlueStudioTableTickets – Data Catalog table to use as a source
• AWSGlueStudioTableTrials – Data Catalog table to use as a source
• AWSGlueStudioParkingTicketCount –Data Catalog table to use as the destination

Creating a job

A job is the AWS Glue component that allows the implementation of business logic to transform data as part of the ETL process. For more information, see Adding Jobs in AWS Glue.

To create an AWS Glue job using AWS Glue Studio, complete the following steps:

1. On the AWS Management Console, choose Services.
2. Under Analytics, choose AWS Glue.
   
3. In the navigation pane, choose AWS Glue Studio.
4. On the AWS Glue Studio home page, choose Create and manage jobs.
   

AWS Glue Studio supports different sources, including Amazon S3, Amazon RDS, Amazon Kinesis, and Apache Kafka. For this post, you use two AWS Glue tables as data sources and one S3 bucket as the destination.

5. In the Create Job section, select Blank graph.
6. Choose Create.
   

This takes you to the Visual Canvas to create an AWS Glue job.

7. Change the Job name from Untitled Job to YYZ-Tickets-Job.
   

You now have an AWS Glue job ready to filter, join, and aggregate data from two different sources.

Adding sources

For this post, you use two AWS Glue tables as data sources: Tickets and Trials, which the CloudFormation template created. The data is located in an external S3 bucket in Parquet format. To add these tables as sources, complete the following steps:

1. Choose the (+) icon.
2. On the Node properties tab, for Name, enter Tickets.
3. For Node type, choose S3.
   
4. On the Data Source properties -S3 tab, for Database, choose yyz-tickets.
5. For Table, choose tickets.
6. For Partition predicate (optional), leave blank.
   

Before adding the second data source to the ETL job, be sure that the node you just created isn’t selected.

7. Choose the (+) icon.
8. On the Node properties tab, for Name, enter Trials.
9. For Node type, choose S3.
   
10. On the Data Source properties -S3 tab, for Database, choose yyz-tickets.
11. For Table, choose trials.
12. For Partition predicate (optional), leave blank.
    

You now have two AWS Glue tables as the data sources for the AWS Glue job.

Adding transforms

A transform is the AWS Glue Studio component were the data is modified. You have the option of using different transforms that are part of this service or custom code. To add transforms, complete the following steps:

1. Choose the Tickets node.
   
2. Choose the (+) icon.
   
   
3. On the Node properties tab, for Name, enter Ticket_Mapping.
4. For Node type, choose ApplyMapping.
5. For Node parents, choose Tickets.
   
6. On the Transform tab, change the ticket_number data type from decimal to int.
7. Drop the following columns:
   • Location1
   • Location2
   • Location3
   • Location4
   • Province

Now you add a second ApplyMapping transform to modify the Trials data source.

8. Choose the Trials data source node.
9. Choose the (+) icon.
   
10. On the Node properties tab, for Name, enter Trial_Mapping.
11. For Node type, choose ApplyMapping.
12. For Node parents, leave at default value (Trials).
    

13. On the Transform tab, change the parking_ticket_number data type from long to int.
    

Now that you have set the right data types and removed some of the columns, it’s time to join the data sources using the Join transform.

14. Choose the Ticket_Mapping transform.
15. Choose the (+) icon.
    
    
16. On the Node properties tab, for Name, enter Join_Ticket_Trial.
17. For Node type, choose Join.
18. For Node parents, choose Ticket_Mapping and Trial_Mapping.
19. On the Transform tab, for Join type, choose Inner join.
20. For Join conditions, choose Add condition.
21. For Ticket_Mapping, choose ticket_number.
22. For Trial_Mapping, choose parking_ticket_number.
    

Now the two data sources are joined by the ticket_number and parking_ticket_number columns.

Performing data aggregation

In this step, you do some data aggregation to see the number of tickets handled per court in Toronto.

AWS Glue Studio offers the option of adding custom code for those use cases that need a more complex transformation. For this post, we use PySpark code to do the data transformation. It contains Sparksql code and a combination of dynamic frames and data frames.

1. Choose the Join_Tickets_Trial transform.
2. Choose the (+) icon.
   
   
3. On the Node properties tab, for Name, enter Aggregate_Tickets.
4. For Node type, choose Custom transform.
5. For Node parents, leave Join_Ticket_Trial selected.
   
6. On the Transform tab, for Code block, change the function name from MyTransform to Aggregate_Tickets.
7. Enter the following code:

   selected = dfc.select(list(dfc.keys())[0]).toDF()
   selected.createOrReplaceTempView("ticketcount")
   totals = spark.sql("select court_location as location, infraction_description as infraction, count(infraction_code) as total  FROM ticketcount group by infraction_description, infraction_code, court_location order by court_location asc")
   results = DynamicFrame.fromDF(totals, glueContext, "results")
   return DynamicFrameCollection({"results": results}, glueContext)
   

   

After adding the custom transformation to the AWS Glue job, you want to store the result of the aggregation in the S3 bucket. To do this, you need a Select from collection transform to read the output from the Aggregate_Tickets node and send it to the destination.

8. Choose the New node node.
   
9. Leave the Transform tab with the default values.
   
10. On the Node Properties tab, change the name of the transform to Select_Aggregated_Data.
11. Leave everything else with the default values.
    
12. Choose the Select_Aggregated_Data node.
    
13. Choose the (+) icon.
    
    
14. On the Node properties tab, for Name, enter Ticket_Count_Dest.
15. For Node type, choose S3 in the Data target section.
16. For Node parents, choose Select_Aggregated_Data.
    
17. On the Data Target Properties-S3 tab, for Format, choose Parquet.
18. For Compression Type, choose GZIP.
19. For S3 Target Location, enter s3://glue-studio-blog-{Your Account ID as a 12-digit number}/parking_tickets_count/.

The job should look like the following screenshot.

You now have three transforms to do data mapping, filtering, and aggregation.

Configuring the job

When the logic behind the job is complete, you must set the parameters for the job run. In this section, you configure the job by selecting components such as the IAM role and the AWS Glue version you use to run the job.

1. On the Job details tab, for Description, enter Glue Studio blog post job.
2. For IAM Role, choose AWSGlueStudioRole (which the CloudFormation template created).
   
3. For Job Bookmark, choose Disable.
4. For Number of retries, optionally enter 1.
   
5. Choose Save.
   
6. When the job is saved, choose Run.
   

Monitoring the job

AWS Glue Studio offers a job monitoring dashboard that provides comprehensive information about your jobs. You can get job statistics and see detailed info about the job and the job status when running.

1. In the AWS Glue Studio navigation panel, choose Monitoring.
2. Choose the entry with the job name YYZ-Tickets_Job.
3. For get more details about the job run, choose View run details.
   
4. Wait until Run Status changes to Succeeded.
   

You can verify that the job ran successfully on the Amazon Athena console.

5. On the Athena console, choose the yyz-tickets database.
6. Choose the … icon next to the parking_tickets_count table (which the CloudFormation template created).

For more information about creating AWS Glue tables, see Defining Tables in the AWS Glue Data Catalog.

7. Choose Preview table.
   

As you can see in the following screenshot, the information that the job generated is available and you can query the number of tickets types per court issued in the city of Toronto in 2018.

Cleaning up

To avoid incurring future charges and to clean up unused roles and policies, delete the resources you created: the CloudFormation stack, S3 bucket, and AWS Glue job.

Conclusion

In this post, you learned how to use AWS Glue Studio to create an ETL job. You can use AWS Glue Studio to speed up the ETL job creation process and allow different personas to transform data without any previous coding experience. For more information about AWS Glue Studio, see the AWS Glue Studio documentation and What’s New with AWS.

About the author

Leonardo Gómez is a Senior Analytics Specialist Solution Architect at AWS. Based in Toronto, Canada, He works with customers across Canada to design and build big data solutions.