Do databases besides Postgres have features comparable to foreign data wrappers?

Question

I'm very excited by several of the more recently-added Postgres features, such as foreign data wrappers. I'm not aware of any other RDBMS having this feature, but before I try to make the case to my main client that they should begin preferring Postgres over their current cocktail of RDBMSs, and include in my case that no other database can do this, I'd like to verify that.

I've been unable to find evidence of any other database supporting SQL/MED, and things like this short note stating that Oracle does not support SQL/MED.

The main thing that gives me doubt is a statement on http://wiki.postgresql.org/wiki/SQL/MED:

SQL/MED is Management of External Data, a part of the SQL standard that deals with how a database management system can integrate data stored outside the database.

If FDWs are based on SQL/MED, and SQL/MED is an open standard, then it seems likely that other RDBMSs have implemented it too.

TL;DR:

Does any database besides Postgres support SQL/MED?

Heerlen

I think SQL Server can do something like that (can't remember the name). And Oracle has "Heterogeneous Services": oracle-base.com/articles/misc/… – Tectrix

I'm pretty sure all commercial databases support methods for accessing data in remote databases. Certainly, SQL Server, Oracle, and Teradata provide this functionality. Once upon a time, ODBC was standardized which made this functionality readily available. (Note: I'm not saying that these products use SQL/MED.) – Mattins

Given that this seems to be so common, I wonder why other DB vendors are not trumpeting this ability the way the Postgres people are. (Maybe they have a better/painless implementation of it??? Or are they just better at marketing?) – Heerlen

I think the point is not if it can or has suport to something like. Truly what is more important is the interfaces, things like "how to make a FDW" and "how to manipulate that data from SQL" are the scope of SQL/MED (or SQL/MED like implementations). – Entasis

One of the flags of the standard is that you could interchange FDW between databases (like, "oh I have created this crazy shit FDW on XSQL, now lets put it to run on MariaDB!"). Ending... Even through SQL/MED is quite old (was made to align with ISO/ANSI SQL:1999) it seems it is not mature enough (as there is only 1 full implemented FDW API, quoting IBM DB2, suggesting it's a overcomplicated API) and has space for changes. – Entasis

Any update on this question? @Heerlen the provided link is not available anymore. – Conk

Answer 1

• IBM DB2 claims compliance with SQL/MED (including full FDW API);
• MySQL's FEDERATED storage engine can connect to another MySQL database, but NOT to other RDBMSs;
• MariaDB's CONNECT engine allows access to various file formats (CSV, XML, Excel, etc), gives access to "any" ODBC data sources (Oracle, DB2, SQLServer, etc) and can access data on the storage engines MyIsam and InnoDB.
• Farrago has some of it too;
• PostgreSQL implements parts of it (notably it does not implement routine mappings, and has a simplified FDW API). It is usable as readeable since PG 9.1 and writeable since 9.3, and prior to that there was the DBI-Link.

PostgreSQL communities have a plenty of nice FDW like noSQL FDW (couchdb_fdw, mongo_fdw, redis_fdw), Multicorn (for using Python output instead of C for the wrapper per se), or the nuts PGStrom (which uses GPU for some operations!)

Entasis

Answer 2

SQL Server has the concept of Linked Servers (http://technet.microsoft.com/en-us/library/ms188279.aspx), which allows you to connect to external data sources (Oracle, other SQL instances, Active Directory, File system data via the Indexing Service provider, etc.) and, if you really needed to, you can create your own Providers that can be used by a SQL Server Linked Server.

Another option within SQL Server is the CLR, in which you can write code to retrieve data from web services or other data sources as needed.

While this may not technically be "SQL/MED", it seems to accomplish the same thing.

Distributed query using local table joined to 4-part linked server query. I think case the remotetable filter might not be applied until after the entire table is pulled local (documentation is fuzzy on this and I've found article with conflicting opinions):

SELECT * 
FROM LocalDB.dbo.table t
INNER JOIN LinkedServer1.RemoteDB.dbo.remotetable r on t.val = r.val
WHERE r.val < 1000
;

Using OpenQuery, remotetable filter is applied on the remote server, as long as the filter is passed into the OpenQuery 2nd parameter:

SELECT * 
FROM LocalDB.dbo.table t
INNER JOIN OPENQUERY(LinkedServer1, 'SELECT * FROM RemoteDB.dbo.remotetable r WHERE r.val < 1000') r on t.val = r.val

Genovevagenre

Thanks! Can you do joins across the foreign data and the local data? And is there something comparable to materialized views, so that it is available locally without the latency involved in querying the foreign db repeatedly? – Heerlen

AFAIK there is not an option to "automagically" materialize linked server tables in SQL Server, and linked server queries will hit the live remote datasource data each time. You would need to write an ETL job to pull the data into a local table if you want a local copy of the data (otherwise how do you know the remote data is not stale?). You can do joins across the local and foreign data, but there are some quirks when accessing the data. Ex: select * from LinkedServer1.dbo.someTable where someColumn = 1 will actually pulls down the ENTIRE remote table BEFORE applying the filter. – Genovevagenre

There is an option to use OPENQUERY with your filter embedded as well, which will always apply the filters remotely, but if you need dynamic parameters with OPENQUERY then it has to be done via dynamic SQL. I will update the answer with an example of using openquery. – Genovevagenre

@Heerlen both SQL Server and PostgreSQL have materialized views, but I think you're missing what they are for. They do not copy data from other tables, they simply can have its data mapped down to the member tables, so you can insert/update/delete data through the view. – Entasis