MongoDB FDW for PostgreSQL 9.2 ============================== 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.2. 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. mongo_fdw can collect data distribution statistics and incorporates them when estimating costs for the query execution plan. In the mean time, you run EXPLAIN on your queries to see the selected execution plans. 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'); -- collect data distribution statistics ANALYZE customer_reviews; Limitations ----------- * If the BSON document key contains upper-case 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. * Please 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. 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.