Azure Cosmos DB SQL API Data Provider requirements, configuration, and best practices

This topic describes the requirements for the Azure Cosmos DB for SQL API Data Provider as well as how to configure and recommended best practices when you use the Azure Cosmos DB for SQL API Data Provider with virtual entities.

Important

  • A preview feature is a feature that is not complete, but is made available before it’s officially in a release so customers can get early access and provide feedback. Preview features aren’t meant for production use and may have limited or restricted functionality.
  • We expect changes to this feature, so you shouldn’t use it in production. Use it only in test and development environments.
  • Microsoft doesn't provide support for this preview feature. Microsoft Dynamics 365 Technical Support won’t be able to help you with issues or questions. Preview features aren't meant for production use and are subject to a separate supplemental terms of use.

What is Azure Cosmos DB?

Azure Cosmos DB is Microsoft's globally distributed multi-model database service for mission-critical applications. It provides rich and familiar SQL query capabilities with consistent low latencies over schema-less JSON data. More information: Introduction to Azure Cosmos DB: SQL API

Requirements

  • Azure subscription that includes Azure Cosmos DB.
  • An Azure Cosmos DB SQL API collection.
  • The Azure Cosmos DB database type should be SQL.

Data type mapping

Suppose you have an Azure Cosmos DB document in a collection named Orders that has the following JSON structure.

Example JSON for SQL API document.

This table indicates the data type mappings for the SQL API document in the Orders collection with the appropriate data type.

SQL API Data Data Type
id Primary Key
name Single Line of Text
quantity Whole Number
orderid Single line of text
ordertype Option Set
amount Decimal Number or Currency
delivered Two Options
datetimeoffset Date and Time

Note

  • Attributes with an underscore (_) prefix are generated by the SQL API.
  • Attributes that are configured as optional in the SQL API document and are mapped as Business Required will cause a runtime error.
  • id attribute values must be guids.
  • For more information about using dates in SQL API, see Working with Dates in Azure Cosmos DB.

Supported SQL query filtering

SQL query filtering supports the following operators.

  • Comparison operators:<,>,<=, >=,!=
  • Logical operators: and, or
  • Set operators: in, not in
  • String operators: like, contains, begins with, ends with

Note

Usage of the like operator is translated to the equivalent contains/begins with/ends with operators. The SQL API does not support pattern arguments as described in the topic Like (Transact-SQL). The Azure Cosmos DB for SQL API Data Provider can translate the single special case Like(‘[aA]%’) to BeginsWith(‘a’) OR BeginsWith(‘A’). Notice that string comparison in the SQL API is case sensitive.

Add a data source using the Azure Cosmos DB for SQL API Data Provider

  1. Go to AppSource, select GET IT NOW, and follow the instructions to add the application to your environment or Dynamics 365 Customer Engagement (on-premises) or later instance.
  2. After the solution is installed, sign in to the environment and go to Settings > Administration > Virtual Entity Data Sources.
  3. On the Actions toolbar select NEW, and in the Select Data Provider dialog box select Azure Cosmos DB for SQL API Data Provider, and then select OK. Select the Azure Cosmos DB for SQL API Data Provider.
  4. Enter the following information, and then select SAVE & CLOSE.
    • Name. Type a name that describes the data source.
    • Collection Name. The name of the Azure Cosmos DB database containing the collection you want to surface in a virtual entity.
    • Authorization Key. The primary or secondary key for the Azure Cosmos DB account. You can find the key from the Azure admin portal under the Keys setting under your Azure Cosmos DB account.
    • Uri. The URI of the resource group where the Azure Cosmos DB collection is located. The URI is formed similar to https://contoso/documents.azure.com:443. You can find the URI from the Azure admin portal under the Keys setting for the Azure Cosmos DB account.
    • Timeout in seconds. Enter the number of seconds to wait for a response from the Azure Cosmos DB service before a data request time-out. For example, enter 30 to wait a maximum of thirty seconds before a time-out occurs. The default timeout is 120 seconds.

Create the data source using the SQL API Data Provider.

Best practices and limitations

  • Notice the following when you use Azure Cosmos DB as a Data Source:

    • Each Azure Cosmos DB Data Source can only be associated with a single virtual entity.
    • You can connect multiple Data Sources to the same Collection in the Azure Cosmos DB.
  • You can’t segment the data in a collection by entity.

  • Azure Cosmos DB databases do not require a schema, however the data within the Azure Cosmos DB must be structured using a predictable schema.

  • Although the Azure Cosmos DB for SQL API Data Provider implements query translation of projection, filtering, and sorting operators, it does not support join operations.

  • You can only filter by a single column with SQL API.

See also

Create and edit virtual entities that contain data from an external data source