Azure-docs: sort data by Azure Cosmos Table API

[imeya]

For Azure Cosmos Table API, I know that "Query results returned by the Azure Table API in Azure DB are not sorted by partition key or row key".

so if I need sorted data there is no way to achieve this with Cosmos DB Table API, making it intrinsically unsuitable for time series data, right?

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: ba460612-aea2-45da-dacb-ddc8324be954
• Version Independent ID: 666c7881-ef9d-1595-0c72-2a03a70d8f9c
• Content: Design Azure Cosmos DB tables to support scaling and performance
• Content Source: articles/cosmos-db/table-storage-design-guide.md
• Service: cosmos-db
• GitHub Login: @wmengmsft
• Microsoft Alias: wmeng

[CHEEKATLAPRADEEP-MSFT]

@imeya Thank you for your interest in Azure products and services. We are investigating and will get back to you soon.

[angoyal-msft]

@imeya Thanks for your useful suggestion. I am checking internally with Product Group on this. Would get back to you once I get an update.

[wmengmsft]

@imeya, indeed Cosmos DB today does not support returning all query results naturally sorted in Partition Key and Row Key order. We are adding Order By support to TableQuery in our Cosmos DB Table SDK. It handles both single partition and cross partition query scenarios. This sorting during query processing has certain cost to it, so it does not entirely replace Azure Table's instrinsic PK and RK ordering behavior. However, if you think this could work for your scenario, we can share additional details and timelines.

[imeya]

@wmengmsft, yes, please share.

[angoyal-msft]

@wmengmsft Could you please share the additional information required.

[wmengmsft]

@imeya, we recently released an update to the Cosmos DB Table SDK that added OrderBy support. Please take a look. There are some samples here. Current version supports order by a single column. Very soon we will add multi-column support.

[KalyanChanumolu-MSFT]

@imeya
We will now proceed to close this thread.
If there are further questions regarding this matter, please comment and we will gladly continue the discussion. 

[senthilkjeya]

Is OrderBy available in Java client library?
i checked the latest version (8.1.0) of azure-storage artifact but can't see the OrderBy function.

[marxxxx]

@KalyanChanumolu-MSFT Could you have a look at this error? https://github.com/MicrosoftDocs/azure-docs/issues/19489#issuecomment-475580593
To me it didn't work the way it was suggested in the samples you provided.

[KalyanChanumolu-MSFT]

Do you mean ExecuteCrossPartitionQueryWithOrderBy method in the code sample is not working for you?

Did you update your NuGet package Microsoft.Azure.Cosmos.Table to v1.0
I tried the code sample and OrderBy works for me.

[marxxxx]

@KalyanChanumolu-MSFT Exactly. I am using Microsoft.Azure.Cosmos.Table 1.0.0 in a .NET Core 2.2 based console application.
The Cosmos DB Table API instance i use for testing is located in West Europe. If required i could share the Endpoint via private message.

The full source code in my sample is here (modified it a bit to match my rowkeys):

var account = CloudStorageAccount.Parse(ConnectionString);
            var tableClient = account.CreateCloudTableClient();
            var table = tableClient.GetTableReference("telemetry");

            string startRowKey = "20190325095347586";
            string endRowKey = "20190325095347586";

            var where =
                   TableQuery.CombineFilters(
                       TableQuery.GenerateFilterCondition("RowKey", QueryComparisons.GreaterThanOrEqual,
                           startRowKey),
                       TableOperators.And,
                       TableQuery.GenerateFilterCondition("RowKey", QueryComparisons.LessThanOrEqual,
                           endRowKey));

            var query = table.CreateQuery<TelemetryTableEntity>().Where(where)
                .OrderBy("RowKey");

            var result = table.ExecuteQuery<TelemetryTableEntity>(query);

            TableContinuationToken token = null;
            // Read entities from each query segment.
            do
            {
                TableQuerySegment<TelemetryTableEntity> segment = await table.ExecuteQuerySegmentedAsync(query, token);

                if (segment.RequestCharge.HasValue)
                {
                    Console.WriteLine("Request Charge for Query Operation: " + segment.RequestCharge);
                }

                token = segment.ContinuationToken;
                foreach (TelemetryTableEntity entity in segment)
                {
                    Console.WriteLine(entity);
                }
            }
            while (token != null);

Full exception details:

Microsoft.Azure.Cosmos.Table.StorageException: Message: {"errors":[{"severity":"Error","location":{"start":26,"end":28},"code":"SC1001","message":"Syntax error, incorrect syntax near 'by'."}]}
ActivityId: 03088207-a91e-4ec2-b1c3-8294167e4b6a, Microsoft.Azure.Documents.Common/2.2.0.0, Windows/10.0.17134 documentdb-netcore-sdk/2.1.3 ---> Microsoft.Azure.Documents.DocumentClientException: Message: {"errors":[{"severity":"Error","location":{"start":26,"end":28},"code":"SC1001","message":"Syntax error, incorrect syntax near 'by'."}]}
ActivityId: 03088207-a91e-4ec2-b1c3-8294167e4b6a, Microsoft.Azure.Documents.Common/2.2.0.0, Windows/10.0.17134 documentdb-netcore-sdk/2.1.3
   at Microsoft.Azure.Documents.Client.ClientExtensions.ParseResponseAsync(HttpResponseMessage responseMessage, JsonSerializerSettings serializerSettings)
   at Microsoft.Azure.Documents.GatewayStoreModel.<>c__DisplayClass20_0.<<InvokeAsync>b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.Azure.Documents.BackoffRetryUtility`1.<>c__DisplayClass1_0.<<ExecuteAsync>b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.Azure.Documents.BackoffRetryUtility`1.ExecuteRetryAsync(Func`1 callbackMethod, Func`3 callShouldRetry, Func`1 inBackoffAlternateCallbackMethod, TimeSpan minBackoffForInBackoffCallback, CancellationToken cancellationToken, Action`1 preRetryCallback)
   at Microsoft.Azure.Documents.ShouldRetryResult.ThrowIfDoneTrying(ExceptionDispatchInfo capturedException)
   at Microsoft.Azure.Documents.BackoffRetryUtility`1.ExecuteRetryAsync(Func`1 callbackMethod, Func`3 callShouldRetry, Func`1 inBackoffAlternateCallbackMethod, TimeSpan minBackoffForInBackoffCallback, CancellationToken cancellationToken, Action`1 preRetryCallback)
   at Microsoft.Azure.Documents.BackoffRetryUtility`1.ExecuteAsync(Func`1 callbackMethod, IRetryPolicy retryPolicy, CancellationToken cancellationToken, Action`1 preRetryCallback)
   at Microsoft.Azure.Documents.GatewayStoreModel.InvokeAsync(DocumentServiceRequest request, ResourceType resourceType, CancellationToken cancellationToken)
   at Microsoft.Azure.Documents.GatewayStoreModel.ProcessMessageAsync(DocumentServiceRequest request, CancellationToken cancellationToken)
   at Microsoft.Azure.Documents.Client.DocumentClient.ExecuteQueryAsync(DocumentServiceRequest request, CancellationToken cancellationToken)
   at Microsoft.Azure.Documents.Query.DocumentQueryClient.ExecuteQueryAsync(DocumentServiceRequest request, CancellationToken cancellationToken)
   at Microsoft.Azure.Documents.Query.DocumentQueryExecutionContextBase.ExecuteQueryRequestInternalAsync(DocumentServiceRequest request, CancellationToken cancellationToken)
   at Microsoft.Azure.Documents.Query.DocumentQueryExecutionContextBase.ExecuteQueryRequestAsync(DocumentServiceRequest request, CancellationToken cancellationToken)
   at Microsoft.Azure.Documents.Query.DocumentQueryExecutionContextBase.ExecuteRequestAsync(DocumentServiceRequest request, CancellationToken cancellationToken)
   at Microsoft.Azure.Documents.Query.DefaultDocumentQueryExecutionContext.ExecuteOnceAsync(IDocumentClientRetryPolicy retryPolicyInstance, CancellationToken cancellationToken)
   at Microsoft.Azure.Documents.Query.DefaultDocumentQueryExecutionContext.<>c__DisplayClass9_0.<<ExecuteInternalAsync>b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.Azure.Documents.BackoffRetryUtility`1.<>c__DisplayClass1_0.<<ExecuteAsync>b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.Azure.Documents.BackoffRetryUtility`1.ExecuteRetryAsync(Func`1 callbackMethod, Func`3 callShouldRetry, Func`1 inBackoffAlternateCallbackMethod, TimeSpan minBackoffForInBackoffCallback, CancellationToken cancellationToken, Action`1 preRetryCallback)
   at Microsoft.Azure.Documents.ShouldRetryResult.ThrowIfDoneTrying(ExceptionDispatchInfo capturedException)
   at Microsoft.Azure.Documents.BackoffRetryUtility`1.ExecuteRetryAsync(Func`1 callbackMethod, Func`3 callShouldRetry, Func`1 inBackoffAlternateCallbackMethod, TimeSpan minBackoffForInBackoffCallback, CancellationToken cancellationToken, Action`1 preRetryCallback)
   at Microsoft.Azure.Documents.BackoffRetryUtility`1.ExecuteAsync(Func`1 callbackMethod, IRetryPolicy retryPolicy, CancellationToken cancellationToken, Action`1 preRetryCallback)
   at Microsoft.Azure.Documents.Query.DefaultDocumentQueryExecutionContext.ExecuteInternalAsync(CancellationToken cancellationToken)
   at Microsoft.Azure.Documents.Query.DocumentQueryExecutionContextBase.ExecuteNextAsync(CancellationToken cancellationToken)
   at Microsoft.Azure.Documents.Query.ProxyDocumentQueryExecutionContext.ExecuteNextAsync(CancellationToken token)
   at Microsoft.Azure.Documents.Linq.DocumentQuery`1.ExecuteNextPrivateAsync[TResponse](CancellationToken cancellationToken)
   at Microsoft.Azure.Cosmos.Table.Extensions.TableExtensionQueryHelper.QueryDocumentsAsync[TResult](Nullable`1 maxItemCount, String filterString, IList`1 selectColumns, TableContinuationToken token, CloudTableClient client, CloudTable table, EntityResolver`1 resolver, TableRequestOptions requestOptions, OperationContext operationContext, Boolean isLinqExpression, IList`1 orderByItems)
   at Microsoft.Azure.Cosmos.Table.Extensions.TableExtensionExecutor.<>c__DisplayClass19_0`2.<<ExecuteQuerySegmentedInternalAsync>b__0>d.MoveNext()
   --- End of inner exception stack trace ---
   at Microsoft.Azure.Cosmos.Table.Extensions.TableExtensionExecutor.<>c__DisplayClass19_0`2.<<ExecuteQuerySegmentedInternalAsync>b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.Azure.Cosmos.Table.Extensions.TableExtensionRetryPolicy.ExecuteUnderRetryPolicy[TResult](Func`1 executionMethod, CancellationToken cancellationToken, OperationContext operationContext, TableRequestOptions requestOptions)
   at CosmosDbTableClientDemo.Program.ExecuteCrossPartitionQueryWithOrderBy() in C:\Dev\Test\CosmosDbTableClientDemo\CosmosDbTableClientDemo\Program.cs:line 96
   at CosmosDbTableClientDemo.Program.Main(String[] args) in C:\Dev\Test\CosmosDbTableClientDemo\CosmosDbTableClientDemo\Program.cs:line 15
Request Information
RequestID:03088207-a91e-4ec2-b1c3-8294167e4b6a
RequestCharge:0
RequestDate:
StatusMessage:BadRequest
ErrorCode:
ErrorMessage:Message: {"errors":[{"severity":"Error","location":{"start":26,"end":28},"code":"SC1001","message":"Syntax error, incorrect syntax near 'by'."}]}
ActivityId: 03088207-a91e-4ec2-b1c3-8294167e4b6a, Microsoft.Azure.Documents.Common/2.2.0.0, Windows/10.0.17134 documentdb-netcore-sdk/2.1.3

As soon as i remove .OrderBy("RowKey") the query executes without exception.

[KalyanChanumolu-MSFT]

@marxxxx Could you please open a new issue for this.
Also, please provide the structure for TelemetryTableEntity.

[wmengmsft]

@marxxxx, thanks for reporting this. Can you please open an issue at https://github.com/Azure/azure-cosmos-table-dotnet/issues, and we will investigate asap.

[marxxxx]

@marxxxx, thanks for reporting this. Can you please open an issue at https://github.com/Azure/azure-cosmos-table-dotnet/issues, and we will investigate asap.

done: https://github.com/Azure/azure-cosmos-table-dotnet/issues/2

[marxxxx]

@marxxxx Could you please open a new issue for this.
Also, please provide the structure for TelemetryTableEntity.

I added structure of TelemetryTableEntity as requested.

[bernstml19]

Hey folks I see this support was added to .NET Standard but what about good old .NET? AKA Microsoft.Azure.CosmosDB.Table

[wmengmsft]

@bernstml19 , you should be able to consume Microsoft.Azure.Cosmos.Table from .NET Framework as well provided your application targets a sufficiently new version.

[freakyfriday]

What about OrderBy in the azure-storage-node library. I've raised it with the team but they said to email [email protected] (whom haven't replied to my query sent on March 26 2019)

https://github.com/Azure/azure-storage-node/issues/571

[ManfredLange]

@imeya, indeed Cosmos DB today does not support returning all query results naturally sorted in Partition Key and Row Key order. We are adding Order By support to TableQuery in our Cosmos DB Table SDK. It handles both single partition and cross partition query scenarios. This sorting during query processing has certain cost to it, so it does not entirely replace Azure Table's instrinsic PK and RK ordering behavior. However, if you think this could work for your scenario, we can share additional details and timelines.

I think at some point the statement was made that CosmosDB's Table API would be plug compatible with the classic Azure Storage Table API. Clearly, that statement is dead wrong. The mechanics might be the same but the behavior is very different in some key features. Sorting is just one of them. Writing objects with null value for a property results in that property not showing up in the table API when using CosmosDB. Also, property names that were acceptable for Azure Storage Table, e.g. "Id", now lead to errors.

For future reference: Please do not claim that an API is a plug-compatible replacement if the behavior has significant breaking changes. This just causes significant grief in the development community.