Hi, I’m trying to read data into a daft dataframe from the result of running a sql query against a SQLServer instance. The salient parts of the code are:

import ray
import daft

ray.init()

daft.read_sql(<sql query>, mssql://<user>:<password>@<server>:<port>/<db>, parallelism=<parallelism>, partition_col=<col>)

Which almost immediately returns

RuntimeError: Timed out in bb8

If it really is a timeout, there doesn’t seem like a way to configure it as part of the read_sql call. Verified that the server is up, and the creds are correct because I can use pyodbc + ray dataset’s read_sql to read the data.

Hmm interesting! Looks like there’s a bunch of issues online about connectorx compatibility with SQLServer https://stackoverflow.com/questions/70879465/connecting-ms-sql-server-using-connectorx-in-python It seems you could try manually specifying the driver to not use odbc:

?driver=SQL+Server&trusted_connection={trusted_conn}'

Let me know if that works?

thanks! adding

driver=SQL+Server

also results in the same error 😕

:( are you able to get “just” connectorx working without Daft? I’m looking at the example in that stackoverflow post:

import connectorx

username = 'my_username'
password = '1234'
server = 'SERVER1'
database = 'db1'
trusted_conn = 'no' # or yes

query = "SELECT * FROM table"

mssql_url = f'mssql://{username}:{password}@{server}/{database}?driver=SQL+Server&trusted_connection={trusted_conn}'

df = connectorx.read_sql(mssql_url, query)

Curiously that makes no progress/hangs at read_sql call on Ray Will try locally

Aha… I’m guessing connectorx just really doesn’t work with your setup of SQLServer :( I’m currently out on a lunch food run, but can show you how to use SQLAlchemy instead! It’s our “fallback option” which will be slower but should work as long as you can provide Daft with a callback to construct a working SQLAlchemy connection object.

Thanks so much! Will try SQL alchemy

No probs! Keep us posted I’m excited to see how your experience is with Daft, especially since you’re a Ray user 😁

Thank you, and I'll keep you posted!

Found the example on our docs: https://www.getdaft.io/projects/docs/en/latest/api_docs/doc_gen/io_functions/daft.read_sql.html#daft.read_sql

def create_conn():
    return sqlalchemy.create_engine("mssql+pyodbc://<user>:<password>@<server>:<port>/<db>").connect()

df = daft.read_sql("SELECT * FROM my_table", create_conn)

I think you’ll need to specify

mssql+pyodbc://

so that SQLAlchemy will use pyodbc for the connection under the hood. Let me know if that works for you!

Hey @Neeraj Krishnan! I'm Colin, I worked on the read_sql functionality, just wanted to check in and see if using SQLAlchemy worked for you?

Hi Colin, and thanks for following up! Connecting via sqlalchemy does work. Query execution fails however, and this is what I see:

UserWarning: Failed to execute the query with limit 1: Failed to execute sql: SELECT *
FROM (<original query>) AS subquery
LIMIT 1

which is expected since sqlserver does not support

limit

(uses

TOP

) However, after stating

Attempting to read the entire table

we still get:

Incorrect syntax near 'LIMIT
...
[SQL: SELECT *
FROM (SELECT *
FROM (<original query>) AS subquery
LIMIT 1) AS subquery]

using daft 0.2.21 https://github.com/Eventual-Inc/Daft/blob/v0.2.21/daft/sql/sql_connection.py where the second query construction is supposed to leave LIMIT out of the query. B_ut_ the

sql

arg passed to it is the shadowed sql variable which has the wrapping select * and limit already added to it. Also, if its of interest later to someone

connection_url = sqlalchemy.engine.URL.create(
    "mssql+pyodbc",
    username=user,
    password=password,
    host=host,
    port=1433,
    database=database,
    query={
        "driver": "ODBC Driver 18 for SQL Server",
    },
)
def create_conn():
    return sqlalchemy.create_engine(connection_url).connect()

Where ODBC drivers are installed per:
<https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15&tabs=ubuntu18-install%2Calpine17-install%2Cdebian8-install%2Credhat7-13-install%2Crhel7-offline>

is what worked connection-wise.

Thanks so much for the detailed logs and code examples! I was able to reproduce the error, which is due to Daft manually constructing queries using

limit

in version

0.2.21

We've actually updated Daft starting from version

0.2.22

to use a separate SQL parsing library called SQLGlot to construct our sql queries, which should make us compatible across more dialects. However, I do need to make a small fix to allow it to work with SQL server (essentially SQLGlot recognizes the sql server dialect as

tsql

instead of

mssql

), which should be ready in the next release! https://github.com/Eventual-Inc/Daft/pull/2330 @jay could you take a look?

Looks good! Just stamped it ✅

thank you!

Thanks @Neeraj Krishnan 🙂 We’ll provide an update again when the new version of Daft is released

Hey @Neeraj Krishnan! the latest version of Daft was just released (

0.2.26

), the new fixes should be ready to try out!

Thank you!

Awesome! glad to hear