I can try SQLAlchemy, but don't think it will help because the generated SQL statement with LIMIT 1 is a not a valid T-SQL statement.

As you can see the pandas.read_sql return the value column in scientific format, but it matches the SQL Server Studio, but Daft returns None for all 5 rows.

Other observation: looks like the daft automatically run in distributed mode using local ray cluster as long as ray is installed. We don't need to explicitly call daft.context.set_runner_ray(). So it is indeed automatically detect ray.

Question, by specifying SqlAlchemy does that mean it won't be using ConnectorX? In addition, when I create the SqlAlachemy engine, I have to provide a URL. Here is how I construct URL from the connection_string I showed earlier. Note here I specify pyodbc as the driver. Will we still be using arrow at the driver layer if we specify pyodbc as the driver? url = sqlalachemy.engine.URL.create(drivername="mssql+pyodbc", query={"odbc_connect": connection_string}) sqlalachemy.create_engine(url).connect()

In summary, we seems to have a number of issues here; 1. The incorrect value seems to be a serious one 2. Follow by not supporting connection_string directly (resulting in wrong SQL query statement with RuntimeError exception). 3. Follow by have to use SqlAlachemy explicitly and not sure if ConnectorX is really used under the hood 4. Follow by have to explicitly specify pyodbc driver

We can maybe expose a parameter for how many rows we read for inference… and also for specific backends (the more common ones such as Postgres and SQL Server) we can define type mappings

A quick fix of this is to increase limit from 1 to something like 10 or even 100 to improve schema inference accuracy. In case of any column with NULL, try to fetch more data for that particular column. If all rows are null for the column in question, issue a warning. Or in the case of database, would it be simpler just call INFORMATION_SCHEMA to find out the data type than infering from data?

A quick question, what is the default data type if daft cannot infer data type correctly. Will it be string type? In that case, we should still have a string representation of the data rather than None, I'd assume?

Answer my own question early, I see why daft cannot rely on INFORMATION_SCHEMA to learn schema. For example, a query could use any SQL function to transform table columns to a new value with different types. In this case, we can only rely on inferencing or user provided schema.

Today if we receive all None, we infer it as a null column which is why you’re seeing all null data. We should allow you to hint Daft though. That mechanism already exists for our other reads and should be easy to add

PR is ready for review: https://github.com/Eventual-Inc/Daft/pull/2676

Additionally, to provide some clarification: • If a user provides a connection string, we will parse the string to determine the dialect and generate sql appropriately. I believe the reason why the connection string did not work for you is because it did not match a url format like ``mssql://host:port`` . You could try reformatting your connection string. That being said, i'm not 100% confident that the read will execute, because ConnectorX does have some issues with MSSQL. See: https://github.com/sfu-db/connector-x/issues/233 for instance. • Regarding whether or not ConnectorX/SQLAlchemy is used under the hood, there's essentially 2 rules. 1: if user passes in a connection string and the dialect parsed is not supported, use SQLAlchemy. 2: If user passes in SQLAlchemy connection, use SQLAlchemy. • Unfortunately, SQLAlchemy is not an arrow backed driver. This means when Daft executes a query using SQLAlchemy, the results are first returned into a Python dictionary, which is subsequently converted into Arrow format.

Regarding append/overwrites for writes: There is an outstanding issue for this: https://github.com/Eventual-Inc/Daft/issues/1768, and it's definitely something we'd like to get done! The biggest challenge is figuring out how to do it in a distributed fashion.

@Colin Ho, quick feedback. upgrade to 0.3.0 and the read_sql works fine now! I haven't tried with explicitly providing schema yet, but it works out of box with default parameters. I guess the default 10 samples made the difference for schema inference. Thanks you so much for fixing the issue so quickly. Had a quick look of release notes for 0.3.0, awesome progresses. Keep up the great work.

@Colin Ho I tried the read_sql with schema, it works like a charm. Great work. I am able to read a lot of tables, large and small. Everything went smoothly until I hot a very large table. It crashes with this statement: SELECT count(*) FROM (....) AS Subquery with Arithmetic overflow error converting expression into data type int. I believe if we change the generated SQL code to use count_big rather than count, it will fix the issue: SELECT count_big(*) FROM (...) AS Subquery Of course any return variable should also be changed to int64 if it is int32.

I guess this is also dialect dependent. As some database, for example, for postgrelsql, count() always return bigint.

Hi @Colin Ho, just got back to run my ETL job (was on vacation) and the arithmetic overflow issue is not longer happening. Thanks again for quick fix!