Document Relational Definition Language

Example

Given documents of the following shape in the collection abc in the database test:

{
    "_id": ObjectId(),
    "close": 7.45,
    "detail": { "a": 2, "b": 3 }
}

Run mongodrdl to generate a schema based on this collection:

mongodrdl -d test -c abc -o schema.drdl

The generated schema file (schema.drdl) looks similar to the following:

schema:
- db: test
  tables:
  - table: abc
    collection: abc
    pipeline: []
    columns:
    - Name: _id
      MongoType: bson.ObjectId
      SqlName: _id
      SqlType: varchar
    - Name: close
      MongoType: float64
      SqlName: close
      SqlType: numeric
    - Name: detail.a
      MongoType: float64
      SqlName: detail.a
      SqlType: numeric
    - Name: detail.b
      MongoType: float64
      SqlName: detail.b
      SqlType: numeric

Example

Consider the following document:

{
    "_id": 1,
    "familyName": "Partridge",
    "hometown" : "Hollywood Hills",
    "address" : { "street": "123 Main Street",
                  "city" : "Hollywood",
                  "state" : "CA",
                  "zip" : "90210" },
    "members_since" : ISODate("2002-04-12T00:00:00Z")
}

Running mongodrdl on a collection containing this document results in the following fields in the generated schema:

Arrays

BI Connector exposes arrays to business intelligence tools using two collections: one without the array, and the other having one document per array element.

Example

If you run mongodrdl on a collection named families which contains the following document:

{
   "_id": 1,
   "familyName": "Partridge",
   "hometown" : "Hollywood Hills",
   "familyMembers" : [
     {
         "firstname" : "Shirley",
         "age" : 42,
         "attributes" : [
             { "name" : "instrument", "value" : "singer" },
             { "name" : "role", "value" : "mom" }
         ]
     },
     {
         "firstname" : "Keith",
         "age" : 18,
         "attributes" : [
             { "name" : "instrument", "value" : "guitar" },
             { "name" : "role", "value" : "son" }
         ]
     },
     {
         "firstname" : "Laurie",
         "age" : 16,
         "attributes" : [
             { "name" : "instrument", "value" : "keyboard" },
             { "name" : "role", "value" : "sister" }
         ]
     }]
}

This results in the following three tables:

families

_id	numeric
familyName	varchar
hometown	varchar

families_familyMembers

_id	numeric
familyMembers.age	numeric
familyMembers.firstname	varchar
familyMembers_idx	numeric

families_familyMembers_attributes

_id	numeric
familyMembers.attributes.name	varchar
familyMembers.attributes.value	varchar
familyMembers.attributes_idx	numeric
familyMembers_idx	numeric

You can join these tables together to view the data in a denormalized format. For example, you can list the people named in the above schema together with their family information using the following query:

SELECT f.*, m.`familyMembers.firstname`
FROM families_familyMembers m
JOIN families f
ON m._id = f._id;

Pre-Joining

If you provide the --preJoined option to mongodrdl, BI Connector adds the fields in the containing document to each array element's document, thus "pre-joining" the table.

In the previous example, the tables would contain the following additional columns:

families_familyMembers

familyName	varchar
hometown	varchar

families_familyMembers_attributes

familyMembers.age	numeric
familyMembers.firstname	varchar
familyMembers_idx	numeric
familyName	varchar
hometown	varchar

Aggregation Pipelines using Views

MongoDB 3.4 introduces Read-Only Views that you can use to filter incompatible data.

For example, you can create a view in the test database that contains only documents containing a number in the grade field of a grades collection:

db.runCommand( { create: "numericGrades", viewOn: "grades", pipeline: [ { "$match": { "grade": { "$type": "number" } } } ] } )

You can then use mongodrdl to generate a schema from this view as you would a collection:

mongodrdl -d test -c numericGrades

Aggregation Pipelines in DRDL

BI Connector can use aggregation pipelines as part of the schema to transform documents from the collection into the proper form for the relational tables.

For example, consider a simple document in a collection named simpleFamilies:

{
   "_id": 1,
   "familyName": "Partridge",
   "familyMembers" : [ "Shirley", "Keith", "Laurie"]
}

mongodrdl generates a schema with the tables simpleFamilies and simpleFamilies_familyMembers.

The table simpleFamilies_familyMembers enumerates each family member and has the following pipeline:

pipeline:
- $unwind:
    includeArrayIndex: familyMembers_idx
    path: $familyMembers

This pipeline uses $unwind to create a new record for each member of familyMembers. The schema tracks the array index in the field familyMembers_idx.

db.points.createIndex( { pos : "2dsphere" } )
db.points.insertOne({
    pos : { type: "Point", coordinates: [ -73.97, 40.77 ] },
    name: "Central Park",
    category : "Parks"
})

schema:
- db: test
  tables:
  - table: points
    collection: points
    pipeline: []
    columns:
    - Name: _id
      MongoType: bson.ObjectId
      SqlName: _id
      SqlType: varchar
    - Name: category
      MongoType: string
      SqlName: category
      SqlType: varchar
    - Name: name
      MongoType: string
      SqlName: name
      SqlType: varchar
    - Name: pos.coordinates
      MongoType: geo.2darray
      SqlName: pos.coordinates
      SqlType: numeric[]