odbc_fdw -> apache drill

[parisni]

Hi,

I have a working odbc apache-drill connection (cf https://drill.apache.org/docs/odbc-configuration-reference/ ). I am able to connect to my remote drill and query it.
Example: schema=hbase and table =hbase_t_random
SELECT * FROM hbase.hbase_t_random
works from my odbc client.

I have compiled and installed odbc_fdw. Those steps work :

CREATE EXTENSION odbc_fdw;
CREATE SERVER odbc_drill
FOREIGN DATA WRAPPER odbc_fdw
OPTIONS (
dsn 'Sample MapR Drill DSN 64'
);
CREATE USER MAPPING FOR postgres
SERVER odbc_drill;

But when importing the table :

IMPORT FOREIGN SCHEMA test
FROM SERVER odbc_drill
INTO public
OPTIONS (
schema 'hbase',
table 'hbase_t_random'
);

I get this error:

ERROR: Connecting to driver
********** Error **********
ERROR: Connecting to driver
État SQL :58000

Here is my odbc.ini

[Sample MapR Drill DSN 64]
Description=MapR Drill ODBC Driver (64-bit) DSN
Driver=/opt/mapr/drillodbc/lib/64/libmaprdrillodbc64.so
ConnectionType=Direct
HOST=localhost
PORT=31010
AuthenticationType=No Authentication
AdvancedProperties=CastAnyToVarchar=true;HandshakeTimeout=5;QueryTimeout=180;TimestampTZDisplayTimezone=utc;ExcludedSchemas=sys,INFORMATION_SCHEMA;NumberOfPrefetchBuffers=5;
Catalog=DRILL

Can anybody help (where are the logs, how to debug, what obvious parameters I forgot)

Thanks

---

ubuntu 14.04
postgresql 9.5.3
apache drill 1.6.0

[parisni]

I have made some advances:

The odbc_fdw extension is well installed, because I have installed postgresql odbc, and created a foreign table from a remote postgresql database. -> this works well with postgres.

My odbc configuration for apache drill is ok too, because I am able to access to it with libreofficeBase though ODBC.

However, I am still getting this error with odbc_fdw:

/etc/odbc.ini

[Drill]
Driver = DrillDriver
ConnectionType = Direct
HOST = localhost
PORT = 31010
AuthenticationType = No Authentication

/etc/odbcinst.ini

[DrillDriver]
Driver = libmaprdrillodbc64.so

/etc/mapr.drillodbc.ini

[Driver]
DisableAsync=0
DriverManagerEncoding=UTF-16
ErrorMessagesPath=/opt/mapr/drillodbc/ErrorMessages
LogLevel=6
LogPath=/tmp/drilllog/
SwapFilePath=/tmp
ODBCInstLib=libodbcinst.so.1

Postgresql query

CREATE EXTENSION odbc_fdw;
CREATE SERVER odbc_drill
FOREIGN DATA WRAPPER odbc_fdw
OPTIONS (
"dsn" 'Drill'
);
CREATE USER MAPPING FOR postgres
SERVER odbc_drill;

CREATE FOREIGN TABLE
odbc_table (
employee_id integer
)
SERVER odbc_drill
OPTIONS (
schema 'cp',
table 'employee.json',
sql_query 'select employee_id from cp.employee.json',
sql_count 'select count(1) from cp.employee.json'
);

SELECT * FROM odbc_table

I still get this error:

ERROR: Connecting to driver
********** Error **********
ERROR: Connecting to driver
État SQL :58000

Why odbc works with libreofficeBase, and not odbc_fdw, this is the question

---

Notice I use unixODBC V2.2.14
ubuntu 14.04
postgresql 9.5.3
apache drill 1.6.0

[parisni]

I have added to /var/lib/postgresql/.bash_profile:

export LD_LIBRARY_PATH=/usr/local/lib:/usr/lib/:/opt/mapr/drillodbc/lib/64/:/usr/lib/x86_64-linux-gnu/:/usr/lib/x86_64-linux-gnu/odbc/
export ODBCINI=/etc/odbc.ini
export MAPRDRILLINI=/etc/mapr.drillodbc.ini

The unixodbc command isql 'Drill' works with the postgres user.

But again, odbc_fdw has this driver error. Is it possible that the database ignore the configs below ?

[ccakes]

Having the same issue with an Impala connection,

[Hercules888]

I had the same issue, and it was due to not fully qualifying the queries in the wrapper definition. I had to use database.schema.table . That solved it for me.