Microsoft SQL Server support?

[sfirke]

Is SQL Server currently supported? I see it is on the R version: https://github.com/rstudio/pointblank. If not, is this planned?

[rich-iannone]

It might actually work right now! We use Ibis to handle many non-DataFrame backends. If you can create an Ibis table with an mssql connection (as in this Ibis reference page: https://ibis-project.org/backends/mssql), it may work pretty well. I say this because I haven't formally tested against SQL Server, and I'd only like to certify everything is good when it's tested.

Would you be willing to try it out and maybe let me know? :)

[sfirke]

I took it for a spin and got stuck, maybe you can tell whether I'm using pointblank wrong or if SQL Server is problematic.

I made a connection with ibis.mssql.connect() and the result is good:

>>> type(solar)
<class 'ibis.expr.types.relations.Table'>

get_column_count works:

>>> pb.get_column_count(solar)
18

But preview and an attempt to Validate failed:

>>> pb.preview(solar)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python3.10/site-packages/pointblank/validate.py", line 450, in preview
    return _generate_display_table(

  File "/usr/local/lib/python3.10/site-packages/pointblank/validate.py", line 544, in _generate_display_table
    tbl_schema = Schema(tbl=data)
  File "/usr/local/lib/python3.10/site-packages/pointblank/schema.py", line 294, in __init__
    self.__post_init__()
  File "/usr/local/lib/python3.10/site-packages/pointblank/schema.py", line 300, in __post_init__
    self._collect_schema_from_table()
  File "/usr/local/lib/python3.10/site-packages/pointblank/schema.py", line 334, in _collect_schema_from_table
    raise ValueError(  # pragma: no cover
ValueError: The provided table object cannot be converted to a Narwhals DataFrame.
>>> (
...     pb.Validate(data=solar)
...     .col_vals_lt(columns="SOLAR_KWSIZE", value=100)
...     .interrogate()
... )
Traceback (most recent call last):
  File "<stdin>", line 4, in <module>
  File "/usr/local/lib/python3.10/site-packages/pointblank/validate.py", line 5041, in interrogate
    results_tbl = ColValsCompareOne(
  File "<string>", line 11, in __init__
  File "/usr/local/lib/python3.10/site-packages/pointblank/_interrogation.py", line 1190, in __post_init__
    ).lt()
  File "/usr/local/lib/python3.10/site-packages/pointblank/_interrogation.py", line 194, in lt
    self.x.with_columns(
  File "/usr/local/lib/python3.10/site-packages/narwhals/dataframe.py", line 2523, in with_columns
    return super().with_columns(*exprs, **named_exprs)
  File "/usr/local/lib/python3.10/site-packages/narwhals/dataframe.py", line 140, in with_columns
    compliant_exprs, kinds = self._flatten_and_extract(*exprs, **named_exprs)
  File "/usr/local/lib/python3.10/site-packages/narwhals/dataframe.py", line 96, in _flatten_and_extract
    compliant_expr = self._extract_compliant(expr).alias(alias)
  File "/usr/local/lib/python3.10/site-packages/narwhals/dataframe.py", line 2192, in _extract_compliant
    return arg._to_compliant_expr(self.__narwhals_namespace__())
  File "/usr/local/lib/python3.10/site-packages/narwhals/dataframe.py", line 79, in __narwhals_namespace__
    return self._compliant_frame.__narwhals_namespace__()
  File "/usr/local/lib/python3.10/site-packages/narwhals/_ibis/dataframe.py", line 142, in __getattr__
    raise NotImplementedError(msg)
NotImplementedError: Attribute __narwhals_namespace__ is not supported for metadata-only dataframes.

If you would like to see this kind of object better supported in Narwhals, please open a feature request at https://github.com/narwhals-dev/narwhals/issues.

I see both error messages mention Narwhals and per the package documentation I think it would be using Ibis for this so maybe that's a hint? But I'm stuck here. My pip freeze shows ibis-framework==10.3.1 and pointblank==0.6.3.

[rich-iannone]

Coincidentally, someone had the same error (reported in the pointblank discord) and I applied a hopeful fix as #95. Apologies for the oversight here but could you install from main and try again?

[sfirke]

Great, that seems to have helped - now I can run pb.preview() on it. (It first failed because I didn't have polars installed, FYI but I just pip installed that).

Now when I try:

(
    pb.Validate(data=solar)
    .col_vals_lt(columns="Year_Installed", value=2030)
    .interrogate()
)

I get:

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'IS'. (156) (SQLExecDirectW)")

Is there a way I can see what SQL it's generating?

[rich-iannone]

It's possible if we use the .to_sql() method (https://ibis-project.org/reference/expression-generic.html#ibis.to_sql) along with print() in the right line.

If you're comfortable cloning the project and doing the debugging (and I would be very grateful for this), the statement that is likely the problematic one might be the one starting at

pointblank/pointblank/_interrogation.py

Line 176 in 9f47b9c

tbl = self.x.mutate(

(because it uses an isnull() method).

[sfirke]

Thanks for the pointer! I'll try to dig into this but it might not be soon.