[TOC] > https://github.com/EnterpriseDB/mongo_fdw ## 概述 并且设置外部源后,可以通过sql 的语句来操作 mongodb ## 安装 ### yum 安装 ``` yum install mongo_fdw_15 ``` ### 自动 ``` git clone https://github.com/EnterpriseDB/mongo_fdw cd mongo_fdw autogen.sh --with-master make USE_PGXS=1 make USE_PGXS=1 install ``` ### 手动 下载各个依赖库,并进行安装,详情查看githu官网 ## 教程 ### 开启扩展 ``` CREATE EXTENSION mongo_fdw; ``` ### 创建 mongo server ``` CREATE SERVER mongo_server FOREIGN DATA WRAPPER mongo_fdw OPTIONS (address '127.0.0.1', port '27017'); ``` ### 创建用户映射到 mongo 创建mongo用户 ``` CREATE USER MAPPING FOR postgres SERVER mongo_server OPTIONS (username 'mongo_user', password 'mongo_pass'); ``` ## 示例 需要创建一个带账号密码的mongo库 ``` // 创建一个mongo数据库 ,如demo > use demo > db.createUser({ user: 'db_user', // 用户名 pwd: '123456', // 密码 roles:[{ role: 'root', // 角色 db: 'admin' // 数据库 }] }) ``` > 注意创建用户必须要进入mongo中才能创建 创建 pgsql 表 ``` -- create foreign table CREATE FOREIGN TABLE warehouse ( _id name, warehouse_id int, warehouse_name text, warehouse_created timestamptz ) SERVER mongo_server OPTIONS (database 'db', collection 'warehouse'); -- 注意:第一行必须是 _id ``` 查找 ``` -- select from table 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) db.warehouse.find ( { "warehouse_id" : 1 } ).pretty() { "_id" : ObjectId("53720b1904864dc1f5a571a0"), "warehouse_id" : 1, "warehouse_name" : "UPS", "warehouse_created" : ISODate("2014-12-12T07:12:10Z") } ``` 插入 ``` -- insert row in table INSERT INTO warehouse VALUES (0, 2, 'Laptop', '2015-11-11T08:13:10Z'); -- Note: The given value for "_id" column will be ignored and allows MongoDB to -- insert the unique value for the "_id" column. db.warehouse.insert ( { "warehouse_id" : NumberInt(2), "warehouse_name" : "Laptop", "warehouse_created" : ISODate("2015-11-11T08:13:10Z") } ) ``` 删除 ``` -- delete row from table DELETE FROM warehouse WHERE warehouse_id = 2; db.warehouse.remove ( { "warehouse_id" : 2 } ) ``` 更新 ``` -- update a row of table UPDATE warehouse SET warehouse_name = 'UPS_NEW' WHERE warehouse_id = 1; db.warehouse.update ( { "warehouse_id" : 1 }, { "warehouse_id" : 1, "warehouse_name" : "UPS_NEW", "warehouse_created" : ISODate("2014-12-12T07:12:10Z") } ) ``` 解释 ``` -- explain a table EXPLAIN SELECT * FROM warehouse WHERE warehouse_id = 1; QUERY PLAN ----------------------------------------------------------------- Foreign Scan on warehouse (cost=0.00..0.00 rows=1000 width=84) Filter: (warehouse_id = 1) Foreign Namespace: db.warehouse (3 rows) -- collect data distribution statistics ANALYZE warehouse; ```