Unlocking Proprietary Data with Postgre SQL Foreign Data

Unlocking Proprietary Data with Postgre. SQL Foreign Data Wrappers Patterson Principal Developer Evangelist ppatterson@salesforce. com @metadaddy

Agenda § Foreign Data Wrappers § Writing FDW’s in C § Multicorn § Database. com FDW for Postgre. SQL § FDW in action

Why Foreign Data Wrappers? § External data sources look like local tables! – Other SQL database • My. SQL, Oracle, SQL Server, etc – No. SQL database • Couch. DB, Redis, etc – File – LDAP – Web services • Twitter!

Why Foreign Data Wrappers? § Make the database do the work – SELECT syntax • DISTINCT, ORDER BY etc – Functions • COUNT(), MIN(), MAX() etc – JOIN external data to internal tables – Use standard apps, libraries for data analysis, reporting

Foreign Data Wrappers § 2003 - SQL Management of External Data (SQL/MED) § 2011 – Postgre. SQL 9. 1 implementation – Read-only – SELECT-clause optimization – WHERE-clause push-down • Minimize data requested from external source § Future Improvements – JOIN push-down • Where two foreign tables are in the same server – Support cursors

FDW’s in Postgre. SQL § ‘Compiled language’ (C) interface § Implement a set of callbacks typedef struct Fdw. Routine { Node. Tag type; /* These functions are required. */ Get. Foreign. Rel. Size_function Get. Foreign. Rel. Size; Get. Foreign. Paths_function Get. Foreign. Paths; Get. Foreign. Plan_function Get. Foreign. Plan; Explain. Foreign. Scan_function Explain. Foreign. Scan; Begin. Foreign. Scan_function Begin. Foreign. Scan; Iterate. Foreign. Scan_function Iterate. Foreign. Scan; Re. Scan. Foreign. Scan_function Re. Scan. Foreign. Scan; End. Foreign. Scan_function End. Foreign. Scan; /* These functions are optional. */ Analyze. Foreign. Table_function Analyze. Foreign. Table; } Fdw. Routine;

FDW’s in Postgre. SQL § Much work! • Couch. DB FDW • https: //github. com/Zheng. Yang/couchdb_fdw/ • couchdb_fdw. c > 1700 Lo. C

Multicorn § http: //multicorn. org/ § Postgre. SQL 9. 1+ extension § Python framework for FDW’s § Implement two methods…

Multicorn from multicorn import Foreign. Data. Wrapper class Constant. Foreign. Data. Wrapper(Foreign. Data. Wrapper): def __init__(self, options, columns): super(Constant. Foreign. Data. Wrapper, self). __init__(options, columns) self. columns = columns def execute(self, quals, columns): for index in range(20): line = {} for column_name in self. columns: line[column_name] = '%s %s' % (column_name, index) yield line

Database. com FDW for Postgre. SQL § OAuth login to Database. com / Force. com – Refresh on token expiry § Force. com REST API – SOQL query • SELECT firstname, lastname FROM Contact § Request thread puts records in Queue, execute() method gets them from Queue § JSON parsing – skip embedded metadat § < 250 lines code

Demo

Conclusion § Foreign Data Wrappers make the whole world look like tables! § Writing FDW’s in C is hard! – Or, at least, time consuming! § Writing FDW’s in Python via Multicorn is easy! – Or, at least, quick! § Try it for yourself!

Resources § http: //wiki. postgresql. org/wiki/SQL/MED § http: //wiki. postgresql. org/wiki/Foreign_data_wrappers § http: //multicorn. org/ § https: //github. com/metadaddy-sfdc/Database. com-FDWfor-Postgre. SQL