Azure Cosmos DB Connection Details¶
Introduction¶
Connector Version
This documentation is based on version 23.0.8895 of the connector.
Get Started¶
Azure Cosmos DB Version Support
The connector enables standards-based access to Azure Cosmos DB.
Establish a Connection¶
Before You Connect¶
Role Assignment¶
Ensure that the Azure identity has the correct role assignment. The identity is the account that you log in to the browser during AzureAD authentication or the Application itself for AzureServicePrincipal
authentication. Please visit the link below for more details:
Configure role-based access control for your Azure Cosmos DB account with Azure AD
You can either assign one of the built-in role definitions:
- CosmosDB Built-in Data Reader
- CosmosDB Built-in Data Contributor
or create your own custom role definitions. You must also set the scope of the role assignment, where "/" means that the identity has access to all the databases.
Connect to Azure Cosmos DB¶
Account Key¶
Log in to the Azure Portal, select Azure Cosmos DB
, and select your account.
Set the following to authenticate:
AccountEndpoint
: The Cosmos DB account URL. Set this to theURI
value found in theSettings > Keys
blade of the Cosmos DB account.AccountKey
: A master key token or a resource token for connecting to Azure Cosmos DB. Set this to thePRIMARY KEY
value found in theSettings > Keys
blade of the Cosmos DB account.TokenType
: (optional). Set this to "master" (the default value) if you are using a Master Token, which is a full permissions token generated during account creation. Otherwise, set this property to "resource" if you are using a Resource Token, which is a custom permissions token generated when a database user is set up.
Azure AD¶
Azure AD is Microsoft’s multi-tenant, cloud-based directory and identity management service. It is user-based authentication that requires that you set AuthScheme
to AzureAD
.
Authentication to Azure AD over a Web application always requires the creation of a custom OAuth application. For details, see Creating an Azure AD Application.
Desktop Applications¶
provides an embedded OAuth application that simplifies connection to Azure AD from a Desktop application.
You can also authenticate from a desktop application using a custom OAuth application. (For further information, see Creating an Azure AD Application.) To authenticate via Azure AD, set these parameters:
AuthScheme
:AzureAD
.- Custom applications only:
OAuthClientId
: The client ID assigned when you registered your custom OAuth application.OAuthClientSecret
: The client secret assigned when you registered your custom OAuth application.CallbackURL
: The redirect URI you defined when you registered your custom OAuth application.
When you connect, the connector opens Azure Cosmos DB's OAuth endpoint in your default browser. Log in and grant permissions to the application.
The connector completes the OAuth process, obtaining an access token from Azure Cosmos DB and using it to request data. The OAuth values are saved in the path specified in OAuthSettingsLocation
. These values persist across connections.
When the access token expires, the connector refreshes it automatically.
Azure Service Principal¶
Azure Service Principal is role-based application-based authentication. This means that authentication is done per application, rather than per user. All tasks taken on by the application are executed without a default user context, but based on the assigned roles. The application access to the resources is controlled through the assigned roles' permissions.
For information about how to set up Azure Service Principal authentication, see Creating an Azure AD Application with Service Principal.
Create an Azure AD Application¶
Create an Azure AD Application¶
Azure Cosmos DB supports user-based authentication using Azure AD. This authentication is OAuth-based.
To connect to Azure Cosmos DB via the Web, you must always create a custom application, as described here.
Custom OAuth applications are useful if you want to:
- Control branding of the authentication dialog.
- Control the redirect URI that the application redirects the user to after the user authenticates.
- Customize the permissions that you are requesting from the user.
Authenticate With Azure AD¶
In <https://portal.azure.com>:
-
In the left-hand navigation pane, select
Azure Active Directory > App registrations
. -
Click
New registration
. -
Enter a name for the application.
-
Specify the types of accounts this application should support:
- For private use applications, select
Accounts in this organization directory only
. - For distributed applications, select one of the multi-tenant options.
- For private use applications, select
Note
If you select Accounts in this organizational directory only
(default), when you establish a connection with Azure Cosmos DB connector you must set AzureTenant
to the ID of the Azure AD Tenant. Otherwise, the authentication attempt fails.
-
Set the redirect URI to
http://localhost:33333
(default) OR, if you want tospecify a different port, specify the desired port and set
CallbackURL
to the exact reply URL you just defined. -
To register the new application, click
Register
. An application management screen displays. Record these values for later use. (You will use theApplication (client) ID
value to set theOAuthClientId
parameters, and theDirectory (tenant) ID
value to set theAzureTenant
parameter.) -
Navigate to
Certificates & Secrets
. SelectNew Client Secret
for this application and specify the desired duration. After the client secret is saved, the Azure App Registration displays the key value. This value is displayed only once, so record it for future use. (You will use it to set theOAuthClientSecret
.) -
If you have specified the use of permissions that require admin consent (such as the Application Permissions), you can grant them from the current tenant on the API Permissions page.
Create an Azure AD Application with Service Principal¶
Create an Azure AD Application with Service Principal¶
Azure Cosmos DB supports Service Principal-based authentication, which is role-based. If you wish to use a Service Principal to authenticate to Azure Cosmos DB you must create a custom Azure AD application as described here. To use Azure Service Principal authentication, you must set up the ability to assign a role to the authentication application, then register an application with the Azure AD tenant to create a new Service Principal. That new Service Principal can then leverage the assigned role-based access control to access resources in your subscription.
Authenticate with an Azure Service Principal¶
In <https://portal.azure.com>:
- In the left-hand navigation pane, select
Azure Active Directory > App registrations
. - Click
New registration
. - Enter a name for the application.
- Select the desired tenant setup. Since this custom application is for Azure Service Principal, choose
Any Microsoft Entra ID tenant - Multi Tenant
. - To register the new application, click
Register
. An application management screen displays.
Note the value inApplication (client) ID
as theOAuthClientId
and theDirectory (tenant) ID
as theAzureTenant
. - Navigate to
Certificates & Secrets
and define the application authentication type. There are two types of authentication available: certificate (recommended) or client secret.- For certificate authentication: In
Certificates & Secrets
, selectUpload certificate
, then upload the certificate from your local machine. For an example of how to create this certificate, seehttps://learn.microsoft.com/en-us/entra/identity-platform/howto-create-self-signed-certificate
. - For creating a new client secret: In
Certificates & Secrets
, selectNew Client Secret
for the application and specify its duration. After the client secret is saved, Azure Cosmos DB displays the key value. This value is displayed only once, so record it for future use. (This value becomes theOAuthClientSecret
.)
- For certificate authentication: In
- Navigate to the Authentication tab and select the
Access tokens
option. - Save your changes.
Consent for Client Credentials¶
OAuth supports the use of client credentials to authenticate. In a client credentials authentication flow, credentials are created for the authenticating application itself. The auth flow acts just like the usual auth flow, except that there is no prompt for an associated user to provide credentials.
All tasks accepted by the application are executed outside of the context of a default user.
Note
Since the embedded OAuth credentials authenticate on a per-user basis, you cannot
use them in a client authentication flow. You must always create a custom Azure AD application to use client credentials.
In <https://portal.azure.com>:
- Create a custom Azure AD application, as described above.
- Navigate to
App Registrations
. - Find the application you just created, and open
API Permissions
. - Select the Microsoft Graph permissions. There are two distinct sets of permissions: Delegated and Application.
- For use with Service Principal, specify
Application
permissions. - Select the permissions you require for your integration.
Fine-Tuning Data Access¶
Fine Tuning Data Access¶
You can use the following properties to gain greater control over Azure Cosmos DB API features and the strategies the connector uses to surface them:
RowScanDepth
: This property determines the number of rows that will be scanned to detect column data types when generating table metadata.TypeDetectionScheme
: This property allows more control over the strategy implemented by theRowScanDepth
property.GenerateSchemaFiles
: This property enables you to persist table metadata in static schema files that are easy to customize, to persist your changes to column data types, for example.
You can set this property to "OnStart" to generate schema files for all tables in your database at connection. Or, you can generate schemas as you execute SELECT queries to tables.
The resulting schemas are based on the connection properties you use to configure Automatic Schema Discovery
To use the resulting schema files, set theLocation
property to the folder containing the schemas.
Set a RU Budget for Batch Writes¶
Just as described in the SQL Compliance the connector supports batch CUD (Create, Update, Delete) operations. Batch processing is achieved by issuing multiple requests simultaneously. Even though this method greatly improves the performance for write operations, the cost of these operations is relatively high, thus the Request Units (RU) budget per second for a certain container or database may be exceeded. Depending on your Azure Cosmos DB Service Quotas, exceeding the RU budgets may incur in extra costs, or it may even temporary throttle or interrupt the Azure Cosmos DB usage for other workloads.
In order to avoid exceeding the RU budget per second, the connector dynamically adjusts the number of concurrent requests per second depending on the set WriteThroughputBudget
and the constantly adjusted average RU cost per statement. The user can utilize the WriteThroughputBudget
connection property to define the RU budged per second, that batch write operations should not exceed. Another important factor in batch write operations is the MaxThreads
connection property, which specifies the maximum number of concurrent requests. If using a low MaxThreads
value, the connector might not be able to efficiently use the available budget.
Since the requests throttling logic is applied client-side, in a few cases the RU/s budged may be exceeded by a relatively small amount. These cases include inserting, updating and deleting records with highly variable column count and input value length per column.
Note
By default, the WriteThroughputBudget
property is set 1000 RU/s and the MaxThreads
property is set to 200 threads.
Important Notes¶
Configuration Files and Their Paths¶
- All references to adding configuration files and their paths refer to files and locations on the Jitterbit agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.
NoSQL Database¶
Azure 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 connector offers to bridge the gap with relational SQL and a document database.
Work with Azure Cosmos DB Objects as Tables
The connector models the schemaless Azure Cosmos DB objects into relational tables and translates SQL queries into Azure Cosmos DB queries to get the requested data. See Query Mapping (Sql API) for more details on how various Azure Cosmos DB operations are represented as SQL.
Discover Schemas Automatically
The Automatic Schema Discovery scheme automatically finds the data types in a Azure 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 Azure Cosmos DB. You can also write Free-Form Queries not tied to the schema.
Customize Schemas
Optionally, you can use Custom Schema Definitions to project your chosen relational structure on top of a Azure 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 connector 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.
Automatic Schema Discovery¶
The connector automatically infers a relational schema by inspecting a series of Azure Cosmos DB documents in a collection. You can use the RowScanDepth
property to define the number of documents the connector will scan to do so. The columns identified during the discovery process depend on the FlattenArrays
and FlattenObjects
properties.
Flatten Objects¶
If FlattenObjects
is set, all nested objects will be flattened into a series of columns. For example, consider the following document:
{
id: 12,
name: "Lohia Manufacturers Inc.",
address: {street: "Main Street", city: "Chapel Hill", state: "NC"},
offices: ["Chapel Hill", "London", "New York"],
annual_revenue: 35,600,000
}
This document will be represented by the following columns:
Column Name | Data Type | Example Value |
---|---|---|
id | Integer | 12 |
name | String | Lohia Manufacturers Inc. |
address.street | String | Main Street |
address.city | String | Chapel Hill |
address.state | String | NC |
offices | String | ["Chapel Hill", "London", "New York"] |
annual_revenue | Double | 35, 600, 000 |
If FlattenObjects
is not set, then the address.street, address.city, and address.state columns will not be broken apart. The address column of type string will instead represent the entire object. Its value would be {street: "Main Street", city: "Chapel Hill", state: "NC"}
. See JSON Functions for more details on working with JSON aggregates. You can change the separator character in the column name from a dot by setting SeparatorCharacter
.
Flatten Arrays¶
The FlattenArrays
property can be used to flatten array values into columns of their own. This is only recommended for arrays that are expected to be short, for example the coordinates below:
"coord": [ -73.856077, 40.848447 ]
The FlattenArrays
property can be set to 2 to represent the array above as follows:
Column Name | Data Type | Example Value |
---|---|---|
coord.0 | Float | -73.856077 |
coord.1 | Float | 40.848447 |
It is best to leave other unbounded arrays as they are and piece out the data for them as needed using JSON Functions.
Free-Form Queries¶
As discussed in Automatic Schema Discovery, intuited table schemas enable SQL access to unstructured Azure Cosmos DB data. JSON Functions enable you to use standard JSON functions to summarize Azure Cosmos DB data and extract values from any nested structures. Custom Schema Definitions enable you to define static tables and give you more granular control over the relational view of your data; for example, you can write schemas defining parent/child tables or fact/dimension tables. However, you are not limited to these schemes.
After connecting you can query any nested structure without flattening the data. Any relations that you can access with FlattenArrays
and FlattenObjects
can also be accessed with an ad hoc SQL query.
Let's consider an example document from the following Restaurant data set:
{
"address": {
"building": "1007",
"coord": [
-73.856077,
40.848447
],
"street": "Morris Park Ave",
"zipcode": "10462"
},
"borough": "Bronx",
"cuisine": "Bakery",
"grades": [
{
"grade": "A",
"score": 2,
"date": {
"$date": "1393804800000"
}
},
{
"date": {
"$date": "1378857600000"
},
"grade": "B",
"score": 6
},
{
"score": 10,
"date": {
"$date": "1358985600000"
},
"grade": "C"
}
],
"name": "Morris Park Bake Shop",
"restaurant_id": "30075445"
}
You can access any nested structure in this document as a column. Use the dot notation to drill down to the values you want to access as shown in the query below. Note that arrays have a zero-based index. For example, the following query retrieves the second grade for the restaurant in the example:
SELECT [address.building], [grades.1.grade] FROM restaurants WHERE restaurant_id = '30075445'
The preceding query returns the following results:
Column Name | Data Type | Example Value |
---|---|---|
address.building | String | 1007 |
grades.1.grade | String | A |
Vertical Flattening¶
It is possible to retrieve an array of documents as if it were a separate table. Take the following JSON structure from the restaurants collection for example:
{
"_id" : ObjectId("568c37b748ddf53c5ed98932"),
"address" : {
"building" : "1007",
"coord" : [-73.856077, 40.848447],
"street" : "Morris Park Ave",
"zipcode" : "10462"
},
"borough" : "Bronx",
"cuisine" : "Bakery",
"grades" : [{
"date" : ISODate("2014-03-03T00:00:00Z"),
"grade" : "A",
"score" : 2
}, {
"date" : ISODate("2013-09-11T00:00:00Z"),
"grade" : "A",
"score" : 6
}, {
"date" : ISODate("2013-01-24T00:00:00Z"),
"grade" : "A",
"score" : 10
}, {
"date" : ISODate("2011-11-23T00:00:00Z"),
"grade" : "A",
"score" : 9
}, {
"date" : ISODate("2011-03-10T00:00:00Z"),
"grade" : "B",
"score" : 14
}],
"name" : "Morris Park Bake Shop",
"restaurant_id" : "30075445"
}
Vertical flattening will allow you to retrieve the grades array as a separate table:
SELECT * FROM [restaurants.grades]
This query returns the following data set:
date | grade | score | P_id | _index |
---|---|---|---|---|
2014-03-03T00:00:00.000Z | A | 2 | 568c37b748ddf53c5ed98932 | 1 |
2013-09-11T00:00:00.000Z | A | 6 | 568c37b748ddf53c5ed98932 | 2 |
2013-01-24T00:00:00.000Z | A | 10 | 568c37b748ddf53c5ed98932 | 3 |
You may also want to include information from the base restaurants table. You can do this with a join. Flattened arrays can only be joined with the root document. The connector expects the left part of the join is the array document you want to flatten vertically. Disable SupportEnhancedSQL
to join nested Azure Cosmos DB documents -- this type of query is supported through the Azure Cosmos DB API.
SELECT [restaurants].[restaurant_id], [restaurants.grades].* FROM [restaurants.grades] JOIN [restaurants] WHERE [restaurants].name = 'Morris Park Bake Shop'
This query returns the following data set:
restaurant_id | date | grade | score | P_id | _index |
---|---|---|---|---|---|
30075445 | 2014-03-03T00:00:00.000Z | A | 2 | 568c37b748ddf53c5ed98932 | 1 |
30075445 | 2013-09-11T00:00:00.000Z | A | 6 | 568c37b748ddf53c5ed98932 | 2 |
30075445 | 2013-01-24T00:00:00.000Z | A | 10 | 568c37b748ddf53c5ed98932 | 3 |
30075445 | 2011-11-23T00:00:00.000Z | A | 9 | 568c37b748ddf53c5ed98932 | 4 |
30075445 | 2011-03-10T00:00:00.000Z | B | 14 | 568c37b748ddf53c5ed98932 | 5 |
JSON Functions¶
The connector can return JSON structures as column values. The connector enables you to use standard SQL functions to work with these JSON structures. The examples in this section use the following array:
[
{ "grade": "A", "score": 2 },
{ "grade": "A", "score": 6 },
{ "grade": "A", "score": 10 },
{ "grade": "A", "score": 9 },
{ "grade": "B", "score": 14 }
]
JSON_EXTRACT¶
The JSON_EXTRACT function can extract individual values from a JSON object. The following query returns the values shown below based on the JSON path passed as the second argument to the function:
SELECT Name, JSON_EXTRACT(grades,'[0].grade') AS Grade, JSON_EXTRACT(grades,'[0].score') AS Score FROM Students;
Column Name | Example Value |
---|---|
Grade | A |
Score | 2 |
JSON_COUNT¶
The JSON_COUNT function returns the number of elements in a JSON array within a JSON object. The following query returns the number of elements specified by the JSON path passed as the second argument to the function:
SELECT Name, JSON_COUNT(grades,'[x]') AS NumberOfGrades FROM Students;
Column Name | Example Value |
---|---|
NumberOfGrades | 5 |
JSON_SUM¶
The JSON_SUM function returns the sum of the numeric values of a JSON array within a JSON object. The following query returns the total of the values specified by the JSON path passed as the second argument to the function:
SELECT Name, JSON_SUM(score,'[x].score') AS TotalScore FROM Students;
Column Name | Example Value |
---|---|
TotalScore | 41 |
JSON_MIN¶
The JSON_MIN function returns the lowest numeric value of a JSON array within a JSON object. The following query returns the minimum value specified by the JSON path passed as the second argument to the function:
SELECT Name, JSON_MIN(score,'[x].score') AS LowestScore FROM Students;
Column Name | Example Value |
---|---|
LowestScore | 2 |
JSON_MAX¶
The JSON_MAX function returns the highest numeric value of a JSON array within a JSON object. The following query returns the maximum value specified by the JSON path passed as the second argument to the function:
SELECT Name, JSON_MAX(score,'[x].score') AS HighestScore FROM Students;
Column Name | Example Value |
---|---|
HighestScore | 14 |
DOCUMENT¶
The DOCUMENT function can be used to retrieve the entire document as a JSON string. See the following query and its result as an example:
SELECT DOCUMENT(*) FROM Customers;
The query above will return the entire document as shown.
{ "id": 12, "name": "Lohia Manufacturers Inc.", "address": { "street": "Main Street", "city": "Chapel Hill", "state": "NC"}, "offices": [ "Chapel Hill", "London", "New York" ], "annual_revenue": 35,600,000 }
SQL API Built-In Functions¶
Cosmos DB also supports a number of built-in functions for common operations, that can be used inside queries. Here are some example of how can be used as part of select columns or the WHERE clause:
Use Built-in functions as part of SELECT columns
SELECT IS_NUMBER(user_id) AS ISN_ATTR, IS_NUMBER(id) AS ISN_ID FROM [users]
SELECT POWER(user_id, 2) AS POWERSSS, LENGTH(id) AS LENGTH_ID, PI() AS JustThePI FROM [users]
Use Built-in functions as part of WHERE clause
SELECT * FROM [users] WHERE STARTSWITH(middle_name, 'G')
SELECT * FROM [users] WHERE REPLACE(middle_name, 'Chr', '___') = '___istopher'
Function group | Operations |
---|---|
Mathematical functions | ABS, CEILING, EXP, FLOOR, LOG, LOG10, POWER, ROUND, SIGN, SQRT, SQUARE, TRUNC, ACOS, ASIN, ATAN, ATN2, COS, COT, DEGREES, PI, RADIANS, SIN, and TAN |
Type checking functions | IS_ARRAY, IS_BOOL, IS_NULL, IS_NUMBER, IS_OBJECT, IS_STRING, IS_DEFINED, and IS_PRIMITIVE |
String functions | ARRAY, CONCAT, CONTAINS, ENDSWITH, INDEX_OF, LEFT, LENGTH, LOWER, LTRIM, REPLACE, REPLICATE, REVERSE, RIGHT, RTRIM, STARTSWITH, SUBSTRING, and UPPER |
Array functions | ARRAY_CONCAT, ARRAY_CONTAINS, ARRAY_LENGTH, and ARRAY_SLICE |
Mathematical functions¶
The mathematical functions each perform a calculation, based on input values that are provided as arguments, and return a numeric value. Here's a table of supported built-in mathematical functions.
Usage | Description |
---|---|
ABS (num_expr) | Returns the absolute (positive) value of the specified numeric expression. |
CEILING (num_expr) | Returns the smallest integer value greater than, or equal to, the specified numeric expression. |
FLOOR (num_expr) | Returns the largest integer less than or equal to the specified numeric expression. |
EXP (num_expr) | Returns the exponent of the specified numeric expression. |
LOG (num_expr \[, base\]) | Returns the natural logarithm of the specified numeric expression, or the logarithm using the specified base |
LOG10 (num_expr) | Returns the base-10 logarithmic value of the specified numeric expression. |
ROUND (num_expr) | Returns a numeric value, rounded to the closest integer value. |
TRUNC (num_expr) | Returns a numeric value, truncated to the closest integer value. |
SQRT (num_expr) | Returns the square root of the specified numeric expression. |
SQUARE (num_expr) | Returns the square of the specified numeric expression. |
POWER (num_expr, num_expr) | Returns the power of the specified numeric expression to the value specified. |
SIGN (num_expr) | Returns the sign value (-1, 0, 1) of the specified numeric expression. |
ACOS (num_expr) | Returns the angle, in radians, whose cosine is the specified numeric expression; also called arccosine. |
ASIN (num_expr) | Returns the angle, in radians, whose sine is the specified numeric expression. This is also called arcsine. |
ATAN (num_expr) | Returns the angle, in radians, whose tangent is the specified numeric expression. This is also called arctangent. |
ATN2 (num_expr) | Returns the angle, in radians, between the positive x-axis and the ray from the origin to the point (y, x), where x and y are the values of the two specified float expressions. |
COS (num_expr) | Returns the trigonometric cosine of the specified angle, in radians, in the specified expression. |
COT (num_expr) | Returns the trigonometric cotangent of the specified angle, in radians, in the specified numeric expression. |
DEGREES (num_expr) | Returns the corresponding angle in degrees for an angle specified in radians. |
PI () | Returns the constant value of PI. |
RADIANS (num_expr) | Returns radians when a numeric expression, in degrees, is entered. |
SIN (num_expr) | Returns the trigonometric sine of the specified angle, in radians, in the specified expression. |
TAN (num_expr) | Returns the tangent of the input expression, in the specified expression. |
Type checking functions¶
The type checking functions allow you to check the type of an expression within SQL queries. Type checking functions can be used to determine the type of properties within documents dynamically when it is variable or unknown. Here's a table of supported built-in type checking functions.
Usage | Description |
---|---|
IS_ARRAY (expr) | Returns a Boolean indicating if the type of the value is an array. |
IS_BOOL (expr) | Returns a Boolean indicating if the type of the value is a Boolean. |
IS_NULL (expr) | Returns a Boolean indicating if the type of the value is null. |
IS_NUMBER (expr) | Returns a Boolean indicating if the type of the value is a number. |
IS_OBJECT (expr) | Returns a Boolean indicating if the type of the value is a JSON object. |
IS_STRING (expr) | Returns a Boolean indicating if the type of the value is a string. |
IS_DEFINED (expr) | Returns a Boolean indicating if the property has been assigned a value. |
IS_PRIMITIVE (expr) | Returns a Boolean indicating if the type of the value is a string, number, Boolean or null. |
Stre functions¶
The following scalar functions perform an operation on a string input value and return a string, numeric or Boolean value. Here's a table of built-in string functions:
Usage | Description |
---|---|
ARRAY (str_expr) | Project the results of the specified query as an array. |
LENGTH (str_expr) | Returns the number of characters of the specified string expression |
CONCAT (str_expr, str_expr \[, str_expr\]) | Returns a string that is the result of concatenating two or more string values. |
SUBSTRING (str_expr, num_expr, num_expr) | Returns part of a string expression. |
STARTSWITH (str_expr, str_expr) | Returns a Boolean indicating whether the first string expression starts with the second |
ENDSWITH (str_expr, str_expr) | Returns a Boolean indicating whether the first string expression ends with the second |
CONTAINS (str_expr, str_expr) | Returns a Boolean indicating whether the first string expression contains the second. |
INDEX_OF (str_expr, str_expr) | Returns the starting position of the first occurrence of the second string expression within the first specified string expression, or -1 if the string is not found. |
LEFT (str_expr, num_expr) | Returns the left part of a string with the specified number of characters. |
RIGHT (str_expr, num_expr) | Returns the right part of a string with the specified number of characters. |
LTRIM (str_expr) | Returns a string expression after it removes leading blanks. |
RTRIM (str_expr) | Returns a string expression after truncating all trailing blanks. |
LOWER (str_expr) | Returns a string expression after converting uppercase character data to lowercase. |
UPPER (str_expr) | Returns a string expression after converting lowercase character data to uppercase. |
REPLACE (str_expr, str_expr, str_expr) | Replaces all occurrences of a specified string value with another string value. |
REPLICATE (str_expr, num_expr) | Repeats a string value a specified number of times. |
REVERSE (str_expr) | Returns the reverse order of a string value. |
Array functions¶
The following scalar functions perform an operation on an array input value and return numeric, Boolean or array value. Here's a table of built-in array functions:
Usage | Description |
---|---|
ARRAY_LENGTH (arr_expr) | Returns the number of elements of the specified array expression. |
ARRAY_CONCAT (arr_expr, arr_expr \[, arr_expr\]) | Returns an array that is the result of concatenating two or more array values. |
ARRAY_CONTAINS (arr_expr, expr \[, bool_expr\]) | Returns a Boolean indicating whether the array contains the specified value. Can specify if the match is full or partial. |
ARRAY_SLICE (arr_expr, num_expr \[, num_expr\]) | Returns part of an array expression. |
Nested functions¶
You can also perform nested built-in functions, wich will be processed server side as well:
i.e. SELECT TOP 10 CONCAT(SUBSTRING(UPPER(cuisine), 0, 3), '-cuisine') FROM [restaurants]
SQL API GROUP BY¶
The GROUP BY clause divides the query's results according to the values of one or more specified properties. This operation is partially done server-side because of some API limitations. We still need to operate a client-side grouping.
GROUP BY Examples¶
SELECT COUNT(*) AS CNT, gender FROM [users] GROUP BY gender
SELECT COUNT(*) AS CNT, gender, doc_type FROM [users] GROUP BY gender, doc_type
Query Mapping (Sql API)¶
The connector maps SQL queries into the corresponding Azure Cosmos DB SQL API queries. A detailed description of all the transformations is out of scope, but we will describe some of the common elements that are used. The connector takes advantage of SQL API features such as the aggregation framework to compute the desired results.
SELECT Queries¶
Since all requests can be submitted to a specific collection, we can send any constant string as table name to the API. Following the Azure Portal standard we are using the "C" character as table name.
SQL Query | Sql API Query |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
Aggregate Queries¶
The connector makes extensive use of this for various aggregate queries. See some examples below:
SQL Query | Sql API Query |
---|---|
|
|
|
|
Built-In functions¶
SQL Query | Sql API Query |
---|---|
|
|
|
|
Custom Schema Definitions¶
You can extend the table schemas created with Automatic Schema Discovery by saving them into schema files. The schema files have a simple format that makes the schemas to edit.
Generate Schema Files¶
Set GenerateSchemaFiles
to "OnStart" to persist schemas for all tables when you connect. You can also generate table schemas as needed: Set GenerateSchemaFiles
to "OnUse" and execute a SELECT query to the table.
For example, consider a schema for the restaurants data set. This is a sample data set provided by Azure Cosmos DB.
Below is an example document from the collection:
{
"address":{
"building":"461",
"coord":[
-74.138492,
40.631136
],
"street":"Port Richmond Ave",
"zipcode":"10302"
},
"borough":"Staten Island",
"cuisine":"Other",
"name":"Indian Oven",
"restaurant_id":"50018994"
}
Customize a Schema¶
When GenerateSchemaFiles
is set, the connector saves schemas into the folder specified by the Location
property. You can then change column behavior in the resulting schema.
The following schema uses the other:bsonpath
property to define where in the collection to retrieve the data for a particular column. Using this model you can flatten arbitrary levels of hierarchy.
Below are the corresponding column definitions for the restaurants data set. In Custom Schema Example, you will find the complete schema.
<rsb:script xmlns:rsb="http://www.rssbus.com/ns/rsbscript/2">
<rsb:info title="StaticRestaurants" description="Custom Schema for the restaurants data set.">
<!-- Column definitions -->
<attr name="_rid" xs:type="string" key="true" other:collrid="hWdRAKRi3Pg=" other:dbrid="hWdRAA==" other:partitionpath="/name" />
<attr name="borough" xs:type="string" />
<attr name="cuisine" xs:type="string" />
<attr name="address.building" xs:type="string" />
<attr name="address.street" xs:type="string" />
<attr name="address.coord.0" xs:type="double" />
<attr name="address.coord.1" xs:type="double" />
<input name="rows@next" desc="Internal attribute used for paging through data." />
</rsb:info>
<rsb:set attr="collection" value="restaurants"/>
</rsb:script>
Custom Schema Example¶
This section contains a complete schema. The info
section enables a relational view of a Azure Cosmos DB object. For more details, see Custom Schema Definitions. The table below allows the SELECT, INSERT, UPDATE, and DELETE commands as implemented in the GET, POST, MERGE, and DELETE sections of the schema below.
Copy the rows@next
input as-is into your schema. The operations, such as cosmosdbadoSysData
, are internal implementations and can also be copied as is.
Set the Location
property to the file directory that will contain the schema file.
When, creating custom schemas, the attr for _rid
, shown below, is required.
Also required are three properties for the _rid column definition:
other:dbrid
is found in the _self property of an item in the collection, after "dbs/".other:collrid
is found in the _self property of an item in the collection, after "/colls/".other:partitionpath
refers to the name of the partition specified when the collection was created.
<rsb:script xmlns:rsb="http://www.rssbus.com/ns/rsbscript/2">
<rsb:info title="StaticRestaurants" description="Custom Schema for the restaurants data set.">
<!-- Column definitions -->
<attr name="_rid" xs:type="string" key="true" other:collrid="hWdRAKRi3Pg=" other:dbrid="hWdRAA==" other:partitionpath="/name" />
<attr name="borough" xs:type="string" />
<attr name="cuisine" xs:type="string" />
<attr name="address.building" xs:type="string" />
<attr name="address.street" xs:type="string" />
<attr name="address.coord.0" xs:type="double" />
<attr name="address.coord.1" xs:type="double" />
<input name="rows@next" desc="Internal attribute used for paging through data." />
</rsb:info>
<rsb:script method="GET">
<rsb:call op="cosmosdbadoSysData">
<rsb:push />
</rsb:call>
</rsb:script>
<rsb:script method="POST">
<rsb:call op="cosmosdbadoSysData">
<rsb:push />
</rsb:call>
</rsb:script>
<rsb:script method="MERGE">
<rsb:call op="cosmosdbadoSysData">
<rsb:push />
</rsb:call>
</rsb:script>
<rsb:script method="DELETE">
<rsb:call op="cosmosdbadoSysData">
<rsb:push />
</rsb:call>
</rsb:script>
</rsb:script>
System Tables¶
You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.
Schema Tables¶
The following tables return database metadata for Azure Cosmos DB:
- sys_catalogs: Lists the available databases.
- sys_schemas: Lists the available schemas.
- sys_tables: Lists the available tables and views.
- sys_tablecolumns: Describes the columns of the available tables and views.
- sys_procedures: Describes the available stored procedures.
- sys_procedureparameters: Describes stored procedure parameters.
- sys_keycolumns: Describes the primary and foreign keys.
- sys_indexes: Describes the available indexes.
Data Source Tables¶
The following tables return information about how to connect to and query the data source:
- sys_connection_props: Returns information on the available connection properties.
- sys_sqlinfo: Describes the SELECT queries that the connector can offload to the data source.
Query Information Tables¶
The following table returns query statistics for data modification queries, including batch operations:
- sys_identity: Returns information about batch operations or single updates.
sys_catalogs¶
Lists the available databases.
The following query retrieves all databases determined by the connection string:
SELECT * FROM sys_catalogs
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The database name. |
sys_schemas¶
Lists the available schemas.
The following query retrieves all available schemas:
SELECT * FROM sys_schemas
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The database name. |
SchemaName | String | The schema name. |
sys_tables¶
Lists the available tables.
The following query retrieves the available tables and views:
SELECT * FROM sys_tables
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The database containing the table or view. |
SchemaName | String | The schema containing the table or view. |
TableName | String | The name of the table or view. |
TableType | String | The table type (table or view). |
Description | String | A description of the table or view. |
IsUpdateable | Boolean | Whether the table can be updated. |
sys_tablecolumns¶
Describes the columns of the available tables and views.
The following query returns the columns and data types for the [].[Entities].Customers table:
SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Customers' AND CatalogName='' AND SchemaName='Entities'
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the table or view. |
SchemaName | String | The schema containing the table or view. |
TableName | String | The name of the table or view containing the column. |
ColumnName | String | The column name. |
DataTypeName | String | The data type name. |
DataType | Int32 | An integer indicating the data type. This value is determined at run time based on the environment. |
Length | Int32 | The storage size of the column. |
DisplaySize | Int32 | The designated column's normal maximum width in characters. |
NumericPrecision | Int32 | The maximum number of digits in numeric data. The column length in characters for character and date-time data. |
NumericScale | Int32 | The column scale or number of digits to the right of the decimal point. |
IsNullable | Boolean | Whether the column can contain null. |
Description | String | A brief description of the column. |
Ordinal | Int32 | The sequence number of the column. |
IsAutoIncrement | String | Whether the column value is assigned in fixed increments. |
IsGeneratedColumn | String | Whether the column is generated. |
IsHidden | Boolean | Whether the column is hidden. |
IsArray | Boolean | Whether the column is an array. |
IsReadOnly | Boolean | Whether the column is read-only. |
IsKey | Boolean | Indicates whether a field returned from sys_tablecolumns is the primary key of the table. |
sys_procedures¶
Lists the available stored procedures.
The following query retrieves the available stored procedures:
SELECT * FROM sys_procedures
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The database containing the stored procedure. |
SchemaName | String | The schema containing the stored procedure. |
ProcedureName | String | The name of the stored procedure. |
Description | String | A description of the stored procedure. |
ProcedureType | String | The type of the procedure, such as PROCEDURE or FUNCTION. |
sys_procedureparameters¶
Describes stored procedure parameters.
The following query returns information about all of the input parameters for the EVAL stored procedure:
SELECT * FROM sys_procedureparameters WHERE ProcedureName='EVAL' AND Direction=1 OR Direction=2
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the stored procedure. |
SchemaName | String | The name of the schema containing the stored procedure. |
ProcedureName | String | The name of the stored procedure containing the parameter. |
ColumnName | String | The name of the stored procedure parameter. |
Direction | Int32 | An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters. |
DataTypeName | String | The name of the data type. |
DataType | Int32 | An integer indicating the data type. This value is determined at run time based on the environment. |
Length | Int32 | The number of characters allowed for character data. The number of digits allowed for numeric data. |
NumericPrecision | Int32 | The maximum precision for numeric data. The column length in characters for character and date-time data. |
NumericScale | Int32 | The number of digits to the right of the decimal point in numeric data. |
IsNullable | Boolean | Whether the parameter can contain null. |
IsRequired | Boolean | Whether the parameter is required for execution of the procedure. |
IsArray | Boolean | Whether the parameter is an array. |
Description | String | The description of the parameter. |
Ordinal | Int32 | The index of the parameter. |
sys_keycolumns¶
Describes the primary and foreign keys.
The following query retrieves the primary key for the [].[Entities].Customers table:
SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Customers' AND CatalogName='' AND SchemaName='Entities'
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
IsKey | Boolean | Whether the column is a primary key in the table referenced in the TableName field. |
IsForeignKey | Boolean | Whether the column is a foreign key referenced in the TableName field. |
PrimaryKeyName | String | The name of the primary key. |
ForeignKeyName | String | The name of the foreign key. |
ReferencedCatalogName | String | The database containing the primary key. |
ReferencedSchemaName | String | The schema containing the primary key. |
ReferencedTableName | String | The table containing the primary key. |
ReferencedColumnName | String | The column name of the primary key. |
sys_foreignkeys¶
Describes the foreign keys.
The following query retrieves all foreign keys which refer to other tables:
SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
PrimaryKeyName | String | The name of the primary key. |
ForeignKeyName | String | The name of the foreign key. |
ReferencedCatalogName | String | The database containing the primary key. |
ReferencedSchemaName | String | The schema containing the primary key. |
ReferencedTableName | String | The table containing the primary key. |
ReferencedColumnName | String | The column name of the primary key. |
ForeignKeyType | String | Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key. |
sys_primarykeys¶
Describes the primary keys.
The following query retrieves the primary keys from all tables and views:
SELECT * FROM sys_primarykeys
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
KeySeq | String | The sequence number of the primary key. |
KeyName | String | The name of the primary key. |
sys_indexes¶
Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.
The following query retrieves all indexes that are not primary keys:
SELECT * FROM sys_indexes WHERE IsPrimary='false'
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the index. |
SchemaName | String | The name of the schema containing the index. |
TableName | String | The name of the table containing the index. |
IndexName | String | The index name. |
ColumnName | String | The name of the column associated with the index. |
IsUnique | Boolean | True if the index is unique. False otherwise. |
IsPrimary | Boolean | True if the index is a primary key. False otherwise. |
Type | Int16 | An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3). |
SortOrder | String | The sort order: A for ascending or D for descending. |
OrdinalPosition | Int16 | The sequence number of the column in the index. |
sys_connection_props¶
Returns information on the available connection properties and those set in the connection string.
When querying this table, the config connection string should be used:
jdbc:cdata:cosmosdb:config:
This connection string enables you to query this table without a valid connection.
The following query retrieves all connection properties that have been set in the connection string or set through a default value:
SELECT * FROM sys_connection_props WHERE Value <> ''
Columns¶
Name | Type | Description |
---|---|---|
Name | String | The name of the connection property. |
ShortDescription | String | A brief description. |
Type | String | The data type of the connection property. |
Default | String | The default value if one is not explicitly set. |
Values | String | A comma-separated list of possible values. A validation error is thrown if another value is specified. |
Value | String | The value you set or a preconfigured default. |
Required | Boolean | Whether the property is required to connect. |
Category | String | The category of the connection property. |
IsSessionProperty | String | Whether the property is a session property, used to save information about the current connection. |
Sensitivity | String | The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms. |
PropertyName | String | A camel-cased truncated form of the connection property name. |
Ordinal | Int32 | The index of the parameter. |
CatOrdinal | Int32 | The index of the parameter category. |
Hierarchy | String | Shows dependent properties associated that need to be set alongside this one. |
Visible | Boolean | Informs whether the property is visible in the connection UI. |
ETC | String | Various miscellaneous information about the property. |
sys_sqlinfo¶
Describes the SELECT query processing that the connector can offload to the data source.
Discover the Data Source's SELECT Capabilities¶
Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.
Name | Description | Possible Values |
---|---|---|
AGGREGATE_FUNCTIONS | Supported aggregation functions. | AVG , COUNT , MAX , MIN , SUM , DISTINCT |
COUNT | Whether COUNT function is supported. | YES , NO |
IDENTIFIER_QUOTE_OPEN_CHAR | The opening character used to escape an identifier. | [ |
IDENTIFIER_QUOTE_CLOSE_CHAR | The closing character used to escape an identifier. | ] |
SUPPORTED_OPERATORS | A list of supported SQL operators. | = , > , < , >= , <= , <> , != , LIKE , NOT LIKE , IN , NOT IN , IS NULL , IS NOT NULL , AND , OR |
GROUP_BY | Whether GROUP BY is supported, and, if so, the degree of support. | NO , NO_RELATION , EQUALS_SELECT , SQL_GB_COLLATE |
STRING_FUNCTIONS | Supported string functions. | LENGTH , CHAR , LOCATE , REPLACE , SUBSTRING , RTRIM , LTRIM , RIGHT , LEFT , UCASE , SPACE , SOUNDEX , LCASE , CONCAT , ASCII , REPEAT , OCTET , BIT , POSITION , INSERT , TRIM , UPPER , REGEXP , LOWER , DIFFERENCE , CHARACTER , SUBSTR , STR , REVERSE , PLAN , UUIDTOSTR , TRANSLATE , TRAILING , TO , STUFF , STRTOUUID , STRING , SPLIT , SORTKEY , SIMILAR , REPLICATE , PATINDEX , LPAD , LEN , LEADING , KEY , INSTR , INSERTSTR , HTML , GRAPHICAL , CONVERT , COLLATION , CHARINDEX , BYTE |
NUMERIC_FUNCTIONS | Supported numeric functions. | ABS , ACOS , ASIN , ATAN , ATAN2 , CEILING , COS , COT , EXP , FLOOR , LOG , MOD , SIGN , SIN , SQRT , TAN , PI , RAND , DEGREES , LOG10 , POWER , RADIANS , ROUND , TRUNCATE |
TIMEDATE_FUNCTIONS | Supported date/time functions. | NOW , CURDATE , DAYOFMONTH , DAYOFWEEK , DAYOFYEAR , MONTH , QUARTER , WEEK , YEAR , CURTIME , HOUR , MINUTE , SECOND , TIMESTAMPADD , TIMESTAMPDIFF , DAYNAME , MONTHNAME , CURRENT_DATE , CURRENT_TIME , CURRENT_TIMESTAMP , EXTRACT |
REPLICATION_SKIP_TABLES | Indicates tables skipped during replication. | |
REPLICATION_TIMECHECK_COLUMNS | A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication. | |
IDENTIFIER_PATTERN | String value indicating what string is valid for an identifier. | |
SUPPORT_TRANSACTION | Indicates if the provider supports transactions such as commit and rollback. | YES , NO |
DIALECT | Indicates the SQL dialect to use. | |
KEY_PROPERTIES | Indicates the properties which identify the uniform database. | |
SUPPORTS_MULTIPLE_SCHEMAS | Indicates if multiple schemas may exist for the provider. | YES , NO |
SUPPORTS_MULTIPLE_CATALOGS | Indicates if multiple catalogs may exist for the provider. | YES , NO |
DATASYNCVERSION | The Data Sync version needed to access this driver. | Standard , Starter , Professional , Enterprise |
DATASYNCCATEGORY | The Data Sync category of this driver. | Source , Destination , Cloud Destination |
SUPPORTSENHANCEDSQL | Whether enhanced SQL functionality beyond what is offered by the API is supported. | TRUE , FALSE |
SUPPORTS_BATCH_OPERATIONS | Whether batch operations are supported. | YES , NO |
SQL_CAP | All supported SQL capabilities for this driver. | SELECT , INSERT , DELETE , UPDATE , TRANSACTIONS , ORDERBY , OAUTH , ASSIGNEDID , LIMIT , LIKE , BULKINSERT , COUNT , BULKDELETE , BULKUPDATE , GROUPBY , HAVING , AGGS , OFFSET , REPLICATE , COUNTDISTINCT , JOINS , DROP , CREATE , DISTINCT , INNERJOINS , SUBQUERIES , ALTER , MULTIPLESCHEMAS , GROUPBYNORELATION , OUTERJOINS , UNIONALL , UNION , UPSERT , GETDELETED , CROSSJOINS , GROUPBYCOLLATE , MULTIPLECATS , FULLOUTERJOIN , MERGE , JSONEXTRACT , BULKUPSERT , SUM , SUBQUERIESFULL , MIN , MAX , JOINSFULL , XMLEXTRACT , AVG , MULTISTATEMENTS , FOREIGNKEYS , CASE , LEFTJOINS , COMMAJOINS , WITH , LITERALS , RENAME , NESTEDTABLES , EXECUTE , BATCH , BASIC , INDEX |
PREFERRED_CACHE_OPTIONS | A string value specifies the preferred cacheOptions. | |
ENABLE_EF_ADVANCED_QUERY | Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side. | YES , NO |
PSEUDO_COLUMNS | A string array indicating the available pseudo columns. | |
MERGE_ALWAYS | If the value is true, The Merge Mode is forcibly executed in Data Sync. | TRUE , FALSE |
REPLICATION_MIN_DATE_QUERY | A select query to return the replicate start datetime. | |
REPLICATION_MIN_FUNCTION | Allows a provider to specify the formula name to use for executing a server side min. | |
REPLICATION_START_DATE | Allows a provider to specify a replicate startdate. | |
REPLICATION_MAX_DATE_QUERY | A select query to return the replicate end datetime. | |
REPLICATION_MAX_FUNCTION | Allows a provider to specify the formula name to use for executing a server side max. | |
IGNORE_INTERVALS_ON_INITIAL_REPLICATE | A list of tables which will skip dividing the replicate into chunks on the initial replicate. | |
CHECKCACHE_USE_PARENTID | Indicates whether the CheckCache statement should be done against the parent key column. | TRUE , FALSE |
CREATE_SCHEMA_PROCEDURES | Indicates stored procedures that can be used for generating schema files. |
The following query retrieves the operators that can be used in the WHERE clause:
SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'
Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the NoSQL Database section for more information.
Columns¶
Name | Type | Description |
---|---|---|
NAME | String | A component of SQL syntax, or a capability that can be processed on the server. |
VALUE | String | Detail on the supported SQL or SQL syntax. |
sys_identity¶
Returns information about attempted modifications.
The following query retrieves the Ids of the modified rows in a batch operation:
SELECT * FROM sys_identity
Columns¶
Name | Type | Description |
---|---|---|
Id | String | The database-generated ID returned from a data modification operation. |
Batch | String | An identifier for the batch. 1 for a single operation. |
Operation | String | The result of the operation in the batch: INSERTED, UPDATED, or DELETED. |
Message | String | SUCCESS or an error message if the update in the batch failed. |
sys_information¶
Describes the available system information.
The following query retrieves all columns:
SELECT * FROM sys_information
Columns¶
Name | Type | Description |
---|---|---|
Product | String | The name of the product. |
Version | String | The version number of the product. |
Datasource | String | The name of the datasource the product connects to. |
NodeId | String | The unique identifier of the machine where the product is installed. |
HelpURL | String | The URL to the product's help documentation. |
License | String | The license information for the product. (If this information is not available, the field may be left blank or marked as 'N/A'.) |
Location | String | The file path location where the product's library is stored. |
Environment | String | The version of the environment or rumtine the product is currently running under. |
DataSyncVersion | String | The tier of Sync required to use this connector. |
DataSyncCategory | String | The category of Sync functionality (e.g., Source, Destination). |
Stored Procedures¶
Stored procedures are function-like interfaces that extend the functionality of the connector beyond simple SELECT/INSERT/UPDATE/DELETE operations with Azure Cosmos DB.
Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Azure Cosmos DB, along with an indication of whether the procedure succeeded or failed.
Azure Cosmos DB Connector Stored Procedures¶
Name | Description |
---|---|
AddDocument | Insert entire JSON string to CosmosDB. |
CreateSchema | Creates a schema file for the collection. |
GetOAuthAccessToken | Gets the OAuth access token from CosmosDB. |
GetOAuthAuthorizationURL | Gets the CosmosDB authorization URL. Access the URL returned in the output in a Web browser. This requests the access token that can be used as part of the connection string to CosmosDB. |
RefreshOAuthAccessToken | Refreshes the OAuth access token used for authentication with CosmosDB. |
AddDocument¶
Insert entire JSON string to CosmosDB.
Input¶
Name | Type | Description |
---|---|---|
Database | String | Name of the database. |
Table | String | Name of the table. |
PartitionKey | String | Partition key value of the table. |
Document | String | The JSON string to be inserted. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Success | String | Returns true if the operation is successful. |
CreateSchema¶
Creates a schema file for the collection.
CreateSchema¶
Creates a local schema file (.rsd) from an existing table or view in the data model.
The schema file is created in the directory set in the Location
connection property when this procedure is executed. You can edit the file to include or exclude columns, rename columns, or adjust column datatypes.
The connector checks the Location
to determine if the names of any .rsd files match a table or view in the data model. If there is a duplicate, the schema file will take precedence over the default instance of this table in the data model. If a schema file is present in Location
that does not match an existing table or view, a new table or view entry is added to the data model of the connector.
Input¶
Name | Type | Description |
---|---|---|
SchemaName | String | The schema of the collection. |
TableName | String | The name of the collection. |
FileName | String | The full file path and name of the schema to generate. If not set, the FileData output is used instead. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Result | String | Returns Success or Failure. |
FileData | String | The generated schema encoded in Base64. Only returned if FileName is not set. |
GetOAuthAccessToken¶
Gets the OAuth access token from CosmosDB.
Input¶
Name | Type | Description |
---|---|---|
AuthMode | String | The type of authentication you are attempting. Use App for a Windows application, or Web for Web-based applications. The default value is APP. |
Verifier | String | A verifier returned by the service that must be input to return the access token. Needed only when using the Web auth mode. Obtained by navigating to the URL returned in GetOAuthAuthorizationUrl. |
CallbackUrl | String | The URL the user will be redirected to after authorizing your application. |
Scope | String | The scope or permissions you are requesting. |
Prompt | String | Defaults to 'select_account' which prompts the user to select account while authenticating. Set to 'None', for no prompt, 'login' to force user to enter their credentials or 'consent' to trigger the OAuth consent dialog after the user signs in, asking the user to grant permissions to the app. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
OAuthAccessToken | String | The access token used for communication with CosmosDB. |
OAuthRefreshToken | String | A token that may be used to obtain a new access token. |
ExpiresIn | String | The remaining lifetime for the access token in seconds. |
GetOAuthAuthorizationURL¶
Gets the CosmosDB authorization URL. Access the URL returned in the output in a Web browser. This requests the access token that can be used as part of the connection string to CosmosDB.
Input¶
Name | Type | Description |
---|---|---|
CallbackUrl | String | The URL that CosmosDB will return to after the user has authorized your app. |
Scope | String | The scope or permissions you are requesting. |
State | String | This field indicates any state that may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to CosmosDB authorization server and back. Uses include redirecting the user to the correct resource in your site, using nonces, and mitigating cross-site request forgery. |
Prompt | String | Defaults to 'select_account' which prompts the user to select account while authenticating. Set to 'None', for no prompt, 'login' to force user to enter their credentials or 'consent' to trigger the OAuth consent dialog after the user signs in, asking the user to grant permissions to the app. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
URL | String | The URL to be entered into a Web browser to obtain the verifier token and authorize the data provider with. |
RefreshOAuthAccessToken¶
Refreshes the OAuth access token used for authentication with CosmosDB.
Input¶
Name | Type | Description |
---|---|---|
OAuthRefreshToken | String | The refresh token returned from the original authorization code exchange. |
Scope | String | The scope or permissions you are requesting. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
OAuthAccessToken | String | The new OAuthAccessToken returned from the service. |
OAuthRefreshToken | String | A token that may be used to obtain a new access token. |
ExpiresIn | String | The remaining lifetime on the access token. |
Advanced Configurations Properties¶
The advanced configurations properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure. Click the links for further details.
Property | Description |
---|---|
AuthScheme | The type of authentication to use when connecting to Azure Cosmos DB. |
AccountEndpoint | The value should be the Cosmos DB account URL from the Keys blade of the Cosmos DB account. |
AccountKey | A master key token or a resource token for connecting to the Azure Cosmos DB REST API. |
TokenType | Denotes the type of token: master or resource. |
Property | Description |
---|---|
AzureTenant | The Microsoft Online tenant being used to access data. If not specified, your default tenant is used. |
AzureEnvironment | The Azure Environment to use when establishing a connection. |
Property | Description |
---|---|
InitiateOAuth | Set this property to initiate the process to obtain or refresh the OAuth access token when you connect. |
OAuthClientId | The client ID assigned when you register your application with an OAuth authorization server. |
OAuthClientSecret | The client secret assigned when you register your application with an OAuth authorization server. |
OAuthAccessToken | The access token for connecting using OAuth. |
OAuthSettingsLocation | The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH . Alternatively, you can hold this location in memory by specifying a value starting with 'memory://' . |
CallbackURL | The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings. |
OAuthGrantType | The grant type for the OAuth flow. |
OAuthVerifier | The verifier code returned from the OAuth authorization URL. |
OAuthRefreshToken | The OAuth refresh token for the corresponding OAuth access token. |
OAuthExpiresIn | The lifetime in seconds of the OAuth AccessToken. |
OAuthTokenTimestamp | The Unix epoch timestamp in milliseconds when the current Access Token was created. |
Property | Description |
---|---|
OAuthJWTCert | The JWT Certificate store. |
OAuthJWTCertType | The type of key store containing the JWT Certificate. |
OAuthJWTCertPassword | The password for the OAuth JWT certificate. |
OAuthJWTCertSubject | The subject of the OAuth JWT certificate. |
Property | Description |
---|---|
SSLClientCert | The TLS/SSL client certificate store for SSL Client Authentication (2-way SSL). |
SSLClientCertType | The type of key store containing the TLS/SSL client certificate. |
SSLClientCertPassword | The password for the TLS/SSL client certificate. |
SSLClientCertSubject | The subject of the TLS/SSL client certificate. |
SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
Property | Description |
---|---|
Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC. |
Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC. |
Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC. |
Schema | Specify the Azure Cosmos DB database you want to work with. |
Property | Description |
---|---|
CalculateAggregates | Specifies whether will return the calculated value of the aggregates or grouped by partiton range. |
ConsistencyLevel | Denotes the type of token: master or resource. |
FlattenArrays | By default, nested arrays are returned as strings of JSON. The FlattenArrays property can be used to flatten the elements of nested arrays into columns of their own. Set FlattenArrays to the number of elements you want to return from nested arrays. |
FlattenObjects | Set FlattenObjects to true to flatten object properties into columns of their own. Otherwise, objects nested in arrays are returned as strings of JSON. |
ForceQueryOnNonIndexedContainers | Force the use of an index scan to process the query if indexing is disabled or the right index path is not available. |
GenerateSchemaFiles | Indicates the user preference as to when schemas should be generated and saved. |
MaxRows | Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses. |
MaxThreads | Specifies the maximum number of concurrent requests for Batch CUD (Create, Update, Delete) operations. |
MultiThreadCount | Aggregate queries in partitioned collections will require parallel requests for different partition ranges. Set this to the number of parallel request to be issued in the same time. |
Other | These hidden properties are used only in specific use cases. |
Pagesize | The maximum number of results to return per page from Azure Cosmos DB. |
PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
RequestPriorityLevel | Specifies the priority for the request sent to Azure Cosmos DB, when there are more requests than the configured RU/s in a second. |
RowScanDepth | The maximum number of rows to scan to look for the columns available in a table. |
SeparatorCharacter | The character or characters used to denote hierarchy. |
SetPartitionKeyAsPK | Whether or not to use the collection's Partition Key field as part of composite Primary Key for the corresponding exposed table. |
Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
TypeDetectionScheme | Comma-separated options for how the provider will scan the data to determine the fields and datatypes in each document collection. |
UserDefinedViews | A filepath pointing to the JSON configuration file containing your custom views. |
UseRidAsPk | Set this property to false to switch using the ID column as primary key instead the default _rid. |
WriteThroughputBudget | Defines the Requests Units (RU) budget per Second that the Batch CUD (Create, Update, Delete) operations should not exceed. |
Authentication¶
This section provides a complete list of authentication properties you can configure.
Property | Description |
---|---|
AuthScheme | The type of authentication to use when connecting to Azure Cosmos DB. |
AccountEndpoint | The value should be the Cosmos DB account URL from the Keys blade of the Cosmos DB account. |
AccountKey | A master key token or a resource token for connecting to the Azure Cosmos DB REST API. |
TokenType | Denotes the type of token: master or resource. |
AuthScheme¶
The type of authentication to use when connecting to Azure Cosmos DB.
Possible Values¶
AccountKey
, AzureAD
, AzureServicePrincipal
, AzureServicePrincipalCert
Data Type¶
string
Default Value¶
AccountKey
Remarks¶
- AccountKey: Set this to perform authentication with AccountKey and AccountEndpoint.
- AzureAD: Set this to perform Azure Active Directory OAuth authentication.
- AzureServicePrincipal: Set this to authenticate as an Azure Service Principal using a Client Secret.
- AzureServicePrincipalCert: Set this to authenticate as an Azure Service Principal using a Certificate.
AccountEndpoint¶
The value should be the Cosmos DB account URL from the Keys blade of the Cosmos DB account.
Data Type¶
string
Default Value¶
""
Remarks¶
The value should be the Cosmos DB account URL from the Keys blade of the Cosmos DB account.
AccountKey¶
A master key token or a resource token for connecting to the Azure Cosmos DB REST API.
Data Type¶
string
Default Value¶
""
Remarks¶
In the Azure portal, navigate to the Cosmos DB service and select your Azure Cosmos DB account. From the resource menu, go to the Keys page. Find the PRIMARY KEY value and set Token to this value.
TokenType¶
Denotes the type of token: master or resource.
Possible Values¶
master
, resource
Data Type¶
string
Default Value¶
master
Remarks¶
The master key is created during the creation of an account. There are two sets of master keys, the primary key and the secondary key. The administrator of the account can then exercise key rotation using the secondary key. In addition, the account administrator can also regenerate the keys as needed.
Resource tokens are created when users in a database are set up with access permissions for precise access control on a resource, also known as a permission resource. A permission resource contains a hash resource token constructed with the information regarding the resource path and access type a user has access to. The permission resource token is time bound and the validity period can be overridden. When a permission resource is acted upon on (POST, GET, PUT), a new resource token is generated.
Azure Authentication¶
This section provides a complete list of Azure authentication properties you can configure.
Property | Description |
---|---|
AzureTenant | The Microsoft Online tenant being used to access data. If not specified, your default tenant is used. |
AzureEnvironment | The Azure Environment to use when establishing a connection. |
AzureTenant¶
The Microsoft Online tenant being used to access data. If not specified, your default tenant is used.
Data Type¶
string
Default Value¶
""
Remarks¶
The Microsoft Online tenant being used to access data. For instance, contoso.onmicrosoft.com. Alternatively, specify the tenant Id. This value is the directory ID in the Azure Portal > Azure Active Directory > Properties.
Typically it is not necessary to specify the Tenant. This can be automatically determined by Microsoft when using the OAuthGrantType set to CODE (default). However, it may fail in the case that the user belongs to multiple tenants. For instance, if an Admin of domain A invites a user of domain B to be a guest user. The user will now belong to both tenants. It is a good practice to specify the Tenant, although in general things should normally work without having to specify it.
The AzureTenant
is required when setting OAuthGrantType to CLIENT. When using client credentials, there is no user context. The credentials are taken from the context of the app itself. While Microsoft still allows client credentials to be obtained without specifying which Tenant, it has a much lower probability of picking the specific tenant you want to work with. For this reason, we require AzureTenant
to be explicitly stated for all client credentials connections to ensure you get credentials that are applicable for the domain you intend to connect to.
The Microsoft Online tenant being used to access data. For instance, contoso.onmicrosoft.com. Alternatively, specify the tenant Id. This value is the directory ID in the Azure Portal > Azure Active Directory > Properties.
Typically it is not necessary to specify the Tenant. This can be automatically determined by Microsoft when using the OAuthGrantType set to CODE (default). However, it may fail in the case that the user belongs to multiple tenants. For instance, if an Admin of domain A invites a user of domain B to be a guest user. The user will now belong to both tenants. It is a good practice to specify the Tenant, although in general things should normally work without having to specify it.
The AzureTenant
is required when setting OAuthGrantType to CLIENT. When using client credentials, there is no user context. The credentials are taken from the context of the app itself. While Microsoft still allows client credentials to be obtained without specifying which Tenant, it has a much lower probability of picking the specific tenant you want to work with. For this reason, we require AzureTenant
to be explicitly stated for all client credentials connections to ensure you get credentials that are applicable for the domain you intend to connect to.
AzureEnvironment¶
The Azure Environment to use when establishing a connection.
Possible Values¶
GLOBAL
, CHINA
, USGOVT
, USGOVTDOD
Data Type¶
string
Default Value¶
GLOBAL
Remarks¶
In most cases, leaving the environment set to global will work. However, if your Azure Account has been added to a different environment, the AzureEnvironment
may be used to specify which environment. The available values are GLOBAL, CHINA, USGOVT, USGOVTDOD.
OAuth¶
This section provides a complete list of OAuth properties you can configure.
Property | Description |
---|---|
InitiateOAuth | Set this property to initiate the process to obtain or refresh the OAuth access token when you connect. |
OAuthClientId | The client ID assigned when you register your application with an OAuth authorization server. |
OAuthClientSecret | The client secret assigned when you register your application with an OAuth authorization server. |
OAuthAccessToken | The access token for connecting using OAuth. |
OAuthSettingsLocation | The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH . Alternatively, you can hold this location in memory by specifying a value starting with 'memory://' . |
CallbackURL | The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings. |
OAuthGrantType | The grant type for the OAuth flow. |
OAuthVerifier | The verifier code returned from the OAuth authorization URL. |
OAuthRefreshToken | The OAuth refresh token for the corresponding OAuth access token. |
OAuthExpiresIn | The lifetime in seconds of the OAuth AccessToken. |
OAuthTokenTimestamp | The Unix epoch timestamp in milliseconds when the current Access Token was created. |
InitiateOAuth¶
Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
Possible Values¶
OFF
, GETANDREFRESH
, REFRESH
Data Type¶
string
Default Value¶
OFF
Remarks¶
The following options are available:
OFF
: Indicates that the OAuth flow will be handled entirely by the user. An OAuthAccessToken will be required to authenticate.GETANDREFRESH
: Indicates that the entire OAuth Flow will be handled by the connector. If no token currently exists, it will be obtained by prompting the user via the browser. If a token exists, it will be refreshed when applicable.REFRESH
: Indicates that the connector will only handle refreshing the OAuthAccessToken. The user will never be prompted by the connector to authenticate via the browser. The user must handle obtaining the OAuthAccessToken and OAuthRefreshToken initially.
OAuthClientId¶
The client ID assigned when you register your application with an OAuth authorization server.
Data Type¶
string
Default Value¶
""
Remarks¶
As part of registering an OAuth application, you will receive the OAuthClientId
value, sometimes also called a consumer key, and a client secret, the OAuthClientSecret.
OAuthClientSecret¶
The client secret assigned when you register your application with an OAuth authorization server.
Data Type¶
string
Default Value¶
""
Remarks¶
As part of registering an OAuth application, you will receive the OAuthClientId, also called a consumer key. You will also receive a client secret, also called a consumer secret. Set the client secret in the OAuthClientSecret
property.
OAuthAccessToken¶
The access token for connecting using OAuth.
Data Type¶
string
Default Value¶
""
Remarks¶
The OAuthAccessToken
property is used to connect using OAuth. The OAuthAccessToken
is retrieved from the OAuth server as part of the authentication process. It has a server-dependent timeout and can be reused between requests.
The access token is used in place of your user name and password. The access token protects your credentials by keeping them on the server.
OAuthSettingsLocation¶
The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH . Alternatively, you can hold this location in memory by specifying a value starting with 'memory://'
.
Data Type¶
string
Default Value¶
%APPDATA%\CosmosDB Data Provider\OAuthSettings.txt
Remarks¶
When InitiateOAuth is set to GETANDREFRESH
or REFRESH
, the connector saves OAuth values to avoid requiring the user to manually enter OAuth connection properties and to allow the credentials to be shared across connections or processes.
Instead of specifying a file path, you can use memory storage. Memory locations are specified by using a value starting with 'memory://'
followed by a unique identifier for that set of credentials (for example, memory://user1). The identifier can be anything you choose but should be unique to the user. Unlike file-based storage, where credentials persist across connections, memory storage loads the credentials into static memory, and the credentials are shared between connections using the same identifier for the life of the process. To persist credentials outside the current process, you must manually store the credentials prior to closing the connection. This enables you to set them in the connection when the process is started again. You can retrieve OAuth property values with a query to the sys_connection_props
system table. If there are multiple connections using the same credentials, the properties are read from the previously closed connection.
The default location is "%APPDATA%\CosmosDB Data Provider\OAuthSettings.txt" with %APPDATA%
set to the user's configuration directory. The default values are
- Windows: "
register://%DSN
" - Unix: "%AppData%..."
- Mac: "%AppData%..."
where DSN is the name of the current DSN used in the open connection.
The following table lists the value of %APPDATA%
by OS:
Platform | %APPDATA% |
---|---|
Windows | The value of the APPDATA environment variable |
Mac | ~/Library/Application Support |
Linux | ~/.config |
CallbackURL¶
The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
Data Type¶
string
Default Value¶
""
Remarks¶
During the authentication process, the OAuth authorization server redirects the user to this URL. This value must match the callback URL you specify in your app settings.
OAuthGrantType¶
The grant type for the OAuth flow.
Possible Values¶
CODE
, CLIENT
, PASSWORD
Data Type¶
string
Default Value¶
CLIENT
Remarks¶
The following options are available: CODE,CLIENT,PASSWORD
OAuthVerifier¶
The verifier code returned from the OAuth authorization URL.
Data Type¶
string
Default Value¶
""
Remarks¶
The verifier code returned from the OAuth authorization URL. This can be used on systems where a browser cannot be launched such as headless systems.
Authentication on Headless Machines¶
See to obtain the OAuthVerifier
value.
Set OAuthSettingsLocation along with OAuthVerifier
. When you connect, the connector exchanges the OAuthVerifier
for the OAuth authentication tokens and saves them, encrypted, to the specified location. Set InitiateOAuth to GETANDREFRESH to automate the exchange.
Once the OAuth settings file has been generated, you can remove OAuthVerifier
from the connection properties and connect with OAuthSettingsLocation set.
To automatically refresh the OAuth token values, set OAuthSettingsLocation and additionally set InitiateOAuth to REFRESH.
OAuthRefreshToken¶
The OAuth refresh token for the corresponding OAuth access token.
Data Type¶
string
Default Value¶
""
Remarks¶
The OAuthRefreshToken
property is used to refresh the OAuthAccessToken when using OAuth authentication.
OAuthExpiresIn¶
The lifetime in seconds of the OAuth AccessToken.
Data Type¶
string
Default Value¶
""
Remarks¶
Pair with OAuthTokenTimestamp to determine when the AccessToken will expire.
OAuthTokenTimestamp¶
The Unix epoch timestamp in milliseconds when the current Access Token was created.
Data Type¶
string
Default Value¶
""
Remarks¶
Pair with OAuthExpiresIn to determine when the AccessToken will expire.
JWT OAuth¶
This section provides a complete list of JWT OAuth properties you can configure.
Property | Description |
---|---|
OAuthJWTCert | The JWT Certificate store. |
OAuthJWTCertType | The type of key store containing the JWT Certificate. |
OAuthJWTCertPassword | The password for the OAuth JWT certificate. |
OAuthJWTCertSubject | The subject of the OAuth JWT certificate. |
OAuthJWTCert¶
The JWT Certificate store.
Data Type¶
string
Default Value¶
""
Remarks¶
The name of the certificate store for the client certificate.
The OAuthJWTCertType field specifies the type of the certificate store specified by OAuthJWTCert
. If the store is password protected, specify the password in OAuthJWTCertPassword.
OAuthJWTCert
is used in conjunction with the OAuthJWTCertSubject
field in order to specify client certificates. If OAuthJWTCert
has a value, and OAuthJWTCertSubject is set, a search for a certificate is initiated. Please refer to the OAuthJWTCertSubject field for details.
Designations of certificate stores are platform-dependent.
The following are designations of the most common User and Machine certificate stores in Windows:
Property | Description |
---|---|
MY | A certificate store holding personal certificates with their associated private keys. |
CA | Certifying authority certificates. |
ROOT | Root certificates. |
SPC | Software publisher certificates. |
In Java, the certificate store normally is a file containing certificates and optional private keys.
When the certificate store type is PFXFile, this property must be set to the name of the file. When the type is PFXBlob, the property must be set to the binary contents of a PFX file (i.e. PKCS12 certificate store).
OAuthJWTCertType¶
The type of key store containing the JWT Certificate.
Possible Values¶
USER
, MACHINE
, PFXFILE
, PFXBLOB
, JKSFILE
, JKSBLOB
, PEMKEY_FILE
, PEMKEY_BLOB
, PUBLIC_KEY_FILE
, PUBLIC_KEY_BLOB
, SSHPUBLIC_KEY_FILE
, SSHPUBLIC_KEY_BLOB
, P7BFILE
, PPKFILE
, XMLFILE
, XMLBLOB
Data Type¶
string
Default Value¶
USER
Remarks¶
This property can take one of the following values:
Property | Description |
---|---|
USER | For Windows, this specifies that the certificate store is a certificate store owned by the current user. Note: This store type is not available in Java. |
MACHINE | For Windows, this specifies that the certificate store is a machine store. Note: this store type is not available in Java. |
PFXFILE | The certificate store is the name of a PFX (PKCS12) file containing certificates. |
PFXBLOB | The certificate store is a string (base-64-encoded) representing a certificate store in PFX (PKCS12) format. |
JKSFILE | The certificate store is the name of a Java key store (JKS) file containing certificates. Note: this store type is only available in Java. |
JKSBLOB | The certificate store is a string (base-64-encoded) representing a certificate store in Java key store (JKS) format. Note: this store type is only available in Java. |
PEMKEY_FILE | The certificate store is the name of a PEM-encoded file that contains a private key and an optional certificate. |
PEMKEY_BLOB | The certificate store is a string (base64-encoded) that contains a private key and an optional certificate. |
PUBLIC_KEY_FILE | The certificate store is the name of a file that contains a PEM- or DER-encoded public key certificate. |
PUBLIC_KEY_BLOB | The certificate store is a string (base-64-encoded) that contains a PEM- or DER-encoded public key certificate. |
SSHPUBLIC_KEY_FILE | The certificate store is the name of a file that contains an SSH-style public key. |
SSHPUBLIC_KEY_BLOB | The certificate store is a string (base-64-encoded) that contains an SSH-style public key. |
P7BFILE | The certificate store is the name of a PKCS7 file containing certificates. |
PPKFILE | The certificate store is the name of a file that contains a PPK (PuTTY Private Key). |
XMLFILE | The certificate store is the name of a file that contains a certificate in XML format. |
XMLBLOB | The certificate store is a string that contains a certificate in XML format. |
OAuthJWTCertPassword¶
The password for the OAuth JWT certificate.
Data Type¶
string
Default Value¶
""
Remarks¶
If the certificate store is of a type that requires a password, this property is used to specify that password in order to open the certificate store.
OAuthJWTCertSubject¶
The subject of the OAuth JWT certificate.
Data Type¶
string
Default Value¶
*
Remarks¶
When loading a certificate the subject is used to locate the certificate in the store.
If an exact match is not found, the store is searched for subjects containing the value of the property.
If a match is still not found, the property is set to an empty string, and no certificate is selected.
The special value "*" picks the first certificate in the certificate store.
The certificate subject is a comma separated list of distinguished name fields and values. For instance "CN=www.server.com, OU=test, C=US, E=example@jbexample.com". Common fields and their meanings are displayed below.
Field | Meaning |
---|---|
CN | Common Name. This is commonly a host name like www.server.com. |
O | Organization |
OU | Organizational Unit |
L | Locality |
S | State |
C | Country |
E | Email Address |
If a field value contains a comma it must be quoted.
SSL¶
This section provides a complete list of SSL properties you can configure.
Property | Description |
---|---|
SSLClientCert | The TLS/SSL client certificate store for SSL Client Authentication (2-way SSL). |
SSLClientCertType | The type of key store containing the TLS/SSL client certificate. |
SSLClientCertPassword | The password for the TLS/SSL client certificate. |
SSLClientCertSubject | The subject of the TLS/SSL client certificate. |
SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
SSLClientCert¶
The TLS/SSL client certificate store for SSL Client Authentication (2-way SSL).
Data Type¶
string
Default Value¶
""
Remarks¶
The name of the certificate store for the client certificate.
The SSLClientCertType field specifies the type of the certificate store specified by SSLClientCert
. If the store is password protected, specify the password in SSLClientCertPassword.
SSLClientCert
is used in conjunction with the SSLClientCertSubject field in order to specify client certificates. If SSLClientCert
has a value, and SSLClientCertSubject is set, a search for a certificate is initiated. See SSLClientCertSubject for more information.
Designations of certificate stores are platform-dependent.
The following are designations of the most common User and Machine certificate stores in Windows:
Property | Description |
---|---|
MY | A certificate store holding personal certificates with their associated private keys. |
CA | Certifying authority certificates. |
ROOT | Root certificates. |
SPC | Software publisher certificates. |
In Java, the certificate store normally is a file containing certificates and optional private keys.
When the certificate store type is PFXFile, this property must be set to the name of the file. When the type is PFXBlob, the property must be set to the binary contents of a PFX file (for example, PKCS12 certificate store).
SSLClientCertType¶
The type of key store containing the TLS/SSL client certificate.
Possible Values¶
USER
, MACHINE
, PFXFILE
, PFXBLOB
, JKSFILE
, JKSBLOB
, PEMKEY_FILE
, PEMKEY_BLOB
, PUBLIC_KEY_FILE
, PUBLIC_KEY_BLOB
, SSHPUBLIC_KEY_FILE
, SSHPUBLIC_KEY_BLOB
, P7BFILE
, PPKFILE
, XMLFILE
, XMLBLOB
Data Type¶
string
Default Value¶
USER
Remarks¶
This property can take one of the following values:
Property | Description |
---|---|
USER - default | For Windows, this specifies that the certificate store is a certificate store owned by the current user. Note that this store type is not available in Java. |
MACHINE | For Windows, this specifies that the certificate store is a machine store. Note that this store type is not available in Java. |
PFXFILE | The certificate store is the name of a PFX (PKCS12) file containing certificates. |
PFXBLOB | The certificate store is a string (base-64-encoded) representing a certificate store in PFX (PKCS12) format. |
JKSFILE | The certificate store is the name of a Java key store (JKS) file containing certificates. Note that this store type is only available in Java. |
JKSBLOB | The certificate store is a string (base-64-encoded) representing a certificate store in JKS format. Note that this store type is only available in Java. |
PEMKEY_FILE | The certificate store is the name of a PEM-encoded file that contains a private key and an optional certificate. |
PEMKEY_BLOB | The certificate store is a string (base64-encoded) that contains a private key and an optional certificate. |
PUBLIC_KEY_FILE | The certificate store is the name of a file that contains a PEM- or DER-encoded public key certificate. |
PUBLIC_KEY_BLOB | The certificate store is a string (base-64-encoded) that contains a PEM- or DER-encoded public key certificate. |
SSHPUBLIC_KEY_FILE | The certificate store is the name of a file that contains an SSH-style public key. |
SSHPUBLIC_KEY_BLOB | The certificate store is a string (base-64-encoded) that contains an SSH-style public key. |
P7BFILE | The certificate store is the name of a PKCS7 file containing certificates. |
PPKFILE | The certificate store is the name of a file that contains a PuTTY Private Key (PPK). |
XMLFILE | The certificate store is the name of a file that contains a certificate in XML format. |
XMLBLOB | The certificate store is a string that contains a certificate in XML format. |
SSLClientCertPassword¶
The password for the TLS/SSL client certificate.
Data Type¶
string
Default Value¶
""
Remarks¶
If the certificate store is of a type that requires a password, this property is used to specify that password to open the certificate store.
SSLClientCertSubject¶
The subject of the TLS/SSL client certificate.
Data Type¶
string
Default Value¶
*
Remarks¶
When loading a certificate the subject is used to locate the certificate in the store.
If an exact match is not found, the store is searched for subjects containing the value of the property. If a match is still not found, the property is set to an empty string, and no certificate is selected.
The special value "*" picks the first certificate in the certificate store.
The certificate subject is a comma separated list of distinguished name fields and values. For example, "CN=www.server.com, OU=test, C=US, E=support@company.com". The common fields and their meanings are shown below.
Field | Meaning |
---|---|
CN | Common Name. This is commonly a host name like www.server.com. |
O | Organization |
OU | Organizational Unit |
L | Locality |
S | State |
C | Country |
E | Email Address |
If a field value contains a comma, it must be quoted.
SSLServerCert¶
The certificate to be accepted from the server when connecting using TLS/SSL.
Data Type¶
string
Default Value¶
""
Remarks¶
If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.
This property can take the following forms:
Description | Example |
---|---|
A full PEM Certificate (example shortened for brevity) | -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE----- |
A path to a local file containing the certificate | C:\\cert.cer |
The public key (example shortened for brevity) | -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY----- |
The MD5 Thumbprint (hex values can also be either space or colon separated) | ecadbdda5a1529c58a1e9e09828d70e4 |
The SHA1 Thumbprint (hex values can also be either space or colon separated) | 34a929226ae0819f2ec14b4a3d904f801cbb150d |
If not specified, any certificate trusted by the machine is accepted.
Certificates are validated as trusted by the machine based on the System's trust store. The trust store used is the 'javax.net.ssl.trustStore' value specified for the system. If no value is specified for this property, Java's default trust store is used (for example, JAVA_HOME\lib\security\cacerts).
Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.
Schema¶
This section provides a complete list of schema properties you can configure.
Property | Description |
---|---|
Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC. |
Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC. |
Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC. |
Schema | Specify the Azure Cosmos DB database you want to work with. |
Location¶
A path to the directory that contains the schema files defining tables, views, and stored procedures.
Data Type¶
string
Default Value¶
%APPDATA%\CosmosDB Data Provider\Schema
Remarks¶
The path to a directory which contains the schema files for the connector (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location
property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.
If left unspecified, the default location is "%APPDATA%\CosmosDB Data Provider\Schema" with %APPDATA%
being set to the user's configuration directory:
Platform | %APPDATA% |
---|---|
Windows | The value of the APPDATA environment variable |
Mac | ~/Library/Application Support |
Linux | ~/.config |
BrowsableSchemas¶
This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
Data Type¶
string
Default Value¶
""
Remarks¶
Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.
Tables¶
This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.
Data Type¶
string
Default Value¶
""
Remarks¶
Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the connector.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.
Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
Views¶
Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.
Data Type¶
string
Default Value¶
""
Remarks¶
Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the connector.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.
Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
Schema¶
Specify the Azure Cosmos DB database you want to work with.
Data Type¶
string
Default Value¶
""
Remarks¶
Specify the Azure Cosmos DB database you want to work with.
Miscellaneous¶
This section provides a complete list of miscellaneous properties you can configure.
Property | Description |
---|---|
CalculateAggregates | Specifies whether will return the calculated value of the aggregates or grouped by partiton range. |
ConsistencyLevel | Denotes the type of token: master or resource. |
FlattenArrays | By default, nested arrays are returned as strings of JSON. The FlattenArrays property can be used to flatten the elements of nested arrays into columns of their own. Set FlattenArrays to the number of elements you want to return from nested arrays. |
FlattenObjects | Set FlattenObjects to true to flatten object properties into columns of their own. Otherwise, objects nested in arrays are returned as strings of JSON. |
ForceQueryOnNonIndexedContainers | Force the use of an index scan to process the query if indexing is disabled or the right index path is not available. |
GenerateSchemaFiles | Indicates the user preference as to when schemas should be generated and saved. |
MaxRows | Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses. |
MaxThreads | Specifies the maximum number of concurrent requests for Batch CUD (Create, Update, Delete) operations. |
MultiThreadCount | Aggregate queries in partitioned collections will require parallel requests for different partition ranges. Set this to the number of parallel request to be issued in the same time. |
Other | These hidden properties are used only in specific use cases. |
Pagesize | The maximum number of results to return per page from Azure Cosmos DB. |
PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
RequestPriorityLevel | Specifies the priority for the request sent to Azure Cosmos DB, when there are more requests than the configured RU/s in a second. |
RowScanDepth | The maximum number of rows to scan to look for the columns available in a table. |
SeparatorCharacter | The character or characters used to denote hierarchy. |
SetPartitionKeyAsPK | Whether or not to use the collection's Partition Key field as part of composite Primary Key for the corresponding exposed table. |
Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
TypeDetectionScheme | Comma-separated options for how the provider will scan the data to determine the fields and datatypes in each document collection. |
UserDefinedViews | A filepath pointing to the JSON configuration file containing your custom views. |
UseRidAsPk | Set this property to false to switch using the ID column as primary key instead the default _rid. |
WriteThroughputBudget | Defines the Requests Units (RU) budget per Second that the Batch CUD (Create, Update, Delete) operations should not exceed. |
CalculateAggregates¶
Specifies whether will return the calculated value of the aggregates or grouped by partiton range.
Data Type¶
bool
Default Value¶
true
Remarks¶
Specifies whether will return the calculated value of the aggregates or grouped by partiton range.
ConsistencyLevel¶
Denotes the type of token: master or resource.
Possible Values¶
STRONG
, BOUNDED
, SESSION
, CONSISTENTPREFIX
, EVENTUAL
Data Type¶
string
Default Value¶
SESSION
Remarks¶
The consistency level override for read options against documents and attachments. The valid values are: Strong, Bounded, Session, or Eventual (in order of strongest to weakest). The override must be the same or weaker than the account's configured consistency level.
The consistency level override for read options against documents and attachments. The valid values are: Strong, Bounded, Session, or Eventual (in order of strongest to weakest). The override must be the same or weaker than the account's configured consistency level.
FlattenArrays¶
By default, nested arrays are returned as strings of JSON. The FlattenArrays property can be used to flatten the elements of nested arrays into columns of their own. Set FlattenArrays to the number of elements you want to return from nested arrays.
Data Type¶
string
Default Value¶
0
Remarks¶
By default, nested arrays are returned as strings of JSON. The FlattenArrays
property can be used to flatten the elements of nested arrays into columns of their own. This is only recommended for arrays that are expected to be short.
Set FlattenArrays
to the number of elements you want to return from nested arrays. The specified elements are returned as columns. The zero-based index is concatenated to the column name. Other elements are ignored.
For example, you can return an arbitrary number of elements from an array of strings:
["FLOW-MATIC","LISP","COBOL"]
When FlattenArrays
is set to 1, the preceding array is flattened into the following table:
Column Name | Column Value |
---|---|
languages.0 | FLOW-MATIC |
Setting FlattenArrays
to -1 will flatten all the elements of nested arrays.
FlattenObjects¶
Set FlattenObjects to true to flatten object properties into columns of their own. Otherwise, objects nested in arrays are returned as strings of JSON.
Data Type¶
bool
Default Value¶
true
Remarks¶
Set FlattenObjects
to true to flatten object properties into columns of their own. Otherwise, objects nested in arrays are returned as strings of JSON. The property name is concatenated onto the object name with a dot to generate the column name.
For example, you can flatten the nested objects below at connection time:
[
{ "grade": "A", "score": 2 },
{ "grade": "A", "score": 6 },
{ "grade": "A", "score": 10 },
{ "grade": "A", "score": 9 },
{ "grade": "B", "score": 14 }
]
When FlattenObjects
is set to true and FlattenArrays is set to 1, the preceding array is flattened into the following table:
Column Name | Column Value |
---|---|
grades.0.grade | A |
grades.0.score | 2 |
ForceQueryOnNonIndexedContainers¶
Force the use of an index scan to process the query if indexing is disabled or the right index path is not available.
Data Type¶
bool
Default Value¶
false
Remarks¶
Queries against containers where indexing is disabled or paths are excluded may fail. Set this property to true to force the use of indexing on the server so the query is processed successfully. By default, queries that require the use of indexing on containers where IndexingMode=None are handled client-side.
GenerateSchemaFiles¶
Indicates the user preference as to when schemas should be generated and saved.
Possible Values¶
Never
, OnUse
, OnStart
, OnCreate
Data Type¶
string
Default Value¶
Never
Remarks¶
GenerateSchemaFiles
enables you to save the table definitions identified by Automatic Schema Discovery. This property outputs schemas to .rsd files in the path specified by Location.
Available settings are the following:
- Never: A schema file will never be generated.
- OnUse: A schema file will be generated the first time a table is referenced, provided the schema file for the table does not already exist.
- OnStart: A schema file will be generated at connection time for any tables that do not currently have a schema file.
- OnCreate: A schema file will be generated by when running a CREATE TABLE SQL query.
Note that if you want to regenerate a file, you will first need to delete it.
Generate Schemas with SQL¶
When you set GenerateSchemaFiles
to OnUse
, the connector generates schemas as you execute SELECT queries. Schemas are generated for each table referenced in the query.
When you set GenerateSchemaFiles
to OnCreate
, schemas are only generated when a CREATE TABLE query is executed.
Generate Schemas on Connection¶
Another way to use this property is to obtain schemas for every table in your database when you connect. To do so, set GenerateSchemaFiles
to OnStart
and connect.
Alternatives to Static Schemas¶
If your data structures are volatile, consider setting GenerateSchemaFiles
to Never and using dynamic schemas. See Automatic Schema Discovery for more information about dynamic schemas.
Editing Schemas¶
Schema files have a simple format that makes them easy to modify. See Custom Schema Definitions for more information.
MaxRows¶
Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Data Type¶
int
Default Value¶
-1
Remarks¶
Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
MaxThreads¶
Specifies the maximum number of concurrent requests for Batch CUD (Create, Update, Delete) operations.
Data Type¶
int
Default Value¶
200
Remarks¶
This property should be used in conjunction with the WriteThroughputBudget connection property. The connector may execute less parallel requests than the configured MaxThreads
value, since it always aims to not exceed the WriteThroughputBudget limit. The number of concurrent requests will also depend on the running machine's resources.
Note
This property is applicable only when executing batch CUD operations.
MultiThreadCount¶
Aggregate queries in partitioned collections will require parallel requests for different partition ranges. Set this to the number of parallel request to be issued in the same time.
Data Type¶
string
Default Value¶
5
Remarks¶
Aggregate queries in partitioned collections will require parallel requests for different partition ranges. Set this to the number of parallel request to be issued in the same time.
Other¶
These hidden properties are used only in specific use cases.
Data Type¶
string
Default Value¶
""
Remarks¶
The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.
Specify multiple properties in a semicolon-separated list.
Integration and Formatting¶
Property | Description |
---|---|
DefaultColumnSize | Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000. |
ConvertDateTimeToGMT | Determines whether to convert date-time values to GMT, instead of the local time of the machine. |
RecordToFile=filename | Records the underlying socket data transfer to the specified file. |
Pagesize¶
The maximum number of results to return per page from Azure Cosmos DB.
Data Type¶
int
Default Value¶
1000
Remarks¶
The Pagesize
property affects the maximum number of results to return per page from Azure Cosmos DB. Setting a higher value may result in better performance at the cost of additional memory allocated per page consumed.
PseudoColumns¶
This property indicates whether or not to include pseudo columns as columns to the table.
Data Type¶
string
Default Value¶
""
Remarks¶
This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".
RequestPriorityLevel¶
Specifies the priority for the request sent to Azure Cosmos DB, when there are more requests than the configured RU/s in a second.
Possible Values¶
None
, Low
, High
Data Type¶
string
Default Value¶
None
Remarks¶
- None: Set this to send requests with the default priority.
- Low: Set this to send low priority requests.
- High: Set this to send high priority requests.
RowScanDepth¶
The maximum number of rows to scan to look for the columns available in a table.
Data Type¶
int
Default Value¶
100
Remarks¶
The columns in a table must be determined by scanning table rows. This value determines the maximum number of rows that will be scanned.
Setting a high value may decrease performance. Setting a low value may prevent the data type from being determined properly, especially when there is null data.
SeparatorCharacter¶
The character or characters used to denote hierarchy.
Data Type¶
string
Default Value¶
.
Remarks¶
In order to flatten out hierarchical structures, the connector needs some specifier that states the path to a column through the hierarchy. If this value is "." and a column comes back with the name address.city, this indicates that there is a mapped attribute with a child called city. If your data has columns that already use a single period within the attribute name, set the SeparatorCharacter
to a different character or characters.
SetPartitionKeyAsPK¶
Whether or not to use the collection's Partition Key field as part of composite Primary Key for the corresponding exposed table.
Data Type¶
bool
Default Value¶
true
Remarks¶
By default, this is set to TRUE, and the collection's Partition Key is used as part of the table's composite Primary Key along with the _rid column. If this is set to FALSE, only the _rid column will serve as the Primary Key for the exposed table.
Timeout¶
The value in seconds until the timeout error is thrown, canceling the operation.
Data Type¶
int
Default Value¶
60
Remarks¶
If Timeout
= 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.
If Timeout
expires and the operation is not yet complete, the connector throws an exception.
TypeDetectionScheme¶
Comma-separated options for how the provider will scan the data to determine the fields and datatypes in each document collection.
Data Type¶
string
Default Value¶
RowScan,Recent
Remarks¶
Property | Description |
---|---|
None | Setting TypeDetectionScheme to None will return all columns as a string type. Cannot be combined with other options. |
RowScan | Setting TypeDetectionScheme to RowScan will scan rows to heuristically determine the data type. The RowScanDepth determines the number of rows to be scanned. Can be used with Recent. |
Recent | Setting TypeDetectionScheme to Recent will determine whether RowScan is executed on the most recent documents in the collection. Can be used with RowScan. |
RawValue | Setting TypeDetectionScheme to RawValue will push each document as single aggregate on a column named JsonData, along with its resource identifier on the separate Primary Key column. Cannot be combined with other options. |
UserDefinedViews¶
A filepath pointing to the JSON configuration file containing your custom views.
Data Type¶
string
Default Value¶
""
Remarks¶
User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json
. The connector automatically detects the views specified in this file.
You can also have multiple view definitions and control them using the UserDefinedViews
connection property. When you use this property, only the specified views are seen by the connector.
This User Defined View configuration file is formatted as follows:
- Each root element defines the name of a view.
- Each root element contains a child element, called
query
, which contains the custom SQL query for the view.
For example:
{
"MyView": {
"query": "SELECT * FROM [].[Entities].Customers WHERE MyColumn = 'value'"
},
"MyView2": {
"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
}
}
Use the UserDefinedViews
connection property to specify the location of your JSON configuration file. For example:
"UserDefinedViews", C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json
Note that the specified path is not embedded in quotation marks.
UseRidAsPk¶
Set this property to false to switch using the ID column as primary key instead the default _rid.
Data Type¶
bool
Default Value¶
true
Remarks¶
Since CosmosDB allows you to use both _rid and ID fields as unique values for retrieving resource data, you can set this property to false to switch using the ID column as primary key instead the default _rid.
WriteThroughputBudget¶
Defines the Requests Units (RU) budget per Second that the Batch CUD (Create, Update, Delete) operations should not exceed.
Data Type¶
int
Default Value¶
1000
Remarks¶
The connector will dynamically adjust the maximum number of requests per second depending on the configured RU budget. Although the connector always aims to not exceed the RU budget, since the requests throttling logic is applied client-side, it may be exceeded by a relatively small amount in a few cases. These cases include inserting, updating and deleting records with highly variable column count and input value length per column.
Note
This property is applicable only when executing batch CUD operations.