Azure Cosmos DB SQL API Documentation

Azure Cosmos DB SQL API

The Azure Cosmos DB SQL API provides a rich, semantic query language that allows you to efficiently query document data. It's a powerful and flexible API that supports complex queries with JSON data, making it ideal for a wide range of applications.

This documentation will guide you through understanding the SQL API, its core concepts, how to get started, and advanced topics such as performance optimization and troubleshooting.

Getting Started with the SQL API

To begin using the SQL API, you'll need to create an Azure Cosmos DB account and then a database and container within that account. You can then use the provided SDKs or the REST API to interact with your data.

Steps:

Create an Azure Cosmos DB account in the Azure portal.
Create a new SQL API database and container.
Choose an SDK (e.g., .NET, Java, Python, Node.js) or use the REST API.
Connect to your Cosmos DB account using connection strings.
Start performing CRUD operations and querying your data.

Note: Ensure you have the necessary permissions and have installed the appropriate SDKs for your development environment.

Core Concepts

Data Model

The SQL API uses a document data model. Data is stored in JSON format within containers. Each item in a container is a self-contained JSON document.

                        
{
    "id": "item1",
    "category": "gear",
    "name": "Kayak",
    "description": "A multi-purpose kayak.",
    "price": 2849.99,
    "tags": ["water", "boating", "fishing"],
    "metadata": {
        "brand": "Azure Kayaks",
        "color": "blue"
    }
}
                        
                    

Querying Data

The SQL API allows you to use a familiar SQL-like syntax to query your JSON documents. Queries are executed against containers.

Example: Selecting all items in the 'gear' category:

SELECT * FROM c WHERE c.category = 'gear'

Example: Selecting specific properties:

SELECT c.name, c.price FROM c WHERE c.category = 'gear'

The SQL API supports a wide range of SQL features, including JOINs, aggregations, subqueries, and more.

Indexing

Azure Cosmos DB automatically indexes all the data written to your containers, and requires no explicit indexing or schema definition. The default indexing policy indexes all properties within your JSON documents. You can customize the indexing policy to optimize performance for specific query patterns.

Default Indexing Policy Snippet:

                        
{
    "indexingMode": "consistent",
    "automatic": true,
    "includedPaths": [
        { "path": "/*" }
    ],
    "excludedPaths": [
        { "path": "/\"_etag\"/?" }
    ]
}
                        
                    

SDK Examples

Azure Cosmos DB offers SDKs for various popular programming languages. Here's a glimpse of how you might create an item using the .NET SDK.

                        
// .NET SDK Example
var item = new
{
    id = "item3",
    category = "personal",
    name = "Mobile phone",
    description = "A state-of-the-art mobile phone.",
    price = 1200.00,
    tags = new string[] { "electronics", "communication" }
};

await container.CreateItemAsync(item, new PartitionKey(item.category));
                        
                    

Refer to the official SDK documentation for detailed examples in your preferred language.

REST API

You can interact with Azure Cosmos DB using its comprehensive REST API. This allows for platform-agnostic integration.

Example: Creating a document via REST API (POST Request):

                        
POST https://{databaseaccount}.documents.azure.com/dbs/{db_id}/colls/{coll_id}/docs

{
    "id": "rest-item-1",
    "name": "REST Document",
    "value": 100
}
                        
                    

Remember to include appropriate authorization headers for all REST API calls.

Performance Tips

Partitioning: Choose a good partition key to distribute requests evenly across physical partitions.
Indexing: Optimize your indexing policy to include only necessary paths.
Request Units (RUs): Understand and manage RU consumption.
Batching: Utilize batch operations for multiple writes.
SDK Optimization: Use the latest SDKs and configure connection pooling.

Tip: Monitor your Request Unit (RU) usage in the Azure portal to identify potential bottlenecks and optimize your costs.

Troubleshooting

Common issues include performance degradation, throttling (429 errors), and incorrect query syntax. Utilize Azure Monitor and diagnostic logs to pinpoint problems.

Common Error Codes:

Code	Description
400	Bad Request
404	Not Found
429	Too Many Requests (Throttling)
500	Internal Server Error