English

FDW for MongoDB

1. Prepare the Mongo Environment

MongoDB environment:

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

2. Create FDW in YMatrix

The MongoDB client library must be installed on all hosts in the cluster.

First, create the mongo_fdw extension:

mxadmin=# CREATE EXTENSION mongo_fdw;
CREATE EXTENSION

Create a server definition for MongoDB:

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

Create a user mapping:

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

Create a foreign 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. Read and Write Data Using mongo_fdw

After successfully creating the foreign table, you can directly query and modify data in the MongoDB warehouse collection through the foreign table.

3.1 Query Data

Query data from 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")
}

Query the same data using mongo_fdw 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 Insert Data

Insert data into the foreign table:

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

← Previous

FDW for MySQL

PXF Install