Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
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/
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