Docs Menu
Docs Home
/
BI Connector
/
Reference

System Variables

On this page

  • Overview
  • Setting System Variables

Overview

BI Connector provides the following environment variables:

System Variable
Data Type
Description
Default Value
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)
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 (Atlas-hosted) and 0 (self-managed)
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
max_num_global_tables
integer
The maximum number of tables allowed in a MongoDB deployment with an automatically- generated schema.
4000
max_num_tables_per_collection
integer
The maximum number of tables allowed in a collection in a table with an automatically-generated schema.
200
max_nested_table_depth
integer
The default limit for table's nesting depth of arrays that MongoDB translates to tables, in a collection with an automatically-generated schema.
10
max_num_fields_per_collection
integer
The maximum number of fields examined per collection in a table with an automatically-generated schema.
2000
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
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
reconcile_arithmetic_agg_functions
boolean

Allows you to choose the behavior where MongoDB does not automatically convert types for some queries using arithmetic aggregate functions. In MongoDB version 3.6 and earlier, this behavior results in improved performance, compared with implicit type conversions for arithmetic aggregation functions in subsequent versions.

See Type Conversion Modes for more information.

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
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
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
type_conversion_mode
string

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

  • mysql

  • mongosql

mongosql

Setting System Variables

You must have the atlasAdmin database role to set environment variables on an Atlas-hosted BI Connector. To set them for a self-managed 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.

Back

User Authorization Model