Cosmos DB is a schemaless, document database that provides high performance, availability, and scalability. These features are not necessarily incompatible with a standards-compliant query language like SQL-92. In this section we will show various schemes that the adapter offers to bridge the gap with relational SQL and a document database.

Working with Cosmos DB Objects as Tables

The adapter models the schemaless Cosmos DB objects into relational tables and translates SQL queries into Cosmos DB queries to get the requested data. See Query Mapping (Sql API) for more details on how various Cosmos DB operations are represented as SQL.

Discovering Schemas Automatically

The Automatic Schema Discovery scheme automatically finds the data types in a Cosmos DB object by scanning a configured number of rows of the object. You can use RowScanDepth, FlattenArrays, and FlattenObjects to control the relational representation of the collections in Cosmos DB. You can also write Free-Form Queries not tied to the schema.

Customizing Schemas

Optionally, you can use Custom Schema Definitions to project your chosen relational structure on top of a Cosmos DB object. This allows you to define your chosen names of columns, their data types, and the location of their values in the collection.

Set GenerateSchemaFiles to save the detected schemas as simple configuration files that are easy to extend. You can persist schemas for all collections in the database or for the results of SELECT queries.

Limitations of the RawValue TypeDetectionScheme

If the TypeDetectionScheme is set to RawValue, the adapter will push each document as single aggregate value on a column named JsonData, along with its resource identifier on the separate Primary Key column. The JSON documents are not processed, and as a result, the below functionalities are NOT supported with this configuration.

• Automatic Schema Discovery
• Free-Form Queries
• Vertical Flattening
• SQL API Built-In Functions
• SQL API GROUP BY
• Almost all server side supported filters apart from WHERE clause conditions built with the resource identifier.