MongoDB Connector

You are looking at an older version of the documentation. The latest version is found here.

The MongoDB Connector is used to read data from a MongoDB Server and make it available in the Data Virtuality Server in the relational form. In this way, users can query a MongoDB Server via SQL query language and also write complex queries that are directed to multiple different Data Sources, e.g. a single SQL query can join tables from MySQL and combine them with collections (that are represented as tables) coming from MongoDB.

Connector Configuration

Connect to MongoDB

Before issuing the SQL queries to the MongoDB Server, we need to represent Mongo's Metadata in relational format, i.e. represent non-relation collections as relation tables etc. This is done by the connecting to MongoDB data source using createConnection/createDatasource commands.

The complete list of connection properties can be found in chapter Connection Configuration Interface.

The MongoDB specific connection properties options and readMode, translator property queryOption model properties (all) are explained in the sections below.

Example

CODE

CALL "SYSADMIN.createConnection"(
    "name" => 'mongodb',
    "jbossCLITemplateName" => 'mongodb',
    "connectionOrResourceAdapterProperties" => 
		'server=localhost
		,port=27019
		,database=test
		,options="connectionsPerHost=51,description=test,alwaysUseMBeans=true"',
    "encryptedProperties" => null
) ;;

CALL "SYSADMIN.createDataSource"(
    "name" => 'mongodb',
    "translator" => 'mongodb',
    "modelProperties" => 
		'importer.useFullSchemaName=false
		,importer.TableTypes="TABLE,VIEW"
		,mongodb.maxColumnsNumber=200
		,mongodb.maxRowsNumber=100
		,mongodb.maxVarcharColumnWidth=256
		,mongodb.maxRecursionDepth=2
		,mongodb.fieldDelimiterChar="_"',
    "translatorProperties" => 'queryOption="queryoption_notimeout"',
    "encryptedModelProperties" => null,
    "encryptedTranslatorProperties" => null
) ;;

MongoDB Driver Options

MongoDB driver options may be provided as a list of comma-separated properties via connection property options.

Supported MongoDB driver options:

Option type	Option name	Default value
String	description
int	minConnectionsPerHost	0
int	connectionsPerHost	100
int	threadsAllowedToBlockForConnectionMultiplier	5
int	serverSelectionTimeout	30000
int	maxWaitTime	120000
int	maxConnectionIdleTime	0
int	maxConnectionLifeTime	0
int	connectTimeout	10000
int	socketTimeout	0
boolean	socketKeepAlive	false
boolean	sslEnabled	false
boolean	sslInvalidHostNameAllowed	false
boolean	alwaysUseMBeans	false
int	heartbeatFrequency	10000
int	minHeartbeatFrequency	500
int	heartbeatConnectTimeout	20000
int	heartbeatSocketTimeout	20000
int	localThreshold	15
String	requiredReplicaSetName
boolean	cursorFinalizerEnabled	true

Important

The list of all possible options are supported dynamically by MongoDB Connector and can be extended in future versions of MongoDB driver automatically.

MongoDB readMode

MongoDB readMode may be provided via connection property readMode.

The not mandatory parameter readMode specifies how MongoDB connection routes read operations to the members of a replica set (a cluster of MongoDB servers that implements master-slave replication and automated failover. MongoDB’s recommended replication strategy). By default, a connection directs its read operations to the primary member (the primary member is the current master instance, which receives all write operations) in a replica set. Because write operations are issued to the single primary, reading from the primary returns the latest version of a document. For a connection that does not require fully up-to-date data, you can improve read throughput or reduce latency by distributing some or all reads to secondary members of the replica set. MongoDB driver supports five read modes:

primary Default mode. All operations read from the current replica set primary.
primaryPreferred In most situations, operations read from the primary but if it is unavailable, operations read from secondary members.
secondary All operations read from the secondary members of the replica set.
secondaryPreferred In most situations, operations read from secondary members but if no secondary members are available, operations read from the primary.
nearest Operations read from the member of the replica set with the least network latency, irrespective of the member’s type.

Important

All read preference modes except primary may return stale data because secondaries replicate operations from the primary with some delay.

MongoDB Query Options

The MongoDB specific query options can be set via MongoDB translator property queryOption.

These options are supported:

QUERYOPTION_TAILABLE

QUERYOPTION_SLAVEOK

QUERYOPTION_OPLOGREPLAY

QUERYOPTION_NOTIMEOUT

QUERYOPTION_AWAITDATA

QUERYOPTION_EXHAUST

QUERYOPTION_PARTIAL

For details about these options consider MongoDB documentation.

MongoDB Model Properties

MongoDB model properties may be provided via modelProperies in the createDatasource statement.

Importing Collection Fields

mongodb.maxColumnsNumber, mongodb.maxRowsNumber

To represent a non-relation MongoDB collection as a relational table, we need to represent the semi-structured collection rows as structured relational rows. To do this completely, we need to check all the fields in all rows of a MongoDB's collection and to add them as columns to a relation table representing that collection. Since extracting MongoDB's collection metadata in this way could be a very costly operation on big collections, Data Virtuality Server iterates only through the maximal number of rows (defined in mongodb.maxRowsNumber) and adds at most maxColumnsNumber of columns to the resulting relational table.

In addition, the same-named collection field can have a different format in different rows. Data Virtuality Server first takes to declare the column type as it has found it first in the field. If it finds out that the field type has changed, it changes the resulting column type to the "bigger" type if needed.

In general, that a type widening goes from a Numeric type to String to CLOB. Per default maxColumnsNumber = 200 and maxRowsNumber = 100. *

mongodb.maxVarcharColumnWidth

States, which column length should be used for varchar columns, e.g. sets varchar(mongodb.maxVarcharColumnWidth). Default value is 256.

mongodb.overrideStructureByExample

On connecting or refreshing of MongoDB data source, Data Virtuality Server always reads the collections to determine their structure. Sometimes the collections are extremely large, but still need to be read completely, because the latest documents have the latest version of the schema. This operation can take a large amount of time and decrease it we introduced the mongodb.overrideStructureByExample model property. This property can hold a potentially very large text, which represents a strict mode JSON document containing one or multiple comma-separated examples of the structure of MongoDB collections to be overridden. These documents will be read instead of the documents from the real collections to determine their structure.

Creation and usage of the current property explained in detail in the Usage section of this page.

Nested Objects

mongodb.maxRecursionDepth

Nested Objects in a MongoDB's collection row can be represented either as a JSON string contained in the table's column or them as additional fields in the resulting table. This parameter controls, how far inside the particular fields should be analyzed to represent their sub-fields as separate columns in the resulting table. The default value is 0 (output the complete JSON string with no additional columns)

mongodb.fieldDelimiterChar

Additional columns generated for the nested objects have the name of their parent objects as a prefix inside. This parameter controls the character, that is used to separate this prefix from the field name itself. Default is '_'.

Usage

The MongoDB's collections are represented as relational tables in the schema named like the name given in createDatasource()

Select Query

To query these tables a normal SQL select query like the following can be used:

CODE

 select * from mongodb.table1

Stored Procedures

Alternatively, one can use the stored procedure named query to pass a native query inside. This stored procedure has the following parameters:

collection - the name of the collection to query
query - the query in the native JSON format
columns - the output columns in the JSON format like

CODE

 { '_id' : 1, 'name': 1 }

or NULL if all columns should be outputted

result - the CLOB containing the results

CODE

 call "mongodb".query('table1','{"city":"berlin"}',NULL)

XML Functions

In order to parse the JSON string values in a field, XML Functions

jsonToXml
xpathValue
xmlserialize

of the Data Virtuality Server can be used as follows:

Examples

CODE

SELECT xpathvalue( jsontoxml( 'test', a.result), '/test/test/[2]/firstname' ), xmlserialize( jsontoxml( 'test', a.result ) ), result FROM ( CALL "mongodb".query( 'table1', '{"city":"berlin"}', NULL) ) AS a;

CODE

SELECT CAST(xpathvalue(jsontoxml('root',"zipcodes"),'/root/root/[1]') AS string) FROM "mongodb"."zip_history_log";

CODE

SELECT CAST(jsontoxml('root',"zipcodes") AS string) FROM "mongodb"."zip_history_log";

CODE

SELECT * FROM (CALL mongo.query('foo', '{ $query : {},$orderby : {} }','{_id:1,desription:1}')) w, xmltable('/root' passing jsontoxml('root',w.result)) t

Creation and Usage of the JSON Examples with the mongodb.overrideStructureByExample Property

In order to create JSON example for CollectionA in database test containing such documents:

JS

{ "_id" : ObjectId("57ea8fc806f2c1c32bcc4a11"), "col1" : 10 } { "_id" : ObjectId("57ea8fd106f2c1c32bcc4a12"), "col1" : NumberLong(200) }

next steps should be performed:

Document with the desired structure for the whole collection should be obtained using mongoexport CLI tool or constructed manually using strict mode JSON syntax. For CollectionA the second document can be used, as a data type of "col1" is wider, than the first one. To do it with mongoexport one of the next commands can be used:
POWERSHELL
```
mongoexport --db test --collection CollectionA --query "{'col1': {$type: 'long'}}" mongoexport --db test --collection CollectionA --query "{'col1': 200}"
```
Such JSON will be a result:
JS
```
{"_id":{"$oid":"57ea8fd106f2c1c32bcc4a12"},"col1":{"$numberLong":"200"}}
```
Collection name should be added as a key to the JSON document obtained on the previous step:
JS
```
{ "CollectionA": {"_id":{"$oid":"57ea8fd106f2c1c32bcc4a12"},"col1":{"$numberLong":"200"}} }
```

All double quotes should be escaped with the backslash symbol:

JS

{ \"CollectionA\": {\"_id\":{\"$oid\":\"57ea8fd106f2c1c32bcc4a12\"},\"col1\":{\"$numberLong\":\"200\"}} }

Resulting document now can be set as a value to the mongodb.overrideStructureByExample property:

JS

mongodb.overrideStructureByExample=" { \"CollectionA\": {\"_id\":{\"$oid\":\"57ea8fd106f2c1c32bcc4a12\"},\"col1\":{\"$numberLong\":\"200\"}} } "

Multiple examples should be separated by a comma:

JS

mongodb.overrideStructureByExample=" { \"CollectionA\": {\"_id\":{\"$oid\":\"57ea8fd106f2c1c32bcc4a12\"},\"col1\":{\"$numberLong\":\"200\"}}, \"CollectionB\": {\"_id\":{\"$oid\":\"57dfffcf190889d653a5374b\"},\"col2\":{\"$date\":\"2012-12-19T06:01:17.171Z\"}} } "

The example above can be used to add the data source as follows:

SQL

CALL SYSADMIN.createConnection
(
"name" => 'mongodb',
"jbossCLITemplateName" => 'mongodb',
"connectionOrResourceAdapterProperties" => 'server=localhost,port=27017,database=test'
) ;;

CALL SYSADMIN.createDatasource
(
"name" => 'mongodb',
"translator" => 'mongodb',
"modelProperties" => 'mongodb.overrideStructureByExample="
	{
		\"CollectionA\": 
						{\"_id\":{\"$oid\":\"57ea8fd106f2c1c32bcc4a12\"},\"col1\":{\"$numberLong\":\"200\"}},
		\"CollectionB\": 
						{\"_id\":{\"$oid\":\"57dfffcf190889d653a5374b\"},\"col2\":{\"$date\":\"2012-12-19T06:01:17.171Z\"}}
	}
"',
"translatorProperties" => ''
) ;;