Schema Definitions

To enable consistent support for your MongoDB data, you must configure the connector to use a schema definition from a JSON file or the database. You can use the Schema Editor application to create a schema definition and then save it in a JSON file or the database.

Note:

The Schema Editor is not supported on AIX. On all other platforms, information about how to use the Schema Editor can be found in the Schema Editor User Guide located in the installation directory of the connector.

For information about how to use the Schema Editor, see the Schema Editor User Guide located in the installation directory of the connector.

On Windows 7 or earlier, the guide is available from the SimbaMongoDB ODBC Driver program group in the Start menu.
On Windows 8 or later, you can search for the guide on the Start screen.

When the connector connects to a database without a specified schema definition, it automatically generates a temporary schema definition using the settings defined in the Sampling area of the Advanced Options dialog box or the SamplingStrategy, SamplingLimit, and SamplingStepSize properties. However, temporary schema definitions do not persist after the connection is closed, and the connector may generate different schema definitions during subsequent connections to the same database due to variances in the data that gets sampled.

Important:

When creating a schema definition or connecting to the database without specifying one, make sure to configure the connector to sample all the necessary data. Documents that are not sampled do not get included in the schema definition, and consequently do not become available in ODBC applications. If the schema definition was created through the Schema Editor, you can use the Schema Editor to sample additional documents and add them to the schema definition. Note that the Schema Editor is not supported on AIX.

Mapping MongoDB Data

MongoDB is able to store data that follows different rules of data typing and structure compared to traditional relational data, and the tables may contain complex data such as nested arrays or arrays of differently-typed elements. Because traditional ODBC toolsets may not support these data structures, the Simba MongoDB ODBC Connector generates a schema definition that maps the MongoDB data to an ODBC-compatible format.

The Simba MongoDB ODBC Connector does the following when generating a schema definition:

Samples the data in the database in order to detect its structure and determine the data mappings that best support the data.
Assigns a MongoDB data type to each column.
Maps each MongoDB data type to the SQL data type that is best able to represent the greatest number of values.
Renormalizes single-level objects into columns.
For each array and nested object in the database, the connector generates a virtual table to expand the data, and saves these virtual tables as part of the schema definition. For more information about virtual tables, see Virtual Tables.

During this sampling process, the connector defines data types for each column, but does not change the data types of the individual cells in the database. As a result, columns may contain mixed data types. During read operations, values are converted to match the SQL data type of the column so that the connector can work with all the data in the column consistently.