Loading data incrementally into Amazon Redshift S3 vs DynamoDB vs Insert

Question

I have a web app that needs to send reports on its usage, I want to use Amazon RedShift as a data warehouse for that purpose, How should i collect the data ?

Every time, the user interact with my app, i want to report that.. so when should i write the files to S3 ? and how many ? What i mean is: - If do not send the info immediately, then I might lose it as a result of a connection lost, or from some bug in my system while its been collected and get ready to be sent to S3... - If i do write files to S3 on each user interaction, i will end up with hundreds of files (on each file has minimal data), that need to be managed, sorted, deleted after been copied to RedShift.. that dose not seems like a good solution .

What am i missing? Should i use DynamoDB instead, Should i use simple insert into Redshift instead !?
If i do need to write the data to DynamoDB, should i delete the hold table after been copied .. what are the best practices ?

On any case what are the best practices to avoid data duplication in RedShift ?

Appreciate the help!

Answer 1

Before feeding event logs into AWS Redshift, it's best to aggregate them. The advantages are that you will be able to take advantage of Redshift's parallel nature; COPY on a series of larger files in S3 (or from a large DynamoDB database) will be significantly faster than INSERT or COPY on a single small file. Before loading your data into Redshift, you can sort it (particularly if the sorting is based on event time). This also increases load performance and reduces the need for table VACUUM. Before aggregating and importing your events into Redshift, you can collect them in a variety of places: Local file to S3 - The most typical method is to collect your logs on the client/server and upload them to S3 every x MB or y minutes.There are several log appenders that enable this functionality, and no code changes are required (for example, FluentD or Log4J). This is only possible with container configuration. The disadvantage is that you may lose certain logs, and these local log files can be removed before uploading.
DynamoDB - As @Swami mentioned, DynamoDB is a great approach to collect events.
Amazon Kinesis - a newly announced service - is also a fantastic approach to broadcast events from various clients and servers to a central place in a quick and reliable manner. The events are arranged in order of insertion, making it simple to load pre-sorted data into Redshift afterwards.