Azure Documentation

Querying Azure Table Storage

This tutorial guides you through the process of querying data efficiently from Azure Table Storage. Azure Table Storage is a NoSQL key-attribute store that allows you to store large amounts of structured, non-relational data. Querying is a fundamental operation to retrieve specific entities based on your criteria.

Introduction to Table Storage Queries

Table storage queries can be simple point lookups using the PartitionKey and RowKey, or they can involve filtering entities based on the values of other properties. Understanding the query capabilities and how to construct efficient queries is crucial for performance and cost optimization.

Key Concepts for Querying

• PartitionKey: Entities with the same PartitionKey are stored together, enabling efficient retrieval of related data.
• RowKey: Within a PartitionKey, entities are sorted by RowKey, allowing for range queries and fast lookups.
• Filter Expressions: You can specify filter expressions to retrieve entities that match certain property values. These expressions use OData syntax.
• Indexing: Azure Table Storage automatically indexes the PartitionKey and RowKey. For better query performance on other properties, consider using projections or creating a secondary index table.

Constructing Filter Expressions

Filter expressions are built using OData query syntax. Here are some common operators:

• Comparison: eq (equals), ne (not equals), gt (greater than), ge (greater than or equal to), lt (less than), le (less than or equal to)
• Logical: and, or, not
• Arithmetic: add, sub, mul, div
• String Functions: substringof, length, concat, tolower, toupper, trim, startswith, endswith

Example: Simple Filter

To retrieve all entities where the 'Status' property is 'Active':

$filter=Status eq 'Active'

Example: Compound Filter

To retrieve entities where 'Category' is 'Electronics' AND 'Price' is less than 100:

$filter=Category eq 'Electronics' and Price lt 100

Example: Using Partition and Row Keys

To retrieve a specific entity:

PartitionKey='MyPartition' and RowKey='Entity1'

Using SDKs for Queries

Azure provides SDKs for various languages (e.g., .NET, Python, Java, Node.js) that simplify the process of constructing and executing table queries.

.NET SDK Example (LINQ to Table)

using Microsoft.Azure.Cosmos.Table;

// Assuming 'table' is a CloudTable object
TableQuery<DynamicTableEntity> query = new TableQuery<DynamicTableEntity>()
    .Where(TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, "Customers"));

foreach (var entity in table.ExecuteQuery(query))
{
    Console.WriteLine($"RowKey: {entity.RowKey}, Name: {entity.Properties["Name"].StringValue}");
}

Python SDK Example

from azure.storage.table import TableService, Entity

table_service = TableService(account_name='YOUR_ACCOUNT_NAME', account_key='YOUR_ACCOUNT_KEY')

query = table_service.query_entities('Tasks', '$filter="PartitionKey eq \'Sales\'"')

for task in query:
    print(f"Description: {task.Description}, DueDate: {task.DueDate}")

Best Practices for Table Storage Queries

• Query by PartitionKey: Always include the PartitionKey in your queries for efficiency.
• Use RowKey for Range Queries: Leverage the sorted nature of RowKeys for efficient range filtering.
• Select Specific Properties (Projections): Use the $select OData query option to retrieve only the properties you need. This reduces network traffic and improves performance.
• Batch Operations: For multiple individual entity operations (reads, writes, updates, deletes), consider using batch operations to reduce the number of network requests.
• Monitor Performance: Use Azure Monitor and diagnostics logs to track query performance and identify potential bottlenecks.

Next Steps

Now that you understand how to query Azure Table Storage, you can explore other advanced topics such as managing entities, handling concurrent operations, and implementing strategies for large-scale data storage.

Previous: Azure Storage Queues Next: Advanced Table Storage Topics