FDW for MongoDB

1. Prepare for Mongo environment

MongoDB environment:

Host: 127.0.0.1
Port: 5888
User: mongo_user
Database: mongo_pass

2. Create FDW in YMatrix

First, install the mongo client library on all hosts of the cluster.

First, create the mongo_fdw extension:

mxadmin=# CREATE EXTENSION mongo_fdw;
CREATE EXTENSION

Create a Mongo server definition:

mxadmin=# CREATE SERVER mongo_server FOREIGN DATA WRAPPER mongo_fdw OPTIONS (address '127.0.0.1', port '5888');
CREATE SERVER

Create a user map:

mxadmin=# CREATE USER MAPPING FOR postgres
    SERVER mongo_server
    OPTIONS (username 'mongo_user', password 'mongo_pass');
CREATE USER MAPPING

Create an external table:

mxadmin=# CREATE FOREIGN TABLE warehouse
    (
        _id name,
        warehouse_id int,
        warehouse_name text,
        warehouse_created timestamptz
    )
    SERVER mongo_server
    OPTIONS (database 'db', collection 'warehouse');
CREATE FOREIGN TABLE

3. Use mongo_fdw to read and write data tables

After the external table is successfully created, you can directly read and write the external table to implement the operation of the test table in MongoDB.

3.1 Query data

Query in MongoDB:

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

Use mongo_fdw to query in YMatrix:

mxadmin=# SELECT * FROM warehouse WHERE warehouse_id = 1;
           _id            | warehouse_id | warehouse_name |     warehouse_created
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 53720b1904864dc1f5a571a0 |            1 | UPS            | 2014-12-12 12:42:10+05:30
(1 row)

3.2 Write data

Insert data into external tables:

mxadmin=# INSERT INTO warehouse VALUES (0, 2, 'Laptop', '2015-11-11T08:13:10Z');
INSERT 0 1