Navigation

System Variables

Overview

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_execution_time integer Specifies the maximum length of time an aggregation operation may run. A value of 0 specifies no limit. 0 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_connection_size integer The maximum size of memory in bytes that may be allocated for evaluating any query on any given client connection. A value of 0 specifies no limit. 0 0
mongodb_max_server_size integer The maximum size of memory in bytes that may be allocated for evaluating all queries on BI Connector. A value of 0 specifies no limit. 0 0
mongodb_max_stage_size integer The maximum size of memory in bytes that may be allocated for evaluating any query in any given query evaluation stage. A value of 0 specifies no limit. 0 0
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
mongodb_version_compatibility string For mixed cluster MongoDB installations, the minimum version of any process within the cluster. none none
mongodb_git_version string The git version of MongoDB the BI Connector is connected to. none none
mongodb_topology string The MongoDB cluster topology the BI Connector is connected to. none none
mongodb_version string The MongoDB version the BI Connector is connected to. none none
mongosqld_version string The BI Connector version. none none
optimize_cross_joins boolean If enabled, cross joins are optimized into inner joins when possible. true true
optimize_evaluations boolean If enabled, constant-folding is performed. true true
optimize_filtering boolean If enabled, predicates in WHERE clauses are moved as close as possible to the MongoDB data source they operate on. true true
optimize_inner_joins boolean If enabled, inner joins are reordered for more optimal query execution. true true
optimize_self_joins boolean If enabled, attempt to translate joins between tables with the same underlying MongoDB collection without $lookup. true true
optimize_view_sampling boolean If enabled, the $sample stage is moved ahead of pipeline stages which do not alter cardinality during sampling of a MongoDB view. true true
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
pushdown boolean If enabled, queries are translated to MongoDB’s native aggregation language. true true
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.