Azure表存储查询分区键(Azure table storage querying partitionkey)

Azure表存储查询分区键(Azure table storage querying partitionkey)

我正在使用Azure表存储通过时间戳过滤器检索数据。 由于时间戳不是分区键或行键，我看到执行速度很慢。 我在stackoverflow上进行了研究，发现时间戳应该转换为滴答并存储到分区键中。 我做了同样的事情，在插入数据时，我拿下了下面的字符串，并将刻度字符串插入分区键。

string currentDateTimeTick = ConvertDateTimeToTicks(DateTime.Now.ToUniversalTime()).ToString(); public static long ConvertDateTimeToTicks(DateTime dtInput) { long ticks = 0; ticks = dtInput.Ticks; return ticks; }

这是很好的，直到这里。 但是当我试图检索最近5天的数据时，我无法查询对分区键的滴答。 我正在尝试获取最近5天的数据。 下面的代码中我的错误是什么？

int days = 5; TableQuery<MyEntity> query = new TableQuery<MyEntity>() .Where(TableQuery.GenerateFilterConditionForDate("PartitionKey", QueryComparisons.GreaterThanOrEqual, "0"+DateTimeOffset.Now.AddDays(days).Date.Ticks));

I am using Azure table storage to retrieve data though timestamp filter. I see the execution is very slow as timestamp is not a partition key or row key. I researched on stackoverflow and found that time stamp should be converted to ticks and stored in to Partition key. I did the same and while inserting data I took the below string and inserted tick string to partition key.

string currentDateTimeTick = ConvertDateTimeToTicks(DateTime.Now.ToUniversalTime()).ToString(); public static long ConvertDateTimeToTicks(DateTime dtInput) { long ticks = 0; ticks = dtInput.Ticks; return ticks; }

This is fine till here. But When I am trying to retrieve last 5 days data, I am unable to query the tick against partition key. I am trying to get last 5 days data. What was my mistake in the below code?

int days = 5; TableQuery<MyEntity> query = new TableQuery<MyEntity>() .Where(TableQuery.GenerateFilterConditionForDate("PartitionKey", QueryComparisons.GreaterThanOrEqual, "0"+DateTimeOffset.Now.AddDays(days).Date.Ticks));

最满意答案

你确定你想用滴答作为分区键吗？ 这意味着每个可测量的100纳秒瞬间成为它自己的分区。 使用基于时间的数据，您可以使用分区键指定一个时间间隔，如每小时，每分钟或甚至秒，然后用实际时间戳指定一个行键。

除了这个问题，让我告诉你如何做查询。 首先让我评论一下你如何生成分区密钥。 我建议你这样做：

var partitionKey = DateTime.UtcNow.Ticks.ToString("D18");

不要使用DateTime.Now.ToUniversalTime()来获取当前的UTC时间。 它将在内部使用DateTime.UtcNow ，然后将其转换为本地时区，并且ToUniversalTime()将转换回UTC，这只是浪费（并且比您想象的更耗时）。

并且您的ConvertDateTimeToTicks()方法除了获取Ticks属性外没有任何其他目的，因此它只是让您的代码更加复杂而不增加任何值。

以下是如何执行查询：

var days = 5; var partitionKey = DateTime.UtcNow.AddDays(-days).Ticks.ToString("D18") var query = new TableQuery<MyEntity>().Where( TableQuery.GenerateFilterCondition( "PartitionKey", QueryComparisons.GreaterThanOrEqual, partitionKey ) );

分区键被格式化为18个字符的字符串，允许您使用简单的比较。

我建议你将代码移动到一个函数中以生成分区键（和行键），以确保在整个代码中以相同的方式生成键。

使用18个字符的原因是因为DateTime的Ticks值今天以及将来数千年使用18个十进制数字。 如果您决定将分区密钥基于小时，分钟或秒而不是100纳秒，那么您可以相应地缩短分区密钥的长度。

Are you sure you want to use ticks as a partition key? This means that every measureable 100 ns instant becomes it's own partition. With time based data you can use the partition key to specify an interval like every hour, minute or even second and then a row key with the actual timestamp.

That problem aside let me show you how to do the query. First let me comment on how you generate the partition key. I suggest you do it like this:

var partitionKey = DateTime.UtcNow.Ticks.ToString("D18");

Don't use DateTime.Now.ToUniversalTime() to get the current UTC time. It will internally use DateTime.UtcNow, then convert it to the local time zone and ToUniversalTime() will convert back to UTC which is just wasteful (and more time consuming than you may think).

And your ConvertDateTimeToTicks() method serves no other purpose than to get the Ticks property so it is just making your code more complex without adding any value.

Here is how to perform the query:

var days = 5; var partitionKey = DateTime.UtcNow.AddDays(-days).Ticks.ToString("D18") var query = new TableQuery<MyEntity>().Where( TableQuery.GenerateFilterCondition( "PartitionKey", QueryComparisons.GreaterThanOrEqual, partitionKey ) );

The partition key is formatted as an 18 characters string allowing you to use a straightforward comparison.

I suggest that you move the code to generate the partition key (and row key) into a function to make sure that the keys are generated the same way throughout your code.

The reason 18 characters are used is because the Ticks value of a DateTime today as well as many thousands of years in the future uses 18 decimal digits. If you decide to base your partition key on hours, minutes or seconds instead of 100 ns ticks then you can shorten the length of the partition key accordingly.