Automate large-scale data validation using Amazon EMR and Apache Griffin

Many enterprises are migrating their on-premises data stores to the AWS Cloud. During data migration, a key requirement is to validate all the data that has been moved from source to target. This data validation is a critical step, and if not done correctly, may result in the failure of the entire project. However, developing custom solutions to determine migration accuracy by comparing the data between the source and target can often be time-consuming.

In this post, we walk through a step-by-step process to validate large datasets after migration using a configuration-based tool using Amazon EMR and the Apache Griffin open source library. Griffin is an open source data quality solution for big data, which supports both batch and streaming mode.

In today’s data-driven landscape, where organizations deal with petabytes of data, the need for automated data validation frameworks has become increasingly critical. Manual validation processes are not only time-consuming but also prone to errors, especially when dealing with vast volumes of data. Automated data validation frameworks offer a streamlined solution by efficiently comparing large datasets, identifying discrepancies, and ensuring data accuracy at scale. With such frameworks, organizations can save valuable time and resources while maintaining confidence in the integrity of their data, thereby enabling informed decision-making and enhancing overall operational efficiency.

The following are standout features for this framework:

• Utilizes a configuration-driven framework
• Offers plug-and-play functionality for seamless integration
• Conducts count comparison to identify any disparities
• Implements robust data validation procedures
• Ensures data quality through systematic checks
• Provides access to a file containing mismatched records for in-depth analysis
• Generates comprehensive reports for insights and tracking purposes

Solution overview

This solution uses the following services:

• Amazon Simple Storage Service (Amazon S3) or Hadoop Distributed File System (HDFS) as the source and target.
• Amazon EMR to run the PySpark script. We use a Python wrapper on top of Griffin to validate data between Hadoop tables created over HDFS or Amazon S3.
• AWS Glue to catalog the technical table, which stores the results of the Griffin job.
• Amazon Athena to query the output table to verify the results.

We use tables that store the count for each source and target table and also create files that show the difference of records between source and target.

The following diagram illustrates the solution architecture.

In the depicted architecture and our typical data lake use case, our data either resides n Amazon S3 or is migrated from on premises to Amazon S3 using replication tools such as AWS DataSync or AWS Database Migration Service (AWS DMS). Although this solution is designed to seamlessly interact with both Hive Metastore and the AWS Glue Data Catalog, we use the Data Catalog as our example in this post.

This framework operates within Amazon EMR, automatically running scheduled tasks on a daily basis, as per the defined frequency. It generates and publishes reports in Amazon S3, which are then accessible via Athena. A notable feature of this framework is its capability to detect count mismatches and data discrepancies, in addition to generating a file in Amazon S3 containing full records that didn’t match, facilitating further analysis.

In this example, we use three tables in an on-premises database to validate between source and target : balance_sheet, covid, and survery_financial_report.

Prerequisites

Before getting started, make sure you have the following prerequisites:

• An AWS account with access to AWS services
• A VPC with private subnet
• An Amazon Elastic Compute Cloud (Amazon EC2) key pair
• An AWS Identity and Access Management (IAM) policy for AWS Secrets Manager permissions
• IAM roles EMR_DefaultRole and EMR_EC2_DefaultRole available in your account
• A SQL editor to connect to the source database
• The AWS Command Line Interface (AWS CLI) set up to run AWS commands locally:
  • Create an IAM policy have access to specific S3 buckets with actions s3:PutObject and s3:GetObject , and an IAM policy to use an AWS CloudFormation template with desired permissions
  • Create an IAM user
  • Create an IAM role and attach the IAM S3 policy and CloudFormation policies
  • Configure ~/.aws/config to use the IAM role and user

Deploy the solution

To make it straightforward for you to get started, we have created a CloudFormation template that automatically configures and deploys the solution for you. Complete the following steps:

1. Create an S3 bucket in your AWS account called bdb-3070-griffin-datavalidation-blog-${AWS::AccountId}-${AWS::Region} (provide your AWS account ID and AWS Region).
2. Unzip the following file to your local system.
3. After unzipping the file to your local system, change <bucket name> to the one you created in your account (bdb-3070-griffin-datavalidation-blog-${AWS::AccountId}-${AWS::Region}) in the following files:
   1. bootstrap-bdb-3070-datavalidation.sh
   2. Validation_Metrics_Athena_tables.hql
   3. datavalidation/totalcount/totalcount_input.txt
   4. datavalidation/accuracy/accuracy_input.txt
4. Upload all the folders and files in your local folder to your S3 bucket:

   aws s3 cp . s3://<bucket_name>/ --recursive

5. Run the following CloudFormation template in your account.

The CloudFormation template creates a database called griffin_datavalidation_blog and an AWS Glue crawler called griffin_data_validation_blog on top of the data folder in the .zip file.

6. Choose Next.
   
7. Choose Next again.
8. On the Review page, select I acknowledge that AWS CloudFormation might create IAM resources with custom names.
9. Choose Create stack.

You can view the stack outputs on the AWS Management Console or by using the following AWS CLI command:

aws cloudformation describe-stacks --stack-name <stack-name> --region us-east-1 --query Stacks[0].Outputs

10. Run the AWS Glue crawler and verify that six tables have been created in the Data Catalog.
11. Run the following CloudFormation template in your account.

This template creates an EMR cluster with a bootstrap script to copy Griffin-related JARs and artifacts. It also runs three EMR steps:

• Create two Athena tables and two Athena views to see the validation matrix produced by the Griffin framework
• Run count validation for all three tables to compare the source and target table
• Run record-level and column-level validations for all three tables to compare between the source and target table

12. For SubnetID, enter your subnet ID.
13. Choose Next.
    
14. Choose Next again.
15. On the Review page, select I acknowledge that AWS CloudFormation might create IAM resources with custom names.
16. Choose Create stack.

You can view the stack outputs on the console or by using the following AWS CLI command:

aws cloudformation describe-stacks --stack-name <stack-name> --region us-east-1 --query Stacks[0].Outputs

It takes approximately 5 minutes for the deployment to complete. When the stack is complete, you should see the EMRCluster resource launched and available in your account.

When the EMR cluster is launched, it runs the following steps as part of the post-cluster launch:

• Bootstrap action – It installs the Griffin JAR file and directories for this framework. It also downloads sample data files to use in the next step.
• Athena_Table_Creation – It creates tables in Athena to read the result reports.
• Count_Validation – It runs the job to compare the data count between source and target data from the Data Catalog table and stores the results in an S3 bucket, which will be read via an Athena table.
• Accuracy – It runs the job to compare the data rows between the source and target data from the Data Catalog table and store the results in an S3 bucket, which will be read via the Athena table.

When the EMR steps are complete, your table comparison is done and ready to view in Athena automatically. No manual intervention is needed for validation.

Validate data with Python Griffin

When your EMR cluster is ready and all the jobs are complete, it means the count validation and data validation are complete. The results have been stored in Amazon S3 and the Athena table is already created on top of that. You can query the Athena tables to view the results, as shown in the following screenshot.

The following screenshot shows the count results for all tables.

The following screenshot shows the data accuracy results for all tables.

The following screenshot shows the files created for each table with mismatched records. Individual folders are generated for each table directly from the job.

Every table folder contains a directory for each day the job is run.

Within that specific date, a file named __missRecords contains records that do not match.

The following screenshot shows the contents of the __missRecords file.

Clean up

To avoid incurring additional charges, complete the following steps to clean up your resources when you’re done with the solution:

1. Delete the AWS Glue database griffin_datavalidation_blog and drop the database griffin_datavalidation_blog cascade.
2. Delete the prefixes and objects you created from the bucket bdb-3070-griffin-datavalidation-blog-${AWS::AccountId}-${AWS::Region}.
3. Delete the CloudFormation stack, which removes your additional resources.

Conclusion

This post showed how you can use Python Griffin to accelerate the post-migration data validation process. Python Griffin helps you calculate count and row- and column-level validation, identifying mismatched records without writing any code.

For more information about data quality use cases, refer to Getting started with AWS Glue Data Quality from the AWS Glue Data Catalog and AWS Glue Data Quality.

About the Authors

Dipal Mahajan serves as a Lead Consultant at Amazon Web Services, providing expert guidance to global clients in developing highly secure, scalable, reliable, and cost-efficient cloud applications. With a wealth of experience in software development, architecture, and analytics across diverse sectors such as finance, telecom, retail, and healthcare, he brings invaluable insights to his role. Beyond the professional sphere, Dipal enjoys exploring new destinations, having already visited 14 out of 30 countries on his wish list.

Akhil is a Lead Consultant at AWS Professional Services. He helps customers design & build scalable data analytics solutions and migrate data pipelines and data warehouses to AWS. In his spare time, he loves travelling, playing games and watching movies.

Ramesh Raghupathy is a Senior Data Architect with WWCO ProServe at AWS. He works with AWS customers to architect, deploy, and migrate to data warehouses and data lakes on the AWS Cloud. While not at work, Ramesh enjoys traveling, spending time with family, and yoga.