Glue Job with load data incrementally

Question

Hello guys, i'm doing a job with glue and i need a incremental load, who only take new or modified data.
I've tried this code below, but doesn't work, its processing all data again and again every time i start the job, and i'm with all data repeated.

```
node = glueContext.create_dynamic_frame.from_catalog(
    database="db",
    table_name="table",
    transformation_ctx="node",
    additional_options = {"jobBookmarkKeys":[""], "jobBookmarkKeysSortOrder":"asc"}
)
```

Can someone help me with this?

Accepted Answer

Hello,

Looking at your script, I notice that:

additional_options = {"jobBookmarkKeys":["REQUEST", "OPENDATE"], "jobBookmarkKeysSortOrder":"asc"}

Per documented at [1], we have:

Warning
	If user-defined bookmarks keys are used, they must each be strictly monotonically increasing or decreasing. When selecting additional fields for a compound key, fields for concepts like "minor versions" or "revision numbers" do not meet this criteria, since their values are reused throughout your dataset.

It seems to me, by guessing via the names, that the keys "REQUEST" and "OPENDATE" do not satisfy the requirements above.

A workaround is, in the source database, create an additional column or using the existing primary key, as long as they meet the requirements. Then use that column for "jobBookmarkKeys".

==========
References:

[1] - https://docs.aws.amazon.com/glue/latest/dg/programming-etl-connect-bookmarks.html

Answer

Hello,

For Glue job bookmark, there are prerequisites before using it, depending on use cases.

Looking at your codes, I can't tell if the requirements have been met, so just providing generic advice.

In case of S3 data source, please note the supported file formats are "JSON, CSV, Apache Avro, XML, Parquet, ORC" [1].

In case of JDBC data source, the following rules apply [1]:

- For each table, AWS Glue uses one or more columns as bookmark keys to determine new and processed data. The bookmark keys combine to form a single compound key.

- AWS Glue by default uses the primary key as the bookmark key, provided that it is sequentially increasing or decreasing (with no gaps).

- You can specify the columns to use as bookmark keys in your AWS Glue script. For more information about using Job bookmarks in AWS Glue scripts, see Using job bookmarks.

- AWS Glue doesn't support using columns with case-sensitive names as job bookmark keys.

Regarding your code:

additional_options = {"jobBookmarkKeys":[""], "jobBookmarkKeysSortOrder":"asc"}

please pick up a suitable column for jobBookmarkKeys, with requirements above. More sample scripts and usage are also available at [2], for your information.

============
References:

[1] - https://docs.aws.amazon.com/glue/latest/dg/monitor-continuations.html#monitor-continuations-implement

[2] - https://docs.aws.amazon.com/glue/latest/dg/programming-etl-connect-bookmarks.html

Glue Job with load data incrementally

Relevant content