Use MongoDB Views

Examples

Consider a collection named salaries in the employees database. By creating a view on the salaries collection that excludes all personally identifiable information, BI Connector can safely facilitate data analysis on the view without compromising the private data in the source collection.

The following command starts mongosqld with data from a view named salaries_redacted in the employees database:

mongosqld --sampleNamespaces "employees.salaries_redacted"

Another use case for managing your schema with a MongoDB view is if your working data set includes a sparsely populated but important field which may be excluded in the BI Connector sampling process.

Consider a collection named sparseCollection which contains one field which occurs in every document and another field which occurs only occasionally. A portion of the collection might look like this:

{ "_id" : 0, "mainField": 247 }
{ "_id" : 1, "mainField": 71 }
{ "_id" : 2, "mainField": 981 }
{ "_id" : 3, "mainField": 712 }
{ "_id" : 4, "mainField": 781, "sparseField": 1000 }
{ "_id" : 5, "mainField": 256 }
{ "_id" : 6, "mainField": 910 }

To ensure that sparseField is included when BI Connector creates its schema, create a view in which sparseField is populated with zeroes in every document in which it does not occur.

db.runCommand (
  {
    create: "denseCollection",
    viewOn: "sparseCollection",
    pipeline: [
      {
        $addFields: {
           rareField: { $ifNull: [ "$rareField", 0 ] }
        }
      }
    ]
  }
)

To use the view as a data source when starting mongosqld, specify it with the --sampleNamespaces option.

mongosqld --sampleNamespaces "myDatabase.denseCollection"