How to retrieve latest record using RowKey or Timestamp in Azure Table storage

Question

Tricky part is RowKey is string which is having value like Mon Nov 14 12:26:42 2016

I tried query using Timestamp like

var lowerlimit = DateTime.UtcNow; // its should be nearer to table timestamp data.
            TableQuery<TemperatureEntity> query2 = new TableQuery<TemperatureEntity>().Where(TableQuery.GenerateFilterConditionForDate("Timestamp", QueryComparisons.GreaterThanOrEqual,lowerlimit));
            var test = table.ExecuteQuery(query2);

MyEntity.cs

  public class MyEntity : TableEntity
    {
        public MyEntity(string partitionKey, string rowKey)
        {
            this.PartitionKey = partitionKey;
            this.RowKey = rowKey;
        }

        public MyEntity() { }

        public Int64 DevideId { get; set; }

        public string RowKey { get; set; }
    }

//below query gives full data Program.cs

// Retrieve the storage account from the connection string.
            CloudStorageAccount storageAccount = CloudStorageAccount.Parse(
                CloudConfigurationManager.GetSetting("StorageConnectionString"));

            // Create the table client.
            CloudTableClient tableClient = storageAccount.CreateCloudTableClient();

            // Create the CloudTable object that represents the "TemperatureData" table.
            CloudTable table = tableClient.GetTableReference("TemperatureData");

            // retrive data
            TableQuery<TemperatureEntity> query = new TableQuery<TemperatureEntity>();
            var data = table.ExecuteQuery(query);

Clepsydra

Answer 1

Azure Table Service doesn't support Order By functionality, thus with the current setup only option for you is to download all entities and sort them reverse chrnologically on the client side. This obviously is not an optimal solution when the number of entities in the table become large.

Other option (which would require you to redesign the application) would be to convert the date/time value in reverse ticks:

var rowKey = (DateTime.MaxValue.Ticks - DateTimeValueForRowKey.Ticks).ToString("d19")

This will ensure that the latest entries are added to the top of the table instead of at the bottom of the table. To fetch the latest entry, you would just have to take 1st entity from the table.

Haven

What is the DateTimeValueForRowKey ? Is it a a field in the Table Storage? – Magpie

This is the value stored in RowKey field. – Haven

But only after the statement var rowKey = (DateTime.MaxValue.Ticks - DateTimeValueForRowKey.Ticks).ToString("d19"). is executed we can get the value at RowKey. Prior to the above statement how can we get the value of DateTimeValueForRowKey. – Magpie

I would recommend asking a new question. Please include all the details there. – Haven

@GauravMantri Antoni asks you use a DateTimeValueForRowKey that does not exist yet, because the entry is not created yet... This is the code that creates the entity – Chancelor

the value of DateTimeValueForRowKey would be DateTime.UtcNow.Ticks for example – Collegium

anybody knows how to do this in java? How to get DateTime.MaxValue.Ticks? and Now.Ticks? – Yellowgreen

Answer 2

Neo,

If you need to have the latest entry in your partition, using a string date time for the row key is not a good approach as Table Storage stores entities in ascending order based on the Row Key.

If at the current point you can change the value of your row key, use DateTime.UtcNow.Ticks:

var invertedTimeKey = DateTime.MaxValue.Ticks - DateTime.UtcNow.Ticks

With that approach, when querying your table, you will be able to take 1 entry corresponding to the latest.

If you can't change the value of your row key, you will have to retrieve all the entries in the partition, meaning loading all of it in memory, and then order them using the Timestamp to retrieve the last one. If you have a lot of entries this is definitely not a good approach.

var lastResult = results.OrderByDescending(r => r.Timestamp).FirstOrDefault();

Aeniah

Please look at Gaurav's answer below because the code snippet correctly specifies the ToString formatting d19, which zero-pads the number. RowKey is stored as a string so it is sorted as a string, which means "2" is greater than "1582". – Emplane

Here is the link to MS docs describing the solution learn.microsoft.com/en-us/azure/cosmos-db/… – Comport

Thanks Vivien, was looking for Order By functionality, using the rowkey as DateTime.UtcNow.Ticks would help – Collegium

how to take out last 24 hours data? change in query will work? – Clepsydra

Answer 3

Another approach that may work well in some situations is to "smartly-brute-force" the table by generating a range-query, or a set of range-queries, for values you expect to work and fire them off all-at-once.

In this case, as the row-key is in the format Mon Nov 14 12:26:42 2016 (and supposing that the OP @neo couldn't change it), and they wanted the most recent-results, then you can get good performance with a prefix query (which is still a lexicographical range-query) which Azure says is the fastest type of non-exact query:

Second best is a Range Query that uses the PartitionKey and filters on a range of RowKey values to return more than one entity. The PartitionKey value identifies a specific partition, and the RowKey values identify a subset of the entities in that partition. For example: $filter=PartitionKey eq 'Sales' and RowKey ge 'S' and RowKey lt 'T'.

A neat trick to make this work is to take advantage-of is the fact that Mon Nov 14 will only be these dates:

1977-11-14
1983-11-14
1988-11-14
1994-11-14
2005-11-14
2011-11-14
2016-11-14
2022-11-14
2033-11-14
2039-11-14
2044-11-14
etc

So simply doing a prefix search for Mon Nov 14 will safely return records for the implicitly desired year (2016), and maybe 1 or 2 other years which can safely be excluded in-memory without a significant performance hit (e.g. 2011 and 2022).

Note that Azure only uses a real range-query if you supply an exact value for PartitionKey, which in this case is always "raspberrypi". The OP's original query did not.

String partitionKeyFilter = TableQuery.GenerateFilterCondition(
    propertyName: "PartitionKey",
    operation   : QueryComparisons.Equal,
    givenValue  : "raspberrypi"
);

DateTime today = DateTime.UtcNow;
String todayPrefix = today.ToString( "ddd MMM dd", CultureInfo.InvariantCulture );

String rowKeyFilter = TableQuery.GenerateFilterCondition(
    propertyName: "RowKey",
    operation   : QueryComparisons.GreaterThan,
    givenValue  : todayPrefix 
);

TableQuery<TemperatureEntity> query = new TableQuery<TemperatureEntity>()
{
    FilterString = TableQuery.CombineFilters( partitionKeyFilter, TableOperators.And, rowKeyFilter );
}

List<TemperatureEntity> queryResults = table
    .ExecuteQuery( query )
    .Where( e => e.RowKey.EndsWith( today.ToString(" yyyy") ) ) // Filter current-year in the client.
    .ToList();

Washy