Get began with Amazon DynamoDB zero-ETL integration with Amazon Redshift

We’re excited to announce the final availability (GA) of Amazon DynamoDB zero-ETL integration with Amazon Redshift, which allows you to run high-performance analytics in your DynamoDB knowledge in Amazon Redshift with little to no influence on manufacturing workloads operating on DynamoDB. As knowledge is written right into a DynamoDB desk, it’s seamlessly made accessible in Amazon Redshift, eliminating the necessity to construct and keep complicated knowledge pipelines.

Zero-ETL integrations facilitate point-to-point knowledge motion with out the necessity to create and handle knowledge pipelines. You possibly can create zero-ETL integration on an Amazon Redshift Serverless workgroup or Amazon Redshift provisioned cluster utilizing RA3 occasion varieties. You possibly can then run enhanced evaluation on this DynamoDB knowledge with the wealthy capabilities of Amazon Redshift, resembling high-performance SQL, built-in machine studying (ML) and Spark integrations, materialized views (MV) with automated and incremental refresh, knowledge sharing, and the flexibility to hitch knowledge throughout a number of knowledge shops and knowledge lakes.

The DynamoDB zero-ETL integration with Amazon Redshift has helped our prospects simplify their extract, remodel, and cargo (ETL) pipelines. The next is a sworn statement from Keith McDuffee, Director of DevOps at Verisk Analytics, a buyer who used zero-ETL integration with DynamoDB instead of their homegrown resolution and benefitted from the seamless replication that it offered:

“We have now dashboards constructed on prime of our transactional knowledge in Amazon Redshift. Earlier, we used our homegrown resolution to maneuver knowledge from DynamoDB to Amazon Redshift, however these jobs would typically trip and result in a number of operational burden and missed insights on Amazon Redshift. Utilizing the DynamoDB zero-ETL integration with Amazon Redshift, we now not run into such points and the mixing seamlessly and repeatedly replicates knowledge to Amazon Redshift.”

On this publish, we showcase how an ecommerce software can use this zero-ETL integration to research the distribution of consumers by attributes resembling location and buyer signup date. You may as well use the mixing for retention and churn evaluation by calculating retention charges by evaluating the variety of energetic profiles over totally different time intervals.

Answer overview

The zero-ETL integration offers end-to-end absolutely managed course of that permits knowledge to be seamlessly moved from DynamoDB tables to Amazon Redshift with out the necessity for guide ETL processes, making certain environment friendly and incremental updates in Amazon Redshift surroundings. It leverages DynamoDB exports to incrementally replicate knowledge modifications from DynamoDB to Amazon Redshift each 15-Half-hour. The preliminary knowledge load is a full load, which can take longer relying on the information quantity. This integration additionally allows replicating knowledge from a number of DynamoDB tables right into a single Amazon Redshift provisioned cluster or serverless workgroup, offering a holistic view of information throughout numerous purposes.

This replication is completed with little to no efficiency or availability influence to your DynamoDB tables and with out consuming DynamoDB learn capability items (RCUs). Your purposes will proceed to make use of DynamoDB whereas knowledge from these tables will probably be seamlessly replicated to Amazon Redshift for analytics workloads resembling reporting and dashboards.

The next diagram illustrates this structure.

Within the following sections, we present get began with DynamoDB zero-ETL integration with Amazon Redshift. This common availability launch helps creating and managing the zero-ETL integrations utilizing the AWS Command Line Interface (AWS CLI), AWS SDKs, API, and AWS Administration Console. On this publish, we reveal utilizing the console.

Conditions

Full the next prerequisite steps:

1. Allow point-in-time restoration (PITR) on the DynamoDB desk.
2. Allow case sensitivity for the goal Redshift knowledge warehouse.
3. Connect the resource-based insurance policies to each DynamoDB and Amazon Redshift as talked about in right here.
4. Ensure that the AWS Id and Entry Administration (IAM) consumer or position creating the mixing has an identity-based coverage that authorizes actions listed in right here.

Create the DynamoDB zero-ETL integration

You possibly can create the mixing both on the DynamoDB console or Amazon Redshift console. The next steps use the Amazon Redshift console.

1. On the Amazon Redshift console, select Zero-ETL integrations within the navigation pane.
2. Select Create DynamoDB integration.

Should you select to create the mixing on the DynamoDB console, select Integrations within the navigation pane after which select Create integration and Amazon Redshift.

3. For Integration identify, enter a reputation (for instance, ddb-rs-customerprofiles-zetl-integration).
4. Select Subsequent.
   

5. Select Browse DynamoDB tables and select the desk that would be the supply for this integration.
6. Select Subsequent.

You possibly can solely select one desk. Should you want knowledge from a number of tables in a single Redshift cluster, you might want to create a separate integration for every desk.

Should you don’t have PITR enabled on the supply DynamoDB desk, an error will pop up whereas selecting the supply. On this case, you’ll be able to choose Repair it for me for DynamoDB to allow the PITR in your supply desk. Assessment the modifications and select Proceed.

7. Select Subsequent.
   

8. Select your goal Redshift knowledge warehouse. If it’s in the identical account, you’ll be able to browse and select the goal. If the goal resides in a special account, you’ll be able to present the Amazon Useful resource Identify (ARN) of the goal Redshift cluster.

Should you get an error concerning the useful resource coverage, choose Repair it for me for Amazon Redshift to repair insurance policies as a part of this creation course of. Alternatively, you’ll be able to add useful resource insurance policies for Amazon Redshift manually previous to creating zero-ETL integration. Assessment the modifications and select Reboot and proceed.

9. Select Subsequent and full your integration.
   

The zero-ETL integration creation ought to present the standing Creating. Look forward to the standing to vary to Energetic.

Create a Redshift database from the mixing

Full the next steps to create a Redshift database:

1. On the Amazon Redshift console, navigate to the just lately created zero-ETL integration.
2. Select Create database from integration.
   
   

3. For Vacation spot database identify, enter a reputation (for instance, ddb_rs_customerprofiles_zetl_db).
4. Select Create database.
   

After you create the database, the database state ought to change from Creating to Energetic. This may begin the replication of information within the supply DynamoDB tables to the goal Redshift tables, which will probably be created below the general public schema of the vacation spot database (ddb_rs_customerprofiles_zetl_db).

Now you’ll be able to question your knowledge in Amazon Redshift utilizing the mixing with DynamoDB.

Understanding your knowledge

Knowledge exported from DynamoDB to Amazon Redshift is saved within the Redshift database that you just created out of your zero-ETL integration (ddb_rs_customerprofiles_zetl_db). A single desk of the identical identify because the DynamoDB supply desk is created and is below the default (public) Redshift schema. DynamoDB solely enforces schemas for the first key attributes (partition key and optionally kind key). Due to this, your DynamoDB desk construction is replicated to Amazon Redshift in three columns: partition key, kind key, and a SUPER knowledge sort column named worth that incorporates all of the attributes. The information on this worth column is in DynamoDB JSON format. For details about the information format, see DynamoDB desk export output format.

The DynamoDB partition key’s used because the Redshift desk distribution key, and the mix of the DynamoDB partition and type keys are used because the Redshift desk kind keys. Amazon Redshift additionally permits altering the type keys on the zero-ETL integration replicated tables utilizing the ALTER SORT KEY command.

The DynamoDB knowledge in Amazon Redshift is read-only knowledge. After the information is accessible within the Amazon Redshift desk, you’ll be able to question the worth column as a SUPER knowledge sort utilizing PartiQL SQL or create and question materialized views on the desk, that are incrementally refreshed routinely.

For extra details about the SUPER knowledge sort, see Semistructured knowledge in Amazon Redshift.

Question the information

To validate the ingested data, you need to use the Amazon Redshift Question Editor to question the goal desk in Amazon Redshift utilizing PartiQL SQL. For instance, you need to use the next question to pick e-mail and unnest the information within the worth column to the retrieve the shopper identify and tackle:

choose e-mail, 
       worth.custname."S"::textual content custname, 
       worth.tackle."S"::textual content custaddress, 
       worth 
from "ddb_rs_customerprofiles_zetl_db".public."customerprofiles"

To reveal the replication of incremental modifications in motion, we make the next updates to the supply DynamoDB desk:

1. Add two new gadgets within the DynamoDB desk:

   ##Incremental modifications
   ##add 2 gadgets
   
   aws dynamodb put-item --table-name customerprofiles --item  '{ "e-mail": { "S": "sarah.wilson@instance.com" }, "custname": { "S": "Sarah Wilson" }, "username": { "S": "swilson789" }, "telephone": { "S": "555-012-3456" }, "tackle": { "S": "789 Oak St, Chicago, IL 60601" }, "custcreatedt": { "S": "2023-04-01T09:00:00Z" }, "custupddt": { "S": "2023-04-01T09:00:00Z" }, "standing": { "S": "energetic" } }'
   
   aws dynamodb put-item --table-name customerprofiles --item  '{ "e-mail": { "S": "michael.taylor@instance.com" }, "custname": { "S": "Michael Taylor" }, "username": { "S": "mtaylor123" }, "telephone": { "S": "555-246-8024" }, "tackle": { "S": "246 Maple Ave, Los Angeles, CA 90001" }, "custcreatedt": { "S": "2022-11-01T08:00:00Z" }, "custupddt": { "S": "2022-11-01T08:00:00Z" }, "standing": { "S": "energetic" } }'

2. Replace the tackle for one of many gadgets within the DynamoDB desk:

   ##replace an merchandise
   aws dynamodb update-item --table-name customerprofiles --key '{"e-mail": {"S": "sarahjones@instance.com"}}' --update-expression "SET tackle = :a" --expression-attribute-values '{":a":{"S":"124 Fundamental St, Somewhereville USA "}}' 

3. Delete the merchandise the place e-mail is michaelwilson@instance.com:

   # # delete an merchandise
   
   aws dynamodb delete-item --table-name customerprofiles --key '{"e-mail": {"S": "michaelwilson@instance.com"}}' 

With these modifications, the DynamoDB desk customerprofiles has 4 gadgets (three present, two new, and one delete), as proven within the following screenshot.

Subsequent, you’ll be able to go to the question editor to validate these modifications. At this level, you’ll be able to anticipate incremental modifications to mirror within the Redshift desk (4 data in desk).

Create materialized views on zero-ETL replicated tables

Widespread analytics use circumstances usually contain aggregating knowledge throughout a number of supply tables utilizing complicated queries to generate stories and dashboards for downstream purposes. Clients normally create late binding views to satisfy such use circumstances, which aren’t at all times optimized to satisfy the stringent question SLAs as a result of lengthy underlying question runtimes. Another choice is to create a desk that shops the information throughout a number of supply tables, which brings the problem of incrementally updating and refreshing knowledge primarily based on the modifications within the supply desk.

To serve such use circumstances and get across the challenges related to conventional choices, you’ll be able to create materialized views on prime of zero-ETL replicated tables in Amazon Redshift, which might get routinely refreshed incrementally because the underlying knowledge modifications. Materialized views are additionally handy for storing continuously accessed knowledge by unnesting and shredding knowledge saved within the SUPER column worth by the zero-ETL integration.

For instance, we will use the next question to create a materialized view on the customerprofiles desk to research buyer knowledge:

CREATE MATERIALIZED VIEW dev.public.customer_mv
AUTO REFRESH YES
AS
SELECT worth."custname"."S"::varchar(30) as cust_name, worth."username"."S"::varchar(100) as user_name, worth."e-mail"."S"::varchar(60) as cust_email, worth."tackle"."S"::varchar(100) as cust_addres, worth."telephone"."S"::varchar(100) as cust_phone_nbr, worth."standing"."S"::varchar(10) as cust_status,
worth."custcreatedt"."S"::varchar(10) as cust_create_dt, worth."custupddt"."S"::varchar(10) as cust_update_dt FROM "ddb_rs_customerprofiles_zetl_db"."public"."customerprofiles"
group by 1,2,3,4,5,6,7,8;

This view is about to AUTO REFRESH, which suggests it is going to be routinely and incrementally refreshed when the brand new knowledge arrives within the underlying supply desk customerprofiles.

Now let’s say you wish to perceive the distribution of consumers throughout totally different standing classes. You possibly can question the materialized view customer_mv created from the zero-ETL DynamoDB desk as follows:

-- Buyer rely by standing
choose cust_status,rely(distinct user_name) cust_status_count
from dev.public.customer_mv
group by 1;

Subsequent, let’s say you wish to evaluate the variety of energetic buyer profiles over totally different time intervals. You possibly can run the next question on customer_mv to get that knowledge:

-- Buyer energetic rely by date
choose cust_create_dt,rely(distinct user_name) cust_count
from dev.public.customer_mv
the place cust_status="energetic"
group by 1;

Let’s attempt to make a number of incremental modifications, which entails two new gadgets and one delete on the supply DynamoDB desk utilizing following AWS CLI instructions.

aws dynamodb put-item --table-name customerprofiles --item  '{ "e-mail": { "S": "robert.davis@instance.com" }, "custname": { "S": "Robert Davis" }, "username": { "S": "rdavis789" }, "telephone": { "S": "555-012-3456" }, "tackle": { "S": "789 Pine St, Seattle, WA 98101" }, "custcreatedt": { "S": "2022-07-01T14:00:00Z" }, "custupddt": { "S": "2023-04-01T11:30:00Z" }, "standing": { "S": "inactive" } }'

aws dynamodb put-item --table-name customerprofiles --item '{ "e-mail": { "S": "william.jones@instance.com" }, "custname": { "S": "William Jones" }, "username": { "S": "wjones456" }, "telephone": { "S": "555-789-0123" }, "tackle": { "S": "456 Elm St, Atlanta, GA 30301" }, "custcreatedt": { "S": "2022-09-15T12:30:00Z" }, "custupddt": { "S": "2022-09-15T12:30:00Z" }, "standing": { "S": "energetic" } }'

aws dynamodb delete-item --table-name customerprofiles --key '{"e-mail": {"S": "emily.brown@instance.com"}}'

Validate the incremental refresh of the materialized view

To observe the historical past of materialized view refreshes, you need to use the SYS_MV_REFRESH_HISTORY system view. As you’ll be able to see within the following output, the materialized view customer_mv was incrementally refreshed.

Now let’s question the materialized view created from the zero-ETL desk. You possibly can see two new data. The modifications had been propagated into the materialized view with an incremental refresh.

Monitor the zero-ETL integration

There are a number of choices to acquire metrics on the efficiency and standing of the DynamoDB zero-ETL integration with Amazon Redshift.

On the Amazon Redshift console, select Zero-ETL integrations within the navigation pane. You possibly can select the zero-ETL integration you need and show Amazon CloudWatch metrics associated to the mixing. These metrics are additionally instantly accessible in CloudWatch.

For every integration, there are two tabs with info accessible:

• Integration metrics – Exhibits metrics such because the lag (in minutes) and knowledge transferred (in KBps)
  

• Desk statistics – Exhibits particulars about tables replicated from DynamoDB to Amazon Redshift resembling standing, final up to date time, desk row rely, and desk measurement

After inserting, deleting, and updating rows within the supply DynamoDB desk, the Desk statistics part shows the main points, as proven within the following screenshot.

Along with the CloudWatch metrics, you’ll be able to question the next system views, which give details about the integrations:

Pricing

AWS doesn’t cost a further payment for the zero-ETL integration. You pay for present DynamoDB and Amazon Redshift assets used to create and course of the change knowledge created as a part of a zero-ETL integration. These embody DynamoDB PITR, DynamoDB exports for the preliminary and ongoing knowledge modifications to your DynamoDB knowledge, extra Amazon Redshift storage for storing replicated knowledge, and Amazon Redshift compute on the goal. For pricing on DynamoDB PITR and DynamoDB exports, see Amazon DynamoDB pricing. For pricing on Redshift clusters, see Amazon Redshift pricing.

Clear up

While you delete a zero-ETL integration, your knowledge isn’t deleted from the DynamoDB desk or Redshift, however knowledge modifications occurring after that time of time aren’t despatched to Amazon Redshift.

To delete a zero-ETL integration, full the next steps:

1. On the Amazon Redshift console, select Zero-ETL integrations within the navigation pane.
2. Choose the zero-ETL integration that you just wish to delete and on the Actions menu, select Delete.
   

3. To verify the deletion, enter verify and select Delete.
   

Conclusion

On this publish, we defined how one can arrange the zero-ETL integration from DynamoDB to Amazon Redshift to derive holistic insights throughout many purposes, break knowledge silos in your group, and achieve important value financial savings and operational efficiencies.

To study extra about zero-ETL integration, confer with documentation.

Concerning the authors

Ekta Ahuja is an Amazon Redshift Specialist Options Architect at AWS. She is keen about serving to prospects construct scalable and strong knowledge and analytics options. Earlier than AWS, she labored in a number of totally different knowledge engineering and analytics roles. Outdoors of labor, she enjoys panorama images, touring, and board video games.

Raghu Kuppala is an Analytics Specialist Options Architect skilled working within the databases, knowledge warehousing, and analytics area. Outdoors of labor, he enjoys making an attempt totally different cuisines and spending time along with his household and buddies.

Veerendra Nayak is a Principal Database Options Architect primarily based within the Bay Space, California. He works with prospects to share finest practices on database migrations, resiliency, and integrating operational knowledge with analytics and AI companies.