mongo數據源 · PHP/Python/前端/Linux 等等學習筆記

[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; ```