ODBC Foreign Data wrapper to query SQL Server on Window - Part 2

As promised in our prior article: ODBC Foreign Data wrapper on windows, we'll demonstrate how to query SQL Server using the Foreign Data Wrapper. This we are testing on windows. As far as querying SQL Server / PostgreSQL goes, the Foreign Data Wrapper still lacks many features that the SQL Server Linked Server approach provides. The key ones we find currently lacking: ability to do updates and reference a table directly from server without knowing underlying structure. That said the Foreign data Wrapper approach has possiblity to support a lot more data sources with ease. We'll demonstrate in subsequent articles using the www_fdw to query web services which we've been playing a lot with and the often packaged in file_fdw. Enough of that let's start with a concrete example.

Warning, this is not production ready, but seems like a very promising start and with more testing can become very robust. Although we are demonstrating odbc_fdw on windows, it is supported on Unix via the UnixODBC, but the data sources you can query will probably be different. I'm really looking forward to how the FDW technology in PostgreSQL will push the envelop. I've been playing around with the www_fdw as well and been impressed how easily it is to query webservices with SQL. A very ah-hah moment.

How to Install ODBC FDW handler and Setup Foreign Server Connection

  1. Copy the odbc_fdw binaries in your PostgreSQL install folder -- for windows we have these for both PostgreSQL 9.1 32-bit and PostgreSQL 9.1 64-bit in our windows FDW packages. PostgreSQL windows 32-bit 9.1 FDWs and PostgreSQL windows 64-bit 9.1 FDWs We'll be adding more fdws to these zip packages as we experiment with new ones.

  2. Install the ODBC FDW in your PostgreSQL with SQL Statement: CREATE EXTENSION odbc_fdw;
  3. Setup a System DSN entry via ODBC manager. If you are running PostgreSQL 32-bit on a windows 64-bit machine, the ODBC manager you want is the one in C:\Windows\SysWOW64\odbcad32.exe. For postgresql 64-bit or postgresql 32-bit on 32-bit windows, its the one in C:\windows\System32\odbcad32.exe. I'm running SQL Server 2008 so I used the SQL Client 10. I named the DSN something meaningful so I know its specifically for postgres e.g. LocalSQL2008ForPg.
  4. Next create a Foreign Server that uses this DSN connection CREATE SERVER localsql2008 FOREIGN DATA WRAPPER odbc_fdw OPTIONS (dsn 'LocalSQL2008ForPg');

  5. Next create a User Mapping that maps a postgres role to a SQL Account. It doesn't need to be a specific postgres user, you can map a whole role group to one SQL Server account and in fact define multiple. Note that the account in the username has to be a valid account on SQL Server and have access to the tables you'll be foreigning.

    CREATE USER MAPPING 
   FOR postgres
   SERVER localsql2008
  OPTIONS (username 'pg_reader',password 'helosqlserver!');

Creating Foreign Tables: The basics

Now we are ready to create tables to query. You can place your Foreign Tables in any postgres schema you want. I like to put ones from the same SQL database in same schema. So for example if I want to query my SuperStore sql server database, I'll create a schema in my postgres database called super_store like shown below. Observe that you don't need to select all columns of a table, you could have a shortened version of your table in postgres that has just the columns you want to select.

CREATE SCHEMA super_store;

Next create the table

CREATE FOREIGN TABLE super_store.orders
   (orderid integer NOT NULL
    , customerid varchar(20) NOT NULL )
   SERVER localsql2008
   OPTIONS (database 'superstore', schema 'dbo', sql_query 'SELECT orderid, customerid
  FROM orders', sql_count 'select count(orderid) from orders');

More challenging table -- information_schema.tables

If I'm going to have a remote server, I'll like to have a catalog of tables. Luckily SQL server has an information_schema.tables similar to postgresql, but the types don't seem to map well and even will CRASH your PostgreSQL server with an Opps! exclamation. work around we found was to do this:

CREATE FOREIGN TABLE super_store.is_tables
(table_catalog character varying(128) ,
    table_schema character varying(128) ,
    table_name character varying(128) ,
    table_type character varying(128) )
   SERVER localsql2005
   OPTIONS (database 'superstore', sql_query 'SELECT CAST(TABLE_CATALOG As varchar(128)) As table_catalog
   , CAST(TABLE_SCHEMA As varchar(128)) As table_schema, CAST(TABLE_NAME As varchar(128)) As table_name
   , CAST(TABLE_TYPE As varchar(128)) As table_type
   FROM information_schema.tables', sql_count 'select count(TABLE_NAME) from information_schema.tables');

Querying the tables

The beauty of the FDW approach is you can query the foreign tables much like you can with other tables. Caution must be taken since the planner doesn't know much about the other side. So for example to pull a listing of views from our super_store.is_tables foreign table.

SELECT table_name, table_type FROM is_tables WHERE table_type = 'VIEW';

Dropping Foreign tables

To drop a foreign table, which it seems you must if you need to change structure, you can do a:

DROP FOREIGN TABLE IF EXISTS super_store.is_tables;

Gotchas

I must stress that this is not a production FDW. So you will have issues, couple we have observed with expermenting with it.