AWS Glue job creates duplicate record in Athena

Question

I have used AWS glue studio. I picked all the options that I needed from the Glue Studio UI. Let me try to explain little more and shared default job script code in the bottom..

Created a Connection with AWS RDS in the glue.
Created a database education_earnings_dev in the Glue Data catalog,
Created a crawler in the glue to sync courses from the RDS which created a new table courses under database education_earnings_dev.
Created a Glue job in the AWS Glue Studio to create a table inside Athena. This process will save Parquet formatted data file inside S3.

Everything works fine what I have wanted. The issue is.. example - RDS courses table has 2 rows with id=1 and id=2. So, glue job created two files in S3 and created the table in Athena after running for the first time. I can see those rows in Athena. But when I run the Job one more time. I can see 4 rows in Athena table . There are 2 id=1 and 2 id=2 (Duplicate records). If I run one more time, there will be more duplicates. How can I avoid this?

    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
    
    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 Courses AWS Glue Data Catalog
    CoursesAWSGlueDataCatalog_node1639083038099 = (
        glueContext.create_dynamic_frame.from_catalog(
            database="education_earnings_dev",
            table_name="ebdb_courses",
            transformation_ctx="CoursesAWSGlueDataCatalog_node1639083038099",
        )
    )
    
    # Script generated for node Amazon S3
    AmazonS3_node1639084410044 = glueContext.getSink(
        path="s3://orca-education-api/big-data/dev/education_earning/source/courses/",
        connection_type="s3",
        updateBehavior="UPDATE_IN_DATABASE",
        partitionKeys=[],
        compression="snappy",
        enableUpdateCatalog=True,
        transformation_ctx="AmazonS3_node1639084410044",
    )
    AmazonS3_node1639084410044.setCatalogInfo(
        catalogDatabase="education_earnings_dev", catalogTableName="courses"
    )
    AmazonS3_node1639084410044.setFormat("glueparquet")
    AmazonS3_node1639084410044.writeFrame(CoursesAWSGlueDataCatalog_node1639083038099)
    job.commit()

Final step in glue job where I defined the s3 location and table name

user_2570424 · Answer

You can use Job Bookmarks. It's just a predefined variable that you need to send in your Glue:Job creation.

Tracking Processed Data Using Job Bookmarks

AWS Glue version	Amazon S3 source formats
Version 0.9	JSON, CSV, Apache Avro, XML
Version 1.0 and later	JSON, CSV, Apache Avro, XML, Parquet, ORC

The command that needs to be sent is inside DefaultArguments:

'--job-bookmark-option': 'job-bookmark-enable'

full example:

GlueJob:
        Type: 'AWS::Glue::Job'
        Properties:
            Role: !Ref AWSGlueServiceRole
            Description: Job created with CloudFormation
            GlueVersion: '3.0'
            Command:
                Name: glueetl
                PythonVersion: '3'
                ScriptLocation: !Sub 's3://${ArtifactBucketName}/scripts/GlueCrawlerDynamodb.py'
            ExecutionProperty:
                MaxConcurrentRuns: 2
            DefaultArguments:
                {
                    '--job-bookmark-option': 'job-bookmark-enable',
                }
            MaxRetries: 3
            WorkerType: 'G.1X'
            NumberOfWorkers: 5
            Name: !Join
                - '-'
                - - !Ref 'AWS::StackName'
                  - etljob

Documentation talking about: Special Parameters Used by Amazon Glue

AWS Glue job creates duplicate record in Athena

1 Answers