https://wiki.postgresql.org/api.php?action=feedcontributions&user=Hadi&feedformat=atomPostgreSQL wiki - User contributions [en]2024-03-29T14:04:46ZUser contributionsMediaWiki 1.35.13https://wiki.postgresql.org/index.php?title=Foreign_data_wrappers&diff=22055Foreign data wrappers2014-04-03T15:37:17Z<p>Hadi: /* Others */ Added cstore_fdw</p>
<hr />
<div>= Foreign Data Wrappers =<br />
In 2003, a new specification called [[SQL/MED]] ("SQL Management of External Data") was added to the SQL standard. It is a standardized way of handling access to remote objects from SQL databases. In 2011, PostgreSQL 9.1 was released with read-only support of this standard, and in 2013 write support was added with PostgreSQL 9.3.<br />
<br />
There are now a variety of Foreign Data Wrappers (FDW) available which enable PostgreSQL Server to different remote data stores, ranging from other SQL databases through to flat file. This page list some of the wrappers currently available. Another [http://pgxn.org/tag/fdw/ fdw list] can be found at [http://pgxn.org/ the PGXN website].<br />
<br />
Please keep in mind that most of these wrappers are '''not officially supported by the PostgreSQL Global Development Group''' (PGDG) and that some of these projects are '''still in Beta''' version. Use carefully!<br />
<br />
== SQL Database Wrappers ==<br />
<br />
===postgres_fdw===<br />
* [http://git.postgresql.org/gitweb/?p=postgresql.git;a=tree;f=contrib/postgres_fdw;hb=HEAD source code]<br />
* [http://www.postgresql.org/docs/current/static/postgres-fdw.html documentation]<br />
<br />
===oracle_fdw===<br />
* [http://oracle-fdw.projects.pgfoundry.org/ project page]<br />
* [https://github.com/laurenz/oracle_fdw source code]<br />
* [http://pgxn.org/dist/oracle_fdw/ install with PGXN]<br />
<br />
===mysql_fdw===<br />
This extension implements a Foreign Data Wrapper for MySQL. It is supported on PostgreSQL 9.1 and above.<br />
* [http://github.com/dpage/mysql_fdw source code]<br />
* [http://pgxn.org/dist/mysql_fdw/ install with PGXN]<br />
* [http://blogs.enterprisedb.com/2011/08/01/postgresql-9-1-meet-mysql/ example]<br />
<br />
===odbc_fdw===<br />
A wrapper for databases with an ODBC driver, including Oracle, DB2, Microsoft SQL Server, Sybase, Pervasive SQL, IBM Lotus Domino, MySQL, PostgreSQL and desktop database products such as FileMaker and Microsoft Access:<br />
* [https://github.com/ZhengYang/odbc_fdw source code]<br />
* [http://pgxn.org/dist/odbc_fdw/ install with PGXN]<br />
* [http://www.postgresonline.com/journal/archives/249-ODBC-Foreign-Data-wrapper-to-query-SQL-Server-on-Window---Part-2.html Using odbc_fdw on windows to query sql server (Postgres OnLine Journal March/April 2012)]<br />
<br />
===jdbc_fdw===<br />
A wrapper for databases with a JDBC driver.<br />
* [https://github.com/atris/JDBC_FDW source code]<br />
<br />
===informix_fdw (WIP)===<br />
* [https://github.com/credativ/informix_fdw source code]<br />
<br />
===firebird_fdw===<br />
An FDW for Firebird; currently work-in-progress.<br />
* [https://github.com/ibarwick/firebird_fdw/ source code]<br />
* [http://pgxn.org/dist/firebird_fdw/ PGXN page]<br />
<br />
===sqlite_fdw===<br />
An FDW for SQLite3 (read-only)<br />
* [https://github.com/gleu/sqlite_fdw source code]<br />
<br />
== NoSQL Database Wrappers ==<br />
<br />
===couchdb_fdw===<br />
A wrapper for [http://couchdb.apache.org/ CouchDB]<br />
* [https://github.com/ZhengYang/couchdb_fdw source code]<br />
* [https://github.com/golgauth/couchdb_fdw source code (9.1 - 9.2+ compatible)]<br />
* [http://pgxn.org/dist/couchdb_fdw/ install with PGXN]<br />
<br />
===MonetDB FDW===<br />
A wrapper for [http://www.monetdb.org/ MonetDB]<br />
*[https://github.com/snaga/monetdb_fdw source code]<br />
<br />
===mongo_fdw===<br />
A wrapper for [http://www.mongodb.org/ MongoDB]<br />
* [https://github.com/citusdata/mongo_fdw source code]<br />
* [http://pgxn.org/dist/mongo_fdw/ install with PGXN]<br />
<br />
===redis_fdw===<br />
A wrapper for [http://redis.io/ Redis]<br />
* [https://github.com/pg-redis-fdw/redis_fdw source code]<br />
* [http://pgxn.org/dist/redis_fdw/ install with PGXN]<br />
<br />
===Neo4j fdw===<br />
A wrapper for Neo4j [http://www.neo4j.org/ Neo4j]<br />
* [https://github.com/nuko-yokohama/neo4j_fdw source code]<br />
<br />
===Tycoon FDW===<br />
A wrapper for [http://fallabs.com/kyototycoon/ Kyoto Tycoon ]<br />
* [https://github.com/cloudflare/kt_fdw Github project]<br />
<br />
== File Wrappers ==<br />
<br />
===file_fdw===<br />
A CSV files wrapper. Delivered as an official extension of PostgreSQL 9.1 <br />
* [http://www.postgresql.org/docs/9.1/static/file-fdw.html documentation]<br />
* [http://www.depesz.com/index.php/2011/03/14/waiting-for-9-1-foreign-data-wrapper/ example]<br />
* [http://www.postgresonline.com/journal/archives/250-File-FDW-Family-Part-1-file_fdw.html Another example] Postgres OnLine Journal May 2012<br />
<br />
===file_text_array_fdw===<br />
Another CSV wrapper<br />
* [https://github.com/adunstan/file_text_array_fdw source code]<br />
* [http://www.postgresonline.com/journal/archives/251-File-FDW-Family-Part-2-file_textarray_fdw-Foreign-Data-Wrapper.html How to use it] Postgres OnLine Journal May 2012<br />
<br />
===file_fixed_length_record_fdw===<br />
Fixed-width flat file wrapper<br />
* [https://github.com/adunstan/file_fixed_length_record_fdw source code]<br />
<br />
===json_fdw===<br />
A wrapper for JSON files.<br />
* [https://github.com/citusdata/json_fdw source code]<br />
* [http://citusdata.com/blog/65-run-sql-on-json-files-without-any-data-loads example]<br />
<br />
== Others ==<br />
<br />
===twitter_fdw===<br />
A wrapper fetching text messages from Twitter over the Internet and returning a table<br />
* [https://github.com/umitanuki/twitter_fdw source code]<br />
* [http://pgxn.org/dist/twitter_fdw/ install with PGXN]<br />
<br />
===ldap_fdw===<br />
Allows PostgreSQL to query an LDAP server and retrieve data from some pre-configured Organizational Unit<br />
* [https://github.com/guedes/ldap_fdw source code]<br />
* [http://pgxn.org/dist/ldap_fdw/ install with PGXN]<br />
<br />
===PGStrom===<br />
<br />
uses GPU devices to accelarate sequential scan on massive amount of records with complex qualifiers.<br />
* http://wiki.postgresql.org/wiki/PGStrom<br />
<br />
===Hadoop FDW===<br />
<br />
Allows read and write access to HBase as well as to HDFS via Hive.<br />
* [https://bitbucket.org/openscg/hadoop_fdw source code]<br />
<br />
===s3_fdw===<br />
Reads files located in Amazon S3 <br />
* [https://github.com/umitanuki/s3_fdw source code]<br />
* [http://pgxn.org/dist/s3_fdw/ install with PGXN]<br />
<br />
===www_fdw===<br />
Allows to query different web services:<br />
* [https://github.com/cyga/www_fdw/ source code]<br />
* [https://github.com/cyga/www_fdw/wiki documentation/examples]<br />
* [http://pgxn.org/dist/www_fdw/ install with PGXN]<br />
<br />
===cstore_fdw===<br />
A Columnar Store for PostgreSQL.<br />
* [https://github.com/citusdata/cstore_fdw source code]<br />
* [http://citusdata.com/blog/76-postgresql-columnar-store-for-analytics example]<br />
<br />
=Multicorn Foreign Data Wrappers=<br />
<br />
[http://multicorn.org/ Multicorn] is an extension that allows FDWs to be written in Python<br />
<br />
<br />
== SQL Database Wrappers ==<br />
<br />
===multicorn.sqlalchemyfdw===<br />
This fdw can be used to access data stored in any database supported by the sqlalchemy python toolkit.<br />
* [http://multicorn.org/foreign-data-wrappers/#sqlalchemy-foreign-data-wrapper documentation]<br />
<br />
== File Wrappers ==<br />
<br />
===muticorn.fsfdw===<br />
This fdw can be used to access data stored in various files, in a filesystem. The files are looked up based on a pattern, and parts of the file's path are mapped to various columns, as well as the file's content itself.<br />
* [http://multicorn.org/foreign-data-wrappers/#filesystem-foreign-data-wrapper documentation]<br />
<br />
===multicorn.csvfdw===<br />
This fdw can be used to access data stored in CSV files. Each column defined in the table will be mapped, in order, against columns in the CSV file.<br />
* [http://multicorn.org/foreign-data-wrappers/#csv-foreign-data-wrapper documentation]<br />
<br />
<br />
== Others ==<br />
<br />
===multicorn.rssfdw===<br />
This fdw can be used to access items from an rss feed. <br />
* [http://multicorn.org/foreign-data-wrappers/#rss-foreign-data-wrapper documentation]<br />
<br />
===multicorn.hivefdw===<br />
Used to access Apache Hive tables.<br />
* [https://github.com/youngwookim/hive-fdw-for-postgresql source code]<br />
[[Category:Foreign-data wrapper|!]]<br />
<br />
<br />
=Writing Foreign Database Wrappers=<br />
<br />
* [http://www.postgresql.org/docs/current/interactive/fdwhandler.html Documentation: Writing a Foreign Data Wrapper]<br />
* [https://bitbucket.org/adunstan/blackhole_fdw Black Hole FDW] - a skeleton FDW pre-populated with relevant excerpts from the documentation<br />
* [http://blog.guillaume.lelarge.info/index.php/post/2013/06/25/The-handler-and-the-validator-functions-of-a-FDW FDW tutorial by Guillaume Lelarge]</div>Hadihttps://wiki.postgresql.org/index.php?title=Foreign_data_wrappers&diff=20019Foreign data wrappers2013-05-31T15:57:02Z<p>Hadi: /* File Wrappers */</p>
<hr />
<div>= Foreign Data Wrappers =<br />
In 2003, a new extension called [[SQL/MED]] ("SQL Management of External Data") was added to the SQL standard. It is a standardized way of handling access to remote objects in SQL databases. In 2011, PostgreSQL 9.1 was released with a great support of this standard.<br />
<br />
In a nutshell, you can now use various Foreign Data Wrappers (FDW) to connect a PostgreSQL Server to remote data stores. This page is an incomplete list of the Wrappers available right now. Another [http://pgxn.org/tag/fdw/ fdw list] can be found at [http://pgxn.org/ the PGXN website].<br />
<br />
The information about how to write a FDW can be found [http://www.postgresql.org/docs/9.2/static/fdwhandler.html here].<br />
<br />
Please keep in mind that most of these wrappers are '''not officially supported by the PostgreSQL Global Development Group''' (PGDG) and that some of these projects are '''still in Beta''' version. Use carefully!<br />
<br />
<br />
== SQL Databases Wrappers ==<br />
<br />
===oracle_fdw===<br />
* [http://pgfoundry.org/projects/oracle-fdw/ source code]<br />
* [http://pgxn.org/dist/oracle_fdw/ install with PGXN]<br />
<br />
===mysql_fdw===<br />
This extension implements a Foreign Data Wrapper for MySQL. It is supported on PostgreSQL 9.1 and above.<br />
* [http://github.com/dpage/mysql_fdw source code]<br />
* [http://pgxn.org/dist/mysql_fdw/ install with PGXN]<br />
* [http://blogs.enterprisedb.com/2011/08/01/postgresql-9-1-meet-mysql/ example]<br />
<br />
===tds_fdw(not working)===<br />
This project is not maintained anymore. Consider using odbc_fdw below to access Microsoft SQL Server and derived.<br />
I guess you can meanwhile do the reverse, access Postgresql database within Microsoft SQL Server using Linked Servers.<br />
<br />
===odbc_fdw===<br />
A wrapper for databases with an ODBC driver, including Oracle, DB2, Microsoft SQL Server, Sybase, Pervasive SQL, IBM Lotus Domino, MySQL, PostgreSQL and desktop database products such as FileMaker and Microsoft Access:<br />
* [https://github.com/ZhengYang/odbc_fdw source code]<br />
* [http://pgxn.org/dist/odbc_fdw/ install with PGXN]<br />
* [http://www.postgresonline.com/journal/archives/249-ODBC-Foreign-Data-wrapper-to-query-SQL-Server-on-Window---Part-2.html Using odbc_fdw on windows to query sql server (Postgres OnLine Journal March/April 2012)]<br />
<br />
===jdbc_fdw===<br />
A wrapper for databases with a JDBC driver.<br />
* [https://github.com/atris/JDBC_FDW source code]<br />
<br />
===informix_fdw (WIP)===<br />
* [https://github.com/credativ/informix_fdw source code]<br />
<br />
== NoSQL Databases Wrappers ==<br />
<br />
===couchdb_fdw===<br />
A wrapper for [http://couchdb.apache.org/ CouchDB]<br />
* [https://github.com/ZhengYang/couchdb_fdw source code]<br />
* [http://pgxn.org/dist/couchdb_fdw/ install with PGXN]<br />
<br />
===mongo_fdw===<br />
A wrapper for [http://www.mongodb.org/ MongoDB]<br />
* [https://github.com/citusdata/mongo_fdw source code]<br />
* [http://pgxn.org/dist/mongo_fdw/ install with PGXN]<br />
<br />
===redis_fdw===<br />
A wrapper for [http://redis.io/ Redis]<br />
* [https://github.com/dpage/redis_fdw source code]<br />
* [http://pgxn.org/dist/redis_fdw/ install with PGXN]<br />
<br />
===Neo4j fdw===<br />
A wrapper for Neo4j [http://www.neo4j.org/ Neo4j]<br />
* [https://github.com/nuko-yokohama/neo4j_fdw source code]<br />
<br />
== File Wrappers ==<br />
<br />
===file_fdw===<br />
A CSV files wrapper. Delivered as an official extension of PostgreSQL 9.1 <br />
* [http://www.postgresql.org/docs/9.1/static/file-fdw.html documentation]<br />
* [http://www.depesz.com/index.php/2011/03/14/waiting-for-9-1-foreign-data-wrapper/ example]<br />
* [http://www.postgresonline.com/journal/archives/250-File-FDW-Family-Part-1-file_fdw.html Another example] Postgres OnLine Journal May 2012<br />
<br />
===file_text_array_fdw===<br />
Another CSV wrapper<br />
* [https://github.com/adunstan/file_text_array_fdw source code]<br />
* [http://www.postgresonline.com/journal/archives/251-File-FDW-Family-Part-2-file_textarray_fdw-Foreign-Data-Wrapper.html How to use it] Postgres OnLine Journal May 2012<br />
<br />
===file_fixed_length_record_fdw===<br />
Fixed-width flat file wrapper<br />
* [https://github.com/adunstan/file_fixed_length_record_fdw source code]<br />
<br />
===json_fdw===<br />
A wrapper for JSON files.<br />
* [https://github.com/citusdata/json_fdw source code]<br />
* [http://citusdata.com/blog/65-run-sql-on-json-files-without-any-data-loads example]<br />
<br />
== Others ==<br />
<br />
===twitter_fdw===<br />
A wrapper fetching text messages from Twitter over the Internet and returning a table<br />
* [https://github.com/umitanuki/twitter_fdw source code]<br />
* [http://pgxn.org/dist/twitter_fdw/ install with PGXN]<br />
<br />
===ldap_fdw===<br />
Allows PostgreSQL to query an LDAP server and retrieve data from some pre-configured Organizational Unit<br />
* [https://github.com/guedes/ldap_fdw source code]<br />
* [http://pgxn.org/dist/ldap_fdw/ install with PGXN]<br />
<br />
===PGStrom===<br />
<br />
uses GPU devices to accelarate sequential scan on massive amount of records with complex qualifiers.<br />
* http://wiki.postgresql.org/wiki/PGStrom<br />
<br />
<br />
===s3_fdw===<br />
Reads files located in Amazon S3 <br />
* [https://github.com/umitanuki/s3_fdw source code]<br />
* [http://pgxn.org/dist/s3_fdw/ install with PGXN]<br />
<br />
===www_fdw===<br />
Allows to query different web services:<br />
* [https://github.com/cyga/www_fdw/ source code]<br />
* [https://github.com/cyga/www_fdw/wiki documentation/examples]<br />
* [http://pgxn.org/dist/www_fdw/ install with PGXN]<br />
<br />
=Multicorn Foreign Data Wrappers=<br />
<br />
[http://multicorn.org/ Multicorn] is an extension that allows FDWs to be written in Python<br />
<br />
<br />
== SQL Database Wrappers ==<br />
<br />
===multicorn.sqlalchemyfdw===<br />
This fdw can be used to access data stored in any database supported by the sqlalchemy python toolkit.<br />
* [http://multicorn.org/foreign-data-wrappers/#sqlalchemy-foreign-data-wrapper documentation]<br />
<br />
== File Wrappers ==<br />
<br />
===muticorn.fsfdw===<br />
This fdw can be used to access data stored in various files, in a filesystem. The files are looked up based on a pattern, and parts of the file's path are mapped to various columns, as well as the file's content itself.<br />
* [http://multicorn.org/foreign-data-wrappers/#filesystem-foreign-data-wrapper documentation]<br />
<br />
===multicorn.csvfdw===<br />
This fdw can be used to access data stored in CSV files. Each column defined in the table will be mapped, in order, against columns in the CSV file.<br />
* [http://multicorn.org/foreign-data-wrappers/#csv-foreign-data-wrapper documentation]<br />
<br />
<br />
== Others ==<br />
<br />
===multicorn.rssfdw===<br />
This fdw can be used to access items from an rss feed. <br />
* [http://multicorn.org/foreign-data-wrappers/#rss-foreign-data-wrapper documentation]<br />
<br />
===multicorn.hivefdw===<br />
Used to access Apache Hive tables.<br />
* [https://github.com/youngwookim/hive-fdw-for-postgresql source code]<br />
[[Category:Foreign-data wrapper|!]]</div>Hadi