Mechanically detect Personally Identifiable Info in Amazon Redshift utilizing AWS Glue

With the exponential progress of knowledge, firms are dealing with big volumes and all kinds of knowledge together with personally identifiable info (PII). PII is a authorized time period pertaining to info that may establish, contact, or find a single individual. Figuring out and defending delicate knowledge at scale has grow to be more and more advanced, costly, and time-consuming. Organizations have to stick to knowledge privateness, compliance, and regulatory necessities resembling GDPR and CCPA, and it’s essential to establish and defend PII to keep up compliance. You’ll want to establish delicate knowledge, together with PII resembling title, Social Safety Quantity (SSN), tackle, e-mail, driver’s license, and extra. Even after identification, it’s cumbersome to implement redaction, masking, or encryption of delicate knowledge at scale.

Many firms establish and label PII by handbook, time-consuming, and error-prone evaluations of their databases, knowledge warehouses and knowledge lakes, thereby rendering their delicate knowledge unprotected and susceptible to regulatory penalties and breach incidents.

On this put up, we offer an automatic answer to detect PII knowledge in Amazon Redshift utilizing AWS Glue.

Answer overview

With this answer, we detect PII in knowledge on our Redshift knowledge warehouse in order that the we take and defend the information. We use the next companies:

• Amazon Redshift is a cloud knowledge warehousing service that makes use of SQL to investigate structured and semi-structured knowledge throughout knowledge warehouses, operational databases, and knowledge lakes, utilizing AWS-designed {hardware} and machine studying (ML) to ship the most effective value/efficiency at any scale. For our answer, we use Amazon Redshift to retailer the information.
• AWS Glue is a serverless knowledge integration service that makes it easy to find, put together, and mix knowledge for analytics, ML, and software improvement. We use AWS Glue to find the PII knowledge that’s saved in Amazon Redshift.
• Amazon Easy Storage Providers (Amazon S3) is a storage service providing industry-leading scalability, knowledge availability, safety, and efficiency.

The next diagram illustrates our answer structure.

The answer consists of the next high-level steps:

1. Arrange the infrastructure utilizing an AWS CloudFormation template.
2. Load knowledge from Amazon S3 to the Redshift knowledge warehouse.
3. Run an AWS Glue crawler to populate the AWS Glue Information Catalog with tables.
4. Run an AWS Glue job to detect the PII knowledge.
5. Analyze the output utilizing Amazon CloudWatch.

Conditions

The sources created on this put up assume {that a} VPC is in place together with a non-public subnet and each their identifiers. This ensures that we don’t considerably change the VPC and subnet configuration. Due to this fact, we wish to arrange our VPC endpoints based mostly on the VPC and subnet we select to show it in.

Earlier than you get began, create the next sources as stipulations:

• An current VPC
• A non-public subnet in that VPC
• A VPC gateway S3 endpoint
• A VPC STS gateway endpoint

Arrange the infrastructure with AWS CloudFormation

To create your infrastructure with a CloudFormation template, full the next steps:

1. Open the AWS CloudFormation console in your AWS account.
2. Select Launch Stack:
   
3. Select Subsequent.
4. Present the next info:
   1. Stack title
   2. Amazon Redshift consumer title
   3. Amazon Redshift password
   4. VPC ID
   5. Subnet ID
   6. Availability Zones for the subnet ID
5. Select Subsequent.
   
6. On the following web page, select Subsequent.
7. Overview the small print and choose I acknowledge that AWS CloudFormation may create IAM sources.
8. Select Create stack.
9. Be aware the values for S3BucketName and RedshiftRoleArn on the stack’s Outputs tab.

Load knowledge from Amazon S3 to the Redshift Information warehouse

With the COPY command, we are able to load knowledge from recordsdata situated in a number of S3 buckets. We use the FROM clause to point how the COPY command locates the recordsdata in Amazon S3. You may present the thing path to the information recordsdata as a part of the FROM clause, or you’ll be able to present the placement of a manifest file that incorporates a listing of S3 object paths. COPY from Amazon S3 makes use of an HTTPS connection.

For this put up, we use a pattern private well being dataset. Load the information with the next steps:

1. On the Amazon S3 console, navigate to the S3 bucket created from the CloudFormation template and test the dataset.
2. Connect with the Redshift knowledge warehouse utilizing the Question Editor v2 by establishing a reference to the database you creating utilizing the CloudFormation stack together with the consumer title and password.

After you’re related, you need to use the next instructions to create the desk within the Redshift knowledge warehouse and replica the information.

3. Create a desk with the next question:

   CREATE TABLE personal_health_identifiable_information (
       mpi char (10),
       firstName VARCHAR (30),
       lastName VARCHAR (30),
       e-mail VARCHAR (75),
       gender CHAR (10),
       mobileNumber VARCHAR(20),
       clinicId VARCHAR(10),
       creditCardNumber VARCHAR(50),
       driverLicenseNumber VARCHAR(40),
       patientJobTitle VARCHAR(100),
       ssn VARCHAR(15),
       geo VARCHAR(250),
       mbi VARCHAR(50)    
   );

4. Load the information from the S3 bucket:

   COPY personal_health_identifiable_information
   FROM 's3://<S3BucketName>/personal_health_identifiable_information.csv'
   IAM_ROLE '<RedshiftRoleArn>'
   CSV
   delimiter ','
   area '<aws area>'
   IGNOREHEADER 1;

Present values for the next placeholders:

• RedshiftRoleArn – Find the ARN on the CloudFormation stack’s Outputs tab
• S3BucketName – Substitute with the bucket title from the CloudFormation stack
• aws area – Change to the Area the place you deployed the CloudFormation template

4. To confirm the information was loaded, run the next command:

   SELECT * FROM personal_health_identifiable_information LIMIT 10;

Run an AWS Glue crawler to populate the Information Catalog with tables

On the AWS Glue console, choose the crawler that you simply deployed as a part of the CloudFormation stack with the title crawler_pii_db, then select Run crawler.

When the crawler is full, the tables within the database with the title pii_db are populated within the AWS Glue Information Catalog, and the desk schema seems to be like the next screenshot.

Run an AWS Glue job to detect PII knowledge and masks the corresponding columns in Amazon Redshift

On the AWS Glue console, select ETL Jobs within the navigation pane and find the detect-pii-data job to know its configuration. The fundamental and superior properties are configured utilizing the CloudFormation template.

The fundamental properties are as follows:

• Sort – Spark
• Glue model – Glue 4.0
• Language – Python

For demonstration functions, the job bookmarks choice is disabled, together with the auto scaling function.

We additionally configure superior properties relating to connections and job parameters.
To entry knowledge residing in Amazon Redshift, we created an AWS Glue connection that makes use of the JDBC connection.

We additionally present customized parameters as key-value pairs. For this put up, we sectionalize the PII into 5 totally different detection classes:

• common – PERSON_NAME, EMAIL, CREDIT_CARD
• hipaa – PERSON_NAME, PHONE_NUMBER, USA_SSN, USA_ITIN, BANK_ACCOUNT, USA_DRIVING_LICENSE, USA_HCPCS_CODE, USA_NATIONAL_DRUG_CODE, USA_NATIONAL_PROVIDER_IDENTIFIER, USA_DEA_NUMBER, USA_HEALTH_INSURANCE_CLAIM_NUMBER, USA_MEDICARE_BENEFICIARY_IDENTIFIER
• networking – IP_ADDRESS, MAC_ADDRESS
• united_states – PHONE_NUMBER, USA_PASSPORT_NUMBER, USA_SSN, USA_ITIN, BANK_ACCOUNT
• customized – Coordinates

In the event you’re making an attempt this answer from different international locations, you’ll be able to specify the customized PII fields utilizing the customized class, as a result of this answer is created based mostly on US areas.

For demonstration functions, we use a single desk and go it as the next parameter:

--table_name: table_name

For this put up, we title the desk personal_health_identifiable_information.

You may customise these parameters based mostly on the person enterprise use case.

Run the job and anticipate the Success standing.

The job has two targets. The primary purpose is to establish PII data-related columns within the Redshift desk and produce a listing of those column names. The second purpose is the obfuscation of knowledge in these particular columns of the goal desk. As part of the second purpose, it reads the desk knowledge, applies a user-defined masking operate to these particular columns, and updates the information within the goal desk utilizing a Redshift staging desk (stage_personal_health_identifiable_information) for the upserts.

Alternatively, you too can use dynamic knowledge masking (DDM) in Amazon Redshift to guard delicate knowledge in your knowledge warehouse.

Analyze the output utilizing CloudWatch

When the job is full, let’s overview the CloudWatch logs to know how the AWS Glue job ran. We will navigate to the CloudWatch logs by selecting Output logs on the job particulars web page on the AWS Glue console.

The job recognized each column that incorporates PII knowledge, together with customized fields handed utilizing the AWS Glue job delicate knowledge detection fields.

Clear up

To wash up the infrastructure and keep away from extra expenses, full the next steps:

1. Empty the S3 buckets.
2. Delete the endpoints you created.
3. Delete the CloudFormation stack through the AWS CloudFormation console to delete the remaining sources.

Conclusion

With this answer, you’ll be able to robotically scan the information situated in Redshift clusters utilizing an AWS Glue job, establish PII, and take mandatory actions. This might assist your group with safety, compliance, governance, and knowledge safety options, which contribute in the direction of the information safety and knowledge governance.

Concerning the Authors

Manikanta Gona is a Information and ML Engineer at AWS Skilled Providers. He joined AWS in 2021 with 6+ years of expertise in IT. At AWS, he’s centered on Information Lake implementations, and Search, Analytical workloads utilizing Amazon OpenSearch Service. In his spare time, he like to backyard, and go on hikes and biking along with his husband.

Denys Novikov is a Senior Information Lake Architect with the Skilled Providers group at Amazon Net Providers. He’s specialised within the design and implementation of Analytics, Information Administration and Large Information methods for Enterprise clients.

Anjan Mukherjee is a Information Lake Architect at AWS, specializing in massive knowledge and analytics options. He helps clients construct scalable, dependable, safe and high-performance functions on the AWS platform.