SAP HANA Provider Package

[j6takish]

Would there be any interest in a provider package to support SAP HANA? I use airflow to interact with a SAP HANA database and I have hooks/operators written to use for work. DbApiHook claims to offers support for SAP HANA as evidenced by the docstring in the generate_insert_sql method but none of the methods will work with HANA as is, most of them need to be overridden.

It would not be too much work for me to make the hooks/operators that I have written public

[dabla]

We actually use SAP Hana, but will ditch it very soon, with the SQL hooks and operators without any issues. For the replace/upsert statements you only need to pass the 'replace_statement_format' hook parameter, and it should work.

transfer_table_task = GenericTransfer(
        task_id="transfer_table",
        source_conn_id="jdbc_hana_conn",
        destination_conn_id="jdbc_hana_conn",
        destination_hook_params={"replace_statement_format": "UPSERT {} {} VALUES ({}) WITH PRIMARY KEY"},
        insert_args={
            "commit_every": "5000",
            "executemany": True,
            "fast_executemany": True,
            "replace": True,
        },
        dag=dag,
        sql="SELECT * FROM STG_USERS",
        destination_table="USERS",
)

[dabla]

There is also another PR in progress, which introduces the notion of dialects which is independant of the connection type you want to use (native/odbc/jdbc/...)

[dabla]

But we managed to use SAP Hana in Airflow without any modifications, except the ones we already merged into Airflow regarding DbApiHook and GenericTransfer operator.

[j6takish]

It appears that you are interacting with HANA using JDBC and not hdbcli, correct? hdbcli is the official SAP cursor package and only dialect for sqlalchemy. Without using hdbcli, you can't create a sqlalchemy engine, or an inspector, and many operations will be executed sub-optimally

[dabla]

Yes you can and we actually do it ar our company, I’ve done a PR in the past so you can use sqlalchemy with SAP Hana through jdbc

[Joffreybvn]

Side note: If you use sqlalchemy-hana, beware that this not an officially supported SAP product (as stated in their README). Which means SAP can decide to yank all the releases on a friday afternoon (like they did for pyrfc).

[j6takish]

I have mostly moved away from sqlalchemy for HANA as hdbcli works much better but there are a few things (reflection, inspection) from sqlalchemy that are incredibly convenient.

Interestingly, the SAP documentation does direct you over to sqlalchemy, I do not recall them ever acknowledging SA in the past. I feel like they used to push hana-ml, which was their attempt at making their own proprietary pandas, which is very on-brand for SAP.

https://help.sap.com/docs/HANA_SERVICE_CF/1efad1691c1f496b8b580064a6536c2d/01e93e584e524747b570cd9083b08d2b.html

[potiuk]

And this is one of the reason we in Airflow community are reluctant to accept providers that are 3rd-party and the 3rd-party is not committed to supporting it. We do not want to deal with things like that and promise our users things we cannot promise.

[potiuk]

@j6takish -> generally approach we have in the community that we are reluctant to maintain something that is used by a handful of our users (or maybe even few handfulls - but not really massive numbers) - and where we - the apache community built around Airlfow - do not have a lot of experience with.

We have very high expectations on when we accept new providers for new technologies - simply because we do not want to increase maintenance burden on us - Airflow is developed by most people in their free time, and maintainers are also volunteers and the maintenanc effort to review, merge, fix etc. is on them (while some of them are paid to do other stuff, they are not necessarily paid to maintain technoloigy X or Y integration with Airflow - so they do it effectively in their own free time).

That's why we have not only guidelines but also very strict expectations on accepting new providers to be maintained here in the community - I recommend you to read them https://github.com/apache/airflow/blob/main/PROVIDERS.rst#community-managed-providers - there are also some examples of discussions and previous attempts (both successful and unsuccessful) of submitting providers to the community here. It also explains what are the limitations of having comunity provider.

One of the important limitations is that the community has to have an easy way to test such provider when things change - if that requires external service like SAP/HANA, that someone must also provide "system test dashboard" where system tests are run against such external service (and pay for it) - and provide public dashboard to the community so that release manager can see that the system tests passed.

You can look at the past discussions and decide yourself if you want to go that road - eventually any provider will have to be accepted by formal VOTE on the devlist, after initiall discussion there.

And you are also absolutely free to go another route - providers do not have to live here - in airlfow repo, in fact many of them don't https://airflow.apache.org/ecosystem/#third-party-airflow-plugins-and-providers - you can easily package and release your own provider in the way and manner you think it is best - you can also add link to it at our "ecosystem" page - we will merge such change gladly.

So it's really up to you to see what road you think is best for such SAP/HANA provider.

[j6takish]

Third party is definitely the way to go as I can't imagine there being a ton of HANA/airflow users, I just wanted to gauge the demand for a HANA provider package.

[potiuk]

Posted it in a wrong thread :). Moved the comment to where it belongs

[potiuk]

Yeah. I think 3rd-party is the way to go too

[potiuk]

Ideally SAP should maintain their own provider :D