DuckDB integration?

[ahmedanadwc]

Hi Tom,
Are there any plans to integrate DuckDB with SASPy?

[tomweber-sas]

Looking at that, it's a database. Not sure in what way you're thinking it would be integrated into SASPy? Can you expand on that?

[ahmedanadwc]

DuckDB is in-process multi-threaded database that can translate existing data-frames within the same process into another supported formats, including ingesting remote files (parquet, json, csv, ....). Basically it could boost the performance and features of the SASPy.

Just as an fyi, there are several Open Source software packages and bundles embedding DuckDB within their internals to enrich their functionalities and features.
Check these videos for more details if you are interested
Going Beyond Two Tier Data Architectures with DuckDB // Hannes Mühleisen // DE4AI
DuckCon #5 in Seattle

[ahmedanadwc]

GM Tom,
After spending more time reading the internals of the SAS Data set export/conversion to Pandas, it looked, if I'm not mistaken, it's heavily dependent on exporting to csv (proc export) and ingesting csv (pandas.read_csv). If that's accurate assessment, then here is where DuckDB integration could yield significant performance and functionality gains!
SAS uses Proc Export to generate *.csv, while DuckDB uses it's ability to scan the *.csv and convert it to whichever format it supports.
Result Conversion

[tomweber-sas]

Good morning. Yes, the sd2sf and df2sd methods stream delimited data to/from pandas. But I don't use proc import/export as they don't support enough kinds of data very well. I still have the method=CSV version of these (_csv) but those are not the default, nor the suggested methods to use. The default methods generate data step code to match up with the numerous options and configurations of the pandas method which reads delimited data (the read_cvs method which reads any kind of delimited data); that's for sd2df. For df2sd, I also generate the data step code to read a stream that I transfer, based upon me traversing and extracting the data from the data frame; this is a bit simpler since SAS only supports a couple data types, but it's still a complicated and tedious task.

I haven't had time to look at this database much. Too much work on SAS itself these days. There are a number of python data products today, other than just pandas, which was by far the defacto standard a decade ago when I wrote saspy. The unfortunate part of this is that SAS doesn't have engines that support these new data formats, else I could use them when transferring these data representations between SAS and Python. There does exist a parquet engine, but only in viya 4, so most of our customers can't take advantage of that. The trouble with trying to implement all of these formats is the fact that I have to use data step to generate a stream that any given Python data product can ingest. That's much harder than it sounds, since SAS doesn't even behave exactly the same for even the one way I'm doing this in the different access methods.

If this duckdb can parse exactly the same data streams as pandas, then maybe that could be straight forward. I haven't had time to investigate that though, as I'm bogged down in SAS work and don't get to spend as much time on saspy as I have previously.

[ahmedanadwc]

I appreciate the response, and good luck with your workload.