bug: ibis mssql backend can not create table for very simple pandas dataframe

ruiyang2015 commented 4 months ago

What happened?

for a very simple test case like this:

c = ibis.mssql.connect(...)
df = pd.DataFrame.from_dict({'name': ['john', 'joe']})
c.create_table('team', df)

What version of ibis are you using?

9.0.0

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

mssql

Relevant log output

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Users/ruiyang/Go/src/github.com/ascend-io/ascend-core/.venv/lib/python3.11/site-packages/ibis/backends/mssql/__init__.py", line 466, in create_table
    self._run_pre_execute_hooks(table)
  File "/Users/ruiyang/Go/src/github.com/ascend-io/ascend-core/.venv/lib/python3.11/site-packages/ibis/backends/__init__.py", line 1030, in _run_pre_execute_hooks
    self._register_in_memory_tables(expr)
  File "/Users/ruiyang/Go/src/github.com/ascend-io/ascend-core/.venv/lib/python3.11/site-packages/ibis/backends/sql/__init__.py", line 257, in _register_in_memory_tables
    self._register_in_memory_table(memtable)
  File "/Users/ruiyang/Go/src/github.com/ascend-io/ascend-core/.venv/lib/python3.11/site-packages/ibis/backends/mssql/__init__.py", line 571, in _register_in_memory_table
    with self._safe_raw_sql(create_stmt) as cur:
  File "/opt/homebrew/Cellar/python@3.11/3.11.9/Frameworks/Python.framework/Versions/3.11/lib/python3.11/contextlib.py", line 137, in __enter__
    return next(self.gen)
           ^^^^^^^^^^^^^^
  File "/Users/ruiyang/Go/src/github.com/ascend-io/ascend-core/.venv/lib/python3.11/site-packages/ibis/backends/mssql/__init__.py", line 268, in _safe_raw_sql
    cur.execute(query, *args, **kwargs)
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]The statement failed. Column 'name' has a data type that cannot participate in a columnstore index. (35343) (SQLExecDirectW)")

Code of Conduct

[X] I agree to follow this project's Code of Conduct

cpcloud commented 4 months ago

@ruiyang2015 It looks like somehow a columnstore index is being created automatically, and the various string types are not allowed to be columnstore indexes. Ibis isn't doing anything special here.

We already have test coverage for the create_table API using the MS SQL backend, including with string column types.

Closing this out as not an issue with ibis.

ruiyang2015 commented 4 months ago

@cpcloud I just realize the MS SQL server is a Azure Synapse Anaylytics Server, so it seems that columnstore index is the default index for Synapse, is there a way to make MSSQL backend to be able to specify the additional WITH (HEAP) at the end of create table statement using ibis ms sql backend?

ruiyang2015 commented 4 months ago

alternatively, is it possible to specify the string column as nvarchar(4000) instead of nvarchar(max) by default?

ibis-project / ibis