C Python Makefile Other
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Failed to load latest commit information.
mongo-c-driver-v0.6 Initial commit for MongoDB foreign data wrapper Oct 17, 2012
.gitignore
CONTRIBUTING.md Return email to README Jan 28, 2014
LICENSE Update license information Jan 28, 2014
Makefile
README.md Notice Apr 1, 2016
mongo_fdw--1.0.sql Update license information Jan 28, 2014
mongo_fdw.c Update license information Jan 28, 2014
mongo_fdw.control Update license information Jan 28, 2014
mongo_fdw.h Update license information Jan 28, 2014
mongo_query.c Update license information Jan 28, 2014
mongo_query.h Update license information Jan 28, 2014

README.md

Notice: Please note this repository has moved to https://github.com/EnterpriseDB/mongo_fdw

MongoDB FDW for PostgreSQL

This PostgreSQL extension implements a Foreign Data Wrapper (FDW) for MongoDB. For an example demonstrating this wrapper's use, see our blog post. Please also note that this version of mongo_fdw only works with PostgreSQL 9.2 or 9.3.

Installation

The MongoDB FDW 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 (like Linux and OS X), you need to ensure the pg_config executable is in your path when you run make. This executable is typically in your PostgreSQL installation's bin directory. 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. If you run into issues on other systems, please let us know.

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 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.

We also currently use the internal PostgreSQL NAME type to represent the BSON object identifier type (the _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');

-- collect data distribution statistics
ANALYZE customer_reviews;

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. For example, a nested field such as "review": { "Votes": 19 } should be declared as "review.Votes" INTEGER in the CREATE TABLE statement.

  • 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.

Support

Please note this repository has moved to https://github.com/EnterpriseDB/mongo_fdw

License

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.