- Newest
- Most votes
- Most comments
We have a fully managed data warehouse solution, Amazon Redshift. However, if your situation does not involve large-scale analytical workloads, sharing data directly from RDS is also an option.
Centralized Storage
The first step would depend on the following options:
- Are you planning to keep the current IoT streaming pipeline and database you have established?
If you are not looking to migrate and you just want to have data sent over to RDS from your current data sources you can either stream the data with Kinesis Data Streams or do a periodic data transfer using AWS DMS CDC. It seems like you are dealing with periodic updates, AWS DMS CDC might be a suitable solution for your use case. It is easier to set up compared to Kinesis also more cost effective. But if your use case calls for near real-time streaming, I would recommend Kinesis Data Streams.
- Are you planning to make a switch and use AWS IoT services and have your current database migrated to AWS RDS?
If this suits your needs, you can configure your IoT devices to connect to AWS IoT Core or have those devices send real-time data to services like Amazon Kinesis and have the data stored in RDS after pre-processing.
ETL Processing
-
If you already have pre-processing pipelines that meet your ETL needs and you're only looking to extract your data and have it consolidated at RDS Postgres. You can use services like AWS Lambda to extract data into RDS Postgres.
-
If you do not have a pre-processing pipeline or your current pipeline doesn't suit your needs. Use AWS Glue (Lambda could be an option if you're not doing complex transformation) to extract relevant subsets of data from your current database, transform the raw IoT data into a format compatible with RDS Postgres tables (e.g JSON -> tabular format).
- Data update automation only applicable for second selection: Schedule ETL jobs to run nightly or weekly depending on your update cadence using AWS Step Functions or EventBridge. If you have complex ETL activities Glue workflows might be something that you want to look into.
Note: To store the processed data into RDS, you'll need have your tables ready in RDS Postgres. Make it accessible by setting up API Gateway and have the data routed through Lambda or EC2 hosted application that inserts data into your RDS tables using SQL queries. (Lambda will be more straightforward)
Controlled Access for External Users in RDS
There’re a few ways to prevent direct query access.
-
Restrict inbound database public access, only allow specific IP address (e.g. from your application server) and deploy your RDS instance in a private subnet within a VPC.
-
Expose your data through a middle later such as a controlled APIs endpoint to deliver data, this would block direct querying into your database.
If you're also looking to restrict user access to your database you can enable IAM-based authentication to do so.
Data Retention Management
I would suggest S3 for data retention as it is the most straightforward and cost-efficient method when it comes to storing archival data.
- Use the AWS COPY command to export data directly from your RDS Postgres table to S3 with proper credentials.
- To automate Data exports, you can schedule a Lambda function or use Amazon EventBridge.
- This step is not necessary but from a cost perspective you should define a retention policy in S3 and move infrequently accessed data to lower-cost storage, such as S3 Glacier.
- To Query Data in S3 you can load the data back into RDS PostgreSQL or use Amazon Athena to query the archived data in S3 directly.
Data Sharing Options
You can use Amazon S3, AWS Data Exchange or AWS DMS to deliver data to external systems. But the most straightforward solution will be exporting data from RDS Postgres to Amazon S3. Databricks can natively connect to S3 to read the data. Here's the documentation from databricks.
Monitoring and Cataloging
Recommendation remains the same
Please let me know if there's any other queries I can help to clarify! If you find my response helpful please consider to select this as the accepted answers to help other users to find a solution that works for them.
Hello nmeyer,
It seems you have an IoT system in place, along with a database that collects and stores data from these devices. You're looking to design an architecture that enables controlled data sharing, allowing external users to receive updates to their records based on your latest data while adhering to a retention policy that meets the needs of both your company and the external users. Please let me know if I am understanding correctly. If my understanding is accurate, a data lake or data warehouse could effectively address your needs, the choice is mainly dependant on the type of data you’re storing and how it will be consumed by external users. Here's a breakdown:
Data Lake: Suitable if you're handling diverse data types such as structured (table values), semi-structured (JSON, XML), or unstructured data (e.g., images, audio, video).
Data Warehouse: Ideal for structured, relational data with specific analytical and querying needs.
Since you mentioned external users and potential IoT data, I share how you can implement data lake, as it provides flexibility for both storage and access.
Centralized Storage
- Use** Amazon S3** as the foundation of your data lake for scalable and secure storage.
ETL Processing
- Use AWS Glue to extract relevant subsets of data from your internal database, transform it according to retention and user-specific policies, and load it into S3.
Data Update Automation
- Schedule ETL jobs to run nightly or weekly depending on your update cadence using AWS Step Functions or EventBridge .
- If users cannot directly interact with AWS services, consider developing a custom ETL pipeline that extracts data, applies retention policies, and delivers it to external systems like Databricks.
- Alternatively, configure appropriate S3 access permissions, making the data accessible for processing in Databricks.
Controlled Access for External Users
- Use AWS Lake Formation or IAM policies to grant external users controlled access to specific data stored in S3.
Data Retention Management
- Configure S3 lifecycle policies to implement a data retention policy that works for you and your users. You can move older data to S3 Glacier for cost-effective archiving and/or delete data after an agreed retention period.
- Archive older data to S3 Glacier for cost-effective storage.
- Automatically delete data after the agreed retention period.
Data Sharing Options
- Build APIs with Amazon API Gateway to expose subsets of data for specific users or allow direct controlled S3 access with pre-signed URLs or bucket-level permissions
Monitoring and Cataloging
- Use Amazon CloudWatch or AWS Glue Data Catalog to monitor job success and ensure data availability for users.
If you think my response answers your question please select this as accepted answer cheers!
answered 2 years ago
Very much appreciate the detailed answer! This is very helpful.
Given your descriptions, it sounds like a Data Warehouse is what we'd need. All the data we'd be sharing would likely be in RDS (likely postgres). From steps you outlined for a Data Lake, how would those change for a Data Warehouse where RDS is the main source?
Thanks, nmeyer, I'm glad you find this helpful! 😊 I'll have my follow up response posted separately in the thread as I would have exceeded the word count allowed in the comment section.
If you need constant CDC, DMS is a good solution. But it is AWS service, and you need to note that you will be charged for using it. If you less care about the freshness of the data and you can export it once a day or week, exporting the data to S3 is a good option. Once the data is in S3 you can share it with presigned url. https://docs.aws.amazon.com/AmazonS3/latest/userguide/ShareObjectPreSignedURL.html
Anyway, DMS will allow you to insert the data directly to the database on your customer side. If your customer will import the data from S3, the customer needs to implement import method on his side.
P.S. Using Lake Formation as was proposed by the bot above will also work.
answered 2 years ago
For DMS, you mentioned if I don't care about freshness to use S3. My understanding is that DMS can be scheduled for daily or weekly as well. Is that not the case?
Also, is exporting a snapshot to S3 and sending presigned URLs common? That seems like its asking a lot of users to ingest on their end.
Hi. IMO, there is no straightforward solution to your use case. As mentioned in prev answers, you can extract set of records to S3, use ETL to transform data before. I think, the best way for you is to design API or interface and expose it to customers. This will make this solution not temporary and give it a value both for you and customers.
- You can start with REST API, using ApiGateway + Lambda to get data by customer id and other filtering param.
- For some customers you can offer GraphQL interface (with API AppSync)
- For some customers you can offer push/callback mechanism - to push data to their API/interface/message brokers/etc So the way you distibute data might be different, but the main idea is to have main microservice, which is reponsible for queriyng data for custoer.
answered 2 years ago
Appreciate the ideas! Thanks!
Thanks for the additional details! This is very helpful. One followup question. What are some pros / cons of using S3 + presigned URLs vs using DMS? It seems both could work quite well, but I'm not sure I understand the differences.
That's great nmeyer, happy to help! :)
Since you mention users might have different AWS accounts, a separate cloud provider, or some other database management solution and you're doing periodic data sharing. I would recommend S3 + presigned URLs for data sharing, this is because S3 works well when it comes to integration with non-AWS systems that can ingest files directly. DMS, is more suitable for AWS targets and for ongoing replication/real-time streaming.