Autonomous JSON Database with Database API for MongoDB

Oracle had announced Database API for MongoDB which can be used together with Autonomous JSON Database. This post will be with some learnings what I went through when I tested it out.

Official announcement post goes through the setup rather well.

When provisioning the database, remember to select Autonomous JSON database as DB type and for the network select connectivity which allows access from specified IPs and VCNs.

Learnings

Even though I had listed my VCN as allowed source, the connection didn’t get established successfully until I had allowed IP address instead. I had created Oracle Linux VM in a public subnet on my VCN, which I then used to run mongo shell, import and export.

Connection with mongo shell was straightforward, I used the following command to login:

mongosh –tls –tlsAllowInvalidCertificates ‘mongodb://myuser@Y999Y999Y9Y9Y99-MONGODB.adb.us-ashburn-1.oraclecloudapps.com:27016/myuser?authMechanism=PLAIN&authSource=$external&ssl=true’

You can get the connection string from ADB Database Actions, NOTE at this point your ADB needs to be whitelisted and there are instructions in the Oracle announcement how to get that sorted out.

After logging in, I could run normal mongodb commands against my ADB-JSON. Here’s an example to list collections and then how to query some data which I had loaded, loading data is explained below:

mongo> show collections
airbnb
airbnb2
airbnb3
airbnb4
airbnb5
airbnbfromoracle

mongo> db.airbnb.find({_id : 10059872})
[
  {
    _id: '10059872',
    first_review: ISODate("2015-12-19T05:00:00.000Z"),
    last_review: ISODate("2018-03-27T04:00:00.000Z"),
    summary: 'Clean, fully furnish, Spacious 1 bedroom flat just off the escalator in Mid Levels. 2 minutes From Soho Bar and Restaurants. Located in a quiet alley 1 minute from Sun Yat Sen',
    availability: {
      availability_30: 0,
      availability_60: 0,
      availability_90: 0,
      availability_365: 0
    },
....
    access: '',
    cancellation_policy: 'flexible'
  }
]

I edited the output above so it wouldn’t include full contents of the JSON document.

Similar query can be run also from SQL Developer, to see contents of the json_document you would need to run something in lines of SELECT json_serialize(json_document) FROM airbnb;

Importing & Exporting Data

I also wanted to test something else, how easy it would be to load data. I found json datasets from github which I used to test everything out. You can view the datasets from here.

After downloading my airbnb dataset, I tried out importing it with mongoimport to my ADB. Connection string comes up similarly as the mongo shell command.

mongoimport -u=myuser -p=mypass–ssl –tlsInsecure –db=”myuser” -c ”airbnbdata” –host=” Y999Y999Y9Y9Y99-MONGODB.adb.us-ashburn-1.oraclecloudapps.com:27016″ –file “airbnb_data.json” –authenticationMechanism=PLAIN

I defined user, pass and collection (table) with the connection string and defined the file where I would load data from. This was really easy! No additional configuration was needed and all data was loaded without errors to ADB. I actually found this part easier compared what Oracle says in their documentation on loading data to JSON DB. Steps from Oracle are available and if you look them through, you can see you need to figure out some additional things.

I played around with the JSON file to get it bigger, edited the _id column so I could load duplicate records and tried out loading 10GB JSON file via mongoimport. It took around 6 minutes with 16 workers. Since I don’t have anything to compare with this right now, I can’t say if it’s good or bad. Will try out testing this out later, so I could have better statistics overall.

Exporting data can be also done via mongoexport. The syntax is no different from import command:

mongoexport u=myuser -p=mypass–ssl –tlsInsecure –db=”myuser” -c “airbnbdata” –host=” Y999Y999Y9Y9Y99-MONGODB.adb.us-ashburn-1.oraclecloudapps.com:27016″ –out=”export_data.json” –authenticationMechanism=PLAIN

Summary

Using this API was really straightforward, good idea from Oracle to introduce also the connection string available for your database. Helps a lot on figuring out the correct command.

Like I say above, using mongoimport was smooth experience – compared to Oracle tools it felt more straightforward! If you plan on using Autonomous JSON DB, there is now way to use mongo clients to work with data which is great!