How to configure Azure Cosmos DB for NoSQL materialized views (preview)

APPLIES TO: NoSQL

महत्वपूर्ण

Azure Cosmos DB for NoSQL materialized views are currently in preview. You can enable this feature by using the Azure portal. This preview is provided without a service-level agreement. At this time, we don't recommend that you use materialized views for production workloads. Certain features of this preview might not be supported or might have constrained capabilities. For more information, see the supplemental terms of use for Microsoft Azure previews.

Materialized views provide a powerful way to optimize query performance and simplify application logic by creating views of your data with a different partition key and/ or data model. This article describes how to create materialized views and how to use them to handle cross-partition queries efficiently.

Prerequisites

• An existing Azure Cosmos DB account.
  • If you have an Azure subscription, create a new account.
  • If you don't have an Azure subscription, create a free account before you begin.
  • Alternatively, you can try Azure Cosmos DB free before you commit.

Enable materialized views

The materialized views feature needs to be enabled for your Azure Cosmos DB account before provisioning a builder or creating views.

Azure portal

1. Sign in to the Azure portal.

2. Go to your Azure Cosmos DB for NoSQL account.

3. In the resource menu, select Settings.

4. In the Features section under Settings, toggle the Materialized View for NoSQL API (preview) option to On.

5. In the new dialog, select Enable to enable this feature for the account.

Azure CLI

Use the Azure CLI to enable the materialized views feature either by using a native command or a REST API operation on your Azure Cosmos DB for NoSQL account.

1. Sign in to the Azure CLI.

   Azure CLI

   az login
   

   नोट

   This requires the Azure CLI, see how to install the Azure CLI.

2. Define the variables for the resource group and account name of your existing Azure Cosmos DB for NoSQL account.

   Azure CLI

   # Variable for resource group name
   $resourceGroupName="<resource-group-name>"
   
   # Variable for account name
   $accountName="<account-name>"
   
   # Variable for Azure subscription
   $subscriptionId="<subscription-id>"
   

3. Create a new JSON file named capabilities.json by using the capabilities manifest.

   JSON

   {
     "properties": {
       "enableMaterializedViews": true
     }
   }
   

4. Get the identifier of the account and store it in a shell variable named $accountId.

   Azure CLI

   $accountId="/subscriptions/$subscriptionId/resourceGroups/$resourceGroupName/providers/Microsoft.DocumentDB/databaseAccounts/$accountName"
   

5. Enable the preview materialized views feature for the account by using the REST API and az rest with an HTTP PATCH verb.

   Azure CLI

   az rest \
       --method PATCH \
       --uri "https://management.azure.com/$accountId/?api-version=2022-11-15-preview" \
       --body @capabilities.json
   

Create a materialized view builder

After the materialized views feature is enabled for your account, you'll see a new page in the Settings section of the Azure portal for Materialized Views Builder. You must provision a materialized views builder before creating views in your account. The builder is responsible for automatically hydrating data in the views and keeping them in sync with source containers. Learn more about options for provisioning the materialized view builder.

Azure portal

1. Sign in to the Azure portal.

2. Go to your Azure Cosmos DB for NoSQL account.

3. In the resource menu, select Materialized Views Builder.

4. On the Materialized Views Builder page, configure the SKU and the number of instances for the builder.

   नोट

   This resource menu option and page appear only when the materialized views feature is enabled for the account.

5. Select Save.

Azure CLI

1. Define the variables for the resource group and account name of your existing Azure Cosmos DB for NoSQL account.

   Azure CLI

   # Variable for resource group name
   $resourceGroupName="<resource-group-name>"
   
   # Variable for account name
   $accountName="<account-name>"
   
   # Variable for Azure subscription
   $subscriptionId="<subscription-id>"
   

2. Create a new JSON file named builder.json by using the builder manifest. Update the instanceCount and instanceSize as needed.

   JSON

   {
     "properties": {
       "serviceType": "materializedViewsBuilder",
       "instanceCount": 1,
       "instanceSize": "Cosmos.D4s"
     }
   }
   

3. Get the identifier of the account and store it in a shell variable named $accountId.

   Azure CLI

   $accountId="/subscriptions/$subscriptionId/resourceGroups/$resourceGroupName/providers/Microsoft.DocumentDB/databaseAccounts/$accountName"
   

4. Enable the materialized views builder for the account using the REST API and az rest with an HTTP PUT verb:

   Azure CLI

   az rest \
       --method PUT \
       --uri "https://management.azure.com$accountId/services/materializedViewsBuilder/?api-version=2022-11-15-preview" \
       --body @builder.json
   

5. Wait for a couple of minutes, and then check the status by using az rest again with the HTTP GET verb. The status in the output should now be Running.

   Azure CLI

   az rest \
       --method GET \
       --uri "https://management.azure.com$accountId/services/materializedViewsBuilder/?api-version=2022-11-15-preview"
   

Create a materialized view

After the feature is enabled and the materialized view builder is provisioned, you can create materialized views using the REST API.

1. Use the Azure portal, the Azure SDKs, the Azure CLI, or the REST API to create a source container that has /customerId as the partition key path. Name this source container mv-src.

   नोट

   The /customerId field is used only as an example in this article. For your own containers, select a partition key that works for your solution.

2. Insert a few items in the source container. To follow the examples that are shown in this article, make sure that the items have customerId and emailAddress fields. A sample item might look like this:

   JSON

   {
     "id": "eaf0338e-2b61-4163-822f-7bef75bf51de",
     "customerId": "36c7cc3d-1709-45c6-819f-10e5586a6cb7",
     "emailAddress": "justine@contoso.com",
     "name": "Justine"
   }
   

   नोट

   In this example, you populate the source container with sample data before adding a view. You can also create a materialized view from an empty source container.

3. Now, create a materialized view named mv-target with a partition key path that is different from the source container. For this example, specify /emailAddress as the partition key path for the mv-target container.

   1. Create a definition manifest for a materialized view and save it in a JSON file named mv-definition.json:

      JSON

      {
        "location": "North Central US",
        "tags": {},
        "properties": {
          "resource": {
            "id": "mv-target",
            "partitionKey": {
              "paths": [
                "/emailAddress"
              ]
            },
            "materializedViewDefinition": {
              "sourceCollectionId": "mv-src",
              "definition": "SELECT c.customerId, c.emailAddress FROM c"
            }
          },
          "options": {
            "throughput": 400
          }
        }
      }        
      

   महत्वपूर्ण

   In the template, notice that the partition key path is set as /emailAddress. The sourceCollectionId defines the source container for the view and the definition contains a query to determine the data model of the view. Learn more about defining materialized views and the query constraints.

   The materialized view source container and definition query can't be changed once created.

4. Next, make a REST API call to create the materialized view as defined in the mv-definition.json file. Use the Azure CLI to make the REST API call.

   1. Create a variable for the name of the materialized view and source database name:

      Azure CLI

      # This should match the resource ID you defined in your json file
      $materializedViewName = "mv-target"
      
      # Database name for the source and view containers
      $databaseName = "<Database that contains source container>"
      
      # Azure Cosmos DB account name
      $accountName = "<Azure Cosmos DB account name>"
      
      # Resource name for your Azure Cosmos DB account
      $resourceGroupName = "<Resource group for Azure Cosmos DB account>"
      
      # Subscription id for your Azure Cosmos DB account
      $subscriptionId = "<Subscription id>"
      

   2. Construct the resource ID using these variables.

      Azure CLI

      $accountId = "/subscriptions/$subscriptionId/resourceGroups/$resourceGroupName/providers/Microsoft.DocumentDB/databaseAccounts/$accountName"
      

   3. Make a REST API call to create the materialized view:

      Azure CLI

      az rest \
          --method PUT \
          --uri "https://management.azure.com$accountId/sqlDatabases/ \
                $databaseName/containers/$materializedViewName/?api-version=2022-11-15-preview" \
          --body @mv-definition.json \
          --headers content-type=application/json
      

   4. Check the status of the materialized view container creation by using the REST API:

      Azure CLI

      az rest \
          --method GET \
          --uri "https://management.azure.com$accountId/sqlDatabases/
                $databaseName/containers/$materializedViewName/?api-version=2022-11-15-preview" \
          --headers content-type=application/json \
          --query "{mvCreateStatus: properties.Status}"
      

5. After the materialized view is created, the materialized view builder automatically syncs changes with the source container. Try executing create, update, and delete operations in the source container. You'll see the same changes propagated to the materialized view container.

Query data from materialized views

In this example, we have a source container partitioned on customerId and a view partitioned on emailAddress. Without the view, queries that only include the emailAddress would be cross-partition, but now they can use be executed against the view instead to increase efficiency.

Querying data from materialized views is similar to querying data from any other container. You can use the Azure portal, Azure SDKs, or REST API to query data in materialized views.

.NET

C#

Container container = client.GetDatabase("mv-db").GetContainer("mv-target");

FeedIterator<MyClass> myQuery = container.GetItemQueryIterator<MyClass>(new QueryDefinition("SELECT * FROM c WHERE c.emailAddress = 'justine@contoso.com'"));

Java

Java

CosmosAsyncDatabase container = client.getDatabase("mv-db");
CosmosAsyncContainer container = database.getContainer("mv-target");

CosmosPagedFlux<MyClass> pagedFluxResponse = container.queryItems(
        "SELECT * FROM c WHERE c.emailAddress = 'justine@contoso.com'", null, MyClass.class);

Node.js

JavaScript

const database = client.database("mv-db");
const container = database.container("mv-target");

const querySpec = {
    query: "SELECT * FROM c WHERE c.emailAddress = 'justine@contoso.com'"
 };
const { resources: items } = await container.items
    .query(querySpec)
    .fetchAll();

Python

Python

database = client.get_database_client("mv-db")
container = database.get_container_client("mv-target")

query = "SELECT * FROM c WHERE c.emailAddress = 'justine@contoso.com'"
container.query_items(
    query=query
)

Next steps

Data modeling and partitioning Materialized views overview