Use a number of bookmark keys in AWS Glue JDBC jobs

AWS Glue is a serverless knowledge integrating service that you need to use to catalog knowledge and put together for analytics. With AWS Glue, you may uncover your knowledge, develop scripts to remodel sources into targets, and schedule and run extract, rework, and cargo (ETL) jobs in a serverless atmosphere. AWS Glue jobs are answerable for operating the information processing logic.

One vital function of AWS Glue jobs is the flexibility to make use of bookmark keys to course of knowledge incrementally. When an AWS Glue job is run, it reads knowledge from an information supply and processes it. A number of columns from the supply desk will be specified as bookmark keys. The column ought to have sequentially growing or lowering values with out gaps. These values are used to mark the final processed document in a batch. The subsequent run of the job resumes from that time. This lets you course of massive quantities of knowledge incrementally. With out job bookmark keys, AWS Glue jobs must reprocess all the information throughout each run. This may be time-consuming and expensive. By utilizing bookmark keys, AWS Glue jobs can resume processing from the place they left off, saving time and lowering prices.

This put up explains learn how to use a number of columns as job bookmark keys in an AWS Glue job with a JDBC connection to the supply knowledge retailer. It additionally demonstrates learn how to parameterize the bookmark key columns and desk names within the AWS Glue job connection choices.

This put up is concentrated in direction of architects and knowledge engineers who design and construct ETL pipelines on AWS. You might be anticipated to have a primary understanding of the AWS Administration Console, AWS Glue, Amazon Relational Database Service (Amazon RDS), and Amazon CloudWatch logs.

Answer overview

To implement this resolution, we full the next steps:

1. Create an Amazon RDS for PostgreSQL occasion.
2. Create two tables and insert pattern knowledge.
3. Create and run an AWS Glue job to extract knowledge from the RDS for PostgreSQL DB occasion utilizing a number of job bookmark keys.
4. Create and run a parameterized AWS Glue job to extract knowledge from completely different tables with separate bookmark keys

The next diagram illustrates the elements of this resolution.

Deploy the answer

For this resolution, we offer an AWS CloudFormation template that units up the providers included within the structure, to allow repeatable deployments. This template creates the next assets:

• An RDS for PostgreSQL occasion
• An Amazon Easy Storage Service (Amazon S3) bucket to retailer the information extracted from the RDS for PostgreSQL occasion
• An AWS Identification and Entry Administration (IAM) position for AWS Glue
• Two AWS Glue jobs with job bookmarks enabled to incrementally extract knowledge from the RDS for PostgreSQL occasion

To deploy the answer, full the next steps:

1. Select  to launch the CloudFormation stack:
2. Enter a stack title.
3. Choose I acknowledge that AWS CloudFormation would possibly create IAM assets with customized names.
4. Select Create stack.
5. Wait till the creation of the stack is full, as proven on the AWS CloudFormation console.
   
6. When the stack is full, copy the AWS Glue scripts to the S3 bucket job-bookmark-keys-demo-<accountid>.
7. Open AWS CloudShell.
8. Run the next instructions and change <accountid> together with your AWS account ID:

aws s3 cp s3://aws-blogs-artifacts-public/artifacts/BDB-2907/glue/scenario_1_job.py s3://job-bookmark-keys-demo-<accountid>/scenario_1_job.py
aws s3 cp s3://aws-blogs-artifacts-public/artifacts/BDB-2907/glue/scenario_2_job.py s3://job-bookmark-keys-demo-<accountid>/scenario_2_job.py

Add pattern knowledge and run AWS Glue jobs

On this part, we hook up with the RDS for PostgreSQL occasion by way of AWS Lambda and create two tables. We additionally insert pattern knowledge into each the tables.

1. On the Lambda console, select Capabilities within the navigation pane.
2. Select the perform LambdaRDSDDLExecute.
   
3. Select Take a look at and select Invoke for the Lambda perform to insert the information.

The 2 tables product and tackle shall be created with pattern knowledge, as proven within the following screenshot.

Run the multiple_job_bookmark_keys AWS Glue job

We run the multiple_job_bookmark_keys AWS Glue job twice to extract knowledge from the product desk of the RDS for PostgreSQL occasion. Within the first run, all the prevailing information shall be extracted. Then we insert new information and run the job once more. The job ought to extract solely the newly inserted information within the second run.

1. On the AWS Glue console, select Jobs within the navigation pane.
2. Select the job multiple_job_bookmark_keys.
3. Select Run to run the job and select the Runs tab to watch the job progress.
4. Select the Output logs hyperlink underneath CloudWatch logs after the job is full.
   
5. Select the log stream within the subsequent window to see the output logs printed.
   
   The AWS Glue job extracted all information from the supply desk product. It retains observe of the final mixture of values within the columns product_id and model.Subsequent, we run one other Lambda perform to insert a brand new document. The product_id 45 already exists, however the inserted document could have a brand new model as 2, making the mix sequentially growing.
6. Run the LambdaRDSDDLExecute_incremental Lambda perform to insert the brand new document within the product desk.
   
7. Run the AWS Glue job multiple_job_bookmark_keys once more after you insert the document and await it to succeed.
8. Select the Output logs hyperlink underneath CloudWatch logs.
9. Select the log stream within the subsequent window to see solely the newly inserted document printed.

The job extracts solely these information which have a mixture larger than the beforehand extracted information.

Run the parameterised_job_bookmark_keys AWS Glue job

We now run the parameterized AWS Glue job that takes the desk title and bookmark key column as parameters. We run this job to extract knowledge from completely different tables sustaining separate bookmarks.

The primary run shall be for the tackle desk with bookmarkkey as address_id. These are already populated with the job parameters.

1. On the AWS Glue console, select Jobs within the navigation pane.
2. Select the job parameterised_job_bookmark_keys.
3. Select Run to run the job and select the Runs tab to watch the job progress.
4. Select the Output logs hyperlink underneath CloudWatch logs after the job is full.
   
5. Select the log stream within the subsequent window to see all information from the tackle desk printed.
   
6. On the Actions menu, select Run with parameters.
7. Increase the Job parameters part.
8. Change the job parameter values as follows:
   • Key --bookmarkkey with worth product_id
   • Key --table_name with worth product
   • The S3 bucket title is unchanged (job-bookmark-keys-demo-<accountnumber>)
9. Select Run job to run the job and select the Runs tab to watch the job progress.
10. Select the Output logs hyperlink underneath CloudWatch logs after the job is full.
11. Select the log stream to see all of the information from the product desk printed.
    

The job maintains separate bookmarks for every of the tables when extracting the information from the supply knowledge retailer. That is achieved by including the desk title to the job title and transformation contexts within the AWS Glue job script.

Clear up

To keep away from incurring future fees, full the next steps:

1. On the Amazon S3 console, select Buckets within the navigation pane.
2. Choose the bucket with job-bookmark-keys in its title.
3. Select Empty to delete all of the recordsdata and folders in it.
4. On the CloudFormation console, select Stacks within the navigation pane.
5. Choose the stack you created to deploy the answer and select Delete.

Conclusion

This put up demonstrated passing a couple of column of a desk as jobBookmarkKeys in a JDBC connection to an AWS Glue job. It additionally defined how one can a parameterized AWS Glue job to extract knowledge from a number of tables whereas protecting their respective bookmarks. As a subsequent step, you may take a look at the incremental knowledge extract by altering knowledge within the supply tables.

Concerning the Authors

Durga Prasad is a Sr Lead Advisor enabling clients construct their Knowledge Analytics options on AWS. He’s a espresso lover and enjoys taking part in badminton.

Murali Reddy is a Lead Advisor at Amazon Net Companies (AWS), serving to clients construct and implement knowledge analytics resolution. When he’s not working, Murali is an avid bike rider and loves exploring new locations.