Hi Vikram, hi all, I hope you're doing well! With 3.2 and 3.3 out and 3.4 around the corner, I'd like to pick this thread up again. In April you mentioned you had some thoughts on it, and in the meantime I turned the proposal into a PR, so there is now something concrete to look at:
Draft SAS provider: https://github.com/apache/airflow/pull/76532 It builds on a small ssh provider fix, useful on its own: https://github.com/apache/airflow/pull/76528 A short recap of why we went for a native hook instead of JdbcHook with the SAS JDBC driver: we ran our SAS pipelines on JDBC first and moved them to this hook, which has been running in our production environment since and transfers millions of records from SAS every day. With JDBC we had to trim the padding SAS puts on character values, convert Java values back to Python by hand, and live with tasks failing at random because the JVM behind JayDeBeApi became unreachable or crashed, plus out-of-memory kills of the workers on large tables. Since the switch those failures are gone, and the loads are much faster. Some numbers from the same Dag on the same worker and SAS server, once with each hook: * 52k rows x 72 columns to Parquet (SqlToS3Operator): 83 s with JDBC vs10 s with the SAS hook * 682k rows x 71 columns into Postgres (GenericTransfer): 20 min with JDBC vs under 4 min with the SAS hook * the same 682k rows to Parquet: JDBC did not finish after almost two hours (the worker was past 6.7 GiB of memory), the SAS hook took under a minute with 1.3 GiB On top of that the SAS hook keeps the column types (dates and timestamps stay dates and timestamps in Parquet), needs no JVM, no JDBC driver and no SAS client on the worker, and works with SQLExecuteQueryOperator, GenericTransfer and SqlToS3Operator out of the box, sync and async. It also brings no new third-party dependency into Airflow: everything it uses (paramiko, asyncssh, pandas, pyarrow) is already there through the ssh and common.sql providers, so uv.lock only gains the provider itself. On my question from April (separate library or everything in the provider): I now propose to keep everything in the provider. The DB-API part lives in its own package without Airflow imports, so it can still become a separate library later if there is demand outside Airflow. About testing, since that was the main concern back in 2022: there is no public SAS server, so the tests run against an SSH/SFTP server that starts inside the test process, with a stand-in "sas" executable that serves real .sas7bdat files written by SAS 9.4 with dummy data. That way the whole code path runs in CI without a SAS server, and before each provider release we run the suite against a real SAS 9.4 server. I'm happy to act as steward and to maintain the provider, and I'd be very grateful for a sponsoring committer, or for any feedback on the approach. Thanks a lot, and looking forward to your thoughts! Best regards, David ________________________________ From: Vikram Koka via dev <[email protected]> Sent: Monday, April 06, 2026 22:51 To: [email protected] <[email protected]> Cc: Vikram Koka <[email protected]> Subject: Re: [DISCUSS] Proposal: Add SAS Provider with Native Hook (No JVM Dependency) EXTERNAL MAIL: Indien je de afzender van deze e-mail niet kent en deze niet vertrouwt, klik niet op een link of open geen bijlages. Bij twijfel, stuur deze e-mail als bijlage naar [email protected]<mailto:[email protected]>. Hi David, Thanks for bringing this up. I have several thoughts on this and would love to discuss them in more detail. At this time, there is a fair bit of cognitive load with the 3.2.0 release. But, will happily discuss right after that. Best regards, Vikram On Thu, Apr 2, 2026 at 6:50 AM Blain David <[email protected]> wrote: > Hi everyone, > > I would like to start a discussion if there would be an interest in adding > a SAS provider to Airflow, based on some challenges and solutions we’ve > encountered in our environment. So let me explain. > > Until recently, we relied on the JdbcHook combined with GenericTransfer to > extract data from SAS to our Postgres DWH. However, this approach > introduces significant overhead due to the JVM dependency (via jaydebeapi). > In practice, this leads to duplicated memory usage and instability during > larger transfers (as our workers on K8S pods are limited in memory). > Lately, we’ve experienced multiple failures due to new imports of very > large tables, including JVM crashes caused by OOM issues as well as > intermittent backend errors from SAS during long runs (multiple hours, > especially for the initial runs). > > We also evaluated saspy as an alternative, but since it also relies on the > same underlying JDBC/JVM mechanism (e.g. jaydebeapi), it does not resolve > these issues. > > To address this, we implemented a native SASHook that uses SSH underneath > to connect to the SAS environment. This allows us to execute SQL queries > directly on the SAS server and retrieve results without involving a JVM. On > top of that, we've built a DB-API 2.0–compatible cursor implementation that > handles the interaction over SSH and integrates with pandas (leveraging > read_sas for returning results as DataFrames), thus it integrates > transparently with the DBApiHook. > > This approach has proven to be much more stable in our DAGs. Since > adopting it, we have not encountered failures anymore. While performance is > not on par with native ODBC or database drivers, it is still an improvement > over the JDBC bridge. For reference, we can fetch around 10k records in ~2 > seconds. The initial query is slightly slower due to SSH handshake and > session setup, but overall the trade-off is acceptable given the stability > gains. Probably performance can still be improved, and we will further > check into it but at least we resolved the instability issue and our > pipelines are reliable again. > > I would like to get feedback on how to contribute this, if any interest of > course. > > Should I split this into two parts? A standalone open-source project > providing a generic SAS DB-API 2.0 implementation and an Airflow provider > that depends on this library? > Or would it be preferable to include the full implementation directly > within the SAS Airflow provider (e.g., as a hook and cursor)? > > I’m happy to contribute the implementation and help maintain it, but I’d > like to align with the community on the preferred approach before moving > forward, if there would be any interest of course. > > The advantage of this solution is that it doesn't need of any new > dependencies, it purely relies on dependencies (paramiko an pandas) already > present in the Airflow ecosystem. > > Any thoughts or suggestions would be greatly appreciated. > > Best regards, > David > General (Internal Property)
