topic Re: How to write Change Data from Delta Lake to aws dynamodb in Data Engineering

How to write Change Data from Delta Lake to aws dynamodb

hari — Mon, 14 Feb 2022 12:47:14 GMT

Is there some direct way to write data from DeltaLake to AWS DynamoDB.

If there is none, Is there any way to do the same.

Re: How to write Change Data from Delta Lake to aws dynamodb

AmanSehgal — Mon, 14 Feb 2022 13:15:27 GMT

This is something that might be achievable via delta live tables and delta sharing in future.

But for now, what you can do is track changes in delta tables when you do an upsert.

To know more you'll have to describe how your delta tables are getting updated? What's the source, frequency of data operations in delta lake?

Re: How to write Change Data from Delta Lake to aws dynamodb

hari — Mon, 14 Feb 2022 14:12:43 GMT

The data will be updated as batches nightly

Re: How to write Change Data from Delta Lake to aws dynamodb

AmanSehgal — Mon, 14 Feb 2022 14:43:08 GMT

A way to track INSERT/UPDATE/DELETE would be to split out your upsert process as a 3 part process.

Step1: Include an update timestamp field in your target table.

Step2: In your incoming refresh dataset, add a update timestamp column and set it to current timestamp.

UPDATE count

Step3: Run upsert job with just whenMatchedUpdate clause. Count rows that have current timestamp for update.

Also, get an extract of the rows with current timestamp - these are the rows that you'll UPDATE in DyanmoDB

INSERT count

Step5: Get the row count of table - RC1.

Step6: Run upsert job with just whenNotMatchedInsert clause. Get row count of table after insert - RC2

INSERT count = RC2-RC1

Get an extract of the rows with current timestamp and perform a left-anti join with UPDATE df - these are the rows that you'll INSERT in DyanmoDB

DELETE count

Step7: Get the row count of table - RC1.

Step8: Run upsert job with just whenNotMatchedDelete clause. Get row count of table after insert - RC2

DELETE count = RC1-RC2

From your source data, there would be a way to identify the records that are to be deleted. Grab those indices/primary keys to delete records.

Re: How to write Change Data from Delta Lake to aws dynamodb

-werners- — Mon, 14 Feb 2022 15:27:54 GMT

there is change data feed on delta tables:

https://docs.databricks.com/delta/delta-change-data-feed.html

Re: How to write Change Data from Delta Lake to aws dynamodb

hari — Mon, 14 Feb 2022 16:04:56 GMT

@Aman Sehgal Thanks for the reply

Are you suggesting collecting the update/delete set and then manually writing to dynamodb?

Re: How to write Change Data from Delta Lake to aws dynamodb

hari — Mon, 14 Feb 2022 16:06:47 GMT

@Werner Stinckens Thanks for the reply

I have gone through the CDF docs. But was wondering if there is some connector available to write data from Delta Table directly to dynamodb.

Eg: Read data from Delta table with change data mode on and write to dynamodb directly.

Or are you suggesting to get the Change Data and then write to DynamoDB

Re: How to write Change Data from Delta Lake to aws dynamodb

-werners- — Mon, 14 Feb 2022 16:17:31 GMT

I suggest fetching the change data and write that to dynamodb indeed.

Re: How to write Change Data from Delta Lake to aws dynamodb

jose_gonzalez — Tue, 15 Mar 2022 00:55:55 GMT

Hi @Harikrishnan P H ,

Did @Werner Stinckens reply help you to resolved your issue? if yes, please mark it as best. if not, please let us know.