MS SQL Linked Server to Dremio using Arrow Flight ODBC

1

Hi There,

I’m trying to set up a Linked Server on a MS SQL Server (16.0.4085.2) to our Dremio community instance (24.0.0-202302100528110223-3a169b7c) using the Arrow Flight ODBC driver. The driver is set up on the host machine and the connection test is successful. I can also use it in MS Excel without issues.

I can also set up the Linked Server ([DREMIO]) without issues. When trying to run a query via the linked server, the issues start:

The query SELECT id FROM [DREMIO]..[clients.client_a].[data] returns the following error:

OLE DB provider "MSDASQL" for linked server "DREMIO" returned message "Method is not supported by this provider.".
OLE DB provider "MSDASQL" for linked server "DREMIO" returned message "[Apache Arrow][Dremio Server] (100) Unsupported function".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "DREMIO" reported an error. The provider does not support the necessary method.
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_INDEXES" for OLE DB provider "MSDASQL" for linked server "DREMIO". The provider supports the interface, but returns a failure code when it is used.

When I instead try to use openquery (e.g. select * from Openquery(DREMIO, 'select external_id from clients.client_a.data')), I can get some columns such as the id, but most will return the following error:

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

In the Dremio Jobs tab, I can see that all queries are completed successfully.

Any help with this issue is greatly appreciated! I’m also open for any other ways of making Dremio data available on a MS SQL Server.

Darian

2

Hi, Darian. Are you running MS SQL Server on Windows? If so, you can also consider trying to use the PolyBase Generic ODBC connector instead: Access external data: ODBC generic types - PolyBase - SQL Server | Microsoft Learn

I am not sure if anyone has had success with either Linked Server or PolyBase, but it is worth a try.

3

Hi Max,

thank you for the hint about PolyBase Generic ODBC. We are indeed running the SQL Sever on windows. I will give it a try.

4

Hi Darian,

Did you manage to solve this issue? we are facing the same with dremio linked server.

5

Hi Darian,

We encountered the same issue and could fix by adding “stringColumnLength”=“2000” in the advanced properties of the Arrow Flight ODBC config.

MS SQL Server seems to have a maximum size of 2000 chars for a column and our Dremio deployment uses a larger value.

With this fix Openquery did work.
The direct query (e.g. [DREMIO]…[clients.client_a].[data] ) is still not working. Has anyone been able to get it working?

References: