Skip to content
By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post
About re:Post

Glue ETL Loadtimestamp bookmark

0

I'm running a simple test from Redshift to Redshift using JDBC connector and Glue ETL. Objective is to test JOB BOOKMARK feature. I don't have a primary key defined in source, so using LoadTimestamp which increments as I insert new records in source table. However, there would be gaps in time as records are being inserted manually for testing. When I run the job and it does not load incrementally, it reloads all the records in the source. Job Bookmark is enabled. Below is the entire script. What am I doing wrong?

import sys from awsglue.transforms import * from awsglue.utils import getResolvedOptions from pyspark.context import SparkContext from awsglue.context import GlueContext from awsglue.job import Job from awsglue import DynamicFrame

args = getResolvedOptions(sys.argv, ['JOB_NAME']) sc = SparkContext() glueContext = GlueContext(sc) spark = glueContext.spark_session job = Job(glueContext) job.init(args['JOB_NAME'], args)

Script generated for node Amazon Redshift

AmazonRedshift_node1746122516877 = glueContext.create_dynamic_frame.from_options(connection_type="redshift", connection_options={"redshiftTmpDir": "s3://aws-glue-assets-590183828039-us-east-1/temporary/", "useConnectionProperties": "true", "dbtable": "analytics.pl_demoemployee", "connectionName": "DC-Redshift connection"}, transformation_ctx="AmazonRedshift_node1746122516877", additional_options={"jobBookmarkKeys": ["loadtimestamp"], "jobBookmarkKeysSortOrder": "desc"} )

Script generated for node Amazon Redshift

AmazonRedshift_node1746122739660 = glueContext.write_dynamic_frame.from_options(frame=AmazonRedshift_node1746122516877, connection_type="redshift", connection_options={"redshiftTmpDir": "s3://aws-glue-assets-590183828039-us-east-1/temporary/", "useConnectionProperties": "true", "dbtable": "analytics.pl_demoemployee2", "connectionName": "DC-Redshift connection", "preactions": "CREATE TABLE IF NOT EXISTS analytics.pl_demoemployee2 (empid INTEGER, empname VARCHAR, loadtimestamp TIMESTAMP);"}, transformation_ctx="AmazonRedshift_node1746122739660")

job.commit()

Topics
AnalyticsComputeDatabase
Tags
AnalyticsExtract Transform & Load DataAWS GlueAmazon RedshiftAWS Batch
Language
English

asked a year ago138 views

1 Answer
0

Based on your script and description, it appears that you're using the LoadTimestamp column as a job bookmark key. While this approach can work, there are a few things to consider and potential adjustments to make:

  1. Ensure that the LoadTimestamp column is indeed incrementing for new records. If it's not consistently increasing, it may cause issues with the bookmark functionality. If user-defined bookmarks keys are used, they must each be strictly monotonically increasing or decreasing.

  2. Consider adding a primary key or a unique identifier to your table if possible, as this can make bookmarking more reliable.

  3. You're using "desc" as the sort order for the bookmark key. This means it will process the most recent records first. Consider changing this to "asc" to process older records first.

  4. Remember that job bookmarks work best with monotonically increasing values. If LoadTimestamp has significant isn't strictly increasing, it might cause unexpected behavior with the bookmark feature.

If you've verified all these points and the issue persists, in order troubleshoot further, please feel free to open a support case with AWS using the following link. We will be happy to assist you.

Sources

  1. Job Bookmark without primary key as bookmark key | AWS re:Post
  2. Tracking processed data using job bookmarks
  3. Using job bookmarks

answered a year ago

AWS
SUPPORT ENGINEER

revised a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Relevant content