0

In my PostgreSQL/PostGIS I need to create FDW to an instance of Teradata Database. My aim is to get attributic-nonspatial data from the Teradata. I opted to use OGR_FDW instead of ODBC_FDW as this article recommends OGR_FDW as the performance is generally better, and the fact that I am using PostGIS in my PostgreSQL DB.

In order to create the FDW with OGR_FDW I have to do several steps that include CREATE SERVER, CREATE USER MAPPING, and IMPORT FOREIGN SCHEMA. Unfortunately I can not find example specifically for Teradata ODBC. I need to now what are the accepted OPTIONS values for Teradata ODBC.

So then I read several reference: (1) by Paul Ramsey and (2) by Leo Hsu & Regina Obe .

The one from Paul Ramsey states that we can use command ogr_fdw_info to generate server and table definition, but I do not understand what is the datasource used in the ogr_fdw_info call?

I have the config for the Teradata in my PostgreSQL server. As the PostgreSQL server is with Windows Server i made it in the ODBC Data Source Administrator 64-bit, as system Data Sources:

(gambaR)

And subsequent connection test was a success:

enter image description here

However, when I tried to use this tdprod in the ogr_fdw_info call, but the ogr_fdw_info does not recognize it:

enter image description here

I tried to create th remote server anyway, but failed:

enter image description here

Can anyone :

  1. show me how to issue ogr_fdw_info with my Teradata ODBC so i can get the CREATE SERVER scripts? or
  2. point me to example of setting up OGR_FDW to Teradata ODBC?

My environment is:

  • PostgreSQL 9.6.1 64-bit & PostGIS 2.3.3
  • Windows Server 2016 Standard, 64-bit
  • Remote datasource: Teradata Database version 16.00.00.14
  • Teradata ODBC Driver 16.20 64-bit
  • As per the Tour there should be only one question asked per question. – PolyGeo Feb 5 '18 at 6:32
1

Re 1.

If you want to use a registered ODBC DSN (e.g. 'tdprod'), you can run:

ogr_fdw_info -s "ODBC:tdprod"

May you need to authenticate (e.g. as '{user}' with a '{password}'), then:

ogr_fdw_info -s "ODBC:{user}/{password}@tdprod"

If ogr_fdw_info supports some database natively, you can also specify a whole specific connection string by yourself:

ogr_fdw_info -s "{protocol}:server={server_name};port={port};uid={user};pwd={password}[;{etc.}]"

Re 2.

OGR_FDW is a abstraction layer so creating a server for Teradata isn't different from creating a server for MS SQL except for a connection string you give in the server's options.

Some examples are:

a. SQL commands - http://www.postgresonline.com/journal/archives/339-OGR-foreign-data-wrapper-on-Windows-first-taste.html - and a very detailed post by Regina - Import SQL Server Spatial Data into PostGIS;

b. pgAdmin4 - https://www.pgadmin.org/docs/pgadmin4/1.x/foreign_server_dialog.html Please, note that in an ogr_fdw server's case you don't create explicit user mapping. If needed, it can be given implicitely in datasource connection string.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.