Foreign data wrappers

转载地址:https://wiki.postgresql.org/index.php?title=Foreign_data_wrappers&spm=a2c6h.12873639.0.0.68097480atbVAh


Foreign data wrappers

Contents

 [hide] 

• 1Foreign Data Wrappers
  • 1.1Generic SQL Database Wrappers
  • 1.2Specific SQL Database Wrappers
  • 1.3NoSQL Database Wrappers
  • 1.4File Wrappers
  • 1.5Geo Wrappers
  • 1.6LDAP Wrappers
  • 1.7Generic Web Wrappers
  • 1.8Specific Web Wrappers
  • 1.9Big Data Wrappers
  • 1.10Column-Oriented Wrappers
  • 1.11Scientific Wrappers
  • 1.12Operating System Wrappers
  • 1.13Exotic Wrappers
  • 1.14Example Wrappers
• 2Writing Foreign Database Wrappers

Foreign Data Wrappers

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.

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 fdw list can be found at the PGXN website.

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!

 

Generic SQL Database Wrappers

Data Source	Type	License	Code	Install	Doc	Notes
ODBC	Native		github			CartoDB took over active development of the ODBC FDW for PG 9.5+
JDBC	Native		github			Not maintained ?
JDBC2	Native		github
SQL_Alchemy	Multicorn	PostgreSQL	GitHub	PGXN	documentation	Can be used to access data stored in any database supported by the sqlalchemy python toolkit.
VirtDB	Native	GPL	GitHub			A generic FDW to access VirtDB data sources (SAP ERP, Oracle RDBMS)

Specific SQL Database Wrappers

Data Source	Type	License	Code	Install	Doc	Notes
PostgreSQL	Native	PostgreSQL	git.postgresql.org		documentation
Oracle	Native	PostgreSQL	github	PGXN	website
MySQL	Native		github	PGXN	example	FDW for MySQL
Informix	Native	PostgreSQL	github
Firebird	Native	PostgreSQL	github	PGXN	README	version 1.1 released (2019-05)
SQLite	Native		github			An FDW for SQLite3 (read-only)
SQLite	Native	PostgreSQL	github	PGXN	README	An FDW for SQLite3 (write support and several pushdown optimization)
Sybase / MS SQL Server	Native		github	PGXN		An FDW for Sybase and Microsoft SQL server
MonetDB	Native		github

NoSQL Database Wrappers

Data Source	Type	License	Code	Install	Doc	Notes
BigTable or HBase	Native Rust Binding (RPGFFI)	MIT	Github
Cassandra	Multicorn	MIT	Github	Rankactive
Cassandra2	Native	MIT	Github
Cassandra	Multicorn	PostgreSQL	Github
ClickHouse	Multicorn	BSD	Github		README
ClickHouse	Native	Apache	Github		README
CouchDB	Native	PostgreSQL	Github	PGXN		Original version
CouchDB	Native	PostgreSQL	Github			golgauth version (9.1 - 9.2+ compatible)
GridDB	Native	PostgreSQL	Github		README
InfluxDB	Native	PostgreSQL	Github		README
Kafka	Native	PostgreSQL	GitHub		README
Kyoto Tycoon	Native	MIT	Github
MongoDB	Native	GPL3+	Github	PGXN	README	EDB version
MongoDB	Multicorn	MIT	Github
MongoDB	Multicorn		Github			Yet Another Postgres FDW for MongoDB
Neo4j	Multicorn	GPLv3	Github		README	FWD for Neo4j and also add a Cypher function to Pg
Neo4j	Native	?	Github
Quasar	Native	Apache	Github
Redis	Native	PostgreSQL	Github
Redis	Native	BSD	Github
RethinkDB	Multicorn	MIT	Github		blog
Riak	Multicorn	PostgreSQL	Github
WhiteDB	Native	MIT	Github
RocksDB	Native	Apache	Github		README	FDW for RocksDB

File Wrappers

Data Source	Type	License	Code	Install	Doc	Notes
CSV	Native	PostgreSQL	git.postgresql.org		documentation	Delivered as an official extension of PostgreSQL 9.1 / example / Another example
CSV	Multicorn	PostgreSQL	GitHub	PGXN	documentation	Each column defined in the table will be mapped, in order, against columns in the CSV file.
CSV / Text Array	Native		GitHub		How to	Another CSV wrapper
CSV / Fixed-length	Native		GitHub
CSV / gzipped	Multicorn		GitHub			PostgreSQL Foreign Data Wrapper for gzipped cvs file
Compressed File	Native		GitHub
Document Collection	Native	PostgreSQL	GitHub		wiki
JSON	Native	GPL3	GitHub		Example
Multi-File	Multicorn	PostgreSQL	GitHub	PGXN	doc	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.
Multi CDR	Native	PostgreSQL	GitHub	PGXN
Parquet	Native	PostgreSQL	GitHub			Foreign data wrapper for reading Parquet files using libarrow/libparquet
pg_dump	Native	New BSD	GitHub			Allows querying of data directly against Postgres custom format files created by pg_dump
TAR Files	Native		GitHub
XML	Multicorn	PostgreSQL	GitHub	PGXN
ZIP Files	Native		GitHub

Geo Wrappers

Data Source	Type	License	Code	Install	Doc	Notes
GDAL/OGR	Native	MIT	GitHub			A wrapper for data sources with a GDAL/OGR driver, including databases like Oracle, Informix, SQLite, SQL Server, ODBC as well as file formats like Shape, FGDB, MapInfo, CSV, Excel, OpenOffice, OpenStreetMap PBF and XML, OGC WebServices, and more Spatial columns are linked in as PostGIS geometry if PostGIS is installed.
Geocode / GeoJSON	Multicorn	GPL	GitHub			a collection of PostGIS-related foreign data wrappers
Open Street Map PBF	Native	PostgreSQL	GitHub

LDAP Wrappers

Data Source	Type	License	Code	Install	Doc	Notes
LDAP	Native		GitHub	PGXN		Allows to query an LDAP server and retrieve data from some pre-configured Organizational Unit
LDAP	Multicorn	PostgreSQL	GitHub	PGXN	documentation

Generic Web Wrappers

Data Source	Type	License	Code	Install	Doc	Notes
Git	Multicorn	PostgreSQL	GitHub	PGXN
Git	Native	MIT	GitHub
ICAL	Multicorn	PostgreSQL	GitHub		pdf
IMAP	Multicorn	PostgreSQL	GitHub	PGXN	documentation
RSS	Multicorn	PostgreSQL	GitHub	PGXN	documentation	This fdw can be used to access items from an rss feed.
www	Native	PostgreSQL	GitHub	PGXN	wiki	Allows to query different web services

Specific Web Wrappers

Data Source	Type	License	Code	Install	Doc	Notes
Database.com	Multicorn	BSD	GitHub
Dun & Badstreet	Multicorn	PostgreSQL	GitHub			Access to the Data Universal Numbering System (DUNS)
DynamoDB	Multicorn	GPL	GitHub
Facebook	Multicorn		GitHub
Fixer.io	based on www_fdw		GitHub
Google	Multicorn	PostgreSQL	GitHub	PGXN
Heroku dataclips	Native	PostgreSQL	GitHub
Keycloak	Multicorn	MIT	GitHub	PGXN	README	Direct database integration with the Keycloak open-source Identity/Access Management solution.
Mailchimp	Multicorn	PostgreSQL	GitHub			Beta
Parse	Multicorn	MIT	GitHub
S3	Native	PostgreSQL	GitHub	PGXN
S3CSV	Multicorn	GPL 3	GitHub			This is meant to replace s3_fdw that does is not supported on PostgreSQL version 9.2+
Telegram	Multicorn	PostgreSQL	GitHub			telegram_fdw is a Telegram BOT implemented using the PostgreSQL foreign data wrapper interface.
Twitter	Native	PostgreSQL	GitHub	PGXN		A wrapper fetching text messages from Twitter over the Internet and returning a table
Treasure Data	Native	Apache	GitHub	PGXN		A FDW for Treasure Data internally using a Rust library
Treasure Data	Multicorn	Apache	GitHub
Google Spreadsheets	Multicorn	MIT	GitHub
Open Weather Map	Multicorn	MIT	GitHub			A FDW for Open Weather Map (single city)

Big Data Wrappers

Data Source	Type	License	Code	Install	Doc	Notes
Elasticsearch	Multicorn	MIT	GitHub			Supports up to PG 12, ES 7.
Google BigQuery	Multicorn	MIT	GitHub		Documentation	bigquery_fdw is a BigQuery FDW compatible with PostgreSQL >= 9.5
file_fdw-gds (Hadoop)	Native		GitHub			Hadoop file_fdw is a slightly modified version of PostgreSQL 9.3's file_fdw module.
Hadoop	Native	PostgreSQL	Bitbucket			Allows read and write access to HBase as well as to HDFS via Hive.
HDFS	Native	Apache	GitHub
Hive	Multicorn		GitHub			Used to access Apache Hive tables.
Hive / ORC File	Native		GitHub
Impala	Native	BSD	GitHub
Apache Arrow	Native	GPLv2	GitHub			A part of PG-Strom feature; as a columnar data source with support of SSD-to-GPU Direct SQL

Column-Oriented Wrappers

Data Source	Type	License	Code	Install	Doc	Notes
Columnar Store	Native		github	example		A Columnar Store for PostgreSQL.
MonetDB	Native		github
GPU Memory Store	Native	GPL v2	github			FDW to GPU device memory; a part of PG-Strom feature for PL/CUDA

Scientific Wrappers

Data Source	Type	License	Code	Install	Doc	Notes
Ambry	Multicorn		GitHub
ROOT files	Native		GitHub			https://root.cern.ch
VCF files (Genotype)	Multicorn		GitHub			https://en.wikipedia.org/wiki/Variant_Call_Format

Operating System Wrappers

Data Source	Type	License	Code	Install	Doc	Notes
Docker	Multicorn	Expat	GitHub
Log files	Multicorn	PostgreSQL	GitHub
OpenStack / Telemetry	Multicorn	PostgreSQL	GitHub
OS Query	Multicorn	PostgreSQL	GitHub			Like Facebook's OSQuery, but for Postgres
Passwd	Native	PostgreSQL	GitHub			reads linux/unix password and group files.
Process	Multicorn	PostgreSQL	GitHub			A foreign datawrapper for querying system stats based on statgrab
Environment Variables	Multicorn	MIT	GitHub			envFDW is a forign data wrapper for processing environment variables

Exotic Wrappers

Data Source	Type	License	Code	Install	Doc	Notes
faker_fdw	Multicorn	PostgreSQL	GitHub			faker_fdw is a foreign data wrapper for PostgreSQL that generates fake data.
fdw_fdw	Multicorn	PostgreSQL	GitHub			the Meta FDW ! reads this page and returns the list of all the FDW
PPG	Native		GitHub			distributed parallel query engine, based on fdw and hooks of PG
Open Civic Data	Multicorn	Expat	GitHub
Phillips Hue Lighting Systems	Multicorn	MIT	GitHub
Random Number	Multicorn	PostgreSQL	GitHub			A random number generator foreign data wrapper for postgres
Rotfang	Multicorn	Native	BitBucket	PostgreSQL	slides	Advanced random number generator
Template Tables	Native	BSD	GitHub			PostgreSQL data wrapper for template tables - any DML and SELECT operations are disallowed
VMware vSphere	Multicorn	MIT	GitHub			A PostgreSQL FDW to query your VMware vSphere service

Example Wrappers

Data Source	Type	License	Code	Install	Doc	Notes
Dummy	Native	BSD	GitHub			Readable null FDW for testing
Hello World			GitHub
Black Hole			bitbucket			a skeleton FDW pre-populated with relevant excerpts from the documentation

Writing Foreign Database Wrappers

• Multicorn is an extension that allows you to write FDWs in Python
• Holycorn is an extension that allows you to write FDWs in Ruby
• Documentation: Writing a Foreign Data Wrapper
• Black Hole FDW - a skeleton FDW pre-populated with relevant excerpts from the documentation
• FDW tutorial by Guillaume Lelarge
• django-fdw A sample project to test django and Postgres Foreign Data Wrapper

Category: 

• Foreign-data wrapper