System Variables


BI Connector provides the following environment variables:

System Variable Data Type Description Default Value (Atlas-hosted) Default Value (On Premises)
full_pushdown_exec_mode boolean Specifies whether a query error is returned for queries with clauses that aren’t fully translated to the MongoDB query language. SQL query predicates are used to filter data returned by the query. 0 (false) 0 (false)
log_level integer

Specifies the logging level for BI Connector:

  • -1: Do not log.
  • 0: Log only messages for database users, such as basic mongosqld events and state changes.
  • 1: Log messages for database users and mongosqld administrators.
  • 2: For internal use only.
2 0
max_nested_table_depth integer Specifies the maximum number of unique nested field paths that mongosqld maps to a relational table for a collection. 50 50
max_num_columns_per_table integer The maximum number of unique fields that mongosqld maps to relational columns for a collection. 1000 1000
mongodb_max_varchar_length integer Specifies the maximum string length returned for columns using the VARCHAR data type. A value of 0 specifies no limit. 0 0
polymorphic_type_conversion_mode string

Determines how BI Connector evaluates document fields that are specified with multiple data types. For example, count could be a number in one document and a string in another. The data type that BI Connector selects depends on the value of the schema_mapping_mode system variable. A value of majority causes BI Connector to select the type that appears in the majority of document fields, while a value of lattice would cause BI Connector to select string as the data type for count. See Cached Sampling for more information.

Set polymorphic_type_conversion_mode to one of the following values:

  • off: Queries may fail if you do not explicitly cast document fields that are specified with multiple data types.
  • fast: BI Connector converts only document fields that appeared as multiple data types during sampling.
  • safe: BI Connector converts all document fields to the data type discovered during sampling, even if the fields do not appear as multiple data types.
off off
sample_refresh_interval_secs integer

Specifies how frequently, in seconds, that the BI Connector schema is updated. A value of 0 specifies that the schema is not refreshed after BI Connector starts.

See Cached Sampling for more information.

0 0
sample_size integer

Specifies how many documents BI Connector samples when generating its schema. A value of 0 specifies that BI Connector performs a collection scan across all sample namespaces.

See Cached Sampling for more information.

100 100
schema_mapping_mode string

Specifies how the MongoDB schema is transformed into a relational schema:

  • lattice: See Sampling Type Conflicts for more information.
  • majority: BI Connector assigns the most common data type for a field occuring in a sample.
lattice lattice
type_conversion_mode string

Specifies the semantics that BI Connector uses for type conversions, such as the CAST function.

  • mysql
  • mongosql
mongosql mongosql

Setting System Variables

You must have the Organization Owner role to set environment variables on an Atlas-hosted BI Connector. To set them for an on-premises BI Connector, you must have the inprog privilege on the MongoDB database to which you’ve connected the BI Connector.

Use any SQL client that is connected to BI Connector to set these variables. The following example sets mongodb_max_varchar_length to 100:

SET GLOBAL mongodb_max_varchar_length = 100

You must start a new connection to the BI Connector after setting a system variable. The connection you used to set the system variable is not affected.