none
Querying Azure Tables on an entity property

    Question

  • Hi,

    I have created an Azure Table by the name 'Employee' with partition key as 'lastName' and row key as 'firstName'. It also has other properties like 'Email' and 'Department'.

    The questions that I have are as follows:

    1. Is there a way to query the table to retrieve entities based on 'Department'?
    2. If the answer to the above question is yes - how will the above operation work? Would the entire table be first fetched and then the filtering criteria based on 'Department' be applied - OR - would the entities be fetched from table based on 'Department' property in the first place?
    3. Would there be any performance overheads in querying the table based on 'Department' (as it is neither partition key nor the row key ?)
    4. Any other issues with this approach?

    Regards,
    Saurabh
    Friday, December 2, 2016 6:58 AM

All replies

  • Hello,

    We are checking on the query and would get back to you soon on this. I apologize for the inconvenience and appreciate your time and patience in this matter.

    Regards,
    Sumanth BM

    Friday, December 2, 2016 5:02 PM
  • Hi,

    Sorry for the delay response!

    They can generate a filter condition for the property using Generate Filter Condition method from the SDK

    https://msdn.microsoft.com/en-us/library/microsoft.windowsazure.storage.table.tablequery.generatefiltercondition.aspx

    Create Table Query using that filter

    https://msdn.microsoft.com/en-us/library/microsoft.windowsazure.storage.table.tablequery.aspx

    and call Execute Query using that query

    https://msdn.microsoft.com/en-us/library/azure/jj732055.aspx

    that will return only the matching entities from the azure table storage service so it is not going to fetch everything to client first and filter that locally.

    The issue with this is that it is going to cause a full table scan every time you run this query which is pretty bad performance. One of the things they can do is to deformalize the data and create a different partition (or an entirely new table) that has Department as the partition key and query that table instead of your original table (lastName/ firstName).

    The challenge with that approach is maintaining the consistency between the 2 partitions (or tables if you create a new table with Department partition key). Azure table storage only supports atomicity of multiple table operations (Entity Group Transactions) within the same table and same partition.

    To keep both tables or partitions in an eventually consistent state, they may apply eventual consistent transaction pattern.

    All in all it comes down to their requirements, are they concerned about consistency, how big their lastName/firstName table is / will be in future, current / future RPS to that table, latency requirements etc. Based on these and some experimentation on their end, they can decide what pattern to apply between the 2 extremes of querying table by property vs denormalizing and applying an eventual consistent transaction pattern.

    Azure team would most likely give more insights and more options.

    I hope that the reply will assist you in getting your query addressed. In case you require further assistance, please do reply to the thread as we are always available to your queries.

    Regards,
    Sumanth BM

    --------------------------------------------------------------------------------------------------

    Kindly click "Mark as Answer" on the post that helps you, this can be beneficial to other community members reading the thread. And Vote as Helpful.


    Tuesday, January 3, 2017 4:27 PM