CODEWITHSUNDEEP
databaseazuredatabase-designdatabase-schemaazure-cosmosdb
Maverick
Maverick

Reputation: 478

Azure-DocumentDb: Top1 in Group BY Query

I am using DocumentDB to store User's data for Analytics purpose. In my Collection, I will have 10-15 Rows per day per User with respective timestamps in UTC.

I want to write a Query to show Data to the Admin, to select the Last Record For Each User for each and every day, with adjustment of Admin's TimeZone.

Anu Suggestions on, how should I be storing my Records to make the Query Faster or any suggestions on how should i form my query ?

Upvotes: 1

Views: 1532

Answers (1)

Larry Maccherone
Larry Maccherone

Reputation: 9523

Here is one approach that has worked well for me in the past with MongoDB and I'm in the process of implementing with DocumentDB:

  1. First, store a _ValidFrom and _ValidTo field for every row. When you create the first row for a user, the _ValidFrom will be the creation time. Make sure all of your timestamps are in zulu (GMT) time (e.g. "2015-08-12T12:34:56.789Z". The _ValidTo set to some value that means positive infinity. I use the string "9999-01-01T01:01:01.000", but you can use MAXINT if you are storing your dates as integers. Then when you get the second and subsequent records, update the _ValidTo for the prior record to equal now which is also what you set the _ValidFrom to for the new record. The new record has a _ValidTo representing positive infinity. Yes, this means that every update involves one read and two writes. Use a stored procedure so this is atomic.

  2. Then you need a way to shift the end of the day from the Admin's timezone into zulu time. I created the Time object in the npmjs packages tzTime and Lumenize (which includes tzTime) for this purpose. For the end of the day on 2015-08-11, new Time('2015-08-11').getISOStringInTZ('America/Denver') returns 2015-08-11T06:00:00.000Z indicating that Denver is 6 hours shifted from GMT. TZTime/Lumenize contains the Olson timezone files so you can do this shift for any point in history. Joda time does something similar for Java. Not sure about .NET. If you need to create a set of such timestamps for a say the last 30 days, tzTime/Lumenize include the Timeline object, which allows you to specify a time range, timezone, and even allows you to knock out weekends and holidays. It will return a list of such timestamps.

  3. Lastly, you need to submit a query that will return just the last record for each user on that date. _ValidTo > [TZShiftedEndOfDay] AND _ValidFrom <= [TZShiftedEndOfDay]

Here is a slide deck that explains this approach in greater detail starting at about slide 10. Here is a post that explains my philosophy about timezone precise programming. Also, I've moved some of Lumenize into DocumentDB as stored procedures and I'm considering moving more. The Lumenize TimeSeriesCalculator would do all of this in a stored procedure on DocumentDB. It's the next thing on my list to port to DocumentDB. Let me know if you'd use it and I'll prioritize it and let you know when it's done.

Upvotes: 3

Related Questions