How to Connect Helical Insight with MongoDB

This article shows how to connect Helical Insight Application with a MongoDB datasource.

In Helical Insight version 1.0.1 beta currently we are not providing a UI for directly connecting to MongoDB which we are working upon. Meanwhile MongoDB datasource usage inside Helical Insight is a backend process.

There are 2 file types required for proper configuration of MongoDB datasource with Helical Insight application:

• EFWD file
• JSON file

Step1: Create a new folder in Helical Insight repository with the name “MongoDB Datasource”.
Repository Path (for windows), hence a folder like the one shown below gets created

C:\Helical Insight\hi-repository\MongoDB Datasource

Note: Repository path can vary, which will depend on where the Helical Insight Application is installed.

Step2:  Now, open “MongoDB Datasource” folder.

Step3:Then, create a file with content as shown below and save it as EFWD extension.

<EFWD>
    <DataSources>
      <Connection id="6" name="MongoDB Datasource" type="sql.calcite">
        <model>MongoDB Datasource/mongo-model.json</model>
        <driverName>org.apache.calcite.jdbc.Driver</driverName>
      </Connection>
    </DataSources>
</EFWD>

Step4: After creation of EFWD file, save the file as "MongoDB_datasource.efwd" (any name can be used) in a “MongoDB Datasource” folder present inside the helical repository.

Step5: Now, create a mongo-model.json file, enter JSON code in it and save the file. In a JSON code, there are 2 stages involved:

1. Configuration of MongoDB Credentials :

For the configuration, details required are MongoDB Server (either ip address or localhost is used based on the environment), Port Number, Username and Password which will be used in the JSON file.


Note: If no authentication required to the mongo server then no need to enter username and password details.



Format :

operand: {
host: 'localhost:27017',
database: 'zips',
username: 'hiuser',
password: 'hiuser'
}

or

operand: {
host: 'localhost:27017',
database: 'zips'
}





2. Configuration of tables(views) :

For configuration, details required are database name, table name, type, column datatype and so on. In this case “zips” is used as sample database.



Sample Configuration Details :

{
name: 'mongo',
tables: [
  {
    name: 'category',
    type: 'view',
    sql: 'select cast(_MAP[\'city\'] AS VARCHAR(30)) AS \"city\",\n cast(_MAP[\'loc\'][0] AS float) AS \"longitude\", cast(_MAP[\'loc\'][1] AS float) AS \"latitude\",\n cast(_MAP[\'pop\'] AS INTEGER) AS \"pop\",\n cast(_MAP[\'state\'] AS VARCHAR(10) AS \"state\", cast(_MAP[\'_id\'] AS varchar(5)) AS \"id\"\n from \"mongo_raw\".\"category\"'
  }
 ]
}

‘name’ is the name of the schema. For Example, name: ‘mongo’

Under tables array,

‘name’ is the name of the view. For Example, name: ‘category’

‘type’ is view

‘sql’ is the query to create the view

The syntax

cast(_MAP[\'city\'] AS varchar(20)) AS \"city\"

defines one column in the view.

• In this sample ‘city’ is a string (hence varchar) and an alias ‘category_id’ is given to the column.
• _MAP is the collection

Datatypes: Standard SQL datatypes like BOOLEAN, TIMESTAMP, SMALLINT, DECIMAL, DATE, INTEGER, VARCHAR, FLOAT, REAL etc can be used to define the column types.

Step6: Now, combining both the configuration details, the final JSON code is as shown below :

inline: {
  version: '1.0',
  defaultSchema: 'mongo',
  schemas: [
    {
      type: 'custom',
      name: 'mongo_raw',
      factory: 'org.apache.calcite.adapter.mongodb.MongoSchemaFactory',
      operand: {
             host: 'localhost:27017',
             database: 'zips'
        }
    },
    {
      name: 'mongo',
      tables: [
        {
          name: 'category',
          type: 'view',
          sql: 'select cast(_MAP[\'city\'] AS VARCHAR(30)) AS \"city\",\n cast(_MAP[\'loc\'][0] AS float) AS \"longitude\", cast(_MAP[\'loc\'][1] AS float) AS \"latitude\",\n cast(_MAP[\'pop\'] AS INTEGER) AS \"pop\",\n cast(_MAP[\'state\'] AS VARCHAR(10) AS \"state\", cast(_MAP[\'_id\'] AS varchar(5)) AS \"id\"\nfrom \"mongo_raw\".\"category\"'
        }		
      ]
    }
  ]
}

Step6: Following are the list of file types after configuration.

Step7: Now, login to Helical Insight Application.

Step8: Click on the Metadata Tab.

Step9: Select Datasource Type as Virtual Datasource






















Step10: After selecting datasource type, Mongo datasource would be now available in the list.

Step11: Now, using this datasource, metadata can be created which will be then further utilized for Adhoc report creation.

For further assistance, kindly contact us on support@helicalinsight.com or post your queries at forum.helicalinsight.com