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:

1 Like
6

@Phil,

Thanks for the tip about the stringColumnLength configuration. That bandaid helps for some of our desired use cases.

Sometimes DateCol, DateTimeCol, and UIDcol datatypes need to be cast to varchar to come through for us. Direct query is definitely non-functional.

There are thousands of views on the linked server topics, but it doesn’t seem this is a priority for the Dremio team, unfortunately. I don’t expect this will be working as desired anytime soon.