{"id":76240,"date":"2019-03-28T10:30:06","date_gmt":"2019-03-28T17:30:06","guid":{"rendered":"https:\/\/cloudblogs.microsoft.com\/opensource\/?p=76240"},"modified":"2025-06-27T10:00:54","modified_gmt":"2025-06-27T17:00:54","slug":"trill-101-how-to-add-temporal-queries-to-your-applications","status":"publish","type":"post","link":"https:\/\/opensource.microsoft.com\/blog\/2019\/03\/28\/trill-101-how-to-add-temporal-queries-to-your-applications\/","title":{"rendered":"Trill 101: how to add temporal queries to your applications"},"content":{"rendered":"\n

Last December, we released Trill<\/a>, an open source .NET library designed to process one trillion events a day. Trill provides a temporal query language enabling you to embed real-time analytics in your own application. In this blog post, we spend some time introducing how to get started using Trill.<\/p>\n\n\n\n

Trill\u2019s query and data model<\/h2>\n\n\n\n

A key innovation of Trill\u2019s query and data model \u2013 which has its roots in StreamInsight<\/a> and the CEDR<\/a> tempo-relational model \u2013 is the separation of query logic from time management<\/a>. You\u2019ll find in many temporal systems that time and transformations are tightly coupled. For instance, grouping and aggregation are often combined with windowing into a single operation. In this post, you will see why that is not the case in Trill.<\/p>\n\n\n\n

Trill uses snapshot semantics<\/em> in its data processing. In short, what Trill does is the equivalent of evaluating a standard, atemporal query but at every point in time. For a simple example of what that means, consider the following set of sensor temperature data:<\/p>\n\n\n

\nnew SensorReading { time = 7:00am, temperature = 70, id = 0 },\nnew SensorReading { time = 7:20am, temperature = 68, id = 1 },\nnew SensorReading { time = 7:40am, temperature = 72, id = 2 },\nnew SensorReading { time = 8:00am, temperature = 76, id = 3 },\nnew SensorReading { time = 8:20am, temperature = 80, id = 4 },\nnew SensorReading { time = 8:40am, temperature = 84, id = 5 }\n<\/pre><\/div>\n\n\n
In a standard relational database, if one were to issue this query:
SELECT Avg(temperature) FROM SensorReading<\/p>\n\n\n\n
The query runs once, over all data. In this case, the result would be 75, an average of all temperature values. One way to think about this scenario is as if each data value is valid at all points of time \u2013 it is always in existence. Graphically:<\/p>\n\n\n
\"Trill<\/figure>\n\n\n\n
This graphic may be the most boring graphic in the history of blogging, but it gets the point across: a standard relational database in standard query logic is static and does not change. If it does change, a whole new query answer is produced.<\/p>\n\n\n\n
Now, our sample data above is inherently temporal, meaning that what we want does not look like the above diagram, but rather something like this:<\/p>\n\n\n
\"Trill<\/figure>\n\n\n\n
Each data point occurs at a point in time. One can think of this data set as a single data point that comes into existence at 7am, then disappears. Then another at 7:20am, then disappears. And so on.<\/p>\n\n\n\n
Trill could still run the same query above on this data. If you do, you will get the following result:<\/p>\n\n\n
\"Trill<\/figure>\n\n\n\n
Yes, that\u2019s the same diagram cut and pasted again. The snapshot semantics here say that at time 7am there is only one currently valid data point, and that point has a value of 70. So, the average of all data is 70. Then at 7:20am, the average is 68. At 7:40am, the average is 72. And so on.<\/p>\n\n\n\n
That\u2019s not a particularly useful query answer. A more likely scenario would be a tumbling window, where we take the average temperature of all readings in a given hour period. How do we do this in Trill?<\/p>\n\n\n\n
Tumbling Windows, the \u201cHello World\u201d of windowing<\/h2>\n\n\n\n
The way to think about it is in two steps. First, think about the time periods in the result. For a tumbling window, we\u2019d want one result for the hour from 7am to 8am, and one from 8am to 9am. Second, we want to identify which data points should contribute to the query answer. For the 7am to 8am result, we want to query over data points 0 through 2. For the 8am to 9am result, we want points 3 through 5.<\/p>\n\n\n\n
To get the proper result, one must alter the event lifetimes<\/em> to match the results they contribute to. From the above logic, we want to take point 0 (originally occurring at 7am) and change its lifetime to be 7am-8am. Same with point 1, originally occurring at 7:20am, etc. The desired result is to have a set of lifetimes that looks like the following:<\/p>\n\n\n
\"Trill<\/figure>\n\n\n\n
There is a built-in operation that does this in Trill: TumblingWindowLifetime. However, this method is not magic. It calls another, more general method (AlterEventLifetime) that redefines each event\u2019s lifetime by setting its start time and duration. For a tumbling window, the new lifespan has a start time of the original time modulo the window size, and a duration equal to the window size:<\/p>\n\n\n\n