BI Connector: Using Customizations - Part 2

The BI Connector has long supported the sharing of schema files (DRDL files - Document Relational Definition Language defines a relational view of a MongoDB schema). But it has previously relied on manually sharing the files between various users and manually managing the different versions needed.

The latest enhancement to the BI Connector removes that manual management issue by storing the DRDL files where they can best be shared with different personas - in the MongoDB database itself.

To understand more about why you would want to customize a schema file, check out our previous article BI Connector: Mapping Document Structures to Relational Schema. At the end of that article we had a bands_flat.drdl file which we ran the BI Connector with. But imagine if we wanted to share that customization. We'd have to track who was using it and make sure they were using the latest version of the customization file.

Wouldn't it be better to keep the customization information in some sort of database, like say, MongoDB? That's exactly what the new BI Connector enhancement does.

Upload/Import DRDL Mapping to MongoDB Collection

Let's take our bands_flat DRDL mapping. We want to load the mapping into MongoDB. DRDL definitions are stored within a collection in MongoDB. To create these schema documents in MongoDB, we use mongodrdl to upload or import DRDL files into a collection. This can be any collection for which the user has write-access.

Uploading a .drdl schema mapping requires two steps.

1. Upload schema mapping to MongoDB
2. Provide schema with unique name

Upload Schema Mapping

First, we upload our DRDL schema mapping:

mongodrdl upload --drdl bands_flat.drdl --schemaSource=bic_schema

NOTE: In this example, we store the uploaded schema in the bic_schema collection.

If successful, this command will return a unique schema ID.

This is what our DRDL definitions looks like as a document in the database:

{ 
    "_id" : ObjectId("5d10ff86287ce3565386ae19"), 
    "created" : ISODate("2019-06-24T16:51:18.397+0000"), 
    "schema" : {
        "databases" : [
            {
                "name" : "sql", 
                "tables" : [
                    {
                        "sql_name" : "bands", 
                        "mongo_name" : "bands", 
                        "pipeline" : "[{\"$unwind\": \"$popular_albums\"},{\"$project\": {\"band\": \"$band\",\"formed\": \"$formation.year\",\"city\": \"$formation.city\",\"popular_album\": \"$popular_albums\"}}]", 
                        "columns" : [
                            {
                                "mongo_name" : "_id", 
                                "mongo_type" : "bson.ObjectId", 
                                "sql_name" : "_id", 
                                "sql_type" : "objectid"
                            }, 
                            {
                                "mongo_name" : "band", 
                                "mongo_type" : "string", 
                                "sql_name" : "band", 
                                "sql_type" : "varchar"
                            }, 
                            {
                                "mongo_name" : "city", 
                                "mongo_type" : "string", 
                                "sql_name" : "city", 
                                "sql_type" : "timestamp"
                            }, 
                            {
                                "mongo_name" : "popular_album", 
                                "mongo_type" : "string", 
                                "sql_name" : "album", 
                                "sql_type" : "varchar"
                            }
                        ]
                    }
                ]
            }
        ]
    }
}

Name an Uploaded Schema

We use this schema ID to give our schema a unique name.

mongodrdl name-schema --name bands_flat --schemaSource=bic_schema --schema <unique ID>

NOTE: If you have misplaced the schema’s unique ID, you can retrieve it using:

mongodrdl list-schema-ids --schemaSource=bic_schema

With the custom bands_flat schema loaded, our SQL clients now see the following table:

mysql> describe bands;
+-------+----------------+------+------+---------+-------+
| Field | Type           | Null | Key  | Default | Extra |
+-------+----------------+------+------+---------+-------+
| _id   | varchar(24)    | YES  | PRI  | NULL    |       |
| album | varchar(65535) | YES  |      | NULL    |       |
| band  | varchar(65535) | YES  |      | NULL    |       |
| city  | datetime       | YES  |      | NULL    |       |
+-------+----------------+------+------+---------+-------+
4 rows in set (0.01 sec)

Starting the BI Connector using a Custom Schema

To use this custom schema with the BI Connector, we specify both custom mode and this schema name: bands_flat.

mongosqld --schemaSource=bic_schemas --schemaName=bands_flat --schemaMode=custom

Conclusion

MongoDB’s BI connector is the best way to generate dashboards using SQL based BI tools for MongoDB without having to perform any ETL operations. Now, with the added functionality of creating the schema files within the database itself helps seamlessly share and modify the schemas within different personas. If you need more help, please check our docs. You can follow these steps to install BI connector on premises. If you have your MongoDB instance running on Atlas, you can connect via the BI connector for Atlas.