mongo_fdw 1.0.0

This Release
mongo_fdw 1.0.0
Date
Status
Stable
Latest Stable
mongo_fdw 2.0.0 —
Other Releases
Abstract
Foreign Data Wrapper for MongoDB
Description
PostgreSQL extension which implements a Foreign Data Wrapper (FDW) for MongoDB.
Released By
citusdata
License
The GNU General Public License, Version 3, June 2007
Resources
Special Files
Tags

Extensions

mongo_fdw 1.0.0
Foreign Data Wrapper for MongoDB

Documentation

README
MongoDB C Driver
HISTORY
MongoDB C Driver History
connections
Connections
building
Building the MongoDB C Driver
tutorial
MongoDB C Driver Tutorial
write_concern
Write Concern (a.k.a. "Safe Mode")
index
MongoDB C Driver Documentation
errors
Error Reporting
bson
BSON

README

Contents

MongoDB FDW for PostgreSQL 9.1
==============================

This PostgreSQL extension implements a Foreign Data Wrapper (FDW) for MongoDB.
For an example setup that demonstrates this wrapper's use, please see
http://www.citusdata.com/blog/51-run-sql-on-mongodb. Please also note that this
version of mongo_fdw only works with PostgreSQL 9.1.


Building
--------

The MongoDB FDW already includes the official MongoDB C Driver version 0.6. When
you type make, the C driver's source code also gets automatically compiled and
linked.

To build on POSIX compliant systems (Linux and OS X), you simply need to include
the pg_config directory path in your make command. This path is typically the
same as your PostgreSQL installation's bin/ directory path. For example:

$ PATH=/usr/local/pgsql/bin/:$PATH make
$ sudo PATH=/usr/local/pgsql/bin/:$PATH make install

Note that we have tested the mongo_fdw extension only on Fedora and Ubuntu
systems. Please let us know if you run into any issues on other systems.


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.

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 foreign
  table name specified in the 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.

We also currently use the internal PostgreSQL NAME type to represent the BSON
object identifier type (_id field).

-- 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 foreign table
CREATE FOREIGN TABLE customer_reviews
(
    _id NAME,
    customer_id TEXT,
    review_date TIMESTAMP,
    review_rating INTEGER,
    product_id CHAR(10),
    product_title TEXT,
    product_group TEXT,
    product_category TEXT,
    similar_product_ids CHAR(10)[]
)
SERVER mongo_server
OPTIONS (database 'test', collection 'customer_reviews');


Limitations
-----------

* mongo_fdw currently can't reach into nested BSON documents. We can overcome
  this limitation by interpreting the period character in column names as a
  separator, and recursing into the BSON document for each separated key name.

* mongo_fdw currently can't access BSON document keys that have upper-case
  letters in them. This is because column names in SQL are case insensitive. We
  can overcome this limitation by having an option for each column name, where
  the option value is case sensitive.

* mongo_fdw only incorporates the document count and average document size when
  estimating costs for the query execution plan. A better estimate also includes
  data distribution statistics; and PostgreSQL 9.2 allows for this. In the mean
  time, you run EXPLAIN on your queries to see the selected execution plans.


Copyright
---------

Copyright (c) 2012 Citus Data, Inc.

This module is free software; you can redistribute it and/or modify it under the
GNU GPL v3.0 License.

For all types of questions and comments about the wrapper, please contact us at
engage @ citusdata.com.