To enable consistent support for your MongoDB data, you must configure the driver 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.
For information about how to use the Schema Editor, see the Schema Editor User Guide located in the installation directory of the driver.
- 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 driver 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
SamplingStepSize properties. However, temporary schema definitions do not
persist after the connection is closed, and the driver may generate different schema definitions
during subsequent connections to the same database due to variances in the data that gets sampled.
When creating a schema definition or connecting to the database without specifying one, make sure to configure the driver 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.
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 Driver generates a schema definition that maps the MongoDB data to an ODBC-compatible format.
The Simba MongoDB ODBC Driver 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 driver 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 driver 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 driver can work with all the data in the column consistently.