dabla opened a new pull request, #76532: URL: https://github.com/apache/airflow/pull/76532
Adds `apache-airflow-providers-sas` (incubation, `not-ready`) with a `SASHook` and a `SASDialect`. The hook runs `PROC SQL` on a SAS 9.4 server over SSH: it uploads the SQL as a SAS program, runs the SAS binary, and reads the result back as a `.sas7bdat` file that pandas parses, with the column types taken from the file's own metadata. Nothing SAS-specific is installed on the worker: no JVM, no JDBC driver, no SAS client. Discussion on the devlist: https://lists.apache.org/thread/ts05swn91mgzv5wv9dgql0otj1sg57jj. This PR includes the commit of #76528 (`SSHHookAsync` reads the extra asynchronously and compresses like `SSHHook`), which the async path needs; I will rebase once that one is merged. ## Why not the JDBC driver Today the way to read SAS from Airflow is `JdbcHook` with the SAS/SHARE JDBC driver, through JayDeBeApi and JPype. That runs a JVM inside the worker process, crosses the Python/Java bridge for every row and several times per cell, and SAS SQL has no `LIMIT`/`OFFSET`, so paging needs `firstobs=`/`obs=` with an `ORDER BY`: one SAS query per page that sorts the table again. We ran our SAS pipelines on that JDBC route 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 fixed-width padding SAS puts on character values, convert every Java value back to Python by hand, and live with tasks that failed at random because the JVM behind JayDeBeApi became unreachable or crashed, plus out-of-memory kills of the worker on large tables. Since the switch those failures are gone, and the same loads are much faster. We ran the same Dag against the same SAS 9.4 server from the same worker, once with `JdbcHook` and once with `SASHook`: `GenericTransfer` into Postgres (page size 10,000), then `SqlToS3Operator` to Parquet with `dtype_backend="pyarrow"`. Single runs; the `SASHook` run went over a slower VPN link than the JDBC one. | Table | Task | JDBC | SASHook | |---|---|---:|---:| | A: 51,970 rows × 72 columns, 3.8 MiB Parquet | `GenericTransfer` | 84 s (SAS reads 64 s) | 27 s (SAS reads 6 s) | | | `SqlToS3Operator` | 83 s | 10 s | | B: 682,239 rows × 71 columns, 41 MiB Parquet | `GenericTransfer` | 20 min (SAS reads 18.5 min, rising per page) | 3.7 min (SAS reads 80 s) | | | `SqlToS3Operator` | did not finish in 1 h 50 min, worker memory +6.7 GiB and rising | 54 s, +1.3 GiB | The inserts into Postgres are the same for both, which is why the `GenericTransfer` totals differ less than the reads. Over JDBC the Parquet file had no timestamp column (the datetimes came back as strings) and 1.2 M string cells carried SAS's fixed-width padding; with `SASHook` the five datetime columns stay `timestamp[us]` and nothing needs trimming. One JDBC read over the VPN also hung for over two hours without an error or a timeout. ## What the hook does The provider has two parts: `airflow.providers.sas.driver`, a DB-API driver for SAS over SSH with no Airflow imports (a test keeps it that way, so it could become its own library if the community prefers), and `airflow.providers.sas.hooks.sas.SASHook` plus `dialects.sas.SASDialect`, which hold everything Airflow-specific. - `DbApiHook` contract: `get_records`, `get_df`, `get_df_by_chunks`, `run`, `test_connection`, plus a dialect (`DICTIONARY.COLUMNS`), so `SQLExecuteQueryOperator`, `GenericTransfer` and `SqlToS3Operator` work unchanged. - One SAS run per statement. `get_df_by_chunks` parses the result while it downloads. A `LIMIT n OFFSET m` page from `GenericTransfer` is not sent to SAS: the hook serves consecutive pages from one streamed run, so a paged transfer costs one SAS run and one download. - Typed results from the `.sas7bdat` metadata: character → string, numeric → float, `DATE` formats → `date32`, `DATETIME` formats → `timestamp[us]`, the same dtypes for every chunk, with `pyarrow` and `numpy_nullable` backends and polars output. - Bind parameters are written as SAS literals (SAS has no client-side binding): strings single-quoted, dates and datetimes as `'01JAN2026'd` / `'01JAN2026:00:00:00'dt`, `None` refused. - SSH in direct mode (credentials and host key on the SAS connection) or delegated to an SSH connection; zlib on the transport and `COMPRESS=` on the result data set by default. - Async counterparts `aget_records`, `aget_df`, `aget_df_by_chunks`, `aclose` on `SSHHookAsync`, safe in async tasks (no synchronous SDK call). ## Tests - Unit tests for the driver, the hook and the dialect (133 cases), with mocked SSH/SFTP and a fake reader; no SAS server needed. One of them keeps the `driver` package free of Airflow imports. - To follow in this PR: end-to-end tests that run the real code path (SSH exec, SFTP, the shell line, cleanup, parsing, typing, chunking, paging, sync and async) against an SSH/SFTP server started inside the test process, with a stand-in `sas` executable that serves real `.sas7bdat` files written by SAS 9.4 with dummy data (every column kind and format, missing and special missing values, Latin-9 text, the three `COMPRESS=` variants, a 10k-row file for chunks and pages). They need neither a SAS server nor network access, so they run in CI. There is no public SAS server for system tests. The stewards will run the suite against a real SAS 9.4 server before each provider release; see the devlist thread. --- ##### Was generative AI tooling used to co-author this PR? - [X] Yes — Claude Opus 5.5 Generated-by: Claude Opus 5.5 following [the guidelines](https://github.com/apache/airflow/blob/main/contributing-docs/05_pull_requests.rst#gen-ai-assisted-contributions) 🤖 Generated with [Claude Code](https://claude.com/claude-code) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
