search

ibis-project / ibis

the portable Python dataframe library
https://ibis-project.org
Apache License 2.0
5.13k stars 590 forks source link

bug: export to pyarrow does not works with Fabric DWH #10199

Open djouallah opened 5 days ago

djouallah commented 5 days ago

What happened?

when I run this code

con.sql(sql, dialect="mssql").to_pyarrow(),

I get this error

DMV (Dynamic Management View) 'dm_exec_describe_first_result_set' is not supported.

What version of ibis are you using?

9.5

What backend(s) are you using, if any?

mssql

Relevant log output

No response

Code of Conduct

cpcloud commented 4 days ago

Thanks for the issue!

Not sure if I'd categorize this as a bug. We don't test against Fabric and the various MS SQLs seem to be wildly incompatible with each other.

The reason we're using dm_exec_describe_first_result_set is to compute the column names and types of arbitrary SELECT statements. Is there a way to do that that works in both Fabric and vanilla MS SQL? If so, then we can probably implement it without having an explicit Fabric backend (though I imagine eventually we'll want that).

djouallah commented 4 days ago

Thanks for the issue!

Not sure if I'd categorize this as a bug. We don't test against Fabric and the various MS SQLs seem to be wildly incompatible with each other.

The reason we're using dm_exec_describe_first_result_set is to compute the column names and types of arbitrary SELECT statement. Is there a way to do that that works in both Fabric and vanilla MS SQL? If so, then we can probably implement it without having an explicit Fabric backend (though I imagine eventually we'll want that).

to be honest, I know, it is an upstream issue, I add it just as a bookmark for the lack of a better word

gforsyth commented 4 days ago

The previous method that we used (https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-describe-first-result-set-transact-sql) would work on both vanilla MSSQL and Fabric, according to their docs.

Unfortunately, using sp_describe_first_result_set with a case-sensitive collation apparently causes DLL loading issues because... no one knows?

lostmygithubaccount commented 4 days ago

@djouallah do you know people on the Fabric (or Synapse) team(s) that we could reach out to about discussing proper Azure data backend(s) to Ibis? it would be great to at least enumerate all the work we need and have a point of contact for the key differences between what we already support (MSSQL)