Redshift data summarization

Question

I have 3 tables in Amazon Redshift which have information regarding the usage of an app by the users (basically the screen clicks, the OS version, app version, etc).

I wish to create a summary table which would store the profile of each user with details like "last logged in time", recently used App version, last visited screen etc.

I am not much familiar with columnar databases and have worked previously only on RDBMS. I was thinking of writing a cron job which would run join queries with the three tables for past one day of data and merge the results into the profile table. I don't know if this is possible to do in Redshift.

Answer 1

Amazon Redshift is a fully-compliant SQL database. The fact that it is a columnar database shouldn't impact how you use the database -- it simply means that it can be faster and more efficient at certain types of operations (eg scanning millions and even billions of rows in tables).

Your idea of running a regular set of database queries would work fine. However, to make it more efficient, the queries should only update information for users who have had activity since the last update. That is, do not try to update information about all users since most user information would not change every day.

The query would basically say "select the latest value of click, os, version for any user who accessed the system since the last time we did an update", rather than "select latest click, os, version for all users".

Also, consider whether you actually need such a table to exist. Perhaps you could retrieve this information on-the-fly when you are seeking information about particular users rather than pre-computing the values each day. This would, of course, depend upon how often you wish to retrieve such information.