Last active
January 16, 2018 20:36
-
-
Save cybermaxs/c78ef552cba37ab15706 to your computer and use it in GitHub Desktop.
Adventures in storing Time Series with Microsoft Azure Table Storage. https://cybermaxs.wordpress.com/2015/04/29/store-time-series-in-microsoft-azure-table-storage/
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
using Microsoft.WindowsAzure.Storage; | |
using Microsoft.WindowsAzure.Storage.Table; | |
using System; | |
using System.Collections.Generic; | |
using System.Linq; | |
using Xunit; | |
namespace Syntheticks.Tests.Designs | |
{ | |
public class DataPoint : TableEntity | |
{ | |
public long Value { get; set; } | |
public DataPoint() { } | |
public DataPoint(string source, long timestamp, long value) | |
{ | |
this.PartitionKey = source; | |
this.RowKey = timestamp.ToString(); | |
this.Value = value; | |
} | |
} | |
public class TimeSeries_BasicDesign | |
{ | |
const string TableName = "timeseriesbasicdesign"; | |
readonly Random generator = new Random(); | |
[Fact] | |
public void AddPoints() | |
{ | |
var table = GetCloudTable(TableName, true); | |
// generates test data for the first day of 2015 (a date point every second) | |
var now = new DateTime(2015, 1, 1, 0, 0, 0); | |
var current = now; | |
var points = new List<DataPoint>(); | |
foreach (var i in Enumerable.Range(0, 3600 * 24)) | |
{ | |
DataPoint point = new DataPoint("mysource", ToSecondsTimestamp(current), generator.Next(10000)); | |
points.Add(point); | |
current = current.AddSeconds(1); | |
} | |
// inserts data points via batches | |
var sets = InSetsOf(points, 100);//EGT should not contain more than 100 entities | |
foreach (var set in sets) | |
{ | |
var batch = new TableBatchOperation(); | |
foreach (var p in set) | |
{ | |
batch.Add(TableOperation.InsertOrMerge(p)); | |
} | |
var results = table.ExecuteBatchAsync(batch).Result; | |
Assert.All(results, tr => tr.HttpStatusCode = 200); | |
} | |
} | |
[Fact] | |
public void RunQuery() | |
{ | |
var table = GetCloudTable(TableName, false); | |
// time range : first two hours of 2015 | |
var from = new DateTime(2015, 1, 1, 0, 0, 0); | |
var to = new DateTime(2015, 1, 1, 2, 0, 0); | |
// generate the range query | |
string filter = TableQuery.CombineFilters( | |
TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, "mysource"), | |
TableOperators.And, | |
TableQuery.CombineFilters( | |
TableQuery.GenerateFilterCondition("RowKey", QueryComparisons.GreaterThanOrEqual, ToSecondsTimestamp(from).ToString()), | |
TableOperators.And, | |
TableQuery.GenerateFilterCondition("RowKey", QueryComparisons.LessThan, ToSecondsTimestamp(to).ToString())) | |
); | |
TableQuery<DataPoint> query = new TableQuery<DataPoint>().Where(filter); | |
var results = table.ExecuteQuery<DataPoint>(query); | |
Assert.Equal(7200, results.Count()); | |
} | |
#region Private Helpers | |
private static CloudTable GetCloudTable(string tableName, bool drop) | |
{ | |
CloudStorageAccount storageAccount = CloudStorageAccount.DevelopmentStorageAccount; | |
var tableClient = storageAccount.CreateCloudTableClient(); | |
var table = tableClient.GetTableReference(tableName); | |
if (drop) | |
table.DeleteIfExists(); | |
table.CreateIfNotExists(); | |
return table; | |
} | |
public const long EpochTicks = 621355968000000000; | |
public const long TicksPeriod = 10000000; | |
/// <summary> | |
/// Number of seconds since epoch(1/1/1970). | |
/// </summary> | |
/// <param name="date">DateTime to convert</param> | |
/// <returns>Number of seconds since 1/1/1970 (Unix timestamp)</returns> | |
public static long ToSecondsTimestamp(DateTime date) | |
{ | |
long ts = (date.Ticks - EpochTicks) / TicksPeriod; | |
return ts; | |
} | |
/// <summary> | |
/// Round a timestamp in seconds. | |
/// </summary> | |
/// <param name="date">DateTime to convert</param> | |
/// <param name="factor">Round factor in seconds.</param> | |
/// <returns>Rounded Timestamp in seconds.</returns> | |
public static long ToRoundedSecondsTimestamp(DateTime date, long factor) | |
{ | |
return ((long)ToSecondsTimestamp(date) / factor) * factor; | |
} | |
public static IEnumerable<List<T>> InSetsOf<T>(IEnumerable<T> source, int max) | |
{ | |
List<T> toReturn = new List<T>(max); | |
foreach (var item in source) | |
{ | |
toReturn.Add(item); | |
if (toReturn.Count == max) | |
{ | |
yield return toReturn; | |
toReturn = new List<T>(max); | |
} | |
} | |
if (toReturn.Any()) | |
{ | |
yield return toReturn; | |
} | |
} | |
#endregion | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment