feat: support Synapase Serverless SQL pool

[ruiyang2015]

Is your feature request related to a problem?

Synapse Serverless/Dedicated are both compatible with MS SQL server dialect, but the default behave of Synapse Serveless is tht DBO schema is read only.

c = ibis.mssql.connect(...)
df = pd.DataFrame.from_dict({'v': ['v1', 'v2']})
c.create_table('team', df, database='myschema')

this will getting an error from _register_in_memory_tables, where a temp in memory table is created in the dbo schema even though i tried to specify the database parameter for a different schema.

What is the motivation behind your request?

to support broader ms sql server like server.

Describe the solution you'd like

fix the _register_in_memory_tables code, so it register the in memory temp table in the same schema as the target table.

What version of ibis are you running?

9.1.0

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

synapse serverless

Code of Conduct

• I agree to follow this project's Code of Conduct

[deepyaman]

At least for MS SQL, it seems like it was deliberate to create the temp tables under dbo (see #9434), because of some quirks of MS SQL specifically. I think @gforsyth looked into this quite a bit and may be able to chime in here.

That said, what you're saying makes sense, that this approach doesn't work on an implementation where dbo is read-only.

[gforsyth]

MSSQL doesn't allow you to specify where temp tables are created -- if that behavior is different in Synapase, then the similarity of dialect seems insufficient to allow using the mssql backend there.

[ruiyang2015]

if i read the code correctly, it seems the register in mem table func in ibis ms sql lib is not correcting a temp table, it is creating a regular table, and drop it later after use.

…

On Sat, Jul 13, 2024 at 10:37 AM Gil Forsyth ***@***.***> wrote: MSSQL doesn't allow you to specify where temp tables are created -- if that behavior is different in Synapase, then the similarity of dialect seems insufficient to allow using the mssql backend there. — Reply to this email directly, view it on GitHub <#9531 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ADU6JF2MF7NCQFUN7YDL62DZMFQWBAVCNFSM6AAAAABKN7746CVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDEMRXGAYDKMJXGM> . You are receiving this because you authored the thread.Message ID: ***@***.***>