mongotranslate

Description

mongotranslate is a learning tool designed to help users understand how SQL queries can be expressed in the MongoDB aggregation language. The BI Connector's SQL-to-aggregation translation engine provides the translations, which can also be used to troubleshoot specific BI Connector translation issues.

mongotranslate is a standalone program. It requires a drdl file generated by mongodrdl, but does not require mongosqld or mongod to be running at the time of execution.

Usage

mongotranslate has the following syntax:

mongotranslate [--query | --queryFile] <query | queryfile> [options]

Command Line Options

--query

Specifies a SQL query to translate into a MongoDB aggregation pipeline. Either --query or --queryFile is required.

--queryFile

Specifies a path to a file containing a SQL query to translate into a MongoDB aggregation pipeline. Either --query or --queryFile is required.

--schema

Required. Specifies a .drdl schema file or a directory containing one or more .drdl schema files created by the mongodrdl program to use when translating a SQL query into an aggregation pipeline.

--dbName

Default: test

The database name to use for unqualified table names in the SQL query.

The following example uses a collection named fruit and the --dbName option to specify that fruit is in the groceries database:

mongotranslate "SELECT * FROM fruit WHERE _id > 100;" \
  --schema schema.drdl --dbName groceries

If you do not use the --dbName option to specify a database, mongotranslate assumes that fruit is in the test database. If the schema does not contain a database named test, or a table name fruit in the test database, mongotranslate returns an error.

The following example uses a fully-qualified table name, so it does not need the --dbName option.

mongotranslate "SELECT * FROM groceries.fruit WHERE _id > 100;" \
  --schema schema.drdl

If you specify a database with each table name in your SQL query, the --dbName option is ignored if it is used.

--explain

Optional. Returns the explain output for the query plan instead of the translated aggregation pipeline. Using --explain returns similar output to running EXPLAIN <query> on a BI tool connected to BI Connector.

--format

Default: multiline

Optional. Valid options are none and multiline. The default multiline option displays results in a more easily readable format.

Command Type	Option	Description
Without the --explain option	--format none	Returns an aggregation pipeline all on one line.
Without the --explain option	--format multiline	Returns an aggregation pipeline with one pipeline stage per line.
With the --explain option	--format none	Returns all fields on one line.
With the --explain option	--format multiline	Returns one field per line, with additional formatting for arrays and object subfields.

--mongoversion

Default: latest

Optional. Returns an aggregation pipeline compatible with the specified MongoDB version. Acceptable values include:

• 3.2

• 3.4

• 3.6

• 4.0

• latest

The default value is latest.

Examples

The following example specifies an inline query for translation:

mongotranslate --query=“select test.name from restaurants where name like 'Brooklyn%'” \
--schema=schema.drdl

The above command returns the following results:

[
  {"$match": {"name": {"$regex": "^Brooklyn.*$","$options": "i"}}},
  {"$project": {"test_DOT_restaurants_DOT_name": "$name","_id": {"$numberInt":"0"}}},
]

The following example specifies a query file and returns a one-line result:

mongotranslate --queryFile=query.txt --schema=schema.drdl --format=none

The following example uses the --explain option:

mongotranslate --query="select count(name) from restaurants;" \
--schema=schema.drdl --explain

The above command returns the following results:

[
      {
         "ID": 1,
         "StageType": "MongoSourceStage",
         "Columns": "[{name: 'count(name)', type: 'int'}]",
         "Sources": null,
         "Database": {},
         "Tables": {},
         "Aliases": {},
         "Collections": {},
         "Pipeline": {},
         "PipelineExplain": {},
         "PushdownFailures": null
      }
]