Default Mapping of Columns with Inconsistent Native Data Types
Due to the flexibility of the MongoDB schema data model, your native data sources may not enforce consistent data types. To ensure data integrity when mapping to a relational model, the driver describes columns with inconsistent native data types as a single SQL data type. The driver determines which SQL type to use based on the combination of native types detected when sampling data. The following table lists combinations of MongoDB data types and their default mapping for ODBC. If you need to change the ODBC data type, default mappings can be overridden using the Schema Tool. See "Defining Columns" for additional information.
Note: In some cases, a value with a specific native type can be concealed or obscured because the driver describes a field with inconsistent native types as a column with a single SQL type. The CAST_TO_NATIVE function escape allows users to send a value as it is defined in the MongoDB database, rather than how it is described in the relational model of the data. Currently, CAST_TO_NATIVE can only be used with the ObjectID type in SELECT statement filters and literal INSERT values. See "CAST_TO_NATIVE Function Escape" for details.
Table 2. Default Mapping for Columns Containing Inconsistent MongoDB Data Types
1 Whether columns are mapped as either WVARCHAR (-9) or WLONGVARCHAR (-10) depends on the size of the data of the sampled rows and the setting of the defaultVarcharSize configuration option. See "Config Options" for details.