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.