Connect ecto to SAP HANA by means of odbc / jdbc fdw

Good day!

Does anyone of you have any experience with hooking up elixir with or without ecto to SAP HANA ˋdirectlyˋ or over Postgresql fdw to SAP Hana? I am working on a liveview frontend would be awesome if I could consume the data from our HANA Datawarehouse.

If yes, how does that work performance wise? And in general? I am now working with mongodb…

Your help is much appreciated. Thank you for reading.

Hi @tschnibo

I have not yet connected to SAP HANA, but I have some knowledge using ODBC with Elixir because I need to connect to a rather esotheric DB over ODBC for a customer.

What I did was to patch up https://github.com/findmypast-oss/mssqlex as “ODBX”, which is as of yet ugly enough to not publish it… nevertheless: that is a starting point.

I also started implementing the Ecto adapter for this driver and got it somewhat working but decided that it’s not worth it to use Ecto, since we have no use for migrations in this specific project.

That’s why we simply use a patched version of https://github.com/lpil/yesql by the dear community member @lpil :slight_smile:
To use it we only had to patch in the aforementioned ODBX driver and make some adjustments on how we receive results. The benefit is that we can simply write the SQL dialect our database needs while avoiding injection attacks. This freed us from having to maintain our own Ecto DSL mapping.


To the other questions: ODBC has a decent performance. erlang_odbc is hands down the best ODBC implementation I came across, which is mainly the reason we went with Elixir in that project. Although we found the connection to be somewhat brittle if you do lots and lots of parallel requests (this might still be a problem in our driver, actually).

Apart from that: the hardest part was really to figure out how to properly setup unixODBC.

Since you’re currently working with mongodb, I wonder if your problem should be represented in a relational database at all. I’d assume you were tasked to “put that data in the main system”, weren’t you? ^^

4 Likes

Hi @mmmrrr

yes, you guessed completely right. The main system just moved to HANA - and my system is a high visibility project they would be glad to be the data provider for. Just the idea of a nice software stack differs quite a lot ;). I am willing to try since neither mongodb is officially supported… at first I used mysql (with python, not elixir), then mongodb, so it would “just” be touching quite a lot of DB related stuff again - good time to refactor a bit ;).

Thank you for your insights! That is very helpful. I have never heard of yesql before. To make sure I understand this correctly, you used unixODBC with the driver you received from your database toolchain. ODBX as a “driver” on the erlang-elixir side and yesql for querying and data consumption, right?

So in case I would use unixODBC together with the ODBC driver included in the SAP HANA client, there is no generic erlang/elixir ODBC driver which can handle this. This is where mssqlex in a modified version can be used. I don’t understand the role of yesql and mssqlex completely, is yesql a necessity, or would the modified ODBX also be sufficient for sql queries, but yesql does this more elegantly? And or is ODBC for the different dialects not generic enough, this is why you need to patch mssqlex?

Again on the indirect idea of a postgres-fdw, would you know the steps to hook up this exotic DB to postgres?
Would the ODBC_fdw package https://github.com/CartoDB/odbc_fdw, possibily work with the HANA client driver, or would I need a separate tool too?

Sorry, many questions, I sure can dive into it deeper myself - still I am very glad for pushes into the right direction, as you already provided. These tools seem like an excellent starting point.

Thank you very much for your advice, and best regards!

https://github.com/CartoDB/odbc_fdw looks really interesting. I simply wasn’t aware of it so I cannot say if this would or would not work. Nevertheless I’d always be hesitant to introduce another layer of indirection if not absolutely necessary or beneficial.

The setup/architecture contains 4 parts:

  1. unixODBC
    a) The program from your package manager. This will establish the low level connection.
    b) Database specific driver (for you, this would be the HANA ODBC driver)
  2. erlang_odbc, which is a binding to unixODBC
  3. “ODBX” is the Elixir wrapper I wrote (based on mssqlex), that is modeled after postgrex. This manages the connection pooling, supervision of the requests and a streamlined API for an Elixir consumer. It also translates UTF-16 to UTF-8 and vice versa due to some unfortunate decisions in the past :wink: This is the core part: You could simply pass SQL strings to this library and it will yield results
  4. Yesql is the thing we’re using to write the actual SQL. It translates something like select * from foo where bar = :baz into an Elixir function that takes an argument baz: "something" and returns a query like {'select * from foo where bar = ?', ['something']}. Note the ?? This is a placeholder and most database engines will populate these placeholders with the values from the passed list. The cool thing about this is that now these queries are protected against SQL injections, which we would not have if we had simply concatenated strings.

Actually the question mark was one of the things we had to patch in because Postgres and MySQL use $1, $2, etc. to identify the placeholders…

1 Like

Hi @mmmrrr,

thank you for your quick and awesome explanations!! :heart_eyes: Very helpful!

Now I understand (at least I think so now, but there is at least fifty shades of understanding :wink: ), and I’ll do some trying. Step 1 is going to be the same for fdw, obviously this is going to be my starting point. The whole process is going to take a while, because I can not work on this continuously…

Maybe I approach you again if I am deeper in the details and struggle.

Much appreciation!

1 Like