PostgreSQL foreign data wrapper for MongoDB
C Shell Makefile
#60 Compare This branch is 103 commits ahead, 3 commits behind citusdata:master.
Latest commit 4b2947b May 23, 2016 @ibrarahmad ibrarahmad README update
Permalink
Failed to load latest commit information.
data Error - (#23) Regression test cases added Feb 25, 2015
expected Fix PostgreSQL 9.5 build and remove some compilation warnings. Jan 18, 2016
mongo-c-driver @ 2929c2d Fix a crash while fetching remote row count using MongoDB's Meta C Dr… Feb 25, 2015
sql Error ((#38) Nested field has column type 0, so we Oct 20, 2015
.gitignore Added basic pgregress tests. May 25, 2014
.gitmodules write JSON (object and array) Nov 7, 2014
CONTRIBUTING.md Update CONTRIBUTING.md Jun 24, 2014
LICENSE Update license information Jan 28, 2014
Makefile Error - (#23) Test case update Feb 25, 2015
Makefile.legacy Error - (#23) Regression test cases for Meta Driver (Makefile change). Feb 25, 2015
Makefile.meta Error - (#10) Adding JSON support while using Mongo Meta driver. Aug 25, 2015
README.md README update May 23, 2016
autogen.sh New Mongo'C Driver (mongo-c-driver-1.3.1.tar.gz) support in autogen.s… Jan 28, 2016
connection.c Merge remote-tracking branch 'upstream/master' Nov 4, 2014
json_compilation_error.patch JSON-C compilation error patch. Feb 26, 2015
mongo_fdw--1.0.sql Licence information update Jun 2, 2014
mongo_fdw.c Error (#32) Disable INSERT and UPDATE on __doc system column. Jan 29, 2016
mongo_fdw.control Licence information update Jun 2, 2014
mongo_fdw.h fixing some compile errors Nov 4, 2014
mongo_query.c Fix a crash while fetching remote row count using MongoDB's Meta C Dr… Feb 25, 2015
mongo_query.h License header change Sep 30, 2014
mongo_wrapper.c Error (#32) full document retrieval. Jan 28, 2016
mongo_wrapper.h Error (#32) full document retrieval. Jan 28, 2016
mongo_wrapper_meta.c Error (#32) full document retrieval. Jan 28, 2016
option.c fixing some compile errors Nov 4, 2014

README.md

MongoDB Foreign Data Wrapper for PostgreSQL

This MongoDB extension implements the PostgreSQL's Foreign Data Wrapper.

Please note that this version of mongo_fdw works with PostgreSQL and EDB Postgres Advanced Server 9.3, 9.4 and 9.5. Work is underway for certification with 9.6Beta.

Installation

The MongoDB FDW depends on the official MongoDB C Driver version 0.8 and includes it as a git submodule. If you are cloning this repository for the first time, be sure to pass the --recursive option to git clone in order to initialize the driver submodule to a useable state.

If checked out this project before and for some reason your submodule is not up-to-date, run git submodule update --init.

When you type make, the C driver's source code also gets automaticallycompiled and linked.

Note: Make sure you have permission to "/usr/local" (default installation location) folder.

Note that we have verified the mongo_fdw extension only on MacOS X, Fedora and Ubuntu systems. If you run into issues on other systems, please let us know

Enhancements

The following enhancements are added to the latest version of mongo_fdw

Write-able FDW

The previous version was only read-only, the latest version provides the write capability. The user can now issue an insert / update and delete statements for the foreign tables using the mongo_fdw.

Connection Pooling

The latest version comes with a connection pooler that utilizes the same mango database connection for all the queries in the same session. The previous version would open a new MongoDB connection for every query. This is a performance enhancement.

New MongoDB C Driver Support

The third enhancement is to add a new MongoDB' C driver. The current implementation is based on the legacy driver of MongoDB. But MongoDB is provided completely new library for driver called MongoDB's Meta Driver. So I have added support of that driver. Now compile time option is available to use legacy and Meta driver. I am sure there are many other benefits of the new Mongo-C-driver that we are not leveraging but we will adopt those as we learn more about the new C driver.

In order to use MongoDB driver 1.0.0+, take the following steps:

  • clone libbson version 1.0.0+ (https://github.com/mongodb/libbson). Follow install directions on that project's README.
  • clone libmongoc version 1.0.0+ (https://github.com/mongodb/mongo-c-driver). Follow the install directions, except make sure to also run ./configure --with-libbson=system after running automake but before running make. This should be the default behavior, but to be certain include this step.
  • ensure pkg-config / pkgconf is installed on your system.
  • run make -f Makefile.meta && make -f Makefile.meta install
  • if you get an error when trying to CREATE EXTENSION mongo_fdw;, then try running ldconfig

Compilation script

Number of manual steps needs to be performed to compile and install different type of MongoDB drivers and supported libraries. If you want to avoid the manual steps, there is a shell script available which will download and install the appropriate drivers and libraries for you.

Here is how it works :

Build with MongoDB's legacy branch driver

  • autogen.sh --with-legacy

Build MongoDB's master branch driver

  • autogen.sh --with-master

The script will do all the necessary steps to build with legacy and metadriver accordingly.

Usage

The following parameters can be set on a MongoDB foreign server object:

  • address: the address or hostname of the MongoDB server Defaults to 127.0.0.1
  • port: the port number of the MongoDB server. Defaults to 27017
  • read_preference: primary [default], secondary, primaryPreferred, secondaryPreferred, or nearest (meta driver only). Defaults to `primary'

The following parameters can be set on a MongoDB foreign table object:

  • database: the name of the MongoDB database to query. Defaults to test
  • collection: the name of the MongoDB collection to query. Defaults to the foreign table name used in the relevant CREATE command

As an example, the following commands demonstrate loading the mongo_fdw wrapper, creating a server, and then creating a foreign table associated with a MongoDB collection. The commands also show specifying option values in the OPTIONS clause. If an option value isn't provided, the wrapper uses the default value mentioned above.

mongo_fdw can collect data distribution statistics will incorporate them when estimating costs for the query execution plan. To see selected execution plans for a query, just run EXPLAIN.

Examples with MongoDB's equivalent statments.

-- load extension first time after install
CREATE EXTENSION mongo_fdw;

-- create server object
CREATE SERVER mongo_server
         FOREIGN DATA WRAPPER mongo_fdw
         OPTIONS (address '127.0.0.1', port '27017');

-- create user mapping
CREATE USER MAPPING FOR postgres
         SERVER mongo_server
         OPTIONS (username 'mongo_user', password 'mongo_pass');

-- create foreign table
CREATE FOREIGN TABLE warehouse(
         _id NAME,
         warehouse_id int,
         warehouse_name text,
         warehouse_created timestamptz)
SERVER mongo_server
         OPTIONS (database 'db', collection 'warehouse');

-- Note: first column of the table must be "_id" of type "NAME".

-- select from table
SELECT * FROM warehouse WHERE warehouse_id = 1;

           _id          | warehouse_id | warehouse_name |     warehouse_created
------------------------+----------------+---------------------------
53720b1904864dc1f5a571a0|            1 | UPS            | 12-DEC-14 12:12:10 +05:00


db.warehouse.find({"warehouse_id" : 1}).pretty()
{
    "_id" : ObjectId("53720b1904864dc1f5a571a0"),
    "warehouse_id" : 1,
    "warehouse_name" : "UPS",
    "warehouse_created" : ISODate("2014-12-12T07:12:10Z")
}


-- insert row in table
INSERT INTO warehouse values (0, 1, 'UPS', '2014-12-12T07:12:10Z');

db.warehouse.insert
(
    {
        "warehouse_id" : NumberInt(1),
        "warehouse_name" : "UPS",
        "warehouse_created" : ISODate("2014-12-12T07:12:10Z")
    }
);

-- delete row from table
DELETE FROM warehouse where warehouse_id = 3;

>    db.warehouse.remove({"warehouse_id" : 2})


-- update a row of table
UPDATE warehouse set warehouse_name = 'UPS_NEW' where warehouse_id = 1;

db.warehouse.update
(
   {
        "warehouse_id" : 1
   },
   {
        "warehouse_id" : 1,
        "warehouse_name" : "UPS_NEW"
   }
)

-- explain a table
EXPLAIN SELECT * FROM warehouse WHERE warehouse_id = 1;
                           QUERY PLAN
 -----------------------------------------------------------------
 Foreign Scan on warehouse  (cost=0.00..0.00 rows=1000 width=44)
   Filter: (warehouse_id = 1)
   Foreign Namespace: db.warehouse
 Planning time: 0.671 ms
(4 rows)

-- collect data distribution statistics`
ANALYZE warehouse;

Limitations

  • If the BSON document key contains uppercase letters or occurs within a nested document, mongo_fdw requires the corresponding column names to be declared in double quotes.

  • Note that PostgreSQL limits column names to 63 characters by default. If you need column names that are longer, you can increase the NAMEDATALEN constant in src/include/pg_config_manual.h, compile, and reinstall.

Contributing

Have a fix for a bug or an idea for a great new feature? Great! Check out the contribution guidelines here. For all other types of questions or comments about the wrapper please contact us at mongo_fdw @ enterprisedb.com.

Support

This project will be modified to maintain compatibility with new PostgreSQL and EDB Postgres Advanced Server releases.

If you need commercial support, please contact the EnterpriseDB sales team, or check whether your existing PostgreSQL support provider can also support mongo_fdw.

License

Portions Copyright © 2004-2016, EnterpriseDB Corporation.

Portions Copyright © 2012–2014 Citus Data, Inc.

This program is free software: you can redistribute it and/or modify it under the terms of the GNU Lesser General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.

See the LICENSE file for full details.