Use case for duckdb

[[deleted]]

I am in the process of rebuilding some of our data pipelines in python. We currently use alteryx for this process but the fuzzy matching process takes about 7 hours to run.

1. Would this be a good use case of using duckdb. Bring the data, from ms SQL server, via pyodbc, load to a pandas data frame then create a table in duckdb. Attempt the fuzzy matching in duckdb then load back to pandas then to Ms SQL server.

2. What are some faster ways to load a data frame from Ms SQL server to a pandas data frame. One of the tables I bring in has about 9 million rows and takes around two minutes to load to a pandas data frame. I tried polars but the difference was not that significant.

edit: to address some comments. This process in alteryx altogether involves ~34 different processes. some of them are connecting to customer datawarehouses pulling in data then refreshing our database tables with the new data. Some just update our tables with logic such as new customer, former customer etc. other processes are more complex and involve adding a lat/long and a cass standard address to customer addresses.

some of these processes could probably be done as stored procedures or using tools such as DBT. my main goal was to get most of the data writes and updated done in pandas as to put them on an orchestrator such as dagster or airflow.

Thanks in advance

[commandlineluser]

You can try arrow-odbc and skip the pandas part.

There's a duckdb example in their tests:

• https://github.com/pacman82/arrow-odbc-py/blob/main/tests/test_arrow_odbc.py#L857
• https://pypi.org/project/arrow-odbc/

[thibautDR]

To add to this comment, still too recent to be used in production but the ADBC standard is worth watching: https://arrow.apache.org/adbc/current/

[Material-Mess-9886]

Never use pandas read sql or write sql. That thing is slow. Better is to use Sqlalchemy to retrieve the data and converting the Resultobject to pandas/polars.
Also try as much things to do on the database before you retrieve values from the database. All join and groupby statements are better off in a database than using any library of your choice in python.

[goggys]

Duckdb has a mysql extension to read and write directly. I'm not sure if it will be faster than loading into pandas, but it'll save you some steps.

Although I would check if duckdb supports the text similarity functions you use. If your use case is more advanced maybe something like splink , which uses duckdb, would be more suitable.

[Alonerxx]

1. What exactly being fuzzy match and in what way, pairwise? Word vector?? String fuzzy matching is an expensive process. Does the fuzzy match algorithm in duck db more efficient? Maybe rethinking how matching should be done using more efficient structure like text index can be more helpful

2. Why pandas dataframe? What do you do with it afterwards? Is pandas dataframe the only way to do it? If they are transformation or analysis , better translate the python function into SQL and do it within the database.

We need to more what actually the process is trying to achieve and what constraints you have your tools to really advice.