Simple Demo about MongoDB FDW of PostgreSQL

In [34]:

    

import os
import numpy as np
from pymongo import MongoClient
import datetime
from random import randrange
from random import randint
client =  MongoClient("mongodb://mongo:27017")
db = client.testdb
db.add_user('test', 'test', roles=[{'role':'readWrite','db':'testdb'}])
ds = db.dataset
result = ds.delete_many({})
ds.insert_many([{'id': i,
                 'price':20000 + np.random.normal(0, 0.5, 1)[0],
                 'coord_id':randint(1,1000),
                 'time': datetime.datetime.strptime(str(datetime.date(randint(2000,2017), randint(1,12),1)), "%Y-%m-%d")
                } for i in range(1,10**4)]).inserted_ids
ds.count()


    

    
Out[34]:





9999

In [35]:

    

import psycopg2
conn = psycopg2.connect(database="gis", user="mongo", password="mongotest", host="db", port="5432")
cursor = conn.cursor()
cursor.execute("select relname from pg_class where relkind='r' and relname !~ '^(pg_|sql_)';")
print(cursor.fetchall() )


    

    




[('addrfeat',), ('spatial_ref_sys',), ('edges',), ('faces',), ('topology',), ('place_lookup',), ('layer',), ('geocode_settings',), ('geocode_settings_default',), ('direction_lookup',), ('secondary_unit_lookup',), ('state_lookup',), ('street_type_lookup',), ('county_lookup',), ('countysub_lookup',), ('zip_lookup_all',), ('zip_lookup_base',), ('zip_lookup',), ('county',), ('state',), ('place',), ('zip_state',), ('zip_state_loc',), ('cousub',), ('featnames',), ('addr',), ('zcta5',), ('loader_platform',), ('loader_variables',), ('loader_lookuptables',), ('tract',), ('tabblock',), ('bg',), ('pagc_gaz',), ('pagc_lex',), ('pagc_rules',)]

In [36]:

    

cursor.execute("CREATE EXTENSION mongo_fdw;")
cursor.execute("CREATE SERVER mongo_server \
               FOREIGN DATA WRAPPER mongo_fdw \
               OPTIONS (address 'mongo', port '27017');")
cursor.execute("CREATE USER MAPPING FOR postgres SERVER mongo_server OPTIONS (username 'test', password 'test');")
cursor.execute("CREATE FOREIGN TABLE dataset( \
                id int, price real, \
                coord_id int,\
                time timestamptz) SERVER mongo_server\
                OPTIONS (database 'testdb', collection 'dataset');")
    
cursor.execute("SELECT * FROM dataset limit 1;")
print(cursor.fetchall())
conn.close()


    

    





ProgrammingErrorTraceback (most recent call last)
<ipython-input-36-9a3b3aeb41d9> in <module>()
----> 1 cursor.execute("CREATE EXTENSION mongo_fdw;")
      2 cursor.execute("CREATE SERVER mongo_server                FOREIGN DATA WRAPPER mongo_fdw                OPTIONS (address 'mongo', port '27017');")
      3 cursor.execute("CREATE USER MAPPING FOR postgres SERVER mongo_server OPTIONS (username 'test', password 'test');")
      4 cursor.execute("CREATE FOREIGN TABLE dataset(                 id int, price real,                 coord_id int,                time timestamptz) SERVER mongo_server                OPTIONS (database 'testdb', collection 'dataset');")
      5 

ProgrammingError: extension "mongo_fdw" already exists

In [19]:

    

import pprint
pprint.pprint(ds.find_one())


    

    




{u'_id': ObjectId('593d00cad592b5004cf8ae07'),
 u'coord_id': 860,
 u'id': 1,
 u'price': 20000.522469014984,
 u'time': datetime.datetime(2010, 10, 1, 0, 0)}