CData Cloud offers access to MongoDB across several standard services and protocols, in a cloud-hosted solution. Any application that can connect to a MySQL or SQL Server database can connect to MongoDB through CData Cloud.
CData Cloud allows you to standardize and configure connections to MongoDB as though it were any other OData endpoint, or standard SQL Server/MySQL database.
This page provides a guide to Establishing a Connection to MongoDB in CData Cloud, as well as information on the available resources, and a reference to the available connection properties.
Establishing a Connection shows how to authenticate to MongoDB and configure any necessary connection properties to create a database in CData Cloud
Accessing data from MongoDB through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.
Connect to MongoDB by selecting the corresponding icon in the Database tab. Required properties are listed under Settings. The Advanced tab lists connection properties that are not typically required.
Set the following connection properties to connect to a single MongoDB instance:
To connect to a replica set, set the following in addition to the preceding connection properties:
You can set UseSSL to negotiate SSL/TLS encryption when you connect.
Supported AuthScheme types (MONGODB-CR,SCRAM-SHA-1,SCRAM-SHA-256,PLAIN,GSSAPI) are challenge-response authentication and LDAP.
In challenge-response authentication, the User and Password properties correspond to a username and password stored in a MongoDB database. If you want to connect to data from one database and authenticate to another database, set both Database and AuthDatabase.
To use LDAP authentication, set AuthDatabase to "$external" and set AuthScheme to PLAIN. This value specifies the SASL PLAIN mechanism; note that this mechanism transmits credentials over plaintext, so it is not suitable for use without TLS/SSL on untrusted networks.
Set AuthScheme to X509 to use X.509 certificate authentication.
Before you can connect to Amazon DocumentDB, you will first need to, ensure your Amazon DocumentDB cluster and the EC2 instance containing the mongo shell are currently running.
Next, configure an SSH tunnel to the EC2 instance as follows.
Specify the following to connect to the DocumentDB cluster.
To obtain the connection string needed to connect to a Cosmos DB account using the MongoDB API, log in to the Azure Portal, select Azure Cosmos DB, and select your account. In the Settings section, click Connection String and set the following values.
When you connect to Atlas, ObjectRocket, or another database-as-a-service provider, there typically are a few variations on the procedure outlined in Establishing a Connection. The following sections show how to obtain the necessary connection properties for several popular services.
You can authenticate to MongoDB Atlas with a MongoDB user or an LDAP user. The following sections show how to map Atlas connection strings to Cloud connection properties. To obtain the Atlas connection string, follow the steps below:
In addition to creating a MongoDB user and/or setting up LDAP, your Atlas project's white-list must include the IP address of the machine the Cloud is connecting from. To add an IP address to the white-list, select the Security tab in the Clusters view and then click IP Whitelist -> Add IP Address.
Below is an example connection string providing a MongoDB user's credentials.
mongodb://USERNAME:[email protected]:27017,cluster0-shard-00-01.mongodb.net:27017,cluster0-shard-00-02.mongodb.net:27017/test?ssl=true&replicaSet=Cluster0-shard-0&authSource=admin
Below are the corresponding Cloud connection properties:
cluster0-shard-00-00.mongodb.netmycluster0-shard-00-01.mongodb.net:27017,mycluster0-shard-00-02.mongodb.net:27017User: The username of a MongoDB user you added to your MongoDB project.
Password: The password of the MongoDB user.
The following list shows the MongoDB Atlas requirements for authenticating with an LDAP user.
Below is an example command to connect with the mongo client:
mongo "mongodb://cluster0-shard-00-00.mongodb.net:27017,cluster0-shard-00-01.mongodb.net:27017,cluster0-shard-00-02.mongodb.net:27017/test?ssl=true&replicaSet=Cluster0-shard-0&authSource=$external" --authenticationMechanism PLAIN --username cn=rob,cn=Users,dc=atlas-ldaps-01,dc=myteam,dc=com
Server: The first server in the replica set. Or, you can specify another primary or secondary server here (the Cloud queries the servers in Server and ReplicaSet to find the primary).
For example:
cluster0-shard-00-00.mongodb.net
mycluster0-shard-00-01.mongodb.net:27017,mycluster0-shard-00-02.mongodb.net:27017AuthScheme: PLAIN in LDAP authentication.
Database: The database you want to read from and write to.
AuthDatabase: "$external" to authenticate with an LDAP user.
User: The full Distinguished Name (DN) of a user in your LDAP server as the Atlas username. For example:
cn=rob,cn=Users,dc=atlas-ldaps-01,dc=myteam,dc=com
Password: The password of the LDAP user.
UseSSL: true. Atlas requires TLS/SSL.
To connect to ObjectRocket, you authenticate with the credentials for a database user. You can obtain the necessary connection properties from the control panel: On the Instances page, select your instance and then select the Connect menu to display a MongoDB connection string.
In addition to adding a user for your database, you also need to allow access to the IP address for the machine the Cloud is connecting from. You can configure this by selecting your instance on the Instances page and then clicking Add ACL.
mongodb://YOUR_USERNAME:[email protected]:52826,abc123-d4-2.mongo.objectrocket.com:52826,abc123-d4-1.mongo.objectrocket.com:52826/YOUR_DATABASE_NAME?replicaSet=89c04c5db2cf403097d8f2e8ca871a1c
Below are the corresponding Cloud connection properties:
abc123-d4-0.mongo.objectrocket.comabc123-d4-2.mongo.objectrocket.com:52826,abc123-d4-1.mongo.objectrocket.com:52826MongoDB 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 Cloud offers to bridge the gap with relational SQL and a document database.
The Cloud models the schemaless MongoDB objects into relational tables and translates SQL queries into MongoDB queries to get the requested data. See Query Mapping for more details on how various MongoDB operations are represented as SQL.
The Automatic Schema Discovery scheme automatically finds the data types in a MongoDB 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 MongoDB. You can also write Free-Form Queries not tied to the schema.
The Cloud automatically infers a relational schema by inspecting a series of MongoDB documents in a collection. You can use the RowScanDepth property to define the number of documents the Cloud will scan to do so. The columns identified during the discovery process depend on the FlattenArrays and FlattenObjects properties.
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.
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.
As discussed in Automatic Schema Discovery, intuited table schemas enable SQL access to unstructured MongoDB data. JSON Functions enable you to use standard JSON functions to summarize MongoDB 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 |
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 |
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 |
It's also possible to build queries targeting arrays within other arrays.
Consider this sample Inventory collection:
{
"_id": {
"$oid": "xxxxxxxxxxxxxxxxxxxxxx"
},
"Company Branch": "Main Branch",
"ItemList": [
{
"item": "journal",
"instock": [
{
"warehouse": "A",
"qty": 15
},
{
"warehouse": "B",
"qty": 45
}
]
},
{
"item": "paper",
"instock": [
{
"warehouse": "A",
"qty": 50
},
{
"warehouse": "B",
"qty": 5
}
]
}
]
}
Insert data into the nested arrays using the syntax of <parent array>.<index>.<child array>, as follows:
INSERT INTO [Inventory.ItemList] (p_id, item, [instock.0.warehouse], [instock.0.qty], [instock.0.price]) VALUES ('xxxxxxxxxxxxxxxxxxxxxx', 'NoteBook', 'B', 20, '5$')
The Inventory collection after executing the INSERT statement:
{
"_id": {
"$oid": "xxxxxxxxxxxxxxxxxxxxxx"
},
"Company Branch": "Main Branch",
"ItemList": [
{
"item": "journal",
"instock": [
{
"warehouse": "A",
"qty": 15
},
{
"warehouse": "B",
"qty": 45
}
]
},
{
"item": "paper",
"instock": [
{
"warehouse": "A",
"qty": 50
},
{
"warehouse": "B",
"qty": 5
}
]
},
{
"item": "NoteBook",
"instock": [
{
"warehouse": "B",
"qty": 20,
"price": "5$"
}
]
}
]
}
The Cloud can return JSON structures as column values. The Cloud 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 }
]
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 |
SELECT Name, JSON_COUNT(grades,'[x]') AS NumberOfGrades FROM Students;
| Column Name | Example Value |
| NumberOfGrades | 5 |
SELECT Name, JSON_SUM(score,'[x].score') AS TotalScore FROM Students;
| Column Name | Example Value |
| TotalScore | 41 |
SELECT Name, JSON_MIN(score,'[x].score') AS LowestScore FROM Students;
| Column Name | Example Value |
| LowestScore | 2 |
SELECT Name, JSON_MAX(score,'[x].score') AS HighestScore FROM Students;
| Column Name | Example Value |
| HighestScore | 14 |
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 }
The Cloud maps SQL queries into the corresponding MongoDB 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 Cloud takes advantage of MongoDB features such as the aggregation framework to compute the desired results.
| SQL Query | MongoDB Query |
SELECT * FROM Users | db.users.find() |
SELECT user_id, status FROM Users | db.users.find(
{},
{ user_id: 1, status: 1, _id: 0 }
) |
SELECT * FROM Users WHERE status = 'A' | db.users.find(
{ status: "A" }
) |
SELECT * FROM Users WHERE status = 'A' OR age=50 | db.users.find(
{ $or: [ { status: "A" },
{ age: 50 } ] }
) |
SELECT * FROM Users WHERE name LIKE 'A%' | db.users.find(
{name: /^a/}
) |
SELECT * FROM Users WHERE status = 'A' ORDER BY user_id ASC | db.users.find( { status: "A" }.sort( { user_id: 1 } ) |
SELECT * FROM Users WHERE status = 'A' ORDER BY user_id DESC | db.users.find( {status: "A" }.sort( {user_id: -1} ) |
| SQL Query | MongoDB Query |
SELECT Count(*) As Count FROM Orders | db.orders.aggregate( [
{
$group: {
_id: null,
count: { $sum: 1 }
}
}
] ) |
SELECT Sum(price) As Total FROM Orders | db.orders.aggregate( [
{
$group: {
_id: null,
total: { $sum: "$price" }
}
}
] ) |
SELECT cust_id, Sum(price) As total FROM Orders GROUP BY cust_id ORDER BY total | db.orders.aggregate( [
{
$group: {
_id: "$cust_id",
total: { $sum: "$price" }
}
} ,
{ $sort: {total: 1 } }
] ) |
SELECT cust_id, ord_date, Sum(price) As total FROM Orders GROUP BY cust_id, ord_date HAVING total > 250 |
db.orders.aggregate( [
{
$group: {
_id: {
cust_id: "$cust_id",
ord_date: {
month: { $month: "$ord_date" },
day: { $dayOfMonth: "$ord_date" },
year: { $year: "$ord_date"}
}
},
total: { $sum: "$price" }
}
},
{ $match: { total: { $gt: 250 } } }
] ) |
| SQL Query | MongoDB Query |
INSERT INTO users (user_id, age, status, [address.city], [address.postalcode])
VALUES ('bcd001', 45, 'A', 'Chapel Hill', 27517) | db.users.insert(
{ user_id: "bcd001", age: 45, status: "A", address:{ city:"Chapel Hill", postalCode:27514} }
) |
INSERT INTO t1 ("c1") VALUES (('a1', 'a2', 'a3')) | db.users.insert({"c1": ['a1', 'a2', 'a3']}) |
INSERT INTO t1 ("c1") VALUES (()) | db.users.insert({"c1": []}) |
INSERT INTO t1 ("a.b.c.c1") VALUES (('a1', 'a2', 'a3')) | db.users.insert("a":{"b":{"c":{"c1":['a1','a2', 'a3']}}}) |
| SQL Query | MongoDB Query |
UPDATE users SET status = 'C', [address.postalcode] = 90210 WHERE age > 25 | db.users.update(
{ age: { $gt: 25 } },
{ $set: { status: "C", address.postalCode: 90210 },
{ multi: true }
) |
| SQL Query | MongoDB Query |
DELETE FROM users WHERE status = 'D' | db.users.remove( { status: "D" } ) |
You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.
The following tables return database metadata for MongoDB:
The following tables return information about how to connect to and query the data source:
The following table returns query statistics for data modification queries, including batch operations::
Lists the available databases.
The following query retrieves all databases determined by the connection string:
SELECT * FROM sys_catalogs
| Name | Type | Description |
| CatalogName | String | The database name. |
Lists the available schemas.
The following query retrieves all available schemas:
SELECT * FROM sys_schemas
| Name | Type | Description |
| CatalogName | String | The database name. |
| SchemaName | String | The schema name. |
Lists the available tables.
The following query retrieves the available tables and views:
SELECT * FROM sys_tables
| 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. |
Describes the columns of the available tables and views.
The following query returns the columns and data types for the [CData].[Sample].Customers table:
SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Customers' AND CatalogName='CData' AND SchemaName='Sample'
| 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. |
Lists the available stored procedures.
The following query retrieves the available stored procedures:
SELECT * FROM sys_procedures
| 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. |
Describes stored procedure parameters.
The following query returns information about all of the input parameters for the AddDocument stored procedure:
SELECT * FROM sys_procedureparameters WHERE ProcedureName='AddDocument' AND Direction=1 OR Direction=2
| 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. |
Describes the primary and foreign keys.
The following query retrieves the primary key for the [CData].[Sample].Customers table:
SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Customers' AND CatalogName='CData' AND SchemaName='Sample'
| 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. |
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'
| 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. |
Describes the primary keys.
The following query retrieves the primary keys from all tables and views:
SELECT * FROM sys_primarykeys
| 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. |
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'
| 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. |
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:mongodb: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 <> ''
| 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. |
Describes the SELECT query processing that the Cloud can offload to the data source.
See SQL Compliance for SQL syntax details.
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 |
| OJ_CAPABILITIES | The supported varieties of outer joins supported. | NO, LEFT, RIGHT, FULL, INNER, NOT_ORDERED, ALL_COMPARISON_OPS |
| OUTER_JOINS | Whether outer joins are supported. | YES, NO |
| SUBQUERIES | Whether subqueries are supported, and, if so, the degree of support. | NO, COMPARISON, EXISTS, IN, CORRELATED_SUBQUERIES, QUANTIFIED |
| 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 CData Data Sync version needed to access this driver. | Standard, Starter, Professional, Enterprise |
| DATASYNCCATEGORY | The CData 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.
| 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. |
Returns information about attempted modifications.
The following query retrieves the Ids of the modified rows in a batch operation:
SELECT * FROM sys_identity
| 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. |
Stored procedures are function-like interfaces that extend the functionality of the Cloud beyond simple SELECT/INSERT/UPDATE/DELETE operations with MongoDB.
Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from MongoDB, along with an indication of whether the procedure succeeded or failed.
| Name | Description |
| AddDocument | Insert entire JSON documents to MongoDB as-is. |
| CreateUserTable | Creates a schema file for the collection. |
| GetDocument | Take a pass-through query to retrieve documents. |
| SearchDocument | Get the entire document as a string. |
Insert entire JSON documents to MongoDB as-is.
| Name | Type | Description |
| Collection | String | The collection name to be inserted. |
| Name | Type | Description |
| Success | String | Returns true if the operation is successful, else an exception is returned. |
Creates a schema file for the collection.
| Name | Type | Description |
| CatalogName | String | The catalog of the collection. |
| SchemaName | String | The schema of the collection. |
| TableName | String | The name of the collection. |
| Location | String | The location where the file is saved. |
| ColumnNames# | String | The name of column. |
| ColumnDataTypes# | String | The datatype of column. |
| ColumnSizes# | String | The size of column. |
| ColumnScales# | String | The scale of column. |
| ColumnIsKeys# | String | The isKey of column. |
| ColumnIsNulls# | String | The isNull of column. |
| ColumnDefaults# | String | The default value of column. |
| ColumnAutoIncrements# | String | The AutoIncrement of column. |
| Name | Type | Description |
| AffectedTables | String | The number of tables created, either 0 or 1 |
Take a pass-through query to retrieve documents.
| Name | Type | Description |
| Collection | String | The collection name to be inserted. |
| Query | String | The Mongo pass-through JSON-style query. |
| Projection | String | The Mongo pass-through JSON-style projection. |
| Name | Type | Description |
| * | String | Output will vary for each collection. |
Get the entire document as a string.
| Name | Type | Description |
| Collection | String | The collection name to search. |
| _id | String | The primary key value of the collection. |
| Name | Type | Description |
| Document | String | Returns the entire document as a string. |
By default, the Cloud attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.
To specify another certificate, see the SSLServerCert property for the available formats to do so.
The MongoDB Cloud also supports setting client certificates. Set the following to connect using a client certificate.
Set the following properties:
The connection string 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 in the connection string for this provider. Click the links for further details.
For more information on establishing a connection, see Establishing a Connection.
| Property | Description |
| AuthScheme | The authentication mechanism that MongoDB will use to authenticate the connection. |
| Server | The host name or IP address of the server hosting the MongoDB database. |
| Port | The port for the MongoDB database. |
| User | The MongoDB user account used to authenticate. |
| Password | The password used to authenticate the user. |
| Database | The name of the MongoDB database. |
| UseSSL | This field sets whether SSL is enabled. |
| AuthDatabase | The name of the MongoDB database for authentication. |
| ReplicaSet | This property allows you to specify multiple servers in addition to the one configured in Server and Port . Specify both a server name and port; separate servers with a comma. |
| DNSServer | Specify the DNS server when resolving MongoDB seed list. |
| Property | Description |
| SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
| Property | Description |
| SSHAuthMode | The authentication method used when establishing an SSH Tunnel to the service. |
| SSHClientCert | A certificate to be used for authenticating the SSHUser. |
| SSHClientCertPassword | The password of the SSHClientCert key if it has one. |
| SSHClientCertSubject | The subject of the SSH client certificate. |
| SSHClientCertType | The type of SSHClientCert private key. |
| SSHServer | The SSH server. |
| SSHPort | The SSH port. |
| SSHUser | The SSH user. |
| SSHPassword | The SSH password. |
| SSHServerFingerprint | The SSH server fingerprint. |
| UseSSH | Whether to tunnel the MongoDB connection over SSH. Use SSH. |
| Property | Description |
| Verbosity | The verbosity level that determines the amount of detail included in the log file. |
| Property | Description |
| BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC. |
| Property | Description |
| BuiltInColumnMapping | A list of column name mappings for MongoDB's built-in columns. |
| DataModel | By default, the provider will not automatically discover the metadata for a child table as its own distinct table. To enable this functionality, set DataModel to Relational . |
| 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. |
| MaxRows | Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses. |
| NoCursorTimeout | The server normally times out idle cursors after an inactivity period (10 minutes) to prevent excess memory use. Set this option to prevent that. |
| Pagesize | The maximum number of results to return per page from MongoDB. |
| PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
| ReadPreference | Set this to a strategy for reading from a replica set. Accepted values are primary, primaryPreferred, secondary, secondaryPreferred, and nearest. |
| ReadPreferenceTags | Use this property to target a replica set member or members that are associated with tags. |
| RowScanDepth | The maximum number of rows to scan to look for the columns available in a table. |
| SlaveOK | This property sets whether the provider is allowed to read from secondary (slave) servers. |
| 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. |
| UpdateScheme | Sets replacing or merging target document with updating fields is performed by executing update statement. |
| UseFindAPI | Execute MongoDB queries using db.collection.find(). |
| WriteConcern | Requests acknowledgment that the write operation has propagated to the specified number of mongod instances. |
| WriteConcernJournaled | Requires acknowledgment that the mongod instances, as specified in the WriteConcern property, have written to the on-disk journal. |
| WriteConcernTimeout | This option specifies a time limit, in milliseconds, for the write concern. |
| WriteScheme | Sets whether the object type for inserted or updated objects is determined from the existing column metadata or the input value type. |
This section provides a complete list of the Authentication properties you can configure in the connection string for this provider.
| Property | Description |
| AuthScheme | The authentication mechanism that MongoDB will use to authenticate the connection. |
| Server | The host name or IP address of the server hosting the MongoDB database. |
| Port | The port for the MongoDB database. |
| User | The MongoDB user account used to authenticate. |
| Password | The password used to authenticate the user. |
| Database | The name of the MongoDB database. |
| UseSSL | This field sets whether SSL is enabled. |
| AuthDatabase | The name of the MongoDB database for authentication. |
| ReplicaSet | This property allows you to specify multiple servers in addition to the one configured in Server and Port . Specify both a server name and port; separate servers with a comma. |
| DNSServer | Specify the DNS server when resolving MongoDB seed list. |
The authentication mechanism that MongoDB will use to authenticate the connection.
string
"SCRAM-SHA-1"
Accepted values are MONGODB-CR, SCRAM-SHA-1, SCRAM-SHA-256, GSSAPI, PLAIN, and NONE. The following authentication types correspond to the authentication values.
Generally, this property does not need to be set for this authentication type, as the Cloud uses different challenge-response mechanisms by default to authenticate a user to different versions of MongoDB.
Set AuthScheme to PLAIN to use LDAP authentication. This value specifies the SASL PLAIN mechanism; note that this mechanism transmits credentials over plain-text, so it is not suitable for use without TLS/SSL on untrusted networks.
Set AuthScheme to GSSAPI to use Kerberos authentication. Additionally configure the following properties as configured for the MongoDB environment:
| KerberosKDC | The FQDN of the domain controller. |
| KerberosRealm | The Kerberos Realm (for Windows this will be the AD domain). |
| KerberosSPN | The assigned service principle name for the user. |
| AuthDatabase | This value should be set to '$external'. |
| User | The user created in the $external database. |
| Password | The corresponding User's password. |
Set AuthScheme to X509 to use X.509 certificate authentication.
The host name or IP address of the server hosting the MongoDB database.
string
"localhost"
The host name or IP address of the server hosting the MongoDB database. If you choose to connect using DNS seed lists, set this option to "mongodb+srv://" + the name of the server your MongoDB instance is running on..
The port for the MongoDB database.
string
"27017"
The port for the MongoDB database.
The MongoDB user account used to authenticate.
string
""
Together with Password, this field is used to authenticate against the MongoDB server.
The password used to authenticate the user.
string
""
The User and Password are together used to authenticate with the server.
The name of the MongoDB database.
string
""
The name of the MongoDB database.
This field sets whether SSL is enabled.
bool
true
This field sets whether the Cloud will attempt to negotiate TLS/SSL connections to the server. By default, the Cloud checks the server's certificate against the system's trusted certificate store. To specify another certificate, set SSLServerCert.
The name of the MongoDB database for authentication.
string
""
The name of the MongoDB database for authentication. Only needed if the authentication database is different from the database to retrieve data from.
This property allows you to specify multiple servers in addition to the one configured in Server and Port . Specify both a server name and port; separate servers with a comma.
string
""
This property allows you to specify the other servers in the replica set in addition to the one configured in Server and Port. You must specify all servers in the replica set using ReplicaSet, Server, and Port.
Specify both a server name and port in ReplicaSet; separate servers with a comma. For example:
Server=localhost;Port=27017;ReplicaSet=localhost:27018,localhost:27019;
To find the primary server, the Cloud queries the servers in ReplicaSet and the server specified by Server and Port.
Note that only the primary server in a replica set is writable. Secondaries can be readable if the SlaveOK setting allows it. To configure a strategy executing SELECT queries to secondaries, see ReadPreference.
Specify the DNS server when resolving MongoDB seed list.
string
""
Specify the DNS server when resolving MongoDB seed list.
This section provides a complete list of the SSL properties you can configure in the connection string for this provider.
| Property | Description |
| SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
The certificate to be accepted from the server when connecting using TLS/SSL.
string
""
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.
Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.
This section provides a complete list of the SSH properties you can configure in the connection string for this provider.
| Property | Description |
| SSHAuthMode | The authentication method used when establishing an SSH Tunnel to the service. |
| SSHClientCert | A certificate to be used for authenticating the SSHUser. |
| SSHClientCertPassword | The password of the SSHClientCert key if it has one. |
| SSHClientCertSubject | The subject of the SSH client certificate. |
| SSHClientCertType | The type of SSHClientCert private key. |
| SSHServer | The SSH server. |
| SSHPort | The SSH port. |
| SSHUser | The SSH user. |
| SSHPassword | The SSH password. |
| SSHServerFingerprint | The SSH server fingerprint. |
| UseSSH | Whether to tunnel the MongoDB connection over SSH. Use SSH. |
The authentication method used when establishing an SSH Tunnel to the service.
string
"Password"
A certificate to be used for authenticating the SSHUser.
string
""
SSHClientCert must contain a valid private key in order to use public key authentication. A public key is optional, if one is not included then the Cloud generates it from the private key. The Cloud sends the public key to the server and the connection is allowed if the user has authorized the public key.
The SSHClientCertType field specifies the type of the key store specified by SSHClientCert. If the store is password protected, specify the password in SSHClientCertPassword.
Some types of key stores are containers which may include multiple keys. By default the Cloud will select the first key in the store, but you can specify a specific key using SSHClientCertSubject.
The password of the SSHClientCert key if it has one.
string
""
This property is only used when authenticating to SFTP servers with SSHAuthMode set to PublicKey and SSHClientCert set to a private key.
The subject of the SSH client certificate.
string
"*"
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, [email protected]". 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.
The type of SSHClientCert private key.
string
"PEMKEY_FILE"
This property can take one of the following values:
| Types | Description | Allowed Blob Values |
| MACHINE/USER | Blob values are not supported. | |
| JKSFILE/JKSBLOB | base64-only | |
| PFXFILE/PFXBLOB | A PKCS12-format (.pfx) file. Must contain both a certificate and a private key. | base64-only |
| PEMKEY_FILE/PEMKEY_BLOB | A PEM-format file. Must contain an RSA, DSA, or OPENSSH private key. Can optionally contain a certificate matching the private key. | base64 or plain text. Newlines may be replaced with spaces when providing the blob as text. |
| PPKFILE/PPKBLOB | A PuTTY-format private key created using the puttygen tool. | base64-only |
| XMLFILE/XMLBLOB | An XML key in the format generated by the .NET RSA class: RSA.ToXmlString(true). | base64 or plain text. |
The SSH server.
string
""
The SSH server.
The SSH port.
string
"22"
The SSH port.
The SSH user.
string
""
The SSH user.
The SSH password.
string
""
The SSH password.
The SSH server fingerprint.
string
""
The SSH server fingerprint.
Whether to tunnel the MongoDB connection over SSH. Use SSH.
bool
false
By default the Cloud will attempt to connect directly to MongoDB. When this option is enabled, the Cloud will instead establish an SSH connection with the SSHServer and tunnel the connection to MongoDB through it.
This section provides a complete list of the Logging properties you can configure in the connection string for this provider.
| Property | Description |
| Verbosity | The verbosity level that determines the amount of detail included in the log file. |
The verbosity level that determines the amount of detail included in the log file.
string
"1"
The verbosity level determines the amount of detail that the Cloud reports to the Logfile. Verbosity levels from 1 to 5 are supported. These are detailed in the Logging page.
This section provides a complete list of the Schema properties you can configure in the connection string for this provider.
| Property | Description |
| BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC. |
This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
string
""
Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.
This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.
| Property | Description |
| BuiltInColumnMapping | A list of column name mappings for MongoDB's built-in columns. |
| DataModel | By default, the provider will not automatically discover the metadata for a child table as its own distinct table. To enable this functionality, set DataModel to Relational . |
| 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. |
| MaxRows | Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses. |
| NoCursorTimeout | The server normally times out idle cursors after an inactivity period (10 minutes) to prevent excess memory use. Set this option to prevent that. |
| Pagesize | The maximum number of results to return per page from MongoDB. |
| PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
| ReadPreference | Set this to a strategy for reading from a replica set. Accepted values are primary, primaryPreferred, secondary, secondaryPreferred, and nearest. |
| ReadPreferenceTags | Use this property to target a replica set member or members that are associated with tags. |
| RowScanDepth | The maximum number of rows to scan to look for the columns available in a table. |
| SlaveOK | This property sets whether the provider is allowed to read from secondary (slave) servers. |
| 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. |
| UpdateScheme | Sets replacing or merging target document with updating fields is performed by executing update statement. |
| UseFindAPI | Execute MongoDB queries using db.collection.find(). |
| WriteConcern | Requests acknowledgment that the write operation has propagated to the specified number of mongod instances. |
| WriteConcernJournaled | Requires acknowledgment that the mongod instances, as specified in the WriteConcern property, have written to the on-disk journal. |
| WriteConcernTimeout | This option specifies a time limit, in milliseconds, for the write concern. |
| WriteScheme | Sets whether the object type for inserted or updated objects is determined from the existing column metadata or the input value type. |
A list of column name mappings for MongoDB's built-in columns.
string
""
This property takes a comma-separated list of MongoDB column names for built-in columns and maps them to new names.
The remappable built-in columns are "_index" and "P_id".
For example:
_index=BuiltInIndex,P_id=Parent_Id
Remapping these columns is particularly useful for resolving "column names must be unique" errors that can arise when the Cloud finds additional columns named "_index" or "P_id" other than the built-in columns.
By default, the provider will not automatically discover the metadata for a child table as its own distinct table. To enable this functionality, set DataModel to Relational .
string
"DOCUMENT"
When setting DataModel to Relational, the discovery of child tables extends to root level elements and those found within top-level array elements.
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.
string
""
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.
Set FlattenObjects to true to flatten object properties into columns of their own. Otherwise, objects nested in arrays are returned as strings of JSON.
bool
true
Set FlattenObjects to true to flatten object properties into columns of their own. Otherwise, objects nested in arrays are returned as strings of JSON. To generate the column name, the Cloud concatenates the property name onto the object name with a dot.
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 |
Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
int
-1
Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
The server normally times out idle cursors after an inactivity period (10 minutes) to prevent excess memory use. Set this option to prevent that.
bool
false
The server normally times out idle cursors after an inactivity period (10 minutes) to prevent excess memory use. Set this option to prevent that.
The maximum number of results to return per page from MongoDB.
int
4096
The Pagesize property affects the maximum number of results to return per page from MongoDB. Setting a higher value may result in better performance at the cost of additional memory allocated per page consumed.
This property indicates whether or not to include pseudo columns as columns to the table.
string
""
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, "*=*".
Set this to a strategy for reading from a replica set. Accepted values are primary, primaryPreferred, secondary, secondaryPreferred, and nearest.
string
"primary"
This property enables you to execute queries to a member in a replica set other other than the primary member. Accepted values are the following:
When this property is set, query results may not reflect the latest changes if a write operation has not yet been replicated to a secondary machine. You can use ReadPreference to accomplish the following, with some risk that the Cloud will return stale data:
When directing the Cloud to execute SELECT statements to a secondary server, SlaveOK must also be set. Otherwise, the Cloud will return an error response.
Use this property to target a replica set member or members that are associated with tags.
string
""
To make use of ReadPreferenceTags you must configure ReadPreference to a value other than the primary value (the default value). The required format is a list of semicolon seperated tag sets where each tag set is a list of key value pairs separated by commas. For example:
The maximum number of rows to scan to look for the columns available in a table.
int
100
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.
Setting to a value of -1 causes the Cloud to scan an arbitrary number of rows until it reaches the final row.
This property sets whether the provider is allowed to read from secondary (slave) servers.
bool
false
This property sets whether the Cloud is allowed to read from secondary (slave) servers in a replica set. You can fine-tune how the Cloud queries secondary servers with ReadPreference.
The value in seconds until the timeout error is thrown, canceling the operation.
int
60
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 Cloud throws an exception.
Comma-separated options for how the provider will scan the data to determine the fields and datatypes in each document collection.
string
"RowScan"
| 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 instead execute the rowscan on the most recent documents inserted into the collection. This is a more expensive operation that may be significantly slower on large datasets. |
Sets replacing or merging target document with updating fields is performed by executing update statement.
string
"Default"
Sets replacing or merging target document with updating fields is performed by executing update statement. When the default value Default is used, the Cloud updates the target document by replacing the whole original document with new one. When the value is set to Merge, only the specific field in the target document will be updated.
For example, if you have a collection 'classySample' as below.
{
"_id": "1",
"message": {
"component_items": [{"locked": true}],
"id":1
}
}
UPDATE [classySample] SET [message.component_items.0.locked] = false WHERE [message.id] = 1
In the query above, the 'message' document will be replaced with new document constructed with SET clause, the collection after updating looks like
{
"_id": "1",
"message": {
"component_items": [
{
"locked": false
}
]
}
}
But when using Merge, only the 'locked' field in 'component_items' will be updated, the collection becomes
{
"_id": "1",
"message": {
"component_items": [
{
"locked": false
}
],
"id": 1
}
}
Execute MongoDB queries using db.collection.find().
bool
true
Amazon DocumentDB doesn't support the legacy OP_QUERY interface, so this must be set to True to query DocumentDB clusters with db.collection.find() instead.
Requests acknowledgment that the write operation has propagated to the specified number of mongod instances.
string
"0"
Requests acknowledgment that the write operation has propagated to the specified number of mongod instances.
Requires acknowledgment that the mongod instances, as specified in the WriteConcern property, have written to the on-disk journal.
bool
true
It requests acknowledgment that the mongod instances, as specified in the WriteConcern property, have written to the on-disk journal.
This option specifies a time limit, in milliseconds, for the write concern.
string
"0"
This option specifies a time limit, in milliseconds, for the write concern.
Sets whether the object type for inserted or updated objects is determined from the existing column metadata or the input value type.
string
"Metadata"
Sets whether the object type for inserted or updated objects is determined from the existing column metadata or the input value type. When the default value Metadata is used, the Cloud uses the data type as determined by the TypeDetectionScheme for objects pushed to MongoDB. When the value is set to RawValue, the type of the object in the INSERT determines what type is used for MongoDB.
For example, if you have a field 'c1' in MongoDB defined as String type, the metadata returns the column as String as well. In the following query, the resulting field in MongoDB is therefore defined as String when using WriteScheme=Metadata. But when using RawValue, the inserting field type is Date instead since the FROM_UNIXTIME() function returns an actual Date object:
INSERT INTO Table1 (c1) VALUES (FROM_UNIXTIME(1636910867039, 0))
INSERT INTO t1 ("c1") VALUES (())
This returns an empty array:
"c1":[]