카테고리 없음

PostgreSQL FDW(Foreign Data Wrapper)

남용2 2019. 11. 22. 15:45

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 Licence Code Install Doc Notes
ODBC Native   github PGXN example Does not compile with PostgreSQL >= 9.2!
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 Licence Code Install Doc Notes
PostgreSQL Native PostgreSQL git.postgresql.org   documentation  
Oracle Native PostgreSQL github PGXN website  
MySQL Native   github PGXN example An early version of the Foreign Data Wrapper for MySQL that supports PostgreSQL 9.1 and above:
MySQL Native   github PGXN example An updated FDW for MySQL that support PostgreSQL 9.3 and above
Informix Native PostgreSQL github      
Firebird Native   github PGXN   currently work-in-progress.
SQLite Native   github     An FDW for SQLite3 (read-only)
SQLite Native PostgreSQL github   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 Licence 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  
CouchDB Native PostgreSQL Github PGXN   Original version
CouchDB Native PostgreSQL Github     golgauth version (9.1 - 9.2+ compatible)
GridDB 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 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      

File Wrappers

Data Source Type Licence 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    
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 Licence 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 moreSpatial 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 Licence 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 Licence 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 Licence 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      
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      

Big Data Wrappers

Data Source Type Licence Code Install Doc Notes
Elastic Search Multicorn PostgreSQL GitHub      
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      

Column-Oriented Wrappers

Data Source Type Licence 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 Licence 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 Licence 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

Exotic Wrappers

Data Source Type Licence 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
PGStrom Native GPL 2 GitHub   wiki uses GPU devices to accelarate sequential scan on massive amount of records with complex qualifiers. Now it moved to CustomScan based implementation, so its core logic no longer uses 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

Example Wrappers

Data Source Type Licence 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

Original Link

 

저작자표시 (새창열림)