Skip to content
Data > Datasource Adapters

MongoDB

Setting up the Adapter

  1. Unzip ami_adapter_mongo.<your_version>.obv.tar.gz and copy the .jar files to amione/lib/.

  2. Go to the config directory ami/amione/config and edit or make a local.properties. Add the lines below or if the ami.datasource.plugins property already exists, add the mongo plugin to the comma deliminated list:

    1
    ami.datasource.plugins=$${ami.datasource.plugins},com.f1.ami.plugins.mongo.AmiMongoDatasourcePlugin

    Note

    $${ami.datasource.plugins} references the existing plugin list. Do not put a space before or after the comma.

  3. Restart AMI

  4. Go to Dashboard->Data Modeler and select Attach Datasource.

  5. Select MongoDB as the Datasource. Give your Datasource a name and configure the URL. The URL should take the following format: 

    1
    URL: server_address:port_number/Your_Database_Name

Querying MongoDB

In the demonstrations below, the URL is set to localhost:27017/test.

The AMI MongoDB adapter can query a MongoDB datasource and output sql tables. The general syntax for querying MongoDB is: 

1
 CREATE TABLE Your_Table_Name AS USE EXECUTE <Your_MongoDB_query>

Collections

In the MongoDB shell, let's create a collection called "customer", and insert some rows into it. 

1
2
db.createCollection("zipcodes");
db.zipcodes.insert({id:01001,city:'AGAWAM', loc:[-72.622,42.070],pop:15338, state:'MA'});

We can then query this table in AMI:

1
CREATE TABLE zips AS USE EXECUTE SELECT (String)_id,(String)city,(String)loc,(Integer)pop,(String)state FROM zipcodes WHERE ${WHERE};

Collections with Nested Columns

Inside the MongoDB shell, we can create a collection named "myAccounts" and insert one row into the collection. 

1
2
db.createCollection("myAccounts");
db.myAccounts.insert({id:1,name:'John Doe', address:{city:'New York City', state:'New York'}});

We can then query this table in AMI:

1
 CREATE TABLE account AS USE EXECUTE SELECT (String)_id,(String)name,(String)address.city, (String)address.state FROM myAccounts WHERE ${WHERE};

Using EVAL methods

Find

Let's use the myAccounts MongoDB collection that we created before and insert some rows into it. Inside MongoDB shell: 

1
2
db.myAccounts.insert({id:1,name:'John Doe', address:{city:'New York City', state:'NY'}});
db.myAccounts.insert({id:2,name:'Jane Doe', address:{city:'New Orleans', state:'LA'}});

If we want to create a sql table from MongoDB that finds all rows whose address state is LA, we can enter the following command in AMI script and hits test: 

1
CREATE TABLE account AS USE EXECUTE SELECT (String)_id,(String)name,(String)address.city, (String)address.state FROM EVAL db.myAccounts.find({'address.state':'LA'}) WHERE ${WHERE};

Limit

1
CREATE TABLE myAccounts AS USE EXECUTE SELECT (String)_id,(Double)id,(String)name,(String)address FROM EVAL db.myAccounts.find().limit(1) WHERE ${WHERE};

Skip

1
CREATE TABLE myAccounts AS USE EXECUTE SELECT (String)_id,(Double)id,(String)name,(String)address FROM EVAL db.myAccounts.find().skip(1) WHERE ${WHERE};

Sort

1
CREATE TABLE myAccounts AS USE EXECUTE SELECT (String)_id,(Double)id,(String)name,(String)address FROM EVAL db.myAccounts.find().sort({name:1}) WHERE ${WHERE};

Projection

1
CREATE TABLE myAccounts AS USE EXECUTE SELECT (String)_id,(Double)id,(String)name,(String)address FROM EVAL db.myAccounts.find().projection({id:0}) WHERE ${WHERE};