ODBC driver for impala doesn't support sqlserver: Requested conversion is not supported

[#1]

Error message when select sqlserver view "tblFactValidationErrors_viewFromImpala":

OLE DB provider "MSDASQL" for linked server "ImpalaDW" returned message "Requested conversion is not supported.".
Msg 7341, Level 16, State 2, Line 2
Cannot get the current row value of column "[MSDASQL].sessionid" from OLE DB provider "MSDASQL" for linked server "ImpalaDW".

Background:

1. One hive table "tblFactValidationErrors_view4impala", it works well since I can select it in impala-shell

2. One Linked server "ImpalaDW", based on ODBC driver for Impala v2.5.2.1002

3.  create view [dbo].[tblFactValidationErrors_viewFromImpala]  AS SELECT * FROM OPENQUERY(ImpalaDW, 'select * from tblFactValidationErrors_view4impala'); on SQL server 2008R2

4. select sqlserver view "tblFactValidationErrors_viewFromImpala", if select int type column, no error;

but if select varchar/text type column, with error above.

Question:

Is it a bug that ODBC driver for Impala? that means ODBC driver for impala doesn't support sqlserver 2008R2.

or Can I do anything to workaround it?

If this is a bug, then a block bug, string/varchar/text is a most basic type, we cannot move on without it.

There is also some other source about this issue:

https://groups.google.com/a/cloudera.org/forum/#!topic/impala-user/fvGRgL3lSU4

then search "a problem with strings", then find "This is only affecting SQL Server linked servers, I believe it's because it expects VARCHAR(4000) but IMPALA string size is INT_MAX (32,767 f)."

[#2 · Accepted solution]

The updated Impala ODBC 2.5.5 driver patch that should unblock the issue you encountered:

http://go.cloudera.com/odbc-driver-hive-impala.html

The ODBC Driver for Impala v2.5.5 patch adds a DSN setting (‘StringColumnLength’) that you can use to control the length STRING columns are exposed as. If you are using Linked Server, you will need to use a value lower than 8000 or Linked Server will reject the column. This option is visible when you click the Advanced button in the configuration dialog:

   

Thanks,

Justin

[#3]

Great!  I will try it.

Many thanks.

[#4]

Hi justin,

I am facing the same problem as qwert as described here in the google group discussion:

https://groups.google.com/a/cloudera.org/forum/#!topic/cdh-user/Xc4DUwa2qOQ

Unfortunately, even the latest ODBC driver for Impala v2.5.13 does not solve this problem.

For background information:

I have set up a data source for Hive using the ODBC driver version v2.5.5 (CDH_Hive) and for Impala using v2.5.13 (CDH_Impala). Test connections were both completed successfully.

In the SQL Server Management Studio I have created two linked server objects, one for Hive Data source and one for Impala:

EXEC master.dbo.sp_addlinkedserver
 @server = 'HiveForInitions', @srvproduct='HIVE',
 @provider='MSDASQL', @datasrc='CDH_Hive',
 @provstr='Provider=MSDASQL.1;Persist Security Info=True;User ID=hive;Password=';


EXEC master.dbo.sp_addlinkedserver
 @server = 'ImpalaForInitions', @srvproduct='Impala',
 @provider='MSDASQL', @datasrc='CDH_Impala',
 @provstr='Provider=MSDASQL.1;Persist Security Info=True;User ID=impala;Password=';

Now, I want to query a table named 'yelp_reviews' that was created in Hive Metastore based on the dataset from the yelp dataset challenge. The table only contains int and string columns. The following query for Hive is executed successfully, although it was necessary to set the default string column length to 8000 in the advanced options of the data source (default was 255).

SELECT * FROM [HiveForInitions].[HIVE].[default].yelp_reviews

 Now, changing the query to use the impala data source.

SELECT * FROM [ImpalaForInitions].[Impala].[default].yelp_reviews

 results in the error message:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider 'MSDASQL' for the linked server 'ImpalaForInitions' reported an error. The provider did not give any information about the error

Msg 7306, Level 16, State 2, Line 1
Cannot open the table `Impala`.`default`.`yelp_reviews` from OLE DB provider 'MSDASQL' for the linked server 'ImpalaForInitions'. Unknown provider error.

Changing the string column length to 8000 like in the hive data source does not solve the issue either.

However, there is no such problem with the impala data source, if the queried table in the metastore only contains int-coulmns. I already verified this as described in the goolge group discussion.

Therefore, if I have not overlooked anything, the error results in the handling of string columns. Since this is working for hive data sources, I guess the ODBC driver for impala just needs a little bit of tweaking. Can you please have a look?

Thanks a lot and Merry Christmas!

[#5]

Hey there,

after half a year I am coming back to report that this problem is fixed with the newest release of the Impala ODBC driver 2.5.15. Too bad I could not find any change log but apparently the problem was solved after installing 2.5.15.

Thanks and best regards